Kitty_Fall_Mytel/Kitty_Fall/Kitty_Fall/Kitty_Fall.Cms/Controllers/MonthlyWinnerController.cs

using Common;
using ClosedXML.Excel;
using Database.Database;
using Kitty_Fall.Cms.Helpers;
using Kitty_Fall.Cms.Models;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;

namespace Kitty_Fall.Cms.Controllers;

public class MonthlyWinnerController : CmsAuthorizedController
{
    private readonly ModelContext _db;

    public MonthlyWinnerController(ModelContext db)
    {
        _db = db;
    }

    [HttpGet("/MonthlyWinner")]
    [HttpGet("/MonthlyWinner/Index")]
    public async Task<IActionResult> Index(int? monthKey = null)
    {
        if (!CheckLogin()) return RedirectToLogin();
        if (!HasMenuPermission(CmsSecurityHelper.MonthlyWinnersMenuKey)) return RedirectNoPermission();

        var normalizedMonthKey = NormalizeMonthKey(monthKey);
        var draw = await _db.GameMonthlyDraws
            .AsNoTracking()
            .FirstOrDefaultAsync(x => x.MonthKey == normalizedMonthKey);

        var winners = await (
            from winner in _db.GameMonthlyWinners.AsNoTracking()
            join ticket in _db.GameMonthlyDrawTickets.AsNoTracking()
                on winner.TicketId equals ticket.TicketId into ticketJoin
            from ticket in ticketJoin.DefaultIfEmpty()
            where winner.MonthKey == normalizedMonthKey
            orderby winner.CreatedTime descending
            select new MonthlyWinnerItemViewModel
            {
                WinnerId = winner.WinnerId,
                DrawId = winner.DrawId,
                MonthKey = winner.MonthKey,
                Msisdn = winner.Msisdn,
                TicketId = winner.TicketId,
                TicketNo = ticket == null ? null : ticket.TicketNo,
                TotalScore = ticket == null ? null : ticket.TotalScore,
                PrizeCode = winner.PrizeCode,
                PrizeName = winner.PrizeName,
                PrizeValue = winner.PrizeValue,
                PrizeUnit = winner.PrizeUnit,
                Status = winner.Status,
                StatusText = ToStatusText(winner.Status),
                AnnounceTime = winner.AnnounceTime,
                PaidTime = winner.PaidTime,
                CreatedTime = winner.CreatedTime
            })
            .ToListAsync();

        var prizeConfigs = await _db.GameMonthlyPrizeConfigs
            .AsNoTracking()
            .Where(x => x.Status != false)
            .OrderBy(x => x.DisplayOrder)
            .ThenBy(x => x.PrizeConfigId)
            .Select(x => new MonthlyWinnerPrizeViewModel
            {
                PrizeCode = x.PrizeCode,
                PrizeName = x.PrizeName,
                PrizeValue = x.PrizeValue,
                PrizeUnit = x.PrizeUnit
            })
            .ToListAsync();

        var selectedMsisdns = winners.Select(x => x.Msisdn).ToHashSet(StringComparer.OrdinalIgnoreCase);
        var tickets = await _db.GameMonthlyDrawTickets
            .AsNoTracking()
            .Where(x => x.MonthKey == normalizedMonthKey && !selectedMsisdns.Contains(x.Msisdn))
            .OrderByDescending(x => x.TotalScore)
            .ThenBy(x => x.TicketNo)
            .Take(300)
            .Select(x => new MonthlyWinnerTicketViewModel
            {
                TicketId = x.TicketId,
                Msisdn = x.Msisdn,
                TicketNo = x.TicketNo,
                TotalScore = x.TotalScore
            })
            .ToListAsync();

        var model = new MonthlyWinnerViewModel
        {
            MonthKey = normalizedMonthKey,
            MonthText = ToMonthText(normalizedMonthKey),
            TotalWinners = winners.Count,
            TotalEligible = draw?.TotalEligible ?? 0,
            TotalTicket = draw?.TotalTicket ?? 0,
            Winners = winners,
            PrizeConfigs = prizeConfigs,
            Tickets = tickets
        };

        return View(model);
    }

