<?php
session_start();
include 'layouts/dbconn.php';

// Check if user is logged in
if (!isset($_SESSION['user_id'])) {
    header('Location: login.php');
    exit();
}

// Get user details for permission checks
$user_id = $_SESSION['user_id'];
$sql_user_details = "SELECT username, user_role, full_name, email, role_id FROM users WHERE user_id = $user_id";
$result_user_details = $conn->query($sql_user_details);

if ($result_user_details && $row_user_details = $result_user_details->fetch_assoc()) {
    $username = $row_user_details['username'];
    $user_role = $row_user_details['user_role'];
    $full_name = $row_user_details['full_name'];
    $email = $row_user_details['email'];
    $role_id = $row_user_details['role_id'];
}

// Function to check if user has admin permissions
function isAdmin($role_id) {
    return $role_id == 1 || $role_id == 2; // Admin or General Manager
}

// Function to check if user can approve requests
function canApprove($role_id) {
    // Admins, General Managers, and Finance managers can approve
    return in_array($role_id, [1, 2, 4]);
}

// Function to check if user can release funds
function canRelease($role_id) {
    // Admins, General Managers, and Finance managers can release
    return in_array($role_id, [1, 2, 4]);
}

// Process actions (approve, reject, release)
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['action'])) {
    $action = $_POST['action'];
    $request_id = $_POST['request_id'];
    
    // Start transaction
    $conn->begin_transaction();
    
    try {
        if ($action === 'approve' && canApprove($role_id)) {
            // Approve fund request
            $sql = "UPDATE fund_requests SET 
                    status = 'Approved', 
                    approval_date = NOW(), 
                    approved_by = ? 
                    WHERE request_id = ?";
            $stmt = $conn->prepare($sql);
            $stmt->bind_param("ii", $user_id, $request_id);
            
            if (!$stmt->execute()) {
                throw new Exception("Error approving request: " . $stmt->error);
            }
            
            // Add log entry
            $log_sql = "INSERT INTO fund_request_logs (
                            request_id, action, status_from, status_to, performed_by, remarks
                        ) VALUES (?, 'Approved', 'Pending', 'Approved', ?, 'Fund request approved')";
            $log_stmt = $conn->prepare($log_sql);
            $log_stmt->bind_param("ii", $request_id, $user_id);
            $log_stmt->execute();
            
            $_SESSION['success_message'] = "Fund request approved successfully.";
            
        } elseif ($action === 'reject' && canApprove($role_id)) {
            // Reject fund request
            $rejection_reason = $_POST['rejection_reason'];
            
            $sql = "UPDATE fund_requests SET 
                    status = 'Rejected', 
                    approval_date = NOW(), 
                    approved_by = ?, 
                    rejection_reason = ? 
                    WHERE request_id = ?";
            $stmt = $conn->prepare($sql);
            $stmt->bind_param("isi", $user_id, $rejection_reason, $request_id);
            
            if (!$stmt->execute()) {
                throw new Exception("Error rejecting request: " . $stmt->error);
            }
            
            // Add log entry
            $log_sql = "INSERT INTO fund_request_logs (
                            request_id, action, status_from, status_to, performed_by, remarks
                        ) VALUES (?, 'Rejected', 'Pending', 'Rejected', ?, ?)";
            $log_stmt = $conn->prepare($log_sql);
            $log_stmt->bind_param("iis", $request_id, $user_id, $rejection_reason);
            $log_stmt->execute();
            
            $_SESSION['success_message'] = "Fund request rejected.";
            
        } elseif ($action === 'release' && canRelease($role_id)) {
            // Release funds
            $received_by = $_POST['received_by'];
            
            $sql = "UPDATE fund_requests SET 
                    status = 'Released', 
                    release_date = NOW(), 
                    released_by = ?, 
                    received_by = ? 
                    WHERE request_id = ?";
            $stmt = $conn->prepare($sql);
            $stmt->bind_param("iii", $user_id, $received_by, $request_id);
            
            if (!$stmt->execute()) {
                throw new Exception("Error releasing funds: " . $stmt->error);
            }
            
            // Add log entry
            $log_sql = "INSERT INTO fund_request_logs (
                            request_id, action, status_from, status_to, performed_by, remarks
                        ) VALUES (?, 'Released', 'Approved', 'Released', ?, 'Funds released')";
            $log_stmt = $conn->prepare($log_sql);
            $log_stmt->bind_param("ii", $request_id, $user_id);
            $log_stmt->execute();
            
            $_SESSION['success_message'] = "Funds released successfully.";
        }
        
        // Commit transaction
        $conn->commit();
        
    } catch (Exception $e) {
        // Rollback transaction
        $conn->rollback();
        $_SESSION['error_message'] = "Error: " . $e->getMessage();
    }
    
    // Redirect to prevent form resubmission
    header('Location: fund_request_list.php');
    exit();
}

