import type Database from "better-sqlite3";
import { CID } from "@atproto/lex-data";
import { BlockMap, type CommitData } from "@atproto/repo";
import { ReadableBlockstore, type RepoStorage } from "@atproto/repo";

/**
 * SQLite-backed repository storage using better-sqlite3.
 *
 * Implements the RepoStorage interface from @atproto/repo, storing blocks
 * in a SQLite database on the local filesystem.
 */
export class SqliteRepoStorage
	extends ReadableBlockstore
	implements RepoStorage
{
	constructor(private db: Database.Database) {
		super();
	}

	/**
	 * Initialize the database schema. Should be called once on startup.
	 */
	initSchema(initialActive: boolean = true): void {
		this.db.exec(`
			-- Block storage (MST nodes + record blocks)
			CREATE TABLE IF NOT EXISTS blocks (
				cid TEXT PRIMARY KEY,
				bytes BLOB NOT NULL,
				rev TEXT NOT NULL
			);

			CREATE INDEX IF NOT EXISTS idx_blocks_rev ON blocks(rev);

			-- Repo state (single row)
			CREATE TABLE IF NOT EXISTS repo_state (
				id INTEGER PRIMARY KEY CHECK (id = 1),
				root_cid TEXT,
				rev TEXT,
				seq INTEGER NOT NULL DEFAULT 0,
				active INTEGER NOT NULL DEFAULT 1,
				email TEXT
			);

			-- Initialize with empty state if not exists
			INSERT OR IGNORE INTO repo_state (id, root_cid, rev, seq, active)
			VALUES (1, NULL, NULL, 0, ${initialActive ? 1 : 0});

			-- Firehose events (sequenced commit log)
			CREATE TABLE IF NOT EXISTS firehose_events (
				seq INTEGER PRIMARY KEY AUTOINCREMENT,
				event_type TEXT NOT NULL,
				payload BLOB NOT NULL,
				created_at TEXT NOT NULL DEFAULT (datetime('now'))
			);

			CREATE INDEX IF NOT EXISTS idx_firehose_created_at ON firehose_events(created_at);

			-- User preferences (single row, stores JSON array)
			CREATE TABLE IF NOT EXISTS preferences (
				id INTEGER PRIMARY KEY CHECK (id = 1),
				data TEXT NOT NULL DEFAULT '[]'
			);

			-- Initialize with empty preferences array if not exists
			INSERT OR IGNORE INTO preferences (id, data) VALUES (1, '[]');

			-- Track blob references in records (populated during importRepo)
			CREATE TABLE IF NOT EXISTS record_blob (
				recordUri TEXT NOT NULL,
				blobCid TEXT NOT NULL,
				PRIMARY KEY (recordUri, blobCid)
			);

			CREATE INDEX IF NOT EXISTS idx_record_blob_cid ON record_blob(blobCid);

			-- Track successfully imported blobs (populated during uploadBlob)
			CREATE TABLE IF NOT EXISTS imported_blobs (
				cid TEXT PRIMARY KEY,
				size INTEGER NOT NULL,
				mimeType TEXT,
				createdAt TEXT NOT NULL DEFAULT (datetime('now'))
			);

			-- Collection name cache (for describeRepo)
			CREATE TABLE IF NOT EXISTS collections (
				collection TEXT PRIMARY KEY
			);
		`);
	}

	async getRoot(): Promise<CID | null> {
		const row = this.db
			.prepare("SELECT root_cid FROM repo_state WHERE id = 1")
			.get() as { root_cid: string | null } | undefined;
		if (!row || !row.root_cid) {
			return null;
		}
		return CID.parse(row.root_cid);
	}

	async getRev(): Promise<string | null> {
		const row = this.db
			.prepare("SELECT rev FROM repo_state WHERE id = 1")
			.get() as { rev: string | null } | undefined;
		return row?.rev ?? null;
	}

	async getSeq(): Promise<number> {
		const row = this.db
			.prepare("SELECT seq FROM repo_state WHERE id = 1")
			.get() as { seq: number } | undefined;
		return row?.seq ?? 0;
	}

	async nextSeq(): Promise<number> {
		this.db
			.prepare("UPDATE repo_state SET seq = seq + 1 WHERE id = 1")
			.run();
		return this.getSeq();
	}

	async getBytes(cid: CID): Promise<Uint8Array | null> {
		const row = this.db
			.prepare("SELECT bytes FROM blocks WHERE cid = ?")
			.get(cid.toString()) as { bytes: Buffer } | undefined;
		if (!row || !row.bytes) {
			return null;
		}
		return new Uint8Array(row.bytes);
	}

	async has(cid: CID): Promise<boolean> {
		const row = this.db
			.prepare("SELECT 1 FROM blocks WHERE cid = ? LIMIT 1")
			.get(cid.toString());
		return row !== undefined;
	}

	async getBlocks(cids: CID[]): Promise<{ blocks: BlockMap; missing: CID[] }> {
		const blocks = new BlockMap();
		const missing: CID[] = [];

		for (const cid of cids) {
			const bytes = await this.getBytes(cid);
			if (bytes) {
				blocks.set(cid, bytes);
			} else {
				missing.push(cid);
			}
		}

		return { blocks, missing };
	}

	async putBlock(cid: CID, block: Uint8Array, rev: string): Promise<void> {
		this.db
			.prepare(
				"INSERT OR REPLACE INTO blocks (cid, bytes, rev) VALUES (?, ?, ?)",
			)
			.run(cid.toString(), Buffer.from(block), rev);
	}

	async putMany(blocks: BlockMap, rev: string): Promise<void> {
		const stmt = this.db.prepare(
			"INSERT OR REPLACE INTO blocks (cid, bytes, rev) VALUES (?, ?, ?)",
		);
		const internalMap = (blocks as unknown as { map: Map<string, Uint8Array> })
			.map;
		if (internalMap) {
			const insertMany = this.db.transaction(() => {
				for (const [cidStr, bytes] of internalMap) {
					stmt.run(cidStr, Buffer.from(bytes), rev);
				}
			});
			insertMany();
		}
	}

	async updateRoot(cid: CID, rev: string): Promise<void> {
		this.db
			.prepare("UPDATE repo_state SET root_cid = ?, rev = ? WHERE id = 1")
			.run(cid.toString(), rev);
	}

	async applyCommit(commit: CommitData): Promise<void> {
		const applyTransaction = this.db.transaction(() => {
			// Add new blocks
			const insertStmt = this.db.prepare(
				"INSERT OR REPLACE INTO blocks (cid, bytes, rev) VALUES (?, ?, ?)",
			);
			const internalMap = (
				commit.newBlocks as unknown as { map: Map<string, Uint8Array> }
			).map;
			if (internalMap) {
				for (const [cidStr, bytes] of internalMap) {
					insertStmt.run(cidStr, Buffer.from(bytes), commit.rev);
				}
			}

			// Remove old blocks
			const deleteStmt = this.db.prepare(
				"DELETE FROM blocks WHERE cid = ?",
			);
			const removedSet = (
				commit.removedCids as unknown as { set: Set<string> }
			).set;
			if (removedSet) {
				for (const cidStr of removedSet) {
					deleteStmt.run(cidStr);
				}
			}

			// Update root
			this.db
				.prepare(
					"UPDATE repo_state SET root_cid = ?, rev = ? WHERE id = 1",
				)
				.run(commit.cid.toString(), commit.rev);
		});
		applyTransaction();
	}

	async sizeInBytes(): Promise<number> {
		const row = this.db
			.prepare("SELECT SUM(LENGTH(bytes)) as total FROM blocks")
			.get() as { total: number | null } | undefined;
		return row?.total ?? 0;
	}

	async destroy(): Promise<void> {
		this.db.exec("DELETE FROM blocks");
		this.db
			.prepare(
				"UPDATE repo_state SET root_cid = NULL, rev = NULL WHERE id = 1",
			)
			.run();
	}

	async countBlocks(): Promise<number> {
		const row = this.db
			.prepare("SELECT COUNT(*) as count FROM blocks")
			.get() as { count: number };
		return row.count;
	}

	async getPreferences(): Promise<unknown[]> {
		const row = this.db
			.prepare("SELECT data FROM preferences WHERE id = 1")
			.get() as { data: string } | undefined;
		if (!row?.data) return [];
		try {
			return JSON.parse(row.data);
		} catch {
			return [];
		}
	}

	async putPreferences(preferences: unknown[]): Promise<void> {
		this.db
			.prepare("UPDATE preferences SET data = ? WHERE id = 1")
			.run(JSON.stringify(preferences));
	}

	async getActive(): Promise<boolean> {
		const row = this.db
			.prepare("SELECT active FROM repo_state WHERE id = 1")
			.get() as { active: number } | undefined;
		return row ? row.active === 1 : true;
	}

	async setActive(active: boolean): Promise<void> {
		this.db
			.prepare("UPDATE repo_state SET active = ? WHERE id = 1")
			.run(active ? 1 : 0);
	}

	getEmail(): string | null {
		const row = this.db
			.prepare("SELECT email FROM repo_state WHERE id = 1")
			.get() as { email: string | null } | undefined;
		return row?.email ?? null;
	}

	setEmail(email: string): void {
		this.db
			.prepare("UPDATE repo_state SET email = ? WHERE id = 1")
			.run(email);
	}

	// ============================================
	// Collection Cache Methods
	// ============================================

	getCollections(): string[] {
		const rows = this.db
			.prepare("SELECT collection FROM collections ORDER BY collection")
			.all() as Array<{ collection: string }>;
		return rows.map((row) => row.collection);
	}

	addCollection(collection: string): void {
		this.db
			.prepare("INSERT OR IGNORE INTO collections (collection) VALUES (?)")
			.run(collection);
	}

	hasCollections(): boolean {
		const row = this.db
			.prepare("SELECT 1 FROM collections LIMIT 1")
			.get();
		return row !== undefined;
	}

	// ============================================
	// Blob Tracking Methods
	// ============================================

	addRecordBlob(recordUri: string, blobCid: string): void {
		this.db
			.prepare(
				"INSERT OR IGNORE INTO record_blob (recordUri, blobCid) VALUES (?, ?)",
			)
			.run(recordUri, blobCid);
	}

	addRecordBlobs(recordUri: string, blobCids: string[]): void {
		for (const cid of blobCids) {
			this.addRecordBlob(recordUri, cid);
		}
	}

	removeRecordBlobs(recordUri: string): void {
		this.db
			.prepare("DELETE FROM record_blob WHERE recordUri = ?")
			.run(recordUri);
	}

	trackImportedBlob(cid: string, size: number, mimeType: string): void {
		this.db
			.prepare(
				"INSERT OR REPLACE INTO imported_blobs (cid, size, mimeType) VALUES (?, ?, ?)",
			)
			.run(cid, size, mimeType);
	}

	isBlobImported(cid: string): boolean {
		const row = this.db
			.prepare("SELECT 1 FROM imported_blobs WHERE cid = ? LIMIT 1")
			.get(cid);
		return row !== undefined;
	}

	countExpectedBlobs(): number {
		const row = this.db
			.prepare(
				"SELECT COUNT(DISTINCT blobCid) as count FROM record_blob",
			)
			.get() as { count: number };
		return row.count;
	}

	countImportedBlobs(): number {
		const row = this.db
			.prepare("SELECT COUNT(*) as count FROM imported_blobs")
			.get() as { count: number };
		return row.count;
	}

	listMissingBlobs(
		limit: number = 500,
		cursor?: string,
	): { blobs: Array<{ cid: string; recordUri: string }>; cursor?: string } {
		const blobs: Array<{ cid: string; recordUri: string }> = [];

		const rows = cursor
			? (this.db
					.prepare(
						`SELECT rb.blobCid, rb.recordUri FROM record_blob rb
					 LEFT JOIN imported_blobs ib ON rb.blobCid = ib.cid
					 WHERE ib.cid IS NULL AND rb.blobCid > ?
					 ORDER BY rb.blobCid
					 LIMIT ?`,
					)
					.all(cursor, limit + 1) as Array<{
					blobCid: string;
					recordUri: string;
				}>)
			: (this.db
					.prepare(
						`SELECT rb.blobCid, rb.recordUri FROM record_blob rb
					 LEFT JOIN imported_blobs ib ON rb.blobCid = ib.cid
					 WHERE ib.cid IS NULL
					 ORDER BY rb.blobCid
					 LIMIT ?`,
					)
					.all(limit + 1) as Array<{
					blobCid: string;
					recordUri: string;
				}>);

		for (const row of rows.slice(0, limit)) {
			blobs.push({ cid: row.blobCid, recordUri: row.recordUri });
		}

		const hasMore = rows.length > limit;
		const nextCursor = hasMore ? blobs[blobs.length - 1]?.cid : undefined;

		return { blobs, cursor: nextCursor };
	}

	clearBlobTracking(): void {
		this.db.exec("DELETE FROM record_blob");
		this.db.exec("DELETE FROM imported_blobs");
	}
}