    [HttpGet("/MonthlyWinner/ExportTickets")]
    public async Task<IActionResult> ExportTickets(int? monthKey = null)
    {
        if (!CheckLogin()) return RedirectToLogin();
        if (!HasMenuPermission(CmsSecurityHelper.MonthlyWinnersMenuKey)) return RedirectNoPermission();

        var normalizedMonthKey = NormalizeMonthKey(monthKey);
        var tickets = await _db.GameMonthlyDrawTickets
            .AsNoTracking()
            .Where(x => x.MonthKey == normalizedMonthKey)
            .OrderBy(x => x.TicketNo)
            .Select(x => new
            {
                x.TicketNo,
                x.Msisdn,
                x.TotalScore,
                x.CreatedTime
            })
            .ToListAsync();

        using var workbook = new XLWorkbook();
        var worksheet = workbook.Worksheets.Add("Tickets");
        var headers = new[] { "No.", "Ticket No.", "MSISDN", "Total Score", "Created Time" };
        for (var column = 0; column < headers.Length; column++)
        {
            worksheet.Cell(1, column + 1).Value = headers[column];
        }

        for (var index = 0; index < tickets.Count; index++)
        {
            var ticket = tickets[index];
            var row = index + 2;
            worksheet.Cell(row, 1).Value = index + 1;
            worksheet.Cell(row, 2).Value = ticket.TicketNo;
            worksheet.Cell(row, 3).Value = ticket.Msisdn;
            worksheet.Cell(row, 4).Value = ticket.TotalScore;
            worksheet.Cell(row, 5).Value = ticket.CreatedTime;
        }

        var lastRow = Math.Max(1, tickets.Count + 1);
        var tableRange = worksheet.Range(1, 1, lastRow, headers.Length);
        tableRange.Style.Border.InsideBorder = XLBorderStyleValues.Thin;
        tableRange.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
        tableRange.Style.Border.InsideBorderColor = XLColor.LightGray;
        tableRange.Style.Border.OutsideBorderColor = XLColor.LightGray;

        var headerRange = worksheet.Range(1, 1, 1, headers.Length);
        headerRange.Style.Font.Bold = true;
        headerRange.Style.Font.FontColor = XLColor.White;
        headerRange.Style.Fill.BackgroundColor = XLColor.FromHtml("#6D28D9");
        headerRange.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;

        worksheet.Column(1).Style.NumberFormat.Format = "#,##0";
        worksheet.Column(2).Style.NumberFormat.Format = "#,##0";
        worksheet.Column(3).Style.NumberFormat.Format = "@";
        worksheet.Column(4).Style.NumberFormat.Format = "#,##0";
        worksheet.Column(5).Style.DateFormat.Format = "yyyy-mm-dd hh:mm:ss";
        worksheet.SheetView.FreezeRows(1);
        worksheet.RangeUsed()?.SetAutoFilter();
        worksheet.Columns().AdjustToContents();
        worksheet.Column(3).Width = Math.Max(worksheet.Column(3).Width, 18);
        worksheet.Column(5).Width = Math.Max(worksheet.Column(5).Width, 21);

        using var stream = new MemoryStream();
        workbook.SaveAs(stream);
        var fileName = $"monthly-tickets-{normalizedMonthKey}.xlsx";
        return File(
            stream.ToArray(),
            "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
            fileName);
    }

    [HttpPost]
    public async Task<JsonResult> SaveWinner(MonthlyWinnerSaveRequest request)
    {
        if (!CheckLogin()) return Json(new { errorCode = 401, message = "Unauthorized" });
        if (!HasMenuPermission(CmsSecurityHelper.MonthlyWinnersMenuKey)) return NoPermissionJson();

        try
        {
            var monthKey = NormalizeMonthKey(request.MonthKey);
            var prizeCode = (request.PrizeCode ?? string.Empty).Trim();
            if (string.IsNullOrWhiteSpace(prizeCode))
            {
                return Json(new { errorCode = 400, message = "Prize is required." });
            }

            var prize = await _db.GameMonthlyPrizeConfigs
                .AsNoTracking()
                .FirstOrDefaultAsync(x => x.PrizeCode == prizeCode && x.Status != false);
            if (prize == null)
            {
                return Json(new { errorCode = 404, message = "Prize config not found." });
            }

            var draw = await EnsureDrawAsync(monthKey);
            var ticket = await LoadTicketAsync(monthKey, request.TicketId);
            var msisdn = NormalizeMsisdn(ticket?.Msisdn ?? request.Msisdn);
            if (string.IsNullOrWhiteSpace(msisdn))
            {
                return Json(new { errorCode = 400, message = "MSISDN or ticket is required." });
            }

            var prizeWinnerCount = await _db.GameMonthlyWinners
                .AsNoTracking()
                .CountAsync(x => x.DrawId == draw.DrawId
                    && x.PrizeCode == prize.PrizeCode
                    && x.WinnerId != request.WinnerId);
            if (prizeWinnerCount >= prize.Quantity)
            {
                return Json(new { errorCode = 409, message = $"This monthly prize already has {prize.Quantity} winner(s)." });
            }

            var userAlreadyHasPrize = await _db.GameMonthlyWinners
                .AsNoTracking()
                .AnyAsync(x => x.DrawId == draw.DrawId
                    && x.Msisdn == msisdn
                    && x.WinnerId != request.WinnerId);
            if (userAlreadyHasPrize)
            {
                return Json(new { errorCode = 409, message = "This user already has a prize in the selected month." });
            }

            var now = DateTime.Now;
            GameMonthlyWinner? winner = null;
            if (request.WinnerId > 0)
            {
                winner = await _db.GameMonthlyWinners.FirstOrDefaultAsync(x => x.WinnerId == request.WinnerId);
                if (winner == null)
                {
                    return Json(new { errorCode = 404, message = "Winner not found." });
                }
            }

            if (winner == null)
            {
                winner = new GameMonthlyWinner
                {
                    WinnerId = await NextDecimalIdAsync("SEQ_GAME_MONTHLY_WINNER"),
                    DrawId = draw.DrawId,
                    MonthKey = monthKey,
                    CreatedTime = now
                };
                _db.GameMonthlyWinners.Add(winner);
            }

            winner.DrawId = draw.DrawId;
            winner.MonthKey = monthKey;
            winner.Msisdn = msisdn;
            winner.TicketId = ticket?.TicketId;
            winner.PrizeCode = prize.PrizeCode;
            winner.PrizeName = prize.PrizeName;
            winner.PrizeValue = prize.PrizeValue;
            winner.PrizeUnit = prize.PrizeUnit;
            winner.Status = NormalizeStatus(request.Status);
            winner.UpdatedTime = now;

            ApplyStatusTime(winner, now);
            await _db.SaveChangesAsync();

            return Json(new { errorCode = 200, message = "Success" });
        }
        catch (Exception ex)
        {
            return Json(new { errorCode = 500, message = ex.Message });
        }
    }

