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

// === AUTHENTICATION ===
if (!isset($_SESSION['user_id'])) {
    header("Location: index.php");
    exit();
}
$admin_id = $_SESSION['user_id'];
$username = $_SESSION['username'] ?? 'Admin';

// === MONTH & YEAR SELECTION ===
$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
if ($selected_month < 1 || $selected_month > 12) $selected_month = $current_month;
if ($selected_year < 2000 || $selected_year > 2100) $selected_year = $current_year;

// === AUTO-CREATE TABLES & COLUMNS ===
function ensureTableExists($conn, $table, $create_sql) {
    $check = mysqli_query($conn, "SHOW TABLES LIKE '$table'");
    if (mysqli_num_rows($check) == 0 && !mysqli_query($conn, $create_sql)) {
        die("Failed to create $table: " . mysqli_error($conn));
    }
}

function ensureColumnExists($conn, $table, $column, $add_sql) {
    $check = mysqli_query($conn, "SHOW COLUMNS FROM `$table` LIKE '$column'");
    if (mysqli_num_rows($check) == 0 && !mysqli_query($conn, $add_sql)) {
        die("Failed to add $column to $table: " . mysqli_error($conn));
    }
}

// payroll_records
ensureTableExists($conn, 'payroll_records', "
    CREATE TABLE payroll_records (
        id INT AUTO_INCREMENT PRIMARY KEY,
        employee_name VARCHAR(255) NOT NULL,
        month INT NOT NULL,
        employee_id INT NOT NULL,
        year INT NOT NULL,
        UNIQUE KEY unique_payroll (employee_id, month, year),
        basic_salary DECIMAL(10,2) DEFAULT 0.00,
        hra DECIMAL(10,2) DEFAULT 0.00,
        other_allowance DECIMAL(10,2) DEFAULT 0.00,
        total_gross_salary DECIMAL(10,2) DEFAULT 0.00,
        it DECIMAL(10,2) DEFAULT 0.00,
        pt DECIMAL(10,2) DEFAULT 0.00,
        pf DECIMAL(10,2) DEFAULT 0.00,
        vpf DECIMAL(10,2) DEFAULT 0.00,
        esi DECIMAL(10,2) DEFAULT 0.00,
        loan_principal DECIMAL(10,2) DEFAULT 0.00,
        loan_interest DECIMAL(10,2) DEFAULT 0.00,
        salary_advance DECIMAL(10,2) DEFAULT 0.00,
        late_hours INT DEFAULT 0,
        unpaid_leave INT DEFAULT 0,
        other_deductions DECIMAL(10,2) DEFAULT 0.00,
        total_deductions DECIMAL(10,2) DEFAULT 0.00,
        net_earnings DECIMAL(10,2) DEFAULT 0.00,
        office_working_days INT DEFAULT 0,
        no_of_absent INT DEFAULT 0,
        no_of_present INT DEFAULT 0,
        unpaid_leave_difference INT DEFAULT 0,
        status ENUM('draft','pending','approved','sent') DEFAULT 'draft',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        approved_by INT DEFAULT NULL,
        sent_to_employee TINYINT(1) DEFAULT 0
    ) ENGINE=InnoDB
");

// sent_to_employee
ensureColumnExists($conn, 'payroll_records', 'sent_to_employee', 
    "ALTER TABLE payroll_records ADD COLUMN sent_to_employee TINYINT(1) DEFAULT 0");

// notifications
ensureTableExists($conn, 'notifications', "
    CREATE TABLE notifications (
        id INT AUTO_INCREMENT PRIMARY KEY,
        employee_id INT NOT NULL,
        message TEXT NOT NULL,
        status ENUM('Unread','Read') DEFAULT 'Unread',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        INDEX(idx_employee) (employee_id)
    ) ENGINE=InnoDB
");

// bank details in users
ensureColumnExists($conn, 'users', 'bank_name', "
    ALTER TABLE users 
    ADD COLUMN bank_name VARCHAR(255) NULL,
    ADD COLUMN account_number VARCHAR(50) NULL,
    ADD COLUMN ifsc_code VARCHAR(20) NULL
");

// === ACTION HANDLER ===
$message = '';
if ($_POST['action'] ?? false) {
    $action = $_POST['action'];

    // Helper: Update status
    $update = function($status, $extra = '') use ($conn, $selected_month, $selected_year, &$message) {
        $sql = "UPDATE payroll_records SET status = ? $extra WHERE month = ? AND year = ?";
        $stmt = mysqli_prepare($conn, $sql);
        mysqli_stmt_bind_param($stmt, "sii", $status, $selected_month, $selected_year);
        $ok = mysqli_stmt_execute($stmt);
        $msg = $ok ? ucfirst($status) . " update successful." : "Failed: " . mysqli_stmt_error($stmt);
        $message = $msg;
        mysqli_stmt_close($stmt);
        return $ok;
    };

    if ($action === 'send_for_approval') {
        $update('pending');
    }

    elseif ($action === 'send_to_employees') {
        $update('sent', ', sent_to_employee = 1');
    }

    elseif ($action === 'release_salary_slips') {
        if ($update('sent', ', sent_to_employee = 1')) {
            $month_name = date('F', mktime(0,0,0,$selected_month,1));
            $notif_sql = "INSERT INTO notifications (employee_id, message, status)
                          SELECT u.id, CONCAT('Your payslip for $month_name $selected_year is ready!'), 'Unread'
                          FROM users u
                          JOIN payroll_records p ON u.id = p.employee_id
                          WHERE p.month = ? AND p.year = ? AND p.status = 'sent' AND u.role = 'employee' AND u.status = 'active'";
            $stmt = mysqli_prepare($conn, $notif_sql);
            mysqli_stmt_bind_param($stmt, "ii", $selected_month, $selected_year);
            mysqli_stmt_execute($stmt);
            $count = mysqli_stmt_affected_rows($stmt);
            $message .= " Notifications sent to $count employees.";
            mysqli_stmt_close($stmt);
        }
    }

    elseif ($action === 'download_bank_details') {
        $sql = "SELECT u.first_name, u.last_name, u.bank_name, u.account_number, u.ifsc_code, p.net_earnings
                FROM users u
                LEFT JOIN payroll_records p ON u.id = p.employee_id AND p.month = ? AND p.year = ?
                WHERE u.status = 'active' AND u.role != 'superadmin'";
        $stmt = mysqli_prepare($conn, $sql);
        mysqli_stmt_bind_param($stmt, "ii", $selected_month, $selected_year);
        mysqli_stmt_execute($stmt);
        $res = mysqli_stmt_get_result($stmt);

        header('Content-Type: text/csv');
        header('Content-Disposition: attachment; filename="bank_details_' . $selected_month . '_' . $selected_year . '.csv"');
        $out = fopen('php://output', 'w');
        fputcsv($out, ['Name', 'Bank', 'Account No', 'IFSC', 'Net Salary']);
        while ($r = mysqli_fetch_assoc($res)) {
            fputcsv($out, [
                $r['first_name'] . ' ' . $r['last_name'],
                $r['bank_name'] ?? '',
                $r['account_number'] ?? '',
                $r['ifsc_code'] ?? '',
                $r['net_earnings'] ?? '0.00'
            ]);
        }
        fclose($out);
        mysqli_stmt_close($stmt);
        exit();
    }
}

// === EMPLOYEE COUNT & STATUS SUMMARY ===
$total_employees = (int)mysqli_fetch_assoc(mysqli_query($conn, 
    "SELECT COUNT(*) as total FROM users WHERE status = 'active' AND role != 'superadmin'"
))['total'];

$status_sql = "SELECT status, COUNT(*) as count FROM payroll_records 
               WHERE month = ? AND year = ? GROUP BY status";
$status_stmt = mysqli_prepare($conn, $status_sql);
mysqli_stmt_bind_param($status_stmt, "ii", $selected_month, $selected_year);
mysqli_stmt_execute($status_stmt);
$status_res = mysqli_stmt_get_result($status_stmt);

$statuses = ['draft' => 0, 'pending' => 0, 'approved' => 0, 'sent' => 0];
$processed = 0;
while ($row = mysqli_fetch_assoc($status_res)) {
    $statuses[$row['status']] = (int)$row['count'];
    $processed += $row['count'];
}
mysqli_stmt_close($status_stmt);

$missing = $total_employees - $processed;
$has_draft = $statuses['draft'] > 0 || $missing > 0;
$all_approved = $statuses['approved'] == $total_employees && $total_employees > 0;
$all_sent = $statuses['sent'] == $total_employees && $total_employees > 0;
$show_release = $statuses['approved'] > 0 && !$all_sent;

// === FETCH EMPLOYEE PAYROLL DATA ===
$data_sql = "SELECT u.id, u.first_name, u.last_name, u.designation, u.division, u.location, u.basic_salary,
                    p.total_gross_salary, p.net_earnings, p.status
             FROM users u
             LEFT JOIN payroll_records p ON u.id = p.employee_id AND p.month = ? AND p.year = ?
             WHERE u.status = 'active' AND u.role != 'superadmin'
             ORDER BY u.first_name";
$data_stmt = mysqli_prepare($conn, $data_sql);
mysqli_stmt_bind_param($data_stmt, "ii", $selected_month, $selected_year);
mysqli_stmt_execute($data_stmt);
$data_result = mysqli_stmt_get_result($data_stmt);
?>

<!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 Dashboard</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: #f4f4f4; margin: 0; padding: 0; display: flex; min-height: 100vh; }
        .sidebar { width: 250px; background: #2c3e50; color: #fff; padding: 15px; position: fixed; top: 0; bottom: 0; z-index: 1000; transform: translateX(-250px); transition: 0.3s; }
        .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, .sidebar a.active { background: #1abc9c; }
        .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: 0.3s; }
        .content.active { margin-left: 250px; }
        h1 { font-size: 1.5rem; color: #333; }
        .status-badge { font-size: 0.8rem; padding: 4px 8px; border-radius: 12px; }
        .table-responsive { overflow-x: auto; }
        .btn-group { gap: 0.5rem; flex-wrap: wrap; }
        @media (max-width: 576px) {
            h1 { font-size: 1.2rem; }
            .btn { font-size: 0.8rem; padding: 0.4rem 0.6rem; }
        }
    </style>
</head>
<body>
    <!-- Hamburger -->
    <button class="hamburger" onclick="toggleSidebar()">Menu</button>

    <!-- Sidebar -->
    <div class="sidebar" id="sidebar">
        <h2>Mypeopleconnect</h2>
        <a href="dashboard_admin.php" class="active">Organisation Chart</a>
        <!-- Keep your existing menu items -->
        <div class="dropdown">
            <a href="#" data-bs-toggle="dropdown">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</a></li>
                <li><a class="dropdown-item" href="digital.php">Acceptance</a></li>
            </ul>
        </div>
        <div class="dropdown">
            <a href="#" data-bs-toggle="dropdown">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</a></li>
            </ul>
        </div>
        <a href="logout.php" class="text-danger">Logout</a>
    </div>

    <!-- Main Content -->
    <div class="content" id="content">
        <h1 class="text-primary text-center mb-4">Payroll Dashboard</h1>

        <!-- Messages -->
        <?php if ($message): ?>
            <div class="alert alert-info alert-dismissible fade show">
                <?= htmlspecialchars($message) ?>
                <button type="button" class="btn-close" data-bs-dismiss="alert"></button>
            </div>
        <?php endif; ?>

        <!-- Month/Year Selector -->
        <form method="GET" class="mb-4">
            <div class="row g-3 align-items-end">
                <div class="col-auto">
                    <label class="form-label">Month</label>
                    <select name="month" class="form-select" onchange="this.form.submit()">
                        <?php for ($m=1; $m<=12; $m++): 
                            $name = date('F', mktime(0,0,0,$m,1));
                            $sel = $m == $selected_month ? 'selected' : '';
                            echo "<option value='$m' $sel>$name</option>";
                        endfor; ?>
                    </select>
                </div>
                <div class="col-auto">
                    <label class="form-label">Year</label>
                    <select name="year" class="form-select" onchange="this.form.submit()">
                        <?php for ($y=$current_year-5; $y<=$current_year+5; $y++): 
                            $sel = $y == $selected_year ? 'selected' : '';
                            echo "<option value='$y' $sel>$y</option>";
                        endfor; ?>
                    </select>
                </div>
            </div>
        </form>

        <!-- Action Buttons -->
        <form method="POST" class="mb-4 btn-group">
            <input type="hidden" name="month" value="<?= $selected_month ?>">
            <input type="hidden" name="year" value="<?= $selected_year ?>">

            <?php if ($has_draft || $missing > 0): ?>
                <button type="submit" name="action" value="send_for_approval" class="btn btn-primary">
                    Send for MD Approval
                </button>
            <?php endif; ?>

            <button type="submit" name="action" value="download_bank_details" class="btn btn-secondary">
                Download Bank CSV
            </button>

            <?php if ($show_release): ?>
                <button type="submit" name="action" value="release_salary_slips" class="btn btn-warning">
                    Release Payslips
                </button>
            <?php endif; ?>

            <?php if ($all_approved || $all_sent): ?>
                <a href="generate_report.php?month=<?= $selected_month ?>&year=<?= $selected_year ?>" 
                   class="btn btn-success">Generate Report</a>
                <?php if (!$all_sent): ?>
                    <button type="submit" name="action" value="send_to_employees" class="btn btn-info">
                        Send to Employees
                    </button>
                <?php endif; ?>
            <?php else: ?>
                <button class="btn btn-success" disabled>Generate Report</button>
            <?php endif; ?>
        </form>

        <!-- Status Summary -->
        <div class="row mb-3 text-center">
            <div class="col">
                <strong>Total:</strong> <?= $total_employees ?><br>
                <small class="text-muted">Active Employees</small>
            </div>
            <div class="col">
                <strong>Processed:</strong> <?= $processed ?><br>
                <small class="text-muted">Payroll Entries</small>
            </div>
            <div class="col">
                <strong>Missing:</strong> <?= $missing ?><br>
                <small class="text-danger">Need Processing</small>
            </div>
        </div>

        <!-- Add New Payroll Button -->
        <div class="mb-3">
            <a href="select_employee.php?month=<?= $selected_month ?>&year=<?= $selected_year ?>" 
               class="btn btn-success">
                Process New Salary
            </a>
        </div>

        <!-- Payroll Table -->
        <div class="table-responsive">
            <table class="table table-bordered table-striped table-hover">
                <thead class="table-dark">
                    <tr>
                        <th>#</th>
                        <th>Employee</th>
                        <th>Dept</th>
                        <th>Role</th>
                        <th>Location</th>
                        <th>Basic</th>
                        <th>Gross</th>
                        <th>Net</th>
                        <th>Status</th>
                        <th>Actions</th>
                    </tr>
                </thead>
                <tbody>
                    <?php $i = 1; while ($row = mysqli_fetch_assoc($data_result)): 
                        $name = htmlspecialchars($row['first_name'] . ' ' . $row['last_name']);
                        $status = $row['status'] ?? 'pending';
                        $badge = match($status) {
                            'draft' => 'secondary',
                            'pending' => 'warning',
                            'approved' => 'success',
                            'sent' => 'info',
                            default => 'danger'
                        };
                    ?>
                    <tr>
                        <td><?= $i++ ?></td>
                        <td><?= $name ?></td>
                        <td><?= htmlspecialchars($row['division'] ?? '') ?></td>
                        <td><?= htmlspecialchars($row['designation'] ?? '') ?></td>
                        <td><?= htmlspecialchars($row['location'] ?? '') ?></td>
                        <td>₹<?= number_format($row['basic_salary'] ?? 0, 2) ?></td>
                        <td>₹<?= number_format($row['total_gross_salary'] ?? 0, 2) ?></td>
                        <td>₹<?= number_format($row['net_earnings'] ?? 0, 2) ?></td>
                        <td><span class="badge bg-<?= $badge ?>"><?= ucfirst($status) ?></span></td>
                        <td>
                            <div class="dropdown">
                                <button class="btn btn-sm btn-outline-primary dropdown-toggle" data-bs-toggle="dropdown">
                                    Actions
                                </button>
                                <ul class="dropdown-menu">
                                    <li><a class="dropdown-item" href="view_payroll.php?emp_id=<?= $row['id'] ?>&month=<?= $selected_month ?>&year=<?= $selected_year ?>">View</a></li>
                                    <li><a class="dropdown-item" href="edit_payroll.php?emp_id=<?= $row['id'] ?>&month=<?= $selected_month ?>&year=<?= $selected_year ?>">Edit</a></li>
                                    <li><a class="dropdown-item" href="download_payslip.php?emp_id=<?= $row['id'] ?>&month=<?= $selected_month ?>&year=<?= $selected_year ?>">Payslip</a></li>
                                </ul>
                            </div>
                        </td>
                    </tr>
                    <?php endwhile; mysqli_stmt_close($data_stmt); ?>
                </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() {
            document.getElementById('sidebar').classList.toggle('active');
            document.getElementById('content').classList.toggle('active');
        }
    </script>
</body>
</html>