/**
 * Proposal expiry cron query helpers — proposal-expiry-deadline (wave-12).
 *
 * These helpers are called by the cron route handlers.
 * All DB access is through this file — routes MUST NOT import raw Drizzle tables.
 */
import { eq, and, inArray, sql, ne } from 'drizzle-orm'
import type { Db } from '../client'

type Tx = Parameters<Parameters<Db['transaction']>[0]>[0]
type DbOrTx = Db | Tx

import { proposals } from '../schema/proposals'
import { notifications } from '../schema/communications'
import { leads, leadActivities } from '../schema/marketing'
import { tenantMemberships, roles } from '../schema/rbac'

// ── Auto-expiry (06:00 UTC cron) ─────────────────────────────────────────────

export interface ExpiredProposalRow {
  id: string
  tenantId: string
  title: string | null
  leadId: string | null
}

/**
 * Bulk-expire all SENT/VIEWED proposals whose expires_at is in the past.
 * Returns the expired rows for notification processing.
 */
export async function expireOverdueProposals(db: Db): Promise<ExpiredProposalRow[]> {
  const now = new Date()
  const rows = await db
    .update(proposals)
    .set({ status: 'expired', updatedAt: now })
    .where(
      and(
        sql`${proposals.expiresAt} IS NOT NULL`,
        sql`${proposals.expiresAt} < ${now.toISOString()}`,
        inArray(proposals.status, ['sent', 'viewed']),
      ),
    )
    .returning({
      id: proposals.id,
      tenantId: proposals.tenantId,
      title: proposals.name,
      leadId: proposals.leadId,
    })

  return rows.map((r) => ({
    id: r.id,
    tenantId: r.tenantId,
    title: r.title,
    leadId: r.leadId ?? null,
  }))
}

/**
 * Resolve OWNER/ADMIN user IDs for a tenant (for notification fanout).
 */
export async function getOwnerAdminUserIds(db: Db, tenantId: string): Promise<string[]> {
  const rows = await db
    .select({ userId: tenantMemberships.userId })
    .from(tenantMemberships)
    .innerJoin(roles, eq(roles.id, tenantMemberships.roleId))
    .where(
      and(
        eq(tenantMemberships.tenantId, tenantId),
        inArray(roles.name, ['OWNER', 'ADMIN']),
      ),
    )
  return rows.map((r) => r.userId)
}

/**
 * Insert a proposal_expired notification for one user.
 */
export async function insertProposalExpiredNotification(
  db: DbOrTx,
  tenantId: string,
  userId: string,
  proposalId: string,
  title: string,
): Promise<void> {
  await db.insert(notifications).values({
    tenantId,
    userId,
    type: 'proposal_expired',
    titleKey: 'notifications.proposal_expired.title',
    bodyKey: 'notifications.proposal_expired.body',
    params: { title },
    entityType: 'proposal',
    entityId: proposalId,
  })
}

/**
 * Insert a proposal_expiring notification for one user.
 */
export async function insertProposalExpiringNotification(
  db: Db,
  tenantId: string,
  userId: string,
  proposalId: string,
  title: string,
  days: number,
): Promise<void> {
  await db.insert(notifications).values({
    tenantId,
    userId,
    type: 'proposal_expiring',
    titleKey: 'notifications.proposal_expiring.title',
    bodyKey: 'notifications.proposal_expiring.body',
    params: { title, days },
    entityType: 'proposal',
    entityId: proposalId,
  })
}

/**
 * Lead state-exit: if lead is in PROPOSAL and has no other live proposal,
 * revert to QUALIFIED and insert a lead_activities row.
 * Returns true if the lead was reverted.
 */
export async function revertLeadToQualifiedIfNoLiveProposal(
  db: DbOrTx,
  tenantId: string,
  leadId: string,
  expiredProposalId: string,
): Promise<boolean> {
  const now = new Date()

  const [lead] = await db
    .select({ id: leads.id, stage: leads.stage })
    .from(leads)
    .where(and(eq(leads.id, leadId), eq(leads.tenantId, tenantId)))
    .limit(1)

  if (!lead || lead.stage !== 'PROPOSAL') return false

  // Check if another live proposal exists
  const [otherLive] = await db
    .select({ id: proposals.id })
    .from(proposals)
    .where(
      and(
        eq(proposals.leadId, leadId),
        inArray(proposals.status, ['sent', 'viewed']),
        ne(proposals.id, expiredProposalId),
      ),
    )
    .limit(1)

  if (otherLive) return false

  await db
    .update(leads)
    .set({ stage: 'QUALIFIED', updatedAt: now })
    .where(and(eq(leads.tenantId, tenantId), eq(leads.id, lead.id)))

  await db.insert(leadActivities).values({
    tenantId,
    leadId: lead.id,
    type: 'stage_changed',
    content: 'Proposal expired — lead returned to Qualified for follow-up.',
    metadata: { from: 'PROPOSAL', to: 'QUALIFIED', reason: 'proposal_expired' },
  })

  return true
}

// ── Reminder (08:00 UTC cron) ─────────────────────────────────────────────────

export interface ExpiringProposalRow {
  id: string
  tenantId: string
  title: string | null
  expiresAt: Date
}

/**
 * Find proposals expiring within the next 3 days that haven't been reminded recently.
 */
export async function findExpiringProposals(db: Db): Promise<ExpiringProposalRow[]> {
  const now = new Date()
  const in3Days = new Date(now.getTime() + 3 * 24 * 60 * 60 * 1000)

  // Dedup: exclude proposals already reminded in the last 3 days
  const rows = await db
    .select({
      id: proposals.id,
      tenantId: proposals.tenantId,
      title: proposals.name,
      expiresAt: proposals.expiresAt,
    })
    .from(proposals)
    .where(
      and(
        sql`${proposals.expiresAt} IS NOT NULL`,
        sql`${proposals.expiresAt} >= ${now.toISOString()}`,
        sql`${proposals.expiresAt} <= ${in3Days.toISOString()}`,
        inArray(proposals.status, ['sent', 'viewed']),
        sql`${proposals.id} NOT IN (
          SELECT entity_id FROM notifications
          WHERE type = 'proposal_expiring'
            AND entity_id IS NOT NULL
            AND created_at > ${new Date(now.getTime() - 3 * 24 * 60 * 60 * 1000).toISOString()}
        )`,
      ),
    )

  return rows
    .filter((r) => r.expiresAt !== null)
    .map((r) => ({
      id: r.id,
      tenantId: r.tenantId,
      title: r.title,
      expiresAt: r.expiresAt as Date,
    }))
}