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

// Check if user is logged in and has 'md' role
if (!isset($_SESSION['user_id']) || strtolower($_SESSION['role']) !== 'md') {
    header("Location: index.php");
    exit();
}

$employee_id = $_SESSION['user_id'];
$username = $_SESSION['username'];

// Get current month and year or selected values
$current_month = date('m');
$current_year = date('Y');
$selected_month = isset($_GET['month']) ? (int)$_GET['month'] : $current_month;
$selected_year = isset($_GET['year']) ? (int)$_GET['year'] : $current_year;

// Validate month and year
if ($selected_month < 1 || $selected_month > 12) {
    $selected_month = $current_month;
}
if ($selected_year < 2000 || $selected_year > 2100) {
    $selected_year = $current_year;
}

$month_year = sprintf("%d-%02d", $selected_year, $selected_month);

// Handle payroll approval action
$message = '';
if (isset($_POST['action']) && $_POST['action'] === 'approve_payroll') {
    $update_query = "UPDATE payroll_records SET status = 'approved', approved_by = ? WHERE month = ? AND year = ? AND status = 'pending'";
    $stmt_update = mysqli_prepare($conn, $update_query);
    mysqli_stmt_bind_param($stmt_update, "sii", $username, $selected_month, $selected_year);
    if (mysqli_stmt_execute($stmt_update)) {
        $message = "Payroll approved for " . date('F Y', mktime(0, 0, 0, $selected_month, 1, $selected_year)) . ".";
    } else {
        $message = "Failed to approve payroll: " . mysqli_stmt_error($stmt_update);
    }
    mysqli_stmt_close($stmt_update);
}

// Handle attendance approval action
if (isset($_POST['action']) && $_POST['action'] === 'approve_attendance') {
    $update_query = "UPDATE attendancetech SET approval_status = 'approved', approved_by = ? WHERE DATE_FORMAT(attendance_date, '%Y-%m') = ? AND approval_status = 'pending'";
    $stmt_update = mysqli_prepare($conn, $update_query);
    mysqli_stmt_bind_param($stmt_update, "ss", $username, $month_year);
    if (mysqli_stmt_execute($stmt_update)) {
        $message = "Attendance approved for " . date('F Y', mktime(0, 0, 0, $selected_month, 1, $selected_year)) . ".";
    } else {
        $message = "Failed to approve attendance: " . mysqli_stmt_error($stmt_update);
    }
    mysqli_stmt_close($stmt_update);
}

// Handle attendance approval status filter
$approved_attendance = isset($_GET['attendance_status']) ? $_GET['attendance_status'] : 'pending';
if (!in_array($approved_attendance, ['pending', 'approved', 'all'])) {
    $approved_attendance = 'pending';
}

// Fetch pending payroll data
$payroll_query = "SELECT u.id, u.first_name, u.last_name, u.designation, u.department, u.location, u.basic_salary, 
                 p.total_gross_salary, p.net_earnings, p.status
          FROM users u
          INNER JOIN payroll_records p ON u.id = p.employee_id 
          WHERE p.month = ? AND p.year = ? AND p.status = 'pending'
          GROUP BY u.id";
$stmt_payroll = mysqli_prepare($conn, $payroll_query);
mysqli_stmt_bind_param($stmt_payroll, "ii", $selected_month, $selected_year);
mysqli_stmt_execute($stmt_payroll);
$payroll_result = mysqli_stmt_get_result($stmt_payroll);

// Fetch attendance summary data
$attendance_query = "SELECT 
                        a.user_id, 
                        COALESCE(u.first_name, '') AS first_name, 
                        COALESCE(u.last_name, '') AS last_name,
                        COUNT(CASE WHEN a.status = 'Present' THEN 1 END) AS days_present,
                        COUNT(CASE WHEN a.status = 'Absent' THEN 1 END) AS days_absent,
                        COUNT(CASE WHEN a.late_by IS NOT NULL AND a.late_by != '' THEN 1 END) AS days_late,
                        COUNT(*) AS total_days,
                        MAX(a.approval_status) AS approval_status
                    FROM attendancetech a
                    LEFT JOIN users u ON a.user_id = u.id
                    WHERE DATE_FORMAT(a.attendance_date, '%Y-%m') = ?" . 
                    ($approved_attendance !== 'all' ? " AND a.approval_status = ?" : "") . "
                    GROUP BY a.user_id
                    ORDER BY u.first_name ASC";
