/**
 * Recurring invoice template query helpers — recurring-invoices (P061).
 *
 * All public functions are tenant-scoped. The cron runner (recurring-invoice-generator)
 * calls listDueRecurringTemplates() which is NOT scoped by tenant — it scans all
 * active templates across all tenants. All other helpers require tenantId.
 *
 * Tier logic is enforced in the API route layer, not here.
 *
 * next_generation_date advancement rules (computeNextDate):
 *   weekly    → add 7 days
 *   monthly   → add 1 calendar month; clamp to last valid day
 *   quarterly → add 3 calendar months; clamp
 *   yearly    → add 1 calendar year; clamp
 *   frequencyDay clamping: stored value (e.g. 31) preserved; at generation
 *   time only the calendar result is clamped to the last valid day of that month.
 *
 * Idempotency guard: before creating an invoice the cron checks
 * SELECT COUNT(*) FROM invoices WHERE recurring_template_id = ? AND recurring_period_start = ?
 * to skip duplicate generation on cron retries (exact period key, not created_at window).
 */
import { eq, and, lte, isNull, gte, or, sql, count, lt, desc } from 'drizzle-orm'
import { z } from 'zod'
import type { Db, DbTx } from '../client'
import { recurringInvoiceTemplates } from '../schema/recurring-invoices'
import { invoices } from '../schema/invoices'
import { assertTenantOwnsOrThrow, assertTenantOwnsCustomer } from './tenant-guards'
import { createInvoiceInTx } from './invoices'

// ── Zod validation schemas (exported for route layer consumption) ──────────────

export const recurringInvoiceLineItemSchema = z.object({
  description: z.string().min(1).max(500),
  quantity: z.number().positive(),
  unitPrice: z.number().nonnegative(),
  discountPct: z.number().min(0).max(100).default(0),
  taxable: z.boolean().default(true),
  position: z.number().int().nonnegative(),
})

export const createRecurringInvoiceSchema = z.object({
  customerId: z.string().uuid(),
  title: z.string().min(1).max(100),
  description: z.string().max(5000).optional().nullable(),
  lineItems: z.array(recurringInvoiceLineItemSchema).min(1),
  currency: z.enum(['ILS', 'USD', 'EUR']).default('ILS'),
  vatRate: z.number().min(0).max(1).default(0.18),
  frequency: z.enum(['weekly', 'monthly', 'quarterly', 'yearly']),
  frequencyDay: z.number().int().min(0).max(31).optional().nullable(),
  paymentTermsDays: z.union([z.literal(7), z.literal(14), z.literal(30), z.literal(45), z.literal(60)]).default(30),
  startDate: z.string().regex(/^\d{4}-\d{2}-\d{2}$/),
  endDate: z.string().regex(/^\d{4}-\d{2}-\d{2}$/).optional().nullable(),
  autoSend: z.boolean().default(false),
})

export const updateRecurringInvoiceSchema = createRecurringInvoiceSchema
  .partial()
  .extend({
    status: z.enum(['active', 'paused', 'completed', 'cancelled']).optional(),
  })

export const listRecurringInvoiceTemplatesSchema = z.object({
  cursor: z.string().optional(),
  limit: z.coerce.number().int().min(1).max(100).optional().default(50),
  status: z.enum(['active', 'paused', 'completed', 'cancelled']).optional(),
  customerId: z.string().uuid().optional(),
})

export type CreateRecurringInvoiceInput = z.infer<typeof createRecurringInvoiceSchema>
export type UpdateRecurringInvoiceInput = z.infer<typeof updateRecurringInvoiceSchema>

// ── Type helpers ──────────────────────────────────────────────────────────────

export type RecurringInvoiceTemplateRow = typeof recurringInvoiceTemplates.$inferSelect

type RecurringLineItem = {
  description: string
  quantity: number
  unitPrice: number
  discountPct: number
  taxable: boolean
  position: number
}

export type GenerateInvoiceFromTemplateResult = {
  invoiceId: string | null
  completed: boolean
  skipped: boolean
}

