-- ─────────────────────────────────────────
-- DROP ALL EXISTING TABLES
-- ─────────────────────────────────────────
DROP TABLE IF EXISTS reports CASCADE;
DROP TABLE IF EXISTS capa CASCADE;
DROP TABLE IF EXISTS rca CASCADE;
DROP TABLE IF EXISTS exceptions CASCADE;
DROP TABLE IF EXISTS three_boom CASCADE;
DROP TABLE IF EXISTS uploads CASCADE;
DROP TABLE IF EXISTS users CASCADE;

-- ─────────────────────────────────────────
-- 1. USERS — auth
-- POST /auth/register
-- POST /auth/login
-- ─────────────────────────────────────────
CREATE TABLE users (
    id            SERIAL PRIMARY KEY,
    name          VARCHAR(200) NOT NULL,
    email         VARCHAR(200) UNIQUE NOT NULL,
    password_hash VARCHAR(300) NOT NULL,
    created_at    TIMESTAMP DEFAULT NOW()
);
CREATE INDEX idx_users_email ON users(email);


-- ─────────────────────────────────────────
-- 2. UPLOADS — POST /upload
-- chat_id is the primary investigation identifier
-- ─────────────────────────────────────────
CREATE TABLE uploads (
    id           SERIAL PRIMARY KEY,
    chat_id      VARCHAR(100) UNIQUE NOT NULL,
    batch_id     VARCHAR(50),
    filenames    JSONB,
    total_files  INTEGER,
    response     JSONB,
    created_at   TIMESTAMP DEFAULT NOW()
);
CREATE INDEX idx_uploads_chat_id ON uploads(chat_id);
CREATE INDEX idx_uploads_batch_id ON uploads(batch_id);


-- ─────────────────────────────────────────
-- 3. THREE_BOOM — POST /agent/validate
-- Stores: classify + extract + validate results
-- ─────────────────────────────────────────
CREATE TABLE three_boom (
    id                  SERIAL PRIMARY KEY,
    chat_id             VARCHAR(100) NOT NULL REFERENCES uploads(chat_id) ON DELETE CASCADE,
    batch_id            VARCHAR(50),
    product_name        VARCHAR(200),
    plant               VARCHAR(200),
    classifications     JSONB,
    readiness_summary   JSONB,
    extraction_summary  JSONB,
    validate            JSONB,
    created_at          TIMESTAMP DEFAULT NOW()
);
CREATE INDEX idx_three_boom_chat_id ON three_boom(chat_id);


-- ─────────────────────────────────────────
-- 4. EXCEPTIONS — POST /agent/exceptions
-- ─────────────────────────────────────────
CREATE TABLE exceptions (
    id               SERIAL PRIMARY KEY,
    chat_id          VARCHAR(100) NOT NULL REFERENCES uploads(chat_id) ON DELETE CASCADE,
    batch_id         VARCHAR(50),
    total_exceptions INTEGER,
    critical         INTEGER,
    major            INTEGER,
    minor            INTEGER,
    risk_level       VARCHAR(50),
    response         JSONB,
    created_at       TIMESTAMP DEFAULT NOW()
);
CREATE INDEX idx_exceptions_chat_id ON exceptions(chat_id);


-- ─────────────────────────────────────────
-- 5. RCA — POST /agent/rca + POST /rca-confirm
-- ─────────────────────────────────────────
CREATE TABLE rca (
    id               SERIAL PRIMARY KEY,
    chat_id          VARCHAR(100) NOT NULL REFERENCES uploads(chat_id) ON DELETE CASCADE,
    batch_id         VARCHAR(50),
    risk_level       VARCHAR(50),
    trip_score       NUMERIC(4,2),
    approval_status  VARCHAR(100),
    confirmed        BOOLEAN DEFAULT FALSE,
    confirmed_by     VARCHAR(200),
    confirmed_at     VARCHAR(50),
    cause_id         VARCHAR(100),
    role             VARCHAR(100),
    response         JSONB,
    created_at       TIMESTAMP DEFAULT NOW()
);
CREATE INDEX idx_rca_chat_id ON rca(chat_id);


-- ─────────────────────────────────────────
-- 6. CAPA — POST /agent/capa + POST /capa-approve
-- ─────────────────────────────────────────
CREATE TABLE capa (
    id                 SERIAL PRIMARY KEY,
    chat_id            VARCHAR(100) NOT NULL REFERENCES uploads(chat_id) ON DELETE CASCADE,
    batch_id           VARCHAR(50),
    deviation_id       VARCHAR(100),
    severity           VARCHAR(50),
    total_actions      INTEGER,
    plan_status        VARCHAR(50) DEFAULT 'Pending',
    approved_by        VARCHAR(200),
    approved_at        VARCHAR(50),
    send_for_execution BOOLEAN DEFAULT FALSE,
    response           JSONB,
    created_at         TIMESTAMP DEFAULT NOW()
);
CREATE INDEX idx_capa_chat_id ON capa(chat_id);


-- ─────────────────────────────────────────
-- 7. REPORTS — POST /agent/report
-- ─────────────────────────────────────────
CREATE TABLE reports (
    id               SERIAL PRIMARY KEY,
    chat_id          VARCHAR(100) NOT NULL REFERENCES uploads(chat_id) ON DELETE CASCADE,
    batch_id         VARCHAR(50),
    ref_id           VARCHAR(100),
    severity         VARCHAR(50),
    generated_date   VARCHAR(50),
    pdf_filename     VARCHAR(300),
    download_url     TEXT,
    evidence_score   INTEGER,
    response         JSONB,
    created_at       TIMESTAMP DEFAULT NOW()
);
CREATE INDEX idx_reports_chat_id ON reports(chat_id);