// @vitest-environment node

import { readdirSync, readFileSync } from "node:fs";
import { join } from "node:path";
import { Miniflare } from "miniflare";
import { afterAll, beforeAll, describe, expect, it } from "vitest";
import { newId, queryDb } from "./db";

const MIGRATIONS_DIR = join(__dirname, "../migrations");

describe("Requirement Migration Tests", () => {
  let db: any;
  let mf: Miniflare;

  beforeAll(async () => {
    mf = new Miniflare({
      modules: true,
      script: "export default { fetch() { return new Response('ok'); } }",
      d1Databases: { DB: "test-db" },
    });
    db = await mf.getD1Database("DB");

    // Setup query function on D1Database handle (similar to helper setup)
    const proto = Object.getPrototypeOf(db);
    if (proto && !proto.query) {
      proto.query = async function (text: string, params: any[] = []) {
        const converted = text.replace(/\$\d+/g, "?");
        const stmt = this.prepare(converted).bind(...(params || []));
        const trimmed = text.trim().toUpperCase();
        if (trimmed.startsWith("SELECT") || text.toUpperCase().includes("RETURNING")) {
          const res = await stmt.all();
          return { rows: res.results, rowCount: res.results.length };
        } else {
          const res = await stmt.run();
          return { rows: [], rowCount: res.meta?.changes ?? 0 };
        }
      };
    }

    // Apply all migrations (from 0001 to 0045)
    const migrationFiles = readdirSync(MIGRATIONS_DIR)
      .filter((f) => f.endsWith(".sql"))
      .sort();

    for (const file of migrationFiles) {
      const sql = readFileSync(join(MIGRATIONS_DIR, file), "utf-8");
      for (const stmt of sql
        .split(";")
        .map((s) => s.trim())
        .filter(Boolean)) {
        if (/^(BEGIN|COMMIT|ROLLBACK|PRAGMA)/i.test(stmt)) {
          continue;
        }
        await db.prepare(stmt).run();
      }
    }

    // Seed a projects record because requirement references projects(id)
    await queryDb(db, `INSERT INTO projects (id, owner_id, code, name, description, created_at, updated_at) VALUES ($1, $2, $3, $4, $5, $6, $7)`, [
      "proj-1",
      "owner-1",
      "test-code",
      "Test Project",
      "Description",
      new Date().toISOString(),
      new Date().toISOString(),
    ]);
  });

  afterAll(async () => {
    await mf.dispose();
  });

  it("should insert requirement and draft task with valid data successfully", async () => {
    const reqId = newId();
    const taskId = newId();

    // Insert valid requirement
    await expect(
      queryDb(db, `INSERT INTO project_requirements (id, project_id, title, description, status) VALUES ($1, $2, $3, $4, $5)`, [
        reqId,
        "proj-1",
        "Valid Requirement",
        "Description",
        "pending",
      ]),
    ).resolves.toBeDefined();

    // Insert valid draft task
    await expect(
      queryDb(
        db,
        `INSERT INTO requirement_draft_tasks (
          id, requirement_id, title, affected_files, steps, qc_checks, acceptance_tests, priority, status
        ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)`,
        [
          taskId,
          reqId,
          "Valid Task",
          JSON.stringify(["file1.ts"]),
          JSON.stringify([{ order: 1, action: "Do", detail: "Something" }]),
          JSON.stringify([{ category: "unit", item: "test", severity: "high", automated: true }]),
          JSON.stringify([{ id: "tc-1", scenario: "Testing", given: "x", when: "y", then: "z", testType: "unit" }]),
          "medium",
          "draft",
        ],
      ),
    ).resolves.toBeDefined();

    // Fetch and check values
    const reqRes = await queryDb(db, `SELECT * FROM project_requirements WHERE id = $1`, [reqId]);
    expect(reqRes.rows[0].title).toBe("Valid Requirement");

    const taskRes = await queryDb(db, `SELECT * FROM requirement_draft_tasks WHERE id = $1`, [taskId]);
    expect(taskRes.rows[0].title).toBe("Valid Task");
    expect(JSON.parse(taskRes.rows[0].affected_files)).toEqual(["file1.ts"]);
  });

  it("should enforce json_valid check on draft tasks JSON fields", async () => {
    const reqId = newId();
    await queryDb(db, `INSERT INTO project_requirements (id, project_id, title, description) VALUES ($1, $2, $3, $4)`, [
      reqId,
      "proj-1",
      "Test Req",
      "Desc",
    ]);

    // Invalid affected_files
    await expect(
      queryDb(db, `INSERT INTO requirement_draft_tasks (id, requirement_id, title, affected_files) VALUES ($1, $2, $3, $4)`, [
        newId(),
        reqId,
        "Task 1",
        "{invalid json}",
      ]),
    ).rejects.toThrow();

    // Invalid steps
    await expect(
      queryDb(db, `INSERT INTO requirement_draft_tasks (id, requirement_id, title, steps) VALUES ($1, $2, $3, $4)`, [
        newId(),
        reqId,
        "Task 2",
        "[invalid steps",
      ]),
    ).rejects.toThrow();

    // Invalid qc_checks
    await expect(
      queryDb(db, `INSERT INTO requirement_draft_tasks (id, requirement_id, title, qc_checks) VALUES ($1, $2, $3, $4)`, [
        newId(),
        reqId,
        "Task 3",
        "{qc: invalid}",
      ]),
    ).rejects.toThrow();

    // Invalid acceptance_tests
    await expect(
      queryDb(db, `INSERT INTO requirement_draft_tasks (id, requirement_id, title, acceptance_tests) VALUES ($1, $2, $3, $4)`, [
        newId(),
        reqId,
        "Task 4",
        "['invalid tests']",
      ]),
    ).rejects.toThrow();
  });

  it("should enforce check constraints on status and priority fields", async () => {
    const reqId = newId();

    // Invalid requirement status
    await expect(
      queryDb(db, `INSERT INTO project_requirements (id, project_id, title, description, status) VALUES ($1, $2, $3, $4, $5)`, [
        reqId,
        "proj-1",
        "Test Req",
        "Desc",
        "invalid_status",
      ]),
    ).rejects.toThrow();

    // Create a valid requirement first to link draft tasks
    const validReqId = newId();
    await queryDb(db, `INSERT INTO project_requirements (id, project_id, title, description) VALUES ($1, $2, $3, $4)`, [
      validReqId,
      "proj-1",
      "Valid Req",
      "Desc",
    ]);

    // Invalid draft task priority
    await expect(
      queryDb(db, `INSERT INTO requirement_draft_tasks (id, requirement_id, title, priority) VALUES ($1, $2, $3, $4)`, [
        newId(),
        validReqId,
        "Task",
        "very_high",
      ]),
    ).rejects.toThrow();

    // Invalid draft task status
    await expect(
      queryDb(db, `INSERT INTO requirement_draft_tasks (id, requirement_id, title, status) VALUES ($1, $2, $3, $4)`, [
        newId(),
        validReqId,
        "Task",
        "completed",
      ]),
    ).rejects.toThrow();
  });
});