import type { Kysely } from "kysely";

/**
 * Migration: per-instance billing ledger (`_emdash_usage`).
 *
 * The recursive-hosting platform bills each instance "cost plus": the parent
 * that provisions an instance meters the instance's real Cloudflare running
 * cost (requests, CPU, D1 rows, R2 ops/storage), applies a markup, and writes
 * charge rows into THIS ledger inside the instance's own D1 — so an instance is
 * always the source of truth for its own bill. Credit top-ups land here too as
 * negative-charge rows. The running balance is therefore `-SUM(charge_micros)`.
 *
 * All monetary amounts are integer micro-dollars (1e-6 USD) to avoid float
 * drift. `cost_micros` is the provider's own cost; `charge_micros` is what the
 * instance is billed (cost × markup), negative for credit purchases. Rows are
 * append-only — the immutable log reads straight from here. `ref` is a caller
 * supplied idempotency key (unique when present) so a re-run of a daily usage
 * sync cannot double-charge.
 */
export async function up(db: Kysely<unknown>): Promise<void> {
	await db.schema
		.createTable("_emdash_usage")
		.addColumn("id", "text", (col) => col.primaryKey())
		.addColumn("ts", "text", (col) => col.notNull())
		.addColumn("day", "text", (col) => col.notNull())
		.addColumn("kind", "text", (col) => col.notNull())
		.addColumn("key", "text", (col) => col.notNull())
		.addColumn("quantity", "real", (col) => col.notNull().defaultTo(0))
		.addColumn("cost_micros", "integer", (col) => col.notNull().defaultTo(0))
		.addColumn("charge_micros", "integer", (col) => col.notNull().defaultTo(0))
		.addColumn("actor_id", "text")
		.addColumn("meta", "text")
		.addColumn("source", "text", (col) => col.notNull().defaultTo("parent"))
		.addColumn("ref", "text")
		.execute();

	// Chronological listing (immutable log + recent-activity views).
	await db.schema
		.createIndex("idx_usage_ts")
		.on("_emdash_usage")
		.column("ts")
		.execute();

	// Daily rollups for the billing analytics screen.
	await db.schema
		.createIndex("idx_usage_day_key")
		.on("_emdash_usage")
		.columns(["day", "key"])
		.execute();

	// Idempotent charge writes: a non-null ref may appear at most once, so a
	// repeated usage sync (same day, same metric) is a no-op instead of a
	// double charge. NULL refs are exempt (SQLite treats NULLs as distinct).
	await db.schema
		.createIndex("uq_usage_ref")
		.on("_emdash_usage")
		.column("ref")
		.unique()
		.where("ref", "is not", null)
		.execute();
}

export async function down(db: Kysely<unknown>): Promise<void> {
	await db.schema.dropTable("_emdash_usage").execute();
}