-- AI Analysis Schemas and Tables for kinosave
-- Idempotent DDL - safe to run multiple times
-- Created: 2026-01-30

-- ============================================================================
-- SCHEMAS
-- ============================================================================

CREATE SCHEMA IF NOT EXISTS features;
CREATE SCHEMA IF NOT EXISTS baseline;
CREATE SCHEMA IF NOT EXISTS analysis;
CREATE SCHEMA IF NOT EXISTS narratives;
CREATE SCHEMA IF NOT EXISTS audits;

-- ============================================================================
-- BASELINE SCHEMA TABLES
-- ============================================================================

-- Calibration version tracking
CREATE TABLE IF NOT EXISTS baseline.calibration_versions (
    id SERIAL PRIMARY KEY,
    version VARCHAR(50) NOT NULL UNIQUE,
    description TEXT,
    effective_date DATE NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    created_by VARCHAR(100),
    is_active BOOLEAN DEFAULT FALSE
);

-- Calibration parameters for each version
CREATE TABLE IF NOT EXISTS baseline.calibration_params (
    id SERIAL PRIMARY KEY,
    version_id INTEGER NOT NULL REFERENCES baseline.calibration_versions(id) ON DELETE CASCADE,
    param_name VARCHAR(100) NOT NULL,
    param_value NUMERIC,
    param_json JSONB,
    unit VARCHAR(50),
    description TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(version_id, param_name)
);

-- ============================================================================
-- FEATURES SCHEMA TABLES
-- ============================================================================

-- Extracted features from tests
CREATE TABLE IF NOT EXISTS features.test_features (
    id SERIAL PRIMARY KEY,
    test_id INTEGER NOT NULL,
    patient_id INTEGER,
    feature_name VARCHAR(100) NOT NULL,
    feature_value NUMERIC,
    feature_json JSONB,
    extraction_version VARCHAR(50),
    extracted_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    metadata JSONB
);

-- Create index for faster lookups if not exists
CREATE INDEX IF NOT EXISTS idx_test_features_test_id ON features.test_features(test_id);
CREATE INDEX IF NOT EXISTS idx_test_features_patient_id ON features.test_features(patient_id);
CREATE INDEX IF NOT EXISTS idx_test_features_feature_name ON features.test_features(feature_name);

-- ============================================================================
-- ANALYSIS SCHEMA TABLES
-- ============================================================================

-- Computed scores from analysis
CREATE TABLE IF NOT EXISTS analysis.test_scores (
    id SERIAL PRIMARY KEY,
    test_id INTEGER NOT NULL,
    patient_id INTEGER,
    score_type VARCHAR(100) NOT NULL,
    score_value NUMERIC,
    confidence NUMERIC,
    percentile NUMERIC,
    reference_range_low NUMERIC,
    reference_range_high NUMERIC,
    interpretation VARCHAR(50),
    analysis_version VARCHAR(50),
    calibration_version_id INTEGER REFERENCES baseline.calibration_versions(id),
    computed_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    metadata JSONB
);

CREATE INDEX IF NOT EXISTS idx_test_scores_test_id ON analysis.test_scores(test_id);
CREATE INDEX IF NOT EXISTS idx_test_scores_patient_id ON analysis.test_scores(patient_id);
CREATE INDEX IF NOT EXISTS idx_test_scores_score_type ON analysis.test_scores(score_type);

-- ============================================================================
-- NARRATIVES SCHEMA TABLES
-- ============================================================================

-- AI-generated narrative reports
CREATE TABLE IF NOT EXISTS narratives.generated (
    id SERIAL PRIMARY KEY,
    test_id INTEGER NOT NULL,
    patient_id INTEGER,
    narrative_type VARCHAR(100) NOT NULL,
    content TEXT NOT NULL,
    ai_model VARCHAR(100),
    prompt_version VARCHAR(50),
    generation_params JSONB,
    generated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(50) DEFAULT 'pending',
    metadata JSONB
);

CREATE INDEX IF NOT EXISTS idx_narratives_generated_test_id ON narratives.generated(test_id);
CREATE INDEX IF NOT EXISTS idx_narratives_generated_status ON narratives.generated(status);

