/**
 * CRM Support Center schema — crm-support-center.
 * Postgres / Neon via Hyperdrive.
 *
 * Tables:
 *  - ticket_categories:          per-tenant category tags for tickets
 *  - tickets:                    core support ticket rows
 *  - ticket_messages:            staff / customer / system messages on a ticket
 *  - ticket_message_attachments: file attachments on ticket messages
 *
 * Plain btree indexes are declared here.
 * Partial/expression/unique-partial indexes are listed in manifest.raw_ddl
 * (drizzle-kit snapshot truncation bug — same convention as tasks.ts).
 */
import {
  pgTable,
  uuid,
  text,
  integer,
  boolean,
  timestamp,
  index,
  check,
  uniqueIndex,
} from 'drizzle-orm/pg-core'
import { sql } from 'drizzle-orm'
import { tenants } from './tenants'
import { users } from './users'
import { customers } from './customers'
import { customerContacts } from './customers'

// ── ticket_categories ─────────────────────────────────────────────────────────

export const ticketCategories = pgTable(
  'ticket_categories',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    name: text('name').notNull(),
    color: text('color'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    tenantNameUniq: uniqueIndex('idx_ticket_categories_tenant_name_uniq').on(t.tenantId, t.name),
  }),
)

export type TicketCategoryRow = typeof ticketCategories.$inferSelect
export type NewTicketCategory = typeof ticketCategories.$inferInsert

// ── tickets ───────────────────────────────────────────────────────────────────

export const tickets = pgTable(
  'tickets',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    // Nullable — anonymous or unmatched-email tickets have no customer
    customerId: uuid('customer_id').references(() => customers.id, { onDelete: 'set null' }),
    // Nullable — specific contact within the customer account
    contactId: uuid('contact_id').references(() => customerContacts.id, {
      onDelete: 'set null',
    }),
    title: text('title').notNull(),
    description: text('description').notNull().default(''),
    // 'open' | 'in_progress' | 'pending_customer' | 'resolved' | 'closed'
    status: text('status').notNull().default('open'),
    // 'low' | 'medium' | 'high' | 'urgent'
    priority: text('priority').notNull().default('medium'),
    // Nullable FK to ticket_categories
    categoryId: uuid('category_id').references(() => ticketCategories.id, {
      onDelete: 'set null',
    }),
    // Assigned staff member
    assigneeId: uuid('assignee_id').references(() => users.id, { onDelete: 'set null' }),
    // Originating channel
    // 'web' | 'email' | 'telegram' | 'whatsapp' | 'portal'
    source: text('source').notNull().default('web'),
    // Message-ID from email or equivalent in other channels
    externalId: text('external_id'),
    // Email In-Reply-To thread / Telegram chat_id — used for reply routing
    externalThreadId: text('external_thread_id'),
    resolvedAt: timestamp('resolved_at', { withTimezone: true }),
    closedAt: timestamp('closed_at', { withTimezone: true }),
    // SLA fields (ticket-sla-escalation P063)
    // Deadline for resolution: tickets.created_at + policy.resolution_hours
    dueAt: timestamp('due_at', { withTimezone: true }),
    // Set to now() when first staff reply is posted
    firstResponseAt: timestamp('first_response_at', { withTimezone: true }),
    // Flipped to true by cron when due_at < now() and ticket not resolved/closed
    slaBreached: boolean('sla_breached').default(false),
    // Soft-delete
    deletedAt: timestamp('deleted_at', { withTimezone: true }),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    statusCheck: check(
      'tickets_status_check',
      sql`${t.status} IN ('open','in_progress','pending_customer','resolved','closed')`,
    ),
    priorityCheck: check(
      'tickets_priority_check',
      sql`${t.priority} IN ('low','medium','high','urgent')`,
    ),
    sourceCheck: check(
      'tickets_source_check',
      sql`${t.source} IN ('web','email','telegram','whatsapp','portal')`,
    ),
    // Plain btree indexes
    tenantStatusIdx: index('idx_tickets_tenant_status').on(t.tenantId, t.status, t.createdAt),
    tenantAssigneeIdx: index('idx_tickets_tenant_assignee').on(t.tenantId, t.assigneeId),
    tenantCustomerIdx: index('idx_tickets_tenant_customer').on(t.tenantId, t.customerId),
    tenantSourceIdx: index('idx_tickets_tenant_source').on(t.tenantId, t.source),
    // Partial/expression indexes are listed in manifest.raw_ddl
    // (unique partial on (tenant_id, source, external_thread_id) WHERE external_thread_id IS NOT NULL AND deleted_at IS NULL)
  }),
)

export type TicketRow = typeof tickets.$inferSelect
export type NewTicket = typeof tickets.$inferInsert

// ── ticket_messages ───────────────────────────────────────────────────────────

export const ticketMessages = pgTable(
  'ticket_messages',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    ticketId: uuid('ticket_id')
      .notNull()
      .references(() => tickets.id, { onDelete: 'cascade' }),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    // 'staff' | 'customer' | 'system'
    authorType: text('author_type').notNull(),
    // Staff user id; NULL for customer/system messages
    authorId: uuid('author_id').references(() => users.id, { onDelete: 'set null' }),
    // Customer name from contact or inbound message
    authorName: text('author_name'),
    // HTML — same sanitization allowlist as task messages
    content: text('content').notNull(),
    // 'web' | 'email' | 'telegram' | 'whatsapp' | 'portal'
    source: text('source').notNull().default('web'),
    // Soft-delete
    deletedAt: timestamp('deleted_at', { withTimezone: true }),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    authorTypeCheck: check(
      'ticket_messages_author_type_check',
      sql`${t.authorType} IN ('staff','customer','system')`,
    ),
    sourceCheck: check(
      'ticket_messages_source_check',
      sql`${t.source} IN ('web','email','telegram','whatsapp','portal')`,
    ),
    ticketCreatedIdx: index('idx_ticket_messages_ticket_created').on(t.ticketId, t.createdAt),
    tenantIdx: index('idx_ticket_messages_tenant').on(t.tenantId),
  }),
)

export type TicketMessageRow = typeof ticketMessages.$inferSelect
export type NewTicketMessage = typeof ticketMessages.$inferInsert

// ── ticket_message_attachments ────────────────────────────────────────────────

export const ticketMessageAttachments = pgTable(
  'ticket_message_attachments',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    messageId: uuid('message_id')
      .notNull()
      .references(() => ticketMessages.id, { onDelete: 'cascade' }),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    filename: text('filename').notNull(),
    r2Key: text('r2_key').notNull(),
    url: text('url').notNull(),
    sizeBytes: integer('size_bytes').notNull(),
    mimeType: text('mime_type').notNull(),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    messageIdx: index('idx_ticket_message_attachments_message').on(t.messageId),
  }),
)

export type TicketMessageAttachmentRow = typeof ticketMessageAttachments.$inferSelect
export type NewTicketMessageAttachment = typeof ticketMessageAttachments.$inferInsert