// ─────────────────────────────────────────────
// DEV_Database_Data.jsx
// Data tab: Import, Export history
// Sub-tabs: Imports | Exports
// ─────────────────────────────────────────────

function DEVDatabaseData({ app, initialTab = 'import' }) {
  const [subTab,   setSubTab]   = React.useState(
    initialTab.startsWith('export') ? 'exports' :
    initialTab === 'library' ? 'library' : 'imports'
  );

  // Sync when parent tab changes
  React.useEffect(() => {
    if (initialTab.startsWith('export')) setSubTab('exports');
    else if (initialTab === 'library') setSubTab('library');
    else setSubTab('imports');
  }, [initialTab]);
  const [entries,  setEntries]  = React.useState([]);
  const [tables,   setTables]   = React.useState([]);
  const [loading,  setLoading]  = React.useState(true);

  // Import flow state
  const [importStep,   setImportStep]   = React.useState('list'); // list|upload|map|confirm|done
  const [uploadFile,   setUploadFile]   = React.useState(null);
  const [uploading,    setUploading]    = React.useState(false);
  const [uploadLog,    setUploadLog]    = React.useState(null);  // { logId, headers, preview, rowCount, filename }
  const [mapTable,     setMapTable]     = React.useState('');
  const [mapping,      setMapping]      = React.useState({});    // { f1: 'CSV Col', f2: '__skip__' }
  const [importing,    setImporting]    = React.useState(false);
  const [importResult, setImportResult] = React.useState(null);
  const [importError,  setImportError]  = React.useState(null);

  // Export state
  const [showExport,   setShowExport]   = React.useState(false);
  const [expTable,     setExpTable]     = React.useState('');
  const [expFormat,    setExpFormat]    = React.useState('csv');
  const [exporting,    setExporting]    = React.useState(false);
  const [expError,     setExpError]     = React.useState(null);

  React.useEffect(() => {
    loadData();
    loadTables();
  }, [app.app_id]);

  async function loadData() {
    setLoading(true);
    try {
      const res  = await apiFetch(`${API_BASE}/v6/dev/data/files?appId=${app.app_id}`);
      const data = await res.json();
      if (data.status === 'ok') setEntries(data.entries || []);
    } catch(e) {}
    setLoading(false);
  }

  async function loadTables() {
    try {
      const res  = await apiFetch(`${API_BASE}/v6/dev/data/tables?appId=${app.app_id}`);
      const data = await res.json();
      if (data.status === 'ok') setTables(data.tables || []);
    } catch(e) {}
  }

  // ── Upload CSV/JSON ─────────────────────────
  async function handleFileSelect(e) {
    const file = e.target.files?.[0];
    if (!file) return;
    const ext = file.name.split('.').pop().toLowerCase();
    if (!['csv','json'].includes(ext)) {
      alert('Only CSV and JSON files are supported.');
      return;
    }
    setUploadFile(file);
    setImportStep('uploading');
    setUploading(true);
    setImportError(null);

    try {
      // Step 1: get presigned URL
      const initRes  = await apiFetch(`${API_BASE}/v6/dev/data/upload-init`, {
        method: 'POST',
        body:   JSON.stringify({ appId: app.app_id, filename: file.name, size: file.size, mime: file.type }),
      });
      const initData = await initRes.json();
      if (initData.status !== 'ok') throw new Error(initData.message);

      // Step 2: upload directly to R2
      await fetch(initData.putUrl, {
        method:  'PUT',
        body:    file,
        headers: { 'Content-Type': file.type || 'text/plain' },
      });

      // Step 3: confirm + get preview
      const doneRes  = await apiFetch(`${API_BASE}/v6/dev/data/upload-done`, {
        method: 'POST',
        body:   JSON.stringify({
          appId: app.app_id, fileId: initData.fileId,
          s3Key: initData.s3Key, filename: file.name,
          size:  file.size, ext: initData.ext,
        }),
      });
      const doneData = await doneRes.json();
      if (doneData.status !== 'ok') throw new Error(doneData.message);

      setUploadLog(doneData);
      // Auto-select first table
      if (tables.length) {
        setMapTable(tables[0].table);
        buildAutoMapping(doneData.headers, tables[0].fields);
      }
      setImportStep('map');
    } catch(e) {
      setImportError(e.message);
      setImportStep('list');
    }
    setUploading(false);
  }

  // ── Auto-suggest mapping ────────────────────
  function buildAutoMapping(csvHeaders, fields) {
    const newMapping = {};
    fields.forEach(f => {
      // Fuzzy match: normalize both sides
      const normalize = s => s.toLowerCase().replace(/[^a-z0-9]/g,'');
      const fieldNorm = normalize(f.name);
      const match = csvHeaders.find(h => normalize(h) === fieldNorm)
                 || csvHeaders.find(h => normalize(h).includes(fieldNorm))
                 || csvHeaders.find(h => fieldNorm.includes(normalize(h)));
      newMapping[f.field] = match || '__skip__';
    });
    setMapping(newMapping);
  }

  // ── Run import ──────────────────────────────
  async function handleImport() {
    if (!mapTable || !uploadLog?.logId) return;
    setImporting(true);
    setImportError(null);
    try {
      const res  = await apiFetch(`${API_BASE}/v6/dev/data/import/start`, {
        method: 'POST',
        body:   JSON.stringify({
          appId:  app.app_id,
          logId:  uploadLog.logId,
          table:  mapTable,
          mapping,
        }),
      });
      const data = await res.json();
      if (data.status !== 'ok') throw new Error(data.message);
      setImportResult(data);
      setImportStep('done');
      loadData();
    } catch(e) {
      setImportError(e.message);
    }
    setImporting(false);
  }

  // ── Undo import ─────────────────────────────
  async function handleUndo(entry) {
    if (!confirm(`Undo import of ${entry.inserted?.toLocaleString()} records? They will be soft-deleted.`)) return;
    try {
      const res  = await apiFetch(`${API_BASE}/v6/dev/data/import/undo`, {
        method: 'POST',
        body:   JSON.stringify({ appId: app.app_id, importLogId: entry.id, table: entry.table }),
      });
      const data = await res.json();
      if (data.status !== 'ok') throw new Error(data.message);
      alert(`✅ Undone — ${data.undone} records removed.`);
      loadData();
    } catch(e) { alert('Undo failed: ' + e.message); }
  }

  // ── Export ──────────────────────────────────
  async function handleExport() {
    if (!expTable) return;
    setExporting(true); setExpError(null);
    try {
      const res  = await apiFetch(`${API_BASE}/v6/dev/data/export`, {
        method: 'POST',
        body:   JSON.stringify({ appId: app.app_id, table: expTable, format: expFormat }),
      });
      const data = await res.json();
      if (data.status !== 'ok') throw new Error(data.message);
      setShowExport(false);
      setSubTab('exports');
      loadData();
      if (!data.async && data.downloadUrl) {
        window.open(data.downloadUrl, '_blank');
      }
    } catch(e) { setExpError(e.message); }
    setExporting(false);
  }

  async function handleDownload(entry) {
    try {
      const res  = await apiFetch(`${API_BASE}/v6/dev/data/export/download?appId=${app.app_id}&logId=${entry.id}`);
      const data = await res.json();
      if (data.status !== 'ok') throw new Error(data.message);
      window.open(data.downloadUrl, '_blank');
    } catch(e) { alert('Download failed: ' + e.message); }
  }

  async function handleDelete(entry) {
    if (!confirm('Delete this file?')) return;
    try {
      await apiFetch(`${API_BASE}/v6/dev/data/file?appId=${app.app_id}&logId=${entry.id}&s3Key=${encodeURIComponent(entry.s3Key||'')}`,
        { method: 'DELETE' }
      );
      loadData();
    } catch(e) { alert('Delete failed: ' + e.message); }
  }

  // ── Helpers ─────────────────────────────────
  function formatDate(d) {
    if (!d) return '—';
    try { return new Date(d).toLocaleString('en-US', { month:'short', day:'numeric', year:'numeric', hour:'numeric', minute:'2-digit' }); }
    catch(e) { return d; }
  }

  function statusBadge(status) {
    const map = {
      uploaded:   { bg:'#eff6ff', color:'#2563eb', label:'Uploaded' },
      imported:   { bg:'#dcfce7', color:'#16a34a', label:'Imported' },
      processing: { bg:'#fef9c3', color:'#ca8a04', label:'Processing…' },
      done:       { bg:'#dcfce7', color:'#16a34a', label:'Done' },
      undone:     { bg:'#f1f5f9', color:'#64748b', label:'Undone' },
      deleted:    { bg:'#fef2f2', color:'#dc2626', label:'Deleted' },
      failed:     { bg:'#fef2f2', color:'#dc2626', label:'Failed' },
    };
    const s = map[status] || { bg:'#f1f5f9', color:'#64748b', label: status || '—' };
    return <span style={{ ...SData.badge, background:s.bg, color:s.color }}>{s.label}</span>;
  }

  const imports = entries.filter(e => ['upload','import','import_undo'].includes(e.type));
  const exports = entries.filter(e => e.type === 'export');

  const currentTableFields = tables.find(t => t.table === mapTable)?.fields || [];

  // ── Render ───────────────────────────────────
  return (
    <div>
      {/* Action button row — no sub-tabs, navigation is in parent */}
      <div style={{ display:'flex', justifyContent:'flex-end', marginBottom:16 }}>
        {subTab === 'imports' && importStep === 'list' && (
          <label style={SData.actionBtn}>
            📂 Upload File
            <input type="file" accept=".csv,.json" onChange={handleFileSelect}
              style={{ display:'none' }} />
          </label>
        )}
        {subTab === 'exports' && (
          <button style={SData.actionBtn} onClick={() => { setShowExport(true); setExpTable(tables[0]?.table || ''); }}>
            + New Export
          </button>
        )}
      </div>

      {/* ── IMPORTS TAB ── */}
      {subTab === 'imports' && (
        <>
          {/* Upload/Map flow */}
          {importStep === 'uploading' && (
            <div style={SData.stepBox}>
              <div style={SData.stepTitle}>⏳ Uploading {uploadFile?.name}…</div>
              <div style={SData.progressBar}><div style={{ ...SData.progressFill, width:'60%' }} /></div>
            </div>
          )}

          {importStep === 'map' && uploadLog && (
            <div style={SData.stepBox}>
              <div style={SData.stepHeader}>
                <div>
                  <div style={SData.stepTitle}>📋 Map Fields — {uploadLog.filename}</div>
                  <div style={SData.stepSub}>{uploadLog.rowCount?.toLocaleString()} rows detected</div>
                </div>
                <button style={SData.cancelBtn} onClick={() => setImportStep('list')}>✕ Cancel</button>
              </div>

              {importError && <div style={SData.error}>{importError}</div>}

              {/* Table select */}
              <div style={{ display:'flex', alignItems:'center', gap:12, marginBottom:16 }}>
                <label style={SData.label}>Import into table:</label>
                <select style={SData.select} value={mapTable}
                  onChange={e => {
                    setMapTable(e.target.value);
                    const tbl = tables.find(t => t.table === e.target.value);
                    if (tbl) buildAutoMapping(uploadLog.headers, tbl.fields);
                  }}>
                  {tables.map(t => <option key={t.table} value={t.table}>{t.name} ({t.table})</option>)}
                </select>
              </div>

              {/* Field mapping grid */}
              <div style={SData.mapGrid}>
                <div style={SData.mapHeader}>
                  <span>DO Field</span>
                  <span>Match to CSV Column</span>
                </div>
                {currentTableFields.map(f => (
                  <div key={f.field} style={SData.mapRow}>
                    <div style={SData.mapField}>
                      <span style={SData.mapFieldName}>{f.name}</span>
                      <span style={SData.mapFieldTag}>{f.field}</span>
                    </div>
                    <select style={SData.select}
                      value={mapping[f.field] || '__skip__'}
                      onChange={e => setMapping({ ...mapping, [f.field]: e.target.value })}>
                      <option value="__skip__">— Skip this field —</option>
                      {uploadLog.headers.map(h => <option key={h} value={h}>{h}</option>)}
                    </select>
                  </div>
                ))}
              </div>

              {/* Preview */}
              <div style={{ marginTop:16 }}>
                <div style={SData.previewTitle}>Preview (first {uploadLog.preview?.length} rows)</div>
                <div style={{ overflowX:'auto' }}>
                  <table style={SData.previewTable}>
                    <thead>
                      <tr>
                        {uploadLog.headers.map(h => <th key={h} style={SData.previewTh}>{h}</th>)}
                      </tr>
                    </thead>
                    <tbody>
                      {(uploadLog.preview || []).map((row, ri) => (
                        <tr key={ri}>
                          {row.map((cell, ci) => <td key={ci} style={SData.previewTd}>{cell}</td>)}
                        </tr>
                      ))}
                    </tbody>
                  </table>
                </div>
              </div>

              <div style={{ display:'flex', justifyContent:'flex-end', gap:10, marginTop:16 }}>
                <button style={SData.cancelBtn} onClick={() => setImportStep('list')}>Cancel</button>
                <button style={SData.importBtn} onClick={handleImport}
                  disabled={importing || !mapTable}>
                  {importing ? '⏳ Importing…' : `Import ${uploadLog.rowCount?.toLocaleString()} Records →`}
                </button>
              </div>
            </div>
          )}

          {importStep === 'done' && importResult && (
            <div style={SData.stepBox}>
              <div style={{ textAlign:'center', padding:'24px 0' }}>
                <div style={{ fontSize:40, marginBottom:12 }}>✅</div>
                <div style={{ fontSize:16, fontWeight:700, color:'#0f1923', marginBottom:4 }}>
                  Import Complete
                </div>
                <div style={{ fontSize:13, color:'#64748b', marginBottom:20 }}>
                  {importResult.inserted?.toLocaleString()} records imported
                  {importResult.failed > 0 && ` · ${importResult.failed} failed`}
                </div>
                <button style={SData.actionBtn} onClick={() => { setImportStep('list'); loadData(); }}>
                  Done
                </button>
              </div>
            </div>
          )}

          {/* Imports list */}
          {importStep === 'list' && (
            <div style={SData.list}>
              {loading && <div style={SData.empty}>Loading…</div>}
              {!loading && imports.length === 0 && (
                <div style={SData.empty}>
                  <div style={{ fontSize:32, marginBottom:8 }}>📥</div>
                  <div style={{ fontWeight:600, marginBottom:4 }}>No imports yet</div>
                  <div style={{ fontSize:13, color:'#94a3b8' }}>Upload a CSV or JSON file to get started</div>
                </div>
              )}
              {imports.map(entry => (
                <div key={entry.id} style={SData.listRow}>
                  <div style={{ flex:3 }}>
                    <div style={SData.listFilename}>{entry.filename || '—'}</div>
                    <div style={SData.listMeta}>
                      {entry.table && `→ ${tables.find(t=>t.table===entry.table)?.name || entry.table} · `}
                      {formatDate(entry.date)}
                    </div>
                  </div>
                  <div style={{ flex:1, textAlign:'center' }}>
                    {entry.rowCount && <span style={SData.count}>{Number(entry.rowCount).toLocaleString()} rows</span>}
                    {entry.inserted && <span style={SData.count}>{Number(entry.inserted).toLocaleString()} imported</span>}
                  </div>
                  <div style={{ flex:1, textAlign:'center' }}>
                    {statusBadge(entry.status)}
                  </div>
                  <div style={{ display:'flex', gap:6, justifyContent:'flex-end' }}>
                    {entry.type === 'import' && entry.status === 'done' && (
                      <button style={SData.undoBtn} onClick={() => handleUndo(entry)}>↩ Undo</button>
                    )}
                    {entry.s3Key && entry.status !== 'deleted' && (
                      <button style={SData.deleteBtn} onClick={() => handleDelete(entry)}>🗑</button>
                    )}
                  </div>
                </div>
              ))}
            </div>
          )}
        </>
      )}

      {/* ── EXPORTS TAB ── */}
      {subTab === 'exports' && (
        <>
          {/* New export modal */}
          {showExport && (
            <div style={SData.stepBox}>
              <div style={SData.stepHeader}>
                <div style={SData.stepTitle}>📤 New Export</div>
                <button style={SData.cancelBtn} onClick={() => setShowExport(false)}>✕</button>
              </div>
              {expError && <div style={SData.error}>{expError}</div>}
              <div style={{ display:'flex', gap:16, alignItems:'flex-end', flexWrap:'wrap' }}>
                <div>
                  <label style={SData.label}>Table</label>
                  <select style={SData.select} value={expTable} onChange={e => setExpTable(e.target.value)}>
                    {tables.map(t => <option key={t.table} value={t.table}>{t.name} ({t.table})</option>)}
                  </select>
                </div>
                <div>
                  <label style={SData.label}>Format</label>
                  <select style={SData.select} value={expFormat} onChange={e => setExpFormat(e.target.value)}>
                    <option value="csv">CSV</option>
                    <option value="json">JSON</option>
                  </select>
                </div>
                <button style={SData.importBtn} onClick={handleExport} disabled={exporting || !expTable}>
                  {exporting ? '⏳ Exporting…' : 'Export →'}
                </button>
              </div>
            </div>
          )}

          {/* Exports list */}
          <div style={SData.list}>
            {loading && <div style={SData.empty}>Loading…</div>}
            {!loading && exports.length === 0 && (
              <div style={SData.empty}>
                <div style={{ fontSize:32, marginBottom:8 }}>📤</div>
                <div style={{ fontWeight:600, marginBottom:4 }}>No exports yet</div>
                <div style={{ fontSize:13, color:'#94a3b8' }}>Click "New Export" to generate a file</div>
              </div>
            )}
            {exports.map(entry => (
              <div key={entry.id} style={SData.listRow}>
                <div style={{ flex:3 }}>
                  <div style={SData.listFilename}>{entry.filename || '—'}</div>
                  <div style={SData.listMeta}>{formatDate(entry.date)}</div>
                </div>
                <div style={{ flex:1, textAlign:'center' }}>
                  {entry.rowCount && <span style={SData.count}>{Number(entry.rowCount).toLocaleString()} records</span>}
                </div>
                <div style={{ flex:1, textAlign:'center' }}>
                  {statusBadge(entry.status)}
                </div>
                <div style={{ display:'flex', gap:6, justifyContent:'flex-end' }}>
                  {entry.status === 'done' && (
                    <button style={SData.downloadBtn} onClick={() => handleDownload(entry)}>⬇ Download</button>
                  )}
                  {entry.status === 'processing' && (
                    <button style={SData.cancelBtn} onClick={loadData}>↻ Refresh</button>
                  )}
                  <button style={SData.deleteBtn} onClick={() => handleDelete(entry)}>🗑</button>
                </div>
              </div>
            ))}
          </div>
        </>
      )}
    </div>
  );
}