// Handle filter parameters
$status_filter = isset($_GET['status']) ? $_GET['status'] : 'all';
$date_from = isset($_GET['date_from']) ? $_GET['date_from'] : '';
$date_to = isset($_GET['date_to']) ? $_GET['date_to'] : '';
$search = isset($_GET['search']) ? $_GET['search'] : '';

// Build query with filters
$query = "SELECT fr.*, 
           ec.name AS category_name, 
           ec.type AS category_type,
           u1.full_name AS requester_name,
           u2.full_name AS approver_name,
           u3.full_name AS releaser_name,
           u4.full_name AS receiver_name
          FROM fund_requests fr
          LEFT JOIN expense_categories ec ON fr.category_id = ec.category_id
          LEFT JOIN users u1 ON fr.requested_by = u1.user_id
          LEFT JOIN users u2 ON fr.approved_by = u2.user_id
          LEFT JOIN users u3 ON fr.released_by = u3.user_id
          LEFT JOIN users u4 ON fr.received_by = u4.user_id
          WHERE 1=1";

// Add status filter
if ($status_filter !== 'all') {
    $query .= " AND fr.status = '$status_filter'";
}

// Add date range filter
if (!empty($date_from) && !empty($date_to)) {
    $query .= " AND fr.request_date BETWEEN '$date_from 00:00:00' AND '$date_to 23:59:59'";
} elseif (!empty($date_from)) {
    $query .= " AND fr.request_date >= '$date_from 00:00:00'";
} elseif (!empty($date_to)) {
    $query .= " AND fr.request_date <= '$date_to 23:59:59'";
}

// Add search filter
if (!empty($search)) {
    $query .= " AND (fr.request_number LIKE '%$search%' 
                OR fr.file_number LIKE '%$search%' 
                OR fr.description LIKE '%$search%'
                OR ec.name LIKE '%$search%'
                OR u1.full_name LIKE '%$search%')";
}

// For regular users, only show their own requests unless they are admins or approvers
if (!isAdmin($role_id) && !canApprove($role_id)) {
    $query .= " AND fr.requested_by = $user_id";
}

$query .= " ORDER BY fr.request_date DESC";

$result = $conn->query($query);
$fund_requests = [];

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        $fund_requests[] = $row;
    }
}

// Get all staff for dropdown
$staff_sql = "SELECT user_id, full_name FROM users ORDER BY full_name";
$staff_result = $conn->query($staff_sql);
$staff = [];

if ($staff_result->num_rows > 0) {
    while ($row = $staff_result->fetch_assoc()) {
        $staff[] = $row;
    }
}

// Get system name from settings
$sql_settings = "SELECT setting_value FROM settings WHERE setting_key = 'system_name'";
$result_settings = $conn->query($sql_settings);
if ($result_settings && $result_settings->num_rows > 0) {
    $row_settings = $result_settings->fetch_assoc();
    $system_name = $row_settings['setting_value'];
} else {
    $system_name = "Royal Freight Management System";
}
?>

