ALTER TABLE audit_events
    ADD COLUMN IF NOT EXISTS previous_hash text NOT NULL DEFAULT '',
    ADD COLUMN IF NOT EXISTS event_hash text NOT NULL DEFAULT '',
    ADD COLUMN IF NOT EXISTS hash_algorithm text NOT NULL DEFAULT 'sha256';

CREATE INDEX IF NOT EXISTS audit_events_hash_idx
    ON audit_events (event_hash);

CREATE TABLE privacy_data_inventory (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    system_name text NOT NULL DEFAULT 'banking_api',
    table_name text NOT NULL,
    field_name text NOT NULL DEFAULT '*',
    data_category text NOT NULL CHECK (data_category IN ('pii', 'financial', 'card', 'crypto', 'secret', 'audit', 'operational')),
    classification text NOT NULL CHECK (classification IN ('public', 'internal', 'confidential', 'restricted')),
    lawful_basis text NOT NULL DEFAULT '',
    retention_policy text NOT NULL DEFAULT '',
    residency_scope text NOT NULL DEFAULT 'default',
    encrypted_at_rest boolean NOT NULL DEFAULT false,
    encrypted_in_transit boolean NOT NULL DEFAULT true,
    owner text NOT NULL DEFAULT 'engineering',
    notes text NOT NULL DEFAULT '',
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE (system_name, table_name, field_name)
);

CREATE TRIGGER privacy_data_inventory_set_updated_at
BEFORE UPDATE ON privacy_data_inventory
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE INDEX privacy_data_inventory_category_idx
    ON privacy_data_inventory (data_category, classification);

CREATE TABLE data_retention_policies (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    policy_key text NOT NULL UNIQUE,
    data_category text NOT NULL CHECK (data_category IN ('pii', 'financial', 'card', 'crypto', 'secret', 'audit', 'operational')),
    retention_period_days integer NOT NULL CHECK (retention_period_days >= 0),
    anonymization_strategy text NOT NULL DEFAULT '',
    deletion_strategy text NOT NULL DEFAULT '',
    legal_hold_allowed boolean NOT NULL DEFAULT true,
    active boolean NOT NULL DEFAULT true,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TRIGGER data_retention_policies_set_updated_at
BEFORE UPDATE ON data_retention_policies
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TABLE data_subject_requests (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id uuid REFERENCES users(id) ON DELETE SET NULL,
    requester_email text NOT NULL,
    request_type text NOT NULL CHECK (request_type IN ('access', 'rectification', 'erasure', 'restriction', 'portability', 'objection')),
    status text NOT NULL DEFAULT 'received' CHECK (status IN ('received', 'verifying_identity', 'in_progress', 'completed', 'rejected', 'canceled')),
    verification_status text NOT NULL DEFAULT 'pending' CHECK (verification_status IN ('pending', 'verified', 'failed')),
    details text NOT NULL DEFAULT '',
    response_payload jsonb NOT NULL DEFAULT '{}'::jsonb,
    assigned_to_admin_user_id uuid REFERENCES users(id) ON DELETE SET NULL,
    due_at timestamptz NOT NULL,
    completed_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TRIGGER data_subject_requests_set_updated_at
BEFORE UPDATE ON data_subject_requests
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE INDEX data_subject_requests_status_due_idx
    ON data_subject_requests (status, due_at);

CREATE INDEX data_subject_requests_user_created_idx
    ON data_subject_requests (user_id, created_at DESC);

CREATE TABLE privacy_notices (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    notice_type text NOT NULL CHECK (notice_type IN ('privacy', 'cookie')),
    version text NOT NULL,
    status text NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'published', 'retired')),
    content text NOT NULL,
    effective_at timestamptz,
    published_by_admin_user_id uuid REFERENCES users(id) ON DELETE SET NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE (notice_type, version)
);

CREATE TRIGGER privacy_notices_set_updated_at
BEFORE UPDATE ON privacy_notices
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE INDEX privacy_notices_type_status_idx
    ON privacy_notices (notice_type, status, effective_at DESC);

CREATE TABLE data_protection_attestations (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    control_type text NOT NULL CHECK (control_type IN ('encryption_at_rest', 'tls_in_transit', 'backup_encryption', 'restore_access', 'audit_retention', 'audit_tamper_evidence')),
    status text NOT NULL CHECK (status IN ('pass', 'fail', 'unknown')),
    evidence text NOT NULL DEFAULT '',
    checked_by_admin_user_id uuid REFERENCES users(id) ON DELETE SET NULL,
    checked_at timestamptz NOT NULL DEFAULT now(),
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX data_protection_attestations_control_checked_idx
    ON data_protection_attestations (control_type, checked_at DESC);

CREATE TABLE data_residency_policies (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    region_code text NOT NULL UNIQUE,
    status text NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'active', 'retired')),
    allowed_storage_regions jsonb NOT NULL DEFAULT '[]'::jsonb,
    restricted_data_categories jsonb NOT NULL DEFAULT '[]'::jsonb,
    notes text NOT NULL DEFAULT '',
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TRIGGER data_residency_policies_set_updated_at
BEFORE UPDATE ON data_residency_policies
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TABLE privacy_deletion_jobs (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    data_subject_request_id uuid NOT NULL REFERENCES data_subject_requests(id) ON DELETE CASCADE,
    user_id uuid REFERENCES users(id) ON DELETE SET NULL,
    job_type text NOT NULL CHECK (job_type IN ('dry_run', 'anonymize')),
    status text NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'completed', 'failed')),
    affected_tables jsonb NOT NULL DEFAULT '[]'::jsonb,
    error text NOT NULL DEFAULT '',
    processed_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX privacy_deletion_jobs_request_idx
    ON privacy_deletion_jobs (data_subject_request_id, created_at DESC);

