/**
 * CRM Support Center query helpers — crm-support-center.
 *
 * All helpers are tenant-scoped: every statement carries a tenant_id WHERE clause.
 * Routes MUST NOT import raw Drizzle tables — they call these helpers.
 *
 * Zod validation schemas are re-exported here so route files can import them
 * via @zync/db/queries (no-raw-drizzle-from-routes policy).
 */
import { and, eq, isNull, ilike, desc, asc, sql } from 'drizzle-orm'
import type { Db, DbTx } from '../client'
import {
  tickets,
  ticketMessages,
  ticketMessageAttachments,
  ticketCategories,
} from '../schema/support'
import { customers, customerContacts } from '../schema/customers'
import { users } from '../schema/users'
import {
  assertActiveTenantAssignee,
  assertTenantOwnsContact,
  assertTenantOwnsCustomer,
  assertTenantOwnsOrThrow,
  assertTenantOwnsTicketCategory,
} from './tenant-guards'

// ── Domain object types ───────────────────────────────────────────────────────

export interface TicketObject {
  id: string
  tenant_id: string
  customer_id: string | null
  contact_id: string | null
  title: string
  description: string
  status: 'open' | 'in_progress' | 'pending_customer' | 'resolved' | 'closed'
  priority: 'low' | 'medium' | 'high' | 'urgent'
  category_id: string | null
  assignee_id: string | null
  source: 'web' | 'email' | 'telegram' | 'whatsapp' | 'portal'
  external_id: string | null
  external_thread_id: string | null
  resolved_at: string | null
  closed_at: string | null
  due_at: string | null
  first_response_at: string | null
  sla_breached: boolean
  created_at: string
  updated_at: string
}

export interface TicketDetailObject extends TicketObject {
  customer_name: string | null
  contact_name: string | null
  contact_email: string | null
  assignee_name: string | null
  assignee_avatar_url: string | null
}

export interface TicketMessageObject {
  id: string
  ticket_id: string
  tenant_id: string
  author_type: 'staff' | 'customer' | 'system'
  author_id: string | null
  author_name: string | null
  content: string
  source: 'web' | 'email' | 'telegram' | 'whatsapp' | 'portal'
  created_at: string
}

export type PlatformSupportMessageRow = Record<string, unknown> & {
  id: string
  ticketId: string
  tenantId: string
  authorType: 'staff' | 'customer' | 'system'
  authorId: string | null
  authorName: string | null
  content: string
  source: 'web' | 'email' | 'telegram' | 'whatsapp' | 'portal'
  createdAt: Date
}

export interface TicketMessageAttachmentObject {
  id: string
  message_id: string
  tenant_id: string
  filename: string
  r2_key: string
  url: string
  size_bytes: number
  mime_type: string
  created_at: string
}

export interface TicketCategoryObject {
  id: string
  tenant_id: string
  name: string
  color: string | null
  created_at: string
}

export interface TicketListPage {
  rows: TicketObject[]
  nextCursor: string | null
}

// ── Cursor helpers ────────────────────────────────────────────────────────────

export function encodeTicketCursor(createdAt: string, id: string): string {
  return Buffer.from(JSON.stringify({ createdAt, id })).toString('base64url')
}

export function decodeTicketCursor(cursor: string): { createdAt: string; id: string } | null {
  try {
    const raw = Buffer.from(cursor, 'base64url').toString('utf8')
    const parsed = JSON.parse(raw) as { createdAt: string; id: string }
    if (typeof parsed.createdAt !== 'string' || typeof parsed.id !== 'string') return null
    return parsed
  } catch {
    return null
  }
}

// ── Serializers ───────────────────────────────────────────────────────────────

function serializeTicket(row: typeof tickets.$inferSelect): TicketObject {
  return {
    id: row.id,
    tenant_id: row.tenantId,
    customer_id: row.customerId ?? null,
    contact_id: row.contactId ?? null,
    title: row.title,
    description: row.description,
    status: row.status as TicketObject['status'],
    priority: row.priority as TicketObject['priority'],
    category_id: row.categoryId ?? null,
    assignee_id: row.assigneeId ?? null,
    source: row.source as TicketObject['source'],
    external_id: row.externalId ?? null,
    external_thread_id: row.externalThreadId ?? null,
    resolved_at: row.resolvedAt?.toISOString() ?? null,
    closed_at: row.closedAt?.toISOString() ?? null,
    due_at: row.dueAt?.toISOString() ?? null,
    first_response_at: row.firstResponseAt?.toISOString() ?? null,
    sla_breached: row.slaBreached ?? false,
    created_at: row.createdAt.toISOString(),
    updated_at: row.updatedAt.toISOString(),
  }
}

