/**
 * Contract signatory management query helpers — multi-signatory-coordination (wave-12).
 *
 * All writes include a paired contractAuditLog insert in the same transaction.
 * Route files import from '@zync/db/queries'.
 */
import { and, eq, sql } from 'drizzle-orm'
import type { Db, DbTx } from '../client'
import { contracts, contractSignatories, contractAuditLog } from '../schema/contracts'
import type { ContractRow, ContractSignatoryRow } from '../schema/contracts'

// ── Re-export row types for routes ─────────────────────────────────────────────

export type { ContractSignatoryRow }

// ── Queries ───────────────────────────────────────────────────────────────────

/**
 * Load a contract with tenant scope. Returns null if not found or wrong tenant.
 */
export async function getContractTenantScoped(
  db: Db,
  contractId: string,
  tenantId: string,
): Promise<ContractRow | null> {
  const rows = await db
    .select()
    .from(contracts)
    .where(and(eq(contracts.id, contractId), eq(contracts.tenantId, tenantId)))
    .limit(1)
  return rows[0] ?? null
}

/**
 * List all signatories for a contract, ordered by signing order then creation time.
 */
export async function listSignatories(
  db: Db,
  contractId: string,
  tenantId: string,
): Promise<ContractSignatoryRow[]> {
  return db
    .select()
    .from(contractSignatories)
    .where(
      and(
        eq(contractSignatories.contractId, contractId),
        eq(contractSignatories.tenantId, tenantId),
      ),
    )
    .orderBy(contractSignatories.order, contractSignatories.createdAt)
}

/**
 * Load a single signatory by id, with contract + tenant scope.
 */
export async function getSignatory(
  db: Db,
  sigId: string,
  contractId: string,
  tenantId: string,
): Promise<ContractSignatoryRow | null> {
  const rows = await db
    .select()
    .from(contractSignatories)
    .where(
      and(
        eq(contractSignatories.id, sigId),
        eq(contractSignatories.contractId, contractId),
        eq(contractSignatories.tenantId, tenantId),
      ),
    )
    .limit(1)
  return rows[0] ?? null
}

/**
 * Check for duplicate email on the same contract (for collision detection before update).
 */
export async function checkSignatoryEmailCollision(
  db: Db,
  contractId: string,
  email: string,
): Promise<boolean> {
  const rows = await db
    .select({ id: contractSignatories.id })
    .from(contractSignatories)
    .where(
      and(
        eq(contractSignatories.contractId, contractId),
        eq(contractSignatories.email, email),
      ),
    )
    .limit(1)
  return rows.length > 0
}

// ── Bulk upsert (DRAFT only) ──────────────────────────────────────────────────

export interface BulkUpsertSignatoryInput {
  id?: string
  email: string
  name?: string | null
  order: number
}

/**
 * Bulk upsert signatories for a DRAFT contract. Deletes removed rows, upserts the rest.
 * Appends a contractAuditLog row in the same transaction.
 */
export async function bulkUpsertSignatories(
  db: Db,
  contractId: string,
  tenantId: string,
  actorId: string,
  signatories: BulkUpsertSignatoryInput[],
): Promise<ContractSignatoryRow[]> {
  return db.transaction(async (tx) => {
    // Load existing signatories
    const existing = await tx
      .select({ id: contractSignatories.id })
      .from(contractSignatories)
      .where(
        and(
          eq(contractSignatories.contractId, contractId),
          eq(contractSignatories.tenantId, tenantId),
        ),
      )

    const incomingIds = new Set(signatories.filter((s) => s.id).map((s) => s.id!))
    const toDelete = existing.filter((e) => !incomingIds.has(e.id))

    for (const del of toDelete) {
      await tx.delete(contractSignatories).where(
        and(eq(contractSignatories.tenantId, tenantId), eq(contractSignatories.id, del.id)),
      )
    }

    const upserted: ContractSignatoryRow[] = []

    for (const sig of signatories) {
      if (sig.id) {
        const [row] = await tx
          .update(contractSignatories)
          .set({ name: sig.name, email: sig.email, order: sig.order })
          .where(
            and(
              eq(contractSignatories.id, sig.id),
              eq(contractSignatories.contractId, contractId),
              eq(contractSignatories.tenantId, tenantId),
            ),
          )
          .returning()
        if (row) upserted.push(row)
      } else {
        const [row] = await tx
          .insert(contractSignatories)
          .values({
            contractId,
            tenantId,
            email: sig.email,
            name: sig.name,
            order: sig.order,
            token: crypto.randomUUID(),
          })
          .returning()
        if (row) upserted.push(row)
      }
    }

    await tx.insert(contractAuditLog).values({
      contractId,
      tenantId,
      event: 'sent',
      actorType: 'staff',
      actorId,
      metadata: { action: 'signatories_updated', orders: signatories.map((s) => s.order) },
    })

    return upserted
  })
}

