// Excel Utilities — Client-side Excel parse/export via SheetJS (CDN)
// No backend xlsx needed. Parse in browser → POST JSON to API.

window.ExcelUtils = {
  // Parse Excel file → array of objects (uses first sheet, first row = headers)
  async parseFile(file, options = {}) {
    if (!window.XLSX) throw new Error('SheetJS not loaded');
    const buf = await file.arrayBuffer();
    const wb = window.XLSX.read(buf, { type: 'array' });
    const sheetName = options.sheet || wb.SheetNames[0];
    const ws = wb.Sheets[sheetName];
    if (!ws) throw new Error('Sheet not found: ' + sheetName);
    const rows = window.XLSX.utils.sheet_to_json(ws, { defval: '', raw: false });
    return rows;
  },

  // Export array of objects → download as .xlsx
  exportToFile(rows, filename = 'export.xlsx', sheetName = 'Sheet1') {
    if (!window.XLSX) throw new Error('SheetJS not loaded');
    const ws = window.XLSX.utils.json_to_sheet(rows);
    const wb = window.XLSX.utils.book_new();
    window.XLSX.utils.book_append_sheet(wb, ws, sheetName);
    window.XLSX.writeFile(wb, filename);
  },

  // Build template Excel file for cost code import
  downloadCostCodeTemplate() {
    const sample = [
      { category_code: '100', category_name: 'งานเตรียมพื้นที่', sub_code: '101', sub_name: 'งานสำรวจ-วางผัง', item_code: '001', item_name: 'สำรวจระดับและภูมิประเทศ' },
      { category_code: '200', category_name: 'งานโครงสร้าง', sub_code: '202', sub_name: 'งานดิน', item_code: '003', item_name: 'งานบดอัดดินกลับ' },
    ];
    this.exportToFile(sample, 'cost-codes-template.xlsx', 'CostCodes');
  },

  // Build template for quote/quotation import
  downloadQuoteTemplate() {
    const sample = [
      { cost_code: 'M-202-003', supplier_name: 'SCG', unit: 'ลบ.ม.', unit_price: 850, quantity: 100, total: 85000, valid_until: '2026-12-31', notes: 'มัดจำ 30%' },
      { cost_code: 'L-301-002', supplier_name: 'ทีมงานช่างก่อ', unit: 'ตร.ม.', unit_price: 250, quantity: 200, total: 50000, valid_until: '2026-09-30', notes: '' },
    ];
    this.exportToFile(sample, 'quotes-template.xlsx', 'Quotes');
  },

  // Build template for BOQ line import
  downloadBOQTemplate() {
    const sample = [
      { group_th: 'งานโครงสร้าง', description_th: 'เสาคอนกรีตเสริมเหล็ก', quantity: 24, unit: 'ต้น', unit_price: 12500, total: 300000, cost_code: 'M-203-001', notes: '' },
      { group_th: 'งานสถาปัตยกรรม', description_th: 'ผนังก่ออิฐมอญ ฉาบเรียบ', quantity: 380, unit: 'ตร.ม.', unit_price: 480, total: 182400, cost_code: 'M-301-002', notes: '' },
    ];
    this.exportToFile(sample, 'boq-template.xlsx', 'BOQ');
  },

  // Convert flat rows back to nested cost-code structure
  rowsToCostCodes(rows) {
    const map = new Map();
    for (const r of rows) {
      const catCode = String(r.category_code || '').trim();
      const subCode = String(r.sub_code || '').trim();
      const itemCode = String(r.item_code || '').trim();
      if (!catCode || !subCode || !itemCode) continue;

      if (!map.has(catCode)) {
        map.set(catCode, { code: catCode, name: r.category_name || '', name_en: '', icon: '📁', subs: [] });
      }
      const cat = map.get(catCode);
      let sub = cat.subs.find(s => s.code === subCode);
      if (!sub) {
        sub = { code: subCode, name: r.sub_name || '', items: [] };
        cat.subs.push(sub);
      }
      if (!sub.items.find(i => i.code === itemCode)) {
        sub.items.push({ code: itemCode, name: r.item_name || '' });
      }
    }
    return Array.from(map.values());
  },

  // Merge imported cost codes into existing data (preserve existing)
  mergeCostCodes(existing, imported) {
    const out = [...existing];
    for (const newCat of imported) {
      const existingCat = out.find(c => c.code === newCat.code);
      if (!existingCat) { out.push(newCat); continue; }
      for (const newSub of newCat.subs) {
        const existingSub = (existingCat.subs || []).find(s => s.code === newSub.code);
        if (!existingSub) { existingCat.subs.push(newSub); continue; }
        for (const newItem of newSub.items) {
          if (!existingSub.items.find(i => i.code === newItem.code)) {
            existingSub.items.push(newItem);
          }
        }
      }
    }
    return out;
  },
};

// ─── Reusable File Drop Zone Component ──────────────────────────
window.FileDropZone = function FileDropZone({ onFile, accept = '.xlsx,.xls,.csv', children, lang }) {
  const [dragging, setDragging] = React.useState(false);
  const inputRef = React.useRef(null);

  const onDrop = (e) => {
    e.preventDefault();
    setDragging(false);
    const file = e.dataTransfer.files[0];
    if (file) onFile(file);
  };

  return (
    <div
      onDragOver={(e) => { e.preventDefault(); setDragging(true); }}
      onDragLeave={() => setDragging(false)}
      onDrop={onDrop}
      onClick={() => inputRef.current?.click()}
      style={{
        border: '2px dashed ' + (dragging ? 'var(--syk-blue-soft)' : 'var(--line-strong)'),
        background: dragging ? 'rgba(40,72,255,0.08)' : 'var(--glass-2)',
        borderRadius: 12, padding: 24, textAlign: 'center', cursor: 'pointer',
        transition: 'all .2s',
      }}>
      <input ref={inputRef} type="file" accept={accept} style={{ display: 'none' }}
        onChange={(e) => { const f = e.target.files[0]; if (f) onFile(f); }} />
      {children || (
        <>
          <div style={{ fontSize: 32, marginBottom: 8 }}>📊</div>
          <div style={{ fontSize: 13, fontWeight: 600 }}>{lang === 'th' ? 'ลาก Excel มาวาง หรือคลิกเพื่อเลือก' : 'Drop Excel or click to browse'}</div>
          <div className="micro" style={{ marginTop: 6 }}>.xlsx, .xls, .csv</div>
        </>
      )}
    </div>
  );
};

