/**
 * Recurring expense template query helpers — recurring-expenses (wave-10).
 *
 * All public functions are tenant-scoped.
 * The cron runner calls generateDueExpenses() which IS tenant-scoped.
 *
 * nextDueAt advancement rules:
 *   daily   → add 1 day
 *   weekly  → add 7 days
 *   monthly → add 1 calendar month; clamp to last valid day of month
 *   yearly  → add 1 calendar year; clamp
 *   dayOfMonth clamping: stored value (e.g. 31) preserved; at generation
 *   time only the calendar result is clamped to the last valid day.
 */
import { eq, and, lte, isNull as _isNull, sql } from 'drizzle-orm'
import { z } from 'zod'
import type { Db } from '../client'
import { recurringExpenseTemplates } from '../schema/recurring-expenses'
import { expenses } from '../schema/expenses'
import { tenants } from '../schema/tenants'
import { auditLog } from './_audit-forward'
import { resolveApprovalStatus } from './expense-approvals'

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

export const createRecurringExpenseSchema = z.object({
  description: z.string().min(1).max(200),
  amount: z.number().positive(),
  currency: z.string().length(3).default('ILS'),
  categoryId: z.string().nullable().optional(),
  vendorName: z.string().max(200).nullable().optional(),
  interval: z.enum(['daily', 'weekly', 'monthly', 'yearly']),
  dayOfMonth: z.number().int().min(1).max(31).nullable().optional(),
  nextDueAt: z.string().datetime({ offset: true }),
  isActive: z.boolean().default(true),
})

export const updateRecurringExpenseSchema = createRecurringExpenseSchema.partial()

export type CreateRecurringExpenseInput = z.infer<typeof createRecurringExpenseSchema>
export type UpdateRecurringExpenseInput = z.infer<typeof updateRecurringExpenseSchema>

// ── Types ─────────────────────────────────────────────────────────────────────

export type RecurringExpenseTemplateRow = typeof recurringExpenseTemplates.$inferSelect

// ── Date advancement helper ───────────────────────────────────────────────────

function advanceNextDueAt(
  current: Date,
  interval: string,
  dayOfMonth?: number | null,
): Date {
  const d = new Date(current)

  switch (interval) {
    case 'daily':
      d.setUTCDate(d.getUTCDate() + 1)
      break
    case 'weekly':
      d.setUTCDate(d.getUTCDate() + 7)
      break
    case 'monthly': {
      d.setUTCMonth(d.getUTCMonth() + 1)
      if (dayOfMonth) {
        const lastDay = new Date(Date.UTC(d.getUTCFullYear(), d.getUTCMonth() + 1, 0)).getUTCDate()
        d.setUTCDate(Math.min(dayOfMonth, lastDay))
      }
      break
    }
    case 'yearly': {
      d.setUTCFullYear(d.getUTCFullYear() + 1)
      if (dayOfMonth) {
        const lastDay = new Date(Date.UTC(d.getUTCFullYear(), d.getUTCMonth() + 1, 0)).getUTCDate()
        d.setUTCDate(Math.min(dayOfMonth, lastDay))
      }
      break
    }
  }

  return d
}

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

/**
 * List all recurring expense templates for a tenant, ordered newest first.
 */
export async function listRecurringExpenses(
  db: Db,
  tenantId: string,
): Promise<RecurringExpenseTemplateRow[]> {
  return db
    .select()
    .from(recurringExpenseTemplates)
    .where(eq(recurringExpenseTemplates.tenantId, tenantId))
    .orderBy(sql`${recurringExpenseTemplates.createdAt} DESC`)
}

/**
 * Create a new recurring expense template.
 */