export interface RecurringInvoiceTemplateListPage {
  items: RecurringInvoiceTemplateRow[]
  nextCursor: string | null
  total: number
}

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

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

function decodeCursor(cursor: string): { id: string; created_at: string } | null {
  try {
    return JSON.parse(Buffer.from(cursor, 'base64url').toString('utf8'))
  } catch {
    return null
  }
}

// ── Date helpers (timezone-safe YYYY-MM-DD) ───────────────────────────────────

function parseDateOnly(dateStr: string): { y: number; m: number; d: number } {
  const parts = dateStr.split('-').map(Number)
  return { y: parts[0]!, m: parts[1]!, d: parts[2]! }
}

function formatDateOnly(y: number, m: number, d: number): string {
  return `${y}-${String(m).padStart(2, '0')}-${String(d).padStart(2, '0')}`
}

function lastDayOfMonth(y: number, m: number): number {
  return new Date(Date.UTC(y, m, 0)).getUTCDate()
}

function addMonthsSafe(y: number, m: number, delta: number): { y: number; m: number } {
  const total = y * 12 + (m - 1) + delta
  return { y: Math.floor(total / 12), m: (total % 12) + 1 }
}

function setDayInMonth(y: number, m: number, day: number): string {
  return formatDateOnly(y, m, Math.min(day, lastDayOfMonth(y, m)))
}

export function addDaysToDateString(dateStr: string, days: number): string {
  const d = new Date(`${dateStr}T00:00:00Z`)
  d.setUTCDate(d.getUTCDate() + days)
  return d.toISOString().slice(0, 10)
}

function resolveFrequencyDay(
  startDate: string,
  frequency: string,
  frequencyDay?: number | null,
): number {
  if (frequencyDay != null) return frequencyDay
  if (frequency === 'weekly') {
    return new Date(`${startDate}T00:00:00Z`).getUTCDay()
  }
  return parseDateOnly(startDate).d
}

function effectiveFrequencyDay(
  startDate: string,
  frequency: string,
  frequencyDay?: number | null,
): number {
  return resolveFrequencyDay(startDate, frequency, frequencyDay)
}

function snapToFrequencyDay(
  dateStr: string,
  frequency: string,
  startDate: string,
  frequencyDay?: number | null,
): string {
  const day = effectiveFrequencyDay(startDate, frequency, frequencyDay)
  const { y, m } = parseDateOnly(dateStr)

  switch (frequency) {
    case 'weekly': {
      const current = new Date(`${dateStr}T00:00:00Z`)
      const currentDow = current.getUTCDay()
      let delta = day - currentDow
      if (delta < 0) delta += 7
      current.setUTCDate(current.getUTCDate() + delta)
      return current.toISOString().slice(0, 10)
    }
    case 'monthly':
      return setDayInMonth(y, m, day)
    case 'quarterly': {
      const snapped = setDayInMonth(y, m, day)
      if (snapped >= dateStr) return snapped
      const next = addMonthsSafe(y, m, 1)
      return setDayInMonth(next.y, next.m, day)
    }
    case 'yearly': {
      const snapped = setDayInMonth(y, m, day)
      if (snapped >= dateStr) return snapped
      return setDayInMonth(y + 1, m, day)
    }
    default:
      return dateStr
  }
}

// ── Next-date computation ─────────────────────────────────────────────────────

/**
 * Compute the next generation date given frequency and the current date.
 * frequencyDay clamping: the calendar result is clamped to the last valid
 * day of the target month. The stored frequencyDay value is never mutated.
 */
