-- Users of the monitoring panel
CREATE TABLE IF NOT EXISTS users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  email TEXT UNIQUE NOT NULL,
  password_hash TEXT NOT NULL,
  is_admin INTEGER NOT NULL DEFAULT 0,
  lang TEXT NOT NULL DEFAULT 'en',
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- Lists group monitors together (e.g. "Main List", "Client A", "Personal").
-- Only the list flagged is_main=1 belongs to the cron-driven main account run.
CREATE TABLE IF NOT EXISTS lists (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  is_main INTEGER NOT NULL DEFAULT 0,
  public_token TEXT UNIQUE,        -- if set, list is viewable at /status/:public_token
  public_enabled INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- Individual monitors: a domain/IP + a check type (http, https, ping-like tcp, or specific port)
CREATE TABLE IF NOT EXISTS monitors (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  list_id INTEGER NOT NULL REFERENCES lists(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  target TEXT NOT NULL,              -- hostname or IP
  type TEXT NOT NULL,                 -- 'http' | 'https' | 'tcp'
  port INTEGER,                       -- required for type=tcp (e.g. 21, 22, 25, 3306); ignored for http/https unless custom
  path TEXT DEFAULT '/',              -- for http/https checks
  expected_status INTEGER DEFAULT 200,-- for http/https checks
  slow_threshold_ms INTEGER DEFAULT 3000, -- above this = "slow" alert
  check_interval_min INTEGER NOT NULL DEFAULT 60,
  consecutive_fail_to_alert INTEGER NOT NULL DEFAULT 2,
  is_active INTEGER NOT NULL DEFAULT 1,
  current_status TEXT NOT NULL DEFAULT 'unknown', -- unknown|up|down|slow
  consecutive_fails INTEGER NOT NULL DEFAULT 0,
  last_checked_at TEXT,
  last_response_ms INTEGER,
  last_error TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- History of every check performed, used for uptime % and graphs
CREATE TABLE IF NOT EXISTS checks (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  monitor_id INTEGER NOT NULL REFERENCES monitors(id) ON DELETE CASCADE,
  status TEXT NOT NULL,       -- up|down|slow
  response_ms INTEGER,
  error TEXT,
  checked_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- Alert log so we don't spam emails
CREATE TABLE IF NOT EXISTS alerts (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  monitor_id INTEGER NOT NULL REFERENCES monitors(id) ON DELETE CASCADE,
  type TEXT NOT NULL,   -- down|up|slow
  message TEXT,
  sent_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS idx_checks_monitor ON checks(monitor_id, checked_at);
CREATE INDEX IF NOT EXISTS idx_monitors_list ON monitors(list_id);