/* excel.js — Export & Import data pelamar ke/dari Excel (SheetJS) */
(function (global) {
  'use strict';

  var HEADERS = [
    'No', 'Nama', 'No WhatsApp', 'Email', 'Posisi', 'Status', 'Sumber', 'Domisili',
    'Gaji Ekspektasi', 'Tanggal Melamar', 'Catatan',
    'Tanggal Interview', 'Jam Interview', 'Metode', 'Lokasi/Link',
    'Pewawancara', 'No WA Pewawancara'
  ];

  var COL_WIDTH = [5, 24, 16, 24, 20, 18, 12, 16, 16, 16, 30, 16, 12, 10, 24, 18, 18];

  var ALIASES = {
    nama: ['nama', 'nama lengkap', 'name', 'full name'],
    phoneRaw: ['no whatsapp', 'no wa', 'nomor whatsapp', 'nomor wa', 'no hp', 'no. hp', 'whatsapp', 'phone', 'telepon', 'no telp', 'no. telp'],
    email: ['email', 'e-mail'],
    posisi: ['posisi', 'posisi dilamar', 'posisi yang dilamar', 'jabatan', 'position'],
    status: ['status', 'status rekrutmen'],
    sumber: ['sumber', 'sumber pelamar', 'source'],
    domisili: ['domisili', 'alamat', 'kota'],
    gajiEkspektasi: ['gaji ekspektasi', 'gaji', 'ekspektasi gaji', 'expected salary'],
    tanggalMelamar: ['tanggal melamar', 'tgl melamar', 'tanggal', 'date'],
    catatan: ['catatan', 'catatan hr', 'keterangan', 'note', 'notes'],
    ivTanggal: ['tanggal interview', 'tgl interview', 'jadwal interview'],
    ivJam: ['jam interview', 'jam', 'waktu'],
    ivMetode: ['metode', 'metode interview'],
    ivLokasi: ['lokasi/link', 'lokasi', 'link', 'lokasi interview', 'lokasi / link', 'tautan'],
    pewawancara: ['pewawancara', 'nama pewawancara', 'interviewer'],
    telpPewawancara: ['no wa pewawancara', 'no. wa pewawancara', 'no hp pewawancara', 'telp pewawancara', 'whatsapp pewawancara']
  };

  function hasLib() { return typeof global.XLSX !== 'undefined' && global.XLSX; }

  function statusName(id, statuses) {
    var s = (statuses || []).filter(function (x) { return x.id === id; })[0];
    return s ? s.nama : '';
  }

  function resolveStatus(nama, statuses) {
    var target = String(nama || '').trim().toLowerCase();
    var list = statuses || [];
    if (target) {
      var found = list.filter(function (s) { return String(s.nama).trim().toLowerCase() === target; })[0];
      if (found) return found.id;
      var partial = list.filter(function (s) {
        var n = String(s.nama).trim().toLowerCase();
        return n.indexOf(target) >= 0 || target.indexOf(n) >= 0;
      })[0];
      if (partial) return partial.id;
    }
    return list.length ? list.slice().sort(function (a, b) { return (a.urutan || 0) - (b.urutan || 0); })[0].id : 'baru';
  }

  function normalizeKey(k) {
    return String(k == null ? '' : k).trim().toLowerCase().replace(/\s+/g, ' ').replace(/:$/, '');
  }

  function pad(n) { return String(n).padStart(2, '0'); }

  function toDateStr(v) {
    if (v == null || v === '') return '';
    if (v instanceof Date) return v.getFullYear() + '-' + pad(v.getMonth() + 1) + '-' + pad(v.getDate());
    if (typeof v === 'number' && isFinite(v)) {
      var epoch = Date.UTC(1899, 11, 30);
      var d = new Date(epoch + v * 86400000);
      return d.getUTCFullYear() + '-' + pad(d.getUTCMonth() + 1) + '-' + pad(d.getUTCDate());
    }
    var s = String(v).trim();
    var iso = s.match(/^(\d{4})-(\d{1,2})-(\d{1,2})/);
    if (iso) return iso[1] + '-' + pad(iso[2]) + '-' + pad(iso[3]);
    var dmy = s.match(/^(\d{1,2})[\/\-.](\d{1,2})[\/\-.](\d{2,4})$/);
    if (dmy) {
      var yr = dmy[3].length === 2 ? '20' + dmy[3] : dmy[3];
      return yr + '-' + pad(dmy[2]) + '-' + pad(dmy[1]);
    }
    var d2 = new Date(s);
    if (!isNaN(d2.getTime())) return d2.getFullYear() + '-' + pad(d2.getMonth() + 1) + '-' + pad(d2.getDate());
    return '';
  }

  function toTimeStr(v) {
    if (v == null || v === '') return '';
    if (v instanceof Date) return pad(v.getHours()) + ':' + pad(v.getMinutes());
    if (typeof v === 'number' && isFinite(v)) {
      var total = Math.round(v * 24 * 60);
      return pad(Math.floor(total / 60) % 24) + ':' + pad(total % 60);
    }
    var s = String(v).trim();
    var m = s.match(/^(\d{1,2})[:.](\d{2})/);
    if (m) return pad(m[1]) + ':' + m[2];
    return s;
  }

  function normalizeMetode(v) {
    var s = String(v == null ? '' : v).trim().toLowerCase();
    if (s.indexOf('online') >= 0 || s.indexOf('daring') >= 0) return 'Online';
    if (s.indexOf('offline') >= 0 || s.indexOf('luring') >= 0 || s.indexOf('kantor') >= 0) return 'Offline';
    return '';
  }

  function exportCandidates(candidates, statuses) {
    if (!hasLib()) throw new Error('Pustaka Excel tidak termuat.');
    var rows = (candidates || []).map(function (c, i) {
      var iv = c.interview || {};
      var o = {};
      o['No'] = i + 1;
      o['Nama'] = c.nama || '';
      o['No WhatsApp'] = c.phoneNorm || global.Phone.normalize(c.phoneRaw || '');
      o['Email'] = c.email || '';
      o['Posisi'] = c.posisi || '';
      o['Status'] = statusName(c.statusId, statuses);
      o['Sumber'] = c.sumber || '';
      o['Domisili'] = c.domisili || '';
      o['Gaji Ekspektasi'] = c.gajiEkspektasi || '';
      o['Tanggal Melamar'] = c.tanggalMelamar || '';
      o['Catatan'] = c.catatan || '';
      o['Tanggal Interview'] = iv.tanggal || '';
      o['Jam Interview'] = iv.jam || '';
      o['Metode'] = iv.metode || '';
      o['Lokasi/Link'] = iv.lokasi || '';
      o['Pewawancara'] = iv.pewawancara || '';
      o['No WA Pewawancara'] = iv.telpPewawancara || '';
      return o;
    });

    var ws = global.XLSX.utils.json_to_sheet(rows, { header: HEADERS });
    ws['!cols'] = COL_WIDTH.map(function (w) { return { wch: w }; });
    var wb = global.XLSX.utils.book_new();
    global.XLSX.utils.book_append_sheet(wb, ws, 'Pelamar');
    var stamp = new Date().toISOString().slice(0, 10).replace(/-/g, '');
    global.XLSX.writeFile(wb, 'data-pelamar-' + stamp + '.xlsx');
  }

  function exportTemplate() {
    if (!hasLib()) throw new Error('Pustaka Excel tidak termuat.');
    var ws = global.XLSX.utils.aoa_to_sheet([HEADERS]);
    ws['!cols'] = COL_WIDTH.map(function (w) { return { wch: w }; });
    var wb = global.XLSX.utils.book_new();
    global.XLSX.utils.book_append_sheet(wb, ws, 'Pelamar');
    global.XLSX.writeFile(wb, 'template-import-pelamar.xlsx');
  }

  function readRows(file) {
    if (!hasLib()) return Promise.reject(new Error('Pustaka Excel tidak termuat.'));
    var isCsv = /\.csv$/i.test(file.name) || file.type === 'text/csv';
    return new Promise(function (resolve, reject) {
      var fr = new FileReader();
      fr.onerror = function () { reject(fr.error || new Error('Gagal membaca file')); };
      fr.onload = function (e) {
        try {
          var wb = isCsv
            ? global.XLSX.read(e.target.result, { type: 'string' })
            : global.XLSX.read(new Uint8Array(e.target.result), { type: 'array', cellDates: true });
          var sheetName = wb.SheetNames[0];
          var ws = wb.Sheets[sheetName];
          var json = global.XLSX.utils.sheet_to_json(ws, { defval: '', raw: true });
          resolve(json);
        } catch (err) {
          reject(new Error('Format file tidak dikenali. Gunakan .xlsx, .xls, atau .csv.'));
        }
      };
      if (isCsv) fr.readAsText(file); else fr.readAsArrayBuffer(file);
    });
  }

  function mapRows(json, statuses) {
    return (json || []).map(function (row) {
      var lookup = {};
      Object.keys(row).forEach(function (k) { lookup[normalizeKey(k)] = row[k]; });
      function pick(field) {
        var al = ALIASES[field] || [];
        for (var i = 0; i < al.length; i++) {
          var v = lookup[al[i]];
          if (v != null && String(v).trim() !== '') return v;
        }
        return '';
      }
      return {
        nama: String(pick('nama')).trim(),
        phoneRaw: String(pick('phoneRaw')).trim(),
        email: String(pick('email')).trim(),
        posisi: String(pick('posisi')).trim(),
        statusId: resolveStatus(pick('status'), statuses),
        sumber: String(pick('sumber')).trim(),
        domisili: String(pick('domisili')).trim(),
        gajiEkspektasi: String(pick('gajiEkspektasi')).trim(),
        tanggalMelamar: toDateStr(pick('tanggalMelamar')),
        catatan: String(pick('catatan')).trim(),
        interview: {
          tanggal: toDateStr(pick('ivTanggal')),
          jam: toTimeStr(pick('ivJam')),
          metode: normalizeMetode(pick('ivMetode')),
          lokasi: String(pick('ivLokasi')).trim(),
          pewawancara: String(pick('pewawancara')).trim(),
          telpPewawancara: String(pick('telpPewawancara')).trim()
        }
      };
    });
  }

  global.Excel = {
    HEADERS: HEADERS,
    available: hasLib,
    exportCandidates: exportCandidates,
    exportTemplate: exportTemplate,
    readRows: readRows,
    mapRows: mapRows,
    toDateStr: toDateStr,
    toTimeStr: toTimeStr
  };
})(window);