-- Migration 003: UCCA (Use-Case Compliance & Feasibility Advisor) Schema
-- Creates table for storing AI use-case assessments

-- ============================================================================
-- UCCA Assessments Table
-- ============================================================================

CREATE TABLE IF NOT EXISTS ucca_assessments (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES compliance_tenants(id) ON DELETE CASCADE,
    namespace_id UUID REFERENCES compliance_namespaces(id) ON DELETE SET NULL,

    -- Metadata
    title VARCHAR(500),
    policy_version VARCHAR(50) NOT NULL DEFAULT '1.0.0',
    status VARCHAR(50) DEFAULT 'completed',

    -- Input
    intake JSONB NOT NULL,                          -- Full UseCaseIntake
    use_case_text_stored BOOLEAN DEFAULT FALSE,     -- Opt-in for raw text storage
    use_case_text_hash VARCHAR(64),                 -- SHA-256 hash (always stored)

    -- Results - Main verdict
    feasibility VARCHAR(20) NOT NULL,               -- YES/CONDITIONAL/NO
    risk_level VARCHAR(20) NOT NULL,                -- MINIMAL/LOW/MEDIUM/HIGH/UNACCEPTABLE
    complexity VARCHAR(10) NOT NULL,                -- LOW/MEDIUM/HIGH
    risk_score INT NOT NULL DEFAULT 0,              -- 0-100

    -- Results - Details (JSONB for flexibility)
    triggered_rules JSONB DEFAULT '[]',             -- Array of TriggeredRule
    required_controls JSONB DEFAULT '[]',           -- Array of RequiredControl
    recommended_architecture JSONB DEFAULT '[]',    -- Array of PatternRecommendation
    forbidden_patterns JSONB DEFAULT '[]',          -- Array of ForbiddenPattern
    example_matches JSONB DEFAULT '[]',             -- Array of ExampleMatch

    -- Results - Flags
    dsfa_recommended BOOLEAN DEFAULT FALSE,
    art22_risk BOOLEAN DEFAULT FALSE,               -- Art. 22 GDPR automated decision risk
    training_allowed VARCHAR(50),                   -- YES/CONDITIONAL/NO

    -- LLM Explanation (optional)
    explanation_text TEXT,
    explanation_generated_at TIMESTAMPTZ,
    explanation_model VARCHAR(100),

    -- Domain classification
    domain VARCHAR(50),

    -- Audit trail
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    created_by UUID NOT NULL
);

-- ============================================================================
-- Indexes for Performance
-- ============================================================================

-- Primary lookup by tenant
CREATE INDEX idx_ucca_tenant ON ucca_assessments(tenant_id);

-- List view with sorting by date
CREATE INDEX idx_ucca_tenant_created ON ucca_assessments(tenant_id, created_at DESC);

-- Filter by feasibility
CREATE INDEX idx_ucca_tenant_feasibility ON ucca_assessments(tenant_id, feasibility);

-- Filter by domain
CREATE INDEX idx_ucca_tenant_domain ON ucca_assessments(tenant_id, domain);

-- Filter by risk level
CREATE INDEX idx_ucca_tenant_risk ON ucca_assessments(tenant_id, risk_level);

-- JSONB index for searching within triggered_rules
CREATE INDEX idx_ucca_triggered_rules ON ucca_assessments USING GIN (triggered_rules);

-- ============================================================================
-- Comments for Documentation
-- ============================================================================

COMMENT ON TABLE ucca_assessments IS 'UCCA (Use-Case Compliance & Feasibility Advisor) assessments - stores evaluated AI use cases with GDPR compliance verdicts';

COMMENT ON COLUMN ucca_assessments.intake IS 'Full UseCaseIntake JSON including data types, purpose, automation level, hosting, etc.';
COMMENT ON COLUMN ucca_assessments.use_case_text_stored IS 'Whether the raw use case description text is stored (opt-in)';
COMMENT ON COLUMN ucca_assessments.use_case_text_hash IS 'SHA-256 hash of use case text for deduplication without storing raw text';
COMMENT ON COLUMN ucca_assessments.feasibility IS 'Overall verdict: YES (low risk), CONDITIONAL (needs controls), NO (not allowed)';
COMMENT ON COLUMN ucca_assessments.risk_score IS 'Numeric risk score 0-100 calculated from triggered rules';
COMMENT ON COLUMN ucca_assessments.triggered_rules IS 'Array of rules that were triggered during evaluation';
COMMENT ON COLUMN ucca_assessments.required_controls IS 'Array of controls/mitigations that must be implemented';
COMMENT ON COLUMN ucca_assessments.recommended_architecture IS 'Array of recommended architecture patterns';
COMMENT ON COLUMN ucca_assessments.forbidden_patterns IS 'Array of patterns that must NOT be used';
COMMENT ON COLUMN ucca_assessments.example_matches IS 'Array of matching didactic examples';
COMMENT ON COLUMN ucca_assessments.dsfa_recommended IS 'Whether a Data Protection Impact Assessment is recommended';
COMMENT ON COLUMN ucca_assessments.art22_risk IS 'Whether there is risk under Art. 22 GDPR (automated individual decisions)';
COMMENT ON COLUMN ucca_assessments.training_allowed IS 'Whether model training with the data is allowed';
COMMENT ON COLUMN ucca_assessments.explanation_text IS 'LLM-generated explanation in German (optional)';