-- FlowCore AI Workflow Manager - Database Schema
-- MySQL 5.7+ / MariaDB 10.3+


-- Drop tables if they exist (in reverse dependency order)

-- =====================================================
-- SETTINGS TABLE
-- =====================================================
CREATE TABLE `settings` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `setting_key` VARCHAR(100) NOT NULL UNIQUE,
    `setting_value` TEXT NOT NULL,
    `setting_type` ENUM('string', 'number', 'boolean', 'json') DEFAULT 'string',
    `description` VARCHAR(255) DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_setting_key` (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- DEPARTMENTS TABLE
-- =====================================================
CREATE TABLE `departments` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(100) NOT NULL,
    `code` VARCHAR(50) NOT NULL UNIQUE,
    `description` TEXT DEFAULT NULL,
    `is_active` TINYINT(1) DEFAULT 1,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_department_code` (`code`),
    INDEX `idx_department_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- CUSTOMER REQUESTS TABLE
-- =====================================================
CREATE TABLE `customer_requests` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `request_number` VARCHAR(20) NOT NULL UNIQUE,
    `customer_id` INT UNSIGNED NOT NULL,
    `customer_name` VARCHAR(200) NOT NULL,
    `customer_email` VARCHAR(150) NOT NULL,
    `customer_phone` VARCHAR(20) DEFAULT NULL,
    `subject` VARCHAR(255) NOT NULL,
    `description` TEXT NOT NULL,
    `category` VARCHAR(100) DEFAULT NULL,
    `subcategory` VARCHAR(100) DEFAULT NULL,
    `priority` ENUM('low', 'normal', 'high', 'critical') DEFAULT 'normal',
    `status` ENUM('new', 'ai_processing', 'ai_review_required', 'assigned', 
                   'in_progress', 'awaiting_approval', 'approved', 'rejected',
                   'pending_customer', 'overdue', 'escalated', 'resolved', 'closed') 
                   DEFAULT 'new',
    `department_id` INT UNSIGNED DEFAULT NULL,
    `assigned_to` INT UNSIGNED DEFAULT NULL,
    `ai_summary` TEXT DEFAULT NULL,
    `ai_classification` VARCHAR(100) DEFAULT NULL,
    `ai_required_action` TEXT DEFAULT NULL,
    `ai_requires_approval` TINYINT(1) DEFAULT 0,
    `sla_deadline` DATETIME DEFAULT NULL,
    `resolved_at` DATETIME DEFAULT NULL,
    `closed_at` DATETIME DEFAULT NULL,
    `source` VARCHAR(50) DEFAULT 'web_form',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_request_number` (`request_number`),
    INDEX `idx_request_customer` (`customer_id`),
    INDEX `idx_request_status` (`status`),
    INDEX `idx_request_priority` (`priority`),
    INDEX `idx_request_department` (`department_id`),
    INDEX `idx_request_assigned` (`assigned_to`),
    INDEX `idx_request_category` (`category`),
    INDEX `idx_request_deadline` (`sla_deadline`),
    INDEX `idx_request_created` (`created_at`),
    CONSTRAINT `fk_request_department` FOREIGN KEY (`department_id`) 
        REFERENCES `departments` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT `fk_request_assigned` FOREIGN KEY (`assigned_to`) 
        REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- REQUEST TASKS TABLE
-- =====================================================
CREATE TABLE `request_tasks` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `request_id` INT UNSIGNED NOT NULL,
    `task_title` VARCHAR(255) NOT NULL,
    `task_description` TEXT DEFAULT NULL,
    `assigned_to` INT UNSIGNED NOT NULL,
    `status` ENUM('pending', 'in_progress', 'completed', 'cancelled') DEFAULT 'pending',
    `priority` ENUM('low', 'normal', 'high', 'critical') DEFAULT 'normal',
    `due_date` DATETIME DEFAULT NULL,
    `completed_at` DATETIME DEFAULT NULL,
    `completed_notes` TEXT DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_task_request` (`request_id`),
    INDEX `idx_task_assigned` (`assigned_to`),
    INDEX `idx_task_status` (`status`),
    CONSTRAINT `fk_task_request` FOREIGN KEY (`request_id`) 
        REFERENCES `customer_requests` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_task_assigned` FOREIGN KEY (`assigned_to`) 
        REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- APPROVALS TABLE