function serializeMessage(row: typeof ticketMessages.$inferSelect): TicketMessageObject {
  return {
    id: row.id,
    ticket_id: row.ticketId,
    tenant_id: row.tenantId,
    author_type: row.authorType as TicketMessageObject['author_type'],
    author_id: row.authorId ?? null,
    author_name: row.authorName ?? null,
    content: row.content,
    source: row.source as TicketMessageObject['source'],
    created_at: row.createdAt.toISOString(),
  }
}

function serializeAttachment(
  row: typeof ticketMessageAttachments.$inferSelect,
): TicketMessageAttachmentObject {
  return {
    id: row.id,
    message_id: row.messageId,
    tenant_id: row.tenantId,
    filename: row.filename,
    r2_key: row.r2Key,
    url: row.url,
    size_bytes: row.sizeBytes,
    mime_type: row.mimeType,
    created_at: row.createdAt.toISOString(),
  }
}

function serializeCategory(row: typeof ticketCategories.$inferSelect): TicketCategoryObject {
  return {
    id: row.id,
    tenant_id: row.tenantId,
    name: row.name,
    color: row.color ?? null,
    created_at: row.createdAt.toISOString(),
  }
}

// ── listTickets ───────────────────────────────────────────────────────────────

export interface TicketFilters {
  status?: string | string[]
  priority?: string | string[]
  assignee_id?: string
  category_id?: string
  customer_id?: string
  source?: string | string[]
  sla_breached?: boolean
  q?: string
}

export async function listTickets(
  db: Db,
  tenantId: string,
  filters: TicketFilters = {},
  cursor?: string,
  limit = 50,
): Promise<TicketListPage> {
  const conditions = [
    eq(tickets.tenantId, tenantId),
    isNull(tickets.deletedAt),
  ]

  if (filters.status) {
    const statuses = Array.isArray(filters.status) ? filters.status : [filters.status]
    conditions.push(sql`${tickets.status} = ANY(ARRAY[${sql.join(statuses.map((s) => sql`${s}`), sql`, `)}])`)
  }
  if (filters.priority) {
    const priorities = Array.isArray(filters.priority) ? filters.priority : [filters.priority]
    conditions.push(sql`${tickets.priority} = ANY(ARRAY[${sql.join(priorities.map((p) => sql`${p}`), sql`, `)}])`)
  }
  if (filters.assignee_id) {
    conditions.push(eq(tickets.assigneeId, filters.assignee_id))
  }
  if (filters.category_id) {
    conditions.push(eq(tickets.categoryId, filters.category_id))
  }
  if (filters.customer_id) {
    conditions.push(eq(tickets.customerId, filters.customer_id))
  }
  if (filters.source) {
    const sources = Array.isArray(filters.source) ? filters.source : [filters.source]
    conditions.push(sql`${tickets.source} = ANY(ARRAY[${sql.join(sources.map((s) => sql`${s}`), sql`, `)}])`)
  }
  if (filters.sla_breached !== undefined) {
    conditions.push(eq(tickets.slaBreached, filters.sla_breached))
  }
  if (filters.q) {
    conditions.push(ilike(tickets.title, `%${filters.q}%`))
  }

  if (cursor) {
    const decoded = decodeTicketCursor(cursor)
    if (decoded) {
      conditions.push(
        sql`(${tickets.createdAt}, ${tickets.id}) < (${decoded.createdAt}::timestamptz, ${decoded.id}::uuid)`,
      )
    }
  }

  const pageSize = Math.min(limit, 100)
  const rows = await db
    .select()
    .from(tickets)
    .where(and(...conditions))
    .orderBy(desc(tickets.createdAt), desc(tickets.id))
    .limit(pageSize + 1)

  const hasMore = rows.length > pageSize
  const slice = hasMore ? rows.slice(0, pageSize) : rows
  const last = slice[slice.length - 1]
  const nextCursor =
    hasMore && last ? encodeTicketCursor(last.createdAt.toISOString(), last.id) : null

  return { rows: slice.map(serializeTicket), nextCursor }
}

