using Microsoft.EntityFrameworkCore;
using vendorApproverBackend.Data;
using System.Globalization;
using vendorApproverBackend.Requests;
using vendorApproverBackend.Entities;
using vendorApproverBackend.Enums;
using vendorApproverBackend.Responses; 
using System.Threading.Tasks;

namespace vendorApproverBackend.Services
{
    public class ProductService
    {
        private readonly DataContext _context;

        public ProductService(DataContext context)
        {
            _context = context;
        }

        #region Basic CRUD
        
        public async Task<Product?> FindByIdAsync(long id)
        {
            return await _context.Products.FindAsync(id);
        }
        
        public async Task DeleteAsync(long id)
        {
            var product = await FindByIdAsync(id);
            if (product != null)
            {
                _context.Products.Remove(product);
                await _context.SaveChangesAsync();
            }
        }

        #endregion


        #region Criteria Search Method

    
        public async Task<PagedResult<Product>> FindProductsByCriteriaAsync(
    int page,
    int size,
    string? sku = null,
    string? category = null,
    string? search = null,
    string? productType = null,
    string? vendorName = null,
    long? vendorId = null,
    long? pqRequest = null)
{
    // Start query from Products table and include PrequalificationRequest + Vendor navigation
    var query = _context.Products
        .Include(p => p.PrequalificationRequest)
        .ThenInclude(pr => pr.Vendor)
        .AsQueryable();

    // ===== Apply filters dynamically =====

    if (!string.IsNullOrWhiteSpace(sku))
    {
        var skuLower = sku.ToLower();
        query = query.Where(p => p.Sku != null && p.Sku.ToLower().Contains(skuLower));
    }

    if (!string.IsNullOrWhiteSpace(category))
    {
        query = query.Where(p => p.Category != null && p.Category.Equals(category));
    }

    if (!string.IsNullOrWhiteSpace(search))
    {
        var searchLower = search.ToLower();
        query = query.Where(p => p.ProductName != null && p.ProductName.ToLower().Contains(searchLower));
    }

    if (!string.IsNullOrWhiteSpace(productType))
    {
        query = query.Where(p => p.ProductType != null && p.ProductType.Equals(productType));
    }

    if (!string.IsNullOrWhiteSpace(vendorName))
    {
        var vendorNameLower = vendorName.ToLower();
        query = query.Where(p =>
            p.PrequalificationRequest != null &&
            p.PrequalificationRequest.Vendor != null &&
            p.PrequalificationRequest.Vendor.RepresentativeName != null &&
            p.PrequalificationRequest.Vendor.RepresentativeName.ToLower().Contains(vendorNameLower)
        );
    }

    if (vendorId.HasValue)
    {
        query = query.Where(p =>
            (p.PrequalificationRequest != null && p.PrequalificationRequest.VendorId == vendorId.Value)
            || (p.VendorId.HasValue && p.VendorId.Value == vendorId.Value)
        );
    }

    if (pqRequest.HasValue)
    {
        query = query.Where(p => p.PrequalificationRequestId == pqRequest.Value);
    }

    // ===== Pagination and ordering =====
    var totalCount = await query.CountAsync();

    var items = await query
        .OrderBy(p => p.ProductName)
        .Skip(page * size)
        .Take(size)
        .ToListAsync();

    return new PagedResult<Product>(items, totalCount, page, size);
}

        #endregion

        private string GenerateNewSku()
        {
            string uniqueId = Guid.NewGuid().ToString("N").Substring(0, 10).ToUpper();
            return $"PROD_{uniqueId}";
        }


