-- GDPR Compliance Tables

-- Data Subject Consent Management
CREATE TABLE data_subject_consents
(
    id                  UUID PRIMARY KEY     DEFAULT uuid_generate_v4(),
    user_id             UUID         NOT NULL REFERENCES users (id) ON DELETE CASCADE,
    consent_type        VARCHAR(100) NOT NULL, -- PRIVACY_POLICY, COOKIES, MARKETING, DATA_PROCESSING, THIRD_PARTY_SHARING
    consent_status      VARCHAR(50)  NOT NULL DEFAULT 'PENDING' CHECK (consent_status IN ('GRANTED', 'DENIED', 'WITHDRAWN', 'PENDING')),
    consent_version     VARCHAR(50),          -- Version of privacy policy/terms when consent was given
    consent_method      VARCHAR(50),          -- WEB_FORM, EMAIL, IN_PERSON, API
    ip_address          VARCHAR(45),
    user_agent          TEXT,
    granted_at          TIMESTAMP,
    withdrawn_at        TIMESTAMP,
    expires_at          TIMESTAMP,            -- Optional expiration date
    created_at          TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    -- Ensure one consent record per user per consent type
    UNIQUE(user_id, consent_type)
);

-- Data Subject Requests (GDPR Article 15-22)
CREATE TABLE data_subject_requests
(
    id                  UUID PRIMARY KEY     DEFAULT uuid_generate_v4(),
    user_id             UUID         NOT NULL REFERENCES users (id) ON DELETE CASCADE,
    request_type        VARCHAR(100) NOT NULL CHECK (request_type IN ('ACCESS', 'RECTIFICATION', 'ERASURE', 'RESTRICTION', 'PORTABILITY', 'OBJECTION')),
    request_status      VARCHAR(50)  NOT NULL DEFAULT 'PENDING' CHECK (request_status IN ('PENDING', 'IN_PROGRESS', 'COMPLETED', 'REJECTED', 'CANCELLED')),
    description         TEXT,                 -- User's description of the request
    requested_at        TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    completed_at        TIMESTAMP,
    rejected_at         TIMESTAMP,
    rejection_reason    TEXT,                 -- Reason for rejection if applicable
    response_data       JSONB,                -- Response data (for portability requests)
    verification_token  VARCHAR(255),         -- Token for verifying identity
    verified_at         TIMESTAMP,
    processed_by        UUID                  REFERENCES users (id), -- Admin who processed the request
    notes               TEXT,                 -- Internal notes
    created_at          TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Data Processing Records (GDPR Article 30 - Record of Processing Activities)
CREATE TABLE data_processing_records
(
    id                  UUID PRIMARY KEY     DEFAULT uuid_generate_v4(),
    processing_purpose  VARCHAR(255) NOT NULL, -- Purpose of processing
    data_categories     TEXT[],               -- Categories of personal data processed
    data_subjects       TEXT[],               -- Categories of data subjects (PATIENTS, DOCTORS, ADMINS)
    recipients          TEXT[],               -- Categories of recipients
    transfers_to_third_countries TEXT[],     -- Third countries data is transferred to
    retention_period    VARCHAR(100),        -- How long data is retained
    security_measures    TEXT[],              -- Security measures in place
    data_controller     VARCHAR(255),        -- Name of data controller
    data_processor      VARCHAR(255),        -- Name of data processor
    legal_basis         VARCHAR(255),        -- Legal basis for processing
    created_at          TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Data Retention Policies
CREATE TABLE data_retention_policies
(
    id                  UUID PRIMARY KEY     DEFAULT uuid_generate_v4(),
    data_type           VARCHAR(100) NOT NULL, -- USER_DATA, MEDICAL_RECORD, APPOINTMENT, AUDIT_LOG, etc.
    retention_period_days INTEGER      NOT NULL, -- Number of days to retain data
    auto_delete_enabled BOOLEAN        DEFAULT FALSE,
    legal_requirement   TEXT,                  -- Legal basis for retention period
    last_cleanup_at     TIMESTAMP,
    created_at          TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    UNIQUE(data_type)
);

-- HIPAA Accounting of Disclosures
CREATE TABLE accounting_of_disclosures
(
    id                  UUID PRIMARY KEY     DEFAULT uuid_generate_v4(),
    patient_id          UUID         NOT NULL REFERENCES patients (id) ON DELETE CASCADE,
    user_id             UUID         NOT NULL REFERENCES users (id), -- Who accessed/disclosed
    disclosure_date     TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    disclosure_type     VARCHAR(100) NOT NULL, -- ROUTINE, NON_ROUTINE, AUTHORIZED, REQUIRED_BY_LAW
    purpose             TEXT         NOT NULL, -- Purpose of disclosure
    recipient_name      VARCHAR(255),         -- Name of recipient
    recipient_address   TEXT,                 -- Address of recipient
    information_disclosed TEXT,               -- What information was disclosed
    authorized_by       UUID                  REFERENCES users (id), -- Who authorized the disclosure
    authorization_date  TIMESTAMP,
    created_at          TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Data Minimization Tracking
CREATE TABLE data_minimization_logs
(
    id                  UUID PRIMARY KEY     DEFAULT uuid_generate_v4(),
    user_id             UUID         NOT NULL REFERENCES users (id),
    action_type         VARCHAR(100) NOT NULL, -- COLLECTED, ACCESSED, MODIFIED, DELETED
    data_category       VARCHAR(100) NOT NULL, -- Category of data
    purpose             TEXT,                  -- Purpose for which data was collected/used
    legal_basis         VARCHAR(255),         -- Legal basis for processing
    timestamp           TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ip_address          VARCHAR(45),
    user_agent          TEXT
);

-- Create indexes for better query performance
CREATE INDEX idx_data_subject_consents_user_id ON data_subject_consents (user_id);
CREATE INDEX idx_data_subject_consents_consent_type ON data_subject_consents (consent_type);
CREATE INDEX idx_data_subject_consents_status ON data_subject_consents (consent_status);
CREATE INDEX idx_data_subject_consents_user_type ON data_subject_consents (user_id, consent_type);

CREATE INDEX idx_data_subject_requests_user_id ON data_subject_requests (user_id);
CREATE INDEX idx_data_subject_requests_type ON data_subject_requests (request_type);
CREATE INDEX idx_data_subject_requests_status ON data_subject_requests (request_status);
CREATE INDEX idx_data_subject_requests_requested_at ON data_subject_requests (requested_at DESC);
CREATE INDEX idx_data_subject_requests_verification_token ON data_subject_requests (verification_token);

CREATE INDEX idx_data_processing_records_purpose ON data_processing_records (processing_purpose);

CREATE INDEX idx_data_retention_policies_data_type ON data_retention_policies (data_type);

CREATE INDEX idx_accounting_of_disclosures_patient_id ON accounting_of_disclosures (patient_id);
CREATE INDEX idx_accounting_of_disclosures_user_id ON accounting_of_disclosures (user_id);
CREATE INDEX idx_accounting_of_disclosures_disclosure_date ON accounting_of_disclosures (disclosure_date DESC);
CREATE INDEX idx_accounting_of_disclosures_type ON accounting_of_disclosures (disclosure_type);

CREATE INDEX idx_data_minimization_logs_user_id ON data_minimization_logs (user_id);
CREATE INDEX idx_data_minimization_logs_timestamp ON data_minimization_logs (timestamp DESC);
CREATE INDEX idx_data_minimization_logs_data_category ON data_minimization_logs (data_category);

-- Insert default data retention policies
INSERT INTO data_retention_policies (data_type, retention_period_days, auto_delete_enabled, legal_requirement)
VALUES
    ('USER_DATA', 3650, FALSE, 'HIPAA requires retention of patient records for 6 years from last service date'),
    ('MEDICAL_RECORD', 2190, FALSE, 'HIPAA requires retention of medical records for 6 years'),
    ('APPOINTMENT', 2555, FALSE, 'HIPAA requires retention of appointment records for 7 years'),
    ('AUDIT_LOG', 2555, TRUE, 'HIPAA requires retention of audit logs for 7 years'),
    ('PHI_ACCESS_LOG', 2555, TRUE, 'HIPAA requires retention of PHI access logs for 7 years'),
    ('HIPAA_AUDIT_LOG', 2555, TRUE, 'HIPAA requires retention of HIPAA audit logs for 7 years'),
    ('BREACH_NOTIFICATION', 3650, FALSE, 'HIPAA requires retention of breach notifications for 10 years'),
    ('MESSAGE', 365, TRUE, 'Standard retention period for messages'),
    ('PRESCRIPTION', 2190, FALSE, 'HIPAA requires retention of prescription records for 6 years')
ON CONFLICT (data_type) DO NOTHING;

-- Insert default data processing records
INSERT INTO data_processing_records (
    processing_purpose,
    data_categories,
    data_subjects,
    recipients,
    security_measures,
    data_controller,
    legal_basis
)
VALUES
    (
        'Telemedicine Platform Services',
        ARRAY['Personal Identifiers', 'Health Information', 'Contact Information', 'Payment Information'],
        ARRAY['PATIENTS', 'DOCTORS', 'ADMINS'],
        ARRAY['Healthcare Providers', 'System Administrators', 'Authorized Third-Party Services'],
        ARRAY['Encryption at Rest', 'Encryption in Transit', 'Access Controls', 'Audit Logging', 'Two-Factor Authentication'],
        'GNX Soft LTD',
        'Consent, Contract, Legal Obligation, Vital Interests'
    )
ON CONFLICT DO NOTHING;

