/**
 * Projects module schema — three tables for tenant-scoped project management.
 * Postgres / Neon via Hyperdrive.
 *
 * - projects:         central organizing unit; carries billing_type + billing_config JSONB
 * - project_members:  team membership per project (composite PK)
 * - retainer_months:  monthly ledger for retainer hour-bank tracking
 */
import {
  pgTable,
  uuid,
  text,
  jsonb,
  numeric,
  timestamp,
  date,
  index,
  check,
  uniqueIndex,
  primaryKey,
} from 'drizzle-orm/pg-core'
import { sql } from 'drizzle-orm'
import { tenants } from './tenants'
import { users } from './users'
import { customers } from './customers'

// ── projects ──────────────────────────────────────────────────────────────────

export const projects = pgTable(
  'projects',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    // nullable: internal projects have no customer
    customerId: uuid('customer_id').references(() => customers.id, {
      onDelete: 'set null',
    }),
    name: text('name').notNull(),
    description: text('description'),
    status: text('status').notNull().default('active'),
    billingType: text('billing_type').notNull(),
    // JSONB: three shapes — see FixedBillingConfig | HourlyBillingConfig | RetainerBillingConfig
    billingConfig: jsonb('billing_config'),
    currency: text('currency').notNull().default('ILS'),
    startDate: date('start_date'),
    endDate: date('end_date'),
    createdBy: uuid('created_by')
      .notNull()
      .references(() => users.id),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
    // lifecycle timestamps — set on status transitions, nullable until transition occurs
    completedAt: timestamp('completed_at', { withTimezone: true }),
    archivedAt: timestamp('archived_at', { withTimezone: true }),
  },
  (t) => ({
    tenantStatusIdx: index('idx_projects_tenant_status').on(
      t.tenantId,
      t.status,
      t.updatedAt.desc(),
    ),
    tenantCustomerIdx: index('idx_projects_tenant_customer').on(t.tenantId, t.customerId),
    tenantBillingIdx: index('idx_projects_tenant_billing').on(t.tenantId, t.billingType),
    tenantNameIdx: index('idx_projects_tenant_name').on(t.tenantId, t.name),
    statusCheck: check(
      'projects_status_check',
      sql`${t.status} IN ('active', 'on_hold', 'completed', 'archived')`,
    ),
    billingTypeCheck: check(
      'projects_billing_type_check',
      sql`${t.billingType} IN ('fixed', 'hourly', 'retainer')`,
    ),
  }),
)

export type ProjectRow = typeof projects.$inferSelect
export type NewProject = typeof projects.$inferInsert

// ── project_members ───────────────────────────────────────────────────────────

export const projectMembers = pgTable(
  'project_members',
  {
    projectId: uuid('project_id')
      .notNull()
      .references(() => projects.id, { onDelete: 'cascade' }),
    userId: uuid('user_id')
      .notNull()
      .references(() => users.id, { onDelete: 'cascade' }),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    role: text('role').notNull().default('member'),
    // contractor-specific rate override, nullable
    hourlyRate: numeric('hourly_rate', { precision: 10, scale: 2 }),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    pk: primaryKey({ columns: [t.projectId, t.userId] }),
    tenantUserIdx: index('idx_project_members_tenant_user').on(t.tenantId, t.userId),
    roleCheck: check(
      'project_members_role_check',
      sql`${t.role} IN ('owner', 'member', 'viewer')`,
    ),
  }),
)

export type ProjectMemberRow = typeof projectMembers.$inferSelect
export type NewProjectMember = typeof projectMembers.$inferInsert

// ── retainer_months ───────────────────────────────────────────────────────────

export const retainerMonths = pgTable(
  'retainer_months',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    projectId: uuid('project_id')
      .notNull()
      .references(() => projects.id, { onDelete: 'cascade' }),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    // 'YYYY-MM' string — keeps month identity timezone-agnostic
    month: text('month').notNull(),
    hoursIncluded: numeric('hours_included', { precision: 6, scale: 2 }),
    hoursUsed: numeric('hours_used', { precision: 6, scale: 2 }).notNull().default('0'),
    hoursRolledOver: numeric('hours_rolled_over', { precision: 6, scale: 2 })
      .notNull()
      .default('0'),
    invoiceTriggeredAt: timestamp('invoice_triggered_at', { withTimezone: true }),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    projectMonthUniq: uniqueIndex('idx_retainer_months_project_month_uniq').on(
      t.projectId,
      t.month,
    ),
    tenantProjectMonthIdx: index('idx_retainer_months_project').on(
      t.tenantId,
      t.projectId,
      t.month,
    ),
  }),
)

export type RetainerMonthRow = typeof retainerMonths.$inferSelect
export type NewRetainerMonth = typeof retainerMonths.$inferInsert