-- =====================================================
CREATE TABLE `approvals` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `request_id` INT UNSIGNED NOT NULL,
    `approval_type` VARCHAR(50) NOT NULL,
    `requested_by` INT UNSIGNED NOT NULL,
    `approved_by` INT UNSIGNED DEFAULT NULL,
    `status` ENUM('pending', 'approved', 'rejected', 'cancelled') DEFAULT 'pending',
    `reason` TEXT DEFAULT NULL,
    `notes` TEXT DEFAULT NULL,
    `requested_at` DATETIME DEFAULT NULL,
    `decided_at` DATETIME DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_approval_request` (`request_id`),
    INDEX `idx_approval_status` (`status`),
    INDEX `idx_approval_requested` (`requested_by`),
    INDEX `idx_approval_approved` (`approved_by`),
    CONSTRAINT `fk_approval_request` FOREIGN KEY (`request_id`) 
        REFERENCES `customer_requests` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- NOTIFICATIONS TABLE
-- =====================================================
CREATE TABLE `notifications` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` INT UNSIGNED DEFAULT NULL,
    `request_id` INT UNSIGNED DEFAULT NULL,
    `type` VARCHAR(50) NOT NULL,
    `title` VARCHAR(255) NOT NULL,
    `message` TEXT NOT NULL,
    `is_read` TINYINT(1) DEFAULT 0,
    `read_at` DATETIME DEFAULT NULL,
    `is_email_sent` TINYINT(1) DEFAULT 0,
    `email_sent_at` DATETIME DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_notification_user` (`user_id`),
    INDEX `idx_notification_request` (`request_id`),
    INDEX `idx_notification_type` (`type`),
    INDEX `idx_notification_read` (`is_read`),
    INDEX `idx_notification_created` (`created_at`),
    CONSTRAINT `fk_notification_user` FOREIGN KEY (`user_id`) 
        REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_notification_request` FOREIGN KEY (`request_id`) 
        REFERENCES `customer_requests` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- WORKFLOW LOGS TABLE
-- =====================================================
CREATE TABLE `workflow_logs` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `request_id` INT UNSIGNED NOT NULL,
    `user_id` INT UNSIGNED DEFAULT NULL,
    `action` VARCHAR(100) NOT NULL,
    `description` TEXT DEFAULT NULL,
    `old_status` VARCHAR(50) DEFAULT NULL,
    `new_status` VARCHAR(50) DEFAULT NULL,
    `metadata` JSON DEFAULT NULL,
    `ip_address` VARCHAR(45) DEFAULT NULL,
    `user_agent` VARCHAR(500) DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_log_request` (`request_id`),
    INDEX `idx_log_user` (`user_id`),
    INDEX `idx_log_action` (`action`),
    INDEX `idx_log_created` (`created_at`),
    CONSTRAINT `fk_log_request` FOREIGN KEY (`request_id`) 
        REFERENCES `customer_requests` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_log_user` FOREIGN KEY (`user_id`) 
        REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- ESCALATIONS TABLE