// ── Styles ────────────────────────────────────
const SData = {
  subTabs:      { display:'flex', alignItems:'center', gap:4, marginBottom:20,
                  borderBottom:'1px solid #e2e8f0', paddingBottom:12 },
  subTab:       { padding:'6px 16px', background:'none', border:'none', borderRadius:20,
                  fontSize:13, fontWeight:500, cursor:'pointer', color:'#64748b',
                  fontFamily:'DM Sans, sans-serif' },
  subTabActive: { background:'#eff6ff', color:'#2563eb', fontWeight:600 },
  actionBtn:    { padding:'7px 16px', background:'#0f1923', color:'#fff', border:'none',
                  borderRadius:7, fontSize:12, fontWeight:600, cursor:'pointer',
                  fontFamily:'DM Sans, sans-serif', display:'inline-flex', alignItems:'center', gap:6 },
  stepBox:      { background:'#f8fafc', border:'1px solid #e2e8f0', borderRadius:10,
                  padding:'20px 24px', marginBottom:20 },
  stepHeader:   { display:'flex', justifyContent:'space-between', alignItems:'flex-start', marginBottom:16 },
  stepTitle:    { fontSize:14, fontWeight:700, color:'#0f1923' },
  stepSub:      { fontSize:12, color:'#64748b', marginTop:3 },
  progressBar:  { height:6, background:'#e2e8f0', borderRadius:3, overflow:'hidden', marginTop:12 },
  progressFill: { height:'100%', background:'#2563eb', borderRadius:3, transition:'width 0.3s' },
  label:        { display:'block', fontSize:12, fontWeight:500, color:'#374151', marginBottom:5 },
  select:       { padding:'8px 12px', border:'1.5px solid #e2e8f0', borderRadius:7, fontSize:13,
                  fontFamily:'DM Sans, sans-serif', color:'#0f1923', background:'#fff',
                  outline:'none', minWidth:200 },
  mapGrid:      { border:'1px solid #e2e8f0', borderRadius:8, overflow:'hidden' },
  mapHeader:    { display:'grid', gridTemplateColumns:'1fr 1fr', gap:12, padding:'8px 14px',
                  background:'#f1f5f9', fontSize:11, fontWeight:700, color:'#64748b',
                  textTransform:'uppercase', letterSpacing:'0.06em' },
  mapRow:       { display:'grid', gridTemplateColumns:'1fr 1fr', gap:12, padding:'10px 14px',
                  borderTop:'1px solid #f1f5f9', alignItems:'center' },
  mapField:     { display:'flex', alignItems:'center', gap:8 },
  mapFieldName: { fontSize:13, fontWeight:500, color:'#0f1923' },
  mapFieldTag:  { fontSize:11, fontFamily:'DM Mono, monospace', color:'#94a3b8',
                  background:'#f1f5f9', padding:'1px 6px', borderRadius:4 },
  previewTitle: { fontSize:11, fontWeight:700, color:'#94a3b8', textTransform:'uppercase',
                  letterSpacing:'0.06em', marginBottom:8 },
  previewTable: { borderCollapse:'collapse', width:'100%', fontSize:12 },
  previewTh:    { background:'#f8fafc', padding:'6px 10px', textAlign:'left', fontSize:11,
                  fontWeight:600, color:'#64748b', borderBottom:'1px solid #e2e8f0',
                  whiteSpace:'nowrap' },
  previewTd:    { padding:'6px 10px', color:'#374151', borderBottom:'1px solid #f1f5f9',
                  maxWidth:150, overflow:'hidden', textOverflow:'ellipsis', whiteSpace:'nowrap' },
  list:         { border:'1px solid #e2e8f0', borderRadius:10, overflow:'hidden', background:'#fff' },
  listRow:      { display:'flex', alignItems:'center', gap:12, padding:'12px 16px',
                  borderBottom:'1px solid #f1f5f9' },
  listFilename: { fontSize:13, fontWeight:600, color:'#0f1923', fontFamily:'DM Mono, monospace' },
  listMeta:     { fontSize:11, color:'#94a3b8', marginTop:2 },
  count:        { fontSize:12, color:'#64748b' },
  badge:        { fontSize:11, fontWeight:600, padding:'2px 8px', borderRadius:20 },
  importBtn:    { padding:'9px 18px', background:'#0f1923', color:'#fff', border:'none',
                  borderRadius:7, fontSize:13, fontWeight:600, cursor:'pointer',
                  fontFamily:'DM Sans, sans-serif' },
  downloadBtn:  { padding:'5px 12px', background:'#eff6ff', color:'#2563eb',
                  border:'1px solid #bfdbfe', borderRadius:6, fontSize:12,
                  cursor:'pointer', fontFamily:'DM Sans, sans-serif', fontWeight:500 },
  undoBtn:      { padding:'5px 12px', background:'#fef9c3', color:'#92400e',
                  border:'1px solid #fde68a', borderRadius:6, fontSize:12,
                  cursor:'pointer', fontFamily:'DM Sans, sans-serif', fontWeight:500 },
  deleteBtn:    { padding:'5px 8px', background:'#fff', color:'#dc2626',
                  border:'1px solid #fecaca', borderRadius:6, fontSize:12,
                  cursor:'pointer', fontFamily:'DM Sans, sans-serif' },
  cancelBtn:    { padding:'7px 14px', background:'#fff', color:'#374151',
                  border:'1.5px solid #e2e8f0', borderRadius:7, fontSize:12,
                  cursor:'pointer', fontFamily:'DM Sans, sans-serif' },
  error:        { background:'#fef2f2', color:'#dc2626', padding:'10px 14px', borderRadius:7,
                  fontSize:12, marginBottom:14, border:'1px solid #fecaca' },
  empty:        { padding:'60px 20px', textAlign:'center', color:'#94a3b8', fontSize:13 },
};