// lib/db.js — shared SQLite database (schema owned by sql/schema.sql,
// applied idempotently on every panel start).
import fs from 'node:fs';
import path from 'node:path';
import Database from 'better-sqlite3';
import { config } from './config.js';

fs.mkdirSync(path.dirname(config.dbPath), { recursive: true });

export const db = new Database(config.dbPath);
db.pragma('busy_timeout = 5000');

db.exec(fs.readFileSync(config.schemaPath, 'utf8'));

// Migration guard for DBs created before commit_msg existed in schema.sql
// (CREATE TABLE IF NOT EXISTS does not add columns to existing tables).
{
  const depCols = db.prepare('PRAGMA table_info(deployments)').all().map((c) => c.name);
  if (!depCols.includes('commit_msg')) {
    db.exec('ALTER TABLE deployments ADD COLUMN commit_msg TEXT');
  }
  const projCols = db.prepare('PRAGMA table_info(projects)').all().map((c) => c.name);
  if (!projCols.includes('variants')) {
    db.exec('ALTER TABLE projects ADD COLUMN variants TEXT');
  }
  if (!projCols.includes('group_name')) {
    db.exec('ALTER TABLE projects ADD COLUMN group_name TEXT');
  }
  if (!projCols.includes('git_account_id')) {
    db.exec('ALTER TABLE projects ADD COLUMN git_account_id INTEGER');
    db.exec('ALTER TABLE projects ADD COLUMN repo_full TEXT');
  }
  if (!projCols.includes('multi_label')) {
    db.exec('ALTER TABLE projects ADD COLUMN multi_label INTEGER NOT NULL DEFAULT 0');
  }
  if (!projCols.includes('auto_deploy')) {
    // 1 = pushes to the tracked branch (webhook) queue a deployment; 0 = the
    // webhook is acknowledged but ignored — only manual deploys run.
    db.exec('ALTER TABLE projects ADD COLUMN auto_deploy INTEGER NOT NULL DEFAULT 1');
  }
  if (!projCols.includes('runtime')) {
    // node | laravel — mirrors the registry RUNTIME key, fixed at creation
    db.exec("ALTER TABLE projects ADD COLUMN runtime TEXT NOT NULL DEFAULT 'node'");
  }
  for (const tbl of ['traffic_daily', 'traffic_monthly']) {
    const cols = db.prepare(`PRAGMA table_info(${tbl})`).all().map((c) => c.name);
    if (cols.length && !cols.includes('bytes_cache')) {
      db.exec(`ALTER TABLE ${tbl} ADD COLUMN bytes_cache INTEGER NOT NULL DEFAULT 0`);
      db.exec(`ALTER TABLE ${tbl} ADD COLUMN bytes_origin INTEGER NOT NULL DEFAULT 0`);
    }
  }
}

// ---- multi-user migration ----
// Seed the initial admin from the env password hash, give ownership of all
// pre-existing rows to it, and rebuild groups to a per-user primary key.
{
  const userCount = db.prepare('SELECT COUNT(*) AS n FROM users').get().n;
  if (userCount === 0 && config.adminPasswordHash) {
    db.prepare("INSERT INTO users (username, password_hash, role) VALUES ('admin', ?, 'admin')")
      .run(config.adminPasswordHash);
  }
  const adminRow = db.prepare("SELECT id FROM users WHERE role = 'admin' ORDER BY id LIMIT 1").get();
  const adminId = adminRow ? adminRow.id : 1;

  const projCols2 = db.prepare('PRAGMA table_info(projects)').all().map((c) => c.name);
  if (!projCols2.includes('owner_user_id')) {
    db.exec('ALTER TABLE projects ADD COLUMN owner_user_id INTEGER');
    db.prepare('UPDATE projects SET owner_user_id = ? WHERE owner_user_id IS NULL').run(adminId);
  }
  const gaCols = db.prepare('PRAGMA table_info(git_accounts)').all().map((c) => c.name);
  if (!gaCols.includes('owner_user_id')) {
    db.exec('ALTER TABLE git_accounts ADD COLUMN owner_user_id INTEGER');
    db.prepare('UPDATE git_accounts SET owner_user_id = ? WHERE owner_user_id IS NULL').run(adminId);
  }
  const oaCols = db.prepare('PRAGMA table_info(oauth_apps)').all().map((c) => c.name);
  if (oaCols.length && !oaCols.includes('client_secret')) {
    db.exec('ALTER TABLE oauth_apps ADD COLUMN client_secret TEXT');
  }
  if (!gaCols.includes('auth_type')) {
    db.exec("ALTER TABLE git_accounts ADD COLUMN auth_type TEXT NOT NULL DEFAULT 'pat'");
    db.exec('ALTER TABLE git_accounts ADD COLUMN refresh_token TEXT');
    db.exec('ALTER TABLE git_accounts ADD COLUMN token_expires_at INTEGER');
  }
  const grpCols = db.prepare('PRAGMA table_info(groups)').all().map((c) => c.name);
  if (!grpCols.includes('owner_user_id')) {
    const tx = db.transaction(() => {
      db.exec('CREATE TABLE groups_new (owner_user_id INTEGER NOT NULL, name TEXT NOT NULL, PRIMARY KEY (owner_user_id, name))');
      db.prepare('INSERT INTO groups_new (owner_user_id, name) SELECT ?, name FROM groups').run(adminId);
      db.exec('DROP TABLE groups');
      db.exec('ALTER TABLE groups_new RENAME TO groups');
    });
    tx();
  }
}

