-- PDF Accessibility Checker - PostgreSQL Schema
-- Run automatically on first Docker Compose startup

CREATE TABLE IF NOT EXISTS jobs (
    id              SERIAL PRIMARY KEY,
    job_id          VARCHAR(64) UNIQUE NOT NULL,
    filename        VARCHAR(255),
    status          VARCHAR(20) DEFAULT 'queued',
    score           INTEGER,
    grade           CHAR(1),
    total_issues    INTEGER,
    critical_count  INTEGER,
    error_count     INTEGER,
    warning_count   INTEGER,
    result_json     JSONB,
    created_at      TIMESTAMP DEFAULT NOW(),
    completed_at    TIMESTAMP,
    processing_time FLOAT,
    api_key_hash    VARCHAR(64),
    ip_address      INET
);

CREATE TABLE IF NOT EXISTS audit_log (
    id          SERIAL PRIMARY KEY,
    job_id      VARCHAR(64),
    action      VARCHAR(50),
    details     JSONB,
    created_at  TIMESTAMP DEFAULT NOW(),
    ip_address  INET
);

CREATE INDEX IF NOT EXISTS idx_jobs_status ON jobs(status);
CREATE INDEX IF NOT EXISTS idx_jobs_created ON jobs(created_at);
CREATE INDEX IF NOT EXISTS idx_jobs_job_id ON jobs(job_id);
CREATE INDEX IF NOT EXISTS idx_audit_job ON audit_log(job_id);
CREATE INDEX IF NOT EXISTS idx_audit_created ON audit_log(created_at);

CREATE TABLE IF NOT EXISTS dismissed_issues (
    id              SERIAL PRIMARY KEY,
    job_id          VARCHAR(64) NOT NULL,
    issue_index     INTEGER NOT NULL,
    reason          VARCHAR(255),
    dismissed_at    TIMESTAMP DEFAULT NOW(),
    UNIQUE(job_id, issue_index)
);

CREATE INDEX IF NOT EXISTS idx_dismissed_job ON dismissed_issues(job_id);