/**
 * Email sequences query helpers — email-marketing-sequences (wave-10 leaf 6).
 *
 * Tables:
 *   email_sequences           — sequence definitions
 *   email_sequence_steps      — per-step content + delay
 *   email_sequence_enrollments — per-contact enrollment tracking
 *
 * All helpers are tenant-filtered. Routes import via @zync/db/queries barrel.
 */
import { and, eq, lte, desc } from 'drizzle-orm'
import { z } from 'zod'
import type { Db } from '../client'
import {
  emailSequences,
  emailSequenceSteps,
  emailSequenceEnrollments,
} from '../schema/proposals'
import { auditLog } from './_audit-forward'
import { assertTenantOwnsOrThrow, assertTenantOwnsLead } from './tenant-guards'

// ── Zod schemas ───────────────────────────────────────────────────────────────

export const createSequenceSchema = z.object({
  name: z.string().min(1).max(255),
  triggerType: z.enum(['manual', 'lead_created', 'proposal_sent', 'invoice_overdue']).optional(),
  isActive: z.boolean().optional(),
})

export const updateSequenceSchema = createSequenceSchema.partial()

export const createSequenceStepSchema = z.object({
  stepNumber: z.number().int().positive(),
  delayDays: z.number().int().min(0),
  subject: z.string().min(1).max(500),
  bodyHtml: z.string().min(1),
})

export const updateSequenceStepSchema = createSequenceStepSchema.partial()

export const enrollInSequenceSchema = z.object({
  contactEmail: z.string().email(),
  leadId: z.string().uuid().optional(),
})

export type CreateSequenceInput = z.infer<typeof createSequenceSchema>
export type UpdateSequenceInput = z.infer<typeof updateSequenceSchema>
export type CreateSequenceStepInput = z.infer<typeof createSequenceStepSchema>
export type UpdateSequenceStepInput = z.infer<typeof updateSequenceStepSchema>
export type EnrollInSequenceInput = z.infer<typeof enrollInSequenceSchema>

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

export interface EmailSequenceObject {
  id: string
  tenantId: string
  name: string
  triggerType: string
  isActive: boolean
  createdAt: string
}

export interface EmailSequenceStepObject {
  id: string
  sequenceId: string
  tenantId: string
  stepNumber: number
  delayDays: number
  subject: string
  bodyHtml: string
  createdAt: string
}

export interface EmailSequenceEnrollmentObject {
  id: string
  sequenceId: string
  tenantId: string
  contactEmail: string
  leadId: string | null
  currentStep: number
  status: string
  enrolledAt: string
  nextSendAt: string | null
  createdAt: string
}

function serializeSequence(row: typeof emailSequences.$inferSelect): EmailSequenceObject {
  return {
    id: row.id,
    tenantId: row.tenantId,
    name: row.name,
    triggerType: row.triggerType,
    isActive: row.isActive,
    createdAt: row.createdAt.toISOString(),
  }
}

function serializeStep(row: typeof emailSequenceSteps.$inferSelect): EmailSequenceStepObject {
  return {
    id: row.id,
    sequenceId: row.sequenceId,
    tenantId: row.tenantId,
    stepNumber: row.stepNumber,
    delayDays: row.delayDays,
    subject: row.subject,
    bodyHtml: row.bodyHtml,
    createdAt: row.createdAt.toISOString(),
  }
}

function serializeEnrollment(
  row: typeof emailSequenceEnrollments.$inferSelect,
): EmailSequenceEnrollmentObject {
  return {
    id: row.id,
    sequenceId: row.sequenceId,
    tenantId: row.tenantId,
    contactEmail: row.contactEmail,
    leadId: row.leadId ?? null,
    currentStep: row.currentStep,
    status: row.status,
    enrolledAt: row.enrolledAt.toISOString(),
    nextSendAt: row.nextSendAt ? row.nextSendAt.toISOString() : null,
    createdAt: row.createdAt.toISOString(),
  }
}

// ── Sequences CRUD ────────────────────────────────────────────────────────────

export async function listSequences(
  db: Db,
  tenantId: string,
): Promise<EmailSequenceObject[]> {
  const rows = await db
    .select()
    .from(emailSequences)
    .where(eq(emailSequences.tenantId, tenantId))
    .orderBy(desc(emailSequences.createdAt))
  return rows.map(serializeSequence)
}

export async function getSequence(
  db: Db,
  tenantId: string,
  id: string,
): Promise<EmailSequenceObject | null> {
  const [row] = await db
    .select()
    .from(emailSequences)
    .where(and(eq(emailSequences.tenantId, tenantId), eq(emailSequences.id, id)))
  return row ? serializeSequence(row) : null
}

