<?php
/**
 * Employee importer (sysadmin). Upload the employee workbook (sheet 1 = Data:
 * Employee_ID, Employee_Name, Vertical, Sw_Role, Reporting Manager) → create/upsert
 * users with the mapped role + reporting hierarchy. Idempotent by Employee_ID.
 *
 * Every import is logged to employee_import_runs with a per-employee detail (name, email, role, created/updated,
 * the temp password) so you can see WHO was created and their credentials — for this run and previous uploads —
 * and download a credentials CSV for the new accounts.
 */
require_once __DIR__ . '/../includes/config.php';
require_once __DIR__ . '/../includes/xlsx-reader.php';
$user = require_login('internal');
$pdo  = db();
$uid  = (int) $user['id'];
$report = null; $err = null; $viewingRun = null; $runFile = null; $runWhen = null;

/* Download a blank/example workbook in the exact column layout the importer expects. */
if (($_GET['template'] ?? '') === 'xlsx') {
    require_once __DIR__ . '/../includes/xlsx.php';
    $x = new SimpleXlsx();
    $x->addSheet('Employees',
        ['Employee_ID', 'Employee_Name', 'Vertical', 'Sw_Role', 'Reporting Manager'],
        [
            ['EMP001', 'Smith, John',  'Coding', 'Manager',     ''],
            ['EMP002', 'Doe, Jane',    'Coding', 'Supervisor',  'Smith, John'],
            ['EMP003', 'Brown, Alex',  'Coding', 'Coder',       'Doe, Jane'],
            ['EMP004', 'Lee, Sam',     'Coding', 'Auditor',     'Doe, Jane'],
            ['EMP005', 'Park, Riya',   'Coding', 'Validator',   'Doe, Jane'],
            ['EMP006', 'Khan, Omar',   'Coding', 'BS Uploader', 'Doe, Jane'],
        ]);
    $x->download('employee-import-template.xlsx');
    exit;
}

/* Load a stored run's detail (for the ?run=ID detail view + the credentials CSV). */
$loadRun = function (int $rid) use ($pdo): ?array {
    $st = $pdo->prepare("SELECT * FROM employee_import_runs WHERE id=?");
    $st->execute([$rid]); $r = $st->fetch();
    if (!$r) return null;
    $r['detail'] = json_decode((string) $r['detail_json'], true) ?: [];
    return $r;
};

/* CSV of the NEW accounts (Employee ID · Name · Email · Temp password) from a stored run. */
if (($rid = (int) ($_GET['run'] ?? 0)) && ($_GET['csv'] ?? '') === '1') {
    $run = $loadRun($rid);
    if ($run) {
        $pw   = (string) ($run['detail']['default_password'] ?? '');
        $made = array_values(array_filter($run['detail']['rows'] ?? [], fn($x) => ($x['status'] ?? '') === 'created'));
        header('Content-Type: text/csv; charset=utf-8');
        header('Content-Disposition: attachment; filename="new-employee-credentials-run' . $rid . '.csv"');
        $out = fopen('php://output', 'w');
        fputcsv($out, ['Employee ID', 'Name', 'Email', 'Temp password', 'Must change at first login']);
        foreach ($made as $m) fputcsv($out, [$m['code'] ?? '', $m['name'] ?? '', $m['email'] ?? '', $pw, 'Yes']);
        fclose($out); exit;
    }
}

