<?php
session_start();
include 'config.php';
require __DIR__ . '/vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\IOFactory;

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

// Restrict access to superadmin, admin, and md roles
if (!isset($_SESSION['role']) || !in_array(strtolower($_SESSION['role']), ['superadmin', 'admin', 'md'])) {
    header("Location: dashboard.php");
    exit();
}

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

// Initialize defaults for month/year
$upload_month = "";
$upload_year = "";
$filter_month = isset($_POST['filter_month']) ? $_POST['filter_month'] : date('n');
$filter_year = isset($_POST['filter_year']) ? $_POST['filter_year'] : date('Y');

// --- Handle Excel Upload ---
if (isset($_POST['upload'])) {
    $file = $_FILES['attendance_file']['tmp_name'];
    $upload_month = isset($_POST['month']) ? $_POST['month'] : "";
    $upload_year = isset($_POST['year']) ? $_POST['year'] : "";

    if (empty($file) || empty($upload_month) || empty($upload_year)) {
        $message = "<div class='alert alert-danger'>Please select a month, year, and file to upload.</div>";
    } else {
        try {
            $spreadsheet = IOFactory::load($file);
            $sheet = $spreadsheet->getActiveSheet();
            $rows = $sheet->toArray();

            for ($i = 1; $i < count($rows); $i++) {
                $row = $rows[$i];
                $employeeId = trim($row[1] ?? ''); // Handle null employee ID
                if (empty($employeeId)) {
                    error_log("Skipping row $i: Empty employee ID");
                    continue;
                }

                // Check user
                $userQuery = $conn->query("SELECT id FROM users WHERE id='$employeeId' OR username='$employeeId'");
                if ($userQuery->num_rows == 0) {
                    error_log("Skipping row $i: Invalid employee ID $employeeId");
                    continue;
                }
                $user = $userQuery->fetch_assoc();
                $userId = $user['id'];

                // Loop through daily attendance columns (IN/OUT)
                for ($j = 3; $j < count($row); $j += 2) {
                    $dateHeader = $rows[0][$j] ?? '';
                    if (empty($dateHeader)) {
                        error_log("Skipping column $j: Empty date header");
                        continue;
                    }

                    // Validate date
                    $attendanceDate = date('Y-m-d', strtotime($dateHeader));
                    if ($attendanceDate === '1970-01-01' || strtotime($dateHeader) === false) {
                        error_log("Skipping column $j: Invalid date format '$dateHeader'");
                        continue;
                    }

                    // Skip if date not in selected month/year
                    if (date('m', strtotime($attendanceDate)) != $upload_month ||
                        date('Y', strtotime($attendanceDate)) != $upload_year) {
                        continue;
                    }

                    $in = trim($row[$j] ?? ''); // Handle null In Time
                    $out = trim($row[$j + 1] ?? ''); // Handle null Out Time
                    $workingHours = 0.00;
                    $lateBy = null;
                    $earlyExit = null;

                    // Validate times
                    if ($in && $out && strtotime($in) !== false && strtotime($out) !== false) {
                        $inTime = strtotime($in);
                        $outTime = strtotime($out);
                        if ($outTime > $inTime) {
                            $workingHours = round(($outTime - $inTime) / 3600, 2);
                            // Check for late arrival
                            if ($inTime > strtotime("09:15:00")) {
                                $diff = $inTime - strtotime("09:15:00");
                                $lateBy = gmdate("H:i:s", $diff);
                            }
                            // Check for early exit
                            if ($outTime < strtotime("18:00:00")) {
                                $diff = strtotime("18:00:00") - $outTime;
                                $earlyExit = gmdate("H:i:s", $diff);
                            }
                        } else {
                            error_log("Invalid hours for row $i, column $j: In=$in, Out=$out");
                            $workingHours = 0.00;
                        }
                    }

                    $status = ($in && $out && strtotime($in) !== false && strtotime($out) !== false && $workingHours > 0) ? 'Present' : 'Absent';

                    // Insert or Update Attendance in attendancetech table
                    $sql = "INSERT INTO attendancetech 
                            (user_id, attendance_date, in_time, out_time, working_hours, late_by, early_exit, status)
                            VALUES ('$userId', '$attendanceDate', " . ($in ? "'$in'" : "NULL") . ", " . ($out ? "'$out'" : "NULL") . ", '$workingHours', 
                                    " . ($lateBy ? "'$lateBy'" : "NULL") . ", 
                                    " . ($earlyExit ? "'$earlyExit'" : "NULL") . ", '$status')
                            ON DUPLICATE KEY UPDATE 
                            in_time=" . ($in ? "'$in'" : "NULL") . ", 
                            out_time=" . ($out ? "'$out'" : "NULL") . ", 
                            working_hours='$workingHours', 
                            late_by=" . ($lateBy ? "'$lateBy'" : "NULL") . ", 
                            early_exit=" . ($earlyExit ? "'$earlyExit'" : "NULL") . ", 
                            status='$status'";
                    $conn->query($sql);
                }
            }

            // --- Payroll Auto Update ---
            $month = "$upload_year-" . str_pad($upload_month, 2, '0', STR_PAD_LEFT);

            $empQuery = $conn->query("
                SELECT user_id, COUNT(*) AS office_working_days,
                       SUM(CASE WHEN status='Present' THEN 1 ELSE 0 END) AS no_of_days_present,
                       SUM(CASE WHEN status='Absent' THEN 1 ELSE 0 END) AS no_of_days_absent
                FROM attendancetech
                WHERE DATE_FORMAT(attendance_date, '%Y-%m') = '$month'
                GROUP BY user_id
            ");

            while ($row = $empQuery->fetch_assoc()) {
                $userId = $row['user_id'];
                $office_working_days = $row['office_working_days'];
                $no_of_days_present = $row['no_of_days_present'];
                $no_of_days_absent = $row['no_of_days_absent'];
                $unpaid_leave = $no_of_days_absent;

                $salaryQ = $conn->query("SELECT basic_salary, first_name, last_name FROM users WHERE id='$userId'");
                if ($salaryQ->num_rows == 0) continue;
                $salaryR = $salaryQ->fetch_assoc();
                $basic_salary = $salaryR['basic_salary'];
                $employee_name = trim($salaryR['first_name'] . ' ' . $salaryR['last_name']) ?: $userId;

                if ($office_working_days > 0) {
                    $hra = $basic_salary * 0.20;
                    $other_allowance = $basic_salary * 0.10;
                    $total_gross_salary = $basic_salary + $hra + $other_allowance;

                    $pf = $basic_salary * 0.12;
                    $esi = $total_gross_salary * 0.0075;
                    $pt = 200;
                    $it = ($total_gross_salary > 25000) ? $total_gross_salary * 0.05 : 0;

                    $total_deductions = $pf + $esi + $pt + $it;

                    $per_day_salary = $total_gross_salary / $office_working_days;
                    $loss_due_to_unpaid = $per_day_salary * $unpaid_leave;
                    $net_earnings = $total_gross_salary - $total_deductions - $loss_due_to_unpaid;

                    $check = $conn->query("SELECT payroll_id FROM payroll WHERE user_id='$userId' AND month_year='$month'");
                    if ($check->num_rows > 0) {
                        $conn->query("
                            UPDATE payroll 
                            SET employee_name='$employee_name', 
                                basic_salary='$basic_salary',
                                hra='$hra',
                                other_allowance='$other_allowance',
                                total_gross_salary='$total_gross_salary',
                                pf='$pf',
                                esi='$esi',
                                pt='$pt',
                                it='$it',
                                total_deductions='$total_deductions',
                                net_earnings='$net_earnings',
                                no_of_days_present='$no_of_days_present',
                                no_of_days_absent='$no_of_days_absent',
                                unpaid_leave='$unpaid_leave',
                                office_working_days='$office_working_days'
                            WHERE user_id='$userId' AND month_year='$month'
                        ");
                    } else {
                        $conn->query("
                            INSERT INTO payroll 
                            (user_id, employee_name, month_year, basic_salary, hra, other_allowance, total_gross_salary, pf, esi, pt, it, total_deductions, net_earnings, no_of_days_present, no_of_days_absent, unpaid_leave, office_working_days)
                            VALUES ('$userId', '$employee_name', '$month', '$basic_salary', '$hra', '$other_allowance', '$total_gross_salary', '$pf', '$esi', '$pt', '$it', '$total_deductions', '$net_earnings', '$no_of_days_present', '$no_of_days_absent', '$unpaid_leave', '$office_working_days')
                        ");
                    }
                }
            }

            // Set filter to uploaded month/year
            $filter_month = $upload_month;
            $filter_year = $upload_year;
            $message = "<div class='alert alert-success'>Attendance for $upload_month/$upload_year imported and payroll updated successfully!</div>";

        } catch (Exception $e) {
            $message = "<div class='alert alert-danger'>Error reading file: " . $e->getMessage() . "</div>";
            error_log("Excel upload error: " . $e->getMessage());
        }
    }
}

// Fetch filtered attendance records from attendancetech
$filter_condition = "";
if ($filter_month && $filter_year) {
    $filter_condition = "WHERE DATE_FORMAT(a.attendance_date, '%Y-%m') = '$filter_year-" . str_pad($filter_month, 2, '0', STR_PAD_LEFT) . "'";
}

$attendance_data = $conn->query("
    SELECT a.user_id, COALESCE(u.first_name, '') AS first_name, COALESCE(u.last_name, '') AS last_name,
           a.attendance_date, a.in_time, a.out_time, a.status, a.working_hours, a.late_by, a.early_exit
    FROM attendancetech a
    LEFT JOIN users u ON a.user_id = u.id
    $filter_condition
    ORDER BY a.attendance_date DESC, u.first_name ASC
");
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Attendance Management</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>
        :root {
            --primary-color: #1e3a8a;
            --secondary-color: #22c55e;
            --accent-color: #f97316;
            --sidebar-bg: #1f2937;
            --card-bg: #ffffff;
            --text-color: #111827;
            --hover-bg: #3b82f6;
        }

        body {
            font-family: 'Inter', system-ui, sans-serif;
            background-color: #f3f4f6;
            margin: 0;
            display: flex;
            min-height: 100vh;
            color: var(--text-color);
        }

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

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

        .sidebar h2 {
            font-size: 1.5rem;
            font-weight: 700;
            margin-bottom: 20px;
            color: var(--secondary-color);
            text-align: center;
        }

        .sidebar a {
            color: #ffffff;
            text-decoration: none;
            padding: 12px 15px;
            display: flex;
            align-items: center;
            gap: 10px;
            border-radius: 8px;
            margin-bottom: 8px;
            font-size: 1rem;
            transition: background 0.2s ease;
        }

        .sidebar a:hover, .sidebar a.active {
            background: var(--hover-bg);
        }

        .sidebar .dropdown-menu {
            background: #374151;
            border: none;
            padding: 8px 0;
            width: 100%;
            margin-left: 10px;
            border-radius: 6px;
        }

        .sidebar .dropdown-menu a {
            color: #ffffff;
            padding: 10px 25px;
            font-size: 0.95rem;
        }

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

        .sidebar .dropdown-toggle::after {
            margin-left: auto;
        }

        .hamburger {
            position: fixed;
            top: 15px;
            left: 15px;
            z-index: 1100;
            background: var(--primary-color);
            color: #ffffff;
            border: none;
            padding: 10px;
            border-radius: 8px;
            cursor: pointer;
            transition: background 0.2s ease;
        }

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

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

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

        h1 {
            font-size: 2rem;
            font-weight: 800;
            margin-bottom: 20px;
            color: var(--primary-color);
            text-align: center;
        }

        h4 {
            font-size: 1.25rem;
            color: var(--primary-color);
            margin: 20px 0 15px;
        }

        .card {
            background: var(--card-bg);
            border-radius: 12px;
            box-shadow: 0 4px 20px rgba(0, 0, 0, 0.05);
            margin-bottom: 30px;
        }

        .card-body {
            padding: 25px;
        }

        .table {
            width: 100%;
            border-collapse: separate;
            border-spacing: 0;
            background: var(--card-bg);
            border-radius: 12px;
            overflow: hidden;
            box-shadow: 0 4px 20px rgba(0, 0, 0, 0.05);
        }

        .table th, .table td {
            padding: 15px;
            border: 1px solid #e5e7eb;
            text-align: left;
            font-size: 0.95rem;
        }

        .table th {
            background: var(--primary-color);
            color: #ffffff;
            font-weight: 700;
        }

        .table tr:nth-child(even) {
            background: #f9fafb;
        }

        .table tr:hover {
            background: #e0f2fe;
        }

        .form-select, .form-control {
            padding: 10px;
            border: 1px solid #e5e7eb;
            border-radius: 8px;
            font-size: 0.95rem;
        }

        .btn {
            padding: 10px 15px;
            border: none;
            border-radius: 8px;
            font-size: 0.95rem;
            transition: background 0.2s ease;
        }

        .btn-success {
            background: var(--secondary-color);
            color: #ffffff;
        }

        .btn-primary {
            background: var(--primary-color);
            color: #ffffff;
        }

        .btn:hover {
            background: var(--hover-bg);
        }

        .alert {
            border-radius: 8px;
        }

        .table-responsive {
            overflow-x: auto;
        }

        @media (max-width: 576px) {
            .sidebar {
                width: 240px;
                transform: translateX(-240px);
            }

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

            h1 {
                font-size: 1.5rem;
            }

            h4 {
                font-size: 1rem;
            }

            .table {
                font-size: 0.85rem;
                display: block;
                overflow-x: auto;
            }

            .form-select, .form-control, .btn {
                font-size: 0.85rem;
                padding: 8px;
            }

            .card-body {
                padding: 15px;
            }
        }
    </style>
</head>
<body>
    <button class="hamburger" onclick="toggleSidebar()"><i class="fas fa-bars"></i></button>
    <!-- Sidebar -->
    <div class="sidebar" id="sidebar">
        <h2>Mypeopleconnect</h2>
        <a class="dropdown-toggle active" href="<?php echo (strtolower($_SESSION['role']) === 'md') ? 'dashboard_md.php' : 'dashboard_admin.php'; ?>" aria-expanded="false">
            <i class="fas fa-home me-2"></i> <?php echo (strtolower($_SESSION['role']) === 'md') ? 'MD Dashboard' : 'Organisation Chart'; ?>
        </a>
        <div class="dropdown">
            <a class="dropdown-toggle" href="#" role="button" data-bs-toggle="dropdown" aria-expanded="false">
                <i class="fas fa-home me-2"></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 me-2"></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" 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 me-2"></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 me-2"></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 me-2"></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 me-2"></i> Logout</a>
    </div>

    <div class="content" id="content">
        <h1>Attendance Management</h1>

        <div class="card mb-4">
            <div class="card-body">
                <form method="post" enctype="multipart/form-data">
                    <div class="row">
                        <div class="col-md-3">
                            <label class="form-label">Select Month</label>
                            <select name="month" class="form-select" required>
                                <option value="">-- Month --</option>
                                <?php
                                for ($m = 1; $m <= 12; $m++) {
                                    $selected = ($m == date('n')) ? 'selected' : '';
                                    echo "<option value='$m' $selected>" . date('F', mktime(0,0,0,$m,1)) . "</option>";
                                }
                                ?>
                            </select>
                        </div>
                        <div class="col-md-3">
                            <label class="form-label">Select Year</label>
                            <select name="year" class="form-select" required>
                                <option value="">-- Year --</option>
                                <?php
                                $currentYear = date('Y');
                                for ($y = $currentYear; $y >= $currentYear - 5; $y--) {
                                    echo "<option value='$y'>$y</option>";
                                }
                                ?>
                            </select>
                        </div>
                        <div class="col-md-6">
                            <label for="attendance_file" class="form-label">Upload Excel Sheet (.xls / .xlsx)</label>
                            <input type="file" name="attendance_file" id="attendance_file" class="form-control" accept=".xls,.xlsx" required>
                            <button type="submit" name="upload" class="btn btn-success mt-3"><i class="fas fa-upload"></i> Upload</button>
                        </div>
                    </div>
                </form>
                <?php echo $message; ?>
            </div>
        </div>

        <div class="card mb-4">
            <div class="card-body">
                <h4>Filter Attendance</h4>
                <form method="post">
                    <div class="row">
                        <div class="col-md-3">
                            <label class="form-label">Filter by Month</label>
                            <select name="filter_month" class="form-select">
                                <option value="">-- Month --</option>
                                <?php
                                for ($m = 1; $m <= 12; $m++) {
                                    $selected = ($m == $filter_month) ? 'selected' : '';
                                    echo "<option value='$m' $selected>" . date('F', mktime(0,0,0,$m,1)) . "</option>";
                                }
                                ?>
                            </select>
                        </div>
                        <div class="col-md-3">
                            <label class="form-label">Filter by Year</label>
                            <select name="filter_year" class="form-select">
                                <option value="">-- Year --</option>
                                <?php
                                $currentYear = date('Y');
                                for ($y = $currentYear; $y >= $currentYear - 5; $y--) {
                                    $selected = ($y == $filter_year) ? 'selected' : '';
                                    echo "<option value='$y' $selected>$y</option>";
                                }
                                ?>
                            </select>
                        </div>
                        <div class="col-md-3 align-self-end">
                            <button type="submit" name="filter" class="btn btn-primary"><i class="fas fa-filter"></i> Filter</button>
                        </div>
                    </div>
                </form>
            </div>
        </div>

        <h4>Attendance Summary</h4>
        <div class="table-responsive">
            <table class="table table-bordered table-hover">
                <thead>
                    <tr>
                        <th>S.No</th>
                        <th>Employee</th>
                        <th>Date</th>
                        <th>In Time</th>
                        <th>Out Time</th>
                        <th>Status</th>
                        <th>Hours</th>
                        <th>Late By</th>
                        <th>Early Exit</th>
                    </tr>
                </thead>
                <tbody>
                    <?php 
                    $sno = 1;
                    while($row = $attendance_data->fetch_assoc()):
                        $fullname = trim($row['first_name'] . ' ' . $row['last_name']) ?: $row['user_id'];
                    ?>
                    <tr>
                        <td><?= $sno++; ?></td>
                        <td><?= htmlspecialchars($fullname); ?></td>
                        <td><?= date("d-m-Y", strtotime($row['attendance_date'])); ?></td>
                        <td><?= $row['in_time'] ?: '-'; ?></td>
                        <td><?= $row['out_time'] ?: '-'; ?></td>
                        <td><?= $row['status']; ?></td>
                        <td><?= number_format($row['working_hours'], 2); ?></td>
                        <td><?= $row['late_by'] ?: '-'; ?></td>
                        <td><?= $row['early_exit'] ?: '-'; ?></td>
                    </tr>
                    <?php endwhile; ?>
                </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>