<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);
session_start();
include 'config.php';

if (!function_exists('db_query')) die("FATAL: db_query() missing – check config.php");
if (!isset($conn) || $conn->connect_error) die("FATAL: DB not connected – check config.php");

require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\IOFactory;

// GET MONTH / YEAR
$month = $_POST['month'] ?? $_GET['month'] ?? date('n');
$year  = $_POST['year']  ?? $_GET['year']  ?? date('Y');
$message = '';
$user_role = $_SESSION['role'] ?? 'employee';
$payslip_dir = 'payslips/';
if (!is_dir($payslip_dir)) mkdir($payslip_dir, 0755, true);

/* -----------------------------------------------------------------
   IMPORT EXCEL
----------------------------------------------------------------- */
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_FILES['excel_file'])) {
    if ($_FILES['excel_file']['error'] === UPLOAD_ERR_OK) {
        $filePath = $_FILES['excel_file']['tmp_name'];
        $spreadsheet = IOFactory::load($filePath);
        $sheet = $spreadsheet->getActiveSheet();
        $highestRow = $sheet->getHighestRow();
        $inserted = 0;
        $errors = [];

        db_query($conn, "DELETE FROM payroll_belief WHERE month = ? AND year = ?", [$month, $year]);

        for ($row = 2; $row <= $highestRow; $row++) {
            $emp_name_excel = trim($sheet->getCell("A$row")->getFormattedValue() ?? '');
            if (empty($emp_name_excel)) {
                $errors[] = "Row $row: Missing name";
                continue;
            }

            $userRes = db_query($conn, "SELECT id, first_name FROM users WHERE LOWER(first_name) LIKE LOWER(?) LIMIT 1", ["%$emp_name_excel%"]);
            $userRow = $userRes ? $userRes->fetch_assoc() : null;
            if (!$userRow) {
                $errors[] = "Row $row: Name <strong>$emp_name_excel</strong> not found";
                continue;
            }

            $emp_id = $userRow['id'];
            $emp_name = $userRow['first_name'];

            $getVal = fn($col) => (float) preg_replace('/[^\d.-]/', '', $sheet->getCell("$col$row")->getFormattedValue());
            $getInt = fn($col) => (int) preg_replace('/[^\d]/', '', $sheet->getCell("$col$row")->getFormattedValue());

            $data = [
                $emp_id, $emp_name,
                $getVal('C'), $getVal('D'), $getVal('E'), $getVal('F'), $getVal('G'), $getVal('H'), $getVal('I'),
                $getInt('J'), $getInt('K'), $getInt('L'), $getInt('M'), $getInt('N'), $getInt('O'), $getInt('P'),
                $getVal('Q'), $getVal('R'), $getVal('S'), $getVal('T'), $getVal('U'), $getVal('V'), $getVal('W'), $getVal('X'),
                $getVal('Y'), $getVal('Z'), $getVal('AA'), $getVal('AB'), $getVal('AC'), $getVal('AD'), $getVal('AE'), $getVal('AF'),
                $getVal('AG'), $getVal('AH'), $getVal('AI'), $getVal('AJ'),
                $month, $year, 'draft'
            ];

            $placeholders = str_repeat('?,', 38) . '?';
            $sql = "INSERT INTO payroll_belief (
                        employee_id, employee_name, gross_salary, basic, hra, conv, med_reimb, special_allowances, gross_salary_2,
                        no_of_cal_days, no_of_working_days, no_of_paid_days, lop_days, casual_leave, earned_leave, medical_leave,
                        basic_da, hra_2, conv_2, med_reimb_2, others, gross_earnings, others_add, total_gross_earnings,
                        pf, esi, tax, adv, cant, med_claim, loan, variable_pay, prof_tax, bank_deposit, total_ded, net_pay,
                        month, year, status
                    ) VALUES ($placeholders)";

            $stmt = $conn->prepare($sql);
            if ($stmt) {
                $types = 'ssdddddddiiiiiiidddddddddddddddddddddis';
                $stmt->bind_param($types, ...$data);
                $stmt->execute();
                $inserted++;
            }
        }

        $message = "Payroll imported – $inserted record(s)";
        if (!empty($errors)) {
            $message .= "<br><strong>Skipped rows:</strong><ul><li>" . implode("</li><li>", $errors) . "</li></ul>";
        }
    } else {
        $message = "Upload error.";
    }
}

