-- ============================================
-- Academic Writing Platform - Complete Database Schema
-- Version: 2.0.0 - Advanced Features
-- ============================================


-- ============================================
-- CORE LARAVEL TABLES
-- ============================================

CREATE TABLE IF NOT EXISTS `migrations` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `migration` VARCHAR(255) NOT NULL,
    `batch` INT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `sessions` (
    `id` VARCHAR(255) NOT NULL PRIMARY KEY,
    `user_id` BIGINT UNSIGNED NULL,
    `ip_address` VARCHAR(45) NULL,
    `user_agent` TEXT NULL,
    `payload` LONGTEXT NOT NULL,
    `last_activity` INT NOT NULL,
    INDEX `sessions_user_id_index` (`user_id`),
    INDEX `sessions_last_activity_index` (`last_activity`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `jobs` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `queue` VARCHAR(255) NOT NULL,
    `payload` LONGTEXT NOT NULL,
    `attempts` TINYINT UNSIGNED NOT NULL,
    `reserved_at` INT UNSIGNED NULL,
    `available_at` INT UNSIGNED NOT NULL,
    `created_at` INT UNSIGNED NOT NULL,
    INDEX `jobs_queue_index` (`queue`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `job_batches` (
    `id` VARCHAR(255) NOT NULL PRIMARY KEY,
    `name` VARCHAR(255) NOT NULL,
    `total_jobs` INT NOT NULL,
    `pending_jobs` INT NOT NULL,
    `failed_jobs` INT NOT NULL,
    `failed_job_ids` LONGTEXT NOT NULL,
    `options` MEDIUMTEXT NULL,
    `cancelled_at` INT NULL,
    `created_at` INT NOT NULL,
    `finished_at` INT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `failed_jobs` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `uuid` VARCHAR(255) NOT NULL UNIQUE,
    `connection` TEXT NOT NULL,
    `queue` TEXT NOT NULL,
    `payload` LONGTEXT NOT NULL,
    `exception` LONGTEXT NOT NULL,
    `failed_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `admin_profiles` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` BIGINT UNSIGNED NOT NULL,
    `first_name` VARCHAR(100) NULL,
    `last_name` VARCHAR(100) NULL,
    `phone` VARCHAR(30) NULL,
    `avatar` VARCHAR(500) NULL,
    `role` ENUM('super_admin', 'admin', 'manager', 'support', 'editor') DEFAULT 'admin',
    `permissions` JSON NULL,
    `department` VARCHAR(100) NULL,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `writer_profiles` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` BIGINT UNSIGNED NOT NULL,
    `first_name` VARCHAR(100) NULL,
    `last_name` VARCHAR(100) NULL,
    `phone` VARCHAR(30) NULL,
    `avatar` VARCHAR(500) NULL,
    `bio` TEXT NULL,
    `country` VARCHAR(100) NULL,
    `city` VARCHAR(100) NULL,
    `timezone` VARCHAR(50) DEFAULT 'UTC',
    `academic_level` ENUM('high_school', 'college', 'undergraduate', 'masters', 'phd', 'doctorate') DEFAULT 'undergraduate',
    `specialization` VARCHAR(255) NULL,
    `education` JSON NULL,
    `languages` JSON NULL,
    `skills` JSON NULL,
    `writer_level` ENUM('standard', 'premium', 'elite') DEFAULT 'standard',
    `level_upgraded_at` TIMESTAMP NULL DEFAULT NULL,
    `level_upgrade_paid` BOOLEAN DEFAULT FALSE,
    `rating` DECIMAL(3,2) DEFAULT 0.00,
    `total_reviews` INT DEFAULT 0,
    `total_orders` INT DEFAULT 0,
    `completed_orders` INT DEFAULT 0,
    `cancelled_orders` INT DEFAULT 0,
    `late_orders` INT DEFAULT 0,
    `total_earnings` DECIMAL(15,2) DEFAULT 0.00,
    `available_balance` DECIMAL(15,2) DEFAULT 0.00,
    `pending_balance` DECIMAL(15,2) DEFAULT 0.00,
    `withdrawn_balance` DECIMAL(15,2) DEFAULT 0.00,
    `fined_balance` DECIMAL(15,2) DEFAULT 0.00,
    `max_orders` INT DEFAULT 1,
    `current_orders` INT DEFAULT 0,
    `success_rate` DECIMAL(5,2) DEFAULT 0.00,
    `on_time_rate` DECIMAL(5,2) DEFAULT 0.00,
    `response_time` INT DEFAULT 0,
    `profile_link` VARCHAR(100) UNIQUE NULL,
    `profile_completed` BOOLEAN DEFAULT FALSE,
    `test_passed` BOOLEAN DEFAULT FALSE,
    `test_score` DECIMAL(5,2) NULL,
    `test_taken_at` TIMESTAMP NULL DEFAULT NULL,
    `test_attempts` INT DEFAULT 0,
    `samples` JSON NULL,
    `payment_methods` JSON NULL,
    `social_links` JSON NULL,
    `notifications` JSON NULL,
    `verified` BOOLEAN DEFAULT FALSE,
    `verified_at` TIMESTAMP NULL DEFAULT NULL,
    `featured` BOOLEAN DEFAULT FALSE,
    `priority` INT DEFAULT 0,
    `approval_status` ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
    `approved_at` TIMESTAMP NULL DEFAULT NULL,
    `approved_by` BIGINT UNSIGNED NULL,
    `rejection_reason` TEXT NULL,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    INDEX `idx_writer_level` (`writer_level`),
    INDEX `idx_writer_rating` (`rating`),
    INDEX `idx_writer_approval` (`approval_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `client_profiles` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` BIGINT UNSIGNED NOT NULL,
    `first_name` VARCHAR(100) NULL,
    `last_name` VARCHAR(100) NULL,
    `phone` VARCHAR(30) NULL,
    `avatar` VARCHAR(500) NULL,
    `company` VARCHAR(255) NULL,
    `country` VARCHAR(100) NULL,
    `city` VARCHAR(100) NULL,
    `timezone` VARCHAR(50) DEFAULT 'UTC',
    `client_level` ENUM('bronze', 'silver', 'gold', 'platinum', 'diamond') DEFAULT 'bronze',
    `level_upgraded_at` TIMESTAMP NULL DEFAULT NULL,
    `total_orders` INT DEFAULT 0,
    `completed_orders` INT DEFAULT 0,
    `cancelled_orders` INT DEFAULT 0,
    `total_spent` DECIMAL(15,2) DEFAULT 0.00,
    `total_tips` DECIMAL(15,2) DEFAULT 0.00,
    `available_balance` DECIMAL(15,2) DEFAULT 0.00,
    `loyalty_points` INT DEFAULT 0,
    `preferred_writer_id` BIGINT UNSIGNED NULL,
    `notifications` JSON NULL,
    `social_links` JSON NULL,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`preferred_writer_id`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- DOMAIN & LICENSING
-- ============================================

CREATE TABLE IF NOT EXISTS `domains` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `domain` VARCHAR(255) NOT NULL UNIQUE,
    `license_key` VARCHAR(50) NOT NULL,
    `status` ENUM('active', 'expired', 'suspended', 'pending') DEFAULT 'pending',
    `is_primary` BOOLEAN DEFAULT FALSE,
    `theme` VARCHAR(50) DEFAULT 'default',
    `primary_color` VARCHAR(10) DEFAULT '#667eea',
    `secondary_color` VARCHAR(10) DEFAULT '#764ba2',
    `logo` VARCHAR(500) NULL,
    `favicon` VARCHAR(500) NULL,
    `site_name` VARCHAR(255) NULL,
    `site_email` VARCHAR(255) NULL,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_domain` (`domain`),
    INDEX `idx_domain_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `licenses` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `license_key` VARCHAR(50) NOT NULL UNIQUE,
    `domain` VARCHAR(255) NOT NULL,
    `status` ENUM('active', 'expired', 'suspended', 'revoked') DEFAULT 'active',
    `purchase_date` TIMESTAMP NULL DEFAULT NULL,
    `expiry_date` TIMESTAMP NULL DEFAULT NULL,
    `customer_name` VARCHAR(255) NULL,
    `customer_email` VARCHAR(255) NULL,
    `customer_phone` VARCHAR(30) NULL,
    `purchase_code` VARCHAR(100) NULL,
    `verification_count` INT DEFAULT 0,
    `last_verification` TIMESTAMP NULL DEFAULT NULL,
    `notes` TEXT NULL,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_license_key` (`license_key`),
    INDEX `idx_license_domain` (`domain`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ai_usage_logs` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` BIGINT UNSIGNED NULL,
    `order_id` BIGINT UNSIGNED NULL,
    `provider` VARCHAR(100) NOT NULL,
    `model` VARCHAR(100) NOT NULL,
    `feature` VARCHAR(100) NOT NULL,
    `input_tokens` INT DEFAULT 0,
    `output_tokens` INT DEFAULT 0,
    `duration_ms` INT DEFAULT 0,
    `cost` DECIMAL(10,6) DEFAULT 0.000000,
    `status` ENUM('success', 'failed') DEFAULT 'success',
    `error_message` TEXT NULL,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    INDEX `idx_ai_user` (`user_id`),
    INDEX `idx_ai_feature` (`feature`),
    INDEX `idx_ai_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ai_checks` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `order_id` BIGINT UNSIGNED NOT NULL,
    `user_id` BIGINT UNSIGNED NOT NULL,
    `check_type` ENUM('ai_detection', 'plagiarism', 'grammar', 'formatting', 'humanize', 'paraphrase') NOT NULL,
    `provider` VARCHAR(100) NOT NULL,
    `input_text` LONGTEXT NULL,
    `output_text` LONGTEXT NULL,
    `result_json` JSON NULL,
    `score` DECIMAL(5,2) NULL,
    `status` ENUM('pending', 'processing', 'completed', 'failed') DEFAULT 'pending',
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`) ON DELETE CASCADE,
    INDEX `idx_check_order` (`order_id`),
    INDEX `idx_check_type` (`check_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- NOTIFICATIONS & TEMPLATES
-- ============================================

CREATE TABLE IF NOT EXISTS `notification_incidents` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `slug` VARCHAR(100) NOT NULL UNIQUE,
    `name` VARCHAR(255) NOT NULL,
    `description` TEXT NULL,
    `email_enabled` BOOLEAN DEFAULT TRUE,
    `sms_enabled` BOOLEAN DEFAULT FALSE,
    `whatsapp_enabled` BOOLEAN DEFAULT FALSE,
    `push_enabled` BOOLEAN DEFAULT FALSE,
    `in_app_enabled` BOOLEAN DEFAULT TRUE,
    `email_template_id` BIGINT UNSIGNED NULL,
    `sms_template_id` BIGINT UNSIGNED NULL,
    `recipient_types` JSON NULL,
    `is_active` BOOLEAN DEFAULT TRUE,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`email_template_id`) REFERENCES `email_templates`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`sms_template_id`) REFERENCES `sms_templates`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `email_templates` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL UNIQUE,
    `slug` VARCHAR(100) NOT NULL UNIQUE,
    `subject` VARCHAR(255) NOT NULL,
    `body` LONGTEXT NOT NULL,
    `variables` JSON NULL,
    `description` TEXT NULL,
    `is_active` BOOLEAN DEFAULT TRUE,
    `category` VARCHAR(50) DEFAULT 'general',
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_template_slug` (`slug`),
    INDEX `idx_template_category` (`category`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `sms_templates` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL UNIQUE,
    `slug` VARCHAR(100) NOT NULL UNIQUE,
    `message` TEXT NOT NULL,
    `variables` JSON NULL,
    `description` TEXT NULL,
    `is_active` BOOLEAN DEFAULT TRUE,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `whatsapp_templates` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL UNIQUE,
    `slug` VARCHAR(100) NOT NULL UNIQUE,
    `template_name` VARCHAR(100) NULL,
    `message` TEXT NOT NULL,
    `variables` JSON NULL,
    `description` TEXT NULL,
    `is_active` BOOLEAN DEFAULT TRUE,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `notifications` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` BIGINT UNSIGNED NOT NULL,
    `type` VARCHAR(100) NOT NULL,
    `incident_slug` VARCHAR(100) NULL,
    `title` VARCHAR(255) NOT NULL,
    `message` TEXT NOT NULL,
    `data` JSON NULL,
    `read_at` TIMESTAMP NULL DEFAULT NULL,
    `sent_via` JSON NULL COMMENT 'email,sms,whatsapp,push,in_app',
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    INDEX `idx_notification_user` (`user_id`),
    INDEX `idx_notification_read` (`read_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `email_logs` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `to_email` VARCHAR(255) NOT NULL,
    `to_name` VARCHAR(255) NULL,
    `from_email` VARCHAR(255) NOT NULL,
    `from_name` VARCHAR(255) NULL,
    `subject` VARCHAR(500) NOT NULL,
    `template` VARCHAR(100) NULL,
    `body` LONGTEXT NULL,
    `attachments` JSON NULL,
    `status` ENUM('pending', 'sent', 'failed', 'queued') DEFAULT 'pending',
    `error_message` TEXT NULL,
    `sent_at` TIMESTAMP NULL DEFAULT NULL,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_email_to` (`to_email`),
    INDEX `idx_email_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `sms_logs` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `phone` VARCHAR(30) NOT NULL,
    `message` TEXT NOT NULL,
    `template` VARCHAR(100) NULL,
    `provider` VARCHAR(100) NULL,
    `status` ENUM('pending', 'sent', 'failed', 'queued') DEFAULT 'pending',
    `message_id` VARCHAR(255) NULL,
    `error_message` TEXT NULL,
    `cost` DECIMAL(10,4) DEFAULT 0.0000,
    `sent_at` TIMESTAMP NULL DEFAULT NULL,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_sms_phone` (`phone`),
    INDEX `idx_sms_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `whatsapp_logs` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `phone` VARCHAR(30) NOT NULL,
    `message` TEXT NOT NULL,
    `template` VARCHAR(100) NULL,
    `status` ENUM('pending', 'sent', 'failed', 'queued') DEFAULT 'pending',
    `message_id` VARCHAR(255) NULL,
    `error_message` TEXT NULL,
    `sent_at` TIMESTAMP NULL DEFAULT NULL,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_whatsapp_phone` (`phone`),
    INDEX `idx_whatsapp_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- BULK MESSAGING
-- ============================================

CREATE TABLE IF NOT EXISTS `bulk_campaigns` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(255) NOT NULL,
    `type` ENUM('email', 'sms', 'whatsapp', 'push') NOT NULL,
    `subject` VARCHAR(500) NULL,
    `content` LONGTEXT NOT NULL,
    `target_audience` ENUM('all', 'writers', 'clients', 'admins', 'custom') DEFAULT 'all',
    `target_filter` JSON NULL,
    `total_recipients` INT DEFAULT 0,
    `sent_count` INT DEFAULT 0,
    `failed_count` INT DEFAULT 0,
    `status` ENUM('draft', 'scheduled', 'sending', 'completed', 'failed', 'cancelled') DEFAULT 'draft',
    `scheduled_at` TIMESTAMP NULL DEFAULT NULL,
    `started_at` TIMESTAMP NULL DEFAULT NULL,
    `completed_at` TIMESTAMP NULL DEFAULT NULL,
    `created_by` BIGINT UNSIGNED NULL,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `bulk_campaign_recipients` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `campaign_id` BIGINT UNSIGNED NOT NULL,
    `user_id` BIGINT UNSIGNED NULL,
    `email` VARCHAR(255) NULL,
    `phone` VARCHAR(30) NULL,
    `status` ENUM('pending', 'sent', 'failed') DEFAULT 'pending',
    `error_message` TEXT NULL,
    `sent_at` TIMESTAMP NULL DEFAULT NULL,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`campaign_id`) REFERENCES `bulk_campaigns`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- WRITER EXAMS & TESTS
-- ============================================

CREATE TABLE IF NOT EXISTS `writer_exams` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(255) NOT NULL,
    `description` TEXT NULL,
    `instructions` TEXT NULL,
    `time_limit` INT DEFAULT 60 COMMENT 'Time limit in minutes',
    `passing_score` DECIMAL(5,2) DEFAULT 70.00,
    `max_attempts` INT DEFAULT 3,
    `essay_required` BOOLEAN DEFAULT TRUE,
    `essay_topic` TEXT NULL,
    `essay_word_limit` INT DEFAULT 500,
    `is_active` BOOLEAN DEFAULT TRUE,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `exam_questions` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `exam_id` BIGINT UNSIGNED NOT NULL,
    `question` TEXT NOT NULL,
    `question_type` ENUM('multiple_choice', 'true_false', 'short_answer', 'essay') DEFAULT 'multiple_choice',
    `options` JSON NULL,
    `correct_answer` TEXT NOT NULL,
    `points` INT DEFAULT 1,
    `sort_order` INT DEFAULT 0,
    `is_active` BOOLEAN DEFAULT TRUE,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`exam_id`) REFERENCES `writer_exams`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `exam_attempts` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `exam_id` BIGINT UNSIGNED NOT NULL,
    `writer_id` BIGINT UNSIGNED NOT NULL,
    `answers` JSON NULL,
    `essay_content` LONGTEXT NULL,
    `essay_file` VARCHAR(500) NULL,
    `score` DECIMAL(5,2) NULL,
    `percentage` DECIMAL(5,2) NULL,
    `passed` BOOLEAN DEFAULT FALSE,
    `started_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `submitted_at` TIMESTAMP NULL DEFAULT NULL,
    `time_spent` INT DEFAULT 0 COMMENT 'Time spent in seconds',
    `ip_address` VARCHAR(45) NULL,
    `status` ENUM('in_progress', 'submitted', 'graded') DEFAULT 'in_progress',
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`exam_id`) REFERENCES `writer_exams`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`writer_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- ORDERS (Main)
-- ============================================

CREATE TABLE IF NOT EXISTS `orders` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `order_number` VARCHAR(50) NOT NULL UNIQUE,
    `client_id` BIGINT UNSIGNED NOT NULL,
    `writer_id` BIGINT UNSIGNED NULL,
    `assigned_by` BIGINT UNSIGNED NULL,
    `assigned_at` TIMESTAMP NULL DEFAULT NULL,
    `service_id` BIGINT UNSIGNED NOT NULL,
    `paper_type_id` BIGINT UNSIGNED NOT NULL,
    `academic_level` ENUM('high_school', 'college', 'undergraduate', 'masters', 'phd', 'doctorate') NOT NULL,
    `title` VARCHAR(500) NOT NULL,
    `topic` VARCHAR(500) NULL,
    `subject` VARCHAR(100) NULL,
    `discipline` VARCHAR(100) NULL,
    `description` TEXT NULL,
    `instructions` LONGTEXT NULL,
    `pages` INT NOT NULL DEFAULT 1,
    `words` INT NULL,
    `slides` INT DEFAULT 0,
    `charts` INT DEFAULT 0,
    `excel_sheets` INT DEFAULT 0,
    `spacing` ENUM('single', 'double', '1.5') DEFAULT 'double',
    `style` ENUM('apa', 'mla', 'chicago', 'harvard', 'turabian', 'oxford', 'vancouver', 'ieee', 'ama', 'other') DEFAULT 'apa',
    `style_other` VARCHAR(100) NULL,
    `sources` INT DEFAULT 0,
    `language` VARCHAR(50) DEFAULT 'English',
    `deadline` TIMESTAMP NOT NULL,
    `original_deadline` TIMESTAMP NULL,
    `deadline_extended` BOOLEAN DEFAULT FALSE,
    `extension_requested` BOOLEAN DEFAULT FALSE,
    `extension_request` JSON NULL,
    `timezone` VARCHAR(50) DEFAULT 'UTC',
    `urgency` ENUM('standard', 'urgent', 'critical') DEFAULT 'standard',
    `status` ENUM('draft', 'unpaid', 'pending_payment', 'available', 'bidding', 'in_progress', 'revision', 'completed', 'delivered', 'approved', 'cancelled', 'refunded', 'disputed') DEFAULT 'draft',
    `status_history` JSON NULL,
    `payment_status` ENUM('pending', 'partial', 'paid', 'refunded', 'failed', 'cancelled') DEFAULT 'pending',
    `base_price` DECIMAL(15,2) DEFAULT 0.00,
    `urgency_multiplier` DECIMAL(5,2) DEFAULT 1.00,
    `academic_multiplier` DECIMAL(5,2) DEFAULT 1.00,
    `paper_type_multiplier` DECIMAL(5,2) DEFAULT 1.00,
    `service_multiplier` DECIMAL(5,2) DEFAULT 1.00,
    `additional_services` JSON NULL,
    `additional_services_cost` DECIMAL(15,2) DEFAULT 0.00,
    `subtotal` DECIMAL(15,2) DEFAULT 0.00,
    `discount_code` VARCHAR(50) NULL,
    `discount_amount` DECIMAL(15,2) DEFAULT 0.00,
    `discount_percent` DECIMAL(5,2) DEFAULT 0.00,
    `tax` DECIMAL(15,2) DEFAULT 0.00,
    `tax_percent` DECIMAL(5,2) DEFAULT 0.00,
    `total` DECIMAL(15,2) DEFAULT 0.00,
    `currency` VARCHAR(10) DEFAULT 'USD',
    `writer_amount` DECIMAL(15,2) DEFAULT 0.00,
    `writer_percent` DECIMAL(5,2) DEFAULT 20.00,
    `tip_amount` DECIMAL(15,2) DEFAULT 0.00,
    `tip_status` ENUM('pending', 'paid', 'none') DEFAULT 'none',
    `fine_amount` DECIMAL(15,2) DEFAULT 0.00,
    `fine_reason` TEXT NULL,
    `fined_at` TIMESTAMP NULL DEFAULT NULL,
    `writer_rating` TINYINT UNSIGNED NULL,
    `writer_review` TEXT NULL,
    `service_rating` TINYINT UNSIGNED NULL,
    `service_review` TEXT NULL,
    `rated_at` TIMESTAMP NULL DEFAULT NULL,
    `completed_at` TIMESTAMP NULL DEFAULT NULL,
    `delivered_at` TIMESTAMP NULL DEFAULT NULL,
    `approved_at` TIMESTAMP NULL DEFAULT NULL,
    `cancelled_at` TIMESTAMP NULL DEFAULT NULL,
    `cancelled_by` BIGINT UNSIGNED NULL,
    `cancel_reason` TEXT NULL,
    `cancel_request` JSON NULL,
    `revision_count` INT DEFAULT 0,
    `revision_request` TEXT NULL,
    `revision_deadline` TIMESTAMP NULL DEFAULT NULL,
    `last_revision_at` TIMESTAMP NULL DEFAULT NULL,
    `is_resubmission` BOOLEAN DEFAULT FALSE,
    `original_order_id` BIGINT UNSIGNED NULL,
    `priority` ENUM('low', 'normal', 'high', 'urgent') DEFAULT 'normal',
    `featured` BOOLEAN DEFAULT FALSE,
    `complexity` ENUM('simple', 'moderate', 'complex', 'highly_complex') DEFAULT 'moderate',
    `ai_check_status` ENUM('pending', 'checking', 'completed', 'failed') DEFAULT 'pending',
    `ai_check_result` JSON NULL,
    `ai_check_percent` DECIMAL(5,2) NULL,
    `plagiarism_check_status` ENUM('pending', 'checking', 'completed', 'failed') DEFAULT 'pending',
    `plagiarism_check_result` JSON NULL,
    `plagiarism_percent` DECIMAL(5,2) NULL,
    `grammar_check_status` ENUM('pending', 'checking', 'completed', 'failed') DEFAULT 'pending',
    `grammar_check_result` JSON NULL,
    `quality_score` DECIMAL(5,2) NULL,
    `admin_notes` TEXT NULL,
    `internal_notes` TEXT NULL,
    `tags` JSON NULL,
    `metadata` JSON NULL,
    `ip_address` VARCHAR(45) NULL,
    `user_agent` TEXT NULL,
    `domain_id` BIGINT UNSIGNED NULL,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted_at` TIMESTAMP NULL DEFAULT NULL,
    FOREIGN KEY (`client_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`writer_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`assigned_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`domain_id`) REFERENCES `domains`(`id`) ON DELETE SET NULL,
    INDEX `idx_order_number` (`order_number`),
    INDEX `idx_order_client` (`client_id`),
    INDEX `idx_order_writer` (`writer_id`),
    INDEX `idx_order_status` (`status`),
    INDEX `idx_order_payment` (`payment_status`),
    INDEX `idx_order_deadline` (`deadline`),
    INDEX `idx_order_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `admin_profiles` (`user_id`, `first_name`, `last_name`, `role`) VALUES
(1, 'Super', 'Admin', 'super_admin');

-- Insert default domain
INSERT INTO `domains` (`domain`, `license_key`, `status`, `is_primary`, `site_name`, `created_at`) VALUES
('localhost', 'DEMO-KEY-0000-0000', 'active', TRUE, 'Academic Platform', NOW());

-- Insert notification incidents (sample)
INSERT INTO `notification_incidents` (`slug`, `name`, `description`, `email_enabled`, `sms_enabled`, `whatsapp_enabled`, `is_active`) VALUES
('writer_registered', 'Writer Registered', 'When a new writer registers', TRUE, TRUE, FALSE, TRUE),
('client_registered', 'Client Registered', 'When a new client registers', TRUE, TRUE, FALSE, TRUE),
('order_placed', 'Order Placed', 'When a new order is placed', TRUE, TRUE, TRUE, TRUE),
('order_assigned', 'Order Assigned', 'When order is assigned to writer', TRUE, TRUE, FALSE, TRUE),
('order_completed', 'Order Completed', 'When order is completed', TRUE, TRUE, FALSE, TRUE),
('deadline_24h', 'Deadline 24 Hours', '24 hours before deadline', TRUE, TRUE, TRUE, TRUE),
('deadline_6h', 'Deadline 6 Hours', '6 hours before deadline', TRUE, TRUE, TRUE, TRUE),
('payment_received', 'Payment Received', 'When payment is received', TRUE, TRUE, FALSE, TRUE),
('withdrawal_requested', 'Withdrawal Requested', 'When writer requests withdrawal', TRUE, TRUE, FALSE, TRUE);

-- Insert email templates
INSERT INTO `email_templates` (`name`, `slug`, `subject`, `body`, `category`) VALUES
('Welcome Email', 'welcome', 'Welcome to {{site_name}}!', '<h1>Welcome, {{name}}!</h1><p>Thank you for registering at {{site_name}}.</p>', 'registration'),
('OTP Verification', 'otp_verification', 'Your Verification Code', '<p>Your verification code is: <strong>{{otp}}</strong></p><p>Valid for {{expiry}} minutes.</p>', 'verification'),
('New Order', 'new_order', 'New Order #{{order_number}}', '<h2>New Order Received</h2><p>Order: {{order_number}}</p><p>Amount: {{amount}}</p>', 'orders'),
('Order Assigned', 'order_assigned', 'Order Assigned - #{{order_number}}', '<p>You have been assigned to order #{{order_number}}</p><p>Deadline: {{deadline}}</p>', 'orders'),
('Deadline Reminder', 'deadline_reminder', 'Deadline Reminder - {{hours}} hours remaining', '<p>Order #{{order_number}} deadline in {{hours}} hours</p>', 'reminders'),
('Payment Confirmation', 'payment_confirmation', 'Payment Confirmed', '<p>Payment of {{amount}} confirmed for order #{{order_number}}</p>', 'payments'),
('Writer Approved', 'writer_approved', 'Your Account Approved', '<p>Congratulations! Your writer account has been approved.</p>', 'writers'),
('Withdrawal Processed', 'withdrawal_processed', 'Withdrawal Processed', '<p>Your withdrawal of {{amount}} has been processed.</p>', 'payments');

-- Insert SMS templates
INSERT INTO `sms_templates` (`name`, `slug`, `message`) VALUES
('OTP Verification', 'otp_verification', 'Your {{site_name}} verification code is: {{otp}}. Valid for {{expiry}} minutes.'),
('New Order', 'new_order', 'New order #{{order_number}} received. Amount: {{amount}}. Login to view.'),
('Order Assigned', 'order_assigned', 'Order #{{order_number}} assigned to you. Deadline: {{deadline}}.'),
('Deadline Reminder', 'deadline_reminder', 'Order #{{order_number}} due in {{hours}} hours. Submit now.'),
('Payment Received', 'payment_received', 'Payment of {{amount}} received for order #{{order_number}}.');
