-- Migration 064: VVT Master Libraries — 8 global reference tables
-- These are shared across all tenants (no tenant_id).

BEGIN;

-- 1. Data Subjects (Betroffenenkategorien)
CREATE TABLE IF NOT EXISTS vvt_lib_data_subjects (
    id VARCHAR(50) PRIMARY KEY,
    label_de VARCHAR(200) NOT NULL,
    description_de TEXT,
    art9_relevant BOOLEAN DEFAULT FALSE,
    typical_for JSONB DEFAULT '[]'::jsonb,
    sort_order INTEGER DEFAULT 0,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 2. Data Categories (Datenkategorien — hierarchisch)
CREATE TABLE IF NOT EXISTS vvt_lib_data_categories (
    id VARCHAR(50) PRIMARY KEY,
    parent_id VARCHAR(50) REFERENCES vvt_lib_data_categories(id) ON DELETE SET NULL,
    label_de VARCHAR(200) NOT NULL,
    description_de TEXT,
    is_art9 BOOLEAN DEFAULT FALSE,
    is_art10 BOOLEAN DEFAULT FALSE,
    risk_weight INTEGER DEFAULT 1 CHECK (risk_weight BETWEEN 1 AND 5),
    default_retention_rule VARCHAR(50),
    default_legal_basis VARCHAR(50),
    sort_order INTEGER DEFAULT 0,
    created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_vvt_lib_data_categories_parent ON vvt_lib_data_categories(parent_id);

-- 3. Recipients (Empfaengerkategorien)
CREATE TABLE IF NOT EXISTS vvt_lib_recipients (
    id VARCHAR(50) PRIMARY KEY,
    type VARCHAR(20) NOT NULL CHECK (type IN ('INTERNAL', 'PROCESSOR', 'CONTROLLER', 'AUTHORITY')),
    label_de VARCHAR(200) NOT NULL,
    description_de TEXT,
    is_third_country BOOLEAN DEFAULT FALSE,
    country VARCHAR(5),
    sort_order INTEGER DEFAULT 0,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 4. Legal Bases (Rechtsgrundlagen)
CREATE TABLE IF NOT EXISTS vvt_lib_legal_bases (
    id VARCHAR(50) PRIMARY KEY,
    article VARCHAR(50) NOT NULL,
    type VARCHAR(30) NOT NULL CHECK (type IN ('CONSENT', 'CONTRACT', 'LEGAL_OBLIGATION', 'VITAL_INTEREST', 'PUBLIC_TASK', 'LEGITIMATE_INTEREST', 'ART9', 'NATIONAL')),
    label_de VARCHAR(300) NOT NULL,
    description_de TEXT,
    is_art9 BOOLEAN DEFAULT FALSE,
    typical_national_law VARCHAR(100),
    sort_order INTEGER DEFAULT 0,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 5. Retention Rules (Aufbewahrungsfristen)
CREATE TABLE IF NOT EXISTS vvt_lib_retention_rules (
    id VARCHAR(50) PRIMARY KEY,
    label_de VARCHAR(300) NOT NULL,
    description_de TEXT,
    legal_basis VARCHAR(200),
    duration INTEGER NOT NULL,
    duration_unit VARCHAR(10) NOT NULL CHECK (duration_unit IN ('DAYS', 'MONTHS', 'YEARS')),
    start_event VARCHAR(200),
    deletion_procedure VARCHAR(500),
    sort_order INTEGER DEFAULT 0,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 6. Transfer Mechanisms (Uebermittlungsmechanismen)
CREATE TABLE IF NOT EXISTS vvt_lib_transfer_mechanisms (
    id VARCHAR(50) PRIMARY KEY,
    label_de VARCHAR(300) NOT NULL,
    description_de TEXT,
    article VARCHAR(50),
    requires_tia BOOLEAN DEFAULT FALSE,
    sort_order INTEGER DEFAULT 0,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 7. Purposes (Verarbeitungszwecke)
CREATE TABLE IF NOT EXISTS vvt_lib_purposes (
    id VARCHAR(50) PRIMARY KEY,
    label_de VARCHAR(300) NOT NULL,
    description_de TEXT,
    typical_legal_basis VARCHAR(50),
    typical_for JSONB DEFAULT '[]'::jsonb,
    sort_order INTEGER DEFAULT 0,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 8. TOMs (Technisch-Organisatorische Massnahmen)
CREATE TABLE IF NOT EXISTS vvt_lib_toms (
    id VARCHAR(50) PRIMARY KEY,
    category VARCHAR(30) NOT NULL CHECK (category IN ('accessControl', 'confidentiality', 'integrity', 'availability', 'separation')),
    label_de VARCHAR(300) NOT NULL,
    description_de TEXT,
    art32_reference VARCHAR(100),
    sort_order INTEGER DEFAULT 0,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

COMMIT;