import type { Database } from "bun:sqlite";
import { openSqliteWithPragmas } from "@danypops/vehicle-server/storage";
import { SQLITE_BUSY_TIMEOUT_MS } from "../constants.ts";

const INITIAL_SCHEMA = `
CREATE TABLE metric_observations (
	id          INTEGER PRIMARY KEY AUTOINCREMENT,
	source      TEXT NOT NULL,
	scope       TEXT NOT NULL,
	metric      TEXT NOT NULL,
	value       REAL,
	unit        TEXT NOT NULL,
	observed_at INTEGER NOT NULL CHECK(observed_at >= 0),
	attributes  TEXT NOT NULL DEFAULT '{}' CHECK(json_valid(attributes))
);
CREATE INDEX metric_observations_series_time_idx
	ON metric_observations(source, scope, metric, observed_at);
CREATE INDEX metric_observations_time_idx
	ON metric_observations(observed_at);
`;

const SESSION_IDENTITY_SCHEMA = `
CREATE TABLE session_identities (
	session_id    TEXT PRIMARY KEY,
	secret_hash   TEXT NOT NULL,
	registered_at TEXT NOT NULL,
	last_seen_at  TEXT NOT NULL
);
CREATE INDEX session_identities_last_seen_idx
	ON session_identities(last_seen_at);
`;

const USAGE_IMPORT_SCHEMA = `
CREATE TABLE usage_import_identities (
	identity    TEXT PRIMARY KEY,
	imported_at INTEGER NOT NULL CHECK(imported_at >= 0)
);
CREATE TABLE usage_import_state (
	id         INTEGER PRIMARY KEY CHECK(id = 1),
	status     TEXT NOT NULL CHECK(json_valid(status)),
	updated_at INTEGER NOT NULL CHECK(updated_at >= 0)
);
`;

/**
 * Delegates bootstrap (pragmas, migration engine) to `@danypops/vehicle-server/storage`, which
 * generalizes the byte-identical pragma/PRAGMA-user_version skeleton jittor's own db.ts used to
 * hand-roll (see daemon-kit's README). Jittor's only remaining responsibility is its own schema.
 */
export function openJittorDb(path: string): Database {
	return openSqliteWithPragmas(path, {
		databaseOptions: { create: true, strict: true },
		busyTimeoutMs: SQLITE_BUSY_TIMEOUT_MS,
		migrations: [
			{ version: 1, up: (db) => db.exec(INITIAL_SCHEMA) },
			{ version: 2, up: (db) => db.exec(SESSION_IDENTITY_SCHEMA) },
			{ version: 3, up: (db) => db.exec(USAGE_IMPORT_SCHEMA) },
		],
	});
}