const q = {
  insertProject: db.prepare(
    'INSERT INTO projects (name, repo_url, branch, build_cmd, output_dir, webhook_secret, runtime) ' +
    'VALUES (@name, @repo_url, @branch, @build_cmd, @output_dir, @webhook_secret, @runtime)'
  ),
  deleteProject: db.prepare('DELETE FROM projects WHERE name = ?'),
  getProject: db.prepare('SELECT * FROM projects WHERE name = ?'),
  listProjects: db.prepare('SELECT * FROM projects ORDER BY name'),
  insertDeployment: db.prepare(
    'INSERT INTO deployments (app, source, commit_sha, status, step, log_path) ' +
    'VALUES (@app, @source, @commit_sha, @status, @step, @log_path)'
  ),
  getDeployment: db.prepare('SELECT * FROM deployments WHERE id = ?'),
  historyByApp: db.prepare('SELECT * FROM deployments WHERE app = ? ORDER BY id DESC LIMIT ?'),
  historyByAppBefore: db.prepare('SELECT * FROM deployments WHERE app = ? AND id < ? ORDER BY id DESC LIMIT ?'),
  deleteDeploymentsByApp: db.prepare('DELETE FROM deployments WHERE app = ?'),
  activeDeployments: db.prepare(
    "SELECT * FROM deployments WHERE status IN ('queued','cloning','building','deploying') ORDER BY id"
  ),
  latestPerApp: db.prepare(
    'SELECT d.* FROM deployments d ' +
    'JOIN (SELECT app, MAX(id) AS mid FROM deployments GROUP BY app) m ON m.mid = d.id'
  ),
  latestLivePerApp: db.prepare(
    'SELECT d.* FROM deployments d ' +
    "JOIN (SELECT app, MAX(id) AS mid FROM deployments WHERE status = 'live' GROUP BY app) m ON m.mid = d.id"
  ),
  latestLiveByApp: db.prepare(
    "SELECT * FROM deployments WHERE app = ? AND status = 'live' ORDER BY id DESC LIMIT 1"
  ),
  markStale: db.prepare(
    "UPDATE deployments SET status = 'failed', step = 'panel restarted', finished_at = datetime('now') " +
    "WHERE status IN ('queued','cloning','building','deploying')"
  ),
  trafficLast30: db.prepare(
    "SELECT * FROM traffic_daily WHERE app = ? AND day >= date('now', '-29 days') ORDER BY day"
  ),
  trafficTodayPerApp: db.prepare(
    "SELECT app, requests, bytes FROM traffic_daily WHERE day = date('now')"
  ),
  // Archived (closed-ledger) months + any month not yet archived (normally
  // just the running one) computed live from the daily rows.
  trafficMonthly: db.prepare(
    'SELECT month, requests, bytes, hit, miss, s5xx, bytes_cache, bytes_origin, 1 AS final ' +
    'FROM traffic_monthly WHERE app = @app ' +
    'UNION ALL ' +
    "SELECT substr(day, 1, 7) AS month, SUM(requests), SUM(bytes), SUM(hit), SUM(miss), SUM(s5xx), SUM(bytes_cache), SUM(bytes_origin), 0 " +
    'FROM traffic_daily WHERE app = @app ' +
    'AND substr(day, 1, 7) NOT IN (SELECT month FROM traffic_monthly WHERE app = @app) ' +
    'GROUP BY substr(day, 1, 7) ' +
    'ORDER BY month'
  ),
};

export function nowSql() {
  return new Date().toISOString().replace('T', ' ').slice(0, 19);
}

