<?php
session_start();
include 'config.php';

// Redirect if not logged in
if (!isset($_SESSION['user_id'])) {
    header("Location: index.php");
    exit();
}

$user_id = $_SESSION['user_id'];
$username = $_SESSION['username'];
$role = $_SESSION['role']; // 'employee' or 'hr'

// Handle Employee Request
if (isset($_POST['submit_request']) && $role == 'employee') {
    $amount = floatval($_POST['amount']);
    $reason = trim($_POST['reason']);
    if ($amount > 0 && !empty($reason)) {
        $query = "INSERT INTO advance_requests (employee_id, amount, reason, status, request_date) VALUES (?, ?, ?, 'Pending', CURDATE())";
        $stmt = $conn->prepare($query);
        $stmt->bind_param('ids', $user_id, $amount, $reason);
        $stmt->execute();
        $success = "Advance request submitted successfully!";
    } else {
        $error = "Invalid amount or reason.";
    }
}

// HR Approval
if (isset($_POST['approve_request']) && $role == 'hr') {
    $req_id = intval($_POST['req_id']);
    $approved_amount = floatval($_POST['approved_amount']);
    $emi_months = intval($_POST['emi_months']);
    $remarks = trim($_POST['remarks']);

    if ($approved_amount > 0 && $emi_months > 0) {
        $update = $conn->prepare("UPDATE advance_requests SET status='Approved', approved_amount=?, emi_months=?, hr_remarks=? WHERE id=?");
        $update->bind_param("disi", $approved_amount, $emi_months, $remarks, $req_id);
        $update->execute();

        // Reflect to Payroll
        $get = $conn->query("SELECT employee_id, approved_amount, emi_months FROM advance_requests WHERE id=$req_id");
        if ($req = $get->fetch_assoc()) {
            $emi_amount = $req['approved_amount'] / $req['emi_months'];
            for ($i = 1; $i <= $req['emi_months']; $i++) {
                $month = date('Y-m', strtotime("+$i month"));
                $conn->query("INSERT INTO payroll (employee_id, month_year, advance_deduction)
                              VALUES ({$req['employee_id']}, '$month', $emi_amount)
                              ON DUPLICATE KEY UPDATE advance_deduction = advance_deduction + $emi_amount");
            }
        }
        $success = "Advance approved and payroll updated!";
    }
}

// HR Rejection
if (isset($_POST['reject_request']) && $role == 'hr') {
    $req_id = intval($_POST['req_id']);
    $remarks = trim($_POST['remarks']);
    $update = $conn->prepare("UPDATE advance_requests SET status='Rejected', hr_remarks=? WHERE id=?");
    $update->bind_param("si", $remarks, $req_id);
    $update->execute();
    $success = "Advance request rejected.";
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=no">
    <title>Advance Portal</title>
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.5.0/css/all.min.css">
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css" rel="stylesheet">
    <style>
        :root {
            --primary-color: #2c3e50;
            --secondary-color: #1abc9c;
            --accent-color: #3498db;
            --light-bg: #f8f9fa;
            --dark-text: #2c3e50;
            --light-text: #7f8c8d;
            --border-color: #e0e0e0;
        }

        body {
            font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif;
            background-color: var(--light-bg);
            margin: 0;
            padding: 0;
            display: flex;
            min-height: 100vh;
            color: var(--dark-text);
        }

        .sidebar {
            width: 250px;
            background: var(--primary-color);
            color: #fff;
            padding: 15px;
            transition: transform 0.3s ease;
            position: fixed;
            top: 0;
            bottom: 0;
            z-index: 1000;
            transform: translateX(-250px);
            box-shadow: 2px 0 10px rgba(0,0,0,0.1);
        }

        .sidebar.active { transform: translateX(0); }

        .sidebar h2 {
            font-size: 1.2rem;
            margin-bottom: 20px;
            padding-bottom: 10px;
            border-bottom: 1px solid rgba(255,255,255,0.1);
            text-align: center;
        }

        .sidebar a {
            color: #fff;
            text-decoration: none;
            padding: 10px 15px;
            display: block;
            border-radius: 6px;
            margin-bottom: 5px;
            font-size: 0.9rem;
            transition: all 0.2s ease;
        }

        .sidebar a:hover, .sidebar a.active { background: var(--secondary-color); transform: translateX(5px); }

        .sidebar .dropdown-menu {
            background: #34495e;
            border: none;
            padding: 0;
            margin-left: 10px;
            border-radius: 6px;
        }

        .sidebar .dropdown-menu a {
            color: #fff;
            padding: 8px 20px;
            font-size: 0.85rem;
        }

        .sidebar .dropdown-menu a:hover { background: var(--secondary-color); }

        .hamburger {
            position: fixed;
            top: 15px;
            left: 15px;
            z-index: 1100;
            background: var(--primary-color);
            color: #fff;
            border: none;
            padding: 10px 12px;
            border-radius: 6px;
            cursor: pointer;
            box-shadow: 0 2px 5px rgba(0,0,0,0.2);
        }

        .hamburger:hover { background: var(--secondary-color); }

        .content {
            flex: 1;
            padding: 20px;
            margin-left: 0;
            transition: margin-left 0.3s ease;
        }

        .content.active { margin-left: 250px; }

        .dashboard-header {
            background: white;
            padding: 20px;
            border-radius: 10px;
            box-shadow: 0 2px 10px rgba(0,0,0,0.05);
            margin-bottom: 25px;
        }

        h1 {
            font-size: 1.8rem;
            margin-bottom: 10px;
            color: var(--primary-color);
            font-weight: 700;
        }

        .page-description {
            color: var(--light-text);
            font-size: 1rem;
            margin-bottom: 0;
        }

        .card {
            background: #fff;
            border-radius: 10px;
            box-shadow: 0 2px 10px rgba(0,0,0,0.05);
            padding: 20px;
            margin-bottom: 25px;
        }

        .table-container {
            background: #fff;
            border-radius: 10px;
            box-shadow: 0 2px 10px rgba(0,0,0,0.05);
            overflow: hidden;
            margin-bottom: 30px;
        }

        .table {
            width: 100%;
            border-collapse: collapse;
            margin: 0;
        }

        .table th, .table td {
            padding: 14px 12px;
            border-bottom: 1px solid var(--border-color);
            text-align: left;
            font-size: 0.9rem;
        }

        .table th {
            background: var(--primary-color);
            color: white;
            font-weight: 600;
        }

        .table tr:nth-child(even) { background: #f9f9f9; }
        .table tr:hover { background: #e8f4f8; }

        .badge {
            font-size: 0.8rem;
            padding: 0.35em 0.65em;
        }

        .form-control, .btn {
            border-radius: 6px;
        }

        .btn-sm {
            padding: 0.25rem 0.5rem;
            font-size: 0.8rem;
        }

        .alert {
            border-radius: 8px;
            padding: 12px 16px;
            margin-bottom: 20px;
        }

        @media (max-width: 768px) {
            .content { padding: 15px; }
            h1 { font-size: 1.5rem; }
            .table-container { overflow-x: auto; }
            .table { min-width: 800px; }
            .sidebar { width: 220px; transform: translateX(-220px); }
            .content.active { margin-left: 220px; }
        }

        @media (max-width: 576px) {
            .hamburger { top: 10px; left: 10px; padding: 8px 10px; }
        }
    </style>
</head>
<body>

<!-- Hamburger -->
<button class="hamburger" onclick="toggleSidebar()">
    <i class="fas fa-bars"></i>
</button>

<!-- Sidebar -->
<div class="sidebar" id="sidebar">
    <h2>Mypeopleconnect</h2>

    <a href="dashboard_admin.php"><i class="fas fa-home"></i> Organisation Chart</a>

    <div class="dropdown">
        <a class="dropdown-toggle" href="#" role="button" data-bs-toggle="dropdown"><i class="fas fa-home"></i> DAP</a>
        <ul class="dropdown-menu">
            <li><a class="dropdown-item" href="manage_users.php">Employee Details</a></li>
            <li><a class="dropdown-item" href="manage_documents.php">Policies Rolled Out</a></li>
            <li><a class="dropdown-item" href="digital.php">Acceptance Confirmed</a></li>
        </ul>
    </div>

    <div class="dropdown">
        <a class="dropdown-toggle active" href="#" role="button" data-bs-toggle="dropdown"><i class="fas fa-users"></i> CAB</a>
        <ul class="dropdown-menu">
            <li><a class="dropdown-item" href="attendanceexcel.php">Attendance Excel</a></li>
            <li><a class="dropdown-item" href="attendance.php">Attendance</a></li>
            <li><a class="dropdown-item" href="manage_mappings.php">Manage Mappings</a></li>
            <li><a class="dropdown-item" href="attendance_sync.php">Manage Attendance</a></li>
            <li><a class="dropdown-item" href="payroll_excel.php">Payroll Excel</a></li>
            <li><a class="dropdown-item" href="payroll_dashboard.php">Payroll</a></li>
            <li><a class="dropdown-item active" href="advance_requests.php">Advance Portal</a></li>
            <li><a class="dropdown-item" href="expense_dashboard.php">Expenses Dashboard</a></li>
            <li><a class="dropdown-item" href="leave_dashboard.php">Leave Portal</a></li>
            <li><a class="dropdown-item" href="grievance_dashboard.php">Grievance Portal</a></li>
        </ul>
    </div>

    <div class="dropdown">
        <a class="dropdown-toggle" href="#" role="button" data-bs-toggle="dropdown"><i class="fas fa-user"></i> PEP</a>
        <ul class="dropdown-menu">
            <li><a class="dropdown-item" href="individual_rr.php">Individual R & R</a></li>
            <li><a class="dropdown-item" href="individual_kpi.php">Individual KPI</a></li>
            <li><a class="dropdown-item" href="login_credentials.php">Login Credentials</a></li>
        </ul>
    </div>

    <div class="dropdown">
        <a class="dropdown-toggle" href="#" role="button" data-bs-toggle="dropdown"><i class="fas fa-book"></i> L&D</a>
        <ul class="dropdown-menu">
            <li><a class="dropdown-item" href="incident_employee.php">Incident Portal</a></li>
            <li><a class="dropdown-item" href="training_dashboard.php">Training Portal</a></li>
            <li><a class="dropdown-item" href="rr.php">Rewards and Recognition</a></li>
        </ul>
    </div>

    <div class="dropdown">
        <a class="dropdown-toggle" href="#" role="button" data-bs-toggle="dropdown"><i class="fas fa-star"></i> AAA</a>
        <ul class="dropdown-menu">
            <li><a class="dropdown-item" href="employee_tiy.php">Employee TIY</a></li>
            <li><a class="dropdown-item" href="login_credentials.php">Login Credentials</a></li>
        </ul>
    </div>

    <a href="logout.php" class="text-danger"><i class="fas fa-sign-out-alt"></i> Logout</a>
</div>

<!-- Main Content -->
<div class="content" id="content">
    <div class="dashboard-header">
        <h1 class="text-primary">Salary Advance Portal</h1>
        <p class="page-description">Manage advance requests and approvals seamlessly</p>
    </div>

    <?php if (isset($success)): ?>
        <div class="alert alert-success"><i class="fas fa-check-circle"></i> <?= htmlspecialchars($success) ?></div>
    <?php elseif (isset($error)): ?>
        <div class="alert alert-danger"><i class="fas fa-exclamation-triangle"></i> <?= htmlspecialchars($error) ?></div>
    <?php endif; ?>

    <!-- Employee View -->
    <?php if ($role == 'employee'): ?>
        <div class="card">
            <h4 class="text-success mb-3"><i class="fas fa-paper-plane"></i> Submit New Advance Request</h4>
            <form method="POST">
                <div class="row g-3">
                    <div class="col-md-6">
                        <label class="form-label">Amount (₹)</label>
                        <input type="number" name="amount" class="form-control" min="1" step="0.01" required>
                    </div>
                    <div class="col-md-6">
                        <label class="form-label">Reason</label>
                        <textarea name="reason" class="form-control" rows="2" required></textarea>
                    </div>
                </div>
                <button type="submit" name="submit_request" class="btn btn-primary mt-3"><i class="fas fa-paper-plane"></i> Submit Request</button>
            </form>
        </div>

        <h5><i class="fas fa-history"></i> Your Advance Requests</h5>
        <div class="table-container">
            <table class="table table-hover">
                <thead>
                    <tr><th>Date</th><th>Amount</th><th>Status</th><th>HR Remarks</th></tr>
                </thead>
                <tbody>
                <?php
                $res = $conn->query("SELECT * FROM advance_requests WHERE employee_id = $user_id ORDER BY id DESC");
                if ($res->num_rows == 0):
                ?>
                    <tr><td colspan="4" class="text-center text-muted">No requests found.</td></tr>
                <?php else: while ($row = $res->fetch_assoc()): ?>
                    <tr>
                        <td><?= date('d-m-Y', strtotime($row['request_date'])) ?></td>
                        <td>₹<?= number_format($row['amount'], 2) ?></td>
                        <td>
                            <span class="badge bg-<?= $row['status']=='Approved'?'success':($row['status']=='Rejected'?'danger':'warning') ?>">
                                <?= $row['status'] ?>
                            </span>
                        </td>
                        <td><?= htmlspecialchars($row['hr_remarks'] ?? '-') ?></td>
                    </tr>
                <?php endwhile; endif; ?>
                </tbody>
            </table>
        </div>

        <!-- NEW: Salary Advances (EMI Deductions) -->
        <h5><i class="fas fa-rupee-sign"></i> Your Salary Advances (EMI Deductions)</h5>
        <div class="card">
            <?php
            // Fetch all approved advances with EMI details
            $advances_sql = "
                SELECT ar.id, ar.amount, ar.approved_amount, ar.emi_months, ar.request_date,
                       COALESCE(SUM(p.advance_deduction), 0) AS total_deducted
                FROM advance_requests ar
                LEFT JOIN payroll p ON ar.employee_id = p.employee_id
                    AND p.month_year >= DATE_FORMAT(ar.request_date, '%Y-%m')
                WHERE ar.employee_id = ? AND ar.status = 'Approved'
                GROUP BY ar.id
                ORDER BY ar.request_date DESC
            ";
            $stmt = $conn->prepare($advances_sql);
            $stmt->bind_param('i', $user_id);
            $stmt->execute();
            $advances = $stmt->get_result();
            ?>

            <?php if ($advances->num_rows == 0): ?>
                <p class="text-muted text-center mb-0">No salary advances deducted yet.</p>
            <?php else: ?>
                <div class="table-responsive">
                    <table class="table table-sm table-hover align-middle">
                        <thead class="table-light">
                            <tr>
                                <th>Request Date</th>
                                <th>Requested</th>
                                <th>Approved</th>
                                <th>EMI/Month</th>
                                <th>Months</th>
                                <th>Deducted</th>
                                <th>Balance</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php while ($adv = $advances->fetch_assoc()):
                                $emi = $adv['emi_months'] > 0 ? $adv['approved_amount'] / $adv['emi_months'] : 0;
                                $balance = $adv['approved_amount'] - $adv['total_deducted'];
                            ?>
                                <tr>
                                    <td><?= date('d-m-Y', strtotime($adv['request_date'])) ?></td>
                                    <td>₹<?= number_format($adv['amount'], 2) ?></td>
                                    <td>₹<?= number_format($adv['approved_amount'], 2) ?></td>
                                    <td>₹<?= number_format($emi, 2) ?></td>
                                    <td><?= $adv['emi_months'] ?></td>
                                    <td><strong class="text-danger">₹<?= number_format($adv['total_deducted'], 2) ?></strong></td>
                                    <td><strong class="text-<?= $balance > 0 ? 'warning' : 'success' ?>">
                                        ₹<?= number_format($balance, 2) ?>
                                    </strong></td>
                                </tr>
                            <?php endwhile; ?>
                        </tbody>
                    </table>
                </div>
            <?php endif; ?>
        </div>

    <!-- HR View -->
    <?php elseif ($role == 'hr'): ?>
        <h5><i class="fas fa-clock"></i> Pending Advance Requests</h5>
        <div class="table-container">
            <table class="table table-hover">
                <thead>
                    <tr><th>Employee</th><th>Amount</th><th>Reason</th><th>Date</th><th>Action</th></tr>
                </thead>
                <tbody>
                <?php
                $res = $conn->query("SELECT a.*, u.first_name, u.last_name FROM advance_requests a JOIN users u ON a.employee_id=u.id WHERE a.status='Pending' ORDER BY a.request_date DESC");
                if ($res->num_rows == 0):
                ?>
                    <tr><td colspan="5" class="text-center text-muted">No pending requests.</td></tr>
                <?php else: while ($row = $res->fetch_assoc()):
                    $full = htmlspecialchars($row['first_name'] . ' ' . $row['last_name']);
                ?>
                    <tr>
                        <td><strong><?= $full ?></strong></td>
                        <td>₹<?= number_format($row['amount'], 2) ?></td>
                        <td><?= htmlspecialchars($row['reason']) ?></td>
                        <td><?= date('d-m-Y', strtotime($row['request_date'])) ?></td>
                        <td>
                            <form method="POST" class="d-inline">
                                <input type="hidden" name="req_id" value="<?= $row['id'] ?>">
                                <div class="row g-2">
                                    <div class="col-12"><input type="number" name="approved_amount" placeholder="Approved ₹" class="form-control form-control-sm" required></div>
                                    <div class="col-12"><input type="number" name="emi_months" placeholder="EMI Months" class="form-control form-control-sm" min="1" required></div>
                                    <div class="col-12"><textarea name="remarks" placeholder="Remarks" class="form-control form-control-sm" rows="1"></textarea></div>
                                    <div class="col-6"><button name="approve_request" class="btn btn-success btn-sm w-100"><i class="fas fa-check"></i> Approve</button></div>
                                    <div class="col-6"><button name="reject_request" class="btn btn-danger btn-sm w-100"><i class="fas fa-times"></i> Reject</button></div>
                                </div>
                            </form>
                        </td>
                    </tr>
                <?php endwhile; endif; ?>
                </tbody>
            </table>
        </div>

        <h5><i class="fas fa-check-double"></i> Approved / Rejected Requests</h5>
        <div class="table-container">
            <table class="table table-hover">
                <thead>
                    <tr><th>Employee</th><th>Amount</th><th>Approved</th><th>EMI</th><th>Status</th><th>Remarks</th></tr>
                </thead>
                <tbody>
                <?php
                $done = $conn->query("SELECT a.*, u.first_name, u.last_name FROM advance_requests a JOIN users u ON a.employee_id=u.id WHERE a.status IN ('Approved', 'Rejected') ORDER BY a.request_date DESC");
                if ($done->num_rows == 0):
                ?>
                    <tr><td colspan="6" class="text-center text-muted">No processed requests.</td></tr>
                <?php else: while ($row = $done->fetch_assoc()):
                    $full = htmlspecialchars($row['first_name'] . ' ' . $row['last_name']);
                ?>
                    <tr>
                        <td><strong><?= $full ?></strong></td>
                        <td>₹<?= number_format($row['amount'], 2) ?></td>
                        <td>₹<?= number_format($row['approved_amount'] ?? 0, 2) ?></td>
                        <td><?= ($row['emi_months'] ?? '-') ?></td>
                        <td><span class="badge bg-<?= $row['status']=='Approved'?'success':'danger' ?>"><?= $row['status'] ?></span></td>
                        <td><?= htmlspecialchars($row['hr_remarks'] ?? '-') ?></td>
                    </tr>
                <?php endwhile; endif; ?>
                </tbody>
            </table>
        </div>
    <?php endif; ?>
</div>

<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/js/bootstrap.bundle.min.js"></script>
<script>
    function toggleSidebar() {
        document.getElementById('sidebar').classList.toggle('active');
        document.getElementById('content').classList.toggle('active');
    }
</script>
</body>
</html>