namespace Fast.Logic;

class PriceImportHelper
{
    private readonly MyDbContext db;
    private readonly string sheetName;
    private readonly HashSet<string> skipColumns = new HashSet<string>();

    public PriceImportHelper(MyDbContext context, string sheetName)
    {
        db = context;
        this.sheetName = sheetName;
    }

    public void ValidateRequiredFields(List<string> headers, List<string> requiredHeaders)
    {
        HashSet<string> headersHash = headers.Select(x => x.Trim().ToUpper()).ToHashSet();

        foreach (string requiredHeader in requiredHeaders)
        {
            if (!headersHash.Contains(requiredHeader))
                throw new Exception("Required column header {" + requiredHeader + "} not found in sheet {" + sheetName + "}");
        }
    }

    readonly StringBuilder NotesBuilder = new StringBuilder();
    public string Notes
    {
        get
        {
            string notes = "";
            if (NotesBuilder.Length > 0)
                notes = NotesBuilder.ToString();
            return notes;
        }
    }

    public void SetItemValue(MarketPrice item, string headerName, object? fileValue, int row, int col, List<string> requiredHeaders)
    {
        try
        {
            headerName = headerName.Trim().ToUpper();

            if (!string.IsNullOrWhiteSpace(headerName))
            {
                if (requiredHeaders.Contains(headerName) && (fileValue == null || string.IsNullOrWhiteSpace(fileValue.ToString())))
                    throw new Exception("Missing required value");

                DateOnly noDate = new(1900, 1, 1);
                switch (headerName)
                {
                    case "PRICE DATE": item.PriceDate = GetDate(fileValue) ?? noDate; break;
                    case "INDEX": item.IndexId = GetIndexId(fileValue) ?? 0; break;
                    case "PRICE MONTH": item.ContractMonth = fileValue != null ? GetMonth(fileValue) ?? noDate : Util.Date.FirstDayOfMonth(item.PriceDate); break;
                    case "PRICE": item.Price = GetDouble(fileValue) ?? 0; break;
                    case "PUBLISH DATE": item.PublishDate = GetDate(fileValue) ?? noDate; break;
                    default:
                        if (skipColumns.Contains(headerName))
                        {
                            //do nothing, unrecognized header already added
                        }
                        else
                        {
                            skipColumns.Add(headerName);
                            var message = GetNote("Unrecognized column header skipped", headerName, fileValue, row, col);
                            NotesBuilder.Append(message);
                        }
                        break;
                }
            }
        }
        catch (Exception ex)
        {
            string originalMessage = ex.InnerException != null ? ex.InnerException.Message : ex.Message;
            var newMessage = GetNote(originalMessage, headerName, fileValue, row, col);
            throw new Exception(newMessage);
        }
    }

    string GetNote(string note, string headerName, object? fileValue, int row, int col)
    {
        var newNote = "";
        newNote += "Sheet {" + sheetName + "} \u2022 ";
        newNote += "Cell {" + OpenXmlHelper.GetColumnName(col) + row + "} \u2022 ";
        newNote += "Header {" + headerName + "} \u2022 ";
        newNote += "Value {" + Convert.ToString(fileValue) + "}\r\n";
        newNote += note + "\r\n";
        return newNote;
    }

    static DateOnly? GetDate(object? value)
    {
        DateOnly? newValue = null;
        if (value != null)
        {
            // OpenXML often reads Excel dates as raw doubles (OADate), e.g. "45258.5"
            // We need to try parsing as double first.
            string strVal = value.ToString()!;
            if (double.TryParse(strVal, out double oaDate))
            {
                newValue = DateOnly.FromDateTime(DateTime.FromOADate(oaDate));
            }
            else
            {
                // Fallback for string-formatted dates like "2023-01-01"
                newValue = DateOnly.FromDateTime(Convert.ToDateTime(value).Date);
            }
        }
        return newValue;
    }

    static DateOnly? GetMonth(object? value)
    {
        DateOnly? newValue = null;
        if (value != null)
        {
            DateTime dt;
            string strVal = value.ToString()!;

            if (double.TryParse(strVal, out double oaDate))
            {
                dt = DateTime.FromOADate(oaDate);
            }
            else
            {
                dt = Convert.ToDateTime(value).Date;
            }

            newValue = new DateOnly(dt.Year, dt.Month, 1);
        }
        return newValue;
    }

    static double? GetDouble(object? value)
    {
        double? newValue = null;
        if (value != null)
        {
            newValue = Convert.ToDouble(value);
        }
        return newValue;
    }

    Dictionary<string, int>? indexNames = null;
    Dictionary<string, int>? indexShortNames = null;
    Dictionary<string, int>? indexPlattsIds = null;
    Dictionary<string, int>? indexCmeNames = null;
    Dictionary<string, int>? indexArgusIds = null;
    int? GetIndexId(object? name)
    {
        int? newValue = null;

        if (name != null && !string.IsNullOrWhiteSpace(name.ToString()))
        {
            string nameTrimmed = name.ToString()?.Trim() ?? "";
            if (indexNames == null)
            {
                indexNames = (
                    from q in db.MarketIndices
                    select new { q.Id, q.Name }
                ).ToDictionary(x => x.Name, x => x.Id, StringComparer.OrdinalIgnoreCase);

                indexShortNames = (
                    from q in db.MarketIndexAliases
                    where q.IndexAlias != null &&
                    q.IndexAliasTypeId == (int)Enums.IndexAliasType.ShortName
                    select new { q.IndexId, q.IndexAlias }
                ).ToDictionary(x => x.IndexAlias, x => x.IndexId, StringComparer.OrdinalIgnoreCase);

                indexPlattsIds = (
                    from q in db.MarketIndexAliases
                    where q.IndexAlias != null &&
                    q.IndexAliasTypeId == (int)Enums.IndexAliasType.PlattsId
                    select new { q.IndexId, q.IndexAlias }
                ).ToDictionary(x => x.IndexAlias, x => x.IndexId, StringComparer.OrdinalIgnoreCase);

                indexCmeNames = (
                    from q in db.MarketIndexAliases
                    where q.IndexAlias != null &&
                    q.IndexAliasTypeId == (int)Enums.IndexAliasType.CmeName
                    select new { q.IndexId, q.IndexAlias }
                ).ToDictionary(x => x.IndexAlias, x => x.IndexId, StringComparer.OrdinalIgnoreCase);

                indexArgusIds = (
                    from q in db.MarketIndexAliases
                    where q.IndexAlias != null &&
                    q.IndexAliasTypeId == (int)Enums.IndexAliasType.ArgusId
                    select new { q.IndexId, q.IndexAlias }
                ).ToDictionary(x => x.IndexAlias, x => x.IndexId, StringComparer.OrdinalIgnoreCase);
            }

            if (indexNames.ContainsKey(nameTrimmed))
                newValue = indexNames[nameTrimmed];
            else if (indexShortNames != null && indexShortNames.ContainsKey(nameTrimmed))
                newValue = indexShortNames[nameTrimmed];
            else if (indexPlattsIds != null && indexPlattsIds.ContainsKey(nameTrimmed))
                newValue = indexPlattsIds[nameTrimmed];
            else if (indexCmeNames != null && indexCmeNames.ContainsKey(nameTrimmed))
                newValue = indexCmeNames[nameTrimmed];
            else if (indexArgusIds != null && indexArgusIds.ContainsKey(nameTrimmed))
                newValue = indexArgusIds[nameTrimmed];
            else
                throw new Exception("Unrecognized value");
        }

        return newValue;
    }
}
