<?php
require_once __DIR__ . '/../../config/config.php';
require_once __DIR__ . '/../../includes/functions.php';
startSession(); requireLogin(); requireRole('superadmin','admin','hr','management');
$db   = Database::getInstance();
$page = 'Import Employees';

$results  = [];
$errors   = [];
$imported = 0;
$skippedNoLicense = 0;

// FIX (07 Jul 2026): this import had 3 real problems —
// 1. It never set company_id on the INSERT, so every imported employee
//    silently fell back to company_id's default (=1), which could be a
//    DIFFERENT organization's company entirely.
// 2. It created an active `users` login for every single row with no
//    license-capacity check at all — a CSV of 500 rows could blow past
//    a purchased 100-user limit completely unchecked.
// 3. The duplicate-email and next-employee-code lookups scanned ALL
//    organizations' employees, not just this one's.
$orgCompanies = orgCompanyIds($db);
$selectedCompanyId = postInt('company_id') ?: ($orgCompanies[0] ?? 0);
if (!in_array($selectedCompanyId, $orgCompanies)) { $selectedCompanyId = $orgCompanies[0] ?? 0; }

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    verifyCsrf();
    $action = post('action');

    if ($action === 'import' && isset($_FILES['csv_file'])) {
        $file = $_FILES['csv_file'];
        if ($file['error'] !== UPLOAD_ERR_OK) {
            $errors[] = 'File upload failed. Please try again.';
        } elseif (!in_array(strtolower(pathinfo($file['name'], PATHINFO_EXTENSION)), ['csv'])) {
            $errors[] = 'Only CSV files are allowed.';
        } elseif (!$selectedCompanyId) {
            $errors[] = 'No company selected.';
        } else {
            $handle = fopen($file['tmp_name'], 'r');
            $header = fgetcsv($handle); // skip header row
            $row    = 2;

            $depts   = $db->fetchAll("SELECT id, LOWER(name) AS name FROM departments");
            $desigs  = $db->fetchAll("SELECT id, LOWER(name) AS name FROM designations");
            $locs    = $db->fetchAll("SELECT id, LOWER(name) AS name FROM locations");
            $grades  = $db->fetchAll("SELECT id, LOWER(name) AS name FROM grades");

            $deptMap  = array_column($depts,  'id', 'name');
            $desigMap = array_column($desigs, 'id', 'name');
            $locMap   = array_column($locs,   'id', 'name');
            $gradeMap = array_column($grades,  'id', 'name');

            // Auto-generate next employee ID — scoped to this organization
            // so numbering isn't influenced by (or colliding across) other
            // organizations sharing the same database.
            [$seqClause, $seqParams] = orgScopeClause($db, 'company_id');
            $lastEmp  = $db->fetchOne("SELECT employee_id FROM employees WHERE $seqClause ORDER BY id DESC LIMIT 1", $seqParams);
            $prefix   = 'EMP';
            $counter  = 1001;
            if ($lastEmp) {
                preg_match('/(\d+)$/', $lastEmp['employee_id'], $m);
                if (!empty($m[1])) $counter = intval($m[1]) + 1;
                preg_match('/^([A-Z]+)/', $lastEmp['employee_id'], $pm);
                if (!empty($pm[1])) $prefix = $pm[1];
            }

            while (($cols = fgetcsv($handle)) !== false) {
                if (empty(array_filter($cols))) { $row++; continue; }

                // Map CSV columns (flexible — match by position)
                // Expected: first_name,last_name,dob,gender,mobile,email,department,designation,grade,location,joining_date,employment_type
                $firstName    = trim($cols[0]  ?? '');
                $lastName     = trim($cols[1]  ?? '');
                $dob          = trim($cols[2]  ?? '');
                $gender       = strtolower(trim($cols[3] ?? 'male'));
                $mobile       = trim($cols[4]  ?? '');
                $email        = trim($cols[5]  ?? '');
                $deptName     = strtolower(trim($cols[6] ?? ''));
                $desigName    = strtolower(trim($cols[7] ?? ''));
                $gradeName    = strtolower(trim($cols[8] ?? ''));
                $locName      = strtolower(trim($cols[9] ?? ''));
                $joiningDate  = trim($cols[10] ?? date('Y-m-d'));
                $empType      = strtolower(trim($cols[11] ?? 'permanent'));
                $pan          = strtoupper(trim($cols[12] ?? ''));
                $aadhaar      = trim($cols[13] ?? '');

                if (!$firstName || !$lastName) {
                    $errors[] = "Row $row: First name and last name are required.";
                    $row++; continue;
                }

                // Validate dates
                $dobFmt      = $dob     ? date('Y-m-d', strtotime($dob))         : null;
                $joiningFmt  = $joiningDate ? date('Y-m-d', strtotime($joiningDate)) : date('Y-m-d');

                // Look up IDs
                $deptId  = $deptMap[$deptName]  ?? null;
                $desigId = $desigMap[$desigName] ?? null;
                $gradeId = $gradeMap[$gradeName] ?? null;
                $locId   = $locMap[$locName]     ?? null;

                // Generate employee ID
                $employeeId = $prefix . str_pad($counter, 4, '0', STR_PAD_LEFT);

                // Check duplicate by email — scoped to this organization only.
                if ($email) {
                    [$dupClause, $dupParams] = orgScopeClause($db, 'company_id');
                    $dup = $db->fetchOne("SELECT id FROM employees WHERE (personal_email=? OR official_email=?) AND $dupClause", array_merge([$email,$email], $dupParams));
                    if ($dup) {
                        $errors[] = "Row $row: Email '$email' already exists. Skipped.";
                        $row++; continue;
                    }
                }

                try {
                    $db->execute("INSERT INTO employees
                        (employee_id,first_name,last_name,date_of_birth,gender,mobile,personal_email,
                         official_email,company_id,department_id,designation_id,grade_id,location_id,
                         date_of_joining,employment_type,pan_number,aadhaar_number,status)
                        VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,'permanent',?,?,'active')",
                        [$employeeId,$firstName,$lastName,$dobFmt,$gender,$mobile,$email,$email,
                         $selectedCompanyId,$deptId,$desigId,$gradeId,$locId,$joiningFmt,$pan,$aadhaar]);

                    $newEmpId = $db->lastInsertId();

                    // Create default login — but only if there's still a
                    // free seat in this organization's license. Otherwise
                    // the employee record is still created (HR data doesn't
                    // need a seat), just without a login; it's reported below
                    // so nothing silently disappears.
                    if (hasLicenseCapacity($db, organizationId())) {
                        $username = strtolower($prefix . str_pad($counter, 4, '0', STR_PAD_LEFT));
                        $hash     = password_hash('Gmhpl_2025', PASSWORD_BCRYPT, ['cost'=>12]);
                        $db->execute("INSERT IGNORE INTO users (employee_id,username,password_hash,role,company_id,is_active,must_change_password)
                            VALUES (?,?,?,'employee',?,1,1)", [$newEmpId,$username,$hash,$selectedCompanyId]);
                        $results[] = ['row'=>$row, 'id'=>$employeeId, 'name'=>"$firstName $lastName", 'status'=>'success'];
                    } else {
                        $results[] = ['row'=>$row, 'id'=>$employeeId, 'name'=>"$firstName $lastName", 'status'=>'success (no login — license limit reached)'];
                        $skippedNoLicense++;
                    }
                    $imported++;
                    $counter++;
                } catch (Exception $e) {
                    $errors[] = "Row $row ($firstName $lastName): " . $e->getMessage();
                }
                $row++;
            }
            fclose($handle);
            if ($skippedNoLicense > 0) {
                $errors[] = "$skippedNoLicense employee(s) were added without a login because your organization's license limit was reached. Increase your license or deactivate other users, then create their logins from User Management.";
            }
        }
    }
}

