/**
 * Recurring Tasks & Templates schema — recurring-tasks-templates.
 * Postgres / Neon via Hyperdrive.
 *
 * Tables:
 *  - recurring_tasks:  RRULE-bearing blueprint that auto-generates task instances
 *  - task_templates:   reusable task blueprints for one-click task creation
 *
 * Schema delta on tasks table (tasks-board-engine):
 *   ALTER TABLE tasks ADD COLUMN recurring_task_id UUID REFERENCES recurring_tasks(id);
 *
 * Partial unique index on tasks(recurring_task_id, due_date) WHERE recurring_task_id IS NOT NULL
 * is returned in manifest.raw_ddl — drizzle-kit truncates partial index expressions.
 */
import {
  pgTable,
  uuid,
  text,
  boolean,
  integer,
  numeric,
  timestamp,
  index,
  check,
} from 'drizzle-orm/pg-core'
import { sql } from 'drizzle-orm'
import { tenants } from './tenants'
import { users } from './users'
import { projects } from './projects'
import { taskStatuses } from './tasks'

// ── recurring_tasks ───────────────────────────────────────────────────────────

export const recurringTasks = pgTable(
  'recurring_tasks',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    projectId: uuid('project_id').references(() => projects.id, { onDelete: 'set null' }),
    title: text('title').notNull(),
    description: text('description'),
    assigneeId: uuid('assignee_id').references(() => users.id, { onDelete: 'set null' }),
    estimatedHours: numeric('estimated_hours', { precision: 6, scale: 2 }),
    // priority lowercase enum matching tasks.priority CHECK
    priority: text('priority').notNull().default('medium'),
    // TEXT[] for label names (mirrors task_labels.label TEXT)
    labels: text('labels').array().notNull().default(sql`'{}'::text[]`),
    // RFC 5545 RRULE string, e.g. 'FREQ=WEEKLY;BYDAY=MO'
    recurrence: text('recurrence').notNull(),
    // Create instance N days before due date
    advanceDays: integer('advance_days').notNull().default(1),
    // Starting status for generated tasks
    statusId: uuid('status_id').references(() => taskStatuses.id, { onDelete: 'set null' }),
    isActive: boolean('is_active').notNull().default(true),
    createdBy: uuid('created_by')
      .notNull()
      .references(() => users.id, { onDelete: 'restrict' }),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    lastGeneratedAt: timestamp('last_generated_at', { withTimezone: true }),
  },
  (t) => ({
    priorityCheck: check(
      'recurring_tasks_priority_check',
      sql`${t.priority} IN ('low','medium','high','urgent')`,
    ),
    tenantActiveIdx: index('idx_recurring_tasks_tenant_active').on(t.tenantId, t.isActive),
    tenantCreatedIdx: index('idx_recurring_tasks_tenant_created').on(t.tenantId, t.createdAt),
  }),
)

export type RecurringTaskRow = typeof recurringTasks.$inferSelect
export type NewRecurringTask = typeof recurringTasks.$inferInsert

// ── task_templates ────────────────────────────────────────────────────────────

export const taskTemplates = pgTable(
  'task_templates',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    // Template display name
    name: text('name').notNull(),
    // Task title; may contain {{customer}}, {{project}} variables
    title: text('title').notNull(),
    description: text('description'),
    estimatedHours: numeric('estimated_hours', { precision: 6, scale: 2 }),
    // priority lowercase enum matching tasks.priority CHECK
    priority: text('priority').notNull().default('medium'),
    // TEXT[] for label names
    labels: text('labels').array().notNull().default(sql`'{}'::text[]`),
    createdBy: uuid('created_by')
      .notNull()
      .references(() => users.id, { onDelete: 'restrict' }),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    priorityCheck: check(
      'task_templates_priority_check',
      sql`${t.priority} IN ('low','medium','high','urgent')`,
    ),
    tenantNameIdx: index('idx_task_templates_tenant_name').on(t.tenantId, t.name),
  }),
)

export type TaskTemplateRow = typeof taskTemplates.$inferSelect
export type NewTaskTemplate = typeof taskTemplates.$inferInsert