using System.Text.Json;
using System.Text.RegularExpressions;
using Asentrafin.Api.Contracts.Plaid;
using Asentrafin.Api.Data;
using Asentrafin.Api.Entities;
using Microsoft.EntityFrameworkCore;

namespace Asentrafin.Api.Services.Plaid;

public class PlaidIngestionService(AppDbContext dbContext, IPlaidClient plaidClient) : IPlaidIngestionService
{
    private static readonly Regex SlugSanitizer = new("[^a-z0-9]+", RegexOptions.Compiled);

    public async Task<Guid> CreateItemAsync(CreatePlaidItemRequest request, CancellationToken cancellationToken)
    {
        if (string.IsNullOrWhiteSpace(request.AccessToken))
        {
            throw new ArgumentException("AccessToken is required.", nameof(request));
        }

        var existingItem = request.PlaidItemId is null
            ? null
            : await dbContext.PlaidItems
                .SingleOrDefaultAsync(item => item.PlaidItemId == request.PlaidItemId, cancellationToken);

        if (existingItem is not null)
        {
            existingItem.AccessToken = request.AccessToken;
            existingItem.InstitutionId = request.InstitutionId;
            existingItem.InstitutionName = request.InstitutionName;
            existingItem.UpdatedAt = DateTimeOffset.UtcNow;
            await dbContext.SaveChangesAsync(cancellationToken);
            return existingItem.Id;
        }

        var item = new PlaidItem
        {
            AccessToken = request.AccessToken,
            PlaidItemId = request.PlaidItemId,
            InstitutionId = request.InstitutionId,
            InstitutionName = request.InstitutionName
        };

        dbContext.PlaidItems.Add(item);
        await dbContext.SaveChangesAsync(cancellationToken);
        return item.Id;
    }

    public async Task<IngestionSummary> IngestAccountsAsync(Guid itemId, CancellationToken cancellationToken)
    {
        var item = await GetItemAsync(itemId, cancellationToken);
        using var response = await plaidClient.AccountsGetAsync(item.AccessToken, cancellationToken);

        var accountsUpserted = await UpsertAccountsAsync(item, response.RootElement, cancellationToken);
        item.LastAccountsSyncedAt = DateTimeOffset.UtcNow;
        item.UpdatedAt = DateTimeOffset.UtcNow;

        await dbContext.SaveChangesAsync(cancellationToken);

        return new IngestionSummary(item.Id, accountsUpserted, 0, 0, 0, 0, 0);
    }

    public async Task<IngestionSummary> IngestTransactionsAsync(Guid itemId, CancellationToken cancellationToken)
    {
        var item = await GetItemAsync(itemId, cancellationToken);
        var cursor = item.TransactionsCursor;
        var accountsUpserted = 0;
        var transactionsUpserted = 0;
        var transactionsRemoved = 0;
        var hasMore = true;

        while (hasMore)
        {
            using var response = await plaidClient.TransactionsSyncAsync(item.AccessToken, cursor, cancellationToken);
            var root = response.RootElement;

            accountsUpserted += await UpsertAccountsAsync(item, root, cancellationToken);
            transactionsUpserted += await UpsertTransactionsAsync(item, root, "added", cancellationToken);
            transactionsUpserted += await UpsertTransactionsAsync(item, root, "modified", cancellationToken);
            transactionsRemoved += await MarkTransactionsRemovedAsync(root, cancellationToken);

            cursor = root.String("next_cursor") ?? cursor;
            hasMore = root.Property("has_more") is { ValueKind: JsonValueKind.True };
        }

        item.TransactionsCursor = cursor;
        item.LastTransactionsSyncedAt = DateTimeOffset.UtcNow;
        item.UpdatedAt = DateTimeOffset.UtcNow;

        await dbContext.SaveChangesAsync(cancellationToken);

        return new IngestionSummary(item.Id, accountsUpserted, transactionsUpserted, transactionsRemoved, 0, 0, 0);
    }