<?php include 'layouts/main.php'; ?>
<head>
    <?php includeFileWithVariables('layouts/title-meta.php', array('title' => 'Fund Requests')); ?>
    <?php include 'layouts/head-css.php'; ?>
    
    <!-- DataTables CSS -->
    <link href="assets/libs/datatables/dataTables.bootstrap5.min.css" rel="stylesheet" type="text/css" />
    <link href="assets/libs/datatables/buttons.bootstrap5.min.css" rel="stylesheet" type="text/css" />
    <link href="assets/libs/datatables/responsive.bootstrap5.min.css" rel="stylesheet" type="text/css" />
    
    <!-- Sweet Alert css -->
    <link href="assets/libs/sweetalert2/sweetalert2.min.css" rel="stylesheet" type="text/css" />
    
    <!-- Datepicker css -->
    <link href="assets/libs/flatpickr/flatpickr.min.css" rel="stylesheet" type="text/css" />

</head>

<body>
    <!-- Begin page -->
    <div id="layout-wrapper">
        <?php include 'layouts/menu.php'; ?>

        <!-- ============================================================== -->
        <!-- Start right Content here -->
        <!-- ============================================================== -->
        <div class="main-content">
            <div class="page-content">
                <div class="container-fluid">
                    <?php includeFileWithVariables('layouts/page-title.php', array('pagetitle' => 'Fund Management', 'title' => 'Fund Requests')); ?>

                    <?php
                    // Show success message if set
                    if (isset($_SESSION['success_message'])) {
                        echo '<div class="alert alert-success alert-dismissible fade show" role="alert">';
                        echo $_SESSION['success_message'];
                        echo '<button type="button" class="btn-close" data-bs-dismiss="alert" aria-label="Close"></button>';
                        echo '</div>';
                        unset($_SESSION['success_message']);
                    }
                    
                    // Show error message if set
                    if (isset($_SESSION['error_message'])) {
                        echo '<div class="alert alert-danger alert-dismissible fade show" role="alert">';
                        echo $_SESSION['error_message'];
                        echo '<button type="button" class="btn-close" data-bs-dismiss="alert" aria-label="Close"></button>';
                        echo '</div>';
                        unset($_SESSION['error_message']);
                    }
                    ?>

                    <div class="row">
                        <div class="col-lg-12">
                            <div class="card">
                                <div class="card-header d-flex align-items-center">
                                    <h4 class="card-title mb-0 flex-grow-1">Fund Requests</h4>
                                    <div>
                                        <a href="fund_request.php" class="btn btn-primary">
                                            <i class="ri-add-line align-bottom me-1"></i> New Request
                                        </a>
                                    </div>
                                </div>
                                <div class="card-body">
                                    <!-- Filters -->
                                    <div class="filter-row">
                                        <form method="GET" action="">
                                            <div class="row">
                                                <!--<div class="col-md-3 mb-2">
                                                    <label for="status" class="form-label">Status</label>
                                                    <select class="form-select" id="status" name="status">
                                                        <option value="all" <?php echo $status_filter == 'all' ? 'selected' : ''; ?>>All</option>
                                                        <option value="Pending" <?php echo $status_filter == 'Pending' ? 'selected' : ''; ?>>Pending</option>
                                                        <option value="Approved" <?php echo $status_filter == 'Approved' ? 'selected' : ''; ?>>Approved</option>
                                                        <option value="Rejected" <?php echo $status_filter == 'Rejected' ? 'selected' : ''; ?>>Rejected</option>
                                                        <option value="Released" <?php echo $status_filter == 'Released' ? 'selected' : ''; ?>>Released</option>
                                                    </select>
                                                </div>
                                                <div class="col-md-3 mb-2">
                                                    <label for="date_from" class="form-label">From Date</label>
                                                    <input type="text" class="form-control flatpickr-input" id="date_from" name="date_from" placeholder="YYYY-MM-DD" value="<?php echo $date_from; ?>">
                                                </div>
                                                <div class="col-md-3 mb-2">
                                                    <label for="date_to" class="form-label">To Date</label>
                                                    <input type="text" class="form-control flatpickr-input" id="date_to" name="date_to" placeholder="YYYY-MM-DD" value="<?php echo $date_to; ?>">
                                                </div>
                                                <div class="col-md-3 mb-2">
                                                    <label for="search" class="form-label">Search</label>
                                                    <div class="input-group">
                                                        <input type="text" class="form-control" id="search" name="search" placeholder="Search..." value="<?php echo htmlspecialchars($search); ?>">
                                                        <button class="btn btn-primary" type="submit">
                                                            <i class="ri-search-line"></i>
                                                        </button>
                                                    </div>
                                                </div> -->
                                            </div>
                                        </form>
                                    </div>
                                    
                                    <!-- Table -->
                                    <div class="table-responsive">
                                        <table id="fund-requests-table" class="table table-bordered dt-responsive nowrap table-striped align-middle">
                                            <thead>
                                                <tr>
                                                    <th>Request #</th>
                                                    <th>Category</th>
                                                    <th>Amount</th>
                                                    <th>Status</th>
                                                    <th>Request Date</th>
                                                    <th>Requested By</th>
                                                    <th>Action</th>
                                                </tr>
                                            </thead>
                                            <tbody>
                                                <?php if (empty($fund_requests)): ?>
                                                <tr>
                                                    <td colspan="7" class="text-center">No fund requests found</td>
                                                </tr>
                                                <?php else: ?>
                                                    <?php foreach ($fund_requests as $request): ?>
                                                    <tr>
                                                        <td class="fw-medium">
                                                            <?php echo htmlspecialchars($request['request_number']); ?>
                                                            <?php if (!empty($request['file_number'])): ?>
                                                                <span class="file-badge"><?php echo htmlspecialchars($request['file_number']); ?></span>
                                                            <?php endif; ?>
                                                        </td>
                                                        <td>
                                                            <?php echo htmlspecialchars($request['category_name']); ?>
                                                            <span class="badge bg-info"><?php echo $request['category_type']; ?></span>
                                                        </td>
                                                        <td class="currency-text">
                                                            <?php echo $request['currency'] == 'USD' ? '$' : 'Tsh'; ?> 
                                                            <?php echo number_format($request['amount'], 2); ?>
                                                        </td>
                                                        <td>
                                                            <?php
                                                                $status_class = '';
                                                                switch ($request['status']) {
                                                                    case 'Pending':
                                                                        $status_class = 'bg-warning';
                                                                        break;
                                                                    case 'Approved':
                                                                        $status_class = 'bg-info';
                                                                        break;
                                                                    case 'Rejected':
                                                                        $status_class = 'bg-danger';
                                                                        break;
                                                                    case 'Released':
                                                                        $status_class = 'bg-success';
                                                                        break;
                                                                    case 'Cancelled':
                                                                        $status_class = 'bg-secondary';
                                                                        break;
                                                                }
                                                            ?>
                                                            <span class="badge <?php echo $status_class; ?> status-badge">
                                                                <?php echo $request['status']; ?>
                                                            </span>
                                                        </td>
                                                        <td>
                                                            <?php echo date('Y-m-d', strtotime($request['request_date'])); ?>
                                                        </td>
                                                        <td>
                                                            <?php echo htmlspecialchars($request['requester_name']); ?>
                                                        </td>
                                                        <td>
                                                            <div class="d-flex gap-2">
                                                                <a href="view_fund_request.php?id=<?php echo $request['request_id']; ?>" class="btn btn-sm btn-primary">
                                                                    <i class="ri-eye-line"></i>
                                                                </a>
                                                                <?php if ($request['status'] === 'Pending' && canApprove($role_id)): ?>
                                                                <button type="button" class="btn btn-sm btn-success approve-btn" 
                                                                        data-bs-toggle="modal" data-bs-target="#approveModal" 
                                                                        data-id="<?php echo $request['request_id']; ?>"
                                                                        data-number="<?php echo $request['request_number']; ?>">
                                                                    <i class="ri-check-line"></i>
                                                                </button>
                                                                <button type="button" class="btn btn-sm btn-danger reject-btn" 
                                                                        data-bs-toggle="modal" data-bs-target="#rejectModal" 
                                                                        data-id="<?php echo $request['request_id']; ?>"
                                                                        data-number="<?php echo $request['request_number']; ?>">
                                                                    <i class="ri-close-line"></i>
                                                                </button>
                                                                <?php endif; ?>
                                                                <?php if ($request['status'] === 'Approved' && canRelease($role_id)): ?>
                                                                <button type="button" class="btn btn-sm btn-info release-btn" 
                                                                        data-bs-toggle="modal" data-bs-target="#releaseModal" 
                                                                        data-id="<?php echo $request['request_id']; ?>"
                                                                        data-number="<?php echo $request['request_number']; ?>"
                                                                        data-amount="<?php echo $request['amount']; ?>"
                                                                        data-currency="<?php echo $request['currency']; ?>">
                                                                    <i class="ri-bank-line"></i>
                                                                </button>
                                                                <?php endif; ?>
                                                            </div>
                                                        </td>
                                                    </tr>
                                                    <?php endforeach; ?>
                                                <?php endif; ?>
                                            </tbody>
                                        </table>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>
            </div>

            <?php include 'layouts/footer.php'; ?>
        </div>
    </div>

    <!-- Approve Modal -->
    <div class="modal fade" id="approveModal" tabindex="-1" aria-labelledby="approveModalLabel" aria-hidden="true">
        <div class="modal-dialog modal-dialog-centered">
            <div class="modal-content">
                <div class="modal-header bg-light p-3">
                    <h5 class="modal-title" id="approveModalLabel">Approve Fund Request</h5>
                    <button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
                </div>
                <form action="" method="POST">
                    <input type="hidden" name="action" value="approve">
                    <input type="hidden" name="request_id" id="approve_request_id">
                    <div class="modal-body">
                        <p>Are you sure you want to approve fund request <strong id="approve_request_number"></strong>?</p>
                    </div>
                    <div class="modal-footer">
                        <button type="button" class="btn btn-light" data-bs-dismiss="modal">Cancel</button>
                        <button type="submit" class="btn btn-success">Approve</button>
                    </div>
                </form>
            </div>
        </div>
    </div>

    <!-- Reject Modal -->
    <div class="modal fade" id="rejectModal" tabindex="-1" aria-labelledby="rejectModalLabel" aria-hidden="true">
        <div class="modal-dialog modal-dialog-centered">
            <div class="modal-content">
                <div class="modal-header bg-light p-3">
                    <h5 class="modal-title" id="rejectModalLabel">Reject Fund Request</h5>
                    <button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
                </div>
                <form action="" method="POST">
                    <input type="hidden" name="action" value="reject">
                    <input type="hidden" name="request_id" id="reject_request_id">
                    <div class="modal-body">
                        <p>Are you sure you want to reject fund request <strong id="reject_request_number"></strong>?</p>
                        <div class="mb-3">
                            <label for="rejection_reason" class="form-label">Rejection Reason</label>
                            <textarea class="form-control" id="rejection_reason" name="rejection_reason" rows="3" required></textarea>
                        </div>
                    </div>
                    <div class="modal-footer">
                        <button type="button" class="btn btn-light" data-bs-dismiss="modal">Cancel</button>
                        <button type="submit" class="btn btn-danger">Reject</button>
                    </div>
                </form>
            </div>
        </div>
    </div>

    <!-- Release Modal -->
    <div class="modal fade" id="releaseModal" tabindex="-1" aria-labelledby="releaseModalLabel" aria-hidden="true">
        <div class="modal-dialog modal-dialog-centered">
            <div class="modal-content">
                <div class="modal-header bg-light p-3">
                    <h5 class="modal-title" id="releaseModalLabel">Release Funds</h5>
                    <button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
                </div>
                <form action="" method="POST">
                    <input type="hidden" name="action" value="release">
                    <input type="hidden" name="request_id" id="release_request_id">
                    <div class="modal-body">
                        <p>You are about to release funds for request <strong id="release_request_number"></strong>.</p>
                        <p>Amount: <span id="release_amount_display" class="fw-bold"></span></p>
                        <div class="mb-3">
                            <label for="received_by" class="form-label">Released To</label>
                            <select class="form-select" id="received_by" name="received_by" required>
                                <option value="">Select Staff</option>
                                <?php foreach ($staff as $member): ?>
                                    <option value="<?php echo $member['user_id']; ?>"><?php echo htmlspecialchars($member['full_name']); ?></option>
                                <?php endforeach; ?>
                            </select>
                        </div>
                    </div>
                    <div class="modal-footer">
                        <button type="button" class="btn btn-light" data-bs-dismiss="modal">Cancel</button>
                        <button type="submit" class="btn btn-info">Release Funds</button>
                    </div>
                </form>
            </div>
        </div>
    </div>

    <?php include 'layouts/vendor-scripts.php'; ?>
    <script src="assets/js/app.js"></script>