// ─── Import Modal (generic) ─────────────────────────────────────
window.ExcelImportModal = function ExcelImportModal({ title, expectedColumns = [], parseRow, onImport, onClose, lang, downloadTemplate }) {
  const [rows, setRows] = React.useState([]);
  const [fileName, setFileName] = React.useState('');
  const [error, setError] = React.useState('');
  const [importing, setImporting] = React.useState(false);

  const handleFile = async (file) => {
    setError(''); setFileName(file.name);
    try {
      const parsed = await window.ExcelUtils.parseFile(file);
      if (!parsed.length) throw new Error('ไฟล์ว่างเปล่า');
      setRows(parsed);
    } catch (err) {
      setError(err.message);
      setRows([]);
    }
  };

  const handleImport = async () => {
    setImporting(true);
    try {
      await onImport(rows);
    } finally {
      setImporting(false);
    }
  };

  return (
    <div onClick={onClose} style={{ position: 'fixed', inset: 0, background: 'rgba(0,0,0,0.6)', backdropFilter: 'blur(6px)', zIndex: 1000, display: 'flex', alignItems: 'center', justifyContent: 'center', padding: 20 }}>
      <div className="card anim-fadein" onClick={(e) => e.stopPropagation()} style={{ width: 'min(720px, 100%)', maxHeight: '90vh', overflowY: 'auto', padding: 20 }}>
        <div style={{ display: 'flex', justifyContent: 'space-between', alignItems: 'center', marginBottom: 14 }}>
          <div>
            <div className="micro">{lang === 'th' ? 'นำเข้าจาก Excel' : 'Import from Excel'}</div>
            <div style={{ fontSize: 18, fontWeight: 700, marginTop: 4 }}>{title}</div>
          </div>
          <button className="btn btn-sm btn-ghost" onClick={onClose}><I.x /></button>
        </div>

        {expectedColumns.length > 0 && (
          <div style={{ padding: 10, background: 'rgba(40,72,255,0.06)', borderRadius: 8, marginBottom: 12, fontSize: 11.5 }}>
            <div style={{ fontWeight: 600, marginBottom: 4 }}>📋 {lang === 'th' ? 'คอลัมน์ที่ต้องการ' : 'Expected columns'}:</div>
            <div className="mono" style={{ color: 'var(--ink-soft)' }}>{expectedColumns.join(', ')}</div>
          </div>
        )}

        {downloadTemplate && (
          <button className="btn btn-sm" onClick={downloadTemplate} style={{ marginBottom: 12 }}>
            <I.download /> {lang === 'th' ? 'ดาวน์โหลด Template' : 'Download template'}
          </button>
        )}

        <FileDropZone onFile={handleFile} lang={lang} />

        {error && (
          <div style={{ marginTop: 12, padding: 10, background: 'rgba(244,63,94,0.1)', borderRadius: 8, color: 'var(--rose)', fontSize: 12 }}>
            ❌ {error}
          </div>
        )}

        {rows.length > 0 && (
          <div style={{ marginTop: 14 }}>
            <div style={{ display: 'flex', justifyContent: 'space-between', marginBottom: 8 }}>
              <span style={{ fontSize: 13, fontWeight: 600 }}>✅ {fileName}</span>
              <span className="chip success">{rows.length} {lang === 'th' ? 'แถว' : 'rows'}</span>
            </div>
            <div style={{ maxHeight: 240, overflow: 'auto', background: 'var(--glass-2)', borderRadius: 8, padding: 4 }}>
              <table className="table" style={{ fontSize: 11 }}>
                <thead><tr>{Object.keys(rows[0]).map(k => <th key={k}>{k}</th>)}</tr></thead>
                <tbody>
                  {rows.slice(0, 10).map((r, i) => (
                    <tr key={i}>{Object.values(r).map((v, j) => <td key={j} style={{ maxWidth: 120, overflow: 'hidden', textOverflow: 'ellipsis', whiteSpace: 'nowrap' }}>{String(v)}</td>)}</tr>
                  ))}
                </tbody>
              </table>
              {rows.length > 10 && <div style={{ padding: 8, textAlign: 'center', fontSize: 11, color: 'var(--ink-mute)' }}>... + {rows.length - 10} {lang === 'th' ? 'แถวเพิ่มเติม' : 'more rows'}</div>}
            </div>
          </div>
        )}

        <div style={{ display: 'flex', gap: 10, marginTop: 18 }}>
          <button className="btn" style={{ flex: 1, justifyContent: 'center' }} onClick={onClose}>{lang === 'th' ? 'ยกเลิก' : 'Cancel'}</button>
          <button className="btn btn-primary" style={{ flex: 1, justifyContent: 'center' }} onClick={handleImport} disabled={!rows.length || importing}>
            <I.upload /> {importing ? (lang === 'th' ? 'กำลังนำเข้า...' : 'Importing...') : (lang === 'th' ? `นำเข้า ${rows.length} แถว` : `Import ${rows.length} rows`)}
          </button>
        </div>
      </div>
    </div>
  );
};