// ── getTicket ─────────────────────────────────────────────────────────────────

function serializeTicketDetail(row: {
  ticket: typeof tickets.$inferSelect
  customerName: string | null
  contactName: string | null
  contactEmail: string | null
  assigneeName: string | null
  assigneeAvatarUrl: string | null
}): TicketDetailObject {
  return {
    ...serializeTicket(row.ticket),
    customer_name: row.customerName ?? null,
    contact_name: row.contactName ?? null,
    contact_email: row.contactEmail ?? null,
    assignee_name: row.assigneeName ?? null,
    assignee_avatar_url: row.assigneeAvatarUrl ?? null,
  }
}

export async function getTicket(
  db: Db,
  tenantId: string,
  id: string,
): Promise<TicketDetailObject | null> {
  const [row] = await db
    .select({
      ticket: tickets,
      customerName: customers.name,
      contactName: customerContacts.name,
      contactEmail: customerContacts.email,
      assigneeName: users.name,
      assigneeAvatarUrl: users.avatarUrl,
    })
    .from(tickets)
    .leftJoin(customers, and(eq(customers.id, tickets.customerId), eq(customers.tenantId, tenantId)))
    .leftJoin(
      customerContacts,
      and(eq(customerContacts.id, tickets.contactId), eq(customerContacts.tenantId, tenantId)),
    )
    // users is global (no tenant_id); assignee tenant membership is enforced on write via assertActiveTenantAssignee
    .leftJoin(users, eq(users.id, tickets.assigneeId))
    .where(and(eq(tickets.tenantId, tenantId), eq(tickets.id, id), isNull(tickets.deletedAt)))
    .limit(1)
  return row ? serializeTicketDetail(row) : null
}

// ── findTicketByThread ────────────────────────────────────────────────────────
// Used by inbound email/telegram handlers to route replies to existing tickets.

export async function findTicketByThread(
  db: Db,
  tenantId: string,
  source: string,
  externalThreadId: string,
): Promise<TicketObject | null> {
  const [row] = await db
    .select()
    .from(tickets)
    .where(
      and(
        eq(tickets.tenantId, tenantId),
        eq(tickets.source, source),
        eq(tickets.externalThreadId, externalThreadId),
        isNull(tickets.deletedAt),
        sql`${tickets.status} NOT IN ('closed')`,
      ),
    )
    .orderBy(desc(tickets.createdAt))
    .limit(1)
  return row ? serializeTicket(row) : null
}

// ── createTicket ──────────────────────────────────────────────────────────────

import type { CreateTicketInput } from '../validation/support'

export async function createTicket(
  db: Db,
  tenantId: string,
  input: CreateTicketInput,
): Promise<TicketObject> {
  assertTenantOwnsOrThrow(
    'customer_id',
    await assertTenantOwnsCustomer(db, tenantId, input.customer_id),
  )
  assertTenantOwnsOrThrow(
    'contact_id',
    await assertTenantOwnsContact(db, tenantId, input.contact_id, input.customer_id),
  )
  assertTenantOwnsOrThrow(
    'category_id',
    await assertTenantOwnsTicketCategory(db, tenantId, input.category_id),
  )
  assertTenantOwnsOrThrow(
    'assignee_id',
    await assertActiveTenantAssignee(db, tenantId, input.assignee_id),
  )

  const [row] = await db
    .insert(tickets)
    .values({
      tenantId,
      customerId: input.customer_id ?? null,
      contactId: input.contact_id ?? null,
      title: input.title,
      description: input.description ?? '',
      priority: input.priority ?? 'medium',
      categoryId: input.category_id ?? null,
      source: input.source ?? 'web',
      externalId: input.external_id ?? null,
      externalThreadId: input.external_thread_id ?? null,
    })
    .returning()
  if (!row) throw new Error('Ticket not found after insert')
  return serializeTicket(row)
}

// ── updateTicket ──────────────────────────────────────────────────────────────

import type { UpdateTicketInput } from '../validation/support'

