/**
 * Recurring invoice templates schema — recurring-invoices (P061).
 * Postgres / Neon via Hyperdrive.
 *
 * Tables:
 *   recurring_invoice_templates — one row per active schedule
 *
 * DB conventions:
 *   - UUID PK .defaultRandom()
 *   - TIMESTAMPTZ via timestamp(col, { withTimezone: true })
 *   - Money: NUMERIC
 *   - Enums: text() + check(... IN (...)) — NEVER pgEnum
 *   - Expression/partial indexes → manifest.raw_ddl only (drizzle snapshot-truncation bug)
 *   - FKs to tenants/customers/users via .references()
 *   - JSONB for line_items
 *
 * Partial indexes (returned in manifest.raw_ddl):
 *   - idx_rit_next_gen: (next_generation_date) WHERE status = 'active'
 *   - idx_rit_tenant:   (tenant_id, status)
 *   - idx_rit_customer: (tenant_id, customer_id)
 *
 * Tier gate: Business+ (max 20 active on Business, unlimited on Enterprise).
 *
 * Cron: daily at 06:00 UTC (0 6 * * *).
 * Handler: apps/zync-api/src/routes/cron/recurring-invoice-generator.ts
 */
import { pgTable, uuid, text, integer, boolean, jsonb, date, numeric, timestamp, check } from 'drizzle-orm/pg-core'
import { sql } from 'drizzle-orm'
import { tenants } from './tenants'
import { customers } from './customers'
import { users } from './users'

// ── recurring_invoice_templates ───────────────────────────────────────────────

export const recurringInvoiceTemplates = pgTable(
  'recurring_invoice_templates',
  {
    id: uuid('id').primaryKey().defaultRandom(),

    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),

    customerId: uuid('customer_id')
      .notNull()
      .references(() => customers.id, { onDelete: 'restrict' }),

    /** Human-readable name, e.g. "Monthly Retainer — Acme Ltd". Max 100 chars. */
    title: text('title').notNull(),

    /** Optional internal notes for staff. */
    description: text('description'),

    /**
     * Line items stored as JSONB — same structure as invoices.line_items.
     * Shape: Array<{ description: string; quantity: number; unitPrice: number;
     *                discountPct: number; taxable: boolean; position: number }>
     */
    lineItems: jsonb('line_items').notNull().default(sql`'[]'::jsonb`),

    /** ISO 4217 currency code. */
    currency: text('currency').notNull().default('ILS'),

    /** VAT rate as a decimal fraction (e.g. 0.18 = 18%). */
    vatRate: numeric('vat_rate', { precision: 5, scale: 4 }).notNull().default('0.18'),

    /** Generation frequency: weekly / monthly / quarterly / yearly. */
    frequency: text('frequency').notNull(),

    /**
     * For monthly/quarterly/yearly: day of month (1–28).
     *   Days > 28 are clamped to the last valid day at generation time.
     * For weekly: day of week (0=Sunday … 6=Saturday).
     * NULL means "same day as start_date".
     */
    frequencyDay: integer('frequency_day'),

    /** Due date = generation date + payment_terms_days. */
    paymentTermsDays: integer('payment_terms_days').notNull().default(30),

    /** ISO date string. First generation on or after this date. */
    startDate: date('start_date').notNull(),

    /** ISO date string. NULL = runs indefinitely. */
    endDate: date('end_date'),

    /**
     * false = create as DRAFT; true = immediately send to customer.
     * auto_send=true requires Business+ tier.
     */
    autoSend: boolean('auto_send').notNull().default(false),

    /**
     * Placeholder for v2 auto-charge via saved payment method.
     * Always FALSE in v1; enforced server-side.
     */
    autoCharge: boolean('auto_charge').notNull().default(false),

    /** Date (YYYY-MM-DD) of next scheduled generation. */
    nextGenerationDate: date('next_generation_date').notNull(),

    /** Timestamp of the most recently completed generation, or NULL if never run. */
    lastGeneratedAt: timestamp('last_generated_at', { withTimezone: true }),

    /**
     * Lifecycle status.
     * active    — normal operation
     * paused    — generation suspended by user
     * completed — end_date passed or manually closed
     * cancelled — permanently stopped
     */
    status: text('status').notNull().default('active'),

    /** Running count of invoices generated from this template. */
    generatedCount: integer('generated_count').notNull().default(0),

    createdBy: uuid('created_by')
      .notNull()
      .references(() => users.id),

    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    frequencyCheck: check(
      'rit_frequency_check',
      sql`${t.frequency} IN ('weekly','monthly','quarterly','yearly')`,
    ),
    statusCheck: check(
      'rit_status_check',
      sql`${t.status} IN ('active','paused','completed','cancelled')`,
    ),
    currencyCheck: check(
      'rit_currency_check',
      sql`${t.currency} IN ('ILS','USD','EUR')`,
    ),
    // NOTE: idx_rit_next_gen (partial WHERE status = 'active'), idx_rit_tenant,
    // idx_rit_customer are in migration raw_ddl — NOT here (drizzle snapshot-truncation bug).
  }),
)

export type RecurringInvoiceTemplateRow = typeof recurringInvoiceTemplates.$inferSelect
export type NewRecurringInvoiceTemplate = typeof recurringInvoiceTemplates.$inferInsert