-- Clinician-approved narratives
CREATE TABLE IF NOT EXISTS narratives.approved (
    id SERIAL PRIMARY KEY,
    generated_id INTEGER REFERENCES narratives.generated(id) ON DELETE SET NULL,
    test_id INTEGER NOT NULL,
    patient_id INTEGER,
    narrative_type VARCHAR(100) NOT NULL,
    original_content TEXT,
    approved_content TEXT NOT NULL,
    edits_made BOOLEAN DEFAULT FALSE,
    edit_summary TEXT,
    approved_by VARCHAR(100) NOT NULL,
    approved_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    metadata JSONB
);

CREATE INDEX IF NOT EXISTS idx_narratives_approved_test_id ON narratives.approved(test_id);
CREATE INDEX IF NOT EXISTS idx_narratives_approved_approved_by ON narratives.approved(approved_by);

-- ============================================================================
-- AUDITS SCHEMA TABLES
-- ============================================================================

-- Weekly review records
CREATE TABLE IF NOT EXISTS audits.weekly_reviews (
    id SERIAL PRIMARY KEY,
    review_week_start DATE NOT NULL,
    review_week_end DATE NOT NULL,
    reviewer VARCHAR(100) NOT NULL,
    tests_reviewed INTEGER DEFAULT 0,
    narratives_reviewed INTEGER DEFAULT 0,
    issues_found INTEGER DEFAULT 0,
    issues_resolved INTEGER DEFAULT 0,
    notes TEXT,
    status VARCHAR(50) DEFAULT 'in_progress',
    started_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    completed_at TIMESTAMP WITH TIME ZONE,
    metadata JSONB,
    UNIQUE(review_week_start, reviewer)
);

CREATE INDEX IF NOT EXISTS idx_weekly_reviews_week ON audits.weekly_reviews(review_week_start, review_week_end);
CREATE INDEX IF NOT EXISTS idx_weekly_reviews_reviewer ON audits.weekly_reviews(reviewer);

-- Proposed changes to calibration or analysis
CREATE TABLE IF NOT EXISTS audits.proposed_changes (
    id SERIAL PRIMARY KEY,
    change_type VARCHAR(100) NOT NULL,
    target_schema VARCHAR(100),
    target_table VARCHAR(100),
    target_id INTEGER,
    current_value JSONB,
    proposed_value JSONB,
    rationale TEXT,
    proposed_by VARCHAR(100) NOT NULL,
    proposed_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    reviewed_by VARCHAR(100),
    reviewed_at TIMESTAMP WITH TIME ZONE,
    status VARCHAR(50) DEFAULT 'pending',
    review_notes TEXT,
    metadata JSONB
);

CREATE INDEX IF NOT EXISTS idx_proposed_changes_status ON audits.proposed_changes(status);
CREATE INDEX IF NOT EXISTS idx_proposed_changes_change_type ON audits.proposed_changes(change_type);

-- ============================================================================
-- COMMENTS
-- ============================================================================

COMMENT ON SCHEMA features IS 'Schema for extracted test features';
COMMENT ON SCHEMA baseline IS 'Schema for calibration baselines and parameters';
COMMENT ON SCHEMA analysis IS 'Schema for computed analysis scores';
COMMENT ON SCHEMA narratives IS 'Schema for AI-generated and approved narratives';
COMMENT ON SCHEMA audits IS 'Schema for audit trails and reviews';

COMMENT ON TABLE baseline.calibration_versions IS 'Tracks versions of calibration parameters';
COMMENT ON TABLE baseline.calibration_params IS 'Individual calibration parameters per version';
COMMENT ON TABLE features.test_features IS 'Extracted features from patient tests';
COMMENT ON TABLE analysis.test_scores IS 'Computed scores and interpretations';
COMMENT ON TABLE narratives.generated IS 'AI-generated narrative reports';
COMMENT ON TABLE narratives.approved IS 'Clinician-reviewed and approved narratives';
COMMENT ON TABLE audits.weekly_reviews IS 'Weekly quality review records';
COMMENT ON TABLE audits.proposed_changes IS 'Proposed modifications requiring approval';
