-- Migration: Compliance Tables (ISO 27001, PDPL, GDPR)
-- 037_compliance_tables.sql

-- ISO Controls Mapping
CREATE TABLE IF NOT EXISTS compliance_controls (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    standard VARCHAR(50) NOT NULL, -- 'ISO27001', 'ISO27701', 'PDPL', 'GDPR'
    control_id VARCHAR(50) NOT NULL, -- e.g., 'A.8.1.1'
    name VARCHAR(255) NOT NULL,
    description TEXT,
    status VARCHAR(20) DEFAULT 'not_implemented', -- 'implemented', 'partial', 'not_implemented'
    evidence JSONB DEFAULT '[]'::jsonb,
    last_verified TIMESTAMPTZ,
    verified_by UUID REFERENCES users(id),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(standard, control_id)
);

CREATE INDEX IF NOT EXISTS idx_compliance_controls_standard ON compliance_controls(standard);
CREATE INDEX IF NOT EXISTS idx_compliance_controls_status ON compliance_controls(status);

-- Data Subject Requests (PDPL / GDPR)
CREATE TABLE IF NOT EXISTS dsr_requests (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    type VARCHAR(20) NOT NULL CHECK (type IN ('export', 'delete', 'portability', 'rectification')),
    data_subject_email VARCHAR(255) NOT NULL,
    justification TEXT,
    status VARCHAR(20) DEFAULT 'pending', -- 'pending', 'in_progress', 'completed', 'rejected'
    requested_by UUID REFERENCES users(id),
    requested_at TIMESTAMPTZ DEFAULT NOW(),
    completed_at TIMESTAMPTZ,
    response_data JSONB,
    rejection_reason TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_dsr_requests_email ON dsr_requests(data_subject_email);
CREATE INDEX IF NOT EXISTS idx_dsr_requests_status ON dsr_requests(status);
CREATE INDEX IF NOT EXISTS idx_dsr_requests_type ON dsr_requests(type);

-- Consent Tracking (PDPL)
CREATE TABLE IF NOT EXISTS consent_records (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID REFERENCES users(id),
    consent_type VARCHAR(50), -- 'data_processing', 'marketing', 'analytics'
    granted BOOLEAN DEFAULT false,
    granted_at TIMESTAMPTZ,
    revoked_at TIMESTAMPTZ,
    consent_method VARCHAR(50), -- 'web_form', 'email', 'api'
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_consent_records_user_id ON consent_records(user_id);
CREATE INDEX IF NOT EXISTS idx_consent_records_type ON consent_records(consent_type);

-- Cross-Border Transfer Logs (PDPL)
CREATE TABLE IF NOT EXISTS cross_border_transfers (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    document_id UUID REFERENCES documents(id),
    source_country VARCHAR(2), -- ISO country code
    destination_country VARCHAR(2),
    transfer_reason TEXT,
    legal_basis VARCHAR(100), -- 'consent', 'contract', 'legal_obligation'
    transferred_by UUID REFERENCES users(id),
    transferred_at TIMESTAMPTZ DEFAULT NOW(),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_cross_border_transfers_document_id ON cross_border_transfers(document_id);
CREATE INDEX IF NOT EXISTS idx_cross_border_transfers_destination ON cross_border_transfers(destination_country);

-- Retention Policies
CREATE TABLE IF NOT EXISTS retention_policies (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL,
    description TEXT,
    data_category VARCHAR(100), -- 'pii', 'pci', 'phi', 'financial'
    retention_period_days INT NOT NULL,
    retention_action VARCHAR(50) DEFAULT 'delete', -- 'delete', 'anonymize', 'archive'
    is_active BOOLEAN DEFAULT true,
    created_by UUID REFERENCES users(id),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_retention_policies_category ON retention_policies(data_category);
CREATE INDEX IF NOT EXISTS idx_retention_policies_active ON retention_policies(is_active);

-- DPIA (Data Protection Impact Assessment) Templates
CREATE TABLE IF NOT EXISTS dpia_templates (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL,
    description TEXT,
    template_json JSONB NOT NULL,
    is_active BOOLEAN DEFAULT true,
    created_by UUID REFERENCES users(id),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- DPIA Assessments
CREATE TABLE IF NOT EXISTS dpia_assessments (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    template_id UUID REFERENCES dpia_templates(id),
    project_name VARCHAR(255) NOT NULL,
    data_categories TEXT[],
    risk_level VARCHAR(20), -- 'low', 'medium', 'high'
    mitigation_measures TEXT,
    status VARCHAR(20) DEFAULT 'draft', -- 'draft', 'review', 'approved', 'rejected'
    assessed_by UUID REFERENCES users(id),
    reviewed_by UUID REFERENCES users(id),
    assessed_at TIMESTAMPTZ DEFAULT NOW(),
    reviewed_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_dpia_assessments_status ON dpia_assessments(status);
CREATE INDEX IF NOT EXISTS idx_dpia_assessments_risk_level ON dpia_assessments(risk_level);

-- Add comments
COMMENT ON TABLE compliance_controls IS 'ISO 27001/27701 controls mapping and evidence';
COMMENT ON TABLE dsr_requests IS 'Data Subject Requests (PDPL Article 15-20, GDPR Article 15-20)';
COMMENT ON TABLE consent_records IS 'Consent tracking for PDPL Article 6 compliance';
COMMENT ON TABLE cross_border_transfers IS 'Log of cross-border data transfers (PDPL Article 29)';
COMMENT ON TABLE retention_policies IS 'Data retention policies for compliance';
COMMENT ON TABLE dpia_templates IS 'DPIA templates for risk assessment';
COMMENT ON TABLE dpia_assessments IS 'DPIA assessment records';