    public async Task<IngestionSummary> IngestInvestmentsAsync(
        Guid itemId,
        DateOnly? startDate,
        DateOnly? endDate,
        CancellationToken cancellationToken)
    {
        var item = await GetItemAsync(itemId, cancellationToken);
        using var holdingsResponse = await plaidClient.InvestmentsHoldingsGetAsync(item.AccessToken, cancellationToken);

        var accountsUpserted = await UpsertAccountsAsync(item, holdingsResponse.RootElement, cancellationToken);
        var securitiesUpserted = await UpsertSecuritiesAsync(holdingsResponse.RootElement, cancellationToken);
        var holdingsUpserted = await UpsertHoldingsAsync(item, holdingsResponse.RootElement, cancellationToken);

        var rangeEnd = endDate ?? DateOnly.FromDateTime(DateTime.UtcNow);
        var rangeStart = startDate ?? rangeEnd.AddYears(-2);

        var investmentTransactionsUpserted = 0;
        var count = 500;
        var offset = 0;
        var totalInvestmentTransactions = 0;

        do
        {
            using var transactionsResponse = await plaidClient.InvestmentsTransactionsGetAsync(
                item.AccessToken,
                rangeStart,
                rangeEnd,
                count,
                offset,
                cancellationToken);

            accountsUpserted += await UpsertAccountsAsync(item, transactionsResponse.RootElement, cancellationToken);
            securitiesUpserted += await UpsertSecuritiesAsync(transactionsResponse.RootElement, cancellationToken);
            investmentTransactionsUpserted += await UpsertInvestmentTransactionsAsync(
                item,
                transactionsResponse.RootElement,
                cancellationToken);

            totalInvestmentTransactions = transactionsResponse.RootElement.Int("total_investment_transactions") ?? 0;
            offset += count;
        }
        while (offset < totalInvestmentTransactions);

        item.LastInvestmentsSyncedAt = DateTimeOffset.UtcNow;
        item.UpdatedAt = DateTimeOffset.UtcNow;

        await dbContext.SaveChangesAsync(cancellationToken);

        return new IngestionSummary(
            item.Id,
            accountsUpserted,
            0,
            0,
            securitiesUpserted,
            holdingsUpserted,
            investmentTransactionsUpserted);
    }

    public async Task<IngestionSummary> IngestAllAsync(
        Guid itemId,
        DateOnly? investmentStartDate,
        DateOnly? investmentEndDate,
        CancellationToken cancellationToken)
    {
        var accounts = await IngestAccountsAsync(itemId, cancellationToken);
        var transactions = await IngestTransactionsAsync(itemId, cancellationToken);
        var investments = await IngestInvestmentsAsync(itemId, investmentStartDate, investmentEndDate, cancellationToken);

        return new IngestionSummary(
            itemId,
            accounts.AccountsUpserted + transactions.AccountsUpserted + investments.AccountsUpserted,
            transactions.TransactionsAddedOrModified,
            transactions.TransactionsRemoved,
            investments.SecuritiesUpserted,
            investments.HoldingsUpserted,
            investments.InvestmentTransactionsUpserted);
    }

    private async Task<PlaidItem> GetItemAsync(Guid itemId, CancellationToken cancellationToken)
    {
        return await dbContext.PlaidItems.SingleOrDefaultAsync(item => item.Id == itemId, cancellationToken)
            ?? throw new KeyNotFoundException($"Plaid item '{itemId}' was not found.");
    }

