using System.Reflection;
using System.Text.Json;
using Fast.Shared.Database;
using Fast.Shared.Database.Models;
using Microsoft.EntityFrameworkCore;

namespace Fast.Shared.LocalMode;

/// Seeds the in-memory database with fake data loaded from JSON files.
public static class LocalDataSeeder
{
    private static readonly JsonSerializerOptions JsonOptions = new()
    {
        PropertyNameCaseInsensitive = true,
        ReadCommentHandling = JsonCommentHandling.Skip,
        AllowTrailingCommas = true
    };

    public static async Task SeedAsync(MyDbContext db)
    {
        // Seed in dependency order (reference data first)

        // Core reference/lookup tables
        await SeedSimpleTableAsync<ActualType>(db, "actual-types.json", db.ActualTypes);
        await SeedSimpleTableAsync<BuySellType>(db, "buy-sell-types.json", db.BuySellTypes);
        await SeedSimpleTableAsync<CallPutType>(db, "call-put-types.json", db.CallPutTypes);
        await SeedSimpleTableAsync<CollateralType>(db, "collateral-types.json", db.CollateralTypes);
        await SeedSimpleTableAsync<CurrencyType>(db, "currency-types.json", db.CurrencyTypes);
        await SeedSimpleTableAsync<DeliveryMode>(db, "delivery-modes.json", db.DeliveryModes);
        await SeedSimpleTableAsync<FuelCalculationType>(db, "fuel-calculation-types.json", db.FuelCalculationTypes);
        await SeedSimpleTableAsync<ImbalanceNomType>(db, "imbalance-nom-types.json", db.ImbalanceNomTypes);
        await SeedSimpleTableAsync<LeaseType>(db, "lease-types.json", db.LeaseTypes);
        await SeedSimpleTableAsync<MarketType>(db, "market-types.json", db.MarketTypes);
        await SeedSimpleTableAsync<MeterType>(db, "meter-types.json", db.MeterTypes);
        await SeedSimpleTableAsync<PaymentDateOption>(db, "payment-date-options.json", db.PaymentDateOptions);
        await SeedSimpleTableAsync<PaymentDateType>(db, "payment-date-types.json", db.PaymentDateTypes);
        await SeedSimpleTableAsync<PayoutType>(db, "payout-types.json", db.PayoutTypes);
        await SeedSimpleTableAsync<PhysicalDealType>(db, "physical-deal-types.json", db.PhysicalDealTypes);
        await SeedSimpleTableAsync<PipelineContractType>(db, "pipeline-contract-types.json", db.PipelineContractTypes);
        await SeedSimpleTableAsync<PipelineTariffType>(db, "pipeline-tariff-types.json", db.PipelineTariffTypes);
        await SeedSimpleTableAsync<ProducerFeeType>(db, "producer-fee-types.json", db.ProducerFeeTypes);
        await SeedSimpleTableAsync<SalesTaxType>(db, "sales-tax-types.json", db.SalesTaxTypes);
        await SeedSimpleTableAsync<SettleType>(db, "settle-types.json", db.SettleTypes);
        await SeedSimpleTableAsync<ContractPaymentType>(db, "contract-payment-types.json", db.ContractPaymentTypes);
        await SeedSimpleTableAsync<Region>(db, "regions.json", db.Regions);
        await SeedSimpleTableAsync<Industry>(db, "industries.json", db.Industries);
        await SeedSimpleTableAsync<BusinessType>(db, "business-types.json", db.BusinessTypes);
        await SeedSimpleTableAsync<BusinessSubType>(db, "business-sub-types.json", db.BusinessSubTypes);
        await SeedSimpleTableAsync<BusinessRelationship>(db, "business-relationships.json", db.BusinessRelationships);
        await SeedSimpleTableAsync<Exchange>(db, "exchanges.json", db.Exchanges);
        await SeedSimpleTableAsync<UnitOfMeasure>(db, "unit-of-measures.json", db.UnitOfMeasures);
        await SeedSimpleTableAsync<UnitOfMeasureConversion>(db, "unit-of-measure-conversions.json", db.UnitOfMeasureConversions);
        await SeedSimpleTableAsync<UnitPriceCombination>(db, "unit-price-combinations.json", db.UnitPriceCombinations);

        // Market index reference tables
        await SeedSimpleTableAsync<MarketIndexType>(db, "market-index-types.json", db.MarketIndexTypes);
        await SeedSimpleTableAsync<MarketIndexClassificationType>(db, "market-index-classification-types.json", db.MarketIndexClassificationTypes);
        await SeedSimpleTableAsync<MarketIndexAliasType>(db, "market-index-alias-types.json", db.MarketIndexAliasTypes);
        await SeedSimpleTableAsync<MarketIndexPublication>(db, "market-index-publications.json", db.MarketIndexPublications);
        await SeedSimpleTableAsync<MarketIndex>(db, "market-indices.json", db.MarketIndices);
        await SeedSimpleTableAsync<MarketIndexAlias>(db, "market-index-aliases.json", db.MarketIndexAliases);
        await SeedSimpleTableAsync<MarketPrice>(db, "market-prices.json", db.MarketPrices);

        // Core user and security
        await SeedAppUsersAsync(db);
        await SeedSecurityActionsAsync(db);
        await SeedSecurityGroupsAsync(db);
        await SeedSecurityUserGroupsAsync(db);
        await SeedSecurityGroupPermissionsAsync(db);
        await SeedScreensAsync(db);
        await SeedFavoritesAsync(db);
        await SeedSimpleTableAsync<UserScreenSort>(db, "user-screen-sorts.json", db.UserScreenSorts);

        // ASP.NET Identity
        await SeedSimpleTableAsync<AspRole>(db, "asp-roles.json", db.AspRoles);
        await SeedSimpleTableAsync<AspRoleClaim>(db, "asp-role-claims.json", db.AspRoleClaims);
        await SeedSimpleTableAsync<AspUserClaim>(db, "asp-user-claims.json", db.AspUserClaims);
        await SeedSimpleTableAsync<AspUserLogin>(db, "asp-user-logins.json", db.AspUserLogins);
        await SeedSimpleTableAsync<AspUserToken>(db, "asp-user-tokens.json", db.AspUserTokens);

        // Geography
        await SeedCountriesAsync(db);
        await SeedTerritoriesAsync(db);
        await SeedSimpleTableAsync<County>(db, "counties.json", db.Counties);
        await SeedSimpleTableAsync<Location>(db, "locations.json", db.Locations);

        // Calendar
        await SeedSimpleTableAsync<Calendar>(db, "calendars.json", db.Calendars);
        await SeedSimpleTableAsync<Holiday>(db, "holidays.json", db.Holidays);
        await SeedSimpleTableAsync<HolidaySchedule>(db, "holiday-schedules.json", db.HolidaySchedules);

        // Products
        await SeedProductCategoriesAsync(db);
        await SeedProductsAsync(db);
        await SeedSimpleTableAsync<ProductExpiration>(db, "product-expirations.json", db.ProductExpirations);
        await SeedSimpleTableAsync<Benchmark>(db, "benchmarks.json", db.Benchmarks);

        // Invoice types
        await SeedInvoiceTypesAsync(db);

        // Counterparties and related
        await SeedCounterpartiesAsync(db);
        await SeedSimpleTableAsync<CounterpartyDun>(db, "counterparty-duns.json", db.CounterpartyDuns);
        await SeedSimpleTableAsync<CounterpartyParent>(db, "counterparty-parents.json", db.CounterpartyParents);
        await SeedSimpleTableAsync<CounterpartyProduct>(db, "counterparty-products.json", db.CounterpartyProducts);
        await SeedSimpleTableAsync<CounterpartyRelationship>(db, "counterparty-relationships.json", db.CounterpartyRelationships);
        await SeedSimpleTableAsync<DunsNumber>(db, "duns-numbers.json", db.DunsNumbers);

        // Banking
        await SeedSimpleTableAsync<BankRouting>(db, "bank-routings.json", db.BankRoutings);
        await SeedSimpleTableAsync<PaymentInstruction>(db, "payment-instructions.json", db.PaymentInstructions);
        await SeedSimpleTableAsync<PaymentInstructionDoc>(db, "payment-instruction-docs.json", db.PaymentInstructionDocs);

        // Books, Portfolios, Strategies
        await SeedSimpleTableAsync<Book>(db, "books.json", db.Books);
        await SeedSimpleTableAsync<Portfolio>(db, "portfolios.json", db.Portfolios);
        await SeedSimpleTableAsync<Strategy>(db, "strategies.json", db.Strategies);
        await SeedSimpleTableAsync<BookToTrader>(db, "book-to-traders.json", db.BookToTraders);

        // Brokers
        await SeedSimpleTableAsync<Broker>(db, "brokers.json", db.Brokers);
        await SeedSimpleTableAsync<BrokerAccount>(db, "broker-accounts.json", db.BrokerAccounts);
        await SeedSimpleTableAsync<BrokerMemo>(db, "broker-memos.json", db.BrokerMemos);

        // Contacts
        await SeedContactTypesAsync(db);
        await SeedContactsAsync(db);
        await SeedContactCounterpartiesAsync(db);
        await SeedContactToContactTypesAsync(db);

        // Contracts
        await SeedContractStatusesAsync(db);
        await SeedContractsAsync(db);
        await SeedContractAccountingsAsync(db);
        await SeedContractNaesbsAsync(db);
        await SeedSimpleTableAsync<ContractContact>(db, "contract-contacts.json", db.ContractContacts);
        await SeedSimpleTableAsync<ContractCrude>(db, "contract-crudes.json", db.ContractCrudes);
        await SeedSimpleTableAsync<ContractCrudePoint>(db, "contract-crude-points.json", db.ContractCrudePoints);
        await SeedSimpleTableAsync<ContractDoc>(db, "contract-docs.json", db.ContractDocs);
        await SeedSimpleTableAsync<ContractExhibitB>(db, "contract-exhibit-bs.json", db.ContractExhibitBs);
        await SeedSimpleTableAsync<ContractExhibitBSelection>(db, "contract-exhibit-b-selections.json", db.ContractExhibitBSelections);
        await SeedSimpleTableAsync<ContractExhibitC>(db, "contract-exhibit-cs.json", db.ContractExhibitCs);
        await SeedSimpleTableAsync<ContractOther>(db, "contract-others.json", db.ContractOthers);

        // Credit limits
        await SeedSimpleTableAsync<CreditLimit>(db, "credit-limits.json", db.CreditLimits);
        await SeedSimpleTableAsync<CreditLimitApproval>(db, "credit-limit-approvals.json", db.CreditLimitApprovals);
        await SeedSimpleTableAsync<CreditLimitCollateral>(db, "credit-limit-collaterals.json", db.CreditLimitCollaterals);
        await SeedSimpleTableAsync<CreditLimitCollateralBeneficiary>(db, "credit-limit-collateral-beneficiaries.json", db.CreditLimitCollateralBeneficiaries);
        await SeedSimpleTableAsync<CreditLimitCollateralDoc>(db, "credit-limit-collateral-docs.json", db.CreditLimitCollateralDocs);
        await SeedSimpleTableAsync<CreditLimitCollateralProvider>(db, "credit-limit-collateral-providers.json", db.CreditLimitCollateralProviders);
        await SeedSimpleTableAsync<CreditLimitCounterparty>(db, "credit-limit-counterparties.json", db.CreditLimitCounterparties);

        // Pipelines and related
        await SeedPipelineTypesAsync(db);
        await SeedPipelinesAsync(db);
        await SeedZonesAsync(db);
        await SeedPointsAsync(db);
        await SeedMetersAsync(db);
        await SeedMeterProductsAsync(db);
        await SeedSimpleTableAsync<MeterProductDeliveryPoint>(db, "meter-product-delivery-points.json", db.MeterProductDeliveryPoints);
        await SeedSimpleTableAsync<MeterProductSourcePoint>(db, "meter-product-source-points.json", db.MeterProductSourcePoints);
        await SeedSimpleTableAsync<MeterPtr>(db, "meter-ptrs.json", db.MeterPtrs);
        await SeedSimpleTableAsync<MeterPtrExcluded>(db, "meter-ptr-excludeds.json", db.MeterPtrExcludeds);
        await SeedPipelineContractsAsync(db);
        await SeedSimpleTableAsync<PipelineContractDefault>(db, "pipeline-contract-defaults.json", db.PipelineContractDefaults);
        await SeedSimpleTableAsync<PipelineRateSchedule>(db, "pipeline-rate-schedules.json", db.PipelineRateSchedules);
        await SeedSimpleTableAsync<PipelineTariff>(db, "pipeline-tariffs.json", db.PipelineTariffs);
        await SeedSimpleTableAsync<PipelineTariffDatum>(db, "pipeline-tariff-data.json", db.PipelineTariffData);
        await SeedSimpleTableAsync<PipelineRateDiscounted>(db, "pipeline-rate-discounteds.json", db.PipelineRateDiscounteds);
        await SeedSimpleTableAsync<PipelineRateDiscountedCounterparty>(db, "pipeline-rate-discounted-counterparties.json", db.PipelineRateDiscountedCounterparties);
        await SeedSimpleTableAsync<PipelineRateDiscountedFromTo>(db, "pipeline-rate-discounted-from-tos.json", db.PipelineRateDiscountedFromTos);
        await SeedSimpleTableAsync<PipeContractDeliveryMeter>(db, "pipe-contract-delivery-meters.json", db.PipeContractDeliveryMeters);
        await SeedSimpleTableAsync<PipeContractDeliveryZone>(db, "pipe-contract-delivery-zones.json", db.PipeContractDeliveryZones);
        await SeedSimpleTableAsync<PipeContractDoc>(db, "pipe-contract-docs.json", db.PipeContractDocs);
        await SeedSimpleTableAsync<PipeContractReceiptMeter>(db, "pipe-contract-receipt-meters.json", db.PipeContractReceiptMeters);
        await SeedSimpleTableAsync<PipeContractReceiptZone>(db, "pipe-contract-receipt-zones.json", db.PipeContractReceiptZones);
        await SeedSimpleTableAsync<PipeDiscountDoc>(db, "pipe-discount-docs.json", db.PipeDiscountDocs);
        await SeedSimpleTableAsync<PipeRateApplicationRule>(db, "pipe-rate-application-rules.json", db.PipeRateApplicationRules);
        await SeedSimpleTableAsync<PipeRateDoc>(db, "pipe-rate-docs.json", db.PipeRateDocs);
        await SeedSimpleTableAsync<PointProductGradeToIndex>(db, "point-product-grade-to-indices.json", db.PointProductGradeToIndices);
        await SeedSimpleTableAsync<PointSourceDelivery>(db, "point-source-deliveries.json", db.PointSourceDeliveries);

        // Plants
        await SeedSimpleTableAsync<Plant>(db, "plants.json", db.Plants);
        await SeedSimpleTableAsync<PlantNote>(db, "plant-notes.json", db.PlantNotes);
        await SeedSimpleTableAsync<PlantStatement>(db, "plant-statements.json", db.PlantStatements);
        await SeedSimpleTableAsync<PlantStatementCombinedMeter>(db, "plant-statement-combined-meters.json", db.PlantStatementCombinedMeters);
        await SeedSimpleTableAsync<PlantStatementDescriptor>(db, "plant-statement-descriptors.json", db.PlantStatementDescriptors);
        await SeedSimpleTableAsync<PlantStatementOption>(db, "plant-statement-options.json", db.PlantStatementOptions);
        await SeedSimpleTableAsync<PlantStatementPrice>(db, "plant-statement-prices.json", db.PlantStatementPrices);

        // Leases and Storage
        await SeedSimpleTableAsync<Lease>(db, "leases.json", db.Leases);
        await SeedSimpleTableAsync<StorageFacility>(db, "storage-facilities.json", db.StorageFacilities);

        // Deals
        await SeedDealStatusesAsync(db);
        await SeedHypotheticalTypesAsync(db);
        await SeedVolumeTypesAsync(db);
        await SeedDealPurposeTypesAsync(db);
        await SeedDealsAsync(db);
        await SeedSimpleTableAsync<DealConfirmation>(db, "deal-confirmations.json", db.DealConfirmations);
        await SeedSimpleTableAsync<DealFilter>(db, "deal-filters.json", db.DealFilters);
        await SeedSimpleTableAsync<DealFilterParameter>(db, "deal-filter-parameters.json", db.DealFilterParameters);
        await SeedSimpleTableAsync<DealVolume>(db, "deal-volumes.json", db.DealVolumes);

        // Gas supply chain
        await SeedGasSuppliesAsync(db);
        await SeedGasMarketsAsync(db);
        await SeedGasMarketSuppliesAsync(db);
        await SeedSimpleTableAsync<GasActual>(db, "gas-actuals.json", db.GasActuals);
        await SeedSimpleTableAsync<GasPath>(db, "gas-paths.json", db.GasPaths);
        await SeedSimpleTableAsync<GasPathRoute>(db, "gas-path-routes.json", db.GasPathRoutes);

        // Crude supply chain
        await SeedCrudeSuppliesAsync(db);
        await SeedCrudeMarketsAsync(db);
        await SeedCrudeMarketSuppliesAsync(db);
        await SeedSimpleTableAsync<CrudeActual>(db, "crude-actuals.json", db.CrudeActuals);
        await SeedSimpleTableAsync<CrudePath>(db, "crude-paths.json", db.CrudePaths);
        await SeedSimpleTableAsync<CrudePathRoute>(db, "crude-path-routes.json", db.CrudePathRoutes);

        // Invoices
        await SeedInvoicesCrudeAsync(db);
        await SeedInvoicesGasAsync(db);

        // Taxes
        await SeedSimpleTableAsync<SalesTax>(db, "sales-taxes.json", db.SalesTaxes);
        await SeedSimpleTableAsync<SalesTaxDetail>(db, "sales-tax-details.json", db.SalesTaxDetails);
        await SeedSimpleTableAsync<SalesTaxDoc>(db, "sales-tax-docs.json", db.SalesTaxDocs);
        await SeedSimpleTableAsync<SalesTaxExemption>(db, "sales-tax-exemptions.json", db.SalesTaxExemptions);
        await SeedSimpleTableAsync<SalesTaxExemptionDoc>(db, "sales-tax-exemption-docs.json", db.SalesTaxExemptionDocs);
        await SeedSimpleTableAsync<SalesTaxRate>(db, "sales-tax-rates.json", db.SalesTaxRates);
        await SeedSimpleTableAsync<SeveranceTax>(db, "severance-taxes.json", db.SeveranceTaxes);
        await SeedSimpleTableAsync<StateIncomeExemption>(db, "state-income-exemptions.json", db.StateIncomeExemptions);
        await SeedSimpleTableAsync<StateIncomeExemptionDoc>(db, "state-income-exemption-docs.json", db.StateIncomeExemptionDocs);

        // Quality and Producer fees
        await SeedSimpleTableAsync<QualityBankAdjustment>(db, "quality-bank-adjustments.json", db.QualityBankAdjustments);
        await SeedSimpleTableAsync<QualityBankAdjustmentDetail>(db, "quality-bank-adjustment-details.json", db.QualityBankAdjustmentDetails);
        await SeedSimpleTableAsync<ProducerFee>(db, "producer-fees.json", db.ProducerFees);
        await SeedSimpleTableAsync<PublishedToInternalIndex>(db, "published-to-internal-indices.json", db.PublishedToInternalIndices);

        // Reports
        await SeedSimpleTableAsync<Report>(db, "reports.json", db.Reports);
        await SeedSimpleTableAsync<ReportDataSource>(db, "report-data-sources.json", db.ReportDataSources);
        await SeedSimpleTableAsync<ReportFilter>(db, "report-filters.json", db.ReportFilters);
        await SeedSimpleTableAsync<ReportFilterParameter>(db, "report-filter-parameters.json", db.ReportFilterParameters);

        // SOS (Schedule of Schedules)
        await SeedSimpleTableAsync<SosCrudePipeSetting>(db, "sos-crude-pipe-settings.json", db.SosCrudePipeSettings);
        await SeedSimpleTableAsync<SosCrudeSetting>(db, "sos-crude-settings.json", db.SosCrudeSettings);
        await SeedSimpleTableAsync<SosGasPipeSetting>(db, "sos-gas-pipe-settings.json", db.SosGasPipeSettings);
        await SeedSimpleTableAsync<SosGasSetting>(db, "sos-gas-settings.json", db.SosGasSettings);
        await SeedSimpleTableAsync<SosHiddenDeal>(db, "sos-hidden-deals.json", db.SosHiddenDeals);
        await SeedSimpleTableAsync<SosSnapshot>(db, "sos-snapshots.json", db.SosSnapshots);

        // Transfers and Tickets
        await SeedSimpleTableAsync<TransferDeal>(db, "transfer-deals.json", db.TransferDeals);
        await SeedSimpleTableAsync<TransferMeter>(db, "transfer-meters.json", db.TransferMeters);
        await SeedSimpleTableAsync<Ticket>(db, "tickets.json", db.Tickets);

        // W9 Forms
        await SeedSimpleTableAsync<W9Form>(db, "w9-forms.json", db.W9Forms);
        await SeedSimpleTableAsync<W9FormDoc>(db, "w9-form-docs.json", db.W9FormDocs);

        // Transaction types
        await SeedSimpleTableAsync<TransactionType>(db, "transaction-types.json", db.TransactionTypes);

        // Misc
        await SeedSimpleTableAsync<ActualizationFilter>(db, "actualization-filters.json", db.ActualizationFilters);
        await SeedSimpleTableAsync<AppLog>(db, "app-logs.json", db.AppLogs);
        await SeedSimpleTableAsync<BusinessCentralHistory>(db, "business-central-histories.json", db.BusinessCentralHistories);
        await SeedSimpleTableAsync<EmailSetting>(db, "email-settings.json", db.EmailSettings);
        await SeedSimpleTableAsync<ForceMajeure>(db, "force-majeures.json", db.ForceMajeures);
        await SeedSimpleTableAsync<Notification>(db, "notifications.json", db.Notifications);
        await SeedSimpleTableAsync<Password>(db, "passwords.json", db.Passwords);

        // OpenIddict (empty for local mode)
        await SeedSimpleTableAsync<OpenIddictApplication>(db, "openiddict-applications.json", db.OpenIddictApplications);
        await SeedSimpleTableAsync<OpenIddictAuthorization>(db, "openiddict-authorizations.json", db.OpenIddictAuthorizations);
        await SeedSimpleTableAsync<OpenIddictScope>(db, "openiddict-scopes.json", db.OpenIddictScopes);
        await SeedSimpleTableAsync<OpenIddictToken>(db, "openiddict-tokens.json", db.OpenIddictTokens);

        // App settings (last before computed data)
        await SeedAppSettingsAsync(db);

        // Seed computed/calendar data
        await SeedBusinessCalendarsAsync(db);
        await SeedViewDataAsync(db);

        await db.SaveChangesAsync();
        Console.WriteLine("Local mode: Database seeded successfully.");
    }

