import {
  markInvariantsSatisfied,
  type SupplierAccount,
  type SupplierAccountHeader,
  type SupplierAddressUsage,
  type SupplierBankAccountUsage,
} from "../domain/supplierAccount";
import type { Insertable, Selectable, Transaction, Updateable } from "../generated/kysely-tailordb";

export interface SupplierAccountRepository {
  findById(id: string, opts?: { forUpdate?: boolean }): Promise<SupplierAccount | null>;
  findByCompanyAndCode(
    companyId: string,
    code: string,
    opts?: { forUpdate?: boolean },
  ): Promise<SupplierAccount | null>;
  save(account: SupplierAccount): Promise<void>;
}

const SUPPLIER_ACCOUNT_HEADER_COLUMNS = [
  "partnerId",
  "companyId",
  "code",
  "name",
  "preferredCurrencyId",
  "defaultExpenseAccountId",
  "status",
] as const satisfies readonly Exclude<keyof SupplierAccountHeader, "customFields">[];
const SUPPLIER_ADDRESS_USAGE_COLUMNS = [
  "id",
  "addressId",
  "purpose",
  "isDefault",
] as const satisfies readonly Exclude<keyof SupplierAddressUsage, "customFields">[];
const SUPPLIER_BANK_ACCOUNT_USAGE_COLUMNS = [
  "id",
  "bankAccountId",
  "purpose",
  "isDefault",
] as const satisfies readonly Exclude<keyof SupplierBankAccountUsage, "customFields">[];

const HEADER_ROW_ONLY_COLUMNS = ["id", "createdAt", "updatedAt"] as const;
const USAGE_ROW_ONLY_COLUMNS = ["accountId", "createdAt", "updatedAt"] as const;

function pick<T extends object, K extends keyof T>(row: T, keys: readonly K[]): Pick<T, K> {
  const picked = {} as Pick<T, K>;
  for (const key of keys) picked[key] = row[key];
  return picked;
}

/** Runtime rows carry app-extension columns the generated schema does not know. */
function extractCustomFields(row: object, knownColumns: readonly string[]) {
  const known = new Set(knownColumns);
  const customFields: Record<string, unknown> = {};
  for (const [key, value] of Object.entries(row)) {
    if (!known.has(key)) customFields[key] = value;
  }
  return customFields;
}

function isSameColumnValue(a: unknown, b: unknown) {
  if (a instanceof Date || b instanceof Date) {
    return a instanceof Date && b instanceof Date && a.getTime() === b.getTime();
  }
  return a === b;
}

function changedColumns(before: Record<string, unknown>, after: Record<string, unknown>) {
  const changes: Record<string, unknown> = {};
  for (const [column, value] of Object.entries(after)) {
    if (!isSameColumnValue(before[column], value)) changes[column] = value;
  }
  return changes;
}

export function toSupplierAccount(
  headerRow: Selectable<"SupplierAccount">,
  addressUsageRows: Selectable<"SupplierAddressUsage">[],
  bankAccountUsageRows: Selectable<"SupplierBankAccountUsage">[],
): SupplierAccount {
  const header: SupplierAccountHeader = {
    ...pick(headerRow, SUPPLIER_ACCOUNT_HEADER_COLUMNS),
    customFields: extractCustomFields(headerRow, [
      ...SUPPLIER_ACCOUNT_HEADER_COLUMNS,
      ...HEADER_ROW_ONLY_COLUMNS,
    ]),
  };
  const addressUsages = addressUsageRows.map((row): SupplierAddressUsage => ({
    ...pick(row, SUPPLIER_ADDRESS_USAGE_COLUMNS),
    customFields: extractCustomFields(row, [
      ...SUPPLIER_ADDRESS_USAGE_COLUMNS,
      ...USAGE_ROW_ONLY_COLUMNS,
    ]),
  }));
  const bankAccountUsages = bankAccountUsageRows.map((row): SupplierBankAccountUsage => ({
    ...pick(row, SUPPLIER_BANK_ACCOUNT_USAGE_COLUMNS),
    customFields: extractCustomFields(row, [
      ...SUPPLIER_BANK_ACCOUNT_USAGE_COLUMNS,
      ...USAGE_ROW_ONLY_COLUMNS,
    ]),
  }));
  return markInvariantsSatisfied({ id: headerRow.id, header, addressUsages, bankAccountUsages });
}

/** Custom fields first so domain-owned columns always win. */
function toSupplierAccountRow(account: SupplierAccount) {
  const row: Record<string, unknown> = { ...account.header.customFields, id: account.id };
  for (const column of SUPPLIER_ACCOUNT_HEADER_COLUMNS) row[column] = account.header[column];
  return row;
}

/** Custom fields first so domain-owned columns always win. */
function toSupplierAddressUsageRow(usage: SupplierAddressUsage, accountId: string) {
  const row: Record<string, unknown> = { ...usage.customFields, accountId };
  for (const column of SUPPLIER_ADDRESS_USAGE_COLUMNS) row[column] = usage[column];
  return row;
}

/** Custom fields first so domain-owned columns always win. */
function toSupplierBankAccountUsageRow(usage: SupplierBankAccountUsage, accountId: string) {
  const row: Record<string, unknown> = { ...usage.customFields, accountId };
  for (const column of SUPPLIER_BANK_ACCOUNT_USAGE_COLUMNS) row[column] = usage[column];
  return row;
}