    private async Task<int> UpsertAccountsAsync(
        PlaidItem item,
        JsonElement root,
        CancellationToken cancellationToken)
    {
        var accounts = root.Property("accounts");
        if (accounts is not { ValueKind: JsonValueKind.Array })
        {
            return 0;
        }

        var existingAccounts = await dbContext.PlaidAccounts
            .Where(account => account.PlaidItemId == item.Id)
            .ToDictionaryAsync(account => account.PlaidAccountId, cancellationToken);

        var upserted = 0;
        foreach (var accountJson in accounts.Value.EnumerateArray())
        {
            var plaidAccountId = accountJson.String("account_id");
            if (string.IsNullOrWhiteSpace(plaidAccountId))
            {
                continue;
            }

            if (!existingAccounts.TryGetValue(plaidAccountId, out var account))
            {
                account = new PlaidAccount
                {
                    PlaidItemId = item.Id,
                    PlaidAccountId = plaidAccountId
                };
                dbContext.PlaidAccounts.Add(account);
                existingAccounts[plaidAccountId] = account;
            }

            var balances = accountJson.Property("balances");
            account.Name = accountJson.String("name") ?? account.Name;
            account.OfficialName = accountJson.String("official_name");
            account.Mask = accountJson.String("mask");
            account.Type = accountJson.String("type") ?? account.Type;
            account.Subtype = accountJson.String("subtype");
            account.CurrentBalance = balances?.Decimal("current");
            account.AvailableBalance = balances?.Decimal("available");
            account.Limit = balances?.Decimal("limit");
            account.IsoCurrencyCode = balances?.String("iso_currency_code");
            account.UnofficialCurrencyCode = balances?.String("unofficial_currency_code");
            account.BalanceLastUpdatedAt = balances?.DateTimeOffset("last_updated_datetime");
            account.RawJson = accountJson.GetRawText();
            account.UpdatedAt = DateTimeOffset.UtcNow;
            upserted++;
        }

        return upserted;
    }

    private async Task<int> UpsertTransactionsAsync(
        PlaidItem item,
        JsonElement root,
        string arrayName,
        CancellationToken cancellationToken)
    {
        var transactions = root.Property(arrayName);
        if (transactions is not { ValueKind: JsonValueKind.Array })
        {
            return 0;
        }

        var accountLookup = await dbContext.PlaidAccounts
            .Where(account => account.PlaidItemId == item.Id)
            .ToDictionaryAsync(account => account.PlaidAccountId, account => account.Id, cancellationToken);

        var ids = transactions.Value.EnumerateArray()
            .Select(transaction => transaction.String("transaction_id"))
            .Where(id => !string.IsNullOrWhiteSpace(id))
            .Select(id => id!)
            .ToArray();

        var existingTransactions = await dbContext.PlaidTransactions
            .Where(transaction => ids.Contains(transaction.PlaidTransactionId))
            .ToDictionaryAsync(transaction => transaction.PlaidTransactionId, cancellationToken);

        var upserted = 0;
        foreach (var transactionJson in transactions.Value.EnumerateArray())
        {
            var plaidTransactionId = transactionJson.String("transaction_id");
            var accountPlaidId = transactionJson.String("account_id");
            var date = transactionJson.Date("date");

            if (string.IsNullOrWhiteSpace(plaidTransactionId) ||
                string.IsNullOrWhiteSpace(accountPlaidId) ||
                date is null)
            {
                continue;
            }

            if (!existingTransactions.TryGetValue(plaidTransactionId, out var transaction))
            {
                transaction = new PlaidTransaction
                {
                    PlaidItemId = item.Id,
                    PlaidTransactionId = plaidTransactionId
                };
                dbContext.PlaidTransactions.Add(transaction);
                existingTransactions[plaidTransactionId] = transaction;
            }

            var personalFinanceCategory = transactionJson.Property("personal_finance_category");
            transaction.AccountPlaidId = accountPlaidId;
            transaction.PlaidAccountId = accountLookup.TryGetValue(accountPlaidId, out var accountId)
                ? accountId
                : null;
            transaction.Date = date.Value;
            transaction.AuthorizedDate = transactionJson.Date("authorized_date");
            transaction.Name = transactionJson.String("name") ?? transaction.Name;
            transaction.MerchantName = transactionJson.String("merchant_name");
            transaction.Amount = transactionJson.Decimal("amount") ?? transaction.Amount;
            transaction.Pending = transactionJson.Bool("pending");
            transaction.IsoCurrencyCode = transactionJson.String("iso_currency_code");
            transaction.UnofficialCurrencyCode = transactionJson.String("unofficial_currency_code");
            transaction.Categories = transactionJson.StringArray("category");
            transaction.PersonalFinanceCategoryPrimary = personalFinanceCategory?.String("primary");
            transaction.PersonalFinanceCategoryDetailed = personalFinanceCategory?.String("detailed");
            transaction.RawJson = transactionJson.GetRawText();
            transaction.RemovedAt = null;
            transaction.UpdatedAt = DateTimeOffset.UtcNow;
            await UpsertPlaidClassificationAsync(transaction, personalFinanceCategory, cancellationToken);
            upserted++;
        }

        return upserted;
    }

