-- Content Moderation System Tables for Hybrid Vue/Inertia Application
-- Enhanced content moderation with AI analysis and human review workflow

-- Enhanced content_moderations table
CREATE TABLE IF NOT EXISTS `content_moderations` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `content_id` bigint(20) UNSIGNED NOT NULL,
  `content_type` varchar(50) NOT NULL COMMENT 'vault_file, media, etc.',
  `user_id` bigint(20) UNSIGNED DEFAULT NULL,
  `ai_analysis_results` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`ai_analysis_results`)),
  `combined_confidence` decimal(5,2) DEFAULT 0.00,
  `routing_decision` enum('auto_action','human_review','auto_approve') DEFAULT NULL,
  `processing_time_ms` int(11) DEFAULT 0,
  `status` enum('analyzed','pending_review','auto_approved','auto_rejected','approved','rejected','escalated') DEFAULT 'analyzed',
  `moderator_decision` enum('approved','rejected','escalated') DEFAULT NULL,
  `moderator_notes` text DEFAULT NULL,
  `assigned_to` bigint(20) UNSIGNED DEFAULT NULL,
  `assigned_at` timestamp NULL DEFAULT NULL,
  `processed_by` bigint(20) UNSIGNED DEFAULT NULL,
  `processed_at` timestamp NULL DEFAULT NULL,
  `analyzed_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_content` (`content_id`,`content_type`),
  KEY `idx_status` (`status`),
  KEY `idx_assigned_to` (`assigned_to`),
  KEY `idx_combined_confidence` (`combined_confidence`),
  KEY `idx_analyzed_at` (`analyzed_at`),
  KEY `fk_content_moderations_user_id` (`user_id`),
  KEY `fk_content_moderations_processed_by` (`processed_by`),
  CONSTRAINT `fk_content_moderations_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_content_moderations_assigned_to` FOREIGN KEY (`assigned_to`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_content_moderations_processed_by` FOREIGN KEY (`processed_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Moderation queue table
CREATE TABLE IF NOT EXISTS `moderation_queue` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `moderation_id` bigint(20) UNSIGNED NOT NULL,
  `content_id` bigint(20) UNSIGNED NOT NULL,
  `content_type` varchar(50) NOT NULL,
  `priority` enum('high','normal','low') DEFAULT 'normal',
  `confidence_score` decimal(5,2) DEFAULT 0.00,
  `assigned_to` bigint(20) UNSIGNED DEFAULT NULL,
  `assigned_at` timestamp NULL DEFAULT NULL,
  `escalated` tinyint(1) DEFAULT 0,
  `escalation_reason` text 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_priority_created` (`priority`,`created_at`),
  KEY `idx_assigned_to` (`assigned_to`),
  KEY `idx_escalated` (`escalated`),
  KEY `idx_confidence_score` (`confidence_score`),
  CONSTRAINT `fk_moderation_queue_moderation_id` FOREIGN KEY (`moderation_id`) REFERENCES `content_moderations` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_moderation_queue_assigned_to` FOREIGN KEY (`assigned_to`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Enhanced moderation_logs table
CREATE TABLE IF NOT EXISTS `moderation_logs` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `moderation_id` bigint(20) UNSIGNED DEFAULT NULL,
  `action` varchar(50) DEFAULT NULL,
  `reason` text DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `performed_by` bigint(20) UNSIGNED DEFAULT NULL,
  `confidence_score` decimal(5,2) DEFAULT NULL,
  `processing_time_ms` int(11) DEFAULT NULL,
  `api_response` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`api_response`)),
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_moderation_id` (`moderation_id`),
  KEY `idx_action` (`action`),
  KEY `idx_performed_by` (`performed_by`),
  KEY `idx_created_at` (`created_at`),
  CONSTRAINT `fk_moderation_logs_moderation_id` FOREIGN KEY (`moderation_id`) REFERENCES `content_moderations` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_moderation_logs_performed_by` FOREIGN KEY (`performed_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- User violations table for progressive enforcement
CREATE TABLE IF NOT EXISTS `user_violations` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `violation_type` varchar(50) NOT NULL COMMENT 'content_policy, spam, harassment, etc.',
  `severity` enum('minor','moderate','major','severe') DEFAULT 'minor',
  `description` text NOT NULL,
  `moderator_id` bigint(20) UNSIGNED DEFAULT NULL,
  `content_moderation_id` bigint(20) UNSIGNED DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `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_active` (`user_id`,`is_active`),
  KEY `idx_violation_type` (`violation_type`),
  KEY `idx_severity` (`severity`),
  KEY `idx_created_at` (`created_at`),
  CONSTRAINT `fk_user_violations_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_user_violations_moderator_id` FOREIGN KEY (`moderator_id`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_user_violations_content_moderation_id` FOREIGN KEY (`content_moderation_id`) REFERENCES `content_moderations` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Moderation appeals table
CREATE TABLE IF NOT EXISTS `moderation_appeals` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `content_moderation_id` bigint(20) UNSIGNED DEFAULT NULL,
  `user_violation_id` bigint(20) UNSIGNED DEFAULT NULL,
  `appeal_type` enum('content_decision','account_action','violation_dispute') NOT NULL,
  `reason` text NOT NULL,
  `additional_info` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`additional_info`)),
  `status` enum('pending','under_review','approved','denied','escalated') DEFAULT 'pending',
  `reviewed_by` bigint(20) UNSIGNED DEFAULT NULL,
  `reviewed_at` timestamp NULL DEFAULT NULL,
  `reviewer_notes` text DEFAULT NULL,
  `original_decision` varchar(50) DEFAULT NULL,
  `final_decision` varchar(50) 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_status` (`status`),
  KEY `idx_appeal_type` (`appeal_type`),
  KEY `idx_reviewed_by` (`reviewed_by`),
  CONSTRAINT `fk_moderation_appeals_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_moderation_appeals_content_moderation_id` FOREIGN KEY (`content_moderation_id`) REFERENCES `content_moderations` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_moderation_appeals_user_violation_id` FOREIGN KEY (`user_violation_id`) REFERENCES `user_violations` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_moderation_appeals_reviewed_by` FOREIGN KEY (`reviewed_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Add moderation columns to vault_files if not exists
ALTER TABLE `vault_files` 
ADD COLUMN IF NOT EXISTS `moderation_status` enum('pending','approved','rejected','under_review') DEFAULT 'pending' AFTER `status`,
ADD COLUMN IF NOT EXISTS `is_hidden` tinyint(1) DEFAULT 0 AFTER `moderation_status`;

-- Add moderation columns to media if not exists  
ALTER TABLE `media`
ADD COLUMN IF NOT EXISTS `moderation_status` enum('pending','approved','rejected','under_review') DEFAULT 'pending' AFTER `status`;