export function computeNextDate(
  current: string, // YYYY-MM-DD
  frequency: string,
  startDate: string,
  frequencyDay?: number | null,
): string {
  const day = effectiveFrequencyDay(startDate, frequency, frequencyDay)
  const { y, m } = parseDateOnly(current)

  switch (frequency) {
    case 'weekly': {
      const d = new Date(`${current}T00:00:00Z`)
      d.setUTCDate(d.getUTCDate() + 7)
      return d.toISOString().slice(0, 10)
    }
    case 'monthly': {
      const next = addMonthsSafe(y, m, 1)
      return setDayInMonth(next.y, next.m, day)
    }
    case 'quarterly': {
      const next = addMonthsSafe(y, m, 3)
      return setDayInMonth(next.y, next.m, day)
    }
    case 'yearly': {
      const next = addMonthsSafe(y, m, 12)
      return setDayInMonth(next.y, next.m, day)
    }
    default: {
      const next = addMonthsSafe(y, m, 1)
      return setDayInMonth(next.y, next.m, day)
    }
  }
}

/**
 * Compute the initial next_generation_date for a new template.
 * First occurrence on/after start_date, snapped to frequency_day, then on/after today.
 */
export function computeInitialNextDate(
  startDate: string,
  frequency: string,
  frequencyDay?: number | null,
): string {
  const today = new Date().toISOString().slice(0, 10)
  let candidate = snapToFrequencyDay(startDate, frequency, startDate, frequencyDay)
  if (candidate < startDate) {
    candidate = computeNextDate(candidate, frequency, startDate, frequencyDay)
  }
  while (candidate < today) {
    candidate = computeNextDate(candidate, frequency, startDate, frequencyDay)
  }
  return candidate
}

/**
 * Return the next N scheduled dates for preview display.
 */
export function previewNextDates(
  startDate: string,
  frequency: string,
  frequencyDay: number | null | undefined,
  count: number = 3,
): string[] {
  const dates: string[] = []
  let current = computeInitialNextDate(startDate, frequency, frequencyDay)
  for (let i = 0; i < count; i++) {
    dates.push(current)
    current = computeNextDate(current, frequency, startDate, frequencyDay)
  }
  return dates
}

// ── Query helpers ─────────────────────────────────────────────────────────────

/**
 * List recurring invoice templates for a tenant, cursor-paginated newest first.
 */
export async function listRecurringInvoiceTemplates(
  db: Db,
  tenantId: string,
  opts: z.input<typeof listRecurringInvoiceTemplatesSchema> = {},
): Promise<RecurringInvoiceTemplateListPage> {
  const limit = Math.min(opts.limit ?? 50, 100)

  const countRows = await db
    .select({ total: count() })
    .from(recurringInvoiceTemplates)
    .where(
      and(
        eq(recurringInvoiceTemplates.tenantId, tenantId),
        opts.status ? eq(recurringInvoiceTemplates.status, opts.status) : undefined,
        opts.customerId ? eq(recurringInvoiceTemplates.customerId, opts.customerId) : undefined,
      ),
    )
  const totalCount = countRows[0]?.total ?? 0

  let cursorCondition: ReturnType<typeof and> | undefined
  if (opts.cursor) {
    const decoded = decodeCursor(opts.cursor)
    if (decoded) {
      cursorCondition = or(
        lt(recurringInvoiceTemplates.createdAt, new Date(decoded.created_at)),
        and(
          eq(recurringInvoiceTemplates.createdAt, new Date(decoded.created_at)),
          lt(recurringInvoiceTemplates.id, decoded.id),
        ),
      )
    }
  }

  const rows = await db
    .select()
    .from(recurringInvoiceTemplates)
    .where(
      and(
        eq(recurringInvoiceTemplates.tenantId, tenantId),
        opts.status ? eq(recurringInvoiceTemplates.status, opts.status) : undefined,
        opts.customerId ? eq(recurringInvoiceTemplates.customerId, opts.customerId) : undefined,
        cursorCondition,
      ),
    )
    .orderBy(desc(recurringInvoiceTemplates.createdAt), desc(recurringInvoiceTemplates.id))
    .limit(limit + 1)

  const hasMore = rows.length > limit
  const items = rows.slice(0, limit)
  const lastItem = items[items.length - 1]
  const nextCursor =
    hasMore && lastItem ? encodeCursor(lastItem.id, lastItem.createdAt.toISOString()) : null

  return {
    items,
    nextCursor,
    total: totalCount,
  }
}