/* -----------------------------------------------------------------
   ACTIONS: Send to MD / Release Payslips
----------------------------------------------------------------- */
if (isset($_POST['action'], $_POST['month'], $_POST['year'])) {
    $m = (int)$_POST['month'];
    $y = (int)$_POST['year'];

    if ($_POST['action'] === 'send_md' && in_array($user_role, ['admin', 'superadmin'])) {
        $total = $conn->query("SELECT COUNT(*) AS c FROM payroll_belief WHERE month=$m AND year=$y")->fetch_assoc()['c'];
        if ($total > 0) {
            db_query($conn, "UPDATE payroll_belief SET status='pending' WHERE month=? AND year=?", [$m, $y]);
            $message = "Payroll sent to MD for approval!";
        } else {
            $message = "No data to send.";
        }
    }

    if ($_POST['action'] === 'release_payslips' && in_array($user_role, ['admin', 'superadmin'])) {
        $total = $conn->query("SELECT COUNT(*) AS c FROM payroll_belief WHERE month=$m AND year=$y")->fetch_assoc()['c'];
        $approved = $conn->query("SELECT COUNT(*) AS c FROM payroll_belief WHERE month=$m AND year=$y AND status='approved'")->fetch_assoc()['c'];
        if ($total > 0 && $approved == $total) {
            db_query($conn, "UPDATE payroll_belief SET status='released' WHERE month=? AND year=?", [$m, $y]);
            $message = "Payslips Released! <a href='generate_payslips.php?month=$m&year=$y' target='_blank' class='alert-link'>Download All PDFs</a>";
        } else {
            $message = "Cannot release: Not all salaries are approved by MD.";
        }
    }
}

/* -----------------------------------------------------------------
   FETCH CURRENT PAYROLL + STATUS
----------------------------------------------------------------- */
$result = $conn->query("SELECT * FROM payroll_belief WHERE month=$month AND year=$year ORDER BY employee_id");
$total_payout = $conn->query("SELECT SUM(net_pay) AS total FROM payroll_belief WHERE month=$month AND year=$year")->fetch_assoc()['total'] ?? 0;

// Smart status detection
$status_check = $conn->query("SELECT status, COUNT(*) AS cnt FROM payroll_belief WHERE month=$month AND year=$year GROUP BY status ORDER BY cnt DESC LIMIT 1");
$current_status = $status_check && $status_check->num_rows ? $status_check->fetch_assoc()['status'] : 'none';