<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
<script src="https://cdn.datatables.net/1.11.5/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.11.5/js/dataTables.bootstrap5.min.js"></script>
<script src="https://cdn.datatables.net/responsive/2.5.0/js/dataTables.responsive.min.js"></script>
<script src="https://cdn.datatables.net/responsive/2.5.0/js/responsive.bootstrap5.min.js"></script>
<link href="assets/libs/sweetalert2/sweetalert2.min.css" rel="stylesheet">
<script src="assets/libs/sweetalert2/sweetalert2.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/select2@4.1.0-rc.0/dist/js/select2.min.js"></script>
    
    <!-- Datepicker js -->
    <script src="assets/libs/flatpickr/flatpickr.min.js"></script>
    
    <script>
        $(document).ready(function() {
            // Initialize DataTable
            $('#fund-requests-table').DataTable({
                responsive: true,
                lengthChange: true,
                lengthMenu: [10, 25, 50, 100],
                order: [[4, 'desc']], // Order by request date descending
                columnDefs: [
                    { orderable: false, targets: [6] } // Disable sorting for action column
                ]
            });
            
            // Initialize Flatpickr date pickers
            flatpickr('.flatpickr-input', {
                dateFormat: 'Y-m-d',
                allowInput: true
            });
            
            // Approve modal
            $('.approve-btn').click(function() {
                const id = $(this).data('id');
                const number = $(this).data('number');
                
                $('#approve_request_id').val(id);
                $('#approve_request_number').text(number);
            });
            
            // Reject modal
            $('.reject-btn').click(function() {
                const id = $(this).data('id');
                const number = $(this).data('number');
                
                $('#reject_request_id').val(id);
                $('#reject_request_number').text(number);
            });
            
            // Release modal
            $('.release-btn').click(function() {
                const id = $(this).data('id');
                const number = $(this).data('number');
                const amount = $(this).data('amount');
                const currency = $(this).data('currency');
                
                $('#release_request_id').val(id);
                $('#release_request_number').text(number);
                $('#release_amount_display').text((currency === 'USD' ? '$' : 'Tsh') + ' ' + parseFloat(amount).toFixed(2));
            });
            
            // Show success message with SweetAlert if present
            <?php if (isset($_SESSION['swal_success'])): ?>
            Swal.fire({
                icon: 'success',
                title: 'Success!',
                text: '<?php echo $_SESSION['swal_success']; ?>',
                confirmButtonColor: '#3085d6'
            });
            <?php unset($_SESSION['swal_success']); endif; ?>
            
            // Show error message with SweetAlert if present
            <?php if (isset($_SESSION['swal_error'])): ?>
            Swal.fire({
                icon: 'error',
                title: 'Error',
                text: '<?php echo $_SESSION['swal_error']; ?>',
                confirmButtonColor: '#3085d6'
            });
            <?php unset($_SESSION['swal_error']); endif; ?>
        });
    </script>
</body>
</html>