/**
 * Get a single template by ID, scoped to tenant.
 */
export async function getRecurringInvoiceTemplate(
  db: Db,
  tenantId: string,
  id: string,
): Promise<RecurringInvoiceTemplateRow | null> {
  const [row] = await db
    .select()
    .from(recurringInvoiceTemplates)
    .where(
      and(
        eq(recurringInvoiceTemplates.id, id),
        eq(recurringInvoiceTemplates.tenantId, tenantId),
      ),
    )
    .limit(1)
  return row ?? null
}

/**
 * Count active templates for a tenant (used for Business tier limit check).
 */
export async function countActiveRecurringTemplates(
  db: Db,
  tenantId: string,
): Promise<number> {
  const result = await db
    .select({ count: sql<number>`count(*)::int` })
    .from(recurringInvoiceTemplates)
    .where(
      and(
        eq(recurringInvoiceTemplates.tenantId, tenantId),
        eq(recurringInvoiceTemplates.status, 'active'),
      ),
    )
  return result[0]?.count ?? 0
}

/**
 * Create a new recurring invoice template.
 * next_generation_date is computed from startDate and frequency.
 */
export async function createRecurringInvoiceTemplate(
  db: Db,
  tenantId: string,
  actorId: string,
  input: CreateRecurringInvoiceInput,
): Promise<RecurringInvoiceTemplateRow> {
  assertTenantOwnsOrThrow(
    'customerId',
    await assertTenantOwnsCustomer(db, tenantId, input.customerId),
  )

  const resolvedFrequencyDay = resolveFrequencyDay(
    input.startDate,
    input.frequency,
    input.frequencyDay ?? null,
  )

  const nextGenerationDate = computeInitialNextDate(
    input.startDate,
    input.frequency,
    resolvedFrequencyDay,
  )

  const [row] = await db
    .insert(recurringInvoiceTemplates)
    .values({
      tenantId,
      customerId: input.customerId,
      title: input.title,
      description: input.description ?? null,
      lineItems: input.lineItems,
      currency: input.currency,
      vatRate: String(input.vatRate),
      frequency: input.frequency,
      frequencyDay: resolvedFrequencyDay,
      paymentTermsDays: input.paymentTermsDays,
      startDate: input.startDate,
      endDate: input.endDate ?? null,
      autoSend: input.autoSend,
      autoCharge: false, // v2 placeholder
      nextGenerationDate,
      status: 'active',
      generatedCount: 0,
      createdBy: actorId,
    })
    .returning()

  if (!row) throw new Error('Failed to create recurring invoice template')
  return row
}

/**
 * Update a recurring invoice template. Recomputes next_generation_date if
 * startDate, frequency, or frequencyDay changes.
 */
