<?php
session_start();
if (!isset($_SESSION['role_id']) || !in_array($_SESSION['role_id'], [1, 4])) {
    header("Location: ../../index.php");
    exit();
}

include('../../includes/config.php');
include('../../includes/header.php');

$success = '';
$error = '';

// Handle approval/rejection
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['expense_id'], $_POST['action'])) {
    $expense_id = intval($_POST['expense_id']);
    $action = $_POST['action'];
    $payment_account_id = isset($_POST['payment_account_id']) ? intval($_POST['payment_account_id']) : null;

    if (!in_array($action, ['approve', 'reject'])) {
        $error = "Invalid action.";
    } else {
        $new_status = $action === 'approve' ? 'approved' : 'rejected';
        $conn->begin_transaction();
        try {
            $expense = $conn->query("SELECT * FROM expenses WHERE id = $expense_id")->fetch_assoc();
            if (!$expense) throw new Exception("Expense not found");

            $reference = $expense['reference'];
            $date = $expense['expense_date'];
            $amount = $expense['amount'];
            $description = $expense['description'];
            $category_id = $expense['expense_category_id'];
            $supporting_document = $expense['supporting_document'];
            $added_by = $expense['added_by'];
            $store_id = $expense['store_id'];
            $reconciliation_status = 'unreconciled';

            // Expense account (debit)
            $acct = $conn->query("SELECT account_id FROM expense_categories WHERE id = $category_id")->fetch_assoc();
            $expense_account_id = $acct['account_id'] ?? null;
            if (!$expense_account_id) throw new Exception("Expense category not mapped to an account");

            if ($new_status === 'approved') {
                if (!$payment_account_id) throw new Exception("Please select a payment account");

                $expense_account_name = $conn->query("SELECT account_name FROM accounts WHERE id = $expense_account_id")->fetch_assoc()['account_name'] ?? '';
                $payment_account_name = $conn->query("SELECT account_name FROM accounts WHERE id = $payment_account_id")->fetch_assoc()['account_name'] ?? '';

                // Update expense status
                $stmt = $conn->prepare("UPDATE expenses SET status = ? WHERE id = ?");
                $stmt->bind_param("si", $new_status, $expense_id);
                $stmt->execute();

                // Clear existing FTs first
                $conn->query("DELETE FROM financial_transactions WHERE reference_id = $expense_id AND type = 'expense'");

                // Insert debit FT
                $stmt2 = $conn->prepare("INSERT INTO financial_transactions 
                    (transaction_date, account_id, account, store_id, type, amount, debit, credit, description, reference_id, created_by, status, created_at, updated_at) 
                    VALUES (?, ?, ?, ?, 'expense', ?, ?, 0, ?, ?, ?, 'approved', NOW(), NOW())");
                $stmt2->bind_param("ssiiddsii", $date, $expense_account_id, $expense_account_name, $store_id, $amount, $amount, $description, $expense_id, $_SESSION['user_id']);
                $stmt2->execute();

                // Insert credit FT
                $stmt3 = $conn->prepare("INSERT INTO financial_transactions 
                    (transaction_date, account_id, account, store_id, type, amount, debit, credit, description, reference_id, created_by, status, created_at, updated_at) 
                    VALUES (?, ?, ?, ?, 'expense', ?, 0, ?, ?, ?, ?, 'approved', NOW(), NOW())");
                $stmt3->bind_param("ssiiddsii", $date, $payment_account_id, $payment_account_name, $store_id, $amount, $amount, $description, $expense_id, $_SESSION['user_id']);
                $stmt3->execute();

                // Insert into bank_transactions
                $stmt4 = $conn->prepare("INSERT INTO bank_transactions 
                    (transaction_date, reference, amount, type, description, supporting_document, account_id, reconciled, created_at, updated_at, linked_expense_id, status, created_by, reconciliation_status) 
                    VALUES (?, ?, ?, 'expense_payment', ?, ?, ?, 0, NOW(), NOW(), ?, ?, ?, ?)");
                $stmt4->bind_param("ssdsssisss", $date, $reference, $amount, $description, $supporting_document, $payment_account_id, $expense_id, $new_status, $added_by, $reconciliation_status);
                $stmt4->execute();
                $bank_transaction_id = $stmt4->insert_id;

                // Link both FTs to bank txn
                $stmt5 = $conn->prepare("UPDATE financial_transactions SET linked_bank_transaction_id = ? WHERE reference_id = ? AND type = 'expense'");
                $stmt5->bind_param("ii", $bank_transaction_id, $expense_id);
                $stmt5->execute();
            } else {
                // Rejection: just update status
                $stmt = $conn->prepare("UPDATE expenses SET status = ? WHERE id = ?");
                $stmt->bind_param("si", $new_status, $expense_id);
                $stmt->execute();

                $stmt2 = $conn->prepare("UPDATE financial_transactions SET status = ? WHERE reference_id = ? AND type = 'expense_payment'");
                $stmt2->bind_param("si", $new_status, $expense_id);
                $stmt2->execute();
            }

            // Log action
            $stmt6 = $conn->prepare("INSERT INTO expense_approval_logs (expense_id, action, performed_by, account_id) VALUES (?, ?, ?, ?)");
            $stmt6->bind_param("isii", $expense_id, $action, $_SESSION['user_id'], $payment_account_id);
            $stmt6->execute();

            $conn->commit();
            $success = "Expense has been " . $new_status . " successfully.";
        } catch (Exception $e) {
            $conn->rollback();
            $error = "Transaction failed: " . $e->getMessage();
        }
    }
}

// Fetch pending
$pending = $conn->query("
    SELECT e.*, s.store_name, ec.category_name 
    FROM expenses e
    LEFT JOIN stores s ON e.store_id = s.id
    LEFT JOIN expense_categories ec ON e.expense_category_id = ec.id
    WHERE e.status = 'pending'
    ORDER BY e.expense_date DESC
");

// Fetch available payment accounts
$account_options = $conn->query("SELECT id, account_name, bank_name, account_type FROM accounts ORDER BY account_name");
$accounts_list = [];
while ($acc = $account_options->fetch_assoc()) {
    $accounts_list[] = $acc;
}
?>

<!-- STYLES -->
<style>
body {
    font-family: 'Segoe UI', sans-serif;
    background: url('../../images/profile.jpg') no-repeat center center fixed;
    background-size: cover;
    color: #eee;
}
.container {
    margin: 60px auto;
    max-width: 1150px;
    background: rgba(255,255,255,0.08);
    padding: 25px;
    border-radius: 12px;
    backdrop-filter: blur(12px);
}
h2 {
    text-align: center;
    color: #ffa500;
}
table {
    width: 100%;
    border-collapse: collapse;
    margin-top: 20px;
}
th, td {
    padding: 10px 12px;
    text-align: left;
    border-bottom: 1px solid rgba(255,255,255,0.1);
}
th { color: #ffa500; }
button {
    padding: 6px 12px;
    border-radius: 6px;
    border: none;
    cursor: pointer;
    font-weight: bold;
}
.approve-btn { background: #4CAF50; color: white; }
.reject-btn { background: #F44336; color: white; }
.success-message, .error-message {
    padding: 10px 14px;
    border-radius: 6px;
    margin-bottom: 20px;
}
.success-message { background: rgba(34, 197, 94, 0.85); color: #fff; }
.error-message { background: rgba(248, 113, 113, 0.85); color: #fff; }
.preview-link { color: #ffc107; text-decoration: underline; }
select { padding: 4px 6px; border-radius: 4px; }
</style>

<!-- HTML -->
<div class="container">
    <h2>Pending Expense Approvals</h2>

    <?php if ($success): ?>
        <div class="success-message"><?= htmlspecialchars($success) ?></div>
    <?php elseif ($error): ?>
        <div class="error-message"><?= htmlspecialchars($error) ?></div>
    <?php endif; ?>

    <table>
        <thead>
            <tr>
                <th>Ref</th>
                <th>Date</th>
                <th>Store</th>
                <th>Category</th>
                <th>Description</th>
                <th>Amount</th>
                <th>Document</th>
                <th>Pay From</th>
                <th>Action</th>
            </tr>
        </thead>
        <tbody>
            <?php while ($row = $pending->fetch_assoc()): ?>
                <tr>
                    <td><?= htmlspecialchars($row['reference']) ?></td>
                    <td><?= htmlspecialchars($row['expense_date']) ?></td>
                    <td><?= htmlspecialchars($row['store_name']) ?></td>
                    <td><?= htmlspecialchars($row['category_name']) ?></td>
                    <td><?= htmlspecialchars($row['description']) ?></td>
                    <td><?= number_format($row['amount'], 2) ?></td>
                    <td>
                        <?php if ($row['supporting_document']): ?>
                            <a href="../../uploads/expenses/<?= $row['supporting_document'] ?>" target="_blank" class="preview-link">Preview</a>
                        <?php else: ?>
                            <span style="color: #999;">N/A</span>
                        <?php endif; ?>
                    </td>
                    <td>
                        <form method="POST" style="display:inline;">
                            <input type="hidden" name="expense_id" value="<?= $row['id'] ?>">
                            <select name="payment_account_id" required>
                                <option value="">-- Select --</option>
                                <?php foreach ($accounts_list as $acc): ?>
                                    <option value="<?= $acc['id'] ?>">
                                        <?= htmlspecialchars($acc['account_name']) ?> (<?= $acc['account_type'] ?>)
                                    </option>
                                <?php endforeach; ?>
                            </select>
                    </td>
                    <td>
                            <button type="submit" name="action" value="approve" class="approve-btn">Approve</button>
                            <button type="submit" name="action" value="reject" class="reject-btn">Reject</button>
                        </form>
                    </td>
                </tr>
            <?php endwhile; ?>
            <?php if ($pending->num_rows === 0): ?>
                <tr><td colspan="9" style="text-align:center;">No pending expenses found.</td></tr>
            <?php endif; ?>
        </tbody>
    </table>
</div>

<?php include('../../includes/footer.php'); ?>
