-- RCS Authentication & Session Schema
-- Phase 1: users, session_tokens, invitations
-- Phase 2: teams, team_members, team_environments, session_owners, session_shares, sessions

CREATE TABLE IF NOT EXISTS users (
  id TEXT NOT NULL PRIMARY KEY,
  username TEXT UNIQUE NOT NULL,
  password_hash TEXT NOT NULL,
  role TEXT NOT NULL CHECK (role IN ('admin', 'member', 'guest')),
  created_at TEXT NOT NULL,
  last_login_at TEXT
);

CREATE TABLE IF NOT EXISTS session_tokens (
  token_hash TEXT NOT NULL PRIMARY KEY,
  user_id TEXT NOT NULL REFERENCES users(id),
  kind TEXT NOT NULL CHECK (kind IN ('access', 'refresh')),
  expires_at TEXT NOT NULL,
  revoked_at TEXT,
  created_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS invitations (
  token_hash TEXT NOT NULL PRIMARY KEY,
  team_id TEXT,
  role TEXT NOT NULL,
  expires_at TEXT NOT NULL,
  max_uses INTEGER DEFAULT 1,
  uses INTEGER DEFAULT 0,
  created_by TEXT NOT NULL REFERENCES users(id),
  consumed_at TEXT,
  consumed_by TEXT REFERENCES users(id)
);

-- Phase 2 tables

CREATE TABLE IF NOT EXISTS teams (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  slug TEXT UNIQUE NOT NULL,
  description TEXT,
  created_by TEXT NOT NULL REFERENCES users(id),
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL,
  deleted_at TEXT
);

CREATE TABLE IF NOT EXISTS team_members (
  team_id TEXT NOT NULL REFERENCES teams(id),
  user_id TEXT NOT NULL REFERENCES users(id),
  role TEXT NOT NULL CHECK (role IN ('owner', 'admin', 'member')),
  added_at TEXT NOT NULL,
  added_by TEXT NOT NULL REFERENCES users(id),
  PRIMARY KEY (team_id, user_id)
);

CREATE TABLE IF NOT EXISTS team_environments (
  team_id TEXT NOT NULL REFERENCES teams(id),
  environment_id TEXT NOT NULL,
  PRIMARY KEY (team_id, environment_id)
);

CREATE TABLE IF NOT EXISTS session_owners (
  session_id TEXT NOT NULL,
  owner_type TEXT NOT NULL CHECK (owner_type IN ('user', 'team')),
  owner_id TEXT NOT NULL,
  PRIMARY KEY (session_id, owner_type, owner_id)
);

CREATE TABLE IF NOT EXISTS session_shares (
  id TEXT PRIMARY KEY,
  session_id TEXT NOT NULL,
  granted_to_user TEXT REFERENCES users(id),
  granted_to_team TEXT REFERENCES teams(id),
  permission TEXT NOT NULL CHECK (permission IN ('read', 'write')),
  expires_at TEXT,
  granted_by TEXT NOT NULL REFERENCES users(id),
  granted_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS environments (
  id TEXT PRIMARY KEY,
  secret_hash TEXT NOT NULL,
  team_id TEXT,
  registered_by TEXT,
  machine_name TEXT,
  worker_type TEXT NOT NULL DEFAULT 'claude_code',
  status TEXT NOT NULL DEFAULT 'active',
  last_poll_at TEXT,
  created_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS sessions (
  id TEXT PRIMARY KEY,
  environment_id TEXT,
  title TEXT,
  status TEXT NOT NULL DEFAULT 'idle',
  source TEXT NOT NULL DEFAULT 'remote-control',
  visibility TEXT NOT NULL DEFAULT 'private',
  permission_mode TEXT,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);

-- Performance indexes
CREATE INDEX IF NOT EXISTS idx_session_owners_owner ON session_owners(owner_type, owner_id);
CREATE INDEX IF NOT EXISTS idx_session_owners_session ON session_owners(session_id);
CREATE INDEX IF NOT EXISTS idx_team_members_user ON team_members(user_id);
CREATE INDEX IF NOT EXISTS idx_team_members_team ON team_members(team_id);
CREATE INDEX IF NOT EXISTS idx_session_shares_user ON session_shares(granted_to_user);
CREATE INDEX IF NOT EXISTS idx_session_shares_team ON session_shares(granted_to_team);
CREATE INDEX IF NOT EXISTS idx_session_shares_session ON session_shares(session_id);
CREATE INDEX IF NOT EXISTS idx_sessions_visibility ON sessions(visibility);
CREATE INDEX IF NOT EXISTS idx_sessions_environment ON sessions(environment_id);
CREATE INDEX IF NOT EXISTS idx_session_tokens_user ON session_tokens(user_id);
CREATE INDEX IF NOT EXISTS idx_environments_team ON environments(team_id);
