-- OI Test Centre, PostgreSQL 16+. Apply with npm run db:migrate.
CREATE TABLE users (
 id uuid PRIMARY KEY, email text NOT NULL UNIQUE, name text NOT NULL,
 role text NOT NULL CHECK(role IN ('admin','technical','tester')),
 password_hash text NOT NULL, active boolean NOT NULL DEFAULT true,
 must_change_password boolean NOT NULL DEFAULT true, created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE auth_sessions (
 token_hash text PRIMARY KEY, user_id uuid NOT NULL REFERENCES users(id),
 expires_at timestamptz NOT NULL, created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE login_attempts (email text PRIMARY KEY, attempts integer NOT NULL DEFAULT 0, window_start timestamptz NOT NULL DEFAULT now());
CREATE TABLE documents (id uuid PRIMARY KEY, code text NOT NULL, title text NOT NULL, version integer NOT NULL CHECK(version>0), updated_at timestamptz NOT NULL DEFAULT now());
CREATE UNIQUE INDEX documents_code_unique ON documents(lower(code));
CREATE TABLE files (
 id uuid PRIMARY KEY, owner_id uuid NOT NULL REFERENCES users(id), filename text NOT NULL,
 mime text NOT NULL, size integer NOT NULL CHECK(size>0 AND size<=20971520), sha256 text NOT NULL,
 bytes bytea NOT NULL, created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE document_versions (
 document_id uuid NOT NULL REFERENCES documents(id), version integer NOT NULL,
 code text NOT NULL, title text NOT NULL, file_id uuid NOT NULL REFERENCES files(id),
 uploaded_by uuid NOT NULL REFERENCES users(id), created_at timestamptz NOT NULL DEFAULT now(),
 PRIMARY KEY(document_id,version)
);
CREATE TABLE questions (id text PRIMARY KEY, version integer NOT NULL CHECK(version>0), payload jsonb NOT NULL, updated_at timestamptz NOT NULL DEFAULT now());
CREATE TABLE question_versions (question_id text NOT NULL REFERENCES questions(id), version integer NOT NULL, payload jsonb NOT NULL, PRIMARY KEY(question_id,version));
CREATE TABLE question_documents (question_id text NOT NULL REFERENCES questions(id), document_id uuid NOT NULL REFERENCES documents(id), PRIMARY KEY(question_id,document_id));
CREATE TABLE test_sessions (
 id uuid PRIMARY KEY, user_id uuid NOT NULL REFERENCES users(id), title text NOT NULL,
 oi_name text NOT NULL, build text NOT NULL, environment text NOT NULL, channel text NOT NULL,
 selected_question_ids jsonb NOT NULL DEFAULT '[]', status text NOT NULL CHECK(status IN ('Active','Paused','Finished')),
 started_at timestamptz NOT NULL DEFAULT now(), last_activity_at timestamptz NOT NULL DEFAULT now(), finished_at timestamptz
);
CREATE UNIQUE INDEX one_active_session_per_user ON test_sessions(user_id) WHERE status='Active';
CREATE TABLE session_events (
 id uuid PRIMARY KEY, session_id uuid NOT NULL REFERENCES test_sessions(id), user_id uuid NOT NULL REFERENCES users(id),
 action text NOT NULL, created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE runs (
 id uuid PRIMARY KEY, question_id text NOT NULL REFERENCES questions(id), session_id uuid NOT NULL REFERENCES test_sessions(id),
 user_id uuid NOT NULL REFERENCES users(id), previous_run_id uuid REFERENCES runs(id), payload jsonb NOT NULL, created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX runs_question ON runs(question_id,created_at DESC);
CREATE INDEX runs_session ON runs(session_id);
CREATE TABLE run_files (run_id uuid NOT NULL REFERENCES runs(id), file_id uuid NOT NULL REFERENCES files(id), PRIMARY KEY(run_id,file_id));
CREATE TABLE search_checks (
 id uuid PRIMARY KEY, run_id uuid NOT NULL REFERENCES runs(id), user_id uuid NOT NULL REFERENCES users(id),
 external_id text UNIQUE, payload jsonb NOT NULL, created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX search_checks_run ON search_checks(run_id);
CREATE TABLE issues (id text PRIMARY KEY, question_id text NOT NULL REFERENCES questions(id), payload jsonb NOT NULL, updated_at timestamptz NOT NULL DEFAULT now());
CREATE TABLE issue_runs (issue_id text NOT NULL REFERENCES issues(id), run_id uuid NOT NULL REFERENCES runs(id), PRIMARY KEY(issue_id,run_id));
CREATE TABLE comments (id uuid PRIMARY KEY, run_id uuid NOT NULL REFERENCES runs(id), user_id uuid NOT NULL REFERENCES users(id), body text NOT NULL, created_at timestamptz NOT NULL DEFAULT now());
CREATE TABLE drafts (user_id uuid NOT NULL REFERENCES users(id), question_id text NOT NULL REFERENCES questions(id), payload jsonb NOT NULL, revision integer NOT NULL DEFAULT 1, updated_at timestamptz NOT NULL DEFAULT now(), PRIMARY KEY(user_id,question_id));
CREATE TABLE events (id uuid PRIMARY KEY, user_id uuid REFERENCES users(id), type text NOT NULL, target text NOT NULL, detail text NOT NULL, created_at timestamptz NOT NULL DEFAULT now());
CREATE INDEX events_created ON events(created_at DESC);