export async function updateTicket(
  db: Db,
  tenantId: string,
  id: string,
  patch: UpdateTicketInput,
): Promise<TicketObject> {
  if (patch.category_id !== undefined) {
    assertTenantOwnsOrThrow(
      'category_id',
      await assertTenantOwnsTicketCategory(db, tenantId, patch.category_id),
    )
  }
  if (patch.assignee_id !== undefined) {
    assertTenantOwnsOrThrow(
      'assignee_id',
      await assertActiveTenantAssignee(db, tenantId, patch.assignee_id),
    )
  }

  const now = new Date()
  const setValues: Partial<typeof tickets.$inferInsert> = { updatedAt: now }

  if (patch.title !== undefined) setValues.title = patch.title
  if (patch.description !== undefined) setValues.description = patch.description
  if (patch.priority !== undefined) setValues.priority = patch.priority
  if (patch.category_id !== undefined) setValues.categoryId = patch.category_id ?? null
  if (patch.assignee_id !== undefined) setValues.assigneeId = patch.assignee_id ?? null

  if (patch.status !== undefined) {
    setValues.status = patch.status
    if (patch.status === 'resolved') setValues.resolvedAt = now
    if (patch.status === 'closed') setValues.closedAt = now
    // Re-open: clear resolved/closed timestamps when going back to in_progress
    if (patch.status === 'in_progress' || patch.status === 'open') {
      setValues.resolvedAt = null
      setValues.closedAt = null
    }
  }

  const [row] = await db
    .update(tickets)
    .set(setValues)
    .where(and(eq(tickets.tenantId, tenantId), eq(tickets.id, id), isNull(tickets.deletedAt)))
    .returning()

  if (!row) throw new Error('Ticket not found')
  return serializeTicket(row)
}

// ── softDeleteTicket ──────────────────────────────────────────────────────────

export async function softDeleteTicket(
  db: Db,
  tenantId: string,
  id: string,
): Promise<void> {
  await db
    .update(tickets)
    .set({ deletedAt: new Date(), updatedAt: new Date() })
    .where(and(eq(tickets.tenantId, tenantId), eq(tickets.id, id), isNull(tickets.deletedAt)))
}

// ── getTicketMessageById ──────────────────────────────────────────────────────

export async function getTicketMessageById(
  db: Db,
  tenantId: string,
  messageId: string,
): Promise<{ id: string } | null> {
  const [row] = await db
    .select({ id: ticketMessages.id })
    .from(ticketMessages)
    .where(
      and(
        eq(ticketMessages.tenantId, tenantId),
        eq(ticketMessages.id, messageId),
        isNull(ticketMessages.deletedAt),
      ),
    )
    .limit(1)

  return row ?? null
}

export async function getTicketMessage(
  db: Db,
  tenantId: string,
  ticketId: string,
  messageId: string,
): Promise<TicketMessageObject | null> {
  const [row] = await db
    .select()
    .from(ticketMessages)
    .where(
      and(
        eq(ticketMessages.tenantId, tenantId),
        eq(ticketMessages.ticketId, ticketId),
        eq(ticketMessages.id, messageId),
        isNull(ticketMessages.deletedAt),
      ),
    )
    .limit(1)

  return row ? serializeMessage(row) : null
}

/** Re-open pending_customer tickets when the customer sends a new inbound message. */
export async function autoReopenOnCustomerReply(
  db: Db,
  tenantId: string,
  ticket: TicketObject,
): Promise<void> {
  if (ticket.status !== 'pending_customer') return

  await updateTicket(db, tenantId, ticket.id, { status: 'in_progress' })
  await createTicketMessage(db, tenantId, ticket.id, {
    author_type: 'system',
    content: 'Status changed from pending_customer to in_progress — customer replied',
    source: 'web',
  })
}

// ── listTicketMessages ────────────────────────────────────────────────────────

export async function listTicketMessages(
  db: Db,
  tenantId: string,
  ticketId: string,
): Promise<TicketMessageObject[]> {
  const rows = await db
    .select()
    .from(ticketMessages)
    .where(
      and(
        eq(ticketMessages.tenantId, tenantId),
        eq(ticketMessages.ticketId, ticketId),
        isNull(ticketMessages.deletedAt),
      ),
    )
    .orderBy(asc(ticketMessages.createdAt))
  return rows.map(serializeMessage)
}

