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

if (!isset($_SESSION['username']) || !in_array($_SESSION['role'], ['admin','superadmin'])) {
    header("Location: index.php"); exit;
}

$message = '';

// ==================== XLSX READER (Same as attendance – works perfectly) ====================
function readXLSX($file) {
    $data = [];
    $zip = new ZipArchive;
    if ($zip->open($file) === TRUE) {
        $sharedStrings = [];
        $xmlString = $zip->getFromName('xl/sharedStrings.xml');
        if ($xmlString !== false) {
            $xml = simplexml_load_string($xmlString);
            foreach ($xml->si as $si) {
                $text = '';
                foreach ($si->r as $r) $text .= $r->t;
                if ($text === '') $text = $si->t;
                $sharedStrings[] = (string)$text;
            }
        }

        $xmlSheet = $zip->getFromName('xl/worksheets/sheet1.xml');
        if ($xmlSheet !== false) {
            $xml = simplexml_load_string($xmlSheet);
            foreach ($xml->sheetData->row as $row) {
                $r = array_fill(0, 30, ''); // 30 columns
                foreach ($row->c as $cell) {
                    $cellCoord = (string)$cell['r'];
                    preg_match('/([A-Z]+)(\d+)', $cellCoord, $matches);
                    $colLetter = $matches[1];
                    $colIndex = 0;
                    for ($i = 0; $i < strlen($colLetter); $i++) {
                        $colIndex = $colIndex * 26 + (ord($colLetter[$i]) - ord('A') + 1);
                    }
                    $colIndex--; // 0-based

                    $v = (string)$cell->v;
                    $t = (string)$cell['t'];
                    if ($t == 's' && isset($sharedStrings[(int)$v])) {
                        $v = $sharedStrings[(int)$v];
                    }
                    $r[$colIndex] = $v;
                }
                $data[] = $r;
            }
        }
        $zip->close();
    }
    return $data;
}

// ==================== SALARY EXCEL UPLOAD ====================
if (isset($_POST['upload_salary'])) {
    if (!isset($_FILES['salary_file']) || $_FILES['salary_file']['error'] !== 0) {
        $message = '<div class="alert alert-danger">Please select a file!</div>';
    } else {
        $file = $_FILES['salary_file']['tmp_name'];
        $data = readXLSX($file);

        if (empty($data) || count($data) < 10) {
            $message = '<div class="alert alert-danger">Invalid salary file format!</div>';
        } else {
            $conn->autocommit(false);
            try {
                $inserted = 0;
                $updated = 0;

                foreach ($data as $idx => $row) {
                    if ($idx < 4) continue; // skip headers

                    $emp_code = (int)($row[1] ?? 0);
                    if ($emp_code == 0) continue;

                    $name = trim($row[2] ?? '');
                    $salary = (float)($row[3] ?? 0); // Salary column
                    $basic = (float)($row[8] ?? 0);
                    $da = (float)($row[9] ?? 0);
                    $hra = (float)($row[10] ?? 0);
                    $medical = (float)($row[11] ?? 0);
                    $conveyance = (float)($row[12] ?? 0);
                    $gross_salary = (float)($row[13] ?? $salary);
                    $epf = (float)($row[14] ?? 0);
                    $esi = (float)($row[15] ?? 0);
                    $travel = (float)($row[16] ?? 0);
                    $tw_maintenance = (float)($row[17] ?? 0);
                    $tds = (float)($row[19] ?? 0);
                    $advance = (float)($row[20] ?? 0);
                    $net_salary = (float)($row[22] ?? 0);
                    $email = trim($row[23] ?? '');

                    // Find department from attendance or keep General
                    $dept = 'General';
                    $dept_res = $conn->query("SELECT department FROM attendance WHERE emp_code = $emp_code LIMIT 1");
                    if ($dept_res->num_rows > 0) {
                        $dept = $dept_res->fetch_assoc()['department'];
                    }

                    $stmt = $conn->prepare("REPLACE INTO salary_master 
                        (emp_code, name, department, gross_salary, basic, da, hra, medical, conveyance, epf, esi, travel_allowance, tw_maintenance, tds, advance, net_salary, email)
                        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
                    $stmt->bind_param("issssssssssssssss", $emp_code, $name, $dept, $gross_salary, $basic, $da, $hra, $medical, $conveyance, $epf, $esi, $travel, $tw_maintenance, $tds, $advance, $net_salary, $email);
                    
                    if ($stmt->execute()) {
                        if ($stmt->affected_rows > 1) $updated++;
                        else $inserted++;
                    }
                }

                $conn->commit();
                $message = "<div class='alert alert-success'>
                    <strong>Salary Master Updated!</strong><br>
                    New: $inserted | Updated: $updated | Total: " . ($inserted + $updated) . " employees
                </div>";
            } catch (Exception $e) {
                $conn->rollback();
                $message = "<div class='alert alert-danger'>Error: " . htmlspecialchars($e->getMessage()) . "</div>";
            }
        }
    }
}
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>Upload Salary Master</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css" rel="stylesheet">
    <link href="https://cdn.jsdelivr.net/npm/bootstrap-icons@1.11.3/font/bootstrap-icons.min.css" rel="stylesheet">
</head>
<body class="bg-light">
<div class="container py-5">
    <div class="row justify-content-center">
        <div class="col-md-8">
            <div class="card shadow">
                <div class="card-header bg-primary text-white text-center">
                    <h4><i class="bi bi-upload"></i> Upload Monthly Salary Excel</h4>
                </div>
                <div class="card-body">
                    <?= $message ?>

                    <form method="post" enctype="multipart/form-data">
                        <div class="mb-4">
                            <label class="form-label fw-bold">Salary Excel File</label>
                            <input type="file" name="salary_file" accept=".xlsx,.xls" class="form-control form-control-lg" required>
                            <div class="form-text">
                                Your file with columns: <code>Sl No, Emp Code, NAME, Salary, ..., Grand Total</code> is fully supported
                            </div>
                        </div>

                        <div class="d-grid">
                            <button name="upload_salary" class="btn btn-primary btn-lg">
                                <i class="bi bi-cloud-arrow-up"></i> Upload Salary Master
                            </button>
                        </div>
                    </form>

                    <div class="mt-4 text-center">
                        <a href="payroll.php" class="btn btn-outline-secondary">
                            <i class="bi bi-arrow-left"></i> Back to Payroll
                        </a>
                    </div>
                </div>
            </div>

            <div class="mt-4 text-center text-muted small">
                After uploading salary master, your payroll will use this data automatically when attendance is uploaded.
            </div>
        </div>
    </div>
</div>
</body>
</html>