import { sql, type Selectable } from 'kysely'

import type * as Db from '../Db.js'
import type * as db_Schema from '../Schema.js'

/** Columns of the `routes_subsidies` table. */
export type Table = db_Schema.RoutesSubsidy

/** A stored Routes subsidy policy. */
export type Record = Selectable<Table>

/** Locks organization subsidy liability changes inside the caller's transaction. */
export async function lockOrganization(db: Db.Db, orgId: string): Promise<void> {
  const key = `routes-subsidy:${orgId}`
  await sql`SELECT pg_advisory_xact_lock(hashtextextended(${key}, 0))`.execute(db.kysely)
}

/** Reads an organization's default Routes subsidy policy. */
export function getOrganization(db: Db.Db, orgId: string): Promise<Record | undefined> {
  return db.kysely
    .selectFrom('routes_subsidies')
    .selectAll()
    .where('orgId', '=', orgId)
    .executeTakeFirst()
}

/** Enables or replaces an organization's Routes subsidy policy. */
export function upsertOrganization(db: Db.Db, input: upsertOrganization.Input): Promise<Record> {
  const now = new Date().toISOString()
  return db.kysely
    .insertInto('routes_subsidies')
    .values({
      createdAt: now,
      enabled: true,
      frequency: input.frequency,
      maxAmount: input.maxAmount,
      orgId: input.orgId,
      updatedAt: now,
    })
    .onConflict((oc) =>
      oc.column('orgId').doUpdateSet({
        enabled: true,
        frequency: input.frequency,
        maxAmount: input.maxAmount,
        updatedAt: now,
      }),
    )
    .returningAll()
    .executeTakeFirstOrThrow()
}

export declare namespace upsertOrganization {
  /** Fields accepted when enabling or replacing a Routes subsidy policy. */
  type Input = {
    /** Interval over which the subsidy limit applies. */
    frequency: 'tx'
    /** Maximum Routes subsidy gap in decimal USD. */
    maxAmount: string
    /** Organization id (`org_…`) the policy belongs to. */
    orgId: string
  }
}

/** Disables an organization's Routes subsidy policy. */
export async function removeOrganization(db: Db.Db, orgId: string): Promise<boolean> {
  const result = await db.kysely
    .deleteFrom('routes_subsidies')
    .where('orgId', '=', orgId)
    .executeTakeFirst()
  return result.numDeletedRows > 0n
}