import type { Selectable } from 'kysely'

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

/** Columns of the `stripe_customers` table, derived from `Schema.StripeCustomer`. */
export type Table = db_Schema.StripeCustomer

/** A stored Stripe billing-source row. */
export type Record = Selectable<Table>

/** A billing status. */
export type Status = Record['status']

/** Billing statuses; `active` alone opens the production sponsorship gate. */
export const statuses = ['active', 'canceled', 'none', 'past_due'] as const satisfies readonly Status[] // prettier-ignore

/**
 * Inserts a Stripe billing source (status `none`) and returns the stored record.
 * Idempotent per organization: a concurrent first-checkout race resolves to
 * the winner's row — the loser's Stripe customer stays orphaned in Stripe,
 * which is harmless.
 *
 * @param db - The database.
 * @param input - The Stripe source to insert.
 * @returns The stored record.
 */
export async function create(db: Db.Db, input: create.Input): Promise<Record> {
  const now = new Date().toISOString()
  const environment = input.environment ?? 'production'
  const inserted = await db.kysely
    .insertInto('stripe_customers')
    .values({
      createdAt: now,
      environment,
      orgId: input.orgId,
      status: 'none',
      stripeCustomerId: input.stripeCustomerId,
      updatedAt: now,
    })
    // Untargeted so it holds against either the org pkey (before the pkey drop
    // release) or the `(org_id, environment)` unique index (after).
    .onConflict((oc) => oc.doNothing())
    .returningAll()
    .executeTakeFirst()
  if (inserted) return inserted
  return await db.kysely
    .selectFrom('stripe_customers')
    .selectAll()
    .where('orgId', '=', input.orgId)
    .where('environment', '=', environment)
    .executeTakeFirstOrThrow()
}

export declare namespace create {
  /** Fields accepted when inserting a Stripe billing source. */
  type Input = {
    /** Environment this source backs; defaults to `production`. */
    environment?: Record['environment'] | undefined
    /** Organization id (`org_…`) the Stripe source belongs to. */
    orgId: string
    /** Stripe customer id backing this account. */
    stripeCustomerId: string
  }
}

/**
 * Deletes an organization's Stripe billing source. The Stripe customer is never
 * deleted; its invoice history stays in Stripe for audit.
 *
 * @param db - The database.
 * @param orgId - The organization id (`org_…`).
 * @returns Whether a Stripe source existed and was deleted.
 */
export async function deleteByOrg(db: Db.Db, orgId: string): Promise<boolean> {
  const result = await db.kysely
    .deleteFrom('stripe_customers')
    .where('orgId', '=', orgId)
    .executeTakeFirst()
  return result.numDeletedRows > 0n
}

/**
 * Reads an organization's Stripe billing source.
 *
 * @param db - The database.
 * @param orgId - The organization id (`org_…`).
 * @param environment - The environment to read; defaults to `production`.
 * @returns The stored record, or `undefined` when the org has none.
 */
export function get(
  db: Db.Db,
  orgId: string,
  environment: Record['environment'] = 'production',
): Promise<Record | undefined> {
  return db.kysely
    .selectFrom('stripe_customers')
    .selectAll()
    .where('orgId', '=', orgId)
    .where('environment', '=', environment)
    .executeTakeFirst()
}

/**
 * Lists every billing source in an environment — the driver set for the cron
 * job that reconciles cached billing snapshots (e.g. API-key records) against
 * this authoritative table.
 *
 * @param db - The database.
 * @param environment - The environment to enumerate.
 * @returns The stored records for that environment.
 */
export function listByEnvironment(
  db: Db.Db,
  environment: Record['environment'],
): Promise<Record[]> {
  return db.kysely
    .selectFrom('stripe_customers')
    .selectAll()
    .where('environment', '=', environment)
    .execute()
}

/**
 * Reads a Stripe billing source by its customer id — the webhook handler's
 * lookup for inbound events.
 *
 * @param db - The database.
 * @param stripeCustomerId - The Stripe customer id.
 * @returns The stored record, or `undefined` for unknown customers.
 */
export function getByCustomer(db: Db.Db, stripeCustomerId: string): Promise<Record | undefined> {
  return db.kysely
    .selectFrom('stripe_customers')
    .selectAll()
    .where('stripeCustomerId', '=', stripeCustomerId)
    .executeTakeFirst()
}

/**
 * Sets an organization's billing status.
 *
 * @param db - The database.
 * @param orgId - The organization id (`org_…`).
 * @param status - The derived status to persist.
 * @param environment - The environment to update; defaults to `production`.
 * @returns The updated record, or `undefined` when the org has no Stripe source.
 */
export function setStatus(
  db: Db.Db,
  orgId: string,
  status: Status,
  environment: Record['environment'] = 'production',
): Promise<Record | undefined> {
  return db.kysely
    .updateTable('stripe_customers')
    .set({ status, updatedAt: new Date().toISOString() })
    .where('orgId', '=', orgId)
    .where('environment', '=', environment)
    .returningAll()
    .executeTakeFirst()
}