-- =========================================
-- DATABASE COMPONENT: CORE APPROVAL TABLES
-- Foundational tables for approval workflow
-- =========================================

-- 1. APPROVAL QUEUE - Current pending approvals
CREATE TABLE 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,
    
    -- Foreign key constraints
    FOREIGN KEY (request_id) REFERENCES procurement_requests(request_id) ON DELETE CASCADE,
    FOREIGN KEY (approver_id) REFERENCES users(user_id),
    FOREIGN KEY (escalated_to) REFERENCES users(user_id),
    
    -- Performance indexes
    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),
    
    -- Ensure one pending approval per request-approver combination
    UNIQUE KEY unique_pending_approval (request_id, approver_id, status)
);

-- 2. APPROVAL ACTIONS - Complete history of approval decisions
CREATE TABLE 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, -- Store attachment file paths
    approval_conditions TEXT, -- Any conditions attached to approval
    next_approver_id INT NULL,
    processing_time_minutes INT, -- Time taken to make decision
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    ip_address VARCHAR(45),
    user_agent TEXT,
    
    -- Foreign key constraints
    FOREIGN KEY (request_id) REFERENCES procurement_requests(request_id) ON DELETE CASCADE,
    FOREIGN KEY (approver_id) REFERENCES users(user_id),
    FOREIGN KEY (next_approver_id) REFERENCES users(user_id),
    
    -- Performance indexes
    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. APPROVAL DELEGATIONS - Delegation management
CREATE TABLE 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, -- Limit delegation amount
    department_restriction INT NULL, -- Restrict to specific department
    reason TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Foreign key constraints
    FOREIGN KEY (delegator_id) REFERENCES users(user_id),
    FOREIGN KEY (delegate_id) REFERENCES users(user_id),
    FOREIGN KEY (department_restriction) REFERENCES departments(department_id),
    
    -- Performance indexes
    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)
);

-- =========================================
-- STORED PROCEDURES FOR CORE OPERATIONS
-- =========================================

DELIMITER //

-- Procedure to add approval to queue
CREATE PROCEDURE 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 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 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,
        d.department_name,
        bc.category_name as budget_category,
        v.vendor_name,
        req_user.full_name as requester_name,
        req_user.email as requester_email,
        COUNT(ri.item_id) 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
    LEFT JOIN request_items ri ON pr.request_id = ri.request_id
    WHERE aq.status = 'pending' 
    AND aq.approver_id = p_user_id
    GROUP BY aq.queue_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//

-- Procedure to check if user can approve request
CREATE PROCEDURE CanUserApprove(
    IN p_user_id INT,
    IN p_request_id INT
)
BEGIN
    SELECT 
        CASE 
            WHEN COUNT(*) > 0 THEN TRUE 
            ELSE FALSE 
        END as can_approve
    FROM approval_queue aq
    WHERE aq.request_id = p_request_id 
    AND aq.approver_id = p_user_id 
    AND aq.status = 'pending';
END//

-- Procedure to get approval statistics
CREATE PROCEDURE GetApprovalStatistics(
    IN p_user_id INT,
    IN p_period_days INT
)
BEGIN
    DECLARE v_date_from DATE;
    SET v_date_from = DATE_SUB(CURRENT_DATE, INTERVAL p_period_days DAY);
    
    SELECT 
        COUNT(CASE WHEN action = 'approved' THEN 1 END) as approved_count,
        COUNT(CASE WHEN action = 'rejected' THEN 1 END) as rejected_count,
        COUNT(CASE WHEN action = 'returned' THEN 1 END) as returned_count,
        COUNT(*) as total_actions,
        AVG(processing_time_minutes) as avg_processing_time,
        SUM(CASE WHEN action = 'approved' THEN 
            (SELECT estimated_cost FROM procurement_requests WHERE request_id = aa.request_id)
            ELSE 0 END) as total_value_approved
    FROM approval_actions aa
    WHERE aa.approver_id = p_user_id 
    AND DATE(aa.created_at) >= v_date_from;
    
    -- Also get pending and overdue counts
    SELECT 
        COUNT(*) as pending_count,
        COUNT(CASE WHEN due_date < NOW() THEN 1 END) as overdue_count
    FROM approval_queue 
    WHERE approver_id = p_user_id 
    AND status = 'pending';
END//

DELIMITER ;

-- =========================================
-- TRIGGERS FOR AUTOMATION
-- =========================================

-- Trigger to update request status when all approvals complete
DELIMITER //
CREATE TRIGGER tr_approval_queue_complete
    AFTER UPDATE ON approval_queue
    FOR EACH ROW
BEGIN
    IF NEW.status = 'completed' AND OLD.status = 'pending' THEN
        -- Check if this was the final approval step
        IF NOT EXISTS (
            SELECT 1 FROM approval_queue 
            WHERE request_id = NEW.request_id 
            AND status = 'pending'
        ) THEN
            -- All approvals complete - check if any were rejected
            IF EXISTS (
                SELECT 1 FROM approval_actions 
                WHERE request_id = NEW.request_id 
                AND action = 'rejected'
            ) THEN
                UPDATE procurement_requests 
                SET status = 'rejected', rejected_at = NOW()
                WHERE request_id = NEW.request_id;
            ELSE
                UPDATE procurement_requests 
                SET status = 'approved', approved_at = NOW()
                WHERE request_id = NEW.request_id;
            END IF;
        END IF;
    END IF;
END//
DELIMITER ;

-- =========================================
-- SAMPLE DATA FOR TESTING
-- =========================================

-- Insert sample approval queue entries (for testing)
-- Uncomment these lines when you want to test with sample data

/*
INSERT INTO approval_queue (request_id, approver_id, step_number, due_date, priority) VALUES
(1, 2, 1, DATE_ADD(NOW(), INTERVAL 24 HOUR), 'high'),
(2, 2, 1, DATE_ADD(NOW(), INTERVAL 8 HOUR), 'critical'),
(3, 3, 2, DATE_ADD(NOW(), INTERVAL 48 HOUR), 'medium');

INSERT INTO approval_actions (request_id, approver_id, action, step_number, comments, processing_time_minutes) VALUES
(1, 2, 'approved', 1, 'Approved for business necessity', 45),
(2, 2, 'rejected', 1, 'Insufficient justification provided', 30),
(3, 2, 'approved', 1, 'Department budget confirmed', 20);
*/

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

-- Check table structure
-- SELECT TABLE_NAME, TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME LIKE '%approval%';

-- Check indexes
-- SHOW INDEX FROM approval_queue;
-- SHOW INDEX FROM approval_actions;

-- Check stored procedures
-- SHOW PROCEDURE STATUS WHERE Name LIKE '%Approval%';

-- Check triggers
-- SHOW TRIGGERS WHERE `Table` LIKE '%approval%';