using Microsoft.AspNetCore.Mvc;
using TestEnvironment.Models;
using Microsoft.AspNetCore.Http;
using Microsoft.EntityFrameworkCore;
using System.Text.RegularExpressions;
using System.Linq;

namespace TestEnvironment.Controllers
{

        [Route("api/[controller]")]
        [ApiController]
        public class BookController : ControllerBase
        {

            private readonly BookDbContext _bookDbContext;
            public BookController(BookDbContext bookDbContext)
            {
                _bookDbContext = bookDbContext;
            }


            [HttpGet]
            public ActionResult<IEnumerable<Book>> GetBooks()
            {
                return _bookDbContext.Book;
            }


            [HttpGet("{id:int}")]

            public async Task<ActionResult<Book>> GetBookById(int id)
            {
                var book = await _bookDbContext.Book.FindAsync(id);
                return Ok(book);
            }

            [HttpGet("books/title-starting-with-a")]
            public async Task<IActionResult> GetBooksByTitleA()
            {
                var books = await _bookDbContext.Book
                    .FromSqlRaw("SELECT * FROM book WHERE title LIKE 'A%' ORDER BY title;")
                    .ToListAsync();

                return Ok(books);
            }

            [HttpGet("books/title-starting-with-aa")]
            public async Task<IActionResult> GetBooksByTitleAA()
            {
            var books = await _bookDbContext.Book
                .FromSqlRaw("SELECT * FROM book WHERE title LIKE 'Ā%' ORDER BY title;")
                .ToListAsync();

                return Ok(books);
            }

            [HttpGet("books/title-starting-with-b")]
            public async Task<IActionResult> GetBooksByTitleB()
            {
                var books = await _bookDbContext.Book
                    .FromSqlRaw("SELECT * FROM book WHERE title LIKE 'B%' ORDER BY title;")
                    .ToListAsync();

                return Ok(books);
            }

            [HttpGet("books/title-starting-with-c")]
            public async Task<IActionResult> GetBooksByTitleC()
            {
                var books = await _bookDbContext.Book
                    .FromSqlRaw("SELECT * FROM book WHERE title LIKE 'C%' ORDER BY title;")
                    .ToListAsync();

                return Ok(books);
            }

            [HttpGet("books/title-starting-with-cc")]
            public async Task<IActionResult> GetBooksByTitleCC()
            {
                var books = await _bookDbContext.Book
                    .FromSqlRaw("SELECT * FROM book WHERE title LIKE 'Č%' ORDER BY title;")
                    .ToListAsync();

                return Ok(books);
            }

            [HttpGet("books/title-starting-with-d")]
            public async Task<IActionResult> GetBooksByTitleD()
            {
                var books = await _bookDbContext.Book
                    .FromSqlRaw("SELECT * FROM book WHERE title LIKE 'D%' ORDER BY title;")
                    .ToListAsync();

                return Ok(books);
            }

            [HttpGet("books/title-starting-with-e")]
            public async Task<IActionResult> GetBooksByTitleE()
            {
                var books = await _bookDbContext.Book
                    .FromSqlRaw("SELECT * FROM book WHERE title LIKE 'E%' ORDER BY title;")
                    .ToListAsync();

                return Ok(books);
            }

            [HttpGet("books/title-starting-with-ee")]
            public async Task<IActionResult> GetBooksByTitleEE()
            {
                var books = await _bookDbContext.Book
                    .FromSqlRaw("SELECT * FROM book WHERE title LIKE 'Ē%' ORDER BY title;")
                    .ToListAsync();

                return Ok(books);
            }

            [HttpGet("books/title-starting-with-f")]
            public async Task<IActionResult> GetBooksByTitleF()
            {
                var books = await _bookDbContext.Book
                    .FromSqlRaw("SELECT * FROM book WHERE title LIKE 'F%' ORDER BY title;")
                    .ToListAsync();

                return Ok(books);
            }

            [HttpGet("books/title-starting-with-g")]
            public async Task<IActionResult> GetBooksByTitleG()
            {
                var books = await _bookDbContext.Book
                    .FromSqlRaw("SELECT * FROM book WHERE title LIKE 'G%' ORDER BY title;")
                    .ToListAsync();

                return Ok(books);
            }

