-- Watermark GDPR Compliance System Tables
-- Implements GDPR compliance features for watermark operations

-- Watermark Data Protection Settings Table
CREATE TABLE `watermark_data_protection` (
    `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` bigint(20) UNSIGNED NOT NULL,
    `data_processing_consent` tinyint(1) NOT NULL DEFAULT 1,
    `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,
    `retention_period_days` int(11) NOT NULL DEFAULT 2555, -- 7 years default
    `auto_delete_enabled` tinyint(1) NOT NULL DEFAULT 1,
    `encryption_enabled` tinyint(1) NOT NULL DEFAULT 1,
    `consent_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    `consent_ip_address` varchar(45) NULL,
    `consent_user_agent` text 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_data_processing_consent` (`data_processing_consent`),
    KEY `idx_auto_delete_enabled` (`auto_delete_enabled`),
    KEY `idx_consent_date` (`consent_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- GDPR Data Export Requests Table
CREATE TABLE `watermark_gdpr_exports` (
    `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` bigint(20) UNSIGNED NOT NULL,
    `export_id` varchar(36) NOT NULL,
    `data_types` json NOT NULL,
    `export_format` enum('json', 'csv', 'xml') NOT NULL DEFAULT 'json',
    `file_path` varchar(500) NULL,
    `file_size` bigint(20) NULL,
    `status` enum('pending', 'processing', 'completed', 'failed', 'expired') NOT NULL DEFAULT 'pending',
    `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`),
    UNIQUE KEY `export_id` (`export_id`),
    KEY `idx_user_id` (`user_id`),
    KEY `idx_status` (`status`),
    KEY `idx_expires_at` (`expires_at`),
    KEY `idx_requested_at` (`requested_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- GDPR Data Deletion Requests Table
CREATE TABLE `watermark_gdpr_deletions` (
    `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` bigint(20) UNSIGNED NOT NULL,
    `deletion_id` varchar(36) NOT NULL,
    `request_type` enum('partial', 'complete', 'specific_data') NOT NULL DEFAULT 'complete',
    `data_types` json NOT NULL,
    `reason` varchar(255) NOT NULL DEFAULT 'user_request',
    `status` enum('pending', 'processing', 'completed', 'failed', 'cancelled') NOT NULL DEFAULT 'pending',
    `deletion_report` json 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`),
    UNIQUE KEY `deletion_id` (`deletion_id`),
    KEY `idx_user_id` (`user_id`),
    KEY `idx_status` (`status`),
    KEY `idx_request_type` (`request_type`),
    KEY `idx_requested_at` (`requested_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Scheduled Data Deletions Table
CREATE TABLE `watermark_scheduled_deletions` (
    `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` bigint(20) UNSIGNED NOT NULL,
    `scheduled_for` timestamp NOT NULL,
    `retention_days` int(11) NOT NULL,
    `status` enum('scheduled', 'processing', 'completed', 'failed', 'cancelled') NOT NULL DEFAULT 'scheduled',
    `deletion_criteria` json NULL,
    `last_run_at` timestamp NULL DEFAULT NULL,
    `next_run_at` timestamp NULL DEFAULT NULL,
    `runs_count` int(11) NOT NULL DEFAULT 0,
    `items_deleted` int(11) NOT NULL DEFAULT 0,
    `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`),
    UNIQUE KEY `user_id` (`user_id`),
    KEY `idx_scheduled_for` (`scheduled_for`),
    KEY `idx_status` (`status`),
    KEY `idx_next_run_at` (`next_run_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Data Processing Activities Log Table
CREATE TABLE `watermark_data_processing_log` (
    `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` bigint(20) UNSIGNED NOT NULL,
    `processing_activity` varchar(255) NOT NULL,
    `data_types_processed` json NOT NULL,
    `legal_basis` varchar(255) NOT NULL,
    `purpose` text NOT NULL,
    `retention_period` varchar(255) NULL,
    `third_party_recipients` json NULL,
    `data_subject_rights` json NULL,
    `security_measures` json NULL,
    `processing_start` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `processing_end` timestamp NULL DEFAULT NULL,
    `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_user_id` (`user_id`),
    KEY `idx_processing_activity` (`processing_activity`),
    KEY `idx_processing_start` (`processing_start`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Consent Management Table
CREATE TABLE `watermark_consent_records` (
    `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` bigint(20) UNSIGNED NOT NULL,
    `consent_type` varchar(100) NOT NULL,
    `consent_given` tinyint(1) NOT NULL,
    `consent_method` enum('explicit', 'implicit', 'opt_in', 'opt_out') NOT NULL,
    `consent_text` text NULL,
    `consent_version` varchar(50) NULL,
    `ip_address` varchar(45) NULL,
    `user_agent` text NULL,
    `consent_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `withdrawal_timestamp` timestamp NULL DEFAULT NULL,
    `withdrawal_reason` text NULL,
    `is_active` tinyint(1) NOT NULL DEFAULT 1,
    `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_consent_type` (`consent_type`),
    KEY `idx_consent_given` (`consent_given`),
    KEY `idx_is_active` (`is_active`),
    KEY `idx_consent_timestamp` (`consent_timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Data Breach Notifications Table
CREATE TABLE `watermark_data_breach_notifications` (
    `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `breach_id` varchar(36) NOT NULL,
    `notification_type` enum('supervisory_authority', 'data_subject', 'internal') NOT NULL,
    `recipient` varchar(255) NOT NULL,
    `notification_method` enum('email', 'postal', 'phone', 'website', 'api') NOT NULL,
    `notification_content` text NOT NULL,
    `sent_at` timestamp NULL DEFAULT NULL,
    `delivery_status` enum('pending', 'sent', 'delivered', 'failed', 'bounced') NOT NULL DEFAULT 'pending',
    `delivery_confirmation` text NULL,
    `response_received` tinyint(1) NOT NULL DEFAULT 0,
    `response_content` 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_breach_id` (`breach_id`),
    KEY `idx_notification_type` (`notification_type`),
    KEY `idx_delivery_status` (`delivery_status`),
    KEY `idx_sent_at` (`sent_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Privacy Impact Assessments Table
CREATE TABLE `watermark_privacy_impact_assessments` (
    `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `assessment_id` varchar(36) NOT NULL,
    `assessment_name` varchar(255) NOT NULL,
    `data_processing_description` text NOT NULL,
    `data_types` json NOT NULL,
    `data_subjects` json NOT NULL,
    `processing_purposes` json NOT NULL,
    `legal_basis` varchar(255) NOT NULL,
    `necessity_assessment` text NOT NULL,
    `proportionality_assessment` text NOT NULL,
    `risk_identification` json NOT NULL,
    `risk_mitigation` json NOT NULL,
    `residual_risks` json NULL,
    `consultation_required` tinyint(1) NOT NULL DEFAULT 0,
    `consultation_details` text NULL,
    `assessment_status` enum('draft', 'under_review', 'approved', 'requires_consultation') NOT NULL DEFAULT 'draft',
    `assessed_by` bigint(20) UNSIGNED NOT NULL,
    `reviewed_by` bigint(20) UNSIGNED NULL,
    `approved_by` bigint(20) UNSIGNED NULL,
    `assessment_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `review_date` timestamp NULL DEFAULT NULL,
    `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`),
    UNIQUE KEY `assessment_id` (`assessment_id`),
    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;

-- Data Subject Rights Requests Table
CREATE TABLE `watermark_data_subject_requests` (
    `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `request_id` varchar(36) NOT NULL,
    `user_id` bigint(20) UNSIGNED NOT NULL,
    `request_type` enum('access', 'rectification', 'erasure', 'portability', 'restriction', 'objection') NOT NULL,
    `request_description` text NOT NULL,
    `identity_verified` tinyint(1) NOT NULL DEFAULT 0,
    `verification_method` varchar(255) NULL,
    `verification_date` timestamp NULL DEFAULT NULL,
    `status` enum('received', 'verifying_identity', 'processing', 'completed', 'rejected', 'extended') NOT NULL DEFAULT 'received',
    `response_due_date` timestamp NOT NULL,
    `extension_reason` text NULL,
    `response_content` text NULL,
    `response_method` enum('email', 'postal', 'secure_download', 'in_person') NULL,
    `completed_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`),
    UNIQUE KEY `request_id` (`request_id`),
    KEY `idx_user_id` (`user_id`),
    KEY `idx_request_type` (`request_type`),
    KEY `idx_status` (`status`),
    KEY `idx_response_due_date` (`response_due_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default data protection settings for existing users
INSERT INTO `watermark_data_protection` (`user_id`, `data_processing_consent`, `consent_date`)
SELECT `id`, 1, NOW() FROM `users` WHERE NOT EXISTS (
    SELECT 1 FROM `watermark_data_protection` WHERE `watermark_data_protection`.`user_id` = `users`.`id`
);

-- Insert default consent records for existing users
INSERT INTO `watermark_consent_records` (`user_id`, `consent_type`, `consent_given`, `consent_method`, `consent_text`)
SELECT `id`, 'data_processing', 1, 'implicit', 'Consent given during account creation' FROM `users` WHERE NOT EXISTS (
    SELECT 1 FROM `watermark_consent_records` WHERE `watermark_consent_records`.`user_id` = `users`.`id` AND `consent_type` = 'data_processing'
);

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

-- Create indexes for performance optimization
CREATE INDEX `idx_gdpr_exports_user_status` ON `watermark_gdpr_exports` (`user_id`, `status`);
CREATE INDEX `idx_gdpr_deletions_user_status` ON `watermark_gdpr_deletions` (`user_id`, `status`);
CREATE INDEX `idx_scheduled_deletions_status_next_run` ON `watermark_scheduled_deletions` (`status`, `next_run_at`);
CREATE INDEX `idx_processing_log_user_activity` ON `watermark_data_processing_log` (`user_id`, `processing_activity`);
CREATE INDEX `idx_consent_records_user_type_active` ON `watermark_consent_records` (`user_id`, `consent_type`, `is_active`);
CREATE INDEX `idx_breach_notifications_breach_type` ON `watermark_data_breach_notifications` (`breach_id`, `notification_type`);
CREATE INDEX `idx_pia_status_review_date` ON `watermark_privacy_impact_assessments` (`assessment_status`, `next_review_date`);
CREATE INDEX `idx_subject_requests_user_type_status` ON `watermark_data_subject_requests` (`user_id`, `request_type`, `status`);