$stmt_attendance = mysqli_prepare($conn, $attendance_query);
if ($approved_attendance === 'all') {
    mysqli_stmt_bind_param($stmt_attendance, "s", $month_year);
} else {
    mysqli_stmt_bind_param($stmt_attendance, "ss", $month_year, $approved_attendance);
}
mysqli_stmt_execute($stmt_attendance);
$attendance_result = mysqli_stmt_get_result($stmt_attendance);
?>

<!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>Payroll and Attendance Approval</title>
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.4.0/css/all.min.css">
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
    <style>
        body {
            font-family: Arial, sans-serif;
            background-color: #f4f4f4;
            margin: 0;
            padding: 0;
            display: flex;
            min-height: 100vh;
        }
        .sidebar {
            width: 250px;
            background: #2c3e50;
            color: #fff;
            padding: 15px;
            transition: transform 0.3s ease;
            position: fixed;
            top: 0;
            bottom: 0;
            z-index: 1000;
            transform: translateX(-250px);
        }
        .sidebar.active {
            transform: translateX(0);
        }
        .sidebar h2 {
            font-size: 1rem;
            margin-bottom: 15px;
        }
        .sidebar a {
            color: #fff;
            text-decoration: none;
            padding: 8px 10px;
            display: block;
            border-radius: 4px;
            margin-bottom: 5px;
            font-size: 0.9rem;
        }
        .sidebar a:hover {
            background: #1abc9c;
        }
        .sidebar a.active {
            background: #1abc9c;
        }
        .sidebar .dropdown-menu {
            background: #34495e;
            border: none;
            padding: 0;
        }
        .sidebar .dropdown-menu a {
            color: #fff;
            padding: 8px 20px;
            font-size: 0.85rem;
        }
        .sidebar .dropdown-menu a:hover {
            background: #1abc9c;
        }
        .sidebar .dropdown-toggle::after {
            float: right;
            margin-top: 8px;
        }
        .hamburger {
            position: fixed;
            top: 10px;
            left: 10px;
            z-index: 1100;
            background: #2c3e50;
            color: #fff;
            border: none;
            padding: 8px;
            border-radius: 4px;
            cursor: pointer;
        }
        .content {
            flex: 1;
            padding: 15px;
            margin-left: 0;
            transition: margin-left 0.3s ease;
        }
        .content.active {
            margin-left: 250px;
        }
        h1 {
            font-size: 1.5rem;
            margin-bottom: 15px;
            color: #333;
        }
        h2 {
            font-size: 1.2rem;
            color: #2c3e50;
            margin-top: 20px;
            margin-bottom: 15px;
        }
        .table {
            width: 100%;
            max-width: 1200px;
            margin: 0 auto 30px;
            border-collapse: collapse;
            background: #fff;
            box-shadow: 0 2px 4px rgba(0, 0, 0, 0.1);
            border-radius: 8px;
            overflow: hidden;
        }
        .table th, .table td {
            padding: 12px;
            border: 1px solid #ddd;
            text-align: left;
            font-size: 0.9rem;
        }
        .table th {
            background: #2c3e50;
            color: white;
            font-weight: 600;
        }
        .table tr:nth-child(even) {
            background: #f9f9f9;
        }
        .table tr:hover {
            background: #e8f4f8;
        }
        .table-responsive {
            overflow-x: auto;
        }
        @media (max-width: 576px) {
            .table {
                font-size: 0.75rem;
                display: block;
                overflow-x: auto;
            }
            h1 {
                font-size: 1.2rem;
            }
            h2 {
                font-size: 1rem;
            }
        }
    </style>