export async function updateRecurringInvoiceTemplate(
  db: Db,
  tenantId: string,
  id: string,
  patch: UpdateRecurringInvoiceInput,
): Promise<RecurringInvoiceTemplateRow> {
  const existing = await getRecurringInvoiceTemplate(db, tenantId, id)
  if (!existing) throw new Error('Recurring invoice template not found')

  if (patch.customerId !== undefined) {
    assertTenantOwnsOrThrow(
      'customerId',
      await assertTenantOwnsCustomer(db, tenantId, patch.customerId),
    )
  }

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

  if (patch.customerId !== undefined) setValues.customerId = patch.customerId
  if (patch.title !== undefined) setValues.title = patch.title
  if (patch.description !== undefined) setValues.description = patch.description ?? null
  if (patch.lineItems !== undefined) setValues.lineItems = patch.lineItems
  if (patch.currency !== undefined) setValues.currency = patch.currency
  if (patch.vatRate !== undefined) setValues.vatRate = String(patch.vatRate)
  if (patch.frequency !== undefined) setValues.frequency = patch.frequency
  if (patch.frequencyDay !== undefined) {
    const startDate = patch.startDate ?? existing.startDate
    const frequency = patch.frequency ?? existing.frequency
    setValues.frequencyDay =
      patch.frequencyDay !== null
        ? patch.frequencyDay
        : resolveFrequencyDay(startDate, frequency, null)
  }
  if (patch.paymentTermsDays !== undefined) setValues.paymentTermsDays = patch.paymentTermsDays
  if (patch.startDate !== undefined) setValues.startDate = patch.startDate
  if (patch.endDate !== undefined) setValues.endDate = patch.endDate ?? null
  if (patch.autoSend !== undefined) setValues.autoSend = patch.autoSend
  if (patch.status !== undefined) setValues.status = patch.status

  // Recompute next generation date if schedule changed
  const scheduleChanged =
    patch.startDate !== undefined ||
    patch.frequency !== undefined ||
    patch.frequencyDay !== undefined
  if (scheduleChanged) {
    const startDate = patch.startDate ?? existing.startDate
    const frequency = patch.frequency ?? existing.frequency
    const frequencyDay = patch.frequencyDay !== undefined ? patch.frequencyDay : existing.frequencyDay
    setValues.nextGenerationDate = computeInitialNextDate(startDate, frequency, frequencyDay)
  }

  const [updated] = await db
    .update(recurringInvoiceTemplates)
    .set(setValues)
    .where(
      and(
        eq(recurringInvoiceTemplates.id, id),
        eq(recurringInvoiceTemplates.tenantId, tenantId),
      ),
    )
    .returning()

  if (!updated) throw new Error('Recurring invoice template not found')
  return updated
}

/**
 * Soft-cancel a recurring invoice template (sets status = 'cancelled').
 * Hard deletes are not allowed once at least one invoice has been generated.
 */
export async function cancelRecurringInvoiceTemplate(
  db: Db,
  tenantId: string,
  id: string,
): Promise<void> {
  await db
    .update(recurringInvoiceTemplates)
    .set({ status: 'cancelled', updatedAt: new Date() })
    .where(
      and(
        eq(recurringInvoiceTemplates.id, id),
        eq(recurringInvoiceTemplates.tenantId, tenantId),
      ),
    )
}

// ── Cron-runner helpers ───────────────────────────────────────────────────────

/**
 * List all active templates that are due for generation today or earlier.
 * Called by the cron handler — NOT tenant-scoped (scans all tenants).
 * Uses the idx_rit_next_gen partial index for efficiency.
 */
export async function listDueRecurringTemplates(
  db: Db,
  today: string, // YYYY-MM-DD
): Promise<RecurringInvoiceTemplateRow[]> {
  return db
    .select()
    .from(recurringInvoiceTemplates)
    .where(
      and(
        eq(recurringInvoiceTemplates.status, 'active'),
        lte(recurringInvoiceTemplates.nextGenerationDate, today),
        or(
          isNull(recurringInvoiceTemplates.endDate),
          gte(recurringInvoiceTemplates.endDate, today),
        ),
      ),
    )
}

/**
 * Idempotency guard: exact match on template + billing period (next_generation_date).
 */
export async function hasInvoiceForPeriod(
  db: Db | DbTx,
  templateId: string,
  recurringPeriodStart: string, // YYYY-MM-DD — template.nextGenerationDate at generation time
): Promise<boolean> {
  const result = await db
    .select({ count: count() })
    .from(invoices)
    .where(
      and(
        eq(invoices.recurringTemplateId, templateId),
        eq(invoices.recurringPeriodStart, recurringPeriodStart),
      ),
    )
  return (result[0]?.count ?? 0) > 0
}

function isRecurringPeriodUniqueViolation(err: unknown): boolean {
  if (!err || typeof err !== 'object') return false
  const pg = err as { code?: string; message?: string }
  return (
    pg.code === '23505' &&
    (pg.message?.includes('invoices_recurring_template_period_uniq') ?? false)
  )
}