            [HttpGet("books/title-starting-with-gg")]
            public async Task<IActionResult> GetBooksByTitleGG()
            {
                var books = await _bookDbContext.Book
                    .FromSqlRaw("SELECT * FROM book WHERE title LIKE 'Ģ%' ORDER BY title;")
                    .ToListAsync();

                return Ok(books);
            }

            [HttpGet("books/title-starting-with-h")]
            public async Task<IActionResult> GetBooksByTitleH()
            {
                var books = await _bookDbContext.Book
                    .FromSqlRaw("SELECT * FROM book WHERE title LIKE 'H%' ORDER BY title;")
                    .ToListAsync();

                return Ok(books);
            }

            [HttpGet("books/title-starting-with-i")]
            public async Task<IActionResult> GetBooksByTitleI()
            {
                var books = await _bookDbContext.Book
                    .FromSqlRaw("SELECT * FROM book WHERE title LIKE 'I%' ORDER BY title;")
                    .ToListAsync();

                return Ok(books);
            }

            [HttpGet("books/title-starting-with-ii")]
            public async Task<IActionResult> GetBooksByTitleII()
            {
                var books = await _bookDbContext.Book
                    .FromSqlRaw("SELECT * FROM book WHERE title LIKE 'Ī%' ORDER BY title;")
                    .ToListAsync();

                return Ok(books);
            }

            [HttpGet("books/title-starting-with-j")]
            public async Task<IActionResult> GetBooksByTitleJ()
            {
                var books = await _bookDbContext.Book
                    .FromSqlRaw("SELECT * FROM book WHERE title LIKE 'J%' ORDER BY title;")
                    .ToListAsync();

                return Ok(books);
            }

            [HttpGet("books/title-starting-with-k")]
            public async Task<IActionResult> GetBooksByTitleK()
            {
                var books = await _bookDbContext.Book
                    .FromSqlRaw("SELECT * FROM book WHERE title LIKE 'K%' ORDER BY title;")
                    .ToListAsync();

                return Ok(books);
            }

            [HttpGet("books/title-starting-with-kk")]
            public async Task<IActionResult> GetBooksByTitleKK()
            {
                var books = await _bookDbContext.Book
                    .FromSqlRaw("SELECT * FROM book WHERE title LIKE 'Ķ%' ORDER BY title;")
                    .ToListAsync();

                return Ok(books);
            }

            [HttpGet("books/title-starting-with-l")]
            public async Task<IActionResult> GetBooksByTitleL()
            {
                var books = await _bookDbContext.Book
                    .FromSqlRaw("SELECT * FROM book WHERE title LIKE 'L%' ORDER BY title;")
                    .ToListAsync();

                return Ok(books);
            }

            [HttpGet("books/title-starting-with-ll")]
            public async Task<IActionResult> GetBooksByTitleLL()
            {
                var books = await _bookDbContext.Book
                    .FromSqlRaw("SELECT * FROM book WHERE title LIKE 'Ļ%' ORDER BY title;")
                    .ToListAsync();

                return Ok(books);
            }

            [HttpGet("books/title-starting-with-m")]
            public async Task<IActionResult> GetBooksByTitleM()
            {
                var books = await _bookDbContext.Book
                    .FromSqlRaw("SELECT * FROM book WHERE title LIKE 'M%' ORDER BY title;")
                    .ToListAsync();

                return Ok(books);
            }

            [HttpGet("books/title-starting-with-n")]
            public async Task<IActionResult> GetBooksByTitleN()
            {
                var books = await _bookDbContext.Book
                    .FromSqlRaw("SELECT * FROM book WHERE title LIKE 'N%' ORDER BY title;")
                    .ToListAsync();

                return Ok(books);
            }

            [HttpGet("books/title-starting-with-nn")]
            public async Task<IActionResult> GetBooksByTitleNN()
            {
                var books = await _bookDbContext.Book
                    .FromSqlRaw("SELECT * FROM book WHERE title LIKE 'Ņ%' ORDER BY title;")
                    .ToListAsync();

                return Ok(books);
            }

