CREATE TABLE IF NOT EXISTS users (
  id SERIAL PRIMARY KEY,
  email TEXT UNIQUE NOT NULL,
  display_name TEXT,
  avatar_url TEXT,
  role TEXT DEFAULT 'member' CHECK (role IN ('admin', 'member')),
  gitea_username TEXT,
  github_login TEXT,
  password_hash TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  last_seen_at TIMESTAMPTZ
);

CREATE TABLE IF NOT EXISTS organizations (
  id SERIAL PRIMARY KEY,
  slug TEXT UNIQUE NOT NULL,
  name TEXT NOT NULL,
  description TEXT,
  shared_password_hash TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS organization_members (
  organization_id INT NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  user_id INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  role TEXT NOT NULL DEFAULT 'member' CHECK (role IN ('owner', 'admin', 'member')),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  PRIMARY KEY (organization_id, user_id)
);

CREATE INDEX IF NOT EXISTS idx_org_members_user ON organization_members(user_id);

CREATE TABLE IF NOT EXISTS audit_log (
  id BIGSERIAL PRIMARY KEY,
  user_id INT REFERENCES users(id),
  action TEXT NOT NULL,
  target TEXT,
  metadata JSONB,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_audit_log_user ON audit_log(user_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_audit_log_action ON audit_log(action, created_at DESC);

CREATE TABLE IF NOT EXISTS portal_pages (
  id SERIAL PRIMARY KEY,
  slug TEXT UNIQUE NOT NULL,
  title TEXT NOT NULL,
  content TEXT,
  source_repo TEXT,
  source_path TEXT,
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS infra_tokens (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  service TEXT NOT NULL,
  location TEXT NOT NULL,
  env_var_name TEXT,
  expires_at TIMESTAMPTZ,
  last_rotated_at TIMESTAMPTZ,
  machines TEXT[] DEFAULT '{}',
  notes TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS infra_services (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  url TEXT,
  tailscale_ip TEXT,
  health_endpoint TEXT,
  last_check_at TIMESTAMPTZ,
  last_status TEXT DEFAULT 'unknown' CHECK (last_status IN ('ok', 'error', 'timeout', 'unknown')),
  cost_monthly TEXT,
  provider TEXT,
  notes TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_infra_tokens_service ON infra_tokens(service);
CREATE INDEX IF NOT EXISTS idx_infra_tokens_expires ON infra_tokens(expires_at);
CREATE INDEX IF NOT EXISTS idx_infra_services_status ON infra_services(last_status);

-- Gateway merge (see db/migrations/004_gateway.sql)
CREATE TABLE IF NOT EXISTS gateway_votes (
  user_email TEXT NOT NULL,
  slug TEXT NOT NULL,
  item_id TEXT NOT NULL,
  voted_at TIMESTAMPTZ DEFAULT NOW(),
  PRIMARY KEY (user_email, slug, item_id)
);

CREATE TABLE IF NOT EXISTS gateway_reads (
  id SERIAL PRIMARY KEY,
  user_email TEXT NOT NULL,
  slug TEXT NOT NULL,
  reaction TEXT NOT NULL,
  read_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE (user_email, slug, reaction)
);

CREATE TABLE IF NOT EXISTS gateway_agents (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id TEXT NOT NULL,
  title TEXT NOT NULL DEFAULT 'New chat',
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS gateway_agent_messages (
  id TEXT PRIMARY KEY,
  agent_id UUID NOT NULL REFERENCES gateway_agents(id) ON DELETE CASCADE,
  role TEXT NOT NULL CHECK (role IN ('user', 'assistant', 'system')),
  parts JSONB NOT NULL DEFAULT '[]',
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_gateway_agent_messages_agent ON gateway_agent_messages(agent_id);

CREATE TABLE IF NOT EXISTS sentinel_kv (
  key TEXT PRIMARY KEY,
  value JSONB NOT NULL,
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS sentinel_telemetry (
  id BIGSERIAL PRIMARY KEY,
  ts TIMESTAMPTZ NOT NULL,
  model TEXT NOT NULL,
  tier TEXT NOT NULL,
  input_tokens INT NOT NULL,
  output_tokens INT NOT NULL,
  cost_usd DOUBLE PRECISION NOT NULL,
  duration_ms INT NOT NULL,
  action TEXT NOT NULL,
  escalated BOOLEAN NOT NULL DEFAULT FALSE,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_sentinel_telemetry_ts ON sentinel_telemetry(ts DESC);