/* Atelier SQL — Éditeur SQL/REST sur D1 (lecture seule par défaut) */

const SQL_PRESETS = [
  { id: "top-margins", label: "Top 10 marges", sql: "SELECT name, margin, price, stock FROM products\nWHERE stock > 0\nORDER BY margin DESC\nLIMIT 10;" },
  { id: "low-stock",   label: "Stock critique", sql: "SELECT name, asin, stock, vel\nFROM products\nWHERE stock <= 15 AND vel > 0\nORDER BY stock ASC;" },
  { id: "by-owner",    label: "Par owner", sql: "SELECT owner, COUNT(*) as cnt, AVG(margin) as avg_margin\nFROM products\nGROUP BY owner\nORDER BY cnt DESC;" },
  { id: "open-bal",    label: "Soldes ouverts", sql: "SELECT name, openBalance, terms\nFROM suppliers\nWHERE openBalance > 0\nORDER BY openBalance DESC;" },
  { id: "monthly-rev", label: "Revenu mensuel", sql: "SELECT m, rev, profit, ROUND(profit*100.0/rev, 1) as margin_pct\nFROM revenue_monthly\nORDER BY m DESC\nLIMIT 6;" },
];

// Synthetic in-memory executor (read-only) — emulates D1
function runSql(sql) {
  const q = sql.toLowerCase().trim();

  // Very loose pattern matching for the presets above
  if (q.includes("from products")) {
    let rows = PRODUCTS.map(p => ({ name: p.name, asin: p.asin, owner: p.owner, margin: p.margin, price: p.price, stock: p.stock, vel: p.vel }));
    if (q.includes("where stock <= 15")) rows = rows.filter(r => r.stock <= 15 && r.vel > 0);
    else if (q.includes("where stock > 0")) rows = rows.filter(r => r.stock > 0);
    if (q.includes("group by owner")) {
      const grp = {};
      PRODUCTS.forEach(p => {
        (grp[p.owner] ||= { owner: p.owner, cnt: 0, sumMargin: 0 });
        grp[p.owner].cnt++;
        grp[p.owner].sumMargin += p.margin;
      });
      rows = Object.values(grp).map(g => ({ owner: g.owner, cnt: g.cnt, avg_margin: (g.sumMargin / g.cnt).toFixed(1) }));
    } else if (q.includes("order by margin desc")) rows.sort((a, b) => b.margin - a.margin);
    else if (q.includes("order by stock asc")) rows.sort((a, b) => a.stock - b.stock);
    const limitMatch = q.match(/limit\s+(\d+)/);
    if (limitMatch) rows = rows.slice(0, Number(limitMatch[1]));
    return { ok: true, rows, took: 4 + Math.round(Math.random() * 8) };
  }
  if (q.includes("from suppliers")) {
    let rows = SUPPLIERS.map(s => ({ name: s.name, openBalance: s.openBalance, terms: s.terms }));
    if (q.includes("where openbalance > 0")) rows = rows.filter(r => r.openBalance > 0);
    if (q.includes("order by openbalance desc")) rows.sort((a, b) => b.openBalance - a.openBalance);
    return { ok: true, rows, took: 3 + Math.round(Math.random() * 6) };
  }
  if (q.includes("from revenue_monthly")) {
    let rows = REVENUE_MONTHLY.map(r => ({ m: r.m, rev: r.rev, profit: r.profit, margin_pct: ((r.profit / r.rev) * 100).toFixed(1) }));
    if (q.includes("order by m desc")) rows = rows.slice().reverse();
    const limitMatch = q.match(/limit\s+(\d+)/);
    if (limitMatch) rows = rows.slice(0, Number(limitMatch[1]));
    return { ok: true, rows, took: 5 + Math.round(Math.random() * 5) };
  }
  return { ok: false, error: "Table inconnue. Tables dispos: products, suppliers, revenue_monthly" };
}