-- =====================================================
CREATE TABLE `escalations` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `request_id` INT UNSIGNED NOT NULL,
    `escalated_by` INT UNSIGNED DEFAULT NULL,
    `escalated_to` INT UNSIGNED DEFAULT NULL,
    `reason` VARCHAR(255) NOT NULL,
    `description` TEXT DEFAULT NULL,
    `status` ENUM('active', 'resolved', 'cancelled') DEFAULT 'active',
    `resolved_by` INT UNSIGNED DEFAULT NULL,
    `resolved_at` DATETIME DEFAULT NULL,
    `resolved_notes` TEXT DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_escalation_request` (`request_id`),
    INDEX `idx_escalation_status` (`status`),
    INDEX `idx_escalation_escalated_to` (`escalated_to`),
    CONSTRAINT `fk_escalation_request` FOREIGN KEY (`request_id`) 
        REFERENCES `customer_requests` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- AI PROCESSING LOGS TABLE
-- =====================================================
CREATE TABLE `ai_processing_logs` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `request_id` INT UNSIGNED NOT NULL,
    `input_text` TEXT NOT NULL,
    `ai_response` TEXT DEFAULT NULL,
    `parsed_response` JSON DEFAULT NULL,
    `status` ENUM('success', 'failed', 'fallback') DEFAULT 'pending',
    `error_message` TEXT DEFAULT NULL,
    `processing_time_ms` INT UNSIGNED DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_ai_log_request` (`request_id`),
    INDEX `idx_ai_log_status` (`status`),
    INDEX `idx_ai_log_created` (`created_at`),
    CONSTRAINT `fk_ai_log_request` FOREIGN KEY (`request_id`) 
        REFERENCES `customer_requests` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- INSERT DEFAULT SETTINGS
-- =====================================================
INSERT INTO `settings` (`setting_key`, `setting_value`, `setting_type`, `description`) VALUES
('company_name', 'FlowCore Services', 'string', 'Company name displayed throughout the system'),
('sla_low_hours', '72', 'number', 'SLA deadline in hours for low priority requests'),
('sla_normal_hours', '48', 'number', 'SLA deadline in hours for normal priority requests'),
('sla_high_hours', '24', 'number', 'SLA deadline in hours for high priority requests'),
('sla_critical_hours', '4', 'number', 'SLA deadline in hours for critical priority requests'),
('reminder_before_hours', '4', 'number', 'Hours before deadline to send reminder'),
('escalation_after_hours', '2', 'number', 'Hours after deadline to escalate'),
('max_login_attempts', '5', 'number', 'Maximum failed login attempts before lockout'),
('lockout_duration_minutes', '30', 'number', 'Account lockout duration in minutes'),
('ai_provider', 'openai', 'string', 'AI provider: openai, anthropic, ollama'),
('ai_model', 'gpt-4o-mini', 'string', 'AI model to use'),
('ai_fallback_mode', '1', 'boolean', 'Enable demo/fallback mode when AI is unavailable'),
('email_notifications', '1', 'boolean', 'Enable email notifications'),
('email_from', 'noreply@flowcore.example.com', 'string', 'From email address'),
('auto_assign_tasks', '1', 'boolean', 'Automatically assign tasks to staff'),
('require_approval_for_refunds', '1', 'boolean', 'Require manager approval for refunds/compensation'),
('require_approval_threshold', '500.00', 'string', 'Minimum amount requiring approval');

-- =====================================================
-- INSERT DEPARTMENTS
-- =====================================================
INSERT INTO `departments` (`name`, `code`, `description`, `is_active`) VALUES
('Customer Support', 'SUPPORT', 'Handles customer complaints, inquiries and general support', 1),
('Technical Support', 'TECH', 'Handles technical issues, bugs and technical inquiries', 1),
('Finance', 'FINANCE', 'Handles billing, payments and financial matters', 1),
('Operations', 'OPS', 'Handles document reviews and operational processes', 1),
('Sales', 'SALES', 'Handles sales inquiries and new customer onboarding', 1),
('Human Resources', 'HR', 'Handles employee-related matters', 1);





-- =====================================================
-- INSERT APPROVALS
-- =====================================================
INSERT INTO `approvals` (`request_id`, `approval_type`, `requested_by`, `approved_by`, `status`, `reason`, `requested_at`) VALUES
-- Approval for Request 3 (Billing refund may be needed)
(3, 'refund_approval', 10, NULL, 'pending', 'Customer overcharged $300 for premium package', NOW());