export async function createRecurringExpense(
  db: Db,
  tenantId: string,
  userId: string,
  data: CreateRecurringExpenseInput,
): Promise<RecurringExpenseTemplateRow> {
  const parsed = createRecurringExpenseSchema.parse(data)

  const [row] = await db
    .insert(recurringExpenseTemplates)
    .values({
      tenantId,
      createdBy: userId,
      description: parsed.description,
      amount: String(parsed.amount),
      currency: parsed.currency,
      categoryId: parsed.categoryId ?? null,
      vendorName: parsed.vendorName ?? null,
      interval: parsed.interval,
      dayOfMonth: parsed.dayOfMonth ?? null,
      nextDueAt: new Date(parsed.nextDueAt),
      isActive: parsed.isActive,
    })
    .returning()

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

/**
 * Update a recurring expense template (tenant-scoped).
 */
export async function updateRecurringExpense(
  db: Db,
  tenantId: string,
  _userId: string,
  id: string,
  data: UpdateRecurringExpenseInput,
): Promise<RecurringExpenseTemplateRow> {
  const parsed = updateRecurringExpenseSchema.parse(data)

  const updatePayload: Partial<typeof recurringExpenseTemplates.$inferInsert> = {}
  if (parsed.description !== undefined) updatePayload.description = parsed.description
  if (parsed.amount !== undefined) updatePayload.amount = String(parsed.amount)
  if (parsed.currency !== undefined) updatePayload.currency = parsed.currency
  if (parsed.categoryId !== undefined) updatePayload.categoryId = parsed.categoryId ?? null
  if (parsed.vendorName !== undefined) updatePayload.vendorName = parsed.vendorName ?? null
  if (parsed.interval !== undefined) updatePayload.interval = parsed.interval
  if (parsed.dayOfMonth !== undefined) updatePayload.dayOfMonth = parsed.dayOfMonth ?? null
  if (parsed.nextDueAt !== undefined) updatePayload.nextDueAt = new Date(parsed.nextDueAt)
  if (parsed.isActive !== undefined) updatePayload.isActive = parsed.isActive

  const [row] = await db
    .update(recurringExpenseTemplates)
    .set(updatePayload)
    .where(
      and(
        eq(recurringExpenseTemplates.id, id),
        eq(recurringExpenseTemplates.tenantId, tenantId),
      ),
    )
    .returning()

  if (!row) throw new Error('Recurring expense template not found')
  return row
}

/**
 * Deactivate (soft-disable) a recurring expense template.
 */
export async function deactivateRecurringExpense(
  db: Db,
  tenantId: string,
  _userId: string,
  id: string,
): Promise<RecurringExpenseTemplateRow> {
  const [row] = await db
    .update(recurringExpenseTemplates)
    .set({ isActive: false })
    .where(
      and(
        eq(recurringExpenseTemplates.id, id),
        eq(recurringExpenseTemplates.tenantId, tenantId),
      ),
    )
    .returning()

  if (!row) throw new Error('Recurring expense template not found')
  return row
}

/**
 * Generate expense records for all due templates in a given tenant.
 * Advances nextDueAt after each successful generation.
 *
 * Returns a summary of { generated, skipped, errors }.
 */
export async function generateDueExpenses(
  db: Db,
  tenantId: string,
): Promise<{ generated: number; skipped: number; errors: number }> {
  const now = new Date()

  const dueTemplates = await db
    .select()
    .from(recurringExpenseTemplates)
    .where(
      and(
        eq(recurringExpenseTemplates.tenantId, tenantId),
        eq(recurringExpenseTemplates.isActive, true),
        lte(recurringExpenseTemplates.nextDueAt, now),
      ),
    )

  let generated = 0
  let skipped = 0
  let errors = 0

  for (const template of dueTemplates) {
    try {
      const [tenantRow] = await db
        .select({ tier: tenants.tier })
        .from(tenants)
        .where(eq(tenants.id, template.tenantId))
        .limit(1)
      const tier = tenantRow?.tier ?? 'freelancer'
      const approvalStatus = await resolveApprovalStatus({
        tenantId: template.tenantId,
        amount: parseFloat(template.amount),
        tier,
        db,
      })

      await db.transaction(async (tx) => {
        const [newExpense] = await tx.insert(expenses).values({
          tenantId: template.tenantId,
          createdBy: template.createdBy,
          r2Key: '',
          fileName: `recurring-${template.description.slice(0, 40)}.txt`,
          fileType: 'pdf',
          fileSizeBytes: 0,
          vendorName: template.vendorName ?? null,
          currency: template.currency,
          amount: template.amount,
          expenseDate: now.toISOString().slice(0, 10),
          expenseCategory: template.categoryId ?? null,
          source: 'upload',
          sourceMetadata: {
            recurring: true,
            templateId: template.id,
            templateDescription: template.description,
          },
          status: 'COMPLETED',
          approvalStatus,
          notes: `Auto-generated from recurring template: ${template.description}`,
        }).returning({ id: expenses.id })

        await tx.insert(auditLog).values({
          tenantId: template.tenantId,
          actorId: template.createdBy,
          actorType: 'system',
          entityType: 'expense',
          entityId: newExpense?.id ?? template.id,
          action: 'expense.recurring_generated',
        })

        // Advance nextDueAt
        const next = advanceNextDueAt(
          new Date(template.nextDueAt),
          template.interval,
          template.dayOfMonth,
        )

        await tx
          .update(recurringExpenseTemplates)
          .set({ nextDueAt: next })
          .where(
            and(
              eq(recurringExpenseTemplates.tenantId, template.tenantId),
              eq(recurringExpenseTemplates.id, template.id),
            ),
          )

        await tx.insert(auditLog).values({
          tenantId: template.tenantId,
          actorId: template.createdBy,
          actorType: 'system',
          entityType: 'recurring_expense_template',
          entityId: template.id,
          action: 'recurring_expense.next_due_advanced',
        })
      })

      generated++
    } catch {
      errors++
    }
  }

  return { generated, skipped, errors }
}