setup.js

const fs = require('fs');
const path = require('path');
const Database = require('better-sqlite3');

const DB_PATH = path.join(__dirname, 'data', 'status.db');

// Ensure data directory exists
if (!fs.existsSync(path.join(__dirname, 'data'))) {
  fs.mkdirSync(path.join(__dirname, 'data'), { recursive: true });
}

const db = new Database(DB_PATH);
db.pragma('journal_mode = WAL');

db.exec(`
  CREATE TABLE IF NOT EXISTS checks (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    service_name TEXT NOT NULL,
    service_url TEXT NOT NULL,
    status TEXT NOT NULL CHECK(status IN ('up', 'down', 'degraded')),
    response_time INTEGER,
    status_code INTEGER,
    error_message TEXT,
    checked_at DATETIME DEFAULT (datetime('now'))
  );

  CREATE INDEX IF NOT EXISTS idx_checks_service ON checks(service_name, checked_at);
  CREATE INDEX IF NOT EXISTS idx_checks_date ON checks(checked_at);

  CREATE TABLE IF NOT EXISTS incidents (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    service_name TEXT NOT NULL,
    title TEXT NOT NULL,
    description TEXT,
    status TEXT NOT NULL CHECK(status IN ('investigating', 'identified', 'monitoring', 'resolved')),
    severity TEXT NOT NULL CHECK(severity IN ('minor', 'major', 'critical')),
    created_at DATETIME DEFAULT (datetime('now')),
    updated_at DATETIME DEFAULT (datetime('now')),
    resolved_at DATETIME
  );

  CREATE INDEX IF NOT EXISTS idx_incidents_service ON incidents(service_name, created_at);
`);

console.log('Database setup complete at', DB_PATH);
db.close();