-- =====================================================
-- INSERT NOTIFICATIONS
-- =====================================================
INSERT INTO `notifications` (`user_id`, `request_id`, `type`, `title`, `message`, `is_read`, `created_at`) VALUES
-- Notifications for Request 1
(5, 1, 'task_assigned', 'New Task Assigned', 'You have been assigned a task for request FC-000001: Investigate delayed service request', 0, DATE_SUB(NOW(), INTERVAL 1 DAY)),
(5, 1, 'request_overdue', 'Request Overdue', 'Request FC-000001 is now overdue. The SLA deadline has passed.', 0, DATE_SUB(NOW(), INTERVAL 2 DAY)),
(2, 1, 'request_overdue', 'Department Request Overdue', 'Request FC-000001 assigned to Customer Support is overdue.', 0, DATE_SUB(NOW(), INTERVAL 2 DAY)),
-- Notifications for Request 2
(8, 2, 'task_assigned', 'New Task Assigned', 'You have been assigned a task for request FC-000002: Verify account status', 0, DATE_SUB(NOW(), INTERVAL 6 HOUR)),
-- Notifications for Request 3
(10, 3, 'task_assigned', 'New Task Assigned', 'You have been assigned a task for request FC-000003: Review billing records', 0, DATE_SUB(NOW(), INTERVAL 12 HOUR)),
(3, 3, 'approval_required', 'Approval Required', 'Request FC-000003 requires manager approval for potential refund.', 0, DATE_SUB(NOW(), INTERVAL 12 HOUR)),
-- Notifications for Request 4
(12, 4, 'task_assigned', 'New Task Assigned', 'You have been assigned a task for request FC-000004: Review contract terms', 0, DATE_SUB(NOW(), INTERVAL 8 HOUR)),
-- Notifications for Request 5
(6, 5, 'request_resolved', 'Request Resolved', 'Request FC-000005 has been resolved and closed.', 0, DATE_SUB(NOW(), INTERVAL 1 DAY)),
(13, 5, 'request_resolved', 'Your Request Has Been Resolved', 'Request FC-000005 (New user account setup) has been completed.', 0, DATE_SUB(NOW(), INTERVAL 1 DAY)),
-- Admin notification
(1, NULL, 'system', 'System Notification', 'FlowCore AI Workflow Manager is running normally.', 0, NOW());

-- =====================================================
-- INSERT WORKFLOW LOGS
-- =====================================================
INSERT INTO `workflow_logs` (`request_id`, `user_id`, `action`, `description`, `old_status`, `new_status`, `created_at`) VALUES
-- Logs for Request 1 (FC-000001)
(1, NULL, 'request_created', 'Customer submitted new request via web form', NULL, 'new', DATE_SUB(NOW(), INTERVAL 5 DAY)),
(1, NULL, 'ai_processing', 'AI processing initiated', 'new', 'ai_processing', DATE_SUB(NOW(), INTERVAL 5 DAY)),
(1, NULL, 'ai_classified', 'AI classified request as Customer Complaint - Delayed Service', 'ai_processing', 'assigned', DATE_SUB(NOW(), INTERVAL 5 DAY)),
(1, NULL, 'priority_set', 'Priority set to High based on AI analysis', NULL, 'high', DATE_SUB(NOW(), INTERVAL 5 DAY)),
(1, NULL, 'assigned', 'Request assigned to Customer Support department', NULL, 'assigned', DATE_SUB(NOW(), INTERVAL 5 DAY)),
(1, NULL, 'task_created', 'Task created: Investigate delayed service request', 'assigned', 'assigned', DATE_SUB(NOW(), INTERVAL 5 DAY)),
(1, NULL, 'task_created', 'Task created: Contact customer with update', 'assigned', 'assigned', DATE_SUB(NOW(), INTERVAL 5 DAY)),
(1, 5, 'task_started', 'Staff started working on task', 'assigned', 'in_progress', DATE_SUB(NOW(), INTERVAL 4 DAY)),
(1, NULL, 'status_changed', 'Request status changed to In Progress', 'assigned', 'in_progress', DATE_SUB(NOW(), INTERVAL 4 DAY)),
(1, NULL, 'deadline_warning', 'SLA deadline approaching - 4 hours remaining', NULL, NULL, DATE_SUB(NOW(), INTERVAL 1 DAY)),
(1, NULL, 'request_overdue', 'Request has exceeded SLA deadline', 'in_progress', 'overdue', DATE_SUB(NOW(), INTERVAL 2 DAY)),