// History
$history_res = $conn->query("SELECT DISTINCT month, year, status FROM payroll_belief ORDER BY year DESC, month DESC");
$uploaded_months = [];
while ($h = $history_res->fetch_assoc()) {
    $uploaded_months[] = $h;
}
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Payroll – <?= date('F Y', mktime(0,0,0,$month,1,$year)) ?></title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.4.0/css/all.min.css">
    <style>
        :root {--primary:#2c3e50;--success:#1abc9c;--info:#3498db;--light:#f8f9fa;}
        body {font-family:'Segoe UI',sans-serif;background:var(--light);display:flex;min-height:100vh;}
        .sidebar {width:250px;background:var(--primary);color:#fff;padding:15px;position:fixed;top:0;bottom:0;z-index:1000;}
        .sidebar h2 {text-align:center;margin-bottom:20px;font-size:1.2rem;}
        .sidebar a {color:#fff;text-decoration:none;display:block;padding:10px;border-radius:6px;margin-bottom:5px;}
        .sidebar a:hover, .sidebar a.active {background:var(--success);}
        .content {margin-left:260px;padding:20px;width:100%;}
        .card {box-shadow:0 2px 8px rgba(0,0,0,0.1);}
        .action-btns {display:flex;gap:8px;flex-wrap:wrap;align-items:center;margin:15px 0;}
        .history-list a {color:var(--primary);font-weight:500;}
        .history-list a:hover {text-decoration:underline;}
        .table th {background:var(--primary);color:white;}
    </style>
</head>
<body>

<div class="sidebar">
    <h2>Mypeopleconnect</h2>
    <a href="dashboard_admin.php">Organisation Chart</a>
    <a href="manage_users.php">Employee Details</a>
    <a href="attendanceexcel.php">Attendance Excel</a>
    <a href="payroll_excel.php" class="active bg-success text-white">Payroll Excel</a>
    <a href="payroll_dashboard.php">Payroll</a>
    <a href="hr_approval_salary.php">Advance</a>
    <a href="logout.php" class="text-danger">Logout</a>
</div>

<div class="content">
    <div class="card p-4 mb-4">
        <h1 class="h4">Payroll – <?= date('F Y', mktime(0,0,0,$month,1,$year)) ?></h1>
        <?php if ($message): ?>
            <div class="alert <?= strpos($message,'Cannot')!==false ? 'alert-danger' : 'alert-success' ?> alert-dismissible fade show">
                <?= $message ?>
                <button type="button" class="btn-close" data-bs-dismiss="alert"></button>
            </div>
        <?php endif; ?>
        <div class="d-flex justify-content-between align-items-center mb-3">
            <div>
                <strong>Employees:</strong> <?= $result->num_rows ?> |
                <strong>Payout:</strong> ₹ <?= number_format($total_payout, 0) ?>
            </div>
            <div>
                <span class="badge bg-<?= $current_status=='released'?'success':($current_status=='approved'?'info':($current_status=='pending'?'warning':'secondary')) ?> fs-6">
                    <?= ucfirst($current_status) ?>
                </span>
            </div>
        </div>
    </div>

    <!-- Import + Actions -->
    <div class="card p-3 mb-4">
        <h2 class="h5">Import Payroll Excel</h2>
        <form method="POST" enctype="multipart/form-data" class="row g-2 align-items-center mb-3">
            <div class="col-auto">
                <input type="file" name="excel_file" class="form-control form-control-sm" accept=".xlsx,.xls" required>
            </div>
            <div class="col-auto">
                <select name="month" class="form-select form-control-sm">
                    <?php for($m=1;$m<=12;$m++): ?>
                        <option value="<?= $m ?>" <?= $m==$month?'selected':'' ?>><?= date('F',mktime(0,0,0,$m)) ?></option>
                    <?php endfor; ?>
                </select>
            </div>
            <div class="col-auto">
                <select name="year" class="form-select form-control-sm">
                    <?php for($y=date('Y');$y>=2020;$y--): ?>
                        <option value="<?= $y ?>" <?= $y==$year?'selected':'' ?>><?= $y ?></option>
                    <?php endfor; ?>
                </select>
            </div>
            <div class="col-auto">
                <button type="submit" class="btn btn-success btn-sm">Import Excel</button>
            </div>
        </form>

        <div class="action-btns">
            <?php if ($current_status === 'draft'): ?>
                <form method="POST" class="d-inline">
                    <input type="hidden" name="action" value="send_md">
                    <input type="hidden" name="month" value="<?= $month ?>">
                    <input type="hidden" name="year" value="<?= $year ?>">
                    <button type="submit" class="btn btn-warning btn-sm" onclick="return confirm('Send payroll to MD for approval?')">
                        Send to MD
                    </button>
                </form>
            <?php elseif ($current_status === 'pending'): ?>
                <span class="badge bg-warning">Pending MD Approval</span>
                <a href="payroll_md_dashboard.php?month=<?= $month ?>&year=<?= $year ?>" class="btn btn-outline-info btn-sm">MD Review</a>
            <?php elseif ($current_status === 'approved' && in_array($user_role, ['admin','superadmin'])): ?>
                <form method="POST" class="d-inline">
                    <input type="hidden" name="action" value="release_payslips">
                    <input type="hidden" name="month" value="<?= $month ?>">
                    <input type="hidden" name="year" value="<?= $year ?>">
                    <button type="submit" class="btn btn-success btn-sm" onclick="return confirm('Release payslips to all employees?')">
                        Release Payslips
                    </button>
                </form>
                <a href="generate_payslips.php?month=<?= $month ?>&year=<?= $year ?>" class="btn btn-info btn-sm" target="_blank">
                    Download PDFs
                </a>
            <?php elseif ($current_status === 'released'): ?>
                <span class="badge bg-success">Payslips Released</span>
                <a href="generate_payslips.php?month=<?= $month ?>&year=<?= $year ?>" class="btn btn-outline-success btn-sm" target="_blank">
                    Re-Download
                </a>
            <?php endif; ?>
            <a href="view_old_salary.php" class="btn btn-primary btn-sm">View Old Salary</a>
        </div>
    </div>

    <!-- Payroll Table -->
    <?php if ($result->num_rows > 0): ?>
    <div class="card p-3">
        <div class="d-flex justify-content-between mb-3">
            <h2 class="h5">Payroll Details</h2>
            <button class="btn btn-info btn-sm" onclick="document.getElementById('full-table').classList.toggle('d-none')">
                Toggle Full Table
            </button>
        </div>
        <div id="full-table" class="<?= $result->num_rows > 10 ? 'd-none' : '' ?>">
            <div class="table-responsive">
                <table class="table table-sm table-bordered table-striped">
                    <thead>
                        <tr>
                            <th>#</th><th>ID</th><th>Name</th><th>Gross</th><th>Basic</th><th>HRA</th><th>Conv</th><th>Net Pay</th><th>Status</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php $i=1; $result->data_seek(0); while ($r = $result->fetch_assoc()): ?>
                        <tr>
                            <td><?= $i++ ?></td>
                            <td><?= htmlspecialchars($r['employee_id']) ?></td>
                            <td><?= htmlspecialchars($r['employee_name']) ?></td>
                            <td><?= number_format($r['gross_salary'], 2) ?></td>
                            <td><?= number_format($r['basic'], 2) ?></td>
                            <td><?= number_format($r['hra'], 2) ?></td>
                            <td><?= number_format($r['conv'], 2) ?></td>
                            <td class="text-success fw-bold"><?= number_format($r['net_pay'], 2) ?></td>
                            <td>
                                <span class="badge bg-<?= $r['status']=='released'?'success':($r['status']=='approved'?'info':($r['status']=='pending'?'warning':'secondary')) ?>">
                                    <?= ucfirst($r['status']) ?>
                                </span>
                            </td>
                        </tr>
                        <?php endwhile; ?>
                    </tbody>
                </table>
            </div>
        </div>
    </div>
    <?php endif; ?>

    <!-- Upload History -->
    <?php if (!empty($uploaded_months)): ?>
    <div class="card p-3 mt-4">
        <h2 class="h5">Upload History (Click to View)</h2>
        <ul class="list-unstyled history-list">
            <?php foreach ($uploaded_months as $h): ?>
            <li class="d-flex justify-content-between border-bottom pb-2 mb-2">
                <a href="?month=<?= $h['month'] ?>&year=<?= $h['year'] ?>">
                    <strong><?= date('F Y', mktime(0,0,0,$h['month'],1,$h['year'])) ?></strong>
                </a>
                <span class="badge bg-<?= $h['status']=='released'?'success':($h['status']=='approved'?'info':($h['status']=='pending'?'warning':'secondary')) ?>">
                    <?= ucfirst($h['status']) ?>
                </span>
            </li>
            <?php endforeach; ?>
        </ul>
    </div>
    <?php endif; ?>
</div>

<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/js/bootstrap.bundle.min.js"></script>
</body>
</html>