// Migration.jsx — one-time tool to move base64 images from Postgres to Supabase Storage.
// Requires the admin user to be authenticated (storage policies require auth.role = 'authenticated').

// ─── Helpers ─────────────────────────────────────────────────────────────────

const MIGRATION_BUCKET = 'cms-images';

function countBase64InValue(val) {
  if (typeof val === 'string') {
    if (val.startsWith('data:image')) return { count: 1, bytes: val.length };
    if (val.includes('data:image')) {
      const m = [...val.matchAll(/data:image\/[^;]+;base64,[A-Za-z0-9+/=]+/g)];
      return { count: m.length, bytes: m.reduce((s, x) => s + x[0].length, 0) };
    }
    return { count: 0, bytes: 0 };
  }
  if (Array.isArray(val)) {
    return val.reduce((acc, item) => {
      const r = countBase64InValue(item);
      return { count: acc.count + r.count, bytes: acc.bytes + r.bytes };
    }, { count: 0, bytes: 0 });
  }
  if (val && typeof val === 'object') {
    return Object.values(val).reduce((acc, v) => {
      const r = countBase64InValue(v);
      return { count: acc.count + r.count, bytes: acc.bytes + r.bytes };
    }, { count: 0, bytes: 0 });
  }
  return { count: 0, bytes: 0 };
}

async function uploadBase64ToStorage(dataUrl, folder) {
  const comma = dataUrl.indexOf(',');
  const header = dataUrl.slice(0, comma);
  const b64    = dataUrl.slice(comma + 1);
  const mimeMatch = header.match(/data:([^;]+)/);
  const mime = mimeMatch ? mimeMatch[1] : 'image/jpeg';
  const extMap = { 'image/jpeg': 'jpg', 'image/jpg': 'jpg', 'image/png': 'png', 'image/webp': 'webp', 'image/gif': 'gif' };
  const ext = extMap[mime] || 'jpg';

  const byteStr = atob(b64);
  const buf = new Uint8Array(byteStr.length);
  for (let i = 0; i < byteStr.length; i++) buf[i] = byteStr.charCodeAt(i);
  const blob = new Blob([buf], { type: mime });

  const filename = `${folder}/${Date.now()}-${Math.random().toString(36).slice(2, 7)}.${ext}`;
  const { error } = await db.storage.from(MIGRATION_BUCKET).upload(filename, blob, {
    contentType: mime,
    cacheControl: '31536000',
    upsert: false,
  });
  if (error) throw new Error(error.message);
  const { data: { publicUrl } } = db.storage.from(MIGRATION_BUCKET).getPublicUrl(filename);
  return publicUrl;
}

async function migrateJsonValue(val, folder, onUpload) {
  if (typeof val === 'string') {
    if (val.startsWith('data:image')) {
      const url = await uploadBase64ToStorage(val, folder);
      onUpload(val.length);
      return url;
    }
    if (val.includes('data:image')) {
      const matches = [...val.matchAll(/data:image\/[^;]+;base64,[A-Za-z0-9+/=]+/g)].map(m => m[0]);
      const unique = [...new Set(matches)];
      const urlMap = {};
      for (const b64 of unique) {
        urlMap[b64] = await uploadBase64ToStorage(b64, folder);
        onUpload(b64.length);
      }
      let result = val;
      for (const [b64, url] of Object.entries(urlMap)) {
        result = result.split(b64).join(url);
      }
      return result;
    }
    return val;
  }
  if (Array.isArray(val)) {
    const out = [];
    for (const item of val) out.push(await migrateJsonValue(item, folder, onUpload));
    return out;
  }
  if (val && typeof val === 'object') {
    const out = {};
    for (const [k, v] of Object.entries(val)) out[k] = await migrateJsonValue(v, folder, onUpload);
    return out;
  }
  return val;
}

function fmtKB(bytes) {
  if (bytes >= 1024 * 1024) return `${(bytes / 1024 / 1024).toFixed(1)}MB`;
  return `${Math.round(bytes / 1024)}KB`;
}

// ─── StepCard — stable module-level definition (not inside Migration) ─────────