// ── Reminder (manual, 24h-gated) ─────────────────────────────────────────────

/**
 * Bump reminder_sent_at + reminder_count and append audit log row.
 */
export async function markManualReminderSent(
  db: Db,
  sigId: string,
  contractId: string,
  tenantId: string,
  actorId: string,
  now: Date,
): Promise<ContractSignatoryRow | undefined> {
  const [updated] = await db.transaction(async (tx) => {
    const [row] = await tx
      .update(contractSignatories)
      .set({
        reminderSentAt: now,
        reminderCount: sql`${contractSignatories.reminderCount} + 1`,
      })
      .where(and(eq(contractSignatories.tenantId, tenantId), eq(contractSignatories.id, sigId)))
      .returning()

    await tx.insert(contractAuditLog).values({
      contractId,
      tenantId,
      event: 'reminder_sent',
      actorType: 'staff',
      actorId,
      metadata: { signatoryId: sigId },
    })

    return [row]
  })
  return updated
}

// ── Resend (regenerate token, OWNER/ADMIN only) ────────────────────────────────

/**
 * Regenerate signatory token + expiry and append audit log row.
 */
export async function resendSignatoryLink(
  db: Db,
  sigId: string,
  contractId: string,
  tenantId: string,
  actorId: string,
  newToken: string,
  newExpiry: Date,
): Promise<void> {
  await db.transaction(async (tx) => {
    await tx
      .update(contractSignatories)
      .set({ token: newToken, tokenExpiresAt: newExpiry })
      .where(and(eq(contractSignatories.tenantId, tenantId), eq(contractSignatories.id, sigId)))

    await tx.insert(contractAuditLog).values({
      contractId,
      tenantId,
      event: 'link_resent',
      actorType: 'staff',
      actorId,
      metadata: { signatoryId: sigId },
    })
  })
}

// ── Replace signatory (name/email, SENT/VIEWED only) ──────────────────────────

/**
 * Update a signatory's email/name/token (replacement) and append audit log row.
 */
export async function replaceSignatory(
  db: Db,
  sigId: string,
  contractId: string,
  tenantId: string,
  actorId: string,
  oldEmail: string,
  newEmail: string,
  newName: string | null | undefined,
  newToken: string,
  newExpiry: Date,
): Promise<void> {
  await db.transaction(async (tx) => {
    await tx
      .update(contractSignatories)
      .set({ email: newEmail, name: newName, token: newToken, tokenExpiresAt: newExpiry })
      .where(and(eq(contractSignatories.tenantId, tenantId), eq(contractSignatories.id, sigId)))

    await tx.insert(contractAuditLog).values({
      contractId,
      tenantId,
      event: 'signatory_replaced',
      actorType: 'staff',
      actorId,
      metadata: { signatoryId: sigId, oldEmail, newEmail },
    })
  })
}

// ── Cron helpers ──────────────────────────────────────────────────────────────

/** Bump reminder_sent_at + reminder_count for a signatory (used by cron). */
export async function markSignatoryReminderSent(
  tx: DbTx,
  tenantId: string,
  signatoryId: string,
  now: Date,
): Promise<void> {
  await tx
    .update(contractSignatories)
    .set({
      reminderSentAt: now,
      reminderCount: sql`${contractSignatories.reminderCount} + 1`,
    })
    .where(and(eq(contractSignatories.tenantId, tenantId), eq(contractSignatories.id, signatoryId)))
}

/** Append a contract audit log row (used by cron). */
export async function appendContractAuditLog(
  tx: DbTx,
  row: {
    contractId: string
    tenantId: string
    event: string
    actorType: string
    actorId: string
    metadata: Record<string, unknown>
  },
): Promise<void> {
  await tx.insert(contractAuditLog).values(row)
}