using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;

namespace HotelPosApi.Controllers;

[ApiController]
[Route("api/[controller]")]
[Authorize]
public class PricingController : ControllerBase
{
    private readonly HotelPosApi.Data.AppDbContext _db;

    public PricingController(HotelPosApi.Data.AppDbContext db)
    {
        _db = db;
    }

    [HttpGet("quote")]
    public async Task<IActionResult> Quote(
        [FromQuery] string packageName,
        [FromQuery] string checkin,
        [FromQuery] string checkout,
        [FromQuery] string roomCode,
        [FromQuery] int adult = 1,
        [FromQuery] int kid = 0,
        [FromQuery] int elderly = 0,
        [FromQuery] string nationality = "Malaysian")
    {
        if (!string.Equals(nationality?.Trim(), "Malaysian", StringComparison.OrdinalIgnoreCase))
            return BadRequest("Only Malaysian pricing is configured in packagepricing table.");

        if (string.IsNullOrWhiteSpace(packageName) ||
            string.IsNullOrWhiteSpace(checkin) ||
            string.IsNullOrWhiteSpace(checkout) ||
            string.IsNullOrWhiteSpace(roomCode))
        {
            return BadRequest("Missing required pricing parameters.");
        }

        var packageNameTrim = packageName.Trim();

        if (!DateOnly.TryParse(checkin, out var cin) || !DateOnly.TryParse(checkout, out var cout) || cout <= cin)
            return BadRequest("Invalid check-in/check-out.");

        var nights = cout.DayNumber - cin.DayNumber;
        var pax = Math.Max(1, adult) + Math.Max(0, kid) + Math.Max(0, elderly);
        if (pax < 1 || pax > 4)
            return BadRequest("Pricing supports pax capacity from 1 to 4.");

        var roomTypeName = MapRoomTypeName(roomCode);
        if (roomTypeName == null)
            return BadRequest("Unknown room type mapping for selected room.");

        await using var conn = _db.Database.GetDbConnection();
        if (conn.State != System.Data.ConnectionState.Open)
            await conn.OpenAsync();

        await using var cmd = conn.CreateCommand();
        cmd.CommandText = """
            SELECT pp.PricingID, pp.Price
            FROM packagepricing pp
            INNER JOIN packages p ON p.PackageID = pp.PackageID
            INNER JOIN durations d ON d.DurationID = pp.DurationID
            INNER JOIN roomtypes r ON r.RoomTypeID = pp.RoomTypeID
            INNER JOIN paxtypes x ON x.PaxTypeID = pp.PaxTypeID
            WHERE (TRIM(p.Name) = @packageName OR TRIM(p.Name) = LEFT(@packageName, 50))
              AND d.Nights = @nights
              AND r.Name = @roomTypeName
              AND x.Capacity = @pax
            LIMIT 1;
            """;

        // packages.Name is often varchar(50); longer UI names are truncated in DB — also match first 50 chars.
        AddParam(cmd, "@packageName", packageNameTrim);
        AddParam(cmd, "@nights", nights);
        AddParam(cmd, "@roomTypeName", roomTypeName);
        AddParam(cmd, "@pax", pax);

        await using var reader = await cmd.ExecuteReaderAsync();
        if (!await reader.ReadAsync())
        {
            return NotFound(
                $"No pricing row for packageName='{packageNameTrim}', nights={nights}, roomType='{roomTypeName}', totalPax={pax}. " +
                "Add a matching row in packagepricing (join packages.Name, durations.Nights, roomtypes.Name, paxtypes.Capacity).");
        }

        var pricingId = Convert.ToInt32(reader["PricingID"]);
        var baseRate = Convert.ToDecimal(reader["Price"]);
        return Ok(new
        {
            pricingId,
            nights,
            totalPax = pax,
            packageName = packageNameTrim,
            roomType = roomTypeName,
            baseRate,
        });
    }

    private static string? MapRoomTypeName(string roomCode)
    {
        var code = roomCode.Trim().ToUpperInvariant();
        if (code.StartsWith("WHS")) return "Seaview White House";
        if (code.StartsWith("WHT")) return "White House";
        if (code.StartsWith("ZNQ")) return "Zenith Suite";
        if (code.StartsWith("TB")) return "Timber House";
        return null;
    }

    private static void AddParam(System.Data.Common.DbCommand cmd, string name, object value)
    {
        var p = cmd.CreateParameter();
        p.ParameterName = name;
        p.Value = value;
        cmd.Parameters.Add(p);
    }
}
