import {
  pgTable,
  serial,
  integer,
  bigint,
  text,
  boolean,
  date,
  timestamp,
  uniqueIndex,
  index,
} from "drizzle-orm/pg-core";
import { sql } from "drizzle-orm";
import { users } from "officerdb/auth/schema";

/**
 * The ledger: accounts, the transactions in them, and a record of each import.
 *
 * ── Money is an integer of minor units, everywhere ──
 *
 * `bigint` holding cents, never `numeric` and never a float. A float cannot represent 0.10, so a column of
 * them adds up to a balance that is wrong by an amount nobody can find. `numeric` is exact but arrives in
 * JavaScript as a string and leaves as one, so every sum is either a string concatenation bug waiting to
 * happen or a parse somebody forgot. An integer of the smallest unit is what every ledger that has been
 * got right uses, and the only cost is that the UI multiplies by 100 on the way in.
 *
 * `mode: 'number'` is safe here: 2^53 minor units is ninety trillion euros.
 *
 * ── The sign carries the direction ──
 *
 * Negative is money leaving the account, positive is money arriving. Not a `type` column beside a positive
 * amount, because then every sum needs a CASE and one query somewhere will forget it.
 */

/**
 * An account. What the balance is OF.
 *
 * There is no `balance` column. The balance is `openingBalance` plus the transactions, computed on read —
 * a stored balance is a second source of truth that drifts the first time an import half-fails, and the
 * drift is silent because a wrong number looks exactly like a right one.
 */
export const financesAccounts = pgTable(
  "finances_accounts",
  {
    id: serial("id").primaryKey(),
    userId: integer("user_id")
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
    /** What the owner calls it. The only thing the account list shows, so it has to be theirs to set. */
    label: text("label").notNull(),
    /**
     * `checking` | `savings` | `credit` | `cash` | `investment`.
     *
     * A TS-only union with no DB CHECK, the same choice `pipeline_jobs.mode` makes: adding a kind should
     * be a type change and a UI change, not a migration.
     */
    kind: text("kind").notNull().default("checking"),
    /** ISO 4217, uppercase. On the account, not the transaction — a transaction is in its account's money. */
    currency: text("currency").notNull().default("EUR"),
    /**
     * The balance before the first transaction in this table, in minor units.
     *
     * Every real account starts mid-life: you import a statement from March and the account did not begin
     * in March. Without this the ledger is internally consistent and disagrees with the bank forever.
     */
    openingBalance: bigint("opening_balance", { mode: "number" })
      .notNull()
      .default(0),
    /** Closed, not deleted — its transactions are still history. Hidden from the list, kept in totals. */
    archived: boolean("archived").notNull().default(false),
    createdAt: timestamp("created_at", { withTimezone: true })
      .notNull()
      .defaultNow(),
    updatedAt: timestamp("updated_at", { withTimezone: true })
      .notNull()
      .defaultNow(),
  },
  (t) => [uniqueIndex("uq_finances_accounts_user_label").on(t.userId, t.label)],
);

/**
 * One import run, so that a bad one can be undone.
 *
 * The first CSV import is always mapped wrong — a date format, a decimal comma, a sign convention — and
 * without this row the only undo is picking three hundred transactions out by hand. Deleting an import
 * cascades to the transactions it created.
 */
export const financesImports = pgTable("finances_imports", {
  id: serial("id").primaryKey(),
  accountId: integer("account_id")
    .notNull()
    .references(() => financesAccounts.id, { onDelete: "cascade" }),
  /** The file it came from, for recognising it in a list of imports. */
  filename: text("filename").notNull(),
  /** How many rows were written. Rows skipped as duplicates are not counted here. */
  rowCount: integer("row_count").notNull().default(0),
  /** How many rows the file held that were already present. Shown after an import, never acted on. */
  skippedCount: integer("skipped_count").notNull().default(0),
  createdAt: timestamp("created_at", { withTimezone: true })
    .notNull()
    .defaultNow(),
});

/** A line in the ledger. */
export const financesTransactions = pgTable(
  "finances_transactions",
  {
    id: serial("id").primaryKey(),
    /**
     * Scoping goes through the account rather than a `user_id` column beside it.
     *
     * Denormalising the owner onto every row would save a join and introduce a way for the two to
     * disagree — and a transaction whose row says one owner and whose account says another is a
     * permission bug wearing a data bug's clothes.
     */
    accountId: integer("account_id")
      .notNull()
      .references(() => financesAccounts.id, { onDelete: "cascade" }),
    /** The day it hit the account. A date, not a timestamp — a bank statement has no time of day. */
    bookedOn: date("booked_on").notNull(),
    /** Minor units, signed. Negative leaves the account. */
    amount: bigint("amount", { mode: "number" }).notNull(),
    /** What the statement called it. Kept verbatim, however unhelpful — it is the only original record. */
    description: text("description").notNull(),
    /** Who was on the other end, when the source says. Free text; there is no counterparty table yet. */
    counterparty: text("counterparty"),
    /**
     * Free text, deliberately not a foreign key.
     *
     * Categories become a table when budgets arrive and need to be named, coloured and rolled up. Until
     * then a string is the honest shape, and turning it into a table later is a migration over values
     * that already exist rather than a guess at what they will be.
     */
    category: text("category"),
    /** The owner's own note. Never written by an import. */
    notes: text("notes"),
    /**
     * The id this row had at its source — a bank reference, or a hash of the CSV line.
     *
     * This is what makes re-importing an overlapping statement safe, which matters because overlapping
     * statements are the normal case: you export "last 90 days" every month.
     */
    externalId: text("external_id"),
    /** The import that created it. Null means it was entered by hand. */
    importId: integer("import_id").references(() => financesImports.id, {
      onDelete: "cascade",
    }),
    createdAt: timestamp("created_at", { withTimezone: true })
      .notNull()
      .defaultNow(),
    updatedAt: timestamp("updated_at", { withTimezone: true })
      .notNull()
      .defaultNow(),
  },
  (t) => [
    /**
     * Import idempotency, enforced by the database.
     *
     * Partial, over the rows that HAVE an external id: two hand-entered transactions with no id are two
     * legitimate rows, and a plain unique index would refuse the second one.
     */
    uniqueIndex("uq_finances_transactions_external")
      .on(t.accountId, t.externalId)
      .where(sql`${t.externalId} is not null`),
    /** Every view of this table is one account, newest first. */
    index("ix_finances_transactions_account_date").on(t.accountId, t.bookedOn),
  ],
);

export type FinancesAccount = typeof financesAccounts.$inferSelect;
export type FinancesTransaction = typeof financesTransactions.$inferSelect;
export type FinancesImport = typeof financesImports.$inferSelect;