/**
 * Lead scoring query helpers — lead-qualification-scoring (wave-14).
 *
 * Provides:
 *  - getLeadScoringCriteria:    read tenant weights from tenant_settings
 *  - upsertLeadScoringCriteria: update tenant weights in tenant_settings
 *  - computeAndSaveLeadScore:   full recalculation pipeline for one lead
 *  - getLeadScoreBreakdown:     read score + breakdown for one lead (GET endpoint)
 *  - listLeadsForScoreRefresh:  find stale leads for nightly cron
 *  - enqueueLeadScoreRecalc:    enqueue a single lead for async recalc
 */
import { eq, and, lt, isNull, or, sql } from 'drizzle-orm'
import { z } from 'zod'
import type { Db } from '../client'
import { leads, leadActivities } from '../schema/marketing'
import { tenantSettings } from '../schema/tenants'
import { isLeadScoringEnabled } from './settings-crm'
import { proposals } from '../schema/proposals'
import {
  computeLeadScore,
  DEFAULT_LEAD_SCORING_CRITERIA,
  type LeadScoringCriteria,
  type LeadScoreResult,
} from '../scoring/compute-lead-score'

// ── Zod schema ────────────────────────────────────────────────────────────────

export const leadScoringCriteriaSchema = z
  .object({
    estimated_value_set: z.number().int().nonnegative(),
    high_value: z.number().int().nonnegative(),
    stage_advancement: z.number().int().nonnegative(),
    recent_activity: z.number().int().nonnegative(),
    inbound_source: z.number().int().nonnegative(),
    company_present: z.number().int().nonnegative(),
    phone_present: z.number().int().nonnegative(),
    proposal_viewed: z.number().int().nonnegative(),
    high_value_threshold: z.number().positive(),
  })
  .strict() satisfies z.ZodType<LeadScoringCriteria>

export type { LeadScoringCriteria }
export { DEFAULT_LEAD_SCORING_CRITERIA }
export type { LeadScoreResult }

// ── Read scoring criteria ─────────────────────────────────────────────────────

export async function getLeadScoringCriteria(
  db: Db,
  tenantId: string,
): Promise<LeadScoringCriteria> {
  const [row] = await db
    .select({ leadScoringCriteria: tenantSettings.leadScoringCriteria })
    .from(tenantSettings)
    .where(eq(tenantSettings.tenantId, tenantId))
    .limit(1)

  if (!row || row.leadScoringCriteria == null) {
    return DEFAULT_LEAD_SCORING_CRITERIA
  }

  // Validate/coerce — fall back to defaults if the stored JSONB is malformed
  const parsed = leadScoringCriteriaSchema.safeParse(row.leadScoringCriteria)
  return parsed.success ? parsed.data : DEFAULT_LEAD_SCORING_CRITERIA
}

// ── Write scoring criteria ────────────────────────────────────────────────────

export async function upsertLeadScoringCriteria(
  db: Db,
  tenantId: string,
  criteria: LeadScoringCriteria,
): Promise<LeadScoringCriteria> {
  await db
    .update(tenantSettings)
    .set({ leadScoringCriteria: criteria, updatedAt: new Date() })
    .where(eq(tenantSettings.tenantId, tenantId))

  return criteria
}

// ── Core recalc pipeline ──────────────────────────────────────────────────────

/**
 * Read the lead + its latest activity + any VIEWED proposal, compute the score,
 * write `leads.score` + `leads.score_updated_at`, and return the result.
 */
export async function computeAndSaveLeadScore(
  db: Db,
  tenantId: string,
  leadId: string,
): Promise<LeadScoreResult & { leadId: string }> {
  if (!(await isLeadScoringEnabled(db, tenantId))) {
    return { score: 0, breakdown: [], leadId }
  }

  const criteria = await getLeadScoringCriteria(db, tenantId)

  // Read the lead row
  const [leadRow] = await db
    .select({
      id: leads.id,
      stage: leads.stage,
      source: leads.source,
      company: leads.company,
      phone: leads.phone,
      estimatedValue: leads.estimatedValue,
    })
    .from(leads)
    .where(and(eq(leads.id, leadId), eq(leads.tenantId, tenantId)))
    .limit(1)

  if (!leadRow) throw new Error(`Lead not found: ${leadId}`)

  // Latest activity
  const [latestActivity] = await db
    .select({ createdAt: leadActivities.createdAt })
    .from(leadActivities)
    .where(and(eq(leadActivities.leadId, leadId), eq(leadActivities.tenantId, tenantId)))
    .orderBy(sql`${leadActivities.createdAt} DESC`)
    .limit(1)

  // Viewed proposal
  const [viewedProposal] = await db
    .select({ id: proposals.id })
    .from(proposals)
    .where(
      and(
        eq(proposals.leadId, leadId),
        eq(proposals.tenantId, tenantId),
        sql`LOWER(${proposals.status}) = 'viewed'`,
      ),
    )
    .limit(1)

  const result = computeLeadScore(
    leadRow,
    latestActivity?.createdAt ?? null,
    viewedProposal != null,
    criteria,
  )

  // Persist
  await db
    .update(leads)
    .set({ score: result.score, scoreUpdatedAt: new Date() })
    .where(and(eq(leads.id, leadId), eq(leads.tenantId, tenantId)))

  return { ...result, leadId }
}

