/**
 * report_schedules schema — scheduled-reports (wave-15).
 * Postgres / Neon via Hyperdrive.
 *
 * Table:
 *  - report_schedules: per-tenant recurring report delivery configurations
 *
 * DB conventions:
 * - UUID PK .defaultRandom()
 * - TIMESTAMPTZ via timestamp(col, { withTimezone: true })
 * - Enums: text() + check(... IN (...)) — NEVER pgEnum
 * - Plain btree indexes via drizzle index()
 */
import { pgTable, uuid, text, integer, boolean, jsonb, timestamp, index, check } from 'drizzle-orm/pg-core'
import { sql } from 'drizzle-orm'
import { tenants } from './tenants'
import { users } from './users'

export const reportSchedules = pgTable(
  'report_schedules',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    createdBy: uuid('created_by')
      .references(() => users.id, { onDelete: 'set null' }),
    name: text('name').notNull(),
    reportType: text('report_type').notNull(),
    format: text('format').notNull().default('xlsx'),
    frequency: text('frequency').notNull(),
    dayOfWeek: integer('day_of_week'),
    dayOfMonth: integer('day_of_month'),
    timeOfDay: text('time_of_day').notNull().default('08:00'),
    periodType: text('period_type').notNull().default('previous'),
    reportParams: jsonb('report_params').notNull().default('{}'),
    recipients: jsonb('recipients').notNull().default('[]'),
    isActive: boolean('is_active').notNull().default(true),
    nextRunAt: timestamp('next_run_at', { withTimezone: true }),
    lastRunAt: timestamp('last_run_at', { withTimezone: true }),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    activeIdx: index('idx_report_schedules_active').on(t.tenantId, t.isActive, t.nextRunAt),
    reportTypeCheck: check(
      'report_schedules_report_type_check',
      sql`${t.reportType} IN (
        'revenue','invoices','payments','time','expenses',
        'profitability','revenue_forecast','leads','proposals',
        'ar_aging','bad_debt','audit','api_usage',
        'vat','pnl','cashflow','advance_tax','withholding',
        'bituach_leumi','uniform_format'
      )`,
    ),
    formatCheck: check(
      'report_schedules_format_check',
      sql`${t.format} IN ('xlsx','pdf')`,
    ),
    frequencyCheck: check(
      'report_schedules_frequency_check',
      sql`${t.frequency} IN ('daily','weekly','monthly','quarterly')`,
    ),
    periodTypeCheck: check(
      'report_schedules_period_type_check',
      sql`${t.periodType} IN ('previous','current','ytd')`,
    ),
    dayOfWeekCheck: check(
      'report_schedules_day_of_week_check',
      sql`${t.dayOfWeek} IS NULL OR (${t.dayOfWeek} BETWEEN 0 AND 6)`,
    ),
    dayOfMonthCheck: check(
      'report_schedules_day_of_month_check',
      sql`${t.dayOfMonth} IS NULL OR (${t.dayOfMonth} BETWEEN 1 AND 28)`,
    ),
  }),
)

export type ReportScheduleRow = typeof reportSchedules.$inferSelect
export type NewReportSchedule = typeof reportSchedules.$inferInsert