    private async Task<int> MarkTransactionsRemovedAsync(JsonElement root, CancellationToken cancellationToken)
    {
        var removed = root.Property("removed");
        if (removed is not { ValueKind: JsonValueKind.Array })
        {
            return 0;
        }

        var removedIds = removed.Value.EnumerateArray()
            .Select(transaction => transaction.String("transaction_id"))
            .Where(id => !string.IsNullOrWhiteSpace(id))
            .Select(id => id!)
            .ToArray();

        var transactions = await dbContext.PlaidTransactions
            .Where(transaction => removedIds.Contains(transaction.PlaidTransactionId))
            .ToListAsync(cancellationToken);

        foreach (var transaction in transactions)
        {
            transaction.RemovedAt = DateTimeOffset.UtcNow;
            transaction.UpdatedAt = DateTimeOffset.UtcNow;
        }

        return transactions.Count;
    }

    private async Task<int> UpsertSecuritiesAsync(JsonElement root, CancellationToken cancellationToken)
    {
        var securities = root.Property("securities");
        if (securities is not { ValueKind: JsonValueKind.Array })
        {
            return 0;
        }

        var ids = securities.Value.EnumerateArray()
            .Select(security => security.String("security_id"))
            .Where(id => !string.IsNullOrWhiteSpace(id))
            .Select(id => id!)
            .ToArray();

        var existingSecurities = await dbContext.PlaidSecurities
            .Where(security => ids.Contains(security.PlaidSecurityId))
            .ToDictionaryAsync(security => security.PlaidSecurityId, cancellationToken);

        var upserted = 0;
        foreach (var securityJson in securities.Value.EnumerateArray())
        {
            var plaidSecurityId = securityJson.String("security_id");
            if (string.IsNullOrWhiteSpace(plaidSecurityId))
            {
                continue;
            }

            if (!existingSecurities.TryGetValue(plaidSecurityId, out var security))
            {
                security = new PlaidSecurity
                {
                    PlaidSecurityId = plaidSecurityId
                };
                dbContext.PlaidSecurities.Add(security);
                existingSecurities[plaidSecurityId] = security;
            }

            security.Cusip = securityJson.String("cusip");
            security.Isin = securityJson.String("isin");
            security.Sedol = securityJson.String("sedol");
            security.TickerSymbol = securityJson.String("ticker_symbol");
            security.Name = securityJson.String("name");
            security.Type = securityJson.String("type");
            security.ClosePrice = securityJson.Decimal("close_price");
            security.ClosePriceAsOf = securityJson.Date("close_price_as_of");
            security.IsoCurrencyCode = securityJson.String("iso_currency_code");
            security.UnofficialCurrencyCode = securityJson.String("unofficial_currency_code");
            security.RawJson = securityJson.GetRawText();
            security.UpdatedAt = DateTimeOffset.UtcNow;
            upserted++;
        }

        return upserted;
    }

