-- Compliance & PDPL Support Tables
-- 031_compliance_tables.sql

CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Data Subject Requests (PDPL Article 7-11)
CREATE TABLE IF NOT EXISTS data_subject_requests (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    request_type VARCHAR(50) NOT NULL CHECK (request_type IN ('access', 'rectification', 'erasure', 'portability', 'object')),
    subject_name VARCHAR(255) NOT NULL,
    subject_email VARCHAR(255) NOT NULL,
    subject_id VARCHAR(255),
    justification TEXT,
    status VARCHAR(50) DEFAULT 'pending' CHECK (status IN ('pending', 'in_progress', 'completed', 'rejected')),
    requested_at TIMESTAMPTZ DEFAULT NOW(),
    completed_at TIMESTAMPTZ,
    response_data JSONB,
    processed_by UUID REFERENCES users(id) ON DELETE SET NULL,
    notes TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Consent Records (PDPL Article 5-6)
CREATE TABLE IF NOT EXISTS consent_records (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    data_subject_id VARCHAR(255) NOT NULL,
    consent_type VARCHAR(100) NOT NULL,
    consent_given BOOLEAN NOT NULL,
    given_at TIMESTAMPTZ,
    withdrawn_at TIMESTAMPTZ,
    purpose TEXT NOT NULL,
    legal_basis VARCHAR(100) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Cross-border Transfers (PDPL Article 29)
CREATE TABLE IF NOT EXISTS cross_border_transfers (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    destination_country VARCHAR(100) NOT NULL,
    data_types TEXT[] NOT NULL,
    purpose TEXT NOT NULL,
    legal_basis VARCHAR(100) NOT NULL,
    recipient VARCHAR(255),
    transferred_at TIMESTAMPTZ DEFAULT NOW(),
    approved_by UUID REFERENCES users(id) ON DELETE SET NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- ISO 27001 Controls Evidence
CREATE TABLE IF NOT EXISTS iso27001_controls_evidence (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    control_id VARCHAR(50) NOT NULL,
    control_domain VARCHAR(100) NOT NULL,
    evidence_type VARCHAR(50) NOT NULL,
    evidence_data JSONB NOT NULL,
    collected_at TIMESTAMPTZ DEFAULT NOW(),
    collected_by UUID REFERENCES users(id) ON DELETE SET NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Indexes
CREATE INDEX IF NOT EXISTS idx_data_subject_requests_status ON data_subject_requests(status);
CREATE INDEX IF NOT EXISTS idx_data_subject_requests_subject_email ON data_subject_requests(subject_email);
CREATE INDEX IF NOT EXISTS idx_consent_records_subject_id ON consent_records(data_subject_id);
CREATE INDEX IF NOT EXISTS idx_cross_border_transfers_country ON cross_border_transfers(destination_country);
CREATE INDEX IF NOT EXISTS idx_iso27001_controls_control_id ON iso27001_controls_evidence(control_id);