if ($_SERVER['REQUEST_METHOD'] === 'POST' && ($_POST['action'] ?? '') === 'import') {
    csrf_check_or_die();
    if (empty($_FILES['file']['tmp_name']) || !is_uploaded_file($_FILES['file']['tmp_name'])) {
        $err = 'Choose an .xlsx file to upload.';
    } else {
        try {
            $rd = new XlsxReader($_FILES['file']['tmp_name']);
            $rows = []; $i = 0;
            foreach ($rd->rows(0) as $r) {
                $i++; if ($i === 1) continue;                       // header row
                if (trim((string)($r[0] ?? '')) === '') continue;
                $rows[] = ['code'=>$r[0], 'name'=>$r[1] ?? '', 'vertical'=>$r[2] ?? '', 'role'=>$r[3] ?? '', 'manager'=>$r[4] ?? ''];
            }
            $rd->close();
            if (!$rows) $err = 'No data rows found on the first sheet.';
            else {
                $report = coding_import_employees($pdo, $rows, $uid);
                $runFile = mb_substr((string) ($_FILES['file']['name'] ?? 'workbook.xlsx'), 0, 255);
                // Persist the run (incl. per-employee detail + the temp password) so it's reviewable later.
                $detail = json_encode([
                    'rows' => $report['rows'] ?? [],
                    'default_password' => $report['default_password'] ?? '',
                    'unmatched' => $report['unmatched'] ?? [],
                    'no_role' => $report['no_role'] ?? [],
                ]);
                $ins = $pdo->prepare("INSERT INTO employee_import_runs (file_name, uploaded_by, total_count, created_count, updated_count, skipped_count, detail_json, created_at)
                                      VALUES (?,?,?,?,?,?,?,NOW())");
                $ins->execute([$runFile, $uid, (int) $report['total'], (int) $report['created'], (int) $report['updated'], count($report['skipped'] ?? []), $detail]);
                $report['_run_id'] = (int) $pdo->lastInsertId();
            }
        } catch (Throwable $e) {
            $err = 'Could not read the workbook: ' . (APP_DEBUG ? $e->getMessage() : 'invalid file');
        }
    }
} elseif ($rid = (int) ($_GET['run'] ?? 0)) {
    // Detail view of a previously uploaded file.
    $run = $loadRun($rid);
    if ($run) {
        $viewingRun = $rid; $runFile = $run['file_name']; $runWhen = $run['created_at'];
        $report = [
            'total' => (int) $run['total_count'], 'created' => (int) $run['created_count'], 'updated' => (int) $run['updated_count'],
            'roles' => 0, 'linked' => 0, 'scoped' => 0,
            'rows' => $run['detail']['rows'] ?? [], 'default_password' => $run['detail']['default_password'] ?? '',
            'unmatched' => $run['detail']['unmatched'] ?? [], 'no_role' => $run['detail']['no_role'] ?? [],
            '_run_id' => $rid,
        ];
    }
}

/* Recent imports for the history list. */
$history = $pdo->query("SELECT r.id, r.file_name, r.total_count, r.created_count, r.updated_count, r.skipped_count, r.created_at, u.name AS by_name
                        FROM employee_import_runs r LEFT JOIN users u ON u.id=r.uploaded_by
                        ORDER BY r.created_at DESC, r.id DESC LIMIT 50")->fetchAll();

$pageTitle = 'Employee import';
require __DIR__ . '/../includes/header.php';

/* Reusable per-employee result table. */
$resultTable = function (array $rows, string $pw) {
    if (!$rows) { echo '<div class="text-secondary small">No employee rows were processed.</div>'; return; }
    ?>
    <div class="table-responsive"><table class="table table-vcenter table-sm">
        <thead><tr><th>Employee ID</th><th>Name</th><th>Email</th><th>Role</th><th>Reports to</th><th>Status</th><th>Temp password</th></tr></thead>
        <tbody>
        <?php foreach ($rows as $r):
            $st = (string) ($r['status'] ?? ''); $created = $st === 'created'; ?>
            <tr>
                <td class="font-mono"><?= e($r['code'] ?? '') ?></td>
                <td><strong><?= e($r['name'] ?? '') ?></strong><?php if (!empty($r['vertical'])): ?> <span class="text-secondary small"><?= e($r['vertical']) ?></span><?php endif; ?></td>
                <td class="font-mono small"><?= e($r['email'] ?? '—') ?></td>
                <td><?php if (!empty($r['role'])): ?><span class="badge bg-azure-lt"><?= e($r['role']) ?></span><?php else: ?><span class="badge bg-red-lt" title="No role mapping for <?= e(($r['vertical'] ?? '').' / '.($r['sw'] ?? '')) ?>">no role</span><?php endif; ?></td>
                <td class="small"><?php if (($r['manager'] ?? '') === ''): ?><span class="text-secondary">— root —</span>
                    <?php elseif (!empty($r['manager_linked'])): ?><i class="ti ti-check text-success"></i> <?= e($r['manager']) ?>
                    <?php else: ?><i class="ti ti-alert-triangle text-warning" title="Manager name not found — left unlinked"></i> <?= e($r['manager']) ?><?php endif; ?></td>
                <td><span class="badge bg-<?= $created ? 'success' : 'info' ?>-lt"><?= $created ? 'created' : 'updated' ?></span></td>
                <td><?php if ($created): ?><code><?= e($pw) ?></code> <span class="text-secondary small">must change at first login</span><?php else: ?><span class="text-secondary small">unchanged</span><?php endif; ?></td>
            </tr>
        <?php endforeach; ?>
        </tbody>
    </table></div>
    <?php
};
?>
<div class="page-header"><div class="container-xl"><div class="row align-items-center">
    <div class="col"><h2 class="page-title"><i class="ti ti-users-plus me-2 text-primary"></i>Employee import</h2>
        <div class="text-secondary small">Create/refresh users + roles + reporting hierarchy from the employee workbook. See exactly who was created, their email &amp; temp password.</div></div>
    <div class="col-auto d-flex gap-2"><a href="<?= e(url('/admin/employee-import.php?template=xlsx')) ?>" class="btn btn-sm btn-outline-success"><i class="ti ti-file-download me-1"></i>Download template</a>
        <a href="<?= e(url('/admin/users.php?tab=internal')) ?>" class="btn btn-sm btn-outline-secondary"><i class="ti ti-users me-1"></i>Users &amp; hierarchy</a>
        <a href="<?= e(url('/admin/coding-access.php')) ?>" class="btn btn-sm btn-outline-primary"><i class="ti ti-key me-1"></i>Coder access</a></div>
</div></div></div>

<div class="page-body"><div class="container-xl">
    <?php if ($err): ?><div class="alert alert-danger"><i class="ti ti-alert-triangle me-1"></i><?= e($err) ?></div><?php endif; ?>

    <?php if ($report): ?>
    <div class="card mb-3"><div class="card-header py-2 d-flex align-items-center">
        <h3 class="card-title small mb-0"><?= $viewingRun ? 'Import — ' . e($runFile) . ' · ' . e(fmt_dt($runWhen)) : 'Import result' ?></h3>
        <div class="ms-auto d-flex gap-2">
            <?php if ((int)($report['created'] ?? 0) > 0 && !empty($report['_run_id'])): ?>
                <a href="<?= e(url('/admin/employee-import.php?run=' . (int)$report['_run_id'] . '&csv=1')) ?>" class="btn btn-sm btn-outline-primary"><i class="ti ti-download me-1"></i>Download new-user credentials (CSV)</a>
            <?php endif; ?>
            <?php if ($viewingRun): ?><a href="<?= e(url('/admin/employee-import.php')) ?>" class="btn btn-sm btn-ghost-secondary">Back to import</a><?php endif; ?>
        </div>
    </div>
        <div class="card-body">
            <div class="row row-cards mb-3">
                <?php foreach ([['Processed',$report['total'],'secondary'],['Created',$report['created'],'success'],['Updated',$report['updated'],'info'],['Roles set',$report['roles']??0,'azure'],['Hierarchy linked',$report['linked']??0,'purple']] as $k): ?>
                <div class="col-6 col-md"><div class="card card-sm"><div class="card-body"><div class="text-secondary small text-uppercase"><?= e($k[0]) ?></div><div class="h2 mb-0 text-<?= $k[2] ?>"><?= (int)$k[1] ?></div></div></div></div>
                <?php endforeach; ?>
            </div>

            <?php if (!empty($report['default_password']) && (int)($report['created'] ?? 0) > 0): ?>
                <div class="alert alert-info py-2"><i class="ti ti-key me-1"></i>New accounts sign in with email <code>first.last@medmecsbillingservices.com</code> and temp password <code><?= e($report['default_password']) ?></code> — they must change it at first login.</div>
            <?php endif; ?>

            <?php $resultTable($report['rows'] ?? [], (string)($report['default_password'] ?? '')); ?>

            <?php if (!empty($report['unmatched'])): ?>
                <div class="alert alert-warning mt-3 mb-0"><strong>Reporting managers not found</strong> (left as org roots / spelled differently):
                    <?php foreach ($report['unmatched'] as $m=>$n): ?><span class="badge bg-warning-lt me-1"><?= e($m) ?> ×<?= (int)$n ?></span><?php endforeach; ?>
                </div>
            <?php endif; ?>
            <?php if (!empty($report['no_role'])): ?>
                <div class="alert alert-danger mt-2 mb-0"><strong>No role mapping</strong> for: <?= e(implode('; ', array_slice($report['no_role'],0,30))) ?></div>
            <?php endif; ?>
        </div>
    </div>
    <?php endif; ?>

    <?php if (!$viewingRun): ?>
    <div class="card mb-3">
        <form method="POST" action="<?= e(url('/admin/employee-import.php')) ?>" enctype="multipart/form-data" class="card-body">
            <?= csrf_field() ?><input type="hidden" name="action" value="import">
            <div class="mb-3"><label class="form-label">Employee workbook (.xlsx)</label>
                <input type="file" name="file" accept=".xlsx" class="form-control" required>
                <div class="form-hint">First sheet columns: Employee_ID, Employee_Name, Vertical, Sw_Role, Reporting Manager. Existing employees (by Employee_ID) are updated, not duplicated. Need a starting point? <a href="<?= e(url('/admin/employee-import.php?template=xlsx')) ?>">Download the template</a> (Sw_Role values: Manager, Supervisor, Coder, Auditor, Validator, BS Uploader).</div>
            </div>
            <div class="text-secondary small mb-3">New users get email <code>first.last@medmecsbillingservices.com</code> and a default password (<strong>Medmecs@2026</strong>) they must change at first login. Role is mapped from Vertical + Sw_Role.</div>
            <button class="btn btn-primary"><i class="ti ti-upload me-1"></i>Import employees</button>
        </form>
    </div>
    <?php endif; ?>

    <!-- ============================ Previously uploaded files ============================ -->
    <div class="card">
        <div class="card-header py-2"><h3 class="card-title"><i class="ti ti-history me-2"></i>Previous imports</h3>
            <div class="card-actions text-secondary small"><?= count($history) ?> upload<?= count($history)===1?'':'s' ?></div></div>
        <div class="table-responsive"><table class="table table-vcenter card-table table-sm">
            <thead><tr><th>When</th><th>File</th><th>By</th><th class="text-center">Processed</th><th class="text-center">Created</th><th class="text-center">Updated</th><th class="text-center">Skipped</th><th class="w-1"></th></tr></thead>
            <tbody>
            <?php if (!$history): ?><tr><td colspan="8" class="text-center text-secondary py-4">No imports yet.</td></tr>
            <?php else: foreach ($history as $h): ?>
                <tr<?= $viewingRun===(int)$h['id']?' class="table-active"':'' ?>>
                    <td class="small text-nowrap"><?= e(fmt_dt($h['created_at'])) ?></td>
                    <td class="small"><?= e($h['file_name'] ?: '—') ?></td>
                    <td class="small text-secondary"><?= e($h['by_name'] ?: '—') ?></td>
                    <td class="text-center"><?= (int)$h['total_count'] ?></td>
                    <td class="text-center"><span class="badge bg-success-lt"><?= (int)$h['created_count'] ?></span></td>
                    <td class="text-center"><span class="badge bg-info-lt"><?= (int)$h['updated_count'] ?></span></td>
                    <td class="text-center text-secondary"><?= (int)$h['skipped_count'] ?></td>
                    <td class="text-nowrap">
                        <a href="<?= e(url('/admin/employee-import.php?run=' . (int)$h['id'])) ?>" class="btn btn-sm"><i class="ti ti-eye me-1"></i>View</a>
                        <?php if ((int)$h['created_count'] > 0): ?><a href="<?= e(url('/admin/employee-import.php?run=' . (int)$h['id'] . '&csv=1')) ?>" class="btn btn-sm btn-ghost-primary" title="Download new-user credentials"><i class="ti ti-download"></i></a><?php endif; ?>
                    </td>
                </tr>
            <?php endforeach; endif; ?>
            </tbody>
        </table></div>
    </div>
</div></div>
<?php require __DIR__ . '/../includes/footer.php'; ?>
