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

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- Drop tables if they exist (in reverse dependency order)
DROP TABLE IF EXISTS `ai_processing_logs`;
DROP TABLE IF EXISTS `escalations`;
DROP TABLE IF EXISTS `notifications`;
DROP TABLE IF EXISTS `workflow_logs`;
DROP TABLE IF EXISTS `approvals`;
DROP TABLE IF EXISTS `request_tasks`;
DROP TABLE IF EXISTS `customer_requests`;
DROP TABLE IF EXISTS `users`;
DROP TABLE IF EXISTS `departments`;
DROP TABLE IF EXISTS `settings`;

-- =====================================================
-- 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;

-- =====================================================
-- USERS TABLE
-- =====================================================
CREATE TABLE `users` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `username` VARCHAR(50) NOT NULL UNIQUE,
    `email` VARCHAR(150) NOT NULL UNIQUE,
    `password_hash` VARCHAR(255) NOT NULL,
    `first_name` VARCHAR(100) NOT NULL,
    `last_name` VARCHAR(100) NOT NULL,
    `phone` VARCHAR(20) DEFAULT NULL,
    `role` ENUM('admin', 'manager', 'staff', 'customer') NOT NULL DEFAULT 'customer',
    `department_id` INT UNSIGNED DEFAULT NULL,
    `is_active` TINYINT(1) DEFAULT 1,
    `last_login` DATETIME DEFAULT NULL,
    `login_attempts` INT UNSIGNED DEFAULT 0,
    `locked_until` DATETIME DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_user_email` (`email`),
    INDEX `idx_user_role` (`role`),
    INDEX `idx_user_department` (`department_id`),
    INDEX `idx_user_active` (`is_active`),
    CONSTRAINT `fk_user_department` FOREIGN KEY (`department_id`) 
        REFERENCES `departments` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) 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 USERS
-- =====================================================
INSERT INTO `users` (`username`, `email`, `password_hash`, `first_name`, `last_name`, `phone`, `role`, `department_id`, `is_active`) VALUES
-- Admin user
('admin', 'admin@flowcore.example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'System', 'Administrator', '+1-555-0100', 'admin', NULL, 1),
-- Managers
('john.manager', 'john.manager@flowcore.example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'John', 'Mitchell', '+1-555-0201', 'manager', 1, 1),
('sarah.manager', 'sarah.manager@flowcore.example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Sarah', 'Thompson', '+1-555-0202', 'manager', 2, 1),
('robert.manager', 'robert.manager@flowcore.example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Robert', 'Chen', '+1-555-0203', 'manager', 3, 1),
-- Customer Support Staff
('emily.staff', 'emily.staff@flowcore.example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Emily', 'Davis', '+1-555-0301', 'staff', 1, 1),
('michael.staff', 'michael.staff@flowcore.example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Michael', 'Brown', '+1-555-0302', 'staff', 1, 1),
('lisa.staff', 'lisa.staff@flowcore.example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Lisa', 'Wilson', '+1-555-0303', 'staff', 1, 1),
-- Technical Support Staff
('david.tech', 'david.tech@flowcore.example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'David', 'Martinez', '+1-555-0304', 'staff', 2, 1),
('jennifer.tech', 'jennifer.tech@flowcore.example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Jennifer', 'Anderson', '+1-555-0305', 'staff', 2, 1),
-- Finance Staff
('william.finance', 'william.finance@flowcore.example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'William', 'Taylor', '+1-555-0306', 'staff', 3, 1),
('amanda.finance', 'amanda.finance@flowcore.example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Amanda', 'Garcia', '+1-555-0307', 'staff', 3, 1),
-- Operations Staff
('christopher.ops', 'christopher.ops@flowcore.example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Christopher', 'Miller', '+1-555-0308', 'staff', 4, 1),
-- Sample Customers
('customer1', 'maria.santos@email.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Maria', 'Santos', '+1-555-1001', 'customer', NULL, 1),
('customer2', 'james.wilson@email.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'James', 'Wilson', '+1-555-1002', 'customer', NULL, 1),
('customer3', 'patricia.lee@email.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Patricia', 'Lee', '+1-555-1003', 'customer', NULL, 1);

-- =====================================================
-- INSERT SAMPLE CUSTOMER REQUESTS
-- =====================================================
INSERT INTO `customer_requests` (`request_number`, `customer_id`, `customer_name`, `customer_email`, `customer_phone`, `subject`, `description`, `category`, `subcategory`, `priority`, `status`, `department_id`, `assigned_to`, `ai_summary`, `ai_classification`, `ai_required_action`, `ai_requires_approval`, `sla_deadline`, `source`) VALUES
-- Request 1: Customer Complaint (Overdue - for demo)
('FC-000001', 12, 'Maria Santos', 'maria.santos@email.com', '+1-555-1001', 
'Service request not completed for five days',
'A client has complained that their service request has not been completed for five days. This is causing significant inconvenience and they are very frustrated with our service. They have already sent two follow-up emails with no response.',
'Customer Complaint', 'Delayed Service', 'high', 'in_progress', 1, 5,
'Customer reports a service request has remained unresolved for five days despite follow-up emails.',
'Customer Complaint - Delayed Service',
'Investigate the delayed service request immediately. Contact the customer with an update and expedite resolution.',
0, DATE_ADD(NOW(), INTERVAL -2 DAY), 'web_form'),

-- Request 2: Technical Issue
('FC-000002', 13, 'James Wilson', 'james.wilson@email.com', '+1-555-1002',
'Unable to access my account',
'I have been trying to log into my account for the past two hours but keep getting an error message saying "Invalid credentials". I am certain my password is correct as I use a password manager. This is urgent as I need to access important documents for a meeting today.',
'Technical Issue', 'Login Problem', 'high', 'assigned', 2, 8,
'Customer unable to access account due to potential authentication issue.',
'Technical Issue - Login Problem',
'Verify account status, check for lockouts or password issues, assist with account recovery if needed.',
0, DATE_ADD(NOW(), INTERVAL 20 HOUR), 'web_form'),

-- Request 3: Billing Issue
('FC-000003', 14, 'Patricia Lee', 'patricia.lee@email.com', '+1-555-1003',
'Incorrect charge on my invoice',
'I was charged twice for the same service on my last invoice. Invoice #INV-2024-0892 shows $450 for "Premium Support Package" but I only have the basic package at $150. Please review and issue a refund for the overcharge.',
'Billing Issue', 'Overcharge', 'normal', 'pending_customer', 3, 10,
'Customer billed incorrectly - charged for premium package but has basic subscription.',
'Billing Issue - Overcharge',
'Review invoice records, verify subscription level, process refund if overcharge confirmed. Manager approval may be required for refunds over threshold.',
1, DATE_ADD(NOW(), INTERVAL 40 HOUR), 'web_form'),

-- Request 4: Document Review
('FC-000004', 12, 'Maria Santos', 'maria.santos@email.com', '+1-555-1001',
'Contract review requested',
'Please review the attached service agreement before I sign. I want to ensure all terms are favorable and there are no hidden clauses. The signed version needs to be returned by end of week.',
'Document Review', 'Contract Review', 'normal', 'in_progress', 4, 12,
'Customer requesting legal review of service agreement contract.',
'Document Review - Contract Review',
'Route to legal/operations for contract review. Prepare summary of key terms and any recommended changes.',
0, DATE_ADD(NOW(), INTERVAL 48 HOUR), 'web_form'),

-- Request 5: Service Request (Completed)
('FC-000005', 13, 'James Wilson', 'james.wilson@email.com', '+1-555-1002',
'New user account setup',
'Please set up a new user account for our new employee John Smith. They will need access to the project management module and the reporting dashboard.',
'Service Request', 'Account Setup', 'low', 'resolved', 1, 6,
'Request to create new user account for employee John Smith with specific module access.',
'Service Request - Account Setup',
'Create user account in system, assign appropriate permissions for project management and reporting modules.',
0, DATE_ADD(NOW(), INTERVAL -24 HOUR), 'web_form'),

-- Request 6: General Inquiry
('FC-000006', 14, 'Patricia Lee', 'patricia.lee@email.com', '+1-555-1003',
'Product feature question',
'Does your platform support integration with QuickBooks? We are considering migrating our accounting to QuickBooks and want to ensure seamless data synchronization.',
'General Inquiry', 'Product Feature', 'low', 'new', 1, NULL,
'Customer inquiring about QuickBooks integration capabilities.',
'General Inquiry - Product Feature',
'Provide information about QuickBooks integration. If available, direct to documentation. If not available, log as feature request.',
0, DATE_ADD(NOW(), INTERVAL 72 HOUR), 'web_form');

-- =====================================================
-- INSERT TASKS FOR EXISTING REQUESTS
-- =====================================================
INSERT INTO `request_tasks` (`request_id`, `task_title`, `task_description`, `assigned_to`, `status`, `priority`, `due_date`) VALUES
-- Tasks for Request 1 (FC-000001)
(1, 'Investigate delayed service request', 'Contact the original handler of the service request and determine why it has not been completed. Review all related communications.', 5, 'in_progress', 'high', DATE_ADD(NOW(), INTERVAL -2 DAY)),
(1, 'Contact customer with update', 'Reach out to Maria Santos immediately with a status update and expected resolution timeline.', 5, 'pending', 'high', DATE_ADD(NOW(), INTERVAL 4 HOUR)),

-- Tasks for Request 2 (FC-000002)
(2, 'Verify account status', 'Check if the account exists, is active, and investigate the login issue.', 8, 'in_progress', 'high', DATE_ADD(NOW(), INTERVAL 12 HOUR)),

-- Tasks for Request 3 (FC-000003)
(3, 'Review billing records', 'Verify subscription level and confirm if overcharge occurred.', 10, 'pending', 'normal', DATE_ADD(NOW(), INTERVAL 24 HOUR)),
(3, 'Process refund if applicable', 'If overcharge confirmed, prepare refund for manager approval.', 10, 'pending', 'normal', DATE_ADD(NOW(), INTERVAL 36 HOUR)),

-- Tasks for Request 4 (FC-000004)
(4, 'Review contract terms', 'Read through the attached service agreement and prepare a summary.', 12, 'in_progress', 'normal', DATE_ADD(NOW(), INTERVAL 36 HOUR)),

-- Tasks for Request 5 (FC-000005)
(5, 'Create user account for John Smith', 'Set up new user account with appropriate permissions.', 6, 'completed', 'low', DATE_ADD(NOW(), INTERVAL -30 HOUR)),
(5, 'Configure module access', 'Enable project management and reporting dashboard access.', 6, 'completed', 'low', DATE_ADD(NOW(), INTERVAL -28 HOUR));

-- =====================================================
-- 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');

SET FOREIGN_KEY_CHECKS = 1;