</head>
<body>
    <!-- Hamburger Menu -->
    <button class="hamburger" onclick="toggleSidebar()">
        <i class="fas fa-bars"></i>
    </button>

    <!-- Sidebar -->
    <div class="sidebar" id="sidebar">
        <h2>Mypeopleconnect</h2>
        <a href="dashboard_md.php"><i class="fas fa-home"></i> MD Dashboard</a>
        <a class="dropdown-toggle active" href="payroll_approval.php" aria-expanded="false">
            <i class="fas fa-money-check-alt"></i> Payroll & Attendance Approval
        </a>
        <div class="dropdown">
            <a class="dropdown-toggle" href="#" role="button" data-bs-toggle="dropdown" aria-expanded="false">
                <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" href="#" role="button" data-bs-toggle="dropdown" aria-expanded="false">
                <i class="fas fa-users"></i> CAB
            </a>
            <ul class="dropdown-menu">
                <li><a class="dropdown-item" href="payroll_dashboard.php">Payroll</a></li>
                <li><a class="dropdown-item" href="leave_dashboard.php">Leave</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" aria-expanded="false">
                <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" aria-expanded="false">
                <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" aria-expanded="false">
                <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">
        <h1 class="text-primary text-center">Payroll and Attendance Approval</h1>

        <!-- Month/Year Selection -->
        <div class="mb-3">
            <form method="GET" action="payroll_approval.php">
                <div class="row g-2">
                    <div class="col-md-3">
                        <label for="month" class="form-label">Month</label>
                        <select name="month" id="month" class="form-select" onchange="this.form.submit()">
                            <?php
                            for ($m = 1; $m <= 12; $m++) {
                                $month_name = date('F', mktime(0, 0, 0, $m, 1));
                                $selected = ($m == $selected_month) ? 'selected' : '';
                                echo "<option value='$m' $selected>$month_name</option>";
                            }
                            ?>
                        </select>
                    </div>
                    <div class="col-md-3">
                        <label for="year" class="form-label">Year</label>
                        <select name="year" id="year" class="form-select" onchange="this.form.submit()">
                            <?php
                            for ($y = $current_year - 5; $y <= $current_year + 5; $y++) {
                                $selected = ($y == $selected_year) ? 'selected' : '';
                                echo "<option value='$y' $selected>$y</option>";
                            }
                            ?>
                        </select>
                    </div>
                </div>
            </form>
        </div>

        <!-- Message Display -->
        <?php if ($message): ?>
            <div class="alert alert-info"><?php echo htmlspecialchars($message); ?></div>
        <?php endif; ?>

        <!-- Payroll Approval Section -->
        <h2>Payroll Approval</h2>
        <div class="mb-3">
            <form method="POST" action="payroll_approval.php?month=<?php echo $selected_month; ?>&year=<?php echo $selected_year; ?>">
                <input type="hidden" name="action" value="approve_payroll">
                <button type="submit" class="btn btn-primary">Approve All Pending Payrolls</button>
            </form>
        </div>
        <div class="table-responsive">
            <table class="table table-bordered table-striped">
                <thead>
                    <tr>
                        <th>#</th>
                        <th>Employee</th>
                        <th>Department</th>
                        <th>Designation</th>
                        <th>Location</th>
                        <th>Basic Salary (₹)</th>
                        <th>Gross Salary (₹)</th>
                        <th>Net Earnings (₹)</th>
                        <th>Status</th>
                        <th>Actions</th>
                    </tr>
                </thead>
                <tbody>
                <?php
                $count = 1;
                while ($row = mysqli_fetch_assoc($payroll_result)) {
                    $fullName = trim($row['first_name'] . ' ' . $row['last_name']) ?: $row['id'];
                    $basic_salary = number_format($row['basic_salary'] ?? 0, 2);
                    $gross_salary = number_format($row['total_gross_salary'] ?? 0, 2);
                    $net_earnings = number_format($row['net_earnings'] ?? 0, 2);
                    $status = $row['status'] ?? 'N/A';
                    echo "<tr>
                            <td>{$count}</td>
                            <td>" . htmlspecialchars($fullName) . "</td>
                            <td>" . htmlspecialchars($row['department']) . "</td>
                            <td>" . htmlspecialchars($row['designation']) . "</td>
                            <td>" . htmlspecialchars(ucfirst($row['location'])) . "</td>
                            <td>{$basic_salary}</td>
                            <td>{$gross_salary}</td>
                            <td>{$net_earnings}</td>
                            <td>{$status}</td>
                            <td>
                                <a href='view_payroll.php?emp_id={$row['id']}&month={$selected_month}&year={$selected_year}' class='btn btn-sm btn-info'>View</a>
                            </td>
                          </tr>";
                    $count++;
                }
                mysqli_stmt_close($stmt_payroll);
                if ($count == 1) {
                    echo "<tr><td colspan='10' class='text-center'>No payroll data found for the selected period.</td></tr>";
                }
                ?>
                </tbody>
            </table>
        </div>

        <!-- Attendance Approval Section -->
        <h2>Attendance Approval</h2>
        <div class="mb-3">
            <form method="POST" action="payroll_approval.php?month=<?php echo $selected_month; ?>&year=<?php echo $selected_year; ?>">
                <input type="hidden" name="action" value="approve_attendance">
                <button type="submit" class="btn btn-primary">Approve All Pending Attendance</button>
            </form>
        </div>
        <div class="mb-3">
            <form method="GET" action="payroll_approval.php">
                <input type="hidden" name="month" value="<?php echo $selected_month; ?>">
                <input type="hidden" name="year" value="<?php echo $selected_year; ?>">
                <label for="attendance_status" class="form-label">Filter by Approval Status:</label>
                <select name="attendance_status" id="attendance_status" class="form-select" style="width: auto; display: inline-block;" onchange="this.form.submit()">
                    <option value="pending" <?php echo ($approved_attendance === 'pending') ? 'selected' : ''; ?>>Pending</option>
                    <option value="approved" <?php echo ($approved_attendance === 'approved') ? 'selected' : ''; ?>>Approved</option>
                    <option value="all" <?php echo ($approved_attendance === 'all') ? 'selected' : ''; ?>>All</option>
                </select>
            </form>
        </div>
        <div class="table-responsive">
            <table class="table table-bordered table-striped">
                <thead>
                    <tr>
                        <th>#</th>
                        <th>Employee</th>
                        <th>Present Days</th>
                        <th>Absent Days</th>
                        <th>Late Days</th>
                        <th>Total Working Days</th>
                        <th>Approval Status</th>
                        <th>Actions</th>
                    </tr>
                </thead>
                <tbody>
                <?php
                $count = 1;
                while ($row = mysqli_fetch_assoc($attendance_result)) {
                    $fullName = trim($row['first_name'] . ' ' . $row['last_name']) ?: $row['user_id'];
                    $days_present = $row['days_present'] ?? 0;
                    $days_absent = $row['days_absent'] ?? 0;
                    $days_late = $row['days_late'] ?? 0;
                    $total_days = $row['total_days'] ?? 0;
                    $approval_status = $row['approval_status'] ?? 'Pending';
                    echo "<tr>
                            <td>{$count}</td>
                            <td>" . htmlspecialchars($fullName) . "</td>
                            <td>{$days_present}</td>
                            <td>{$days_absent}</td>
                            <td>{$days_late}</td>
                            <td>{$total_days}</td>
                            <td>" . htmlspecialchars(ucfirst($approval_status)) . "</td>
                            <td>
                                <a href='view_attendance.php?user_id={$row['user_id']}&month={$selected_month}&year={$selected_year}' class='btn btn-sm btn-info'>View Details</a>
                            </td>
                          </tr>";
                    $count++;
                }
                mysqli_stmt_close($stmt_attendance);
                if ($count == 1) {
                    echo "<tr><td colspan='8' class='text-center'>No attendance data found for the selected period.</td></tr>";
                }
                ?>
                </tbody>
            </table>
        </div>
    </div>

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