/**
 * report_schedules query helpers — scheduled-reports (wave-15).
 *
 * All DB access for report schedules lives here.
 * Route files MUST NOT import raw Drizzle tables.
 */
import { eq, and, lte, asc } from 'drizzle-orm'
import type { Db } from '../client'
import { reportSchedules } from '../schema/report-schedules'

export type { ReportScheduleRow, NewReportSchedule } from '../schema/report-schedules'

export interface ReportRecipient {
  type: 'user' | 'email'
  user_id?: string
  email?: string
}

export interface CreateReportScheduleInput {
  tenantId: string
  createdBy: string
  name: string
  reportType: string
  format: 'xlsx' | 'pdf'
  frequency: 'daily' | 'weekly' | 'monthly' | 'quarterly'
  dayOfWeek?: number
  dayOfMonth?: number
  timeOfDay: string
  periodType: 'previous' | 'current' | 'ytd'
  reportParams: Record<string, unknown>
  recipients: ReportRecipient[]
  nextRunAt?: Date
}

export interface UpdateReportScheduleInput {
  name?: string
  reportType?: string
  format?: 'xlsx' | 'pdf'
  frequency?: 'daily' | 'weekly' | 'monthly' | 'quarterly'
  dayOfWeek?: number | null
  dayOfMonth?: number | null
  timeOfDay?: string
  periodType?: 'previous' | 'current' | 'ytd'
  reportParams?: Record<string, unknown>
  recipients?: ReportRecipient[]
  isActive?: boolean
  nextRunAt?: Date | null
  lastRunAt?: Date | null
}

export async function listReportSchedules(
  db: Db,
  tenantId: string,
): Promise<(typeof reportSchedules.$inferSelect)[]> {
  return db
    .select()
    .from(reportSchedules)
    .where(eq(reportSchedules.tenantId, tenantId))
    .orderBy(asc(reportSchedules.createdAt))
}

export async function getReportSchedule(
  db: Db,
  tenantId: string,
  id: string,
): Promise<typeof reportSchedules.$inferSelect | undefined> {
  const rows = await db
    .select()
    .from(reportSchedules)
    .where(and(eq(reportSchedules.id, id), eq(reportSchedules.tenantId, tenantId)))
    .limit(1)
  return rows[0]
}

export async function createReportSchedule(
  db: Db,
  input: CreateReportScheduleInput,
): Promise<typeof reportSchedules.$inferSelect> {
  const rows = await db
    .insert(reportSchedules)
    .values({
      tenantId: input.tenantId,
      createdBy: input.createdBy,
      name: input.name,
      reportType: input.reportType,
      format: input.format,
      frequency: input.frequency,
      dayOfWeek: input.dayOfWeek ?? null,
      dayOfMonth: input.dayOfMonth ?? null,
      timeOfDay: input.timeOfDay,
      periodType: input.periodType,
      reportParams: input.reportParams,
      recipients: input.recipients,
      isActive: true,
      nextRunAt: input.nextRunAt ?? null,
    })
    .returning()
  const row = rows[0]
  if (!row) throw new Error('Failed to create report schedule')
  return row
}

export async function updateReportSchedule(
  db: Db,
  tenantId: string,
  id: string,
  input: UpdateReportScheduleInput,
): Promise<typeof reportSchedules.$inferSelect | undefined> {
  const rows = await db
    .update(reportSchedules)
    .set({
      ...input,
      updatedAt: new Date(),
    })
    .where(and(eq(reportSchedules.id, id), eq(reportSchedules.tenantId, tenantId)))
    .returning()
  return rows[0]
}

export async function deleteReportSchedule(
  db: Db,
  tenantId: string,
  id: string,
): Promise<boolean> {
  const rows = await db
    .delete(reportSchedules)
    .where(and(eq(reportSchedules.id, id), eq(reportSchedules.tenantId, tenantId)))
    .returning({ id: reportSchedules.id })
  return rows.length > 0
}

/** Get a single schedule by id (queue consumers pass the row's tenantId). */
export async function getReportScheduleById(
  db: Db,
  tenantId: string,
  id: string,
): Promise<typeof reportSchedules.$inferSelect | undefined> {
  const rows = await db
    .select()
    .from(reportSchedules)
    .where(and(eq(reportSchedules.tenantId, tenantId), eq(reportSchedules.id, id)))
    .limit(1)
  return rows[0]
}

/** Returns all active schedules whose next_run_at <= now (for cron worker). */
export async function listDueSchedules(
  db: Db,
  now: Date,
): Promise<(typeof reportSchedules.$inferSelect)[]> {
  return db
    .select()
    .from(reportSchedules)
    .where(
      and(
        eq(reportSchedules.isActive, true),
        lte(reportSchedules.nextRunAt, now),
      ),
    )
    .orderBy(asc(reportSchedules.nextRunAt))
}

export async function touchScheduleRun(
  db: Db,
  tenantId: string,
  id: string,
  lastRunAt: Date,
  nextRunAt: Date | null,
): Promise<void> {
  await db
    .update(reportSchedules)
    .set({ lastRunAt, nextRunAt, updatedAt: new Date() })
    .where(and(eq(reportSchedules.tenantId, tenantId), eq(reportSchedules.id, id)))
}

export async function deactivateSchedule(
  db: Db,
  tenantId: string,
  id: string,
): Promise<void> {
  await db
    .update(reportSchedules)
    .set({ isActive: false, updatedAt: new Date() })
    .where(and(eq(reportSchedules.tenantId, tenantId), eq(reportSchedules.id, id)))
}