/**
 * Tasks board engine schema — tasks-board-engine.
 * Postgres / Neon via Hyperdrive.
 *
 * Tables:
 *  - task_statuses:       per-tenant/per-project customizable Kanban columns
 *  - tasks:               core task rows with fractional-index position
 *  - task_labels:         many-to-many task ↔ text label junction
 *  - task_sync_settings:  per-tenant adapter sync config + inbound auto-create flags
 *
 * Indexes with plain btree column expressions are declared here.
 * Partial/expression indexes are returned in manifest.raw_ddl (drizzle-kit truncates them).
 */
import {
  pgTable,
  uuid,
  text,
  boolean,
  integer,
  numeric,
  date,
  jsonb,
  timestamp,
  primaryKey,
  index,
  check,
  uniqueIndex,
} from 'drizzle-orm/pg-core'
import { sql } from 'drizzle-orm'
import { tenants } from './tenants'
import { users } from './users'
import { projects } from './projects'
import { leads } from './marketing'

// ── task_statuses ─────────────────────────────────────────────────────────────

export const taskStatuses = pgTable(
  'task_statuses',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    // NULL = tenant-global default; non-null = project-specific override
    projectId: uuid('project_id').references(() => projects.id, { onDelete: 'cascade' }),
    name: text('name').notNull(),
    // CSS design-token name (e.g. '--status-backlog') or hex color
    color: text('color').notNull(),
    position: integer('position').notNull(),
    // true for 'DONE'-type terminal statuses
    isTerminal: boolean('is_terminal').notNull().default(false),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    tenantProjectPositionIdx: index('idx_task_statuses_tenant_project_position').on(
      t.tenantId,
      t.projectId,
      t.position,
    ),
  }),
)

export type TaskStatusRow = typeof taskStatuses.$inferSelect
export type NewTaskStatus = typeof taskStatuses.$inferInsert

// ── tasks ─────────────────────────────────────────────────────────────────────

export const tasks = pgTable(
  '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' }),
    statusId: uuid('status_id')
      .notNull()
      .references(() => taskStatuses.id, { onDelete: 'restrict' }),
    title: text('title').notNull(),
    // Tiptap rich-text JSON; nullable
    description: jsonb('description'),
    priority: text('priority').notNull().default('medium'),
    assigneeId: uuid('assignee_id').references(() => users.id, { onDelete: 'set null' }),
    reporterId: uuid('reporter_id')
      .notNull()
      .references(() => users.id, { onDelete: 'restrict' }),
    startDate: date('start_date'),
    dueDate: date('due_date'),
    estimatedHours: numeric('estimated_hours', { precision: 6, scale: 2 }),
    // project-template-gallery: checklist items stored as JSON array
    checklistItems: jsonb('checklist_items').default(sql`'[]'::jsonb`),
    // project-template-gallery: phase label for grouping tasks
    phase: text('phase'),
    isMilestone: boolean('is_milestone').notNull().default(false),
    // External import source
    source: text('source').notNull().default('manual'),
    // Identifier in the external system (Trello card id, Jira issue key, etc.)
    externalId: text('external_id'),
    // FK to recurring_tasks(id) — set null on delete.
    // Intentionally no .references() to avoid a circular import with recurring-tasks.ts.
    // The FK constraint is enforced via ALTER TABLE in the migration (raw_ddl).
    recurringTaskId: uuid('recurring_task_id'),
    // wave-13: leads-detail-view — CRM task context (nullable)
    leadId: uuid('lead_id').references(() => leads.id, { onDelete: 'set null' }),
    // Fractional index for within-column ordering
    position: numeric('position').notNull(),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    priorityCheck: check(
      'tasks_priority_check',
      sql`${t.priority} IN ('low','medium','high','urgent')`,
    ),
    sourceCheck: check(
      'tasks_source_check',
      sql`${t.source} IN ('manual','email','telegram','slack','whatsapp','trello','asana','jira','monday','clickup','api')`,
    ),
    tenantProjectStatusIdx: index('idx_tasks_tenant_project_status').on(
      t.tenantId,
      t.projectId,
      t.statusId,
    ),
    tenantStatusPositionIdx: index('idx_tasks_tenant_status_position').on(
      t.tenantId,
      t.statusId,
      t.position,
    ),
    tenantAssigneeIdx: index('idx_tasks_tenant_assignee').on(t.tenantId, t.assigneeId),
    // Unique partial index on (tenant_id, source, external_id) WHERE external_id IS NOT NULL
    // is returned in manifest.raw_ddl — drizzle-kit truncates partial index expressions.
  }),
)

export type TaskRow = typeof tasks.$inferSelect
export type NewTask = typeof tasks.$inferInsert

// ── task_labels ───────────────────────────────────────────────────────────────

export const taskLabels = pgTable(
  'task_labels',
  {
    taskId: uuid('task_id')
      .notNull()
      .references(() => tasks.id, { onDelete: 'cascade' }),
    label: text('label').notNull(),
  },
  (t) => ({
    pk: primaryKey({ columns: [t.taskId, t.label] }),
    labelIdx: index('idx_task_labels_label').on(t.label),
  }),
)

export type TaskLabelRow = typeof taskLabels.$inferSelect
export type NewTaskLabel = typeof taskLabels.$inferInsert

// ── task_sync_settings ────────────────────────────────────────────────────────

export const taskSyncSettings = pgTable(
  'task_sync_settings',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    syncIntervalMinutes: integer('sync_interval_minutes').notNull().default(120),
    // { email: bool, telegram: bool, slack: bool, whatsapp: bool }
    autoCreateTicketsFrom: jsonb('auto_create_tickets_from')
      .notNull()
      .default(sql`'{}'::jsonb`),
    // Optional project to assign auto-created tasks to
    defaultProjectId: uuid('default_project_id').references(() => projects.id, {
      onDelete: 'set null',
    }),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    tenantUniq: uniqueIndex('idx_task_sync_settings_tenant_uniq').on(t.tenantId),
  }),
)

export type TaskSyncSettingsRow = typeof taskSyncSettings.$inferSelect
export type NewTaskSyncSettings = typeof taskSyncSettings.$inferInsert