-- Logs for Request 2 (FC-000002)
(2, NULL, 'request_created', 'Customer submitted new request via web form', NULL, 'new', DATE_SUB(NOW(), INTERVAL 8 HOUR)),
(2, NULL, 'ai_processing', 'AI processing initiated', 'new', 'ai_processing', DATE_SUB(NOW(), INTERVAL 8 HOUR)),
(2, NULL, 'ai_classified', 'AI classified request as Technical Issue - Login Problem', 'ai_processing', 'assigned', DATE_SUB(NOW(), INTERVAL 8 HOUR)),
(2, NULL, 'priority_set', 'Priority set to High based on AI analysis', NULL, 'high', DATE_SUB(NOW(), INTERVAL 8 HOUR)),
(2, NULL, 'assigned', 'Request assigned to Technical Support department', NULL, 'assigned', DATE_SUB(NOW(), INTERVAL 8 HOUR)),
(2, NULL, 'task_created', 'Task created: Verify account status', 'assigned', 'assigned', DATE_SUB(NOW(), INTERVAL 8 HOUR)),
(2, 8, 'task_started', 'Staff started working on task', 'assigned', 'in_progress', DATE_SUB(NOW(), INTERVAL 6 HOUR)),
(2, NULL, 'status_changed', 'Request status changed to In Progress', 'assigned', 'in_progress', DATE_SUB(NOW(), INTERVAL 6 HOUR)),

-- Logs for Request 3 (FC-000003)
(3, NULL, 'request_created', 'Customer submitted new request via web form', NULL, 'new', DATE_SUB(NOW(), INTERVAL 24 HOUR)),
(3, NULL, 'ai_processing', 'AI processing initiated', 'new', 'ai_processing', DATE_SUB(NOW(), INTERVAL 24 HOUR)),
(3, NULL, 'ai_classified', 'AI classified request as Billing Issue - Overcharge', 'ai_processing', 'assigned', DATE_SUB(NOW(), INTERVAL 24 HOUR)),
(3, NULL, 'priority_set', 'Priority set to Normal based on AI analysis', NULL, 'normal', DATE_SUB(NOW(), INTERVAL 24 HOUR)),
(3, NULL, 'assigned', 'Request assigned to Finance department', NULL, 'assigned', DATE_SUB(NOW(), INTERVAL 24 HOUR)),
(3, NULL, 'task_created', 'Task created: Review billing records', 'assigned', 'assigned', DATE_SUB(NOW(), INTERVAL 24 HOUR)),
(3, NULL, 'approval_required', 'Approval required for potential refund', 'assigned', 'awaiting_approval', DATE_SUB(NOW(), INTERVAL 12 HOUR)),
(3, NULL, 'status_changed', 'Request status changed to Awaiting Approval', 'assigned', 'awaiting_approval', DATE_SUB(NOW(), INTERVAL 12 HOUR)),
(3, NULL, 'customer_response', 'Customer contacted for additional information', 'awaiting_approval', 'pending_customer', DATE_SUB(NOW(), INTERVAL 6 HOUR)),