export async function createSequence(
  db: Db,
  tenantId: string,
  userId: string,
  input: CreateSequenceInput,
): Promise<EmailSequenceObject> {
  return db.transaction(async (tx) => {
    const [row] = await tx
      .insert(emailSequences)
      .values({
        tenantId,
        name: input.name,
        triggerType: input.triggerType ?? 'manual',
        isActive: input.isActive ?? true,
      })
      .returning()
    if (!row) throw new Error('Insert failed')

    await tx.insert(auditLog).values({
      tenantId,
      actorId: userId,
      actorType: 'user',
      entityType: 'email_sequence',
      entityId: row.id,
      action: 'email_sequence.created',
      changes: null,
    })

    return serializeSequence(row)
  })
}

export async function updateSequence(
  db: Db,
  tenantId: string,
  userId: string,
  id: string,
  input: UpdateSequenceInput,
): Promise<EmailSequenceObject> {
  return db.transaction(async (tx) => {
    const updates: Partial<typeof emailSequences.$inferInsert> = {}
    if (input.name !== undefined) updates.name = input.name
    if (input.triggerType !== undefined) updates.triggerType = input.triggerType
    if (input.isActive !== undefined) updates.isActive = input.isActive

    const [row] = await tx
      .update(emailSequences)
      .set(updates)
      .where(and(eq(emailSequences.tenantId, tenantId), eq(emailSequences.id, id)))
      .returning()
    if (!row) throw new Error('Sequence not found')

    await tx.insert(auditLog).values({
      tenantId,
      actorId: userId,
      actorType: 'user',
      entityType: 'email_sequence',
      entityId: id,
      action: 'email_sequence.updated',
      changes: null,
    })

    return serializeSequence(row)
  })
}

export async function deleteSequence(
  db: Db,
  tenantId: string,
  id: string,
): Promise<void> {
  await db
    .delete(emailSequences)
    .where(and(eq(emailSequences.tenantId, tenantId), eq(emailSequences.id, id)))
}

// ── Steps CRUD ────────────────────────────────────────────────────────────────

export async function listSequenceSteps(
  db: Db,
  tenantId: string,
  sequenceId: string,
): Promise<EmailSequenceStepObject[]> {
  const rows = await db
    .select()
    .from(emailSequenceSteps)
    .where(
      and(
        eq(emailSequenceSteps.tenantId, tenantId),
        eq(emailSequenceSteps.sequenceId, sequenceId),
      ),
    )
    .orderBy(emailSequenceSteps.stepNumber)
  return rows.map(serializeStep)
}

export async function createSequenceStep(
  db: Db,
  tenantId: string,
  sequenceId: string,
  input: CreateSequenceStepInput,
): Promise<EmailSequenceStepObject> {
  const [row] = await db
    .insert(emailSequenceSteps)
    .values({
      tenantId,
      sequenceId,
      stepNumber: input.stepNumber,
      delayDays: input.delayDays,
      subject: input.subject,
      bodyHtml: input.bodyHtml,
    })
    .returning()
  if (!row) throw new Error('Insert failed')
  return serializeStep(row)
}

export async function updateSequenceStep(
  db: Db,
  tenantId: string,
  stepId: string,
  input: UpdateSequenceStepInput,
): Promise<EmailSequenceStepObject> {
  const updates: Partial<typeof emailSequenceSteps.$inferInsert> = {}
  if (input.stepNumber !== undefined) updates.stepNumber = input.stepNumber
  if (input.delayDays !== undefined) updates.delayDays = input.delayDays
  if (input.subject !== undefined) updates.subject = input.subject
  if (input.bodyHtml !== undefined) updates.bodyHtml = input.bodyHtml

  const [row] = await db
    .update(emailSequenceSteps)
    .set(updates)
    .where(and(eq(emailSequenceSteps.tenantId, tenantId), eq(emailSequenceSteps.id, stepId)))
    .returning()
  if (!row) throw new Error('Step not found')
  return serializeStep(row)
}

export async function deleteSequenceStep(
  db: Db,
  tenantId: string,
  stepId: string,
): Promise<void> {
  await db
    .delete(emailSequenceSteps)
    .where(and(eq(emailSequenceSteps.tenantId, tenantId), eq(emailSequenceSteps.id, stepId)))
}

// ── Enrollments ───────────────────────────────────────────────────────────────

export async function listEnrollments(
  db: Db,
  tenantId: string,
  sequenceId: string,
): Promise<EmailSequenceEnrollmentObject[]> {
  const rows = await db
    .select()
    .from(emailSequenceEnrollments)
    .where(
      and(
        eq(emailSequenceEnrollments.tenantId, tenantId),
        eq(emailSequenceEnrollments.sequenceId, sequenceId),
      ),
    )
    .orderBy(desc(emailSequenceEnrollments.enrolledAt))
  return rows.map(serializeEnrollment)
}