export function insertProject(p) { q.insertProject.run({ runtime: 'node', ...p }); }
export function deleteProject(name) { q.deleteProject.run(name); }
export function setProjectGroup(name, group) {
  db.prepare('UPDATE projects SET group_name = ? WHERE name = ?').run(group || null, name);
}
export function listGroups(ownerId) {
  return db.prepare(
    'SELECT name FROM groups WHERE owner_user_id = @o ' +
    'UNION SELECT DISTINCT group_name FROM projects WHERE group_name IS NOT NULL AND owner_user_id = @o ORDER BY 1'
  ).all({ o: ownerId }).map((r) => r.name);
}
export function groupCounts(ownerId) {
  const counts = {};
  for (const g of listGroups(ownerId)) counts[g] = 0;
  const rows = db.prepare(
    'SELECT group_name, COUNT(*) AS n FROM projects WHERE group_name IS NOT NULL AND owner_user_id = ? GROUP BY group_name'
  ).all(ownerId);
  for (const r of rows) counts[r.group_name] = r.n;
  return counts;
}
export function addGroup(ownerId, name) {
  db.prepare('INSERT OR IGNORE INTO groups (owner_user_id, name) VALUES (?, ?)').run(ownerId, name);
}
export function deleteGroup(ownerId, name) {
  db.prepare('DELETE FROM groups WHERE owner_user_id = ? AND name = ?').run(ownerId, name);
}
export function listGitAccounts(ownerId) {
  return db.prepare('SELECT id, provider, label, api_base, auth_type, created_at FROM git_accounts WHERE owner_user_id = ? ORDER BY provider, label').all(ownerId);
}
export function getGitAccount(id) {
  return db.prepare('SELECT * FROM git_accounts WHERE id = ?').get(id);
}
export function addGitAccount({ owner_user_id, provider, label, token, api_base }) {
  const r = db.prepare('INSERT INTO git_accounts (owner_user_id, provider, label, token, api_base) VALUES (?, ?, ?, ?, ?)')
    .run(owner_user_id, provider, label, token, api_base || null);
  return Number(r.lastInsertRowid);
}
export function deleteGitAccount(id, ownerId) {
  db.prepare('DELETE FROM git_accounts WHERE id = ? AND owner_user_id = ?').run(id, ownerId);
}
export function addOauthGitAccount({ owner_user_id, provider, label, token, api_base, refresh_token, token_expires_at }) {
  const r = db.prepare(
    "INSERT INTO git_accounts (owner_user_id, provider, label, token, api_base, auth_type, refresh_token, token_expires_at) VALUES (?, ?, ?, ?, ?, 'oauth', ?, ?)"
  ).run(owner_user_id, provider, label, token, api_base || null, refresh_token || null, token_expires_at || null);
  return Number(r.lastInsertRowid);
}
export function updateGitAccountToken(id, token, refreshToken, expiresAt) {
  db.prepare('UPDATE git_accounts SET token = ?, refresh_token = ?, token_expires_at = ? WHERE id = ?')
    .run(token, refreshToken || null, expiresAt || null, id);
}
// User-initiated "Reconnect" (re-running the OAuth flow against an existing
// account row instead of Connect creating a new one) — keeps the same id, so
// every project's git_account_id link survives the token refresh untouched.
// Ownership-checked, unlike updateGitAccountToken above (that one's only
// ever called from the trusted freshAccount() background-refresh path on an
// account already loaded from the db).
export function reconnectOauthGitAccount(id, ownerId, { label, token, refresh_token, token_expires_at }) {
  const r = db.prepare(
    'UPDATE git_accounts SET label = ?, token = ?, refresh_token = ?, token_expires_at = ? WHERE id = ? AND owner_user_id = ?'
  ).run(label, token, refresh_token || null, token_expires_at || null, id, ownerId);
  return r.changes > 0;
}
export function getOauthApp(provider) {
  return db.prepare('SELECT * FROM oauth_apps WHERE provider = ?').get(provider);
}
export function setOauthApp({ provider, client_id, client_secret, api_base }) {
  db.prepare('INSERT INTO oauth_apps (provider, client_id, client_secret, api_base) VALUES (?, ?, ?, ?) ' +
    'ON CONFLICT(provider) DO UPDATE SET client_id = excluded.client_id, client_secret = excluded.client_secret, api_base = excluded.api_base')
    .run(provider, client_id, client_secret || null, api_base || null);
}
export function deleteOauthApp(provider) {
  db.prepare('DELETE FROM oauth_apps WHERE provider = ?').run(provider);
}