    private async Task<int> UpsertHoldingsAsync(
        PlaidItem item,
        JsonElement root,
        CancellationToken cancellationToken)
    {
        var holdings = root.Property("holdings");
        if (holdings is not { ValueKind: JsonValueKind.Array })
        {
            return 0;
        }

        var accountLookup = await dbContext.PlaidAccounts
            .Where(account => account.PlaidItemId == item.Id)
            .ToDictionaryAsync(account => account.PlaidAccountId, account => account.Id, cancellationToken);
        var securityLookup = await dbContext.PlaidSecurities
            .ToDictionaryAsync(security => security.PlaidSecurityId, security => security.Id, cancellationToken);
        var existingHoldings = await dbContext.PlaidHoldings
            .Where(holding => holding.PlaidItemId == item.Id)
            .ToListAsync(cancellationToken);

        var upserted = 0;
        foreach (var holdingJson in holdings.Value.EnumerateArray())
        {
            var accountPlaidId = holdingJson.String("account_id");
            var securityPlaidId = holdingJson.String("security_id");

            if (string.IsNullOrWhiteSpace(accountPlaidId) ||
                string.IsNullOrWhiteSpace(securityPlaidId) ||
                !accountLookup.TryGetValue(accountPlaidId, out var accountId) ||
                !securityLookup.TryGetValue(securityPlaidId, out var securityId))
            {
                continue;
            }

            var holding = existingHoldings.SingleOrDefault(existing =>
                existing.PlaidAccountId == accountId && existing.PlaidSecurityId == securityId);

            if (holding is null)
            {
                holding = new PlaidHolding
                {
                    PlaidItemId = item.Id,
                    PlaidAccountId = accountId,
                    PlaidSecurityId = securityId
                };
                dbContext.PlaidHoldings.Add(holding);
                existingHoldings.Add(holding);
            }

            holding.Quantity = holdingJson.Decimal("quantity") ?? holding.Quantity;
            holding.InstitutionPrice = holdingJson.Decimal("institution_price");
            holding.InstitutionValue = holdingJson.Decimal("institution_value");
            holding.InstitutionPriceAsOf = holdingJson.Date("institution_price_as_of");
            holding.CostBasis = holdingJson.Decimal("cost_basis");
            holding.IsoCurrencyCode = holdingJson.String("iso_currency_code");
            holding.UnofficialCurrencyCode = holdingJson.String("unofficial_currency_code");
            holding.RawJson = holdingJson.GetRawText();
            holding.UpdatedAt = DateTimeOffset.UtcNow;
            upserted++;
        }

        return upserted;
    }

    private async Task<int> UpsertInvestmentTransactionsAsync(
        PlaidItem item,
        JsonElement root,
        CancellationToken cancellationToken)
    {
        var transactions = root.Property("investment_transactions");
        if (transactions is not { ValueKind: JsonValueKind.Array })
        {
            return 0;
        }

        var accountLookup = await dbContext.PlaidAccounts
            .Where(account => account.PlaidItemId == item.Id)
            .ToDictionaryAsync(account => account.PlaidAccountId, account => account.Id, cancellationToken);
        var securityLookup = await dbContext.PlaidSecurities
            .ToDictionaryAsync(security => security.PlaidSecurityId, security => security.Id, cancellationToken);
        var ids = transactions.Value.EnumerateArray()
            .Select(transaction => transaction.String("investment_transaction_id"))
            .Where(id => !string.IsNullOrWhiteSpace(id))
            .Select(id => id!)
            .ToArray();
        var existingTransactions = await dbContext.PlaidInvestmentTransactions
            .Where(transaction => ids.Contains(transaction.PlaidInvestmentTransactionId))
            .ToDictionaryAsync(transaction => transaction.PlaidInvestmentTransactionId, cancellationToken);

        var upserted = 0;
        foreach (var transactionJson in transactions.Value.EnumerateArray())
        {
            var investmentTransactionId = transactionJson.String("investment_transaction_id");
            var accountPlaidId = transactionJson.String("account_id");
            var date = transactionJson.Date("date");

            if (string.IsNullOrWhiteSpace(investmentTransactionId) ||
                string.IsNullOrWhiteSpace(accountPlaidId) ||
                date is null)
            {
                continue;
            }

            if (!existingTransactions.TryGetValue(investmentTransactionId, out var transaction))
            {
                transaction = new PlaidInvestmentTransaction
                {
                    PlaidItemId = item.Id,
                    PlaidInvestmentTransactionId = investmentTransactionId
                };
                dbContext.PlaidInvestmentTransactions.Add(transaction);
                existingTransactions[investmentTransactionId] = transaction;
            }

            var securityPlaidId = transactionJson.String("security_id");
            transaction.AccountPlaidId = accountPlaidId;
            transaction.PlaidAccountId = accountLookup.TryGetValue(accountPlaidId, out var accountId)
                ? accountId
                : null;
            transaction.SecurityPlaidId = securityPlaidId;
            transaction.PlaidSecurityId = securityPlaidId is not null &&
                securityLookup.TryGetValue(securityPlaidId, out var securityId)
                    ? securityId
                    : null;
            transaction.Date = date.Value;
            transaction.Name = transactionJson.String("name") ?? transaction.Name;
            transaction.Quantity = transactionJson.Decimal("quantity") ?? transaction.Quantity;
            transaction.Amount = transactionJson.Decimal("amount") ?? transaction.Amount;
            transaction.Price = transactionJson.Decimal("price");
            transaction.Fees = transactionJson.Decimal("fees");
            transaction.Type = transactionJson.String("type");
            transaction.Subtype = transactionJson.String("subtype");
            transaction.IsoCurrencyCode = transactionJson.String("iso_currency_code");
            transaction.UnofficialCurrencyCode = transactionJson.String("unofficial_currency_code");
            transaction.RawJson = transactionJson.GetRawText();
            transaction.UpdatedAt = DateTimeOffset.UtcNow;
            upserted++;
        }

        return upserted;
    }

