-- =========================================
-- FIXED APPROVAL WORKFLOW DATABASE SCHEMA
-- Version without foreign key dependencies
-- =========================================

-- First, let's create the missing foundation tables if they don't exist

-- Create departments table if it doesn't exist
CREATE TABLE IF NOT EXISTS departments (
    department_id INT PRIMARY KEY AUTO_INCREMENT,
    department_name VARCHAR(100) NOT NULL UNIQUE,
    department_code VARCHAR(20),
    manager_id INT NULL,
    budget_limit DECIMAL(15,2) DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Create budget_categories table if it doesn't exist
CREATE TABLE IF NOT EXISTS budget_categories (
    category_id INT PRIMARY KEY AUTO_INCREMENT,
    category_name VARCHAR(100) NOT NULL UNIQUE,
    category_code VARCHAR(20),
    annual_budget DECIMAL(15,2) DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create users table if it doesn't exist (basic version)
CREATE TABLE IF NOT EXISTS users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE,
    email VARCHAR(100) UNIQUE,
    full_name VARCHAR(100) NOT NULL,
    role ENUM('user', 'department_head', 'finance_manager', 'procurement_manager', 'managing_director', 'admin') DEFAULT 'user',
    department_id INT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Create procurement_requests table if it doesn't exist (basic version)
CREATE TABLE IF NOT EXISTS procurement_requests (
    request_id INT PRIMARY KEY AUTO_INCREMENT,
    request_number VARCHAR(50) UNIQUE,
    user_id INT,
    department_id INT,
    budget_category_id INT,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    justification TEXT,
    urgency_level ENUM('low', 'medium', 'high', 'critical') DEFAULT 'medium',
    estimated_cost DECIMAL(15,2) NOT NULL,
    preferred_vendor_id INT NULL,
    delivery_location VARCHAR(255),
    expected_delivery_date DATE,
    special_instructions TEXT,
    status ENUM('draft', 'submitted', 'under_review', 'approved', 'rejected', 'returned') DEFAULT 'draft',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    approved_at TIMESTAMP NULL,
    rejected_at TIMESTAMP NULL
);

-- Create vendors_main table if it doesn't exist (basic version)
CREATE TABLE IF NOT EXISTS vendors_main (
    vendor_id INT PRIMARY KEY AUTO_INCREMENT,
    vendor_name VARCHAR(255) NOT NULL,
    contact_email VARCHAR(100),
    contact_phone VARCHAR(20),
    address TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- =========================================
-- NOW CREATE THE APPROVAL WORKFLOW TABLES
-- =========================================

-- 1. APPROVAL QUEUE - Current pending approvals
CREATE TABLE IF NOT EXISTS approval_queue (
    queue_id INT PRIMARY KEY AUTO_INCREMENT,
    request_id INT NOT NULL,
    approver_id INT NOT NULL,
    step_number INT DEFAULT 1,
    assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    due_date TIMESTAMP NULL,
    escalated BOOLEAN DEFAULT FALSE,
    escalation_level INT DEFAULT 0,
    priority ENUM('low', 'medium', 'high', 'critical') DEFAULT 'medium',
    status ENUM('pending', 'completed', 'escalated', 'delegated') DEFAULT 'pending',
    completed_at TIMESTAMP NULL,
    escalated_at TIMESTAMP NULL,
    escalated_to INT NULL,
    comments TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Indexes for performance
    INDEX idx_approver_status (approver_id, status),
    INDEX idx_request_status (request_id, status),
    INDEX idx_due_date (due_date, status),
    INDEX idx_priority_status (priority, status),
    INDEX idx_escalated (escalated, escalated_at)
);

-- 2. APPROVAL ACTIONS - Complete history of approval decisions
CREATE TABLE IF NOT EXISTS approval_actions (
    action_id INT PRIMARY KEY AUTO_INCREMENT,
    request_id INT NOT NULL,
    approver_id INT NOT NULL,
    action ENUM('approved', 'rejected', 'returned', 'delegated', 'escalated') NOT NULL,
    step_number INT DEFAULT 1,
    comments TEXT,
    attachments JSON,
    approval_conditions TEXT,
    next_approver_id INT NULL,
    processing_time_minutes INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    ip_address VARCHAR(45),
    user_agent TEXT,
    
    -- Indexes for performance
    INDEX idx_request_action (request_id, created_at),
    INDEX idx_approver_action (approver_id, created_at),
    INDEX idx_action_type (action, created_at),
    INDEX idx_step_number (step_number, action)
);

-- 3. WORKFLOW RULES - Configurable approval workflows
CREATE TABLE IF NOT EXISTS workflow_rules (
    rule_id INT PRIMARY KEY AUTO_INCREMENT,
    rule_name VARCHAR(100) NOT NULL,
    description TEXT,
    min_amount DECIMAL(15,2) DEFAULT 0,
    max_amount DECIMAL(15,2) NULL,
    department_id INT NULL,
    category_id INT NULL,
    urgency_level ENUM('low', 'medium', 'high', 'critical') NULL,
    approval_steps JSON NOT NULL,
    escalation_hours INT DEFAULT 24,
    escalation_chain JSON,
    parallel_approval BOOLEAN DEFAULT FALSE,
    requires_unanimous BOOLEAN DEFAULT FALSE,
    auto_approve_threshold DECIMAL(15,2) NULL,
    working_hours_only BOOLEAN DEFAULT TRUE,
    is_active BOOLEAN DEFAULT TRUE,
    effective_from DATE DEFAULT (CURRENT_DATE),
    effective_to DATE NULL,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_amount_range (min_amount, max_amount),
    INDEX idx_active_rules (is_active, effective_from, effective_to),
    INDEX idx_department_rules (department_id, is_active)
);

-- 4. USER NOTIFICATION PREFERENCES
CREATE TABLE IF NOT EXISTS user_notification_preferences (
    user_id INT PRIMARY KEY,
    email_enabled BOOLEAN DEFAULT TRUE,
    sms_enabled BOOLEAN DEFAULT FALSE,
    push_enabled BOOLEAN DEFAULT TRUE,
    digest_frequency ENUM('never', 'daily', 'weekly') DEFAULT 'daily',
    digest_time TIME DEFAULT '09:00:00',
    urgent_only BOOLEAN DEFAULT FALSE,
    reminder_hours INT DEFAULT 12,
    escalation_notifications BOOLEAN DEFAULT TRUE,
    approval_summary BOOLEAN DEFAULT TRUE,
    mobile_number VARCHAR(20),
    timezone VARCHAR(50) DEFAULT 'Africa/Lagos',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 5. APPROVAL DELEGATIONS - Delegation management
CREATE TABLE IF NOT EXISTS approval_delegations (
    delegation_id INT PRIMARY KEY AUTO_INCREMENT,
    delegator_id INT NOT NULL,
    delegate_id INT NOT NULL,
    delegation_type ENUM('temporary', 'permanent', 'emergency') DEFAULT 'temporary',
    start_date DATE NOT NULL,
    end_date DATE NULL,
    max_amount DECIMAL(15,2) NULL,
    department_restriction INT NULL,
    reason TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_delegator_active (delegator_id, is_active, start_date, end_date),
    INDEX idx_delegate_active (delegate_id, is_active, start_date, end_date),
    INDEX idx_active_delegations (is_active, start_date, end_date)
);

-- 6. APPROVAL NOTIFICATIONS - Notification delivery tracking
CREATE TABLE IF NOT EXISTS approval_notifications (
    notification_id VARCHAR(100) PRIMARY KEY,
    request_id INT NOT NULL,
    user_id INT NOT NULL,
    notification_type ENUM('email', 'sms', 'push', 'in_app', 'digest') NOT NULL,
    event_type VARCHAR(50) NOT NULL,
    subject VARCHAR(255),
    message TEXT,
    template_used VARCHAR(100),
    sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    delivery_status ENUM('sent', 'failed', 'pending', 'bounced') DEFAULT 'pending',
    delivery_channel VARCHAR(20),
    delivery_attempts INT DEFAULT 0,
    last_attempt_at TIMESTAMP NULL,
    opened_at TIMESTAMP NULL,
    clicked_at TIMESTAMP NULL,
    error_message TEXT,
    message_id VARCHAR(255),
    
    INDEX idx_user_notifications (user_id, sent_at),
    INDEX idx_request_notifications (request_id, sent_at),
    INDEX idx_delivery_status (delivery_status, last_attempt_at)
);

-- 7. APPROVAL TEMPLATES - Reusable approval templates
CREATE TABLE IF NOT EXISTS approval_templates (
    template_id INT PRIMARY KEY AUTO_INCREMENT,
    template_name VARCHAR(100) NOT NULL,
    template_type ENUM('request', 'approval', 'rejection', 'escalation') NOT NULL,
    subject_template VARCHAR(255),
    body_template TEXT,
    variables JSON,
    is_default BOOLEAN DEFAULT FALSE,
    is_active BOOLEAN DEFAULT TRUE,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_template_type (template_type, is_active)
);

-- 8. APPROVAL ANALYTICS - Performance metrics storage
CREATE TABLE IF NOT EXISTS approval_analytics (
    analytics_id INT PRIMARY KEY AUTO_INCREMENT,
    date_recorded DATE NOT NULL,
    approver_id INT,
    department_id INT,
    total_approvals INT DEFAULT 0,
    total_rejections INT DEFAULT 0,
    total_escalations INT DEFAULT 0,
    avg_approval_time_hours DECIMAL(8,2),
    overdue_approvals INT DEFAULT 0,
    auto_approvals INT DEFAULT 0,
    value_approved DECIMAL(15,2) DEFAULT 0,
    value_rejected DECIMAL(15,2) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    UNIQUE KEY unique_daily_record (date_recorded, approver_id, department_id),
    INDEX idx_date_approver (date_recorded, approver_id),
    INDEX idx_date_department (date_recorded, department_id)
);

-- 9. WORKFLOW INSTANCES - Track workflow execution
CREATE TABLE IF NOT EXISTS workflow_instances (
    instance_id INT PRIMARY KEY AUTO_INCREMENT,
    request_id INT NOT NULL,
    rule_id INT NOT NULL,
    steps_definition JSON,
    parallel_approval BOOLEAN DEFAULT FALSE,
    requires_unanimous BOOLEAN DEFAULT FALSE,
    status ENUM('active', 'completed', 'cancelled') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    completed_at TIMESTAMP NULL,
    
    INDEX idx_request_instance (request_id, status),
    INDEX idx_rule_instances (rule_id, status)
);

-- 10. IN-APP NOTIFICATIONS - For real-time notifications
CREATE TABLE IF NOT EXISTS in_app_notifications (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    notification_id VARCHAR(100),
    type VARCHAR(50),
    title VARCHAR(255),
    message TEXT,
    data JSON,
    priority ENUM('low', 'normal', 'high') DEFAULT 'normal',
    is_read BOOLEAN DEFAULT FALSE,
    read_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_user_unread (user_id, is_read, created_at),
    INDEX idx_user_notifications (user_id, created_at)
);

-- =========================================
-- INSERT SAMPLE DATA
-- =========================================

-- Insert sample departments if none exist
INSERT IGNORE INTO departments (department_name, department_code) VALUES
('Information Technology', 'IT'),
('Human Resources', 'HR'),
('Finance', 'FIN'),
('Operations', 'OPS'),
('Administration', 'ADMIN'),
('Sales & Marketing', 'SALES');

-- Insert sample budget categories if none exist
INSERT IGNORE INTO budget_categories (category_name, category_code, annual_budget) VALUES
('IT Equipment', 'IT_EQUIP', 500000),
('Office Supplies', 'OFFICE', 100000),
('Software & Licenses', 'SOFTWARE', 200000),
('Furniture & Fixtures', 'FURNITURE', 150000),
('Travel & Transport', 'TRAVEL', 80000),
('Training & Development', 'TRAINING', 120000),
('Maintenance & Repairs', 'MAINTENANCE', 100000);

-- Insert sample users if none exist
INSERT IGNORE INTO users (username, email, full_name, role, department_id) VALUES
('admin', 'admin@company.com', 'System Administrator', 'admin', 1),
('john.doe', 'john.doe@company.com', 'John Doe', 'department_head', 1),
('jane.smith', 'jane.smith@company.com', 'Jane Smith', 'finance_manager', 3),
('mike.johnson', 'mike.johnson@company.com', 'Mike Johnson', 'procurement_manager', 4),
('sarah.wilson', 'sarah.wilson@company.com', 'Sarah Wilson', 'managing_director', NULL);

-- Insert default workflow rules
INSERT IGNORE INTO workflow_rules (rule_name, description, min_amount, max_amount, approval_steps, escalation_hours) VALUES
('Micro Purchases', 'Small office supplies and minor expenses', 0, 10000, 
 JSON_ARRAY('department_head'), 8),
('Small Purchases', 'Standard departmental purchases', 10001, 50000, 
 JSON_ARRAY('department_head'), 24),
('Medium Purchases', 'Significant purchases requiring financial review', 50001, 200000, 
 JSON_ARRAY('department_head', 'finance_manager'), 24),
('Large Purchases', 'Major purchases requiring procurement review', 200001, 500000, 
 JSON_ARRAY('department_head', 'finance_manager', 'procurement_manager'), 24),
('Major Purchases', 'High-value purchases requiring executive approval', 500001, 2000000, 
 JSON_ARRAY('department_head', 'finance_manager', 'procurement_manager', 'managing_director'), 48),
('Capital Expenditure', 'Major capital investments', 2000001, NULL, 
 JSON_ARRAY('department_head', 'finance_manager', 'procurement_manager', 'managing_director'), 72);

-- Insert default notification preferences for sample users
INSERT IGNORE INTO user_notification_preferences (user_id, email_enabled, digest_frequency, reminder_hours)
SELECT user_id, TRUE, 'daily', 12 
FROM users 
WHERE is_active = TRUE;

-- =========================================
-- CREATE STORED PROCEDURES (WITHOUT FK CONSTRAINTS)
-- =========================================

DELIMITER //

-- Procedure to add approval to queue
CREATE PROCEDURE IF NOT EXISTS AddToApprovalQueue(
    IN p_request_id INT,
    IN p_approver_id INT,
    IN p_step_number INT,
    IN p_due_date TIMESTAMP,
    IN p_priority VARCHAR(20)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;
    
    START TRANSACTION;
    
    -- Insert into approval queue
    INSERT INTO approval_queue (
        request_id, approver_id, step_number, 
        assigned_at, due_date, priority, status
    ) VALUES (
        p_request_id, p_approver_id, p_step_number,
        NOW(), p_due_date, p_priority, 'pending'
    );
    
    -- Update request status if it's the first approval
    IF p_step_number = 1 THEN
        UPDATE procurement_requests 
        SET status = 'under_review', updated_at = NOW()
        WHERE request_id = p_request_id;
    END IF;
    
    COMMIT;
    
    -- Return the queue ID
    SELECT LAST_INSERT_ID() as queue_id;
END//

-- Procedure to record approval action
CREATE PROCEDURE IF NOT EXISTS RecordApprovalAction(
    IN p_request_id INT,
    IN p_approver_id INT,
    IN p_action VARCHAR(20),
    IN p_step_number INT,
    IN p_comments TEXT,
    IN p_processing_time INT,
    IN p_next_approver_id INT,
    IN p_ip_address VARCHAR(45),
    IN p_user_agent TEXT
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;
    
    START TRANSACTION;
    
    -- Insert approval action
    INSERT INTO approval_actions (
        request_id, approver_id, action, step_number, comments,
        next_approver_id, processing_time_minutes, 
        ip_address, user_agent, created_at
    ) VALUES (
        p_request_id, p_approver_id, p_action, p_step_number, p_comments,
        p_next_approver_id, p_processing_time,
        p_ip_address, p_user_agent, NOW()
    );
    
    -- Update queue status
    UPDATE approval_queue 
    SET status = 'completed', completed_at = NOW()
    WHERE request_id = p_request_id 
    AND approver_id = p_approver_id 
    AND status = 'pending';
    
    COMMIT;
    
    -- Return the action ID
    SELECT LAST_INSERT_ID() as action_id;
END//

-- Procedure to get pending approvals for user
CREATE PROCEDURE IF NOT EXISTS GetPendingApprovals(
    IN p_user_id INT,
    IN p_limit INT,
    IN p_offset INT
)
BEGIN
    SELECT 
        aq.*,
        pr.request_number,
        pr.title,
        pr.description,
        pr.estimated_cost,
        pr.urgency_level,
        pr.created_at as request_created_at,
        pr.justification,
        COALESCE(d.department_name, 'Unknown') as department_name,
        COALESCE(bc.category_name, 'General') as budget_category,
        COALESCE(v.vendor_name, 'TBD') as vendor_name,
        COALESCE(req_user.full_name, 'Unknown User') as requester_name,
        COALESCE(req_user.email, '') as requester_email,
        1 as item_count,
        TIMESTAMPDIFF(HOUR, aq.assigned_at, NOW()) as hours_pending,
        CASE 
            WHEN aq.due_date < NOW() THEN 'overdue'
            WHEN TIMESTAMPDIFF(HOUR, NOW(), aq.due_date) <= 2 THEN 'due_soon'
            ELSE 'on_time'
        END as due_status
    FROM approval_queue aq
    JOIN procurement_requests pr ON aq.request_id = pr.request_id
    LEFT JOIN departments d ON pr.department_id = d.department_id
    LEFT JOIN budget_categories bc ON pr.budget_category_id = bc.category_id
    LEFT JOIN vendors_main v ON pr.preferred_vendor_id = v.vendor_id
    LEFT JOIN users req_user ON pr.user_id = req_user.user_id
    WHERE aq.status = 'pending' 
    AND aq.approver_id = p_user_id
    ORDER BY 
        CASE aq.priority 
            WHEN 'critical' THEN 1
            WHEN 'high' THEN 2
            WHEN 'medium' THEN 3
            ELSE 4
        END,
        aq.due_date ASC
    LIMIT p_limit OFFSET p_offset;
END//

DELIMITER ;

-- =========================================
-- VERIFICATION QUERIES
-- =========================================

-- Show created tables
SELECT 'Tables created successfully' as status;
SELECT TABLE_NAME, TABLE_COMMENT 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = DATABASE() 
AND TABLE_NAME LIKE '%approval%' 
OR TABLE_NAME IN ('departments', 'budget_categories', 'users', 'procurement_requests');

-- Show sample data counts
SELECT 'departments' as table_name, COUNT(*) as record_count FROM departments
UNION ALL
SELECT 'budget_categories', COUNT(*) FROM budget_categories
UNION ALL
SELECT 'users', COUNT(*) FROM users
UNION ALL
SELECT 'workflow_rules', COUNT(*) FROM workflow_rules
UNION ALL
SELECT 'approval_queue', COUNT(*) FROM approval_queue;