-- Privacy and Data Protection System Tables
-- Implements GDPR compliance, data encryption, and automated data deletion

-- Data Protection Settings Table
CREATE TABLE `data_protection_settings` (
    `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` bigint(20) UNSIGNED NOT NULL,
    `encryption_enabled` tinyint(1) NOT NULL DEFAULT 1,
    `data_retention_days` int(11) NOT NULL DEFAULT 2555, -- 7 years default
    `auto_delete_enabled` tinyint(1) NOT NULL DEFAULT 1,
    `gdpr_consent` tinyint(1) NOT NULL DEFAULT 0,
    `gdpr_consent_date` timestamp NULL DEFAULT NULL,
    `data_processing_consent` tinyint(1) NOT NULL DEFAULT 0,
    `marketing_consent` tinyint(1) NOT NULL DEFAULT 0,
    `analytics_consent` tinyint(1) NOT NULL DEFAULT 0,
    `third_party_sharing_consent` tinyint(1) NOT NULL DEFAULT 0,
    `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `user_id` (`user_id`),
    KEY `idx_encryption_enabled` (`encryption_enabled`),
    KEY `idx_auto_delete_enabled` (`auto_delete_enabled`),
    KEY `idx_gdpr_consent` (`gdpr_consent`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Encrypted Data Storage Table
CREATE TABLE `encrypted_data` (
    `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` bigint(20) UNSIGNED NOT NULL,
    `data_type` varchar(100) NOT NULL, -- 'id_document', 'payment_info', 'personal_data', etc.
    `encrypted_data` longtext NOT NULL,
    `encryption_key_id` varchar(255) NOT NULL,
    `encryption_algorithm` varchar(50) NOT NULL DEFAULT 'AES-256-GCM',
    `data_hash` varchar(255) NOT NULL,
    `expires_at` timestamp NULL DEFAULT NULL,
    `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_user_id` (`user_id`),
    KEY `idx_data_type` (`data_type`),
    KEY `idx_expires_at` (`expires_at`),
    KEY `idx_encryption_key_id` (`encryption_key_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Data Deletion Requests Table (GDPR Right to be Forgotten)
CREATE TABLE `data_deletion_requests` (
    `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` bigint(20) UNSIGNED NOT NULL,
    `request_type` enum('partial', 'complete', 'specific_data') NOT NULL DEFAULT 'complete',
    `data_types` json NULL, -- Specific data types to delete
    `reason` text NULL,
    `status` enum('pending', 'processing', 'completed', 'failed', 'cancelled') NOT NULL DEFAULT 'pending',
    `requested_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    `processed_at` timestamp NULL DEFAULT NULL,
    `completed_at` timestamp NULL DEFAULT NULL,
    `deletion_report` json NULL, -- Report of what was deleted
    `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_user_id` (`user_id`),
    KEY `idx_status` (`status`),
    KEY `idx_requested_at` (`requested_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Automated Data Cleanup Jobs Table
CREATE TABLE `data_cleanup_jobs` (
    `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `job_type` enum('expired_data', 'retention_policy', 'gdpr_deletion', 'verification_documents') NOT NULL,
    `target_data_type` varchar(100) NULL,
    `criteria` json NOT NULL, -- Cleanup criteria
    `status` enum('scheduled', 'running', 'completed', 'failed') NOT NULL DEFAULT 'scheduled',
    `scheduled_at` timestamp NOT NULL,
    `started_at` timestamp NULL DEFAULT NULL,
    `completed_at` timestamp NULL DEFAULT NULL,
    `items_processed` int(11) NOT NULL DEFAULT 0,
    `items_deleted` int(11) NOT NULL DEFAULT 0,
    `error_message` text NULL,
    `cleanup_report` json NULL,
    `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_job_type` (`job_type`),
    KEY `idx_status` (`status`),
    KEY `idx_scheduled_at` (`scheduled_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Privacy Controls Table
CREATE TABLE `privacy_controls` (
    `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` bigint(20) UNSIGNED NOT NULL,
    `profile_visibility` enum('public', 'followers', 'private') NOT NULL DEFAULT 'public',
    `content_visibility` enum('public', 'followers', 'subscribers', 'private') NOT NULL DEFAULT 'public',
    `search_visibility` tinyint(1) NOT NULL DEFAULT 1,
    `allow_direct_messages` enum('everyone', 'followers', 'subscribers', 'none') NOT NULL DEFAULT 'everyone',
    `allow_comments` enum('everyone', 'followers', 'subscribers', 'none') NOT NULL DEFAULT 'everyone',
    `allow_mentions` tinyint(1) NOT NULL DEFAULT 1,
    `allow_tags` tinyint(1) NOT NULL DEFAULT 1,
    `show_online_status` tinyint(1) NOT NULL DEFAULT 1,
    `show_last_seen` tinyint(1) NOT NULL DEFAULT 1,
    `allow_content_download` tinyint(1) NOT NULL DEFAULT 0,
    `watermark_required` tinyint(1) NOT NULL DEFAULT 1,
    `geo_blocking_enabled` tinyint(1) NOT NULL DEFAULT 0,
    `blocked_countries` json NULL,
    `ip_restrictions` json NULL,
    `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `user_id` (`user_id`),
    KEY `idx_profile_visibility` (`profile_visibility`),
    KEY `idx_content_visibility` (`content_visibility`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Audit Trail Table
CREATE TABLE `audit_trail` (
    `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` bigint(20) UNSIGNED NULL,
    `admin_id` bigint(20) UNSIGNED NULL,
    `action` varchar(100) NOT NULL,
    `resource_type` varchar(100) NOT NULL, -- 'user', 'content', 'payment', etc.
    `resource_id` bigint(20) UNSIGNED NULL,
    `old_values` json NULL,
    `new_values` json NULL,
    `ip_address` varchar(45) NULL,
    `user_agent` text NULL,
    `session_id` varchar(255) NULL,
    `severity` enum('low', 'medium', 'high', 'critical') NOT NULL DEFAULT 'low',
    `description` text NULL,
    `metadata` json NULL,
    `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_user_id` (`user_id`),
    KEY `idx_admin_id` (`admin_id`),
    KEY `idx_action` (`action`),
    KEY `idx_resource_type` (`resource_type`),
    KEY `idx_resource_id` (`resource_id`),
    KEY `idx_severity` (`severity`),
    KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Data Access Log Table
CREATE TABLE `data_access_log` (
    `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` bigint(20) UNSIGNED NULL,
    `admin_id` bigint(20) UNSIGNED NULL,
    `accessed_user_id` bigint(20) UNSIGNED NOT NULL, -- User whose data was accessed
    `data_type` varchar(100) NOT NULL,
    `access_reason` varchar(255) NOT NULL,
    `access_method` enum('api', 'admin_panel', 'automated', 'export') NOT NULL,
    `ip_address` varchar(45) NULL,
    `user_agent` text NULL,
    `data_fields_accessed` json NULL,
    `success` tinyint(1) NOT NULL DEFAULT 1,
    `error_message` text NULL,
    `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_user_id` (`user_id`),
    KEY `idx_admin_id` (`admin_id`),
    KEY `idx_accessed_user_id` (`accessed_user_id`),
    KEY `idx_data_type` (`data_type`),
    KEY `idx_access_method` (`access_method`),
    KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Data Export Requests Table (GDPR Right to Data Portability)
CREATE TABLE `data_export_requests` (
    `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` bigint(20) UNSIGNED NOT NULL,
    `export_format` enum('json', 'csv', 'xml') NOT NULL DEFAULT 'json',
    `data_types` json NOT NULL, -- Types of data to export
    `status` enum('pending', 'processing', 'completed', 'failed', 'expired') NOT NULL DEFAULT 'pending',
    `file_path` varchar(500) NULL,
    `file_size` bigint(20) NULL,
    `download_count` int(11) NOT NULL DEFAULT 0,
    `expires_at` timestamp NOT NULL,
    `requested_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    `processed_at` timestamp NULL DEFAULT NULL,
    `completed_at` timestamp NULL DEFAULT NULL,
    `error_message` text NULL,
    `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_user_id` (`user_id`),
    KEY `idx_status` (`status`),
    KEY `idx_expires_at` (`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Data Breach Incidents Table
CREATE TABLE `data_breach_incidents` (
    `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `incident_type` enum('unauthorized_access', 'data_leak', 'system_breach', 'insider_threat', 'other') NOT NULL,
    `severity` enum('low', 'medium', 'high', 'critical') NOT NULL,
    `affected_users_count` int(11) NOT NULL DEFAULT 0,
    `affected_data_types` json NOT NULL,
    `description` text NOT NULL,
    `detection_method` varchar(255) NULL,
    `detected_at` timestamp NOT NULL,
    `reported_at` timestamp NULL DEFAULT NULL,
    `resolved_at` timestamp NULL DEFAULT NULL,
    `status` enum('detected', 'investigating', 'contained', 'resolved', 'reported_to_authorities') NOT NULL DEFAULT 'detected',
    `mitigation_actions` json NULL,
    `regulatory_notifications` json NULL, -- Track GDPR notifications
    `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_incident_type` (`incident_type`),
    KEY `idx_severity` (`severity`),
    KEY `idx_detected_at` (`detected_at`),
    KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Enterprise Standard: NO FOREIGN KEYS - Pure SQL Implementation
-- Foreign key constraints removed for maximum performance and flexibility
-- Data integrity maintained through application logic

-- Insert default privacy settings for existing users
INSERT INTO `data_protection_settings` (`user_id`, `encryption_enabled`, `gdpr_consent`, `gdpr_consent_date`)
SELECT `id`, 1, 1, NOW() FROM `users` WHERE NOT EXISTS (
    SELECT 1 FROM `data_protection_settings` WHERE `data_protection_settings`.`user_id` = `users`.`id`
);

INSERT INTO `privacy_controls` (`user_id`)
SELECT `id` FROM `users` WHERE NOT EXISTS (
    SELECT 1 FROM `privacy_controls` WHERE `privacy_controls`.`user_id` = `users`.`id`
);

-- Security Alerts Table
CREATE TABLE `security_alerts` (
    `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `alert_type` varchar(100) NOT NULL,
    `severity` enum('low', 'medium', 'high', 'critical') NOT NULL,
    `user_id` bigint(20) UNSIGNED NULL,
    `ip_address` varchar(45) NULL,
    `description` text NOT NULL,
    `metadata` json NULL,
    `status` enum('open', 'investigating', 'resolved', 'false_positive') NOT NULL DEFAULT 'open',
    `resolved_by` bigint(20) UNSIGNED NULL,
    `resolution_notes` text NULL,
    `resolved_at` timestamp NULL DEFAULT NULL,
    `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_alert_type` (`alert_type`),
    KEY `idx_severity` (`severity`),
    KEY `idx_user_id` (`user_id`),
    KEY `idx_status` (`status`),
    KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- IP Blacklist Table
CREATE TABLE `ip_blacklist` (
    `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `ip_address` varchar(45) NOT NULL,
    `reason` varchar(255) NOT NULL,
    `blocked_by` bigint(20) UNSIGNED NULL,
    `blocked_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `expires_at` timestamp NULL DEFAULT NULL,
    `is_permanent` tinyint(1) NOT NULL DEFAULT 0,
    `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `ip_address` (`ip_address`),
    KEY `idx_blocked_at` (`blocked_at`),
    KEY `idx_expires_at` (`expires_at`),
    KEY `idx_is_permanent` (`is_permanent`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Data Breach Response Log Table
CREATE TABLE `data_breach_response_log` (
    `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `breach_incident_id` bigint(20) UNSIGNED NOT NULL,
    `response_action` varchar(255) NOT NULL,
    `action_taken_by` bigint(20) UNSIGNED NOT NULL,
    `action_description` text NOT NULL,
    `action_result` text NULL,
    `regulatory_notification` tinyint(1) NOT NULL DEFAULT 0,
    `notification_details` json NULL,
    `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_breach_incident_id` (`breach_incident_id`),
    KEY `idx_action_taken_by` (`action_taken_by`),
    KEY `idx_regulatory_notification` (`regulatory_notification`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Privacy Impact Assessments Table
CREATE TABLE `privacy_impact_assessments` (
    `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `assessment_name` varchar(255) NOT NULL,
    `data_processing_activity` text NOT NULL,
    `data_types_processed` json NOT NULL,
    `legal_basis` varchar(255) NOT NULL,
    `risk_level` enum('low', 'medium', 'high', 'very_high') NOT NULL,
    `risk_assessment` json NOT NULL,
    `mitigation_measures` json NOT NULL,
    `assessment_status` enum('draft', 'under_review', 'approved', 'requires_update') NOT NULL DEFAULT 'draft',
    `assessed_by` bigint(20) UNSIGNED NOT NULL,
    `approved_by` bigint(20) UNSIGNED NULL,
    `assessment_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `approval_date` timestamp NULL DEFAULT NULL,
    `next_review_date` timestamp NULL DEFAULT NULL,
    `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_risk_level` (`risk_level`),
    KEY `idx_assessment_status` (`assessment_status`),
    KEY `idx_assessed_by` (`assessed_by`),
    KEY `idx_next_review_date` (`next_review_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Enterprise Standard: NO FOREIGN KEYS - Pure SQL Implementation
-- Foreign key constraints removed for maximum performance and flexibility
-- Data integrity maintained through application logic

-- Add account locking columns to users table (if not already present)
ALTER TABLE `users` 
ADD COLUMN `account_locked` tinyint(1) NOT NULL DEFAULT 0,
ADD COLUMN `locked_until` timestamp NULL DEFAULT NULL,
ADD COLUMN `lock_reason` varchar(255) NULL DEFAULT NULL;

-- Create indexes for performance
CREATE INDEX `idx_users_account_locked` ON `users` (`account_locked`);
CREATE INDEX `idx_users_locked_until` ON `users` (`locked_until`);