using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using MySqlConnector;
using Recepie.Data;
using Recepie.Models;
using Recepie.ViewModels;

namespace Recepie.Controllers
{
    public class RecipeController : Controller
    {
        private readonly RecipeContext _context;

        public RecipeController(RecipeContext context)
        {
            _context = context;
        }

        public async Task<IActionResult> Index(string searchTerm = "", string includeIngredients = "", string excludeIngredients = "", string category = "")
        {
            var recipesQuery = _context.Recipes
                .Include(r => r.RecipeIngredients)
                    .ThenInclude(ri => ri.Ingredient)
                .AsQueryable();

            // Search by title or description
            if (!string.IsNullOrEmpty(searchTerm))
            {
                recipesQuery = recipesQuery.Where(r =>
                    (r.Title != null && r.Title.Contains(searchTerm)) ||
                    (r.Description != null && r.Description.Contains(searchTerm)));
            }

            // Include ingredients filter
            if (!string.IsNullOrEmpty(includeIngredients))
            {
                var includeIngredientsList = includeIngredients.Split(',').Select(i => i.Trim().ToLower()).Where(i => !string.IsNullOrEmpty(i)).ToList();
                if (includeIngredientsList.Any())
                {
                    foreach (var ingredient in includeIngredientsList)
                    {
                        recipesQuery = recipesQuery.Where(r => r.RecipeIngredients.Any(ri =>
                            ri.Ingredient != null && ri.Ingredient.Name != null && ri.Ingredient.Name.ToLower().Contains(ingredient)));
                    }
                }
            }

            // Exclude ingredients filter
            if (!string.IsNullOrEmpty(excludeIngredients))
            {
                var excludeIngredientsList = excludeIngredients.Split(',').Select(i => i.Trim().ToLower()).Where(i => !string.IsNullOrEmpty(i)).ToList();
                if (excludeIngredientsList.Any())
                {
                    foreach (var ingredient in excludeIngredientsList)
                    {
                        recipesQuery = recipesQuery.Where(r => !r.RecipeIngredients.Any(ri =>
                            ri.Ingredient != null && ri.Ingredient.Name != null && ri.Ingredient.Name.ToLower().Contains(ingredient)));
                    }
                }
            }

            // Order by title
            recipesQuery = recipesQuery.OrderBy(r => r.Title);

            var recipes = await recipesQuery.ToListAsync();

            var viewModel = new RecipeIndexViewModel
            {
                Recipes = recipes,
                SearchTerm = searchTerm,
                IncludeIngredients = includeIngredients,
                ExcludeIngredients = excludeIngredients,
                SelectedCategory = "", // Disabled for now
                Categories = new List<string>() // Empty for now
            };

            return View(viewModel);
        }

        public async Task<IActionResult> Details(int id)
        {
            var recipe = await _context.Recipes
                .Include(r => r.RecipeIngredients)
                    .ThenInclude(ri => ri.Ingredient)
                .Include(r => r.RecipeSteps)
                .FirstOrDefaultAsync(r => r.Id == id);

            if (recipe == null)
            {
                return NotFound();
            }

            return View(recipe);
        }

        // Action to serve recipe images from BLOB data
        public async Task<IActionResult> GetImage(int recipeId)
        {
            try
            {
                var connection = _context.Database.GetDbConnection();
                await connection.OpenAsync();

                using var command = connection.CreateCommand();
                command.CommandText = "SELECT image FROM recepieImage WHERE recepieId = @recipeId";
                command.Parameters.Add(new MySqlConnector.MySqlParameter("@recipeId", recipeId));

                var result = await command.ExecuteScalarAsync();
                await connection.CloseAsync();

                if (result == null || result == DBNull.Value)
                {
                    return NotFound();
                }

                var imageData = (byte[])result;
                return File(imageData, "image/jpeg");
            }
            catch
            {
                return NotFound();
            }
        }

        // Check if recipe has image without loading BLOB data
        public async Task<IActionResult> HasImage(int recipeId)
        {
            try
            {
                var connection = _context.Database.GetDbConnection();
                await connection.OpenAsync();

                using var command = connection.CreateCommand();
                command.CommandText = "SELECT COUNT(*) FROM recepieImage WHERE recepieId = @recipeId AND image IS NOT NULL";
                command.Parameters.Add(new MySqlConnector.MySqlParameter("@recipeId", recipeId));

                var count = await command.ExecuteScalarAsync();
                await connection.CloseAsync();

                var hasImage = Convert.ToInt32(count) > 0;
                return Json(hasImage);
            }
            catch
            {
                return Json(false);
            }
        }

        // Action to serve recipe images from BLOB data - Temporarily disabled
        /*
        public async Task<IActionResult> GetImage(int recipeId, int imageId = 0)
        {
            RecipeImage? recipeImage;
            
            if (imageId > 0)
            {
                // Get specific image by ID
                recipeImage = await _context.RecipeImages
                    .FirstOrDefaultAsync(ri => ri.Id == imageId && ri.RecipeId == recipeId);
            }
            else
            {
                // Get first image for the recipe
                recipeImage = await _context.RecipeImages
                    .FirstOrDefaultAsync(ri => ri.RecipeId == recipeId);
            }

            if (recipeImage?.ImageData == null)
            {
                return NotFound();
            }

            // Determine content type (default to JPEG if not specified)
            string contentType = !string.IsNullOrEmpty(recipeImage.ContentType) 
                ? recipeImage.ContentType 
                : "image/jpeg";

            return File(recipeImage.ImageData, contentType);
        }
        */

