import type Database from "better-sqlite3";

const DDL_STATEMENTS = [
  `CREATE TABLE IF NOT EXISTS modules (
    id          INTEGER PRIMARY KEY,
    name        TEXT NOT NULL UNIQUE,
    path        TEXT NOT NULL,
    description TEXT,
    layer       TEXT NOT NULL,
    created_at  TEXT NOT NULL
  )`,
  `CREATE TABLE IF NOT EXISTS files (
    id         INTEGER PRIMARY KEY,
    module_id  INTEGER REFERENCES modules(id),
    path       TEXT NOT NULL UNIQUE,
    kind       TEXT NOT NULL,
    lines      INTEGER,
    is_server  INTEGER DEFAULT 0,
    is_client  INTEGER DEFAULT 0,
    has_server_only INTEGER DEFAULT 0
  )`,
  `CREATE TABLE IF NOT EXISTS symbols (
    id          INTEGER PRIMARY KEY,
    file_id     INTEGER NOT NULL REFERENCES files(id),
    name        TEXT NOT NULL,
    kind        TEXT NOT NULL,
    is_exported INTEGER DEFAULT 0,
    is_async    INTEGER DEFAULT 0,
    is_server_action INTEGER DEFAULT 0,
    is_public   INTEGER DEFAULT 0,
    signature   TEXT,
    start_line  INTEGER,
    end_line    INTEGER,
    UNIQUE(file_id, name, start_line)
  )`,
  `CREATE TABLE IF NOT EXISTS calls (
    id            INTEGER PRIMARY KEY,
    caller_id     INTEGER NOT NULL REFERENCES symbols(id),
    callee_id     INTEGER REFERENCES symbols(id),
    callee_external TEXT,
    call_line     INTEGER,
    count         INTEGER DEFAULT 1
  )`,
  `CREATE TABLE IF NOT EXISTS file_imports (
    id            INTEGER PRIMARY KEY,
    source_file_id INTEGER NOT NULL REFERENCES files(id),
    imported_file_id INTEGER REFERENCES files(id),
    import_path   TEXT NOT NULL,
    is_type_only  INTEGER DEFAULT 0,
    imported_names TEXT
  )`,
  `CREATE TABLE IF NOT EXISTS module_deps (
    id            INTEGER PRIMARY KEY,
    source_module_id INTEGER NOT NULL REFERENCES modules(id),
    target_module_id INTEGER NOT NULL REFERENCES modules(id),
    dep_type      TEXT NOT NULL,
    UNIQUE(source_module_id, target_module_id, dep_type)
  )`,
  `CREATE TABLE IF NOT EXISTS tech_tags (
    id    INTEGER PRIMARY KEY,
    name  TEXT NOT NULL UNIQUE,
    category TEXT
  )`,
  `CREATE TABLE IF NOT EXISTS symbol_tech_tags (
    symbol_id INTEGER NOT NULL REFERENCES symbols(id),
    tag_id    INTEGER NOT NULL REFERENCES tech_tags(id),
    PRIMARY KEY(symbol_id, tag_id)
  )`,
  `CREATE TABLE IF NOT EXISTS permissions (
    id          INTEGER PRIMARY KEY,
    key         TEXT NOT NULL UNIQUE,
    description TEXT
  )`,
  `CREATE TABLE IF NOT EXISTS routes (
    id         INTEGER PRIMARY KEY,
    path       TEXT NOT NULL UNIQUE,
    kind       TEXT NOT NULL,
    file_id    INTEGER REFERENCES files(id),
    min_permission TEXT
  )`,
  `CREATE TABLE IF NOT EXISTS db_tables (
    id          INTEGER PRIMARY KEY,
    name        TEXT NOT NULL UNIQUE,
    module_id   INTEGER REFERENCES modules(id),
    description TEXT
  )`,
  `CREATE TABLE IF NOT EXISTS scan_meta (
    key   TEXT PRIMARY KEY,
    value TEXT NOT NULL
  )`,
  `CREATE INDEX IF NOT EXISTS idx_symbols_file ON symbols(file_id)`,
  `CREATE INDEX IF NOT EXISTS idx_symbols_name ON symbols(name)`,
  `CREATE INDEX IF NOT EXISTS idx_calls_caller ON calls(caller_id)`,
  `CREATE INDEX IF NOT EXISTS idx_calls_callee ON calls(callee_id)`,
  `CREATE INDEX IF NOT EXISTS idx_file_imports_source ON file_imports(source_file_id)`,
  `CREATE INDEX IF NOT EXISTS idx_file_imports_target ON file_imports(imported_file_id)`,
  `CREATE INDEX IF NOT EXISTS idx_symbol_tech_tags_tag ON symbol_tech_tags(tag_id)`,
];

export function initSchema(db: Database.Database): void {
  for (const stmt of DDL_STATEMENTS) {
    db.exec(stmt);
  }
}

export function clearAllData(db: Database.Database): void {
  const tables = [
    "symbol_tech_tags",
    "calls",
    "file_imports",
    "module_deps",
    "symbols",
    "files",
    "modules",
    "tech_tags",
    "permissions",
    "routes",
    "db_tables",
    "scan_meta",
  ];
  db.exec("PRAGMA foreign_keys = OFF");
  for (const table of tables) {
    db.exec(`DELETE FROM ${table}`);
  }
  db.exec("PRAGMA foreign_keys = ON");
}