-- Adds team-role-based access to the Team Portal (Support, Finance,
-- Associate Engineer, Lead Engineer), a chat channel between them, and
-- password reset support for platform_admin accounts. CHECK constraint
-- validation for team_role is enforced at the application layer rather
-- than a DB constraint here, so this is always safe to run against an
-- existing platform_admins table with real rows already in it.
ALTER TABLE platform_admins ADD COLUMN IF NOT EXISTS team_role TEXT NOT NULL DEFAULT 'lead_engineer';
ALTER TABLE platform_admins ADD COLUMN IF NOT EXISTS email TEXT;

-- One shared chat room for the whole Team Portal — any department can
-- see and post to it. Polling-based (the frontend refreshes every few
-- seconds), not WebSocket-push — a deliberate, honest scope choice: real
-- push messaging needs a persistent-connection layer (socket.io/ws) that
-- wasn't already part of this stack, and polling gives genuinely "live
-- enough" chat without adding that new infrastructure dependency.
CREATE TABLE IF NOT EXISTS team_chat_messages (
  id              SERIAL PRIMARY KEY,
  sender_id       INTEGER NOT NULL REFERENCES platform_admins(id) ON DELETE CASCADE,
  sender_name     TEXT NOT NULL,
  sender_role     TEXT NOT NULL,
  message         TEXT NOT NULL,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_team_chat_created ON team_chat_messages(created_at);

-- Password reset tokens for platform_admin accounts (the existing
-- password_reset_tokens table is school-scoped via school_id NOT NULL —
-- this is a separate table for the platform level, which has no school).
CREATE TABLE IF NOT EXISTS platform_password_reset_tokens (
  token           TEXT PRIMARY KEY,
  platform_admin_id INTEGER NOT NULL REFERENCES platform_admins(id) ON DELETE CASCADE,
  expires_at      TIMESTAMPTZ NOT NULL,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);