import Database from "better-sqlite3";
import { mkdtempSync, rmSync } from "node:fs";
import { tmpdir } from "node:os";
import { join } from "node:path";
import { beforeEach, describe, expect, it, vi } from "vitest";
import { isErr, isOk, Milestone, Project, Slice, Task } from "@tff/core";
import { SQLiteStateAdapter } from "../../src/infrastructure/adapters/sqlite/sqlite-state.adapter.js";

// Integration tests access the private `db` field for direct SQL operations.
type AdapterWithDb = { db: Database.Database };
const getDb = (adapter: SQLiteStateAdapter): Database.Database =>
	(adapter as unknown as AdapterWithDb).db;

describe("SQLite integration", () => {
	let adapter: SQLiteStateAdapter;

	beforeEach(() => {
		adapter = SQLiteStateAdapter.createInMemory();
		adapter.init();
	});

	it("FK: task with invalid slice_id fails", () => {
		adapter.saveProject({ name: "P" });
		adapter.createMilestone({ number: 1, name: "M" });
		const result = adapter.createTask({ sliceId: "nonexistent", number: 1, title: "T" });
		expect(isErr(result)).toBe(true);
	});

	it("FK: cannot delete milestone with slices", () => {
		adapter.saveProject({ name: "P" });
		const msResult = adapter.createMilestone({ number: 1, name: "M" });
		const milestoneId = isOk(msResult) ? msResult.data.id : "M01";
		adapter.createSlice({ milestoneId, number: 1, title: "S" });
		const db = getDb(adapter);
		expect(() => {
			db.prepare("DELETE FROM milestone WHERE id = ?").run(milestoneId);
		}).toThrow();
	});

	it("CASCADE: deleting task removes dependency edges", () => {
		adapter.saveProject({ name: "P" });
		const msResult = adapter.createMilestone({ number: 1, name: "M" });
		const milestoneId = isOk(msResult) ? msResult.data.id : "M01";
		const slResult = adapter.createSlice({ milestoneId, number: 1, title: "S" });
		const sliceId = isOk(slResult) ? slResult.data.id : "M01-S01";
		adapter.createTask({ sliceId, number: 1, title: "T1" });
		adapter.createTask({ sliceId, number: 2, title: "T2" });
		// Task IDs are derived from sliceId
		adapter.addDependency(`${sliceId}-T01`, `${sliceId}-T02`, "blocks");

		const db = getDb(adapter);
		db.prepare("DELETE FROM task WHERE id = ?").run(`${sliceId}-T01`);
		const deps = db.prepare("SELECT * FROM dependency").all();
		expect(deps).toHaveLength(0);
	});

	it("singleton: second project insert fails at DB level", () => {
		adapter.saveProject({ name: "P1" });
		const db = getDb(adapter);
		expect(() => {
			db.prepare("INSERT INTO project (id, name) VALUES ('other', 'P2')").run();
		}).toThrow();
	});

	it("migration: v1 creates all tables", () => {
		const db = getDb(adapter);
		const tables = db
			.prepare("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name")
			.all() as {
			name: string;
		}[];
		const tableNames = tables.map((t) => t.name);
		expect(tableNames).toContain("project");
		expect(tableNames).toContain("milestone");
		expect(tableNames).toContain("slice");
		expect(tableNames).toContain("task");
		expect(tableNames).toContain("dependency");
		expect(tableNames).toContain("workflow_session");
		expect(tableNames).toContain("schema_version");
	});

	it("init() returns VERSION_MISMATCH when db schema is ahead of code", () => {
		const db = getDb(adapter);
		db.prepare("DELETE FROM schema_version").run();
		db.prepare("INSERT INTO schema_version (version) VALUES (999)").run();
		const adapterWithFutureSchema = new (SQLiteStateAdapter as unknown as new (
			db: Database.Database,
		) => SQLiteStateAdapter)(db);
		const result = adapterWithFutureSchema.init();
		expect(isErr(result)).toBe(true);
		if (isErr(result)) expect(result.error.errorLabel).toBe("VERSION_MISMATCH");
	});

	it("init() recovers file-backed db on VERSION_MISMATCH by wiping and recreating", () => {
		const tempDir = mkdtempSync(join(tmpdir(), "tff-test-"));
		const tempDbPath = join(tempDir, "state.db");
		try {
			const seedDb = new Database(tempDbPath);
			seedDb.prepare("CREATE TABLE schema_version (version INTEGER PRIMARY KEY)").run();
			seedDb.prepare("INSERT INTO schema_version (version) VALUES (9)").run();
			seedDb.close();

			const warnSpy = vi.spyOn(console, "warn").mockImplementation(() => {});
			const fileAdapter = SQLiteStateAdapter.createWithPath(tempDbPath);
			const result = fileAdapter.init();

			expect(isOk(result)).toBe(true);
			expect(warnSpy).toHaveBeenCalledWith(
				"[tff]",
				expect.stringContaining("schema version mismatch"),
			);
			expect(warnSpy).toHaveBeenCalledWith("[tff]", expect.stringContaining(tempDbPath));
			warnSpy.mockRestore();

			// Verify database is functional after recovery
			const projectResult = fileAdapter.saveProject({ name: "P" });
			expect(isOk(projectResult)).toBe(true);

			// Verify schema was reset to current version
			const db = getDb(fileAdapter);
			const versionRow = db.prepare("SELECT MAX(version) as version FROM schema_version").get() as {
				version: number;
			};
			expect(versionRow.version).toBeGreaterThanOrEqual(1);
		} finally {
			rmSync(tempDir, { recursive: true });
		}
	});

	it("closeMilestone with open slices without spec approval returns MILESTONE_COMPLETENESS_VIOLATION", () => {
		adapter.saveProject({ name: "P" });
		const msResult = adapter.createMilestone({ number: 1, name: "M" });
		const milestoneId = isOk(msResult) ? msResult.data.id : "M01";
		adapter.createSlice({ milestoneId, number: 1, title: "S" });
		const result = adapter.closeMilestone(milestoneId);
		expect(isErr(result)).toBe(true);
		if (isErr(result)) expect(result.error.errorLabel).toBe("MILESTONE_COMPLETENESS_VIOLATION");
	});

	it("returns proper entity instances", () => {
		adapter.saveProject({ name: "P" });
		const p = adapter.getProject();
		expect(isOk(p)).toBe(true);
		if (isOk(p) && p.data) expect(p.data).toBeInstanceOf(Project);

		const m = adapter.createMilestone({ number: 1, name: "M" });
		expect(isOk(m)).toBe(true);
		if (isOk(m) && m.data) expect(m.data).toBeInstanceOf(Milestone);

		const sl = adapter.createSlice({
			milestoneId: isOk(m) ? m.data.id : "M01",
			number: 1,
			title: "S",
		});
		expect(isOk(sl)).toBe(true);
		if (isOk(sl) && sl.data) expect(sl.data).toBeInstanceOf(Slice);

		const t = adapter.createTask({
			sliceId: isOk(sl) ? sl.data.id : "M01-S01",
			number: 1,
			title: "T",
		});
		expect(isOk(t)).toBe(true);
		if (isOk(t) && t.data) expect(t.data).toBeInstanceOf(Task);
	});
});