-- Logs for Request 4 (FC-000004)
(4, NULL, 'request_created', 'Customer submitted new request via web form', NULL, 'new', DATE_SUB(NOW(), INTERVAL 12 HOUR)),
(4, NULL, 'ai_processing', 'AI processing initiated', 'new', 'ai_processing', DATE_SUB(NOW(), INTERVAL 12 HOUR)),
(4, NULL, 'ai_classified', 'AI classified request as Document Review - Contract Review', 'ai_processing', 'assigned', DATE_SUB(NOW(), INTERVAL 12 HOUR)),
(4, NULL, 'priority_set', 'Priority set to Normal based on AI analysis', NULL, 'normal', DATE_SUB(NOW(), INTERVAL 12 HOUR)),
(4, NULL, 'assigned', 'Request assigned to Operations department', NULL, 'assigned', DATE_SUB(NOW(), INTERVAL 12 HOUR)),
(4, NULL, 'task_created', 'Task created: Review contract terms', 'assigned', 'assigned', DATE_SUB(NOW(), INTERVAL 12 HOUR)),
(4, 12, 'task_started', 'Staff started working on task', 'assigned', 'in_progress', DATE_SUB(NOW(), INTERVAL 8 HOUR)),
(4, NULL, 'status_changed', 'Request status changed to In Progress', 'assigned', 'in_progress', DATE_SUB(NOW(), INTERVAL 8 HOUR)),

-- Logs for Request 5 (FC-000005) - Completed
(5, NULL, 'request_created', 'Customer submitted new request via web form', NULL, 'new', DATE_SUB(NOW(), INTERVAL 48 HOUR)),
(5, NULL, 'ai_processing', 'AI processing initiated', 'new', 'ai_processing', DATE_SUB(NOW(), INTERVAL 48 HOUR)),
(5, NULL, 'ai_classified', 'AI classified request as Service Request - Account Setup', 'ai_processing', 'assigned', DATE_SUB(NOW(), INTERVAL 48 HOUR)),
(5, NULL, 'priority_set', 'Priority set to Low based on AI analysis', NULL, 'low', DATE_SUB(NOW(), INTERVAL 48 HOUR)),
(5, NULL, 'assigned', 'Request assigned to Customer Support department', NULL, 'assigned', DATE_SUB(NOW(), INTERVAL 48 HOUR)),
(5, NULL, 'task_created', 'Tasks created for account setup', 'assigned', 'assigned', DATE_SUB(NOW(), INTERVAL 48 HOUR)),
(5, 6, 'task_started', 'Staff started working on tasks', 'assigned', 'in_progress', DATE_SUB(NOW(), INTERVAL 46 HOUR)),
(5, 6, 'task_completed', 'All tasks completed', 'in_progress', 'in_progress', DATE_SUB(NOW(), INTERVAL 24 HOUR)),
(5, 6, 'request_resolved', 'Request marked as resolved', 'in_progress', 'resolved', DATE_SUB(NOW(), INTERVAL 24 HOUR)),
(5, NULL, 'customer_notified', 'Customer notified of resolution via email', 'resolved', 'resolved', DATE_SUB(NOW(), INTERVAL 24 HOUR)),

-- Logs for Request 6 (FC-000006) - New
(6, NULL, 'request_created', 'Customer submitted new request via web form', NULL, 'new', DATE_SUB(NOW(), INTERVAL 2 HOUR)),
(6, NULL, 'ai_processing', 'AI processing initiated', 'new', 'ai_processing', DATE_SUB(NOW(), INTERVAL 2 HOUR)),
(6, NULL, 'ai_classified', 'AI classified request as General Inquiry - Product Feature', 'ai_processing', 'new', DATE_SUB(NOW(), INTERVAL 2 HOUR)),
(6, NULL, 'ai_review_required', 'AI recommends manual review - General Inquiry requires customer support routing', 'ai_processing', 'ai_review_required', DATE_SUB(NOW(), INTERVAL 2 HOUR));

-- =====================================================
-- INSERT ESCALATIONS
-- =====================================================
INSERT INTO `escalations` (`request_id`, `escalated_by`, `escalated_to`, `reason`, `description`, `status`) VALUES
(1, NULL, 2, 'SLA Deadline Exceeded', 'Request FC-000001 has exceeded its SLA deadline by 2 days. Customer is frustrated and has sent multiple follow-ups.', 'active');