function StepCard({ n, title, sub, action, actionLabel, disabled, done: stepDone, children }) {
  return (
    <div className="card" style={{ padding: 'var(--space-5)', marginBottom: 'var(--space-4)', opacity: disabled ? 0.45 : 1, transition: 'opacity 200ms' }}>
      <div style={{ display: 'flex', justifyContent: 'space-between', alignItems: 'flex-start', marginBottom: children ? 'var(--space-4)' : 0 }}>
        <div>
          <div style={{ fontSize: 'var(--fs-caption)', fontWeight: 'var(--fw-semibold)', color: 'var(--fg-secondary)', textTransform: 'uppercase', letterSpacing: '.07em', marginBottom: 2 }}>Step {n}</div>
          <div style={{ fontWeight: 'var(--fw-semibold)', fontSize: 'var(--fs-body-sm)' }}>{title}</div>
          {sub && <div style={{ fontSize: 'var(--fs-caption)', color: 'var(--fg-secondary)', marginTop: 2 }}>{sub}</div>}
        </div>
        {action && (
          <button
            className={`btn ${stepDone ? 'btn--ghost' : 'btn--primary'}`}
            onClick={action}
            disabled={disabled || stepDone}
            style={{ flexShrink: 0, marginLeft: 'var(--space-4)' }}
          >
            {stepDone ? '✓ Done' : actionLabel}
          </button>
        )}
      </div>
      {children}
    </div>
  );
}

// ─── Component ────────────────────────────────────────────────────────────────

