import { and, asc, gte, inArray, lte, sql } from "drizzle-orm"

import { db } from "@/lib/db/client"
import { holidays } from "@/lib/db/schema"
import { yearsIn, type Holiday } from "@/lib/holiday-csv"

/**
 * The days the factory is shut.
 *
 * The third input to every date `lib/calendar.ts` computes, alongside the
 * schedule and the holder's working week. A duty fixed to the 15th does not
 * vanish because the 15th is a holiday: it moves to the next working day, and
 * this table is what tells it to.
 *
 * The client sends this once a year as a file. Until it arrives the table is
 * empty, which means every fixed-day duty currently lands on the day it was
 * asked for, holiday or not.
 */

export async function listHolidays(year?: number): Promise<Holiday[]> {
  const rows = await db
    .select({ date: holidays.holidayDate, name: holidays.name })
    .from(holidays)
    .where(
      year
        ? and(
            gte(holidays.holidayDate, `${year}-01-01`),
            lte(holidays.holidayDate, `${year}-12-31`),
          )
        : undefined,
    )
    .orderBy(asc(holidays.holidayDate))
  return rows
}

/** Which years the table already knows about, newest first. */
export async function knownYears(): Promise<{ year: number; days: number }[]> {
  const rows = await db
    .select({
      year: sql<number>`extract(year from ${holidays.holidayDate})::int`,
      days: sql<number>`count(*)::int`,
    })
    .from(holidays)
    .groupBy(sql`extract(year from ${holidays.holidayDate})`)
    .orderBy(sql`extract(year from ${holidays.holidayDate}) desc`)
  return rows
}

export type ImportReport = {
  years: number[]
  removed: number
  added: number
}

/**
 * Load a year's calendar, replacing whatever was there for the years it covers.
 *
 * Replacing and not merging, which is the one decision in this file worth
 * arguing about. A calendar is a statement about a whole year: if the file for
 * 2027 does not mention a day that was in last year's, that day is a working
 * day now, and merging would leave the factory shut on it for ever. Only the
 * years the file actually mentions are touched, so loading 2027 leaves 2026
 * alone.
 *
 * It is also the one place in this schema where rows are deleted. The rule
 * elsewhere is archive and never delete, and it holds because those rows record
 * what somebody did. A holiday records nothing: it is reference data, and a
 * withdrawn holiday that lingers puts work on a day people are at their desks.
 * Task instances already generated are untouched either way, because the unique
 * key means regenerating a day cannot move it.
 */
export async function importHolidays(parsed: Holiday[]): Promise<ImportReport> {
  const years = yearsIn(parsed)

  return db.transaction(async (tx) => {
    const removed = await tx
      .delete(holidays)
      .where(
        inArray(sql`extract(year from ${holidays.holidayDate})::int`, years),
      )
      .returning({ date: holidays.holidayDate })

    const added = await tx
      .insert(holidays)
      .values(parsed.map((h) => ({ holidayDate: h.date, name: h.name })))
      .returning({ date: holidays.holidayDate })

    return { years, removed: removed.length, added: added.length }
  })
}