/**
 * Time management schema — time-management.
 * Postgres / Neon via Hyperdrive.
 *
 * Tables:
 *  - time_entries:     toggle-based timer records per user/project/task
 *  - magic_link_tokens: single-use short-lived tokens for timer magic-links,
 *                       portal access, 2FA challenges, etc.
 *
 * NOTE: time-management OWNS magic_link_tokens. auth-2fa has a forward
 * declaration in queries/_magic-link-forward.ts that mirrors this shape.
 * Once this file is in the schema barrel, that forward file should be removed
 * and its consumers re-pointed to this canonical export.
 *
 * Partial indexes (WHERE clause) are NOT declared via drizzle index() to avoid
 * drizzle-kit snapshot truncation. They are returned in manifest.raw_ddl:
 *   idx_time_entries_active  (tenant_id, user_id) WHERE stopped_at IS NULL
 *   idx_magic_link_tokens_expiry (expires_at) WHERE used_at IS NULL
 *
 * Plain btree indexes and CHECK constraints live here.
 */
import {
  pgTable,
  uuid,
  text,
  boolean,
  integer,
  timestamp,
  jsonb,
  index,
  check,
} from 'drizzle-orm/pg-core'
import { sql } from 'drizzle-orm'
import { tenants } from './tenants'
import { users } from './users'
import { tasks } from './tasks'
import { projects } from './projects'
import { invoices } from './invoices'

// ── time_entries ──────────────────────────────────────────────────────────────

export const timeEntries = pgTable(
  'time_entries',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    // NULL when entry belongs to external contractor (contractor_id set instead)
    userId: uuid('user_id').references(() => users.id, { onDelete: 'set null' }),
    // FK to contractors table (owned by contractor-payouts wave 7); nullable for staff entries
    contractorId: uuid('contractor_id'),
    // nullable — project-level time has no task
    taskId: uuid('task_id').references(() => tasks.id, { onDelete: 'set null' }),
    projectId: uuid('project_id')
      .notNull()
      .references(() => projects.id, { onDelete: 'cascade' }),
    description: text('description'),
    startedAt: timestamp('started_at', { withTimezone: true }).notNull(),
    // NULL = currently running
    stoppedAt: timestamp('stopped_at', { withTimezone: true }),
    // Rounded value stored on stop: roundDuration(computeRawSeconds(started, stopped), tenantRounding)
    durationSeconds: integer('duration_seconds'),
    // 'manual' | 'magic_link' | 'auto' | 'contractor_portal'
    source: text('source').notNull().default('manual'),
    billable: boolean('billable').notNull().default(true),

    // Approval + lock state — OWNED HERE (earliest module in the time-entry domain).
    // approval workflow lives in time-approval-workflow (spec 52);
    // lock/unlock in time-entry-locking (spec 95);
    // contractor-payouts writes 'locked'; contractor-portal sets pending/auto_approved.
    approvalStatus: text('approval_status').notNull().default('auto_approved'),
    lockedAt: timestamp('locked_at', { withTimezone: true }),
    // user_id of the manager who locked the entry (time-entry-locking spec 95)
    lockedBy: uuid('locked_by'),
    lockedReason: text('locked_reason'),

    // time-to-invoice (P064): set when entry is included in an invoice.
    // ON DELETE SET NULL: voiding/deleting invoice frees entries for re-billing.
    invoiceId: uuid('invoice_id').references(() => invoices.id, { onDelete: 'set null' }),
    // Timestamp the entry was marked as billed (set in same tx as invoice create).
    billedAt: timestamp('billed_at', { withTimezone: true }),

    // time-approval-workflow (wave-10 leaf 10): submission + approval timestamps.
    // approvalStatus transitions: auto_approved → pending (submit) → approved|rejected.
    submittedAt: timestamp('submitted_at', { withTimezone: true }),
    approvedAt: timestamp('approved_at', { withTimezone: true }),
    approvedBy: uuid('approved_by'),
    rejectedAt: timestamp('rejected_at', { withTimezone: true }),
    rejectionReason: text('rejection_reason'),

    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    // Plain btree indexes — safe for drizzle index()
    projectIdx: index('idx_time_entries_project').on(t.projectId, t.startedAt),
    cursorIdx: index('idx_time_entries_cursor').on(t.tenantId, t.startedAt, t.id),

    // CHECK constraints
    sourceCheck: check(
      'time_entries_source_check',
      sql`${t.source} IN ('manual','magic_link','auto','contractor_portal')`,
    ),
    approvalStatusCheck: check(
      'time_entries_approval_status_check',
      sql`${t.approvalStatus} IN ('auto_approved','pending','approved','rejected','locked')`,
    ),
    lockedReasonCheck: check(
      'time_entries_locked_reason_check',
      sql`${t.lockedReason} IS NULL OR ${t.lockedReason} IN ('invoiced','period_closed','approved')`,
    ),
    userOrContractorCheck: check(
      'time_entries_user_or_contractor_check',
      sql`${t.userId} IS NOT NULL OR ${t.contractorId} IS NOT NULL`,
    ),
  }),
)