    /// <summary>
    /// Generic method to seed a simple table from a JSON file.
    /// </summary>
    private static async Task SeedSimpleTableAsync<T>(MyDbContext db, string fileName, DbSet<T> dbSet) where T : class
    {
        try
        {
            var items = await LoadJsonAsync<List<T>>(fileName);
            if (items != null && items.Count > 0)
            {
                dbSet.AddRange(items);
                await db.SaveChangesAsync();
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine($"Error seeding {fileName}: {ex.Message}");
            throw;
        }
    }

    private static async Task SeedAppUsersAsync(MyDbContext db)
    {
        var users = await LoadJsonAsync<List<AppUser>>("app-users.json");
        if (users != null && users.Count > 0)
        {
            db.AppUsers.AddRange(users);
            await db.SaveChangesAsync();
        }
    }

    private static async Task SeedSecurityActionsAsync(MyDbContext db)
    {
        var actions = await LoadJsonAsync<List<SecurityAction>>("security-actions.json");
        if (actions != null && actions.Count > 0)
        {
            db.SecurityActions.AddRange(actions);
            await db.SaveChangesAsync();
        }
    }

    private static async Task SeedSecurityGroupsAsync(MyDbContext db)
    {
        var groups = await LoadJsonAsync<List<SecurityGroup>>("security-groups.json");
        if (groups != null && groups.Count > 0)
        {
            db.SecurityGroups.AddRange(groups);
            await db.SaveChangesAsync();
        }
    }

    private static async Task SeedSecurityUserGroupsAsync(MyDbContext db)
    {
        var userGroups = await LoadJsonAsync<List<SecurityUserGroup>>("security-user-groups.json");
        if (userGroups != null && userGroups.Count > 0)
        {
            db.SecurityUserGroups.AddRange(userGroups);
            await db.SaveChangesAsync();
        }
    }

    private static async Task SeedSecurityGroupPermissionsAsync(MyDbContext db)
    {
        var permissions = await LoadJsonAsync<List<SecurityGroupPermission>>("security-group-permissions.json");
        if (permissions != null && permissions.Count > 0)
        {
            db.SecurityGroupPermissions.AddRange(permissions);
            await db.SaveChangesAsync();
        }
    }

    private static async Task SeedScreensAsync(MyDbContext db)
    {
        var screens = await LoadJsonAsync<List<Screen>>("screens.json");
        if (screens != null && screens.Count > 0)
        {
            db.Screens.AddRange(screens);
            await db.SaveChangesAsync();
        }
    }

    private static async Task SeedFavoritesAsync(MyDbContext db)
    {
        var favorites = await LoadJsonAsync<List<Favorite>>("favorites.json");
        if (favorites != null && favorites.Count > 0)
        {
            db.Favorites.AddRange(favorites);
            await db.SaveChangesAsync();
        }
    }

    private static async Task SeedInvoiceTypesAsync(MyDbContext db)
    {
        var types = await LoadJsonAsync<List<InvoiceType>>("invoice-types.json");
        if (types != null && types.Count > 0)
        {
            db.InvoiceTypes.AddRange(types);
            await db.SaveChangesAsync();
        }
    }

    private static async Task SeedCounterpartiesAsync(MyDbContext db)
    {
        var counterparties = await LoadJsonAsync<List<Counterparty>>("counterparties.json");
        if (counterparties != null && counterparties.Count > 0)
        {
            db.Counterparties.AddRange(counterparties);
            await db.SaveChangesAsync();
        }
    }

    private static async Task SeedProductCategoriesAsync(MyDbContext db)
    {
        var categories = await LoadJsonAsync<List<ProductCategory>>("product-categories.json");
        if (categories != null && categories.Count > 0)
        {
            db.ProductCategories.AddRange(categories);
            await db.SaveChangesAsync();
        }
    }

    private static async Task SeedProductsAsync(MyDbContext db)
    {
        var products = await LoadJsonAsync<List<Product>>("products.json");
        if (products != null && products.Count > 0)
        {
            db.Products.AddRange(products);
            await db.SaveChangesAsync();
        }
    }

    private static async Task SeedContractStatusesAsync(MyDbContext db)
    {
        var statuses = await LoadJsonAsync<List<ContractStatus>>("contract-statuses.json");
        if (statuses != null && statuses.Count > 0)
        {
            db.ContractStatuses.AddRange(statuses);
            await db.SaveChangesAsync();
        }
    }

    private static async Task SeedContractsAsync(MyDbContext db)
    {
        var contracts = await LoadJsonAsync<List<Contract>>("contracts.json");
        if (contracts != null && contracts.Count > 0)
        {
            db.Contracts.AddRange(contracts);
            await db.SaveChangesAsync();
        }
    }

    private static async Task SeedInvoicesCrudeAsync(MyDbContext db)
    {
        var invoices = await LoadJsonAsync<List<InvoiceCrude>>("invoices-crude.json");
        if (invoices != null && invoices.Count > 0)
        {
            for (int i = 0; i < invoices.Count; i++)
            {
                var (Month, DueDate, InvoiceDate, CreatedTime, ModifiedTime) = GenerateInvoiceDates(i, invoices.Count);
                invoices[i].Month = Month;
                invoices[i].DueDate = DueDate;
                invoices[i].InvoiceDate = InvoiceDate;
                invoices[i].CreatedTime = CreatedTime;
                invoices[i].ModifiedTime = ModifiedTime;
            }
            db.InvoiceCrudes.AddRange(invoices);
            await db.SaveChangesAsync();
        }

        var lines = await LoadJsonAsync<List<InvoiceCrudeLine>>("invoice-crude-lines.json");
        if (lines != null && lines.Count > 0)
        {
            db.InvoiceCrudeLines.AddRange(lines);
            await db.SaveChangesAsync();
        }
    }

    private static async Task SeedInvoicesGasAsync(MyDbContext db)
    {
        var invoices = await LoadJsonAsync<List<InvoiceGa>>("invoices-gas.json");
        if (invoices != null && invoices.Count > 0)
        {
            for (int i = 0; i < invoices.Count; i++)
            {
                var (Month, DueDate, InvoiceDate, CreatedTime, ModifiedTime) = GenerateInvoiceDates(i, invoices.Count);
                invoices[i].Month = Month;
                invoices[i].DueDate = DueDate;
                invoices[i].InvoiceDate = InvoiceDate;
                invoices[i].CreatedTime = CreatedTime;
                invoices[i].ModifiedTime = ModifiedTime;
            }
            db.InvoiceGas.AddRange(invoices);
            await db.SaveChangesAsync();
        }

        var lines = await LoadJsonAsync<List<InvoiceGasLine>>("invoice-gas-lines.json");
        if (lines != null && lines.Count > 0)
        {
            db.InvoiceGasLines.AddRange(lines);
            await db.SaveChangesAsync();
        }
    }

    private static (DateOnly Month, DateOnly DueDate, DateOnly InvoiceDate, DateTime CreatedTime, DateTime ModifiedTime) GenerateInvoiceDates(int index, int totalCount)
    {
        var today = DateTime.Today;
        var currentMonth = new DateOnly(today.Year, today.Month, 1);
        var dueDate = new DateOnly(currentMonth.Year, currentMonth.Month, 25);

        // Spread invoice dates across the month
        var maxDay = Math.Min(today.Day, DateTime.DaysInMonth(today.Year, today.Month));
        var invoiceDay = Math.Max(1, (index * maxDay / Math.Max(totalCount, 1)) + 1);
        invoiceDay = Math.Min(invoiceDay, maxDay);
        var invoiceDate = new DateOnly(today.Year, today.Month, invoiceDay);

        // CreatedTime: on or before InvoiceDate, with a time component
        var createdDay = Math.Max(1, invoiceDay - (index % 3)); // 0-2 days before invoice date
        var createdTime = new DateTime(today.Year, today.Month, createdDay, 9 + (index % 8), (index * 7) % 60, 0);

        // ModifiedTime: after CreatedTime, still within this month
        var modifiedDay = Math.Min(maxDay, createdDay + 1 + (index % 2));
        var modifiedTime = new DateTime(today.Year, today.Month, modifiedDay, 10 + (index % 7), (index * 11) % 60, 0);

        // Ensure ModifiedTime is after CreatedTime
        if (modifiedTime <= createdTime)
            modifiedTime = createdTime.AddHours(1 + (index % 3));

        return (currentMonth, dueDate, invoiceDate, createdTime, modifiedTime);
    }

    private static async Task SeedAppSettingsAsync(MyDbContext db)
    {
        var settings = await LoadJsonAsync<List<AppSetting>>("app-settings.json");
        if (settings != null && settings.Count > 0)
        {
            db.AppSettings.AddRange(settings);
            await db.SaveChangesAsync();
        }
    }

    private static async Task SeedCountriesAsync(MyDbContext db)
    {
        var items = await LoadJsonAsync<List<Country>>("countries.json");
        if (items != null && items.Count > 0)
        {
            db.Countries.AddRange(items);
            await db.SaveChangesAsync();
        }
    }

    private static async Task SeedTerritoriesAsync(MyDbContext db)
    {
        var items = await LoadJsonAsync<List<Territory>>("territories.json");
        if (items != null && items.Count > 0)
        {
            db.Territories.AddRange(items);
            await db.SaveChangesAsync();
        }
    }

    private static async Task SeedContractAccountingsAsync(MyDbContext db)
    {
        var items = await LoadJsonAsync<List<ContractAccounting>>("contract-accountings.json");
        if (items != null && items.Count > 0)
        {
            db.ContractAccountings.AddRange(items);
            await db.SaveChangesAsync();
        }
    }

    private static async Task SeedContractNaesbsAsync(MyDbContext db)
    {
        var items = await LoadJsonAsync<List<ContractNaesb>>("contract-naesbs.json");
        if (items != null && items.Count > 0)
        {
            db.ContractNaesbs.AddRange(items);
            await db.SaveChangesAsync();
        }
    }

    private static async Task SeedContactTypesAsync(MyDbContext db)
    {
        var items = await LoadJsonAsync<List<ContactType>>("contact-types.json");
        if (items != null && items.Count > 0)
        {
            db.ContactTypes.AddRange(items);
            await db.SaveChangesAsync();
        }
    }

    private static async Task SeedContactsAsync(MyDbContext db)
    {
        var items = await LoadJsonAsync<List<Contact>>("contacts.json");
        if (items != null && items.Count > 0)
        {
            db.Contacts.AddRange(items);
            await db.SaveChangesAsync();
        }
    }

    private static async Task SeedContactCounterpartiesAsync(MyDbContext db)
    {
        var items = await LoadJsonAsync<List<ContactCounterparty>>("contact-counterparties.json");
        if (items != null && items.Count > 0)
        {
            db.ContactCounterparties.AddRange(items);
            await db.SaveChangesAsync();
        }
    }

    private static async Task SeedContactToContactTypesAsync(MyDbContext db)
    {
        var items = await LoadJsonAsync<List<ContactToContactType>>("contact-to-contact-types.json");
        if (items != null && items.Count > 0)
        {
            db.ContactToContactTypes.AddRange(items);
            await db.SaveChangesAsync();
        }
    }

    private static async Task SeedPipelineTypesAsync(MyDbContext db)
    {
        var items = await LoadJsonAsync<List<PipelineType>>("pipeline-types.json");
        if (items != null && items.Count > 0)
        {
            db.PipelineTypes.AddRange(items);
            await db.SaveChangesAsync();
        }
    }

    private static async Task SeedPipelinesAsync(MyDbContext db)
    {
        var items = await LoadJsonAsync<List<Pipeline>>("pipelines.json");
        if (items != null && items.Count > 0)
        {
            db.Pipelines.AddRange(items);
            await db.SaveChangesAsync();
        }
    }

    private static async Task SeedZonesAsync(MyDbContext db)
    {
        var items = await LoadJsonAsync<List<Zone>>("zones.json");
        if (items != null && items.Count > 0)
        {
            db.Zones.AddRange(items);
            await db.SaveChangesAsync();
        }
    }

    private static async Task SeedPointsAsync(MyDbContext db)
    {
        var items = await LoadJsonAsync<List<Point>>("points.json");
        if (items != null && items.Count > 0)
        {
            db.Points.AddRange(items);
            await db.SaveChangesAsync();
        }
    }

    private static async Task SeedMetersAsync(MyDbContext db)
    {
        var items = await LoadJsonAsync<List<Meter>>("meters.json");
        if (items != null && items.Count > 0)
        {
            db.Meters.AddRange(items);
            await db.SaveChangesAsync();
        }
    }

    private static async Task SeedMeterProductsAsync(MyDbContext db)
    {
        var items = await LoadJsonAsync<List<MeterProduct>>("meter-products.json");
        if (items != null && items.Count > 0)
        {
            db.MeterProducts.AddRange(items);
            await db.SaveChangesAsync();
        }
    }

    private static async Task SeedDealStatusesAsync(MyDbContext db)
    {
        var items = await LoadJsonAsync<List<DealStatus>>("deal-statuses.json");
        if (items != null && items.Count > 0)
        {
            db.DealStatuses.AddRange(items);
            await db.SaveChangesAsync();
        }
    }

    private static async Task SeedHypotheticalTypesAsync(MyDbContext db)
    {
        var items = await LoadJsonAsync<List<HypotheticalType>>("hypothetical-types.json");
        if (items != null && items.Count > 0)
        {
            db.HypotheticalTypes.AddRange(items);
            await db.SaveChangesAsync();
        }
    }

    private static async Task SeedVolumeTypesAsync(MyDbContext db)
    {
        var items = await LoadJsonAsync<List<VolumeType>>("volume-types.json");
        if (items != null && items.Count > 0)
        {
            db.VolumeTypes.AddRange(items);
            await db.SaveChangesAsync();
        }
    }

    private static async Task SeedDealPurposeTypesAsync(MyDbContext db)
    {
        var items = await LoadJsonAsync<List<DealPurposeType>>("deal-purpose-types.json");
        if (items != null && items.Count > 0)
        {
            db.DealPurposeTypes.AddRange(items);
            await db.SaveChangesAsync();
        }
    }

    private static async Task SeedPipelineContractsAsync(MyDbContext db)
    {
        var items = await LoadJsonAsync<List<PipelineContract>>("pipeline-contracts.json");
        if (items != null && items.Count > 0)
        {
            db.PipelineContracts.AddRange(items);
            await db.SaveChangesAsync();
        }
    }

    private static async Task SeedDealsAsync(MyDbContext db)
    {
        var items = await LoadJsonAsync<List<Deal>>("deals.json");
        if (items != null && items.Count > 0)
        {
            db.Deals.AddRange(items);
            await db.SaveChangesAsync();
        }
    }

    private static async Task SeedGasSuppliesAsync(MyDbContext db)
    {
        var items = await LoadJsonAsync<List<GasSupply>>("gas-supplies.json");
        if (items != null && items.Count > 0)
        {
            // Update dates to current month
            foreach (var item in items)
            {
                item.Date = GetCurrentMonthDate(item.Date.Day);
            }
            db.GasSupplies.AddRange(items);
            await db.SaveChangesAsync();
        }
    }

    private static async Task SeedGasMarketsAsync(MyDbContext db)
    {
        var items = await LoadJsonAsync<List<GasMarket>>("gas-markets.json");
        if (items != null && items.Count > 0)
        {
            // Update dates to current month
            foreach (var item in items)
            {
                item.Date = GetCurrentMonthDate(item.Date.Day);
                item.Month = GetCurrentMonthStart();
            }
            db.GasMarkets.AddRange(items);
            await db.SaveChangesAsync();
        }
    }

    private static async Task SeedGasMarketSuppliesAsync(MyDbContext db)
    {
        var items = await LoadJsonAsync<List<GasMarketSupply>>("gas-market-supplies.json");
        if (items != null && items.Count > 0)
        {
            // Update dates to current month
            foreach (var item in items)
            {
                item.Date = GetCurrentMonthDate(item.Date.Day);
            }
            db.GasMarketSupplies.AddRange(items);
            await db.SaveChangesAsync();
        }
    }

    private static async Task SeedCrudeSuppliesAsync(MyDbContext db)
    {
        var items = await LoadJsonAsync<List<CrudeSupply>>("crude-supplies.json");
        if (items != null && items.Count > 0)
        {
            // Update dates to current month
            foreach (var item in items)
            {
                item.Date = GetCurrentMonthDate(item.Date.Day);
            }
            db.CrudeSupplies.AddRange(items);
            await db.SaveChangesAsync();
        }
    }

    private static async Task SeedCrudeMarketsAsync(MyDbContext db)
    {
        var items = await LoadJsonAsync<List<CrudeMarket>>("crude-markets.json");
        if (items != null && items.Count > 0)
        {
            // Update dates to current month
            foreach (var item in items)
            {
                item.Date = GetCurrentMonthDate(item.Date.Day);
                item.Month = GetCurrentMonthStart();
            }
            db.CrudeMarkets.AddRange(items);
            await db.SaveChangesAsync();
        }
    }

    private static async Task SeedCrudeMarketSuppliesAsync(MyDbContext db)
    {
        var items = await LoadJsonAsync<List<CrudeMarketSupply>>("crude-market-supplies.json");
        if (items != null && items.Count > 0)
        {
            // Update dates to current month
            foreach (var item in items)
            {
                item.Date = GetCurrentMonthDate(item.Date.Day);
            }
            db.CrudeMarketSupplies.AddRange(items);
            await db.SaveChangesAsync();
        }
    }

    private static DateOnly GetCurrentMonthStart()
    {
        var today = DateTime.Today;
        return new DateOnly(today.Year, today.Month, 1);
    }

    private static DateOnly GetCurrentMonthDate(int day)
    {
        var today = DateTime.Today;
        var maxDay = DateTime.DaysInMonth(today.Year, today.Month);
        var safeDay = Math.Min(day, maxDay);
        return new DateOnly(today.Year, today.Month, safeDay);
    }

    /// Seeds the business calendar with weekdays (Mon-Fri) for current and next month.
    private static Task SeedBusinessCalendarsAsync(MyDbContext db)
    {
        var today = DateTime.Today;
        var startDate = new DateOnly(today.Year, today.Month, 1);
        var endDate = startDate.AddMonths(2).AddDays(-1); // End of next month

        var businessDays = new List<VwBusinessCalendar>();

        for (var date = startDate; date <= endDate; date = date.AddDays(1))
        {
            // Only include weekdays (Monday = 1 through Friday = 5)
            if (date.DayOfWeek != DayOfWeek.Saturday && date.DayOfWeek != DayOfWeek.Sunday)
            {
                var firstOfMonth = new DateOnly(date.Year, date.Month, 1);
                var lastOfMonth = new DateOnly(date.Year, date.Month, DateTime.DaysInMonth(date.Year, date.Month));

                businessDays.Add(new VwBusinessCalendar
                {
                    Date = date,
                    FirstOfMonth = firstOfMonth,
                    LastOfMonth = lastOfMonth
                });
            }
        }

        db.VwBusinessCalendars.AddRange(businessDays);
        return Task.CompletedTask;
    }

    /// Seeds the view data by computing it from the base tables.
    /// In local mode, views are mapped as tables so we can insert directly.
    private static async Task SeedViewDataAsync(MyDbContext db)
    {
        // Compute VwInvoiceCrude from base tables
        var crudeViewData = await (
            from inv in db.InvoiceCrudes.AsNoTracking()
            join ie in db.Counterparties.AsNoTracking() on inv.InternalEntityId equals ie.Id
            join cp in db.Counterparties.AsNoTracking() on inv.CounterpartyId equals cp.Id
            join it in db.InvoiceTypes.AsNoTracking() on inv.InvoiceTypeId equals it.Id
            join emailUser in db.AppUsers.AsNoTracking() on inv.EmailedBy equals emailUser.Id into emailUsers
            from emailUser in emailUsers.DefaultIfEmpty()
            select new VwInvoiceCrude
            {
                Id = inv.Id,
                InvoiceNum = inv.InvoiceNum,
                Month = inv.Month,
                InternalEntityId = inv.InternalEntityId,
                InternalEntity = ie.Name,
                CounterpartyId = inv.CounterpartyId,
                Counterparty = cp.Name,
                InvoiceType = it.Name,
                IsApproved = db.InvoiceCrudeLines.Any(l => l.InvoiceId == inv.Id && l.ApprovedBy != null) ? "yes" : "no",
                CounterpartyEmailAddresses = inv.CounterpartyEmailAddresses,
                DueDate = inv.DueDate,
                FileNameOriginal = inv.FileNameOriginal,
                FileNameOnDisk = inv.FileNameOnDisk,
                EmailedByName = emailUser != null ? emailUser.DisplayName : null,
                EmailedTime = inv.EmailedTime,
                ContractId = null,
                Traders = null
            }
        ).ToListAsync();

        if (crudeViewData.Count > 0)
        {
            db.VwInvoiceCrudes.AddRange(crudeViewData);
        }

        // Compute VwInvoiceGa from base tables
        var gasViewData = await (
            from inv in db.InvoiceGas.AsNoTracking()
            join ie in db.Counterparties.AsNoTracking() on inv.InternalEntityId equals ie.Id
            join cp in db.Counterparties.AsNoTracking() on inv.CounterpartyId equals cp.Id
            join it in db.InvoiceTypes.AsNoTracking() on inv.InvoiceTypeId equals it.Id
            join emailUser in db.AppUsers.AsNoTracking() on inv.EmailedBy equals emailUser.Id into emailUsers
            from emailUser in emailUsers.DefaultIfEmpty()
            select new VwInvoiceGa
            {
                Id = inv.Id,
                InvoiceNum = inv.InvoiceNum,
                Month = inv.Month,
                InternalEntityId = inv.InternalEntityId,
                InternalEntity = ie.Name,
                CounterpartyId = inv.CounterpartyId,
                Counterparty = cp.Name,
                InvoiceType = it.Name,
                IsApproved = db.InvoiceGasLines.Any(l => l.InvoiceId == inv.Id && l.ApprovedBy != null) ? "yes" : "no",
                CounterpartyEmailAddresses = inv.CounterpartyEmailAddresses,
                DueDate = inv.DueDate,
                FileNameOriginal = inv.FileNameOriginal,
                FileNameOnDisk = inv.FileNameOnDisk,
                EmailedByName = emailUser != null ? emailUser.DisplayName : null,
                EmailedTime = inv.EmailedTime,
                ContractId = null,
                Traders = null,
                PaymentDateOptionId = null,
                PaymentDateTypeId = null
            }
        ).ToListAsync();

        if (gasViewData.Count > 0)
        {
            db.VwInvoiceGas.AddRange(gasViewData);
        }
    }

    private static async Task<T?> LoadJsonAsync<T>(string fileName)
    {
        var basePath = GetSeedDataPath();
        var filePath = Path.Combine(basePath, fileName);

        if (!File.Exists(filePath))
        {
            Console.WriteLine($"Local mode: Seed file not found: {filePath}");
            return default;
        }

        var json = await File.ReadAllTextAsync(filePath);
        return JsonSerializer.Deserialize<T>(json, JsonOptions);
    }

    private static string GetSeedDataPath()
    {
        // Try multiple locations for the seed data
        var assemblyLocation = Assembly.GetExecutingAssembly().Location;
        var assemblyDir = Path.GetDirectoryName(assemblyLocation) ?? "";

        // Check in the assembly directory first (for published apps)
        var seedPath = Path.Combine(assemblyDir, "LocalMode", "SeedData");
        if (Directory.Exists(seedPath))
            return seedPath;

        // Check in Fast.Shared project directory (for development)
        var currentDir = Directory.GetCurrentDirectory();
        var devPath = Path.Combine(currentDir, "..", "Fast.Shared", "LocalMode", "SeedData");
        if (Directory.Exists(devPath))
            return Path.GetFullPath(devPath);

        // Check directly under current directory
        var directPath = Path.Combine(currentDir, "LocalMode", "SeedData");
        if (Directory.Exists(directPath))
            return directPath;

        // Fallback to Fast.Shared relative path
        return Path.GetFullPath(Path.Combine(currentDir, "..", "Fast.Shared", "LocalMode", "SeedData"));
    }
}