export function createSupplierAccountRepository(db: Transaction): SupplierAccountRepository {
  async function loadUsages(headerRow: Selectable<"SupplierAccount">) {
    const addressUsageRows = await db
      .selectFrom("SupplierAddressUsage")
      .selectAll()
      .where("accountId", "=", headerRow.id)
      .execute();
    const bankAccountUsageRows = await db
      .selectFrom("SupplierBankAccountUsage")
      .selectAll()
      .where("accountId", "=", headerRow.id)
      .execute();
    return toSupplierAccount(headerRow, addressUsageRows, bankAccountUsageRows);
  }

  return {
    async findById(id, opts) {
      let query = db.selectFrom("SupplierAccount").selectAll().where("id", "=", id);
      if (opts?.forUpdate) query = query.forUpdate();
      const headerRow = await query.executeTakeFirst();
      return headerRow ? loadUsages(headerRow) : null;
    },

    async findByCompanyAndCode(companyId, code, opts) {
      let query = db
        .selectFrom("SupplierAccount")
        .selectAll()
        .where("companyId", "=", companyId)
        .where("code", "=", code);
      if (opts?.forUpdate) query = query.forUpdate();
      const headerRow = await query.executeTakeFirst();
      return headerRow ? loadUsages(headerRow) : null;
    },

    // Diffing stored rows prevents a no-op save from bumping updatedAt.
    async save(account) {
      const storedHeader = await db
        .selectFrom("SupplierAccount")
        .selectAll()
        .where("id", "=", account.id)
        .forUpdate()
        .executeTakeFirst();

      if (!storedHeader) {
        await db
          .insertInto("SupplierAccount")
          .values(toSupplierAccountRow(account) as Insertable<"SupplierAccount">)
          .execute();
        if (account.addressUsages.length > 0) {
          await db
            .insertInto("SupplierAddressUsage")
            .values(
              account.addressUsages.map(
                (usage) =>
                  toSupplierAddressUsageRow(
                    usage,
                    account.id,
                  ) as Insertable<"SupplierAddressUsage">,
              ),
            )
            .execute();
        }
        if (account.bankAccountUsages.length > 0) {
          await db
            .insertInto("SupplierBankAccountUsage")
            .values(
              account.bankAccountUsages.map(
                (usage) =>
                  toSupplierBankAccountUsageRow(
                    usage,
                    account.id,
                  ) as Insertable<"SupplierBankAccountUsage">,
              ),
            )
            .execute();
        }
        return;
      }

      const storedAddressUsages = await db
        .selectFrom("SupplierAddressUsage")
        .selectAll()
        .where("accountId", "=", account.id)
        .execute();
      const storedBankAccountUsages = await db
        .selectFrom("SupplierBankAccountUsage")
        .selectAll()
        .where("accountId", "=", account.id)
        .execute();

      const storedAddressById = new Map(storedAddressUsages.map((row) => [row.id, row]));
      for (const usage of account.addressUsages) {
        const stored = storedAddressById.get(usage.id);
        if (!stored) continue;
        const changes = changedColumns(stored, toSupplierAddressUsageRow(usage, account.id));
        if (Object.keys(changes).length > 0) {
          await db
            .updateTable("SupplierAddressUsage")
            .set(changes as Updateable<"SupplierAddressUsage">)
            .where("id", "=", usage.id)
            .execute();
        }
      }

      const keptAddressIds = new Set(account.addressUsages.map((usage) => usage.id));
      const removedAddressIds = storedAddressUsages
        .filter((row) => !keptAddressIds.has(row.id))
        .map((row) => row.id);
      if (removedAddressIds.length > 0) {
        await db.deleteFrom("SupplierAddressUsage").where("id", "in", removedAddressIds).execute();
      }

      const addedAddressRows = account.addressUsages
        .filter((usage) => !storedAddressById.has(usage.id))
        .map(
          (usage) =>
            toSupplierAddressUsageRow(usage, account.id) as Insertable<"SupplierAddressUsage">,
        );
      if (addedAddressRows.length > 0) {
        await db.insertInto("SupplierAddressUsage").values(addedAddressRows).execute();
      }

      const storedBankById = new Map(storedBankAccountUsages.map((row) => [row.id, row]));
      for (const usage of account.bankAccountUsages) {
        const stored = storedBankById.get(usage.id);
        if (!stored) continue;
        const changes = changedColumns(stored, toSupplierBankAccountUsageRow(usage, account.id));
        if (Object.keys(changes).length > 0) {
          await db
            .updateTable("SupplierBankAccountUsage")
            .set(changes as Updateable<"SupplierBankAccountUsage">)
            .where("id", "=", usage.id)
            .execute();
        }
      }

      const keptBankIds = new Set(account.bankAccountUsages.map((usage) => usage.id));
      const removedBankIds = storedBankAccountUsages
        .filter((row) => !keptBankIds.has(row.id))
        .map((row) => row.id);
      if (removedBankIds.length > 0) {
        await db.deleteFrom("SupplierBankAccountUsage").where("id", "in", removedBankIds).execute();
      }

      const addedBankRows = account.bankAccountUsages
        .filter((usage) => !storedBankById.has(usage.id))
        .map(
          (usage) =>
            toSupplierBankAccountUsageRow(
              usage,
              account.id,
            ) as Insertable<"SupplierBankAccountUsage">,
        );
      if (addedBankRows.length > 0) {
        await db.insertInto("SupplierBankAccountUsage").values(addedBankRows).execute();
      }

      const headerChanges = changedColumns(storedHeader, toSupplierAccountRow(account));
      if (Object.keys(headerChanges).length > 0) {
        await db
          .updateTable("SupplierAccount")
          .set(headerChanges as Updateable<"SupplierAccount">)
          .where("id", "=", account.id)
          .execute();
      }
    },
  };
}