// ---- users ----
export function getUser(id) {
  return db.prepare('SELECT * FROM users WHERE id = ?').get(id);
}
export function getUserByUsername(username) {
  return db.prepare('SELECT * FROM users WHERE username = ?').get(username);
}
export function listUsers() {
  return db.prepare(
    'SELECT u.id, u.username, u.role, u.created_at, ' +
    '(SELECT COUNT(*) FROM projects p WHERE p.owner_user_id = u.id) AS project_count ' +
    'FROM users u ORDER BY u.username'
  ).all();
}
export function addUser({ username, password_hash, role }) {
  const r = db.prepare('INSERT INTO users (username, password_hash, role) VALUES (?, ?, ?)')
    .run(username, password_hash, role);
  return Number(r.lastInsertRowid);
}
export function deleteUser(id) {
  db.prepare('DELETE FROM users WHERE id = ?').run(id);
}
export function updateUserPassword(id, password_hash) {
  db.prepare('UPDATE users SET password_hash = ? WHERE id = ?').run(password_hash, id);
}
export function countAdmins() {
  return db.prepare("SELECT COUNT(*) AS n FROM users WHERE role = 'admin'").get().n;
}
export function listProjectsFor(user) {
  if (user && user.role === 'admin') return q.listProjects.all();
  return db.prepare('SELECT * FROM projects WHERE owner_user_id = ? ORDER BY name').all(user ? user.id : -1);
}
export function setProjectBuildCmd(name, cmd) {
  db.prepare('UPDATE projects SET build_cmd = ? WHERE name = ?').run(cmd, name);
}
export function setProjectMultiLabel(name, on) {
  db.prepare('UPDATE projects SET multi_label = ? WHERE name = ?').run(on ? 1 : 0, name);
}
export function setProjectAutoDeploy(name, on) {
  db.prepare('UPDATE projects SET auto_deploy = ? WHERE name = ?').run(on ? 1 : 0, name);
}
export function setProjectBranch(name, branch) {
  db.prepare('UPDATE projects SET branch = ? WHERE name = ?').run(branch, name);
}
export function setProjectGit(name, gitAccountId, repoFull) {
  db.prepare('UPDATE projects SET git_account_id = ?, repo_full = ? WHERE name = ?')
    .run(gitAccountId || null, repoFull || null, name);
}
export function setProjectRepoUrl(name, repoUrl) {
  db.prepare('UPDATE projects SET repo_url = ? WHERE name = ?').run(repoUrl || null, name);
}

export function renameGroup(ownerId, oldName, newName) {
  const tx = db.transaction(() => {
    db.prepare('INSERT OR IGNORE INTO groups (owner_user_id, name) VALUES (?, ?)').run(ownerId, newName);
    db.prepare('UPDATE projects SET group_name = ? WHERE group_name = ? AND owner_user_id = ?').run(newName, oldName, ownerId);
    db.prepare('DELETE FROM groups WHERE owner_user_id = ? AND name = ?').run(ownerId, oldName);
  });
  tx();
}
export function setProjectOwner(name, ownerId) {
  db.prepare('UPDATE projects SET owner_user_id = ? WHERE name = ?').run(ownerId, name);
}
export function getProject(name) { return q.getProject.get(name); }
export function listProjects() { return q.listProjects.all(); }

export function insertDeployment({ app, source = 'panel', commit_sha = null, status = 'queued', step = null, log_path = null }) {
  const r = q.insertDeployment.run({ app, source, commit_sha, status, step, log_path });
  return Number(r.lastInsertRowid);
}

const DEP_FIELDS = new Set(['status', 'step', 'commit_sha', 'commit_msg', 'log_path', 'release_dir', 'finished_at']);

export function updateDeployment(id, fields) {
  const params = { id };
  const sets = [];
  for (const key of Object.keys(fields)) {
    if (!DEP_FIELDS.has(key)) continue;
    sets.push(`${key} = @${key}`);
    params[key] = fields[key] === undefined ? null : fields[key];
  }
  if (sets.length === 0) return;
  db.prepare(`UPDATE deployments SET ${sets.join(', ')} WHERE id = @id`).run(params);
}

export function getDeployment(id) { return q.getDeployment.get(id); }
export function historyByApp(app, limit = 30, before = null) {
  return before
    ? q.historyByAppBefore.all(app, before, limit)
    : q.historyByApp.all(app, limit);
}
export function deleteDeploymentsByApp(app) { q.deleteDeploymentsByApp.run(app); }
export function deleteTrafficByApp(app) {
  db.prepare('DELETE FROM traffic_daily WHERE app = ?').run(app);
  db.prepare('DELETE FROM traffic_monthly WHERE app = ?').run(app);
}
export function activeDeployments() { return q.activeDeployments.all(); }
export function latestPerApp() { return q.latestPerApp.all(); }
export function latestLivePerApp() { return q.latestLivePerApp.all(); }
export function latestLiveByApp(app) { return q.latestLiveByApp.get(app); }
export function markStale() { return q.markStale.run(); }
export function trafficLast30(app) { return q.trafficLast30.all(app); }
export function trafficMonthly(app) { return q.trafficMonthly.all({ app }); }
export function trafficTodayPerApp() {
  const map = {};
  for (const r of q.trafficTodayPerApp.all()) map[r.app] = r;
  return map;
}