import type { Kysely } from "kysely";
import { ulid } from "ulidx";

import type { Database, UsageTable } from "../types.js";

/** One appended ledger row, as returned to the admin screens. */
export interface UsageEntry {
	id: string;
	ts: string;
	day: string;
	kind: string;
	key: string;
	quantity: number;
	costMicros: number;
	chargeMicros: number;
	actorId: string | null;
	meta: Record<string, unknown> | null;
	source: string;
	ref: string | null;
}

/** A row to append. `ts`/`day`/`id` default to now/ulid when omitted. */
export interface RecordUsageInput {
	kind: string;
	key: string;
	quantity?: number;
	costMicros?: number;
	chargeMicros?: number;
	actorId?: string | null;
	meta?: Record<string, unknown> | null;
	source?: string;
	ref?: string | null;
	ts?: string;
}

/** Aggregate billing view backing the `/billing` screen. */
export interface BillingSummary {
	/** Remaining credit in micro-dollars: purchased minus spent. */
	balanceMicros: number;
	/** Total credits ever purchased (positive micro-dollars). */
	purchasedMicros: number;
	/** Total charged for usage (positive micro-dollars). */
	spentMicros: number;
	/** Provider's own underlying cost so far (positive micro-dollars). */
	costMicros: number;
	/** Charge broken down by metric key, most expensive first. */
	byKey: Array<{ key: string; quantity: number; chargeMicros: number }>;
	/** Charge per day (ascending), for the trend chart. */
	daily: Array<{ day: string; chargeMicros: number; costMicros: number }>;
	/** Most recent ledger rows, newest first. */
	recent: UsageEntry[];
}

function toEntry(row: UsageTable): UsageEntry {
	let meta: Record<string, unknown> | null = null;
	if (row.meta) {
		try {
			meta = JSON.parse(row.meta) as Record<string, unknown>;
		} catch {
			meta = null;
		}
	}
	return {
		id: row.id,
		ts: row.ts,
		day: row.day,
		kind: row.kind,
		key: row.key,
		quantity: Number(row.quantity) || 0,
		costMicros: Number(row.cost_micros) || 0,
		chargeMicros: Number(row.charge_micros) || 0,
		actorId: row.actor_id,
		meta,
		source: row.source,
		ref: row.ref,
	};
}

/**
 * Reads and appends to the per-instance billing ledger (`_emdash_usage`).
 *
 * The ledger is append-only. Charges (usage) are positive `charge_micros`;
 * credit purchases are negative — so the running balance is `-SUM(charge)`.
 * The parent that hosts this instance normally writes usage rows straight into
 * this table over the Cloudflare D1 API; this repository is the read side the
 * admin screens use, plus an idempotent `record` for in-process callers.
 */
export class UsageRepository {
	constructor(private db: Kysely<Database>) {}

	/**
	 * Append a ledger row. When `ref` is set the write is idempotent (a repeat
	 * with the same ref is ignored), so a re-run of a daily usage sync cannot
	 * double-charge. Returns whether a new row was inserted.
	 */
	async record(input: RecordUsageInput): Promise<boolean> {
		const ts = input.ts ?? new Date().toISOString();
		const day = ts.slice(0, 10);
		const values = {
			id: ulid(),
			ts,
			day,
			kind: input.kind,
			key: input.key,
			quantity: input.quantity ?? 0,
			cost_micros: Math.round(input.costMicros ?? 0),
			charge_micros: Math.round(input.chargeMicros ?? 0),
			actor_id: input.actorId ?? null,
			meta: input.meta ? JSON.stringify(input.meta) : null,
			source: input.source ?? "system",
			ref: input.ref ?? null,
		};
		const res = await this.db
			.insertInto("_emdash_usage")
			.values(values)
			.onConflict((oc) => oc.column("ref").doNothing())
			.executeTakeFirst();
		return Number(res?.numInsertedOrUpdatedRows ?? 0n) > 0;
	}

	/** Current balance in micro-dollars (`purchased - spent`). */
	async balanceMicros(): Promise<number> {
		const row = await this.db
			.selectFrom("_emdash_usage")
			.select((eb) => eb.fn.sum<number>("charge_micros").as("charged"))
			.executeTakeFirst();
		return -(Number(row?.charged) || 0);
	}

	/** Aggregate view for the billing screen. `recentLimit` caps the tail. */
	async summary(recentLimit = 25): Promise<BillingSummary> {
		const totals = await this.db
			.selectFrom("_emdash_usage")
			.select((eb) => [
				eb.fn
					.sum<number>(eb.case().when("charge_micros", ">", 0).then(eb.ref("charge_micros")).else(0).end())
					.as("spent"),
				eb.fn
					.sum<number>(eb.case().when("charge_micros", "<", 0).then(eb.ref("charge_micros")).else(0).end())
					.as("credited"),
				eb.fn.sum<number>("cost_micros").as("cost"),
				eb.fn.sum<number>("charge_micros").as("net"),
			])
			.executeTakeFirst();

		const spentMicros = Number(totals?.spent) || 0;
		const purchasedMicros = -(Number(totals?.credited) || 0);
		const costMicros = Number(totals?.cost) || 0;
		const balanceMicros = -(Number(totals?.net) || 0);

		const byKeyRows = await this.db
			.selectFrom("_emdash_usage")
			.where("charge_micros", ">", 0)
			.select((eb) => [
				"key",
				eb.fn.sum<number>("quantity").as("quantity"),
				eb.fn.sum<number>("charge_micros").as("charge"),
			])
			.groupBy("key")
			.orderBy("charge", "desc")
			.execute();

		const dailyRows = await this.db
			.selectFrom("_emdash_usage")
			.select((eb) => [
				"day",
				eb.fn
					.sum<number>(eb.case().when("charge_micros", ">", 0).then(eb.ref("charge_micros")).else(0).end())
					.as("charge"),
				eb.fn.sum<number>("cost_micros").as("cost"),
			])
			.groupBy("day")
			.orderBy("day", "asc")
			.execute();

		const recentRows = await this.db
			.selectFrom("_emdash_usage")
			.selectAll()
			.orderBy("ts", "desc")
			.limit(recentLimit)
			.execute();

		return {
			balanceMicros,
			purchasedMicros,
			spentMicros,
			costMicros,
			byKey: byKeyRows.map((r) => ({
				key: r.key,
				quantity: Number(r.quantity) || 0,
				chargeMicros: Number(r.charge) || 0,
			})),
			daily: dailyRows.map((r) => ({
				day: r.day,
				chargeMicros: Number(r.charge) || 0,
				costMicros: Number(r.cost) || 0,
			})),
			recent: recentRows.map(toEntry),
		};
	}

	/**
	 * Paginated ledger listing for the immutable-log screen, newest first.
	 * `before` is an exclusive `ts` cursor (pass the last row's `ts` to page).
	 */
	async list(opts: { limit?: number; before?: string } = {}): Promise<{
		entries: UsageEntry[];
		nextCursor: string | null;
	}> {
		const limit = Math.min(Math.max(opts.limit ?? 100, 1), 500);
		let q = this.db.selectFrom("_emdash_usage").selectAll().orderBy("ts", "desc").limit(limit + 1);
		if (opts.before) q = q.where("ts", "<", opts.before);
		const rows = await q.execute();
		const hasMore = rows.length > limit;
		const page = hasMore ? rows.slice(0, limit) : rows;
		return {
			entries: page.map(toEntry),
			nextCursor: hasMore ? page[page.length - 1].ts : null,
		};
	}
}