        public async Task<Product> GenerateProductFromPqService(PrequalificationRequest pqRequest)
    {
        if (pqRequest == null) 
            throw new ArgumentNullException(nameof(pqRequest), "PrequalificationRequest cannot be null.");
            

        if (await _context.Products.AnyAsync(p => p.PrequalificationRequestId == pqRequest.Id))
        {
            throw new InvalidOperationException($"Product already exists for PrequalificationRequest ID: {pqRequest.Id}");
        }

        var generalInfo = pqRequest.GeneralInformation 
            ?? throw new InvalidOperationException("GeneralInformation must be present in the PrequalificationRequest......");

        var vendor = pqRequest.Vendor 
            ?? throw new InvalidOperationException("Vendor must be included in the PrequalificationRequest.......");
            
        var vendorUser = vendor.User 
            ?? throw new InvalidOperationException("Vendor User must be included in the PrequalificationRequest's Vendor......");

        DateTime approvalDate = pqRequest.ApprovalDate ?? DateTime.UtcNow;
        DateTime expiryDate = approvalDate.AddYears(3);

        var newProduct = new Product
        {
            Category = generalInfo.Category,
            Subcategory = generalInfo.Subcategory,
            ProductName = generalInfo.ProductName,
            ProductCity = generalInfo.ProductCity,
            ProductCountry = generalInfo.ProductCountry,
            ProductType = generalInfo.ProductType,
            ProductDesc = generalInfo.ProductDesc, 
            Status = RequestStatus.CERTIFIED, 
            VendorName = vendor.RepresentativeName,
            VendorId = vendorUser.Id,
            PrequalificationRequestId = pqRequest.Id,
            Manufacturer = generalInfo.FullName,
            LocalAgent = generalInfo.AgentNames,
            ApprovalDate = approvalDate,
            FirstApprovalDate = approvalDate,
            ExpiryDate = expiryDate,

            Sku = GenerateNewSku(),
        };

        _context.Products.Add(newProduct);
        await _context.SaveChangesAsync();

        return newProduct;
    }
    
    

    public async Task<Product> UpdateProductAsync(long productId, UpdateProductRequest dto)
{
    var product = await _context.Products.FindAsync(productId);

    if (product == null){
            throw new InvalidOperationException($"Product with ID {productId} not found.");
        }

    if (dto.Sku != null)
        product.Sku = dto.Sku;

    if (dto.Category != null)
        product.Category = dto.Category;

    if (dto.Subcategory != null)
        product.Subcategory = dto.Subcategory;

    if (dto.ProductName != null)
        product.ProductName = dto.ProductName;

    if (dto.ProductCity != null)
        product.ProductCity = dto.ProductCity;

    if (dto.ProductCountry != null)
        product.ProductCountry = dto.ProductCountry;

    if (dto.ProductType != null)
        product.ProductType = dto.ProductType;

    if (dto.ProductDesc != null)
        product.ProductDesc = dto.ProductDesc;

    if (dto.Status.HasValue)
        product.Status = dto.Status;

    if (dto.ManufacturerEmail != null)
        product.ManufacturerEmail = dto.ManufacturerEmail;

    if (dto.LocalAgentEmail != null)
        product.LocalAgentEmail = dto.LocalAgentEmail;


    await _context.SaveChangesAsync();
    return product;
}
    public async Task<Product> CreateLegacyProductAsync(LegacyProductRequest request)
{
    var product = new Product
    {
        Sku = request.Sku ?? GenerateNewSku(), // Use provided SKU or auto-generate
        ProductDesc = request.ProductDesc,
        Manufacturer = request.Manufacturer,
        ProductCountry = request.ProductCountry,
        LocalAgent = request.LocalAgent,
        ManufacturerEmail = request.ManufacturerEmail,
        LocalAgentEmail = request.LocalAgentEmail,
        FirstApprovalDate = request.FirstApprovalDate,
        Status = RequestStatus.CERTIFIED, // As requested

        Timestamp = DateTime.UtcNow,
        PrequalificationRequestId = null // Explicitly null for legacy
    }; 

    // 1. Convert Dates from string dd-MM-yyyy
    product.ApprovalDate = ParseLegacyDate(request.ApprovalDate);
    product.ExpiryDate = ParseLegacyDate(request.ExpiryDate);

    // 2. Handle Vendor linkage
    if (request.VendorId.HasValue)
    {
        var vendor = await _context.Vendors.FindAsync(request.VendorId.Value);
        if (vendor != null)
        {
            product.VendorId = vendor.Id;
            product.VendorName = vendor.RepresentativeName; // Matching your search criteria property
        }
    }

    _context.Products.Add(product);
    await _context.SaveChangesAsync();
    return product;
}

private DateTime? ParseLegacyDate(string? dateString)
{
    if (string.IsNullOrWhiteSpace(dateString)) return null;

    // Strict parsing for dd-MM-yyyy format
    if (DateTime.TryParseExact(dateString, "dd-MM-yyyy", 
        CultureInfo.InvariantCulture, DateTimeStyles.None, out DateTime result))
    {
        // Ensure Kind is UTC for database compatibility
        return DateTime.SpecifyKind(result, DateTimeKind.Utc);
    }
    
    return null; 
}

}
}