function Migration() {
  const [phase,       setPhase]      = React.useState('idle');
  const [scanResult,  setScanResult] = React.useState(null);
  const [scanLog,     setScanLog]    = React.useState([]);
  const [log,         setLog]        = React.useState([]);
  const [result,      setResult]     = React.useState(null);
  const logRef     = React.useRef(null);
  const scanLogRef = React.useRef(null);

  const addLog = React.useCallback((msg, type = 'info') => {
    setLog(prev => [...prev, { msg, type, ts: new Date().toLocaleTimeString() }]);
    setTimeout(() => { if (logRef.current) logRef.current.scrollTop = logRef.current.scrollHeight; }, 20);
  }, []);

  const addScanLog = (msg, type = 'info') => {
    setScanLog(prev => [...prev, { msg, type }]);
    setTimeout(() => { if (scanLogRef.current) scanLogRef.current.scrollTop = scanLogRef.current.scrollHeight; }, 20);
  };

  // ── Step 1: Scan ───────────────────────────────────────────────────────

  const runScan = async () => {
    setPhase('scanning');
    setScanLog([]);
    setScanResult(null);

    // ── content_sections ─────────────────────────────────────────────────
    addScanLog('Querying content_sections…');
    const secRes = await db.from('content_sections').select('section_name, content_json');
    if (secRes.error) {
      addScanLog(`content_sections error: ${secRes.error.message}`, 'error');
    } else {
      addScanLog(`content_sections: ${(secRes.data || []).length} row(s) returned`);
    }

    const sections = (secRes.data || []).map(s => {
      const r = countBase64InValue(s.content_json);
      if (r.count > 0) addScanLog(`  ${s.section_name}: ${r.count} image(s), ${fmtKB(r.bytes)}`, 'success');
      return { name: s.section_name, count: r.count, bytes: r.bytes };
    }).filter(s => s.count > 0).sort((a, b) => b.bytes - a.bytes);

    // ── blog_posts — og_image + cover_url only (content fetched separately) ─
    addScanLog('Querying blog_posts (og_image + cover_url)…');
    const postCoverRes = await db.from('blog_posts').select('id, slug, og_image, cover_url');
    if (postCoverRes.error) {
      addScanLog(`blog_posts error: ${postCoverRes.error.message}`, 'error');
    } else {
      addScanLog(`blog_posts: ${(postCoverRes.data || []).length} row(s) returned`);
    }

    addScanLog('Querying blog_posts (content field)…');
    const postContentRes = await db.from('blog_posts').select('id, content');
    if (postContentRes.error) {
      addScanLog(`blog_posts content error: ${postContentRes.error.message}`, 'error');
    }

    const posts = (postCoverRes.data || []).map(p => {
      const ogBytes  = (p.og_image  || '').startsWith('data:image') ? (p.og_image  || '').length : 0;
      const covBytes = (p.cover_url || '').startsWith('data:image') ? (p.cover_url || '').length : 0;
      const contentRow = (postContentRes.data || []).find(c => c.id === p.id);
      const conStats   = contentRow ? countBase64InValue(contentRow.content) : { count: 0, bytes: 0 };
      const count = (ogBytes > 0 ? 1 : 0) + (covBytes > 0 ? 1 : 0) + conStats.count;
      const bytes = ogBytes + covBytes + conStats.bytes;
      if (count > 0) addScanLog(`  ${p.slug}: ${count} image(s), ${fmtKB(bytes)}`, 'success');
      return { id: p.id, slug: p.slug, count, bytes };
    }).filter(p => p.count > 0);

    // ── sign_wall_items ───────────────────────────────────────────────────
    addScanLog('Querying sign_wall_items…');
    const swRes = await db.from('sign_wall_items').select('id, signature_image');
    if (swRes.error) {
      addScanLog(`sign_wall_items error: ${swRes.error.message}`, 'error');
    }
    const swCount = (swRes.data || []).filter(i => (i.signature_image || '').startsWith('data:image')).length;
    if (swCount > 0) addScanLog(`  sign_wall_items: ${swCount} image(s)`, 'success');

    const totalCount = sections.reduce((s, r) => s + r.count, 0)
      + posts.reduce((s, r) => s + r.count, 0) + swCount;
    const totalBytes = sections.reduce((s, r) => s + r.bytes, 0)
      + posts.reduce((s, r) => s + r.bytes, 0);

    setScanResult({ sections, posts, swCount, totalCount, totalBytes });
    setPhase('scanned');

    if (totalCount === 0) {
      addScanLog('No base64 images found. Check errors above, or migration was already run.', 'info');
    } else {
      addScanLog(`Done — ${totalCount} image(s), ${fmtKB(totalBytes)} total.`, 'success');
    }
  };

  // ── Step 2: Backup ─────────────────────────────────────────────────────

  const downloadBackup = async () => {
    setPhase('backingUp');
    const [secRes, postRes, swRes] = await Promise.all([
      db.from('content_sections').select('*'),
      db.from('blog_posts').select('*'),
      db.from('sign_wall_items').select('*'),
    ]);
    const data = {
      created_at: new Date().toISOString(),
      content_sections: secRes.data,
      blog_posts: postRes.data,
      sign_wall_items: swRes.data,
    };
    const blob = new Blob([JSON.stringify(data, null, 2)], { type: 'application/json' });
    const url  = URL.createObjectURL(blob);
    const a    = document.createElement('a');
    a.href     = url;
    a.download = `blan-backup-${Date.now()}.json`;
    a.click();
    URL.revokeObjectURL(url);
    setPhase('backed');
  };

  // ── Step 3: Migrate ────────────────────────────────────────────────────

  const runMigration = async () => {
    setPhase('migrating');
    setLog([]);
    let migrated = 0, errors = 0, totalBytes = 0;

    const onUpload = (bytes) => { totalBytes += bytes; };

    // — content_sections —
    const { data: sections } = await db.from('content_sections').select('section_name, content_json');
    for (const sec of (sections || [])) {
      const stats = countBase64InValue(sec.content_json);
      if (stats.count === 0) continue;
      addLog(`Section: ${sec.section_name} — ${stats.count} image(s), ${fmtKB(stats.bytes)}`);
      try {
        const updated = await migrateJsonValue(sec.content_json, sec.section_name, onUpload);
        const { error } = await db.from('content_sections')
          .update({ content_json: updated })
          .eq('section_name', sec.section_name);
        if (error) throw new Error(error.message);
        migrated += stats.count;
        addLog(`  ✓ Done`, 'success');
      } catch (e) {
        addLog(`  ✗ ${e.message}`, 'error');
        errors++;
      }
    }

    // — blog_posts —
    const { data: posts } = await db.from('blog_posts').select('id, slug, og_image, cover_url, content');
    for (const post of (posts || [])) {
      const hasOg  = (post.og_image  || '').startsWith('data:image');
      const hasCov = (post.cover_url || '').startsWith('data:image');
      const conStats = countBase64InValue(post.content);
      if (!hasOg && !hasCov && conStats.count === 0) continue;

      addLog(`Post: ${post.slug}`);
      const upd = {};
      try {
        if (hasOg) {
          addLog(`  og_image ${fmtKB((post.og_image || '').length)}…`);
          upd.og_image = await uploadBase64ToStorage(post.og_image, `blog/${post.slug}`);
          onUpload((post.og_image || '').length);
          migrated++;
        }
        if (hasCov) {
          addLog(`  cover_url ${fmtKB((post.cover_url || '').length)}…`);
          upd.cover_url = await uploadBase64ToStorage(post.cover_url, `blog/${post.slug}`);
          onUpload((post.cover_url || '').length);
          migrated++;
        }
        if (conStats.count > 0) {
          addLog(`  content (${conStats.count} embedded images)…`);
          upd.content = await migrateJsonValue(post.content, `blog/${post.slug}/content`, onUpload);
          migrated += conStats.count;
        }
        const { error } = await db.from('blog_posts').update(upd).eq('id', post.id);
        if (error) throw new Error(error.message);
        addLog(`  ✓ Done`, 'success');
      } catch (e) {
        addLog(`  ✗ ${e.message}`, 'error');
        errors++;
      }
    }

    // — sign_wall_items —
    const { data: swItems } = await db.from('sign_wall_items').select('id, signature_image');
    for (const item of (swItems || [])) {
      if (!(item.signature_image || '').startsWith('data:image')) continue;
      addLog(`Sign wall item ${item.id}…`);
      try {
        const url = await uploadBase64ToStorage(item.signature_image, 'sign-wall');
        onUpload(item.signature_image.length);
        const { error } = await db.from('sign_wall_items').update({ signature_image: url }).eq('id', item.id);
        if (error) throw new Error(error.message);
        migrated++;
        addLog(`  ✓ Done`, 'success');
      } catch (e) {
        addLog(`  ✗ ${e.message}`, 'error');
        errors++;
      }
    }

    setResult({ migrated, errors, bytes: totalBytes });
    setPhase('done');
    addLog(`\nFinished — ${migrated} images migrated, ${errors} error(s)`, errors > 0 ? 'error' : 'success');
  };

  const totalCount = scanResult?.totalCount ?? 0;
  const totalBytes = scanResult?.totalBytes ?? 0;

  // ── Render ─────────────────────────────────────────────────────────────

  return (
    <div style={{ padding: 'var(--space-6)', maxWidth: 760 }}>
      <h1 className="page-title">Image Migration</h1>
      <p className="page-subtitle" style={{ marginBottom: 'var(--space-6)' }}>
        Moves all base64-encoded images out of Postgres JSONB columns and into Supabase Storage.
        Reduces API payload from ~19 MB → ~50 KB per admin load. Run once.
      </p>

      {/* Step 1 — Scan */}
      <StepCard
        n={1} title="Scan database" sub="Find all base64 images across content_sections, blog_posts, and sign_wall_items"
        action={runScan} actionLabel={phase === 'scanning' ? 'Scanning…' : 'Scan now'}
        disabled={phase === 'scanning' || phase === 'migrating'}
        done={phase === 'scanned' || phase === 'backed' || phase === 'backingUp' || phase === 'migrating' || phase === 'done'}
      >
        {scanLog.length > 0 && (
          <div ref={scanLogRef} style={{
            background: '#0f0f0f', color: '#d4d4d4', fontFamily: 'monospace', fontSize: 11,
            lineHeight: 1.6, padding: '10px 14px', borderRadius: 'var(--radius-xs)',
            maxHeight: 160, overflowY: 'auto', whiteSpace: 'pre-wrap',
            marginBottom: scanResult ? 'var(--space-3)' : 0,
          }}>
            {scanLog.map((e, i) => (
              <div key={i} style={{ color: e.type === 'error' ? '#f87171' : e.type === 'success' ? '#4ade80' : '#9ca3af' }}>
                {e.msg}
              </div>
            ))}
          </div>
        )}
        {scanResult && (
          <div>
            {scanResult.sections.length > 0 && (
              <div style={{ marginBottom: 'var(--space-3)' }}>
                <div style={{ fontSize: 11, fontWeight: 'var(--fw-semibold)', color: 'var(--fg-tertiary)', textTransform: 'uppercase', letterSpacing: '.06em', marginBottom: 6 }}>Content Sections</div>
                {scanResult.sections.map(s => (
                  <div key={s.name} style={{ display: 'flex', justifyContent: 'space-between', alignItems: 'center', fontSize: 'var(--fs-caption)', padding: '5px 0', borderBottom: '1px solid var(--border-subtle)' }}>
                    <span style={{ fontFamily: 'monospace' }}>{s.name}</span>
                    <span style={{ color: 'var(--fg-secondary)' }}>{s.count} image{s.count !== 1 ? 's' : ''} · {fmtKB(s.bytes)}</span>
                  </div>
                ))}
              </div>
            )}
            {scanResult.posts.length > 0 && (
              <div style={{ marginBottom: 'var(--space-3)' }}>
                <div style={{ fontSize: 11, fontWeight: 'var(--fw-semibold)', color: 'var(--fg-tertiary)', textTransform: 'uppercase', letterSpacing: '.06em', marginBottom: 6 }}>Blog Posts</div>
                {scanResult.posts.map(p => (
                  <div key={p.slug} style={{ display: 'flex', justifyContent: 'space-between', alignItems: 'center', fontSize: 'var(--fs-caption)', padding: '5px 0', borderBottom: '1px solid var(--border-subtle)' }}>
                    <span style={{ fontFamily: 'monospace' }}>{p.slug}</span>
                    <span style={{ color: 'var(--fg-secondary)' }}>{p.count} image{p.count !== 1 ? 's' : ''} · {fmtKB(p.bytes)}</span>
                  </div>
                ))}
              </div>
            )}
            {scanResult.swCount > 0 && (
              <div style={{ marginBottom: 'var(--space-3)', fontSize: 'var(--fs-caption)', color: 'var(--fg-secondary)' }}>
                Sign wall: {scanResult.swCount} signature image{scanResult.swCount !== 1 ? 's' : ''}
              </div>
            )}
            <div style={{ padding: 'var(--space-3)', background: 'var(--bg-surface-2)', borderRadius: 'var(--radius-xs)', fontSize: 'var(--fs-caption)', fontWeight: 'var(--fw-semibold)' }}>
              Total: {totalCount} image{totalCount !== 1 ? 's' : ''} · {fmtKB(totalBytes)} stored as base64 in Postgres
            </div>
          </div>
        )}
      </StepCard>

      {/* Step 2 — Backup */}
      <StepCard
        n={2} title="Download backup" sub="Full JSON export of content_sections, blog_posts, sign_wall_items — required before migrating"
        action={downloadBackup} actionLabel={phase === 'backingUp' ? 'Downloading…' : 'Download backup'}
        disabled={phase !== 'scanned' && phase !== 'backed' && phase !== 'backingUp'}
        done={phase === 'backed' || phase === 'migrating' || phase === 'done'}
      />

      {/* Step 3 — Migrate */}
      <StepCard
        n={3} title="Run migration" sub="Upload each base64 image to cms-images bucket and replace in DB with public URL"
        action={runMigration} actionLabel={phase === 'migrating' ? 'Migrating…' : 'Start migration'}
        disabled={phase !== 'backed' && phase !== 'done' && phase !== 'migrating'}
        done={phase === 'done'}
      >
        {log.length > 0 && (
          <div ref={logRef} style={{
            background: '#0f0f0f', color: '#d4d4d4', fontFamily: 'monospace', fontSize: 12,
            lineHeight: 1.65, padding: 'var(--space-4)', borderRadius: 'var(--radius-xs)',
            height: 320, overflowY: 'auto', whiteSpace: 'pre-wrap',
          }}>
            {log.map((e, i) => (
              <div key={i} style={{ color: e.type === 'error' ? '#f87171' : e.type === 'success' ? '#4ade80' : '#d4d4d4' }}>
                [{e.ts}] {e.msg}
              </div>
            ))}
          </div>
        )}
      </StepCard>

      {/* Done summary */}
      {result && (
        <div className="card" style={{ padding: 'var(--space-5)', borderLeft: `3px solid ${result.errors > 0 ? 'var(--color-accent-500)' : '#22c55e'}` }}>
          <div style={{ fontWeight: 'var(--fw-semibold)', fontSize: 'var(--fs-body-sm)', marginBottom: 'var(--space-2)' }}>
            {result.errors > 0 ? `Migration completed with ${result.errors} error(s)` : 'Migration complete'}
          </div>
          <div style={{ fontSize: 'var(--fs-caption)', color: 'var(--fg-secondary)', lineHeight: 1.7 }}>
            {result.migrated} image{result.migrated !== 1 ? 's' : ''} migrated to Supabase Storage<br/>
            ~{fmtKB(result.bytes)} removed from Postgres JSONB<br/>
            {result.errors === 0 && 'API payload per page load is now ~50KB instead of 19MB.'}
          </div>
          {result.errors > 0 && (
            <div style={{ marginTop: 'var(--space-3)', fontSize: 'var(--fs-caption)', color: 'var(--color-accent-500)' }}>
              Some images failed to upload — check the log above. Re-run to retry failed items.
            </div>
          )}
        </div>
      )}
    </div>
  );
}