export async function updatePlatformSupportMessageMetadata(
  db: Db | DbTx,
  args: {
    messageId: string
    tenantId: string
    authorId: string | null
    authorName: string | null
    source: PlatformSupportMessageRow['source']
  },
): Promise<void> {
  await db.execute(sql`
    UPDATE support_messages
    SET
      tenant_id = ${args.tenantId}::uuid,
      author_id = ${args.authorId}::uuid,
      author_name = ${args.authorName},
      source = ${args.source},
      deleted_at = NULL
    WHERE id = ${args.messageId}::uuid
  `)
}

export async function createPlatformSupportMessage(
  db: Db | DbTx,
  args: {
    tenantId: string
    ticketId: string
    authorType: PlatformSupportMessageRow['authorType']
    authorId: string | null
    authorName: string | null
    content: string
    source: PlatformSupportMessageRow['source']
  },
): Promise<PlatformSupportMessageRow> {
  const rows = await db.execute<PlatformSupportMessageRow>(sql`
    INSERT INTO support_messages (
      parent_type,
      parent_id,
      tenant_id,
      author_type,
      author_id,
      author_name,
      body,
      visibility,
      source,
      deleted_at
    )
    VALUES (
      'ticket',
      ${args.ticketId},
      ${args.tenantId}::uuid,
      ${args.authorType},
      ${args.authorId}::uuid,
      ${args.authorName},
      ${args.content},
      ${args.source},
      ${args.source},
      NULL
    )
    RETURNING
      id,
      parent_id AS "ticketId",
      tenant_id AS "tenantId",
      author_type AS "authorType",
      author_id AS "authorId",
      author_name AS "authorName",
      body AS "content",
      source,
      created_at AS "createdAt"
  `)

  const [row] = rows
  if (!row) throw new Error('Platform support message not found after insert')
  return row
}

export async function listPlatformSupportMessages(
  db: Db | DbTx,
  args: {
    tenantId: string
    ticketId: string
  },
): Promise<PlatformSupportMessageRow[]> {
  return db.execute<PlatformSupportMessageRow>(sql`
    SELECT
      id,
      parent_id AS "ticketId",
      tenant_id AS "tenantId",
      author_type AS "authorType",
      author_id AS "authorId",
      author_name AS "authorName",
      body AS "content",
      source,
      created_at AS "createdAt"
    FROM support_messages
    WHERE parent_type = 'ticket'
      AND parent_id = ${args.ticketId}
      AND tenant_id = ${args.tenantId}::uuid
      AND deleted_at IS NULL
    ORDER BY created_at ASC
  `)
}

// ── createTicketMessage ───────────────────────────────────────────────────────

export interface CreateTicketMessageInput {
  author_type: 'staff' | 'customer' | 'system'
  author_id?: string | null
  author_name?: string | null
  content: string
  source?: 'web' | 'email' | 'telegram' | 'whatsapp' | 'portal'
}

export async function createTicketMessage(
  db: Db | DbTx,
  tenantId: string,
  ticketId: string,
  input: CreateTicketMessageInput,
): Promise<TicketMessageObject> {
  const [row] = await db
    .insert(ticketMessages)
    .values({
      ticketId,
      tenantId,
      authorType: input.author_type,
      authorId: input.author_id ?? null,
      authorName: input.author_name ?? null,
      content: input.content,
      source: input.source ?? 'web',
    })
    .returning()
  if (!row) throw new Error('Ticket message not found after insert')
  return serializeMessage(row)
}

// ── softDeleteTicketMessage ───────────────────────────────────────────────────

export async function softDeleteTicketMessage(
  db: Db,
  tenantId: string,
  ticketId: string,
  messageId: string,
): Promise<void> {
  await db
    .update(ticketMessages)
    .set({ deletedAt: new Date() })
    .where(
      and(
        eq(ticketMessages.tenantId, tenantId),
        eq(ticketMessages.ticketId, ticketId),
        eq(ticketMessages.id, messageId),
        isNull(ticketMessages.deletedAt),
      ),
    )
}

// ── addTicketMessageAttachment ────────────────────────────────────────────────

export interface AddTicketAttachmentInput {
  message_id: string
  filename: string
  r2_key: string
  url: string
  size_bytes: number
  mime_type: string
}

