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

// The InvoiceShelf accounts behind /invoices, for the officer-invoiceshelf sidecar.
//
// This used to be INVOICESHELF_URL + INVOICESHELF_TOKEN + INVOICESHELF_COMPANY_ID in the platform-wide
// `.env`, which was wrong twice over: Bun auto-loads `.env` into EVERY process started in the platform
// directory, so `officer` itself held a Sanctum token it has no code to use — and connecting a books
// instance was a shell task on the server rather than something the owner could do from the app.
//
// It is a REGISTRY, not a single row: the owner adds any number of accounts and switches between them, the
// same shape offscale_servers and photos_config use. Uniqueness is on the label rather than the URL,
// because the same instance with a different company is a different account here, and two of those share
// a URL AND a token.
//
// `token` is encrypted at rest via ../crypto.ts. A Sanctum token can read and write the entire books — every
// invoice, customer and payment — so a DB dump must not hand it over. Encryption is confined to
// queries/invoiceshelf.ts; nothing outside that file sees ciphertext, and no route ever returns the token.
export const invoicesAccounts = pgTable(
  'invoices_accounts',
  {
    id: serial('id').primaryKey(),
    userId: integer('user_id')
      .notNull()
      .references(() => users.id, { onDelete: 'cascade' }),
    /** What the owner calls this account. The switcher shows nothing else, so it has to be theirs to set. */
    label: text('label').notNull(),
    // Normalized without a trailing slash before write, so `${url}/api/v1/...` never doubles the separator.
    url: text('url').notNull(),
    token: text('token').notNull(), // encrypted
    /**
     * The company every request is scoped to, pinned explicitly.
     *
     * This is the sharpest edge in InvoiceShelf's API: the `company` header scopes almost every route, and a
     * wrong or missing value does NOT error — it silently returns another company's books. Storing the
     * choice means it is made once, visibly, instead of falling back per request.
     */
    companyId: integer('company_id'),
    /** Shown in the switcher next to the label, so two companies on one token are told apart at a glance. */
    companyName: text('company_name'),
    /** InvoiceShelf version seen at the last successful probe — shown in the UI, never used for behaviour. */
    version: text('version'),
    isActive: boolean('is_active').notNull().default(false),
    lastSeenAt: timestamp('last_seen_at', { withTimezone: true }),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    uniqueIndex('uq_invoices_accounts_user_label').on(t.userId, t.label),
    // At most one active account per owner, enforced by the DB rather than by convention: a partial unique
    // index over the active rows only. setActiveInvoiceshelfAccount still clears the others in a transaction,
    // but a bug there fails loudly here instead of silently leaving two active and the UI picking one.
    uniqueIndex('uq_invoices_accounts_one_active')
      .on(t.userId)
      .where(sql`${t.isActive}`),
  ],
);