// ── Get breakdown (read-only) ─────────────────────────────────────────────────

export async function getLeadScoreBreakdown(
  db: Db,
  tenantId: string,
  leadId: string,
): Promise<LeadScoreResult & { scoreUpdatedAt: Date | null }> {
  const criteria = await getLeadScoringCriteria(db, tenantId)

  const [leadRow] = await db
    .select({
      id: leads.id,
      stage: leads.stage,
      source: leads.source,
      company: leads.company,
      phone: leads.phone,
      estimatedValue: leads.estimatedValue,
      scoreUpdatedAt: leads.scoreUpdatedAt,
    })
    .from(leads)
    .where(and(eq(leads.id, leadId), eq(leads.tenantId, tenantId)))
    .limit(1)

  if (!leadRow) throw new Error(`Lead not found: ${leadId}`)

  const [latestActivity] = await db
    .select({ createdAt: leadActivities.createdAt })
    .from(leadActivities)
    .where(and(eq(leadActivities.leadId, leadId), eq(leadActivities.tenantId, tenantId)))
    .orderBy(sql`${leadActivities.createdAt} DESC`)
    .limit(1)

  const [viewedProposal] = await db
    .select({ id: proposals.id })
    .from(proposals)
    .where(
      and(
        eq(proposals.leadId, leadId),
        eq(proposals.tenantId, tenantId),
        sql`LOWER(${proposals.status}) = 'viewed'`,
      ),
    )
    .limit(1)

  const result = computeLeadScore(
    leadRow,
    latestActivity?.createdAt ?? null,
    viewedProposal != null,
    criteria,
  )

  return { ...result, scoreUpdatedAt: leadRow.scoreUpdatedAt ?? null }
}

// ── Stale leads for nightly cron ──────────────────────────────────────────────

/**
 * Returns ids of active leads whose score_updated_at is older than 24 h
 * or has never been set.
 */
export async function listLeadsForScoreRefresh(
  db: Db,
  tenantId: string,
): Promise<Array<{ id: string; tenantId: string }>> {
  const cutoff = new Date(Date.now() - 24 * 60 * 60 * 1000)

  const rows = await db
    .select({ id: leads.id, tenantId: leads.tenantId })
    .from(leads)
    .where(
      and(
        eq(leads.tenantId, tenantId),
        isNull(leads.archivedAt),
        sql`${leads.stage} NOT IN ('LOST', 'WON')`,
        or(
          isNull(leads.scoreUpdatedAt),
          lt(leads.scoreUpdatedAt, cutoff),
        ),
      ),
    )
    .limit(500)

  return rows
}

/**
 * Returns ids of ALL active leads for a tenant (used by cron to sweep ALL tenants).
 */
export async function listAllActiveLeadTenantPairs(
  db: Db,
): Promise<Array<{ id: string; tenantId: string }>> {
  const cutoff = new Date(Date.now() - 24 * 60 * 60 * 1000)

  const rows = await db
    .select({ id: leads.id, tenantId: leads.tenantId })
    .from(leads)
    .leftJoin(tenantSettings, eq(tenantSettings.tenantId, leads.tenantId))
    .where(
      and(
        isNull(leads.archivedAt),
        sql`${leads.stage} NOT IN ('LOST', 'WON')`,
        sql`COALESCE(${tenantSettings.leadScoringEnabled}, true) = true`,
        or(
          isNull(leads.scoreUpdatedAt),
          lt(leads.scoreUpdatedAt, cutoff),
        ),
      ),
    )
    .limit(5000)

  return rows
}