-- SitePulse schema
-- All rows are scoped to a user_id so the API can filter by the authenticated user.

CREATE TABLE IF NOT EXISTS users (
  id            SERIAL PRIMARY KEY,
  email         TEXT UNIQUE NOT NULL,
  name          TEXT NOT NULL,
  password_hash TEXT NOT NULL,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS clients (
  id         SERIAL PRIMARY KEY,
  user_id    INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  name       TEXT NOT NULL,
  email      TEXT,
  phone      TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_clients_user_id ON clients(user_id);

CREATE TABLE IF NOT EXISTS websites (
  id             SERIAL PRIMARY KEY,
  user_id        INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  client_id      INTEGER REFERENCES clients(id) ON DELETE SET NULL,
  name           TEXT NOT NULL,
  domain         TEXT NOT NULL,
  platform       TEXT NOT NULL DEFAULT 'Website',
  health_score   INTEGER NOT NULL DEFAULT 100,
  status         TEXT NOT NULL DEFAULT 'Healthy',
  uptime         NUMERIC(6,2) NOT NULL DEFAULT 100,
  performance    INTEGER NOT NULL DEFAULT 100,
  security       INTEGER NOT NULL DEFAULT 100,
  ssl            TEXT NOT NULL DEFAULT 'Valid',
  last_checked   TEXT,
  check_interval TEXT NOT NULL DEFAULT '5 min',
  notifications  JSONB NOT NULL DEFAULT '{"push":true,"email":true,"slack":false}',
  created_at     TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_websites_user_id ON websites(user_id);
CREATE INDEX IF NOT EXISTS idx_websites_client_id ON websites(client_id);

CREATE TABLE IF NOT EXISTS incidents (
  id               SERIAL PRIMARY KEY,
  user_id          INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  website_id       INTEGER REFERENCES websites(id) ON DELETE SET NULL,
  severity         TEXT NOT NULL DEFAULT 'Warning',
  title            TEXT NOT NULL,
  description      TEXT,
  started_at       TEXT NOT NULL,
  duration_minutes INTEGER,
  resolved_at      TEXT
);

CREATE INDEX IF NOT EXISTS idx_incidents_user_id ON incidents(user_id);
CREATE INDEX IF NOT EXISTS idx_incidents_website_id ON incidents(website_id);