        public IActionResult Create()
        {
            return View();
        }

        [HttpPost]
        [ValidateAntiForgeryToken]
        public async Task<IActionResult> Create([Bind("Id,Title,Description,Difficulty,Url,Time")] Recipe recipe)
        {
            if (ModelState.IsValid)
            {
                _context.Add(recipe);
                await _context.SaveChangesAsync();
                return RedirectToAction(nameof(Index));
            }
            return View(recipe);
        }

        public async Task<IActionResult> Edit(int id)
        {
            var recipe = await _context.Recipes.FindAsync(id);
            if (recipe == null)
            {
                return NotFound();
            }
            return View(recipe);
        }

        [HttpPost]
        [ValidateAntiForgeryToken]
        public async Task<IActionResult> Edit(int id, [Bind("Id,Title,Description,Difficulty,Url,Time")] Recipe recipe)
        {
            if (id != recipe.Id)
            {
                return NotFound();
            }

            if (ModelState.IsValid)
            {
                try
                {
                    _context.Update(recipe);
                    await _context.SaveChangesAsync();
                }
                catch (DbUpdateConcurrencyException)
                {
                    if (!RecipeExists(recipe.Id))
                    {
                        return NotFound();
                    }
                    else
                    {
                        throw;
                    }
                }
                return RedirectToAction(nameof(Index));
            }
            return View(recipe);
        }

        public async Task<IActionResult> Delete(int id)
        {
            var recipe = await _context.Recipes.FindAsync(id);
            if (recipe == null)
            {
                return NotFound();
            }

            return View(recipe);
        }

        [HttpPost, ActionName("Delete")]
        [ValidateAntiForgeryToken]
        public async Task<IActionResult> DeleteConfirmed(int id)
        {
            var recipe = await _context.Recipes.FindAsync(id);
            if (recipe != null)
            {
                _context.Recipes.Remove(recipe);
            }

            await _context.SaveChangesAsync();
            return RedirectToAction(nameof(Index));
        }

        private bool RecipeExists(int id)
        {
            return _context.Recipes.Any(e => e.Id == id);
        }

        // Simplified API endpoint for testing
        [HttpGet]
        public async Task<IActionResult> Api()
        {
            var recipes = await _context.Recipes.Take(10).ToListAsync();
            return Json(recipes);
        }

        // Simple test page
        public IActionResult SimpleTest()
        {
            return View();
        }

        // Database inspection
        public async Task<IActionResult> InspectDatabase()
        {
            try
            {
                // Try to query the information_schema to get table names
                var tablesQuery = @"
                    SELECT TABLE_NAME 
                    FROM information_schema.TABLES 
                    WHERE TABLE_SCHEMA = 'recept'";

                var connection = _context.Database.GetDbConnection();
                await connection.OpenAsync();

                using var command = connection.CreateCommand();
                command.CommandText = tablesQuery;

                var tables = new List<string>();
                using var reader = await command.ExecuteReaderAsync();
                while (await reader.ReadAsync())
                {
                    tables.Add(reader.GetString(0));
                }
                await reader.CloseAsync();

                ViewBag.Tables = tables;

                // Check if recepieImage table exists
                var imageTableExists = tables.Any(t => t.ToLower().Contains("image"));
                ViewBag.ImageTableExists = imageTableExists;

                if (imageTableExists)
                {
                    var imageTableName = tables.FirstOrDefault(t => t.ToLower().Contains("image"));
                    ViewBag.ImageTableName = imageTableName;

                    // Get table structure
                    try
                    {
                        command.CommandText = $"DESCRIBE `{imageTableName}`";
                        using var structureReader = await command.ExecuteReaderAsync();

                        var columns = new List<string>();
                        while (await structureReader.ReadAsync())
                        {
                            var field = structureReader.GetString(0);
                            var type = structureReader.GetString(1);
                            var isNull = structureReader.GetString(2);
                            var key = structureReader.GetString(3);
                            columns.Add($"{field} ({type}) {(key == "PRI" ? "PRIMARY KEY" : "")} {(isNull == "YES" ? "NULL" : "NOT NULL")}");
                        }
                        await structureReader.CloseAsync();
                        ViewBag.ImageTableColumns = columns;

                        // Get sample data count
                        command.CommandText = $"SELECT COUNT(*) FROM `{imageTableName}`";
                        var count = await command.ExecuteScalarAsync();
                        ViewBag.ImageRecordCount = count;

                    }
                    catch (Exception ex)
                    {
                        ViewBag.ImageTableError = ex.Message;
                    }
                }

                await connection.CloseAsync();
                return View();
            }
            catch (Exception ex)
            {
                ViewBag.Error = ex.Message;
                return View();
            }
        }
    }
}