import { afterEach, beforeEach, describe, expect, it } from "bun:test";
import * as fs from "fs";
import { cleanupTestDb, clearTestEnv, createTestDb, setTestEnv } from "./setup.js";

const TEST_DB = "test-vector.db";

describe("Database Module", () => {
  beforeEach(() => {
    setTestEnv();
    cleanupTestDb(TEST_DB);
  });

  afterEach(() => {
    cleanupTestDb(TEST_DB);
    clearTestEnv();
  });

  it("should create database file", () => {
    const db = createTestDb(TEST_DB);
    db.close();
    expect(fs.existsSync(TEST_DB)).toBe(true);
  });

  it("should initialize items table with correct schema", () => {
    const db = createTestDb(TEST_DB);

    db.exec(`
      CREATE TABLE if not exists items (
        id INTEGER PRIMARY KEY,
        content TEXT,
        path TEXT,
        created_at TEXT DEFAULT (datetime('now')),
        updated_at TEXT DEFAULT (datetime('now'))
      );
    `);

    // テーブルスキーマを確認
    const itemsSchema = db.prepare("PRAGMA table_info(items)").all();
    
    expect(itemsSchema.length).toBe(5);
    expect(itemsSchema[0].name).toBe("id");
    expect(itemsSchema[1].name).toBe("content");
    expect(itemsSchema[2].name).toBe("path");
    expect(itemsSchema[3].name).toBe("created_at");
    expect(itemsSchema[4].name).toBe("updated_at");

    db.close();
  });

  it("should insert item into items table", () => {
    const db = createTestDb(TEST_DB);

    db.exec(`
      CREATE TABLE if not exists items (
        id INTEGER PRIMARY KEY,
        content TEXT,
        path TEXT,
        created_at TEXT DEFAULT (datetime('now')),
        updated_at TEXT DEFAULT (datetime('now'))
      );
    `);

    // 挿入
    const stmt = db.prepare(`
      INSERT INTO items (content, path) VALUES (?, ?)
    `);
    stmt.run("test content", "/test/path");

    // 確認
    const items = db.prepare("SELECT * FROM items").all();
    expect(items.length).toBe(1);
    expect(items[0].content).toBe("test content");
    expect(items[0].path).toBe("/test/path");
    expect(items[0].created_at).not.toBeNull();
    expect(items[0].updated_at).not.toBeNull();

    db.close();
  });

  it("should retrieve items from database", () => {
    const db = createTestDb(TEST_DB);

    db.exec(`
      CREATE TABLE if not exists items (
        id INTEGER PRIMARY KEY,
        content TEXT,
        path TEXT,
        created_at TEXT DEFAULT (datetime('now')),
        updated_at TEXT DEFAULT (datetime('now'))
      );
      DELETE FROM items;
    `);

    // 複数アイテムを挿入
    const stmt = db.prepare("INSERT INTO items (content, path) VALUES (?, ?)");
    stmt.run("item 1", "/path/1");
    stmt.run("item 2", "/path/2");
    stmt.run("item 3", "/path/3");

    // 取得
    const items = db.prepare("SELECT * FROM items").all();
    expect(items.length).toBe(3);
    expect(items[0].content).toBe("item 1");
    expect(items[1].content).toBe("item 2");
    expect(items[2].content).toBe("item 3");

    db.close();
  });

  it("should handle datetime defaults", () => {
    const db = createTestDb(TEST_DB);

    db.exec(`
      CREATE TABLE if not exists items (
        id INTEGER PRIMARY KEY,
        content TEXT,
        path TEXT,
        created_at TEXT DEFAULT (datetime('now')),
        updated_at TEXT DEFAULT (datetime('now'))
      );
    `);

    const stmt = db.prepare("INSERT INTO items (content) VALUES (?)");
    stmt.run("test");
    
    const items = db.prepare("SELECT created_at, updated_at FROM items").all();
    
    expect(items[0].created_at).toBeTruthy();
    expect(items[0].updated_at).toBeTruthy();
    expect(items[0].created_at).toMatch(/\d{4}-\d{2}-\d{2}/);

    db.close();
  });
});