/**
 * Project milestones schema — project-milestones (P060).
 * Postgres / Neon via Hyperdrive.
 *
 * Tables:
 * - project_milestones: milestone checkpoints per project with optional invoice linkage
 *
 * Partial/expression indexes go in raw_ddl (drizzle-kit snapshot safety):
 *   idx_project_milestones_incomplete  (tenant_id, project_id) WHERE completed_at IS NULL
 *
 * DB conventions:
 * - UUID PK .defaultRandom()
 * - TIMESTAMPTZ via timestamp(col, { withTimezone: true })
 * - Money: NUMERIC(10,2)
 * - Enums: text() + check() IN — NEVER pgEnum
 */
import {
  pgTable,
  uuid,
  text,
  numeric,
  integer,
  timestamp,
  date,
  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 { invoices } from './invoices'

// ── project_milestones ────────────────────────────────────────────────────────

export const projectMilestones = pgTable(
  'project_milestones',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    projectId: uuid('project_id')
      .notNull()
      .references(() => projects.id, { onDelete: 'cascade' }),
    // Display name of the milestone
    name: text('name').notNull(),
    description: text('description'),
    amount: numeric('amount', { precision: 10, scale: 2 }).notNull(),
    invoiceId: uuid('invoice_id').references(() => invoices.id, { onDelete: 'set null' }),
    status: text('status').notNull().default('pending'),
    // Scheduled delivery date
    dueDate: date('due_date'),
    // Completion timestamp (set when status transitions to 'completed')
    completedAt: timestamp('completed_at', { withTimezone: true }),
    position: integer('position').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) => ({
    projectIdx: index('idx_project_milestones_project').on(t.projectId),
    tenantProjectIdx: index('idx_project_milestones_tenant_project').on(t.tenantId, t.projectId),
    statusCheck: check(
      'project_milestones_status_check',
      sql`${t.status} IN ('pending', 'in_progress', 'completed', 'cancelled')`,
    ),
  }),
)

export type ProjectMilestoneRow = typeof projectMilestones.$inferSelect
export type NewProjectMilestone = typeof projectMilestones.$inferInsert