async function skipExistingPeriodAndAdvance(
  tx: DbTx,
  template: RecurringInvoiceTemplateRow,
): Promise<GenerateInvoiceFromTemplateResult> {
  const { completed } = await advanceRecurringTemplate(tx, template)
  return { invoiceId: null, completed, skipped: true }
}

/**
 * Advance next_generation_date after a successful generation.
 * Also updates last_generated_at, increments generated_count, and
 * marks the template 'completed' if the new next date exceeds end_date.
 */
export async function advanceRecurringTemplate(
  db: Db | DbTx,
  template: RecurringInvoiceTemplateRow,
): Promise<{ completed: boolean; nextDate: string }> {
  const nextDate = computeNextDate(
    template.nextGenerationDate,
    template.frequency,
    template.startDate,
    template.frequencyDay,
  )

  const completed =
    template.endDate != null && nextDate > template.endDate
  const newStatus = completed ? 'completed' : template.status

  await db
    .update(recurringInvoiceTemplates)
    .set({
      nextGenerationDate: nextDate,
      lastGeneratedAt: new Date(),
      generatedCount: sql`${recurringInvoiceTemplates.generatedCount} + 1`,
      status: newStatus,
      updatedAt: new Date(),
    })
    .where(and(eq(recurringInvoiceTemplates.tenantId, template.tenantId), eq(recurringInvoiceTemplates.id, template.id)))

  return { completed, nextDate }
}

/**
 * Generate one invoice from a template and advance its schedule.
 * Idempotency guard uses exact (recurring_template_id, recurring_period_start) match.
 */
export async function generateInvoiceFromTemplate(
  db: Db,
  template: RecurringInvoiceTemplateRow,
  actorId: string,
  countryCode: string,
): Promise<GenerateInvoiceFromTemplateResult> {
  const generationDate = template.nextGenerationDate

  const lineItems = (template.lineItems ?? []) as RecurringLineItem[]
  if (lineItems.length === 0) {
    throw new Error('Recurring invoice template has no line items')
  }

  const dueDate = addDaysToDateString(generationDate, template.paymentTermsDays ?? 30)

  return db.transaction(async (tx) => {
    const alreadyExists = await hasInvoiceForPeriod(tx, template.id, generationDate)
    if (alreadyExists) {
      return skipExistingPeriodAndAdvance(tx, template)
    }

    try {
      const invoice = await createInvoiceInTx(
        tx,
        template.tenantId,
        actorId,
        {
          customerId: template.customerId,
          currency: template.currency,
          dueDate,
          source: 'manual',
          recurringTemplateId: template.id,
          recurringPeriodStart: generationDate,
          lines: lineItems.map((l) => ({
            description: l.description,
            quantity: l.quantity,
            unitPrice: l.unitPrice,
            discountPct: l.discountPct ?? 0,
            taxable: l.taxable ?? true,
            position: l.position,
          })),
        },
        countryCode,
      )

      if (invoice.skipped) {
        return skipExistingPeriodAndAdvance(tx, template)
      }

      const { completed } = await advanceRecurringTemplate(tx, template)
      return { invoiceId: invoice.id, completed, skipped: false }
    } catch (err) {
      if (isRecurringPeriodUniqueViolation(err)) {
        return skipExistingPeriodAndAdvance(tx, template)
      }
      throw err
    }
  })
}

/**
 * Resolve the active OWNER user for a tenant — used to notify on schedule
 * completion. Returns null when no active owner membership exists.
 */
export async function getTenantOwnerUserId(
  db: Db,
  tenantId: string,
): Promise<string | null> {
  const result = await db.execute(
    sql`SELECT tm.user_id
        FROM tenant_memberships tm
        JOIN roles r ON r.id = tm.role_id
        WHERE tm.tenant_id = ${tenantId}
          AND r.name = 'OWNER'
          AND tm.status = 'active'
        LIMIT 1`,
  )
  const row = (result as unknown as { user_id?: string }[])[0]
  return row?.user_id ?? null
}