            [HttpGet("books/title-starting-with-o")]
            public async Task<IActionResult> GetBooksByTitleO()
            {
                var books = await _bookDbContext.Book
                    .FromSqlRaw("SELECT * FROM book WHERE title LIKE 'O%' ORDER BY title;")
                    .ToListAsync();

                return Ok(books);
            }

            [HttpGet("books/title-starting-with-p")]
            public async Task<IActionResult> GetBooksByTitleP()
            {
                var books = await _bookDbContext.Book
                    .FromSqlRaw("SELECT * FROM book WHERE title LIKE 'P%' ORDER BY title;")
                    .ToListAsync();

                return Ok(books);
            }

            [HttpGet("books/title-starting-with-r")]
            public async Task<IActionResult> GetBooksByTitleR()
            {
                var books = await _bookDbContext.Book
                    .FromSqlRaw("SELECT * FROM book WHERE title LIKE 'R%' ORDER BY title;")
                    .ToListAsync();

                return Ok(books);
            }

            [HttpGet("books/title-starting-with-s")]
            public async Task<IActionResult> GetBooksByTitleS()
            {
                var books = await _bookDbContext.Book
                    .FromSqlRaw("SELECT * FROM book WHERE title LIKE 'S%' ORDER BY title;")
                    .ToListAsync();

                return Ok(books);
            }

            [HttpGet("books/title-starting-with-ss")]
            public async Task<IActionResult> GetBooksByTitleSS()
            {
                var books = await _bookDbContext.Book
                    .FromSqlRaw("SELECT * FROM book WHERE title LIKE 'Š%' ORDER BY title;")
                    .ToListAsync();

                return Ok(books);
            }

            [HttpGet("books/title-starting-with-t")]
            public async Task<IActionResult> GetBooksByTitleT()
            {
                var books = await _bookDbContext.Book
                    .FromSqlRaw("SELECT * FROM book WHERE title LIKE 'T%' ORDER BY title;")
                    .ToListAsync();

                return Ok(books);
            }

            [HttpGet("books/title-starting-with-u")]
            public async Task<IActionResult> GetBooksByTitleU()
            {
                var books = await _bookDbContext.Book
                    .FromSqlRaw("SELECT * FROM book WHERE title LIKE 'U%' ORDER BY title;")
                    .ToListAsync();

                return Ok(books);
            }
            [HttpGet("books/title-starting-with-uu")]
            public async Task<IActionResult> GetBooksByTitleUU()
            {
                var books = await _bookDbContext.Book
                    .FromSqlRaw("SELECT * FROM book WHERE title LIKE 'Ū%' ORDER BY title;")
                    .ToListAsync();

                return Ok(books);
            }

            [HttpGet("books/title-starting-with-v")]
            public async Task<IActionResult> GetBooksByTitleV()
            {
                var books = await _bookDbContext.Book
                    .FromSqlRaw("SELECT * FROM book WHERE title LIKE 'V%' ORDER BY title;")
                    .ToListAsync();

                return Ok(books);
            }

            [HttpGet("books/title-starting-with-z")]
            public async Task<IActionResult> GetBooksByTitleZ()
            {
                var books = await _bookDbContext.Book
                    .FromSqlRaw("SELECT * FROM book WHERE title LIKE 'Z%' ORDER BY title;")
                    .ToListAsync();

                return Ok(books);
            }

            [HttpGet("books/title-starting-with-zz")]
            public async Task<IActionResult> GetBooksByTitleZZ()
            {
                var books = await _bookDbContext.Book
                    .FromSqlRaw("SELECT * FROM book WHERE title LIKE 'Ž%' ORDER BY title;")
                    .ToListAsync();

                return Ok(books);
            }

            [HttpPost]

            public async Task<ActionResult> CreateBook(Book book)
            {
                await _bookDbContext.Book.AddAsync(book);
                await _bookDbContext.SaveChangesAsync();
                return Ok();
            }


            [HttpPut]

            public async Task<ActionResult> UpdateBook(Book book)
            {
                _bookDbContext.Book.Update(book);
                await _bookDbContext.SaveChangesAsync();
                return Ok();
            }


            [HttpDelete("{id:int}")]

            public async Task<ActionResult> DeleteBook(int id)
            {
                var book = await _bookDbContext.Book.FindAsync(id);
                _bookDbContext.Book.Remove(book);
                return Ok();
            }
        }
 }