// Get departments, designations etc. for the template download
$depts  = $db->fetchAll("SELECT name FROM departments WHERE is_active=1 ORDER BY name");
$desigs = $db->fetchAll("SELECT name FROM designations WHERE is_active=1 ORDER BY name");

// Download template
if (isset($_GET['template'])) {
    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename="employee_import_template.csv"');
    $out = fopen('php://output','w');
    fputcsv($out, ['first_name','last_name','date_of_birth','gender','mobile','personal_email',
                   'department','designation','grade','location','joining_date','employment_type','pan_number','aadhaar_number']);
    // Sample rows
    fputcsv($out, ['John','Doe','1990-05-15','male','9876543210','john.doe@email.com',
                   $depts[0]['name']??'IT', $desigs[0]['name']??'Software Engineer','','',date('Y-m-d'),'permanent','ABCDE1234F','123456789012']);
    fputcsv($out, ['Jane','Smith','1992-08-20','female','9123456780','jane.smith@email.com',
                   $depts[1]['name']??'HR', $desigs[1]['name']??'HR Executive','','',date('Y-m-d'),'permanent','FGHIJ5678K','']);
    fclose($out);
    exit;
}

include __DIR__ . '/../../includes/header.php';
?>
<div class="row g-3">
  <div class="col-md-5">
    <div class="pc-card mb-3">
      <div class="pc-card-title"><i class="ti ti-upload me-2"></i>Import Employees from CSV</div>
      <div class="alert alert-info small py-2 mb-3">
        <i class="ti ti-info-circle me-1"></i>
        Download the template first, fill it in, then upload. Each imported employee gets a system login with default password <code>Gmhpl_2025</code>.
      </div>
      <a href="?template=1" class="btn btn-outline-secondary btn-sm w-100 mb-3">
        <i class="ti ti-download me-1"></i>Download CSV Template
      </a>
      <form method="POST" enctype="multipart/form-data">
        <?=csrfField()?><input type="hidden" name="action" value="import">
        <?php if (count($orgCompanies) > 1): $companyRows = $db->fetchAll("SELECT id,name FROM companies WHERE id IN (".implode(',',array_fill(0,count($orgCompanies),'?')).")", $orgCompanies); ?>
        <div class="mb-3">
          <label class="pc-label">Company <span class="text-danger">*</span></label>
          <select name="company_id" class="form-select form-select-sm">
            <?php foreach ($companyRows as $c): ?>
            <option value="<?=$c['id']?>" <?=$selectedCompanyId==$c['id']?'selected':''?>><?=clean($c['name'])?></option>
            <?php endforeach; ?>
          </select>
        </div>
        <?php else: ?>
        <input type="hidden" name="company_id" value="<?=$selectedCompanyId?>">
        <?php endif; ?>
        <div class="mb-3">
          <label class="pc-label">Select CSV File</label>
          <input type="file" name="csv_file" class="form-control form-control-sm" accept=".csv" required>
          <div class="form-text">Max file size: <?=ini_get('upload_max_filesize')?>. Only .csv files.</div>
        </div>
        <button class="btn btn-pc w-100"><i class="ti ti-upload me-1"></i>Import Employees</button>
      </form>
    </div>

    <div class="pc-card">
      <div class="pc-card-title"><i class="ti ti-list-check me-2"></i>CSV Column Format</div>
      <div class="pc-table-wrap">
        <table class="pc-table">
          <thead><tr><th>#</th><th>Column</th><th>Required</th><th>Example</th></tr></thead>
          <tbody>
          <?php $cols = [
            ['1','first_name','Yes','John'],
            ['2','last_name','Yes','Doe'],
            ['3','date_of_birth','No','1990-05-15'],
            ['4','gender','No','male / female'],
            ['5','mobile','No','9876543210'],
            ['6','personal_email','No','john@example.com'],
            ['7','department','No','IT (must match exactly)'],
            ['8','designation','No','Software Engineer'],
            ['9','grade','No','Grade A'],
            ['10','location','No','Chennai'],
            ['11','joining_date','No','2024-01-01'],
            ['12','employment_type','No','permanent'],
            ['13','pan_number','No','ABCDE1234F'],
            ['14','aadhaar_number','No','123456789012'],
          ]; foreach ($cols as [$n,$col,$req,$eg]): ?>
          <tr>
            <td class="text-muted"><?=$n?></td>
            <td><code><?=$col?></code></td>
            <td><?=$req==='Yes'?'<span class="text-danger fw-bold">Yes</span>':'<span class="text-muted">No</span>'?></td>
            <td class="text-muted small"><?=$eg?></td>
          </tr>
          <?php endforeach; ?>
          </tbody>
        </table>
      </div>
    </div>
  </div>

  <div class="col-md-7">
    <?php if ($imported > 0 || $errors): ?>
    <!-- Results -->
    <div class="pc-card mb-3">
      <div class="d-flex align-items-center gap-2 mb-3">
        <div class="pc-stat-icon <?=$imported>0?'green':'red'?>" style="width:40px;height:40px;font-size:18px">
          <i class="ti <?=$imported>0?'ti-check':'ti-x'?>"></i>
        </div>
        <div>
          <div class="fw-bold"><?=$imported?> employee<?=$imported!=1?'s':''?> imported successfully</div>
          <?php if ($errors): ?><div class="text-danger small"><?=count($errors)?> row<?=count($errors)!=1?'s':''?> had errors</div><?php endif; ?>
        </div>
      </div>

      <?php if ($results): ?>
      <div class="pc-table-wrap mb-3">
        <table class="pc-table">
          <thead><tr><th>Row</th><th>Emp ID</th><th>Name</th><th>Status</th></tr></thead>
          <tbody>
          <?php foreach ($results as $r): ?>
          <tr>
            <td class="text-muted"><?=$r['row']?></td>
            <td><code><?=clean($r['id'])?></code></td>
            <td class="fw-semibold"><?=clean($r['name'])?></td>
            <td><span class="badge" style="background:var(--pc-green-bg);color:var(--pc-green)"><i class="ti ti-check me-1"></i>Imported</span></td>
          </tr>
          <?php endforeach; ?>
          </tbody>
        </table>
      </div>
      <?php endif; ?>

      <?php if ($errors): ?>
      <div class="pc-section-divider">Errors</div>
      <?php foreach ($errors as $err): ?>
      <div class="alert alert-danger py-1 px-2 mb-1 small"><i class="ti ti-alert-circle me-1"></i><?=clean($err)?></div>
      <?php endforeach; ?>
      <?php endif; ?>
    </div>
    <?php else: ?>
    <!-- Instructions -->
    <div class="pc-card mb-3">
      <div class="pc-card-title"><i class="ti ti-help me-2"></i>How to Import</div>
      <div class="d-flex gap-3 mb-3">
        <?php foreach ([['1','Download Template','Click "Download CSV Template" to get a pre-formatted file with sample rows.','brand'],['2','Fill Data','Open in Excel or Google Sheets. Fill employee data row by row. One employee per row.','green'],['3','Upload & Import','Upload the filled CSV. System will validate each row and show success/error per row.','blue']] as [$n,$t,$d,$c]): ?>
        <div class="text-center flex-fill">
          <div class="pc-stat-icon <?=$c?> mx-auto mb-2" style="width:38px;height:38px;font-size:16px;font-weight:800"><?=$n?></div>
          <div class="fw-semibold small mb-1"><?=$t?></div>
          <div class="text-muted" style="font-size:11px"><?=$d?></div>
        </div>
        <?php endforeach; ?>
      </div>
    </div>

    <div class="pc-card">
      <div class="pc-card-title"><i class="ti ti-building me-2"></i>Available Departments (for CSV)</div>
      <div class="d-flex flex-wrap gap-1">
        <?php foreach ($depts as $d): ?>
        <span class="badge" style="background:var(--pc-brand-light);color:var(--pc-brand)"><?=clean($d['name'])?></span>
        <?php endforeach; ?>
      </div>
      <div class="pc-section-divider mt-3">Available Designations</div>
      <div class="d-flex flex-wrap gap-1">
        <?php foreach ($desigs as $d): ?>
        <span class="badge" style="background:var(--pc-blue-bg);color:var(--pc-blue)"><?=clean($d['name'])?></span>
        <?php endforeach; ?>
      </div>
    </div>
    <?php endif; ?>
  </div>
</div>
<?php include __DIR__ . '/../../includes/footer.php'; ?>