export async function addTicketMessageAttachment(
  db: Db | DbTx,
  tenantId: string,
  input: AddTicketAttachmentInput,
): Promise<TicketMessageAttachmentObject> {
  const [row] = await db
    .insert(ticketMessageAttachments)
    .values({
      messageId: input.message_id,
      tenantId,
      filename: input.filename,
      r2Key: input.r2_key,
      url: input.url,
      sizeBytes: input.size_bytes,
      mimeType: input.mime_type,
    })
    .returning()
  if (!row) throw new Error('Attachment not found after insert')
  return serializeAttachment(row)
}

// ── getTicketCategoryById ─────────────────────────────────────────────────────

export async function getTicketCategoryById(
  db: Db,
  tenantId: string,
  id: string,
): Promise<TicketCategoryObject | null> {
  const [row] = await db
    .select()
    .from(ticketCategories)
    .where(and(eq(ticketCategories.tenantId, tenantId), eq(ticketCategories.id, id)))
    .limit(1)

  return row ? serializeCategory(row) : null
}

// ── listTicketCategories ──────────────────────────────────────────────────────

export async function listTicketCategories(
  db: Db,
  tenantId: string,
): Promise<TicketCategoryObject[]> {
  const rows = await db
    .select()
    .from(ticketCategories)
    .where(eq(ticketCategories.tenantId, tenantId))
    .orderBy(asc(ticketCategories.name))
  return rows.map(serializeCategory)
}

// ── createTicketCategory ──────────────────────────────────────────────────────

import type { CreateTicketCategoryInput } from '../validation/support'

export async function createTicketCategory(
  db: Db,
  tenantId: string,
  input: CreateTicketCategoryInput,
): Promise<TicketCategoryObject> {
  const [row] = await db
    .insert(ticketCategories)
    .values({
      tenantId,
      name: input.name,
      color: input.color ?? null,
    })
    .returning()
  if (!row) throw new Error('Category not found after insert')
  return serializeCategory(row)
}

// ── deleteTicketCategory ──────────────────────────────────────────────────────

export async function deleteTicketCategory(
  db: Db,
  tenantId: string,
  id: string,
): Promise<void> {
  // Nullify category_id on tickets first (cascade-style via query; FK is set null on delete)
  await db
    .delete(ticketCategories)
    .where(and(eq(ticketCategories.tenantId, tenantId), eq(ticketCategories.id, id)))
}

/** Write a system message describing a ticket status transition. */
export async function appendStatusTransitionMessage(
  db: Db | DbTx,
  tenantId: string,
  ticketId: string,
  toStatus: TicketObject['status'],
  actorLabel: string,
): Promise<void> {
  await createTicketMessage(db, tenantId, ticketId, {
    author_type: 'system',
    content: `Status changed to ${toStatus} by ${actorLabel}`,
    source: 'web',
  })
}

// ── closeStaleResolvedTickets (cron) ──────────────────────────────────────────

/**
 * Auto-close tickets that have been in 'resolved' status longer than each
 * tenant's `ticket_auto_close_days` window. Writes a system transition message
 * per closed ticket. Called by the ticket-close-stale cron route.
 */
export async function closeStaleResolvedTickets(db: Db): Promise<number> {
  const staleRows = await db.execute<{ id: string; tenant_id: string }>(sql`
    UPDATE tickets AS t
    SET
      status = 'closed',
      closed_at = now(),
      updated_at = now()
    FROM tenant_settings AS ts
    WHERE t.tenant_id = ts.tenant_id
      AND t.status = 'resolved'
      AND t.resolved_at IS NOT NULL
      AND t.resolved_at <= now() - (ts.ticket_auto_close_days || ' days')::interval
      AND t.deleted_at IS NULL
    RETURNING t.id, t.tenant_id
  `)

  for (const row of staleRows) {
    await appendStatusTransitionMessage(db, row.tenant_id, row.id, 'closed', 'system')
  }

  return staleRows.length
}

// ── Re-export validation schemas (required by no-raw-drizzle-from-routes) ─────

export {
  createTicketSchema,
  updateTicketSchema,
  replyTicketSchema,
  createTicketCategorySchema,
  ticketFiltersSchema,
} from '../validation/support'
export type {
  CreateTicketInput,
  UpdateTicketInput,
  ReplyTicketInput,
  CreateTicketCategoryInput,
  TicketFiltersInput,
} from '../validation/support'