INSERT INTO data_retention_policies (
    policy_key, data_category, retention_period_days, anonymization_strategy, deletion_strategy, legal_hold_allowed
) VALUES
    ('customer_pii_default', 'pii', 2555, 'anonymize direct identifiers after approved erasure or retention expiry', 'delete non-essential metadata after retention expiry', true),
    ('financial_records_default', 'financial', 3650, 'retain transaction facts and remove direct identifiers when allowed', 'do not delete ledger records without legal approval', true),
    ('card_metadata_default', 'card', 2555, 'retain tokenized/safe card metadata only', 'delete non-essential card metadata after retention expiry', true),
    ('crypto_records_default', 'crypto', 3650, 'retain wallet/address audit facts and remove direct identifiers when allowed', 'do not delete custody/audit records without legal approval', true),
    ('secret_material_default', 'secret', 90, 'rotate secrets and keep only hash/fingerprint evidence', 'delete expired secret material immediately after rotation window', false),
    ('audit_logs_default', 'audit', 2555, 'retain tamper-evident audit chain', 'archive after retention expiry subject to legal hold', true)
ON CONFLICT (policy_key) DO NOTHING;

INSERT INTO privacy_data_inventory (
    table_name, field_name, data_category, classification, lawful_basis, retention_policy, residency_scope, encrypted_at_rest, owner, notes
) VALUES
    ('users', 'email', 'pii', 'restricted', 'contract/compliance', 'customer_pii_default', 'customer_region', false, 'privacy', 'Customer login identifier'),
    ('users', 'full_name', 'pii', 'restricted', 'contract/compliance', 'customer_pii_default', 'customer_region', false, 'privacy', 'Customer display/legal name'),
    ('kyc_profiles', '*', 'pii', 'restricted', 'legal_obligation', 'customer_pii_default', 'customer_region', false, 'compliance', 'KYC identity profile'),
    ('kyc_documents', '*', 'pii', 'restricted', 'legal_obligation', 'customer_pii_default', 'customer_region', false, 'compliance', 'KYC document metadata'),
    ('accounts', '*', 'financial', 'restricted', 'contract/legal_obligation', 'financial_records_default', 'customer_region', false, 'finance', 'Account and IBAN records'),
    ('ledger_journal_entries', '*', 'financial', 'restricted', 'legal_obligation', 'financial_records_default', 'default', false, 'finance', 'Immutable ledger journal records'),
    ('ledger_journal_lines', '*', 'financial', 'restricted', 'legal_obligation', 'financial_records_default', 'default', false, 'finance', 'Immutable ledger lines'),
    ('virtual_cards', '*', 'card', 'restricted', 'contract/legal_obligation', 'card_metadata_default', 'customer_region', false, 'cards', 'Safe card metadata only; PAN/CVV excluded after creation response'),
    ('crypto_wallets', '*', 'crypto', 'restricted', 'contract/legal_obligation', 'crypto_records_default', 'customer_region', false, 'crypto', 'Crypto wallet metadata'),
    ('crypto_addresses', '*', 'crypto', 'restricted', 'contract/legal_obligation', 'crypto_records_default', 'customer_region', false, 'crypto', 'Deposit address metadata'),
    ('auth_sessions', '*', 'secret', 'restricted', 'security', 'secret_material_default', 'default', true, 'security', 'Refresh token hashes only'),
    ('audit_events', '*', 'audit', 'confidential', 'legal_obligation/security', 'audit_logs_default', 'default', false, 'security', 'Tamper-evident audit chain')
ON CONFLICT (system_name, table_name, field_name) DO NOTHING;

INSERT INTO privacy_notices (notice_type, version, status, content, effective_at)
VALUES
    ('privacy', 'draft-2026-06', 'draft', 'Draft privacy notice template. Replace with legal-approved content before launch.', NULL),
    ('cookie', 'draft-2026-06', 'draft', 'Draft cookie notice template. The backend does not set tracking cookies by default.', NULL)
ON CONFLICT (notice_type, version) DO NOTHING;

INSERT INTO data_residency_policies (region_code, status, allowed_storage_regions, restricted_data_categories, notes)
VALUES
    ('EU', 'draft', '["EU"]'::jsonb, '["pii","financial","card","crypto"]'::jsonb, 'Draft EU residency policy; requires hosting/provider validation before production.'),
    ('US', 'draft', '["US"]'::jsonb, '["pii","financial"]'::jsonb, 'Draft US residency policy; requires legal and provider validation before production.')
ON CONFLICT (region_code) DO NOTHING;