    private async Task UpsertPlaidClassificationAsync(
        PlaidTransaction transaction,
        JsonElement? personalFinanceCategory,
        CancellationToken cancellationToken)
    {
        var primary = personalFinanceCategory?.String("primary") ?? "UNCATEGORIZED";
        var detailed = personalFinanceCategory?.String("detailed") ?? primary;
        var category = await GetOrCreateCategoryAsync(primary, detailed, cancellationToken);

        var classification = await dbContext.TransactionClassifications
            .SingleOrDefaultAsync(
                existing => existing.PlaidTransactionId == transaction.Id,
                cancellationToken);

        if (classification is not null && classification.Source != "plaid")
        {
            return;
        }

        if (classification is null)
        {
            classification = new TransactionClassification
            {
                PlaidTransaction = transaction,
                Source = "plaid"
            };
            dbContext.TransactionClassifications.Add(classification);
        }

        classification.Category = category;
        classification.Source = "plaid";
        classification.UpdatedAt = DateTimeOffset.UtcNow;
    }

    private async Task<TransactionCategory> GetOrCreateCategoryAsync(
        string primary,
        string detailed,
        CancellationToken cancellationToken)
    {
        var slug = BuildCategorySlug(detailed);
        var category = await dbContext.TransactionCategories
            .SingleOrDefaultAsync(existing => existing.Slug == slug, cancellationToken);

        if (category is not null)
        {
            category.PlaidPrimary ??= primary;
            category.PlaidDetailed ??= detailed;
            category.UpdatedAt = DateTimeOffset.UtcNow;
            return category;
        }

        category = new TransactionCategory
        {
            Name = BuildCategoryName(detailed),
            Slug = slug,
            PlaidPrimary = primary,
            PlaidDetailed = detailed,
            IsSystem = true
        };

        dbContext.TransactionCategories.Add(category);
        return category;
    }

    private static string BuildCategorySlug(string value)
    {
        var normalized = value.Trim().ToLowerInvariant().Replace("&", "and");
        normalized = SlugSanitizer.Replace(normalized, "-").Trim('-');

        return string.IsNullOrWhiteSpace(normalized)
            ? "uncategorized"
            : normalized;
    }

    private static string BuildCategoryName(string value)
    {
        return string.Join(
            " ",
            value.ToLowerInvariant()
                .Split(['_', '-'], StringSplitOptions.RemoveEmptyEntries | StringSplitOptions.TrimEntries)
                .Select(word => char.ToUpperInvariant(word[0]) + word[1..]));
    }
}