schema.sql

-- Enable standard uuid extension if needed (PostgreSQL 13+ has gen_random_uuid() built-in)
-- CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- =========================================================================
-- 1. BASE MULTI-TENANCY
-- =========================================================================

CREATE TABLE tenant (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name        TEXT NOT NULL UNIQUE,
    code        TEXT NOT NULL UNIQUE,
    created_by  TEXT,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    meta        JSONB
);

-- =========================================================================
-- 2. METADATA REFERENCES
-- =========================================================================

CREATE TABLE leader_category (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name        TEXT NOT NULL,
    description TEXT,
    tenant_code TEXT NOT NULL REFERENCES tenant(code) ON DELETE CASCADE,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE programs (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    leaders_id  UUID NOT NULL REFERENCES leader_category(id) ON DELETE CASCADE,
    name        TEXT NOT NULL,
    description TEXT,
    tenant_code TEXT NOT NULL REFERENCES tenant(code) ON DELETE CASCADE,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- =========================================================================
-- 3. PROMPT MANAGEMENT SYSTEM (Generic & Shared)
-- =========================================================================

CREATE TABLE prompts (
    id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name          TEXT NOT NULL UNIQUE,
    analysis_type TEXT NOT NULL, -- 'pii', 'theme', 'environment', etc.
    created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE prompt_version (
    id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    prompt_id     UUID NOT NULL REFERENCES prompts(id) ON DELETE CASCADE,
    version       INT NOT NULL,
    system_prompt TEXT NOT NULL,
    user_prompt   TEXT NOT NULL, -- Template containing {{variables}}
    is_active     BOOLEAN NOT NULL DEFAULT TRUE,
    created_by    TEXT,
    change_note   TEXT,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
    
    CONSTRAINT unique_prompt_version UNIQUE (prompt_id, version)
);

-- =========================================================================
-- 4. CENTRAL INGESTION TRACKING
-- =========================================================================

CREATE TABLE submissions (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    session_id      TEXT UNIQUE, -- 32-character random alphanumeric string
    submission_id   TEXT NOT NULL,
    tenant_code     TEXT NOT NULL REFERENCES tenant(code) ON DELETE CASCADE,
    submission_type TEXT NOT NULL, -- 'discussion', 'story', etc.
    user_id         TEXT, -- Future login integration
    user_name       TEXT,
    role            TEXT,
    state           TEXT,
    district        TEXT,
    organization    TEXT,
    submission_date TIMESTAMPTZ,
    process_status  JSONB,
    status          TEXT NOT NULL DEFAULT 'pending', -- 'pending', 'processing', 'success', 'failed'
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    program_id      UUID REFERENCES programs(id) ON DELETE SET NULL,
    leader_id       UUID REFERENCES leader_category(id) ON DELETE SET NULL,
    
    CONSTRAINT unique_submission_tenant UNIQUE (submission_id, tenant_code)
);

-- =========================================================================
-- 5. SOURCE PAYLOADS (Raw CSV / Kafka Output)
-- =========================================================================

CREATE TABLE discussion_submissions (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    submission_id       TEXT NOT NULL,
    tenant_code         TEXT NOT NULL,
    title               TEXT,
    challenges          TEXT[], -- one array element per discrete statement (see operations.py's _normalize_statement_list)
    solutions           TEXT[], -- same format as challenges
    author              TEXT,
    language            TEXT,
    image_urls          TEXT[] DEFAULT '{}',
    blur_image_urls     TEXT[] DEFAULT '{}',
    pdf_urls            TEXT[] DEFAULT '{}',
    masked_pdf_urls     TEXT[] DEFAULT '{}',
    transcript_link     TEXT,
    pii_masked          BOOLEAN NOT NULL DEFAULT FALSE,
    pii_masked_at       TEXT[] DEFAULT '{}',
    abusive_masked_at   TEXT[] DEFAULT '{}',
    created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
     meta_data           JSONB,
    
    FOREIGN KEY (submission_id, tenant_code) 
        REFERENCES submissions(submission_id, tenant_code) ON DELETE CASCADE
);

CREATE TABLE story_submissions (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    submission_id       TEXT NOT NULL,
    tenant_code         TEXT NOT NULL,
    title               TEXT,
    objective           TEXT,
    challenge           TEXT,
    action_steps        TEXT,
    impact              TEXT,
    duration            TEXT,
    blurb               TEXT,
    content             TEXT,
    image_urls          TEXT[] DEFAULT '{}',
    blur_image_urls     TEXT[] DEFAULT '{}',
    pdf_urls            TEXT[] DEFAULT '{}',
    masked_pdf_urls     TEXT[] DEFAULT '{}',
    transcript_link     TEXT,
    pii_masked          BOOLEAN NOT NULL DEFAULT FALSE,
    pii_masked_at       TEXT[] DEFAULT '{}',
    abusive_masked_at   TEXT[] DEFAULT '{}',
    created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
    meta_data           JSONB,
    
    FOREIGN KEY (submission_id, tenant_code) 
        REFERENCES submissions(submission_id, tenant_code) ON DELETE CASCADE
);

-- =========================================================================
-- 6. EXECUTION & AUDIT LOGGING
-- =========================================================================

CREATE TABLE llm_logs (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    submission_id       TEXT NOT NULL,
    tenant_code         TEXT NOT NULL,
    model_name          TEXT NOT NULL,
    model_version       TEXT,
    analysis_type       TEXT NOT NULL,
    prompt_version_id   UUID NOT NULL REFERENCES prompt_version(id),
    prompt_tokens       INT NOT NULL DEFAULT 0,
    completion_tokens   INT NOT NULL DEFAULT 0,
    total_tokens        INT GENERATED ALWAYS AS (prompt_tokens + completion_tokens) STORED,
    status              TEXT NOT NULL, -- 'success', 'failed', 'timeout', 'retried'
    error_message       TEXT,
    called_at           TIMESTAMPTZ NOT NULL DEFAULT now(),
    meta_data           JSONB,
    
    FOREIGN KEY (submission_id, tenant_code) 
        REFERENCES submissions(submission_id, tenant_code) ON DELETE CASCADE
);

-- =========================================================================
-- 7. TAXONOMY (Global)
-- =========================================================================

CREATE TABLE themes (
    id                      UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name                    TEXT NOT NULL UNIQUE,
    definitions             TEXT,
    keywords                TEXT,
    examples                TEXT,
    status                  TEXT -- 'Approved', 'Rejected', 'Merged', 'Draft'
    -- total_objective_count   INTEGER DEFAULT 0, --newly added can be removed 
    -- original_statement_text TEXT[] DEFAULT '{}' --newly added can be removed 
);

-- =========================================================================
-- 8. THEMATIC & ENVIRONMENTAL EXTRACTION OUTPUTS
-- =========================================================================

CREATE TABLE analysis_results (
    id                      UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    submission_id           TEXT NOT NULL,
    tenant_code             TEXT NOT NULL,
    theme_id                UUID REFERENCES themes(id) ON DELETE SET NULL, -- Nullable for environmental analysis
    analysis_type           TEXT NOT NULL, -- 'theme', 'environment'
    statements              TEXT,
    statement_type          TEXT, -- Column/context identifier (e.g. 'challenges', 'solutions', 'objective')
    improvement_environment TEXT,
    similarity_score        FLOAT, -- Cosine similarity from local embedding match
    confidence_score        FLOAT, -- Confidence score from local embedding match
    justification           TEXT,
    multi_theme_mapped      BOOLEAN NOT NULL DEFAULT FALSE,
    category_type           TEXT, -- 'Standard', 'Others', 'Unknown/Unclear', 'Flagged'
    meta_data               JSONB,
    
    FOREIGN KEY (submission_id, tenant_code)    
        REFERENCES submissions(submission_id, tenant_code) ON DELETE CASCADE
);

-- =========================================================================
-- 9. QUALITATIVE SCORING & SUMMARIES
-- =========================================================================

CREATE TABLE ranking (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    submission_id       TEXT NOT NULL,
    tenant_code         TEXT NOT NULL,
    criteria_data       JSONB NOT NULL, -- Rich criteria-specific scores & justifications
    composite_score     FLOAT NOT NULL,
    tier                TEXT,           -- 'Tier 1', 'Tier 2', etc.
    overall_summary     TEXT,           -- Concise LLM qualitative report summary
    meta_data           JSONB,
    
    FOREIGN KEY (submission_id, tenant_code) 
        REFERENCES submissions(submission_id, tenant_code) ON DELETE CASCADE
);

-- =========================================================================
-- 10. DYNAMIC KPI METRICS (EAV Model)
-- =========================================================================

CREATE TABLE metric_definitions (
    code            TEXT PRIMARY KEY, -- 'men', 'women', 'duration'
    label           TEXT NOT NULL,
    value_type      TEXT NOT NULL DEFAULT 'numeric' CHECK (value_type IN ('numeric', 'text')),
    submission_type TEXT,            -- Scoped payload type ('discussion', 'story')
    is_active       BOOLEAN NOT NULL DEFAULT TRUE,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE submission_metrics (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    submission_id   TEXT NOT NULL,
    tenant_code     TEXT NOT NULL,
    metric_code     TEXT NOT NULL REFERENCES metric_definitions(code) ON DELETE CASCADE,
    numeric_value   INT,
    text_value      TEXT,
    
    CONSTRAINT one_value_required CHECK (numeric_value IS NOT NULL OR text_value IS NOT NULL),
    CONSTRAINT unique_submission_metric UNIQUE (submission_id, tenant_code, metric_code),
    
    FOREIGN KEY (submission_id, tenant_code) 
        REFERENCES submissions(submission_id, tenant_code) ON DELETE CASCADE
);

-- =========================================================================
-- INDEXES FOR HIGH-PERFORMANCE ANALYTICS
-- =========================================================================

-- Multi-tenancy isolation indexes
CREATE INDEX idx_submissions_tenant ON submissions (tenant_code);
CREATE INDEX idx_discussion_tenant ON discussion_submissions (tenant_code);
CREATE INDEX idx_story_tenant ON story_submissions (tenant_code);
CREATE INDEX idx_llm_logs_tenant ON llm_logs (tenant_code);
CREATE INDEX idx_analysis_results_tenant ON analysis_results (tenant_code);
CREATE INDEX idx_ranking_tenant ON ranking (tenant_code);
CREATE INDEX idx_submission_metrics_tenant ON submission_metrics (tenant_code);

-- Composite query mapping indexes (Foreign key performance optimization)
CREATE INDEX idx_discussion_submission_mapping ON discussion_submissions (submission_id, tenant_code);
CREATE INDEX idx_story_submission_mapping ON story_submissions (submission_id, tenant_code);
CREATE INDEX idx_llm_logs_submission_mapping ON llm_logs (submission_id, tenant_code);
CREATE INDEX idx_analysis_results_submission_mapping ON analysis_results (submission_id, tenant_code);
CREATE INDEX idx_ranking_submission_mapping ON ranking (submission_id, tenant_code);
CREATE INDEX idx_submission_metrics_mapping ON submission_metrics (submission_id, tenant_code);

-- Program & Leader category filtering
CREATE INDEX idx_submissions_program ON submissions (program_id);
CREATE INDEX idx_submissions_leader ON submissions (leader_id);
CREATE INDEX idx_programs_leader ON programs (leaders_id);

-- Theme-specific analytics
CREATE INDEX idx_analysis_results_theme ON analysis_results (theme_id) WHERE theme_id IS NOT NULL;
CREATE INDEX idx_analysis_results_type ON analysis_results (analysis_type);

-- Prompt version active check
CREATE INDEX idx_prompt_version_active ON prompt_version (prompt_id) WHERE is_active = TRUE;

-- LLM log analysis performance
CREATE INDEX idx_llm_logs_called ON llm_logs (called_at DESC);
CREATE INDEX idx_llm_logs_analysis_type ON llm_logs (analysis_type);

-- Dynamic metric filtering
CREATE INDEX idx_submission_metrics_code ON submission_metrics (metric_code);

-- -- =========================================================================
-- -- EXPANDED VIEWS (To bypass JOINs in BI tools like Metabase)
-- -- =========================================================================

-- CREATE OR REPLACE VIEW story_submissions_expanded AS
-- SELECT 
--     s.id AS submission_uuid,
--     ss.id AS story_payload_uuid,
--     ss.submission_id,
--     ss.tenant_code,
--     s.session_id,
--     s.submission_type,
--     s.user_id,
--     s.user_name,
--     s.role,
--     s.state,
--     s.district,
--     s.organization,
--     s.submission_date,
--     s.status AS ingestion_status,
--     s.program_id,
--     s.leader_id,
--     ss.title,
--     ss.objective,
--     ss.challenge,
--     ss.action_steps,
--     ss.impact,
--     ss.duration,
--     ss.blurb,
--     ss.content,
--     ss.image_urls,
--     ss.blur_image_urls,
--     ss.pdf_urls,
--     ss.transcript_link,
--     ss.pii_masked,
--     ss.created_at,
--     ss.updated_at
-- FROM story_submissions ss
-- JOIN submissions s 
--   ON ss.submission_id = s.submission_id 
--  AND ss.tenant_code = s.tenant_code;

-- CREATE OR REPLACE VIEW discussion_submissions_expanded AS
-- SELECT 
--     s.id AS submission_uuid,
--     ds.id AS discussion_payload_uuid,
--     ds.submission_id,
--     ds.tenant_code,
--     s.session_id,
--     s.submission_type,
--     s.user_id,
--     s.user_name,
--     s.role,
--     s.state,
--     s.district,
--     s.organization,
--     s.submission_date,
--     s.status AS ingestion_status,
--     s.program_id,
--     s.leader_id,
--     ds.title,
--     ds.challenges,
--     ds.solutions,
--     ds.author,
--     ds.language,
--     ds.image_urls,
--     ds.blur_image_urls,
--     ds.pdf_urls,
--     ds.transcript_link,
--     ds.pii_masked,
--     ds.created_at,
--     ds.updated_at
-- FROM discussion_submissions ds
-- JOIN submissions s 
--   ON ds.submission_id = s.submission_id 
--  AND ds.tenant_code = s.tenant_code;