/**
 * Task communications schema — tasks-detail-communication.
 * Postgres / Neon via Hyperdrive.
 *
 * Tables:
 *  - task_messages:             per-task comment + system-generated messages
 *  - task_message_attachments:  files attached to task messages
 *  - task_audit_log:            immutable record of every task field mutation
 *
 * Plain btree indexes are declared here.
 * Expression/partial indexes are returned in manifest.raw_ddl.
 */
import {
  pgTable,
  uuid,
  text,
  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'

// ── task_messages ─────────────────────────────────────────────────────────────

export const taskMessages = pgTable(
  'task_messages',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    taskId: uuid('task_id')
      .notNull()
      .references(() => tasks.id, { onDelete: 'cascade' }),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    // NULL for system-generated messages
    authorId: uuid('author_id').references(() => users.id, { onDelete: 'set null' }),
    // 'comment' | 'system'
    messageType: text('message_type').notNull(),
    // sanitized HTML for comments; plain text for system messages
    content: text('content').notNull(),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    // soft delete — author can delete own comment; NULL = active
    deletedAt: timestamp('deleted_at', { withTimezone: true }),
  },
  (t) => ({
    messageTypeCheck: check(
      'task_messages_message_type_check',
      sql`${t.messageType} IN ('comment','system')`,
    ),
    streamIdx: index('idx_task_messages_stream').on(t.taskId, t.createdAt),
    tenantIdx: index('idx_task_messages_tenant').on(t.tenantId),
  }),
)

export type TaskMessageRow = typeof taskMessages.$inferSelect
export type NewTaskMessage = typeof taskMessages.$inferInsert

// ── task_message_attachments ──────────────────────────────────────────────────

export const taskMessageAttachments = pgTable(
  'task_message_attachments',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    // NULL = unparented draft (uploaded before comment is posted).
    // Reparented to the real message when the comment is submitted.
    // Orphaned drafts are cleaned up by a cron job.
    messageId: uuid('message_id')
      .references(() => taskMessages.id, { onDelete: 'cascade' }),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    filename: text('filename').notNull(),
    // R2 signed URL (1h TTL, re-signed on read)
    url: text('url').notNull(),
    // R2 object key for deletion
    r2Key: text('r2_key').notNull(),
    sizeBytes: integer('size_bytes').notNull(),
    mimeType: text('mime_type').notNull(),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    messageIdx: index('idx_task_msg_attachments_message').on(t.messageId),
    tenantIdx: index('idx_task_msg_attachments_tenant').on(t.tenantId),
  }),
)

export type TaskMessageAttachmentRow = typeof taskMessageAttachments.$inferSelect
export type NewTaskMessageAttachment = typeof taskMessageAttachments.$inferInsert

// ── task_audit_log ────────────────────────────────────────────────────────────

export const taskAuditLog = pgTable(
  'task_audit_log',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    taskId: uuid('task_id')
      .notNull()
      .references(() => tasks.id, { onDelete: 'cascade' }),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    actorId: uuid('actor_id')
      .notNull()
      .references(() => users.id),
    // open vocabulary: 'status_changed', 'assigned', 'priority_changed', 'due_date_changed', 'edited', ...
    action: text('action').notNull(),
    previousValue: jsonb('previous_value'),
    newValue: jsonb('new_value'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    taskIdx: index('idx_task_audit_log_task').on(t.taskId, t.createdAt),
    tenantIdx: index('idx_task_audit_log_tenant').on(t.tenantId),
  }),
)

export type TaskAuditLogRow = typeof taskAuditLog.$inferSelect
export type NewTaskAuditLog = typeof taskAuditLog.$inferInsert