    [HttpPost]
    public async Task<JsonResult> UpdateStatus(decimal winnerId, byte status)
    {
        if (!CheckLogin()) return Json(new { errorCode = 401, message = "Unauthorized" });
        if (!HasMenuPermission(CmsSecurityHelper.MonthlyWinnersMenuKey)) return NoPermissionJson();

        var winner = await _db.GameMonthlyWinners.FirstOrDefaultAsync(x => x.WinnerId == winnerId);
        if (winner == null)
        {
            return Json(new { errorCode = 404, message = "Winner not found." });
        }

        var now = DateTime.Now;
        winner.Status = NormalizeStatus(status);
        winner.UpdatedTime = now;
        ApplyStatusTime(winner, now);
        await _db.SaveChangesAsync();

        return Json(new { errorCode = 200, message = "Success" });
    }

    private async Task<GameMonthlyDraw> EnsureDrawAsync(int monthKey)
    {
        var draw = await _db.GameMonthlyDraws.FirstOrDefaultAsync(x => x.MonthKey == monthKey);
        if (draw != null) return draw;

        var now = DateTime.Now;
        draw = new GameMonthlyDraw
        {
            DrawId = await NextDecimalIdAsync("SEQ_GAME_MONTHLY_DRAW"),
            MonthKey = monthKey,
            Status = 0,
            TotalEligible = 0,
            TotalTicket = 0,
            CreatedTime = now
        };
        _db.GameMonthlyDraws.Add(draw);
        await _db.SaveChangesAsync();
        return draw;
    }

    private async Task<GameMonthlyDrawTicket?> LoadTicketAsync(int monthKey, decimal? ticketId)
    {
        if (ticketId == null || ticketId <= 0) return null;
        return await _db.GameMonthlyDrawTickets
            .AsNoTracking()
            .FirstOrDefaultAsync(x => x.TicketId == ticketId.Value && x.MonthKey == monthKey);
    }

    private static void ApplyStatusTime(GameMonthlyWinner winner, DateTime now)
    {
        if (winner.Status == 1 && winner.AnnounceTime == null)
        {
            winner.AnnounceTime = now;
        }

        if (winner.Status == 2)
        {
            winner.AnnounceTime ??= now;
            winner.PaidTime ??= now;
        }

        winner.ResponseCode = winner.Status switch
        {
            0 => "DRAFT",
            1 => "ANNOUNCED",
            2 => "PAID_MANUAL",
            3 => "CANCELLED",
            _ => winner.ResponseCode
        };
        winner.ResponseMessage = winner.Status switch
        {
            0 => "waiting cms confirm",
            1 => "announced by cms",
            2 => "cash prize paid manually",
            3 => "cancelled by cms",
            _ => winner.ResponseMessage
        };
    }

    private async Task<decimal> NextDecimalIdAsync(string sequence)
    {
        return await Database.DbLogic.GenIdAsync(_db, sequence)
            ?? throw new InvalidOperationException($"Oracle sequence '{sequence}' did not return an ID.");
    }

    private static int NormalizeMonthKey(int? monthKey)
    {
        if (monthKey is >= 200001 and <= 299912)
        {
            var month = monthKey.Value % 100;
            if (month is >= 1 and <= 12) return monthKey.Value;
        }

        var previousMonth = DateTime.Now.Date.AddMonths(-1);
        return previousMonth.Year * 100 + previousMonth.Month;
    }

    private static string NormalizeMsisdn(string? msisdn)
    {
        var value = (msisdn ?? string.Empty).Trim();
        if (value.StartsWith("0", StringComparison.Ordinal) && value.Length > 1)
        {
            value = "95" + value[1..];
        }

        return value;
    }

    private static byte NormalizeStatus(byte status)
    {
        return status is <= 3 ? status : (byte)0;
    }

    private static string ToMonthText(int monthKey)
    {
        var year = monthKey / 100;
        var month = monthKey % 100;
        return new DateTime(year, month, 1).ToString("MMMM yyyy", System.Globalization.CultureInfo.InvariantCulture);
    }

    private static string ToStatusText(byte status)
    {
        return status switch
        {
            0 => "Draft",
            1 => "Announced",
            2 => "Paid",
            3 => "Cancelled",
            _ => "Unknown"
        };
    }
}