using ClosedXML.Excel;
using Microsoft.Extensions.Options;
using YD_TrainingCompetition_Api.Options;

namespace YD_TrainingCompetition_Api.Competition;

public sealed class RosterService : IRosterService
{
    private readonly RosterOptions _options;

    public RosterService(IOptions<RosterOptions> options)
    {
        _options = options.Value;
    }

    public IReadOnlyCollection<string> GetTemplates()
    {
        if (!Directory.Exists(_options.FolderPath))
        {
            return [];
        }

        return Directory.GetFiles(_options.FolderPath, "*.xlsx")
            .Select(Path.GetFileName)
            .OfType<string>()
            .OrderBy(x => x, StringComparer.OrdinalIgnoreCase)
            .ToArray();
    }

    public Task<IReadOnlyCollection<RosterEntry>> LoadAsync(string templateFileName, CancellationToken cancellationToken = default)
    {
        var fullPath = Path.Combine(_options.FolderPath, templateFileName);
        if (!File.Exists(fullPath))
        {
            throw new FileNotFoundException("Roster template not found", fullPath);
        }

        using var workbook = new XLWorkbook(fullPath);
        var ws = workbook.Worksheets.First();
        var used = ws.RangeUsed();
        if (used is null)
        {
            return Task.FromResult<IReadOnlyCollection<RosterEntry>>([]);
        }

        var headerRow = used.FirstRow().RowNumber();
        var lastRow = used.LastRow().RowNumber();
        var lastCol = used.LastColumn().ColumnNumber();

        var idCol = FindColumn(ws, headerRow, lastCol, "序号", "编号", "道次", "设备");
        var nameCol = FindColumn(ws, headerRow, lastCol, "姓名", "队员", "学生");
        var schoolCol = FindColumn(ws, headerRow, lastCol, "单位", "学校", "班级");

        if (idCol == 0) idCol = 1;
        if (nameCol == 0) nameCol = 2;
        if (schoolCol == 0) schoolCol = 3;

        var result = new List<RosterEntry>();
        for (var row = headerRow + 1; row <= lastRow; row++)
        {
            var idText = ws.Cell(row, idCol).GetString().Trim();
            var name = ws.Cell(row, nameCol).GetString().Trim();
            var school = ws.Cell(row, schoolCol).GetString().Trim();

            if (string.IsNullOrWhiteSpace(idText) || string.IsNullOrWhiteSpace(name))
            {
                continue;
            }

            if (!int.TryParse(new string(idText.Where(char.IsDigit).ToArray()), out var idx))
            {
                continue;
            }

            result.Add(new RosterEntry
            {
                DeviceId = idx.ToString(),
                Name = name,
                School = string.IsNullOrWhiteSpace(school) ? null : school,
                GroupName = Path.GetFileNameWithoutExtension(templateFileName)
            });
        }

        return Task.FromResult<IReadOnlyCollection<RosterEntry>>(result);
    }

    private static int FindColumn(IXLWorksheet ws, int headerRow, int lastCol, params string[] keywords)
    {
        for (var c = 1; c <= lastCol; c++)
        {
            var text = ws.Cell(headerRow, c).GetString().Trim();
            if (keywords.Any(k => text.Contains(k, StringComparison.OrdinalIgnoreCase)))
            {
                return c;
            }
        }

        return 0;
    }
}