/**
 * Recurring expense templates schema — recurring-expenses (wave-10).
 * Postgres / Neon via Hyperdrive.
 *
 * Tables:
 *   recurring_expense_templates — one row per active recurring expense schedule
 *
 * DB conventions:
 *   - UUID PK .defaultRandom()
 *   - TIMESTAMPTZ via timestamp(col, { withTimezone: true })
 *   - Money: NUMERIC
 *   - Enums: text() + check(... IN (...)) — NEVER pgEnum
 *   - FKs to tenants/users via .references()
 *
 * interval values: 'daily' | 'weekly' | 'monthly' | 'yearly'
 */
import { pgTable, uuid, text, boolean, numeric, integer, timestamp, check } from 'drizzle-orm/pg-core'
import { sql } from 'drizzle-orm'
import { tenants } from './tenants'
import { users } from './users'

// ── recurring_expense_templates ───────────────────────────────────────────────

export const recurringExpenseTemplates = pgTable(
  'recurring_expense_templates',
  {
    id: uuid('id').primaryKey().defaultRandom(),

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

    createdBy: uuid('created_by')
      .notNull()
      .references(() => users.id, { onDelete: 'restrict' }),

    /** Human-readable label, e.g. "Monthly Office Rent". Max 200 chars. */
    description: text('description').notNull(),

    /** Amount in currency units (e.g. ILS). */
    amount: numeric('amount', { precision: 12, scale: 2 }).notNull(),

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

    /** Expense category identifier (mirrors expenses.expense_category). */
    categoryId: text('category_id'),

    /** Vendor / supplier name. */
    vendorName: text('vendor_name'),

    /** Recurrence interval. */
    interval: text('interval').notNull(),

    /**
     * Day of month (1-31) for monthly/yearly intervals.
     * Ignored for daily/weekly. Clamped at generation time.
     */
    dayOfMonth: integer('day_of_month'),

    /** Next scheduled generation timestamp (TIMESTAMPTZ). */
    nextDueAt: timestamp('next_due_at', { withTimezone: true }).notNull(),

    /** Whether the template is active and should generate expenses. */
    isActive: boolean('is_active').notNull().default(true),

    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    intervalCheck: check(
      'recurring_expense_templates_interval_check',
      sql`${t.interval} IN ('daily','weekly','monthly','yearly')`,
    ),
    dayOfMonthCheck: check(
      'recurring_expense_templates_day_of_month_check',
      sql`${t.dayOfMonth} IS NULL OR (${t.dayOfMonth} >= 1 AND ${t.dayOfMonth} <= 31)`,
    ),
    amountCheck: check(
      'recurring_expense_templates_amount_check',
      sql`${t.amount} > 0`,
    ),
  }),
)

export type RecurringExpenseTemplateRow = typeof recurringExpenseTemplates.$inferSelect
export type NewRecurringExpenseTemplate = typeof recurringExpenseTemplates.$inferInsert