export async function enrollInSequence(
  db: Db,
  tenantId: string,
  userId: string,
  sequenceId: string,
  contactEmail: string,
  leadId?: string,
): Promise<EmailSequenceEnrollmentObject> {
  assertTenantOwnsOrThrow(
    'leadId',
    await assertTenantOwnsLead(db, tenantId, leadId),
  )

  return db.transaction(async (tx) => {
    // Load first step to compute nextSendAt
    const [firstStep] = await tx
      .select()
      .from(emailSequenceSteps)
      .where(
        and(
          eq(emailSequenceSteps.tenantId, tenantId),
          eq(emailSequenceSteps.sequenceId, sequenceId),
        ),
      )
      .orderBy(emailSequenceSteps.stepNumber)
      .limit(1)

    const now = new Date()
    let nextSendAt: Date | null = null
    if (firstStep) {
      nextSendAt = new Date(now)
      nextSendAt.setDate(nextSendAt.getDate() + firstStep.delayDays)
    }

    const [row] = await tx
      .insert(emailSequenceEnrollments)
      .values({
        tenantId,
        sequenceId,
        contactEmail,
        leadId: leadId ?? null,
        currentStep: 0,
        status: 'active',
        enrolledAt: now,
        nextSendAt,
      })
      .returning()
    if (!row) throw new Error('Insert failed')

    await tx.insert(auditLog).values({
      tenantId,
      actorId: userId,
      actorType: 'user',
      entityType: 'email_sequence_enrollment',
      entityId: row.id,
      action: 'email_sequence_enrollment.created',
      changes: { sequenceId: [null, sequenceId], contactEmail: [null, contactEmail] },
    })

    return serializeEnrollment(row)
  })
}

export async function getDueEnrollments(
  db: Db,
  tenantId: string,
): Promise<EmailSequenceEnrollmentObject[]> {
  const now = new Date()
  const rows = await db
    .select()
    .from(emailSequenceEnrollments)
    .where(
      and(
        eq(emailSequenceEnrollments.tenantId, tenantId),
        eq(emailSequenceEnrollments.status, 'active'),
        lte(emailSequenceEnrollments.nextSendAt, now),
      ),
    )
  return rows.map(serializeEnrollment)
}

/** Cross-tenant: used by cron only — no tenantId filter. Limit 500. */
export async function getAllDueEnrollments(
  db: Db,
): Promise<EmailSequenceEnrollmentObject[]> {
  const now = new Date()
  const rows = await db
    .select()
    .from(emailSequenceEnrollments)
    .where(
      and(
        eq(emailSequenceEnrollments.status, 'active'),
        lte(emailSequenceEnrollments.nextSendAt, now),
      ),
    )
    .limit(500)
  return rows.map(serializeEnrollment)
}

export async function advanceEnrollment(
  db: Db,
  tenantId: string,
  enrollmentId: string,
): Promise<EmailSequenceEnrollmentObject> {
  return db.transaction(async (tx) => {
    const [enrollment] = await tx
      .select()
      .from(emailSequenceEnrollments)
      .where(
        and(
          eq(emailSequenceEnrollments.tenantId, tenantId),
          eq(emailSequenceEnrollments.id, enrollmentId),
        ),
      )
      .limit(1)

    if (!enrollment) throw new Error('Enrollment not found')

    const nextStepNumber = enrollment.currentStep + 1

    // Find the next step
    const allSteps = await tx
      .select()
      .from(emailSequenceSteps)
      .where(
        and(
          eq(emailSequenceSteps.tenantId, tenantId),
          eq(emailSequenceSteps.sequenceId, enrollment.sequenceId),
        ),
      )
      .orderBy(emailSequenceSteps.stepNumber)

    const nextStep = allSteps.find((s) => s.stepNumber > enrollment.currentStep)

    let newStatus = enrollment.status
    let newNextSendAt: Date | null = null

    if (!nextStep) {
      // No more steps — complete
      newStatus = 'completed'
    } else {
      const now = new Date()
      newNextSendAt = new Date(now)
      newNextSendAt.setDate(newNextSendAt.getDate() + nextStep.delayDays)
    }

    const [row] = await tx
      .update(emailSequenceEnrollments)
      .set({
        currentStep: nextStepNumber,
        status: newStatus,
        nextSendAt: newNextSendAt,
      })
      .where(
        and(
          eq(emailSequenceEnrollments.tenantId, tenantId),
          eq(emailSequenceEnrollments.id, enrollmentId),
        ),
      )
      .returning()
    if (!row) throw new Error('Update failed')

    await tx.insert(auditLog).values({
      tenantId,
      actorId: null,
      actorType: 'system',
      entityType: 'email_sequence_enrollment',
      entityId: enrollmentId,
      action: 'email_sequence_enrollment.advanced',
      changes: { status: [enrollment.status, newStatus] },
    })

    return serializeEnrollment(row)
  })
}

export async function countEnrollments(
  db: Db,
  tenantId: string,
  sequenceId: string,
): Promise<number> {
  const rows = await db
    .select()
    .from(emailSequenceEnrollments)
    .where(
      and(
        eq(emailSequenceEnrollments.tenantId, tenantId),
        eq(emailSequenceEnrollments.sequenceId, sequenceId),
      ),
    )
  return rows.length
}