-- QC Platform Database Schema
-- SQLite database for unified HM QC platform

-- QC Reports table
-- Stores metadata for reports generated by HM QC and other modules
CREATE TABLE IF NOT EXISTS qc_reports (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    report_type VARCHAR(50) NOT NULL,  -- 'hm_qc', 'video_qc', 'video_master', 'box_import'
    job_number VARCHAR(100),           -- Job/campaign number for grouping
    filename VARCHAR(255) NOT NULL,    -- Original filename
    file_path VARCHAR(500) NOT NULL,   -- Relative path to HTML report file
    score REAL,                        -- Overall score (0-100) for scored reports
    status VARCHAR(50) NOT NULL,       -- 'passed', 'warning', 'failed', 'error', 'skipped'
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by VARCHAR(100),           -- User email or ID
    metadata_json TEXT,                -- JSON string for flexible metadata

    -- Indexes for common queries
    INDEX idx_report_type (report_type),
    INDEX idx_job_number (job_number),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at)
);

-- Progress Sessions table
-- Tracks progress of long-running QC operations
CREATE TABLE IF NOT EXISTS progress_sessions (
    id VARCHAR(100) PRIMARY KEY,       -- UUID or session ID
    percent REAL NOT NULL DEFAULT 0.0, -- Progress percentage (0-100)
    message VARCHAR(500),              -- Human-readable progress message
    details TEXT,                      -- JSON string with additional details
    status VARCHAR(50) NOT NULL DEFAULT 'running',  -- 'running', 'completed', 'failed', 'cancelled'
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Sample data for testing (commented out)
-- INSERT INTO qc_reports (report_type, job_number, filename, file_path, score, status, created_by)
-- VALUES ('hm_qc', '2069052', 'test_report.html', 'storage/reports/hm_qc/2069052/test_report.html', 85.5, 'passed', 'test@example.com');