const WorkshopSQL = () => {
  const [sql, setSql] = useState(SQL_PRESETS[0].sql);
  const [result, setResult] = useState(null);
  const [running, setRunning] = useState(false);

  const exec = () => {
    setRunning(true);
    setTimeout(() => {
      setResult(runSql(sql));
      setRunning(false);
    }, 250);
  };

  // run initial query on mount
  useEffect(() => { exec(); }, []);

  return (
    <div className="page">
      <div className="page-head">
        <div>
          <div className="page-title">Atelier SQL</div>
          <div className="page-sub">
            Éditeur SQL · lecture seule sur D1 · tables: products, suppliers, revenue_monthly
          </div>
        </div>
        <div className="row" style={{ gap: 6 }}>
          <Badge tone="green" dot>Read-only</Badge>
        </div>
      </div>

      <div className="grid" style={{ gridTemplateColumns: "220px 1fr", gap: 14, alignItems: "flex-start" }}>
        {/* Presets */}
        <div className="card" style={{ padding: 0, overflow: "hidden" }}>
          <div style={{ padding: 10, borderBottom: "1px solid var(--border-subtle)", fontSize: 11, color: "var(--text-dim)", textTransform: "uppercase", letterSpacing: "0.06em" }}>
            Requêtes types
          </div>
          {SQL_PRESETS.map(p => (
            <button
              key={p.id}
              onClick={() => { setSql(p.sql); setTimeout(exec, 50); }}
              style={{
                display: "block", width: "100%", textAlign: "left",
                padding: "10px 12px", border: 0,
                borderBottom: "1px solid var(--border-subtle)",
                background: "transparent", color: "var(--text)",
                cursor: "pointer", fontSize: 12.5,
              }}
            >
              <Icon name="chevron-right" size={10} style={{ color: "var(--text-faint)", marginRight: 6 }} />
              {p.label}
            </button>
          ))}
        </div>

        {/* Editor + results */}
        <div className="stack" style={{ gap: 12 }}>
          <div className="card" style={{ padding: 0, overflow: "hidden" }}>
            <div className="between" style={{ padding: "8px 12px", borderBottom: "1px solid var(--border-subtle)" }}>
              <span className="mono" style={{ fontSize: 11, color: "var(--text-dim)" }}>kcc.db · sql</span>
              <div className="row" style={{ gap: 6 }}>
                <button className="btn" data-variant="ghost" data-size="sm" onClick={() => setSql("")}>Effacer</button>
                <button className="btn" data-variant="primary" data-size="sm" onClick={exec} disabled={running}>
                  <Icon name="play" size={11} /> {running ? "Exécution…" : "Exécuter"}
                </button>
              </div>
            </div>
            <textarea
              value={sql}
              onChange={(e) => setSql(e.target.value)}
              spellCheck={false}
              style={{
                width: "100%", minHeight: 160, padding: 14,
                background: "var(--bg-1)", border: "none", outline: "none",
                color: "var(--text)", fontFamily: "var(--font-mono)", fontSize: 12.5,
                resize: "vertical", lineHeight: 1.6,
              }}
            />
          </div>

          {result && (
            <div className="card" style={{ padding: 0, overflow: "auto" }}>
              <div style={{ padding: "8px 12px", borderBottom: "1px solid var(--border-subtle)" }}>
                {result.ok ? (
                  <div className="mono" style={{ fontSize: 11, color: "var(--text-dim)" }}>
                    <span style={{ color: "var(--green)" }}>✓</span> {result.rows.length} ligne{result.rows.length > 1 ? "s" : ""} · {result.took}ms
                  </div>
                ) : (
                  <div className="mono" style={{ fontSize: 11, color: "var(--red)" }}>✗ {result.error}</div>
                )}
              </div>
              {result.ok && result.rows.length > 0 && (
                <table className="table">
                  <thead>
                    <tr>
                      {Object.keys(result.rows[0]).map(k => (
                        <th key={k} style={{ textTransform: "lowercase", fontFamily: "var(--font-mono)", fontSize: 11 }}>{k}</th>
                      ))}
                    </tr>
                  </thead>
                  <tbody>
                    {result.rows.map((row, i) => (
                      <tr key={i}>
                        {Object.values(row).map((v, j) => (
                          <td key={j} className="mono" style={{ fontSize: 12 }}>
                            {typeof v === "number" ? v.toLocaleString() : String(v)}
                          </td>
                        ))}
                      </tr>
                    ))}
                  </tbody>
                </table>
              )}
            </div>
          )}
        </div>
      </div>
    </div>
  );
};

window.WorkshopSQL = WorkshopSQL;