export type TimeEntryRow = typeof timeEntries.$inferSelect
export type NewTimeEntry = typeof timeEntries.$inferInsert

// ── magic_link_tokens ─────────────────────────────────────────────────────────
// Canonical owner: time-management (wave 5).
// auth-2fa uses purpose='pending_2fa'/'pending_2fa_setup' (already in CHECK).
// 'magic_link' included for passwordless login (consumed by auth routes).

export const magicLinkTokens = pgTable(
  'magic_link_tokens',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    // NULL for portal invites (pre-registration)
    userId: uuid('user_id').references(() => users.id, { onDelete: 'cascade' }),
    // populated for purpose = 'timer'
    taskId: uuid('task_id').references(() => tasks.id, { onDelete: 'set null' }),
    // populated for portal_invite / portal_password_reset
    email: text('email'),
    // URL-safe plaintext token — stored here so it can be returned in magic-link emails.
    // NULL for purpose='pending_2fa'/'pending_2fa_setup'/'magic_link' (auth-2fa stores
    // only the SHA-256 hash; plaintext is held in client memory, never persisted).
    token: text('token').unique(),
    // SHA-256 hex of token — used for DB lookup (token itself skipped for timing safety)
    tokenHash: text('token_hash').notNull().unique(),
    // 'timer' | 'portal' | 'portal_invite' | 'portal_password_reset' | 'pending_2fa' | 'pending_2fa_setup' | 'magic_link'
    purpose: text('purpose').notNull().default('timer'),
    // TTL varies: 48h timer, 7d portal invite, 1h reset/2fa
    expiresAt: timestamp('expires_at', { withTimezone: true }).notNull(),
    // NULL = unused; set atomically on redemption
    usedAt: timestamp('used_at', { withTimezone: true }),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    // Plain btree index on token_hash — safe for drizzle index()
    tokenHashIdx: index('idx_magic_link_tokens_hash').on(t.tokenHash),
    // Partial index on expires_at WHERE used_at IS NULL emitted in raw_ddl

    // CHECK constraints
    purposeCheck: check(
      'magic_link_tokens_purpose_check',
      sql`${t.purpose} IN ('timer','portal','portal_invite','portal_password_reset','pending_2fa','pending_2fa_setup','magic_link')`,
    ),
  }),
)

export type MagicLinkTokenRow = typeof magicLinkTokens.$inferSelect
export type NewMagicLinkToken = typeof magicLinkTokens.$inferInsert

// ── time_approval_requests ────────────────────────────────────────────────────
// One row per staff submission covering a date period.
// entryIds: JSONB array of UUID strings — the time_entries.id values included.
// status: 'pending' → 'approved' | 'rejected' (manager reviews).

export const timeApprovalRequests = pgTable(
  'time_approval_requests',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    submitterId: uuid('submitter_id').notNull(),
    // nullable — auto-assigned to submitter's manager if known, else any manager reviews
    managerId: uuid('manager_id'),
    periodStart: text('period_start').notNull(), // YYYY-MM-DD
    periodEnd: text('period_end').notNull(),     // YYYY-MM-DD
    // JSONB array of time_entry UUIDs included in this request
    entryIds: jsonb('entry_ids').notNull().$type<string[]>(),
    status: text('status').notNull().default('pending'),
    submittedAt: timestamp('submitted_at', { withTimezone: true }).notNull().defaultNow(),
    reviewedAt: timestamp('reviewed_at', { withTimezone: true }),
    reviewedBy: uuid('reviewed_by'),
    comments: text('comments'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    tenantIdx: index('idx_time_approval_requests_tenant').on(t.tenantId, t.createdAt),
    submitterIdx: index('idx_time_approval_requests_submitter').on(t.tenantId, t.submitterId),
    statusCheck: check(
      'time_approval_requests_status_check',
      sql`${t.status} IN ('pending','approved','rejected')`,
    ),
  }),
)

export type TimeApprovalRequestRow = typeof timeApprovalRequests.$inferSelect
export type NewTimeApprovalRequest = typeof timeApprovalRequests.$inferInsert