SQL query cardFreeNew

"use client";

import { useEffect, useId, useMemo, useRef, useState } from "react";
import { Check, Copy, Database, Play, ShieldAlert, ShieldCheck, Timer } from "lucide-react";
import { AnimatePresence, motion, useReducedMotion } from "motion/react";
import { cn } from "@/lib/utils";

export type SqlQueryRow = Record<string, string | number>;

export type SqlQueryCardProps = {
  title?: string;
  database?: string;
  sql?: string;
  columns?: string[];
  rows?: SqlQueryRow[];
  durationMs?: number;
  /** Force the read-only badge. By default it is inferred from the SQL. */
  readOnly?: boolean;
  /** Rows the statement would change, shown in the write warning. */
  affectedRows?: number;
  /** Called when the user approves a statement that writes data. */
  onApproveWrite?: () => void;
  /** Loop a scripted run → results → write-warning demo. */
  autoPlay?: boolean;
  className?: string;
};

const DEFAULT_SQL = `SELECT plan,
       COUNT(*)     AS orgs,
       SUM(mrr_usd) AS mrr
FROM subscriptions
WHERE status = 'active'
GROUP BY plan
ORDER BY mrr DESC;`;

const WRITE_SQL = `-- Close trials that ended last month
UPDATE subscriptions
SET status = 'expired'
WHERE status = 'trialing'
  AND trial_ends_at < now() - interval '30 days';`;

const DEFAULT_COLUMNS = ["plan", "orgs", "mrr"];
const DEFAULT_ROWS: SqlQueryRow[] = [
  { plan: "Enterprise", orgs: 42, mrr: 84000 },
  { plan: "Pro", orgs: 318, mrr: 47700 },
  { plan: "Team", orgs: 506, mrr: 30360 },
  { plan: "Starter", orgs: 910, mrr: 18200 },
];

const KEYWORDS = new Set(
  "select from where group by order desc asc as and or not in is null limit offset join left right inner outer on having distinct update set insert into values delete returning with case when then else end interval create drop alter table truncate".split(" "),
);
const WRITES = /\b(update|delete|insert|drop|alter|truncate|create|grant|merge)\b/i;

const EASE = [0.22, 1, 0.36, 1] as const;
const TICK = 100;
const LOOP = 12000;

function useDemoClock(enabled: boolean, loop: number) {
  const ref = useRef<HTMLDivElement>(null);
  const [visible, setVisible] = useState(false);
  const [elapsed, setElapsed] = useState(0);
  useEffect(() => {
    const el = ref.current;
    if (!el || !enabled) return;
    const io = new IntersectionObserver(([entry]) => setVisible(entry.isIntersecting));
    io.observe(el);
    return () => io.disconnect();
  }, [enabled]);
  useEffect(() => {
    if (!enabled || !visible) return;
    const id = setInterval(() => setElapsed((e) => e + TICK), TICK);
    return () => clearInterval(id);
  }, [enabled, visible]);
  return { ref, t: elapsed % loop };
}

type View = { tab: "query" | "result"; write: boolean; lines: number; running: number | null; shownRows: number; ms: number; copied: "csv" | "json" | null };

function demoView(t: number): View {
  if (t < 6400) {
    return {
      tab: t >= 2600 ? "result" : "query",
      write: false,
      lines: Math.min(7, Math.floor(t / 180) + 1),
      running: t >= 1500 && t < 2600 ? (t - 1500) / 1100 : null,
      shownRows: t >= 2600 ? Math.min(4, Math.floor((t - 2600) / 120) + 1) : 0,
      ms: t >= 2600 ? Math.min(14, Math.round(((t - 2600) / 500) * 14)) : 0,
      copied: t >= 4300 && t < 5600 ? "csv" : null,
    };
  }
  return { tab: "query", write: true, lines: Math.min(5, Math.floor((t - 6400) / 200) + 1), running: null, shownRows: 0, ms: 0, copied: null };
}

function highlight(sql: string) {
  const out: React.ReactNode[] = [];
  const re = /(--.*$)|('(?:[^']|'')*')|(\b\d+(?:\.\d+)?\b)|([A-Za-z_][A-Za-z0-9_]*)|(\s+)|([^\sA-Za-z0-9_'])/gm;
  let m: RegExpExecArray | null;
  let i = 0;
  while ((m = re.exec(sql))) {
    const [tok] = m;
    let cls = "";
    if (m[1]) cls = "text-muted-foreground italic";
    else if (m[2]) cls = "text-emerald-700 dark:text-emerald-400";
    else if (m[3]) cls = "text-amber-700 dark:text-amber-400";
    else if (m[4]) {
      const lower = tok.toLowerCase();
      if (WRITES.test(tok)) cls = "font-semibold text-red-600 dark:text-red-400";
      else if (KEYWORDS.has(lower)) cls = "font-semibold text-violet-700 dark:text-violet-400";
      else if (sql[re.lastIndex] === "(") cls = "text-sky-700 dark:text-sky-400";
    } else if (m[6]) cls = "text-muted-foreground";
    out.push(cls ? <span key={i++} className={cls}>{tok}</span> : tok);
  }
  return out;
}

function fmt(v: string | number, col: string) {
  if (typeof v !== "number") return v;
  return /mrr|usd|amount|revenue|price/i.test(col) ? `$${v.toLocaleString("en-US")}` : v.toLocaleString("en-US");
}

/** An agent's SQL query and its result: highlighted SQL, a compact result table, run time, CSV / JSON copy and a read-only or write warning badge. */
export function SqlQueryCard({
  title,
  database = "warehouse · postgres 16",
  sql: sqlProp,
  columns = DEFAULT_COLUMNS,
  rows = DEFAULT_ROWS,
  durationMs = 14,
  readOnly,
  affectedRows = 1204,
  onApproveWrite,
  autoPlay = false,
  className,
}: SqlQueryCardProps) {
  const reduce = useReducedMotion() ?? false;
  const uid = useId();
  const [touched, setTouched] = useState(false);
  const [approved, setApproved] = useState(false);
  const [view, setView] = useState<View>({ tab: "result", write: false, lines: 99, running: null, shownRows: 99, ms: durationMs, copied: null });
  const demo = autoPlay && !reduce && !touched && !sqlProp;
  const { ref, t } = useDemoClock(demo, LOOP);

  // Sync the scripted frame during render (no effect → no extra commit per tick).
  const [syncedT, setSyncedT] = useState(-1);
  if (demo && t !== syncedT) {
    setSyncedT(t);
    setView(demoView(t));
  }

  const sql = sqlProp ?? (view.write ? WRITE_SQL : DEFAULT_SQL);
  const writes = readOnly === undefined ? WRITES.test(sql.replace(/--.*$/gm, "")) : !readOnly;
  const shownTitle = title ?? (writes ? "Expire stale trials" : "Active MRR by plan");
  const lines = sql.split("\n");
  const visibleLines = demo ? lines.slice(0, view.lines) : lines;
  const highlighted = useMemo(() => lines.map((l) => highlight(l)), [sql]); // eslint-disable-line react-hooks/exhaustive-deps
  const numericCol = columns.find((c) => rows.every((r) => typeof r[c] === "number") && /mrr|usd|amount|revenue|total/i.test(c));
  const max = numericCol ? Math.max(...rows.map((r) => Number(r[numericCol]))) : 0;
  const ms = demo ? view.ms : durationMs;
  const tab = writes ? "query" : view.tab;

  const copy = (kind: "csv" | "json") => {
    setTouched(true);
    const text =
      kind === "csv"
        ? [columns.join(","), ...rows.map((r) => columns.map((c) => r[c] ?? "").join(","))].join("\n")
        : JSON.stringify(rows, null, 2);
    navigator.clipboard?.writeText(text).catch(() => {});
    setView((v) => ({ ...v, copied: kind }));
    setTimeout(() => setView((v) => ({ ...v, copied: null })), 1400);
  };

  return (
    <div ref={ref} className={cn("w-full max-w-md text-sm", className)}>
      <div className="overflow-hidden rounded-2xl border bg-card text-card-foreground shadow-[0_1px_2px_rgb(0_0_0/0.04),0_12px_32px_-18px_rgb(0_0_0/0.25)]">
        {/* Header */}
        <div className="flex items-center gap-3 bg-gradient-to-b from-muted/50 to-transparent px-4 pt-3 pb-2">
          <span className="grid size-8 shrink-0 place-items-center rounded-lg border bg-background shadow-xs">
            <Database className="size-4" aria-hidden />
          </span>
          <div className="min-w-0 flex-1">
            <AnimatePresence mode="popLayout" initial={false}>
              <motion.p
                key={shownTitle}
                initial={reduce ? false : { opacity: 0, y: 6 }}
                animate={{ opacity: 1, y: 0 }}
                exit={reduce ? undefined : { opacity: 0, y: -6 }}
                className="truncate font-medium leading-tight"
              >
                {shownTitle}
              </motion.p>
            </AnimatePresence>
            <p className="mt-0.5 truncate font-mono text-[10px] text-muted-foreground">{database}</p>
          </div>
          <AnimatePresence mode="popLayout" initial={false}>
            <motion.span
              key={writes ? "w" : "r"}
              initial={reduce ? false : { opacity: 0, scale: 0.8 }}
              animate={{ opacity: 1, scale: 1 }}
              exit={reduce ? undefined : { opacity: 0, scale: 0.8 }}
              transition={{ type: "spring", stiffness: 500, damping: 26 }}
              className={cn(
                "inline-flex h-6 shrink-0 items-center gap-1 rounded-full border px-2 text-[11px] font-medium",
                writes
                  ? "border-red-500/30 bg-red-500/10 text-red-700 dark:text-red-300"
                  : "border-emerald-500/30 bg-emerald-500/10 text-emerald-700 dark:text-emerald-300",
              )}
            >
              {writes ? <ShieldAlert className="size-3" aria-hidden /> : <ShieldCheck className="size-3" aria-hidden />}
              {writes ? "Writes data" : "Read-only verified"}
            </motion.span>
          </AnimatePresence>
        </div>

        {/* Tabs */}
        <div className="flex items-end gap-4 border-b px-4" role="tablist">
          {(["query", "result"] as const).map((k) => {
            const on = tab === k;
            const disabled = k === "result" && writes;
            return (
              <button
                key={k}
                type="button"
                role="tab"
                aria-selected={on}
                disabled={disabled}
                onClick={() => {
                  setTouched(true);
                  setView((v) => ({ ...v, tab: k, lines: 99, shownRows: 99, running: null }));
                }}
                className={cn(
                  "relative flex h-8 cursor-pointer items-center gap-1.5 text-xs font-medium capitalize outline-none transition-colors focus-visible:text-foreground disabled:cursor-not-allowed disabled:opacity-40",
                  on ? "text-foreground" : "text-muted-foreground hover:text-foreground",
                )}
              >
                {k}
                {k === "result" && !writes && (
                  <span className="rounded-full bg-muted px-1.5 font-mono text-[10px] tabular-nums text-muted-foreground">{rows.length}</span>
                )}
                {on && <motion.span layoutId={`${uid}-tab`} className="absolute inset-x-0 -bottom-px h-0.5 rounded-full bg-foreground" transition={{ type: "spring", stiffness: 500, damping: 38 }} />}
              </button>
            );
          })}
        </div>

        {/* Body */}
        <div className="relative h-[178px] overflow-hidden">
          {view.running !== null && (
            <span className="absolute inset-x-0 top-0 z-10 h-0.5 bg-muted" aria-hidden>
              <span className="block h-full bg-foreground transition-[width] duration-100 ease-linear" style={{ width: `${view.running * 100}%` }} />
            </span>
          )}
          <AnimatePresence mode="wait" initial={false}>
            {tab === "query" ? (
              <motion.div
                key={`q-${writes}`}
                initial={reduce ? false : { opacity: 0, x: -10 }}
                animate={{ opacity: 1, x: 0 }}
                exit={reduce ? undefined : { opacity: 0, x: -10 }}
                transition={{ duration: 0.25, ease: EASE }}
                className="flex h-full flex-col"
              >
                <pre className="flex-1 overflow-auto px-0 py-2.5 font-mono text-[11.5px] leading-[1.6]">
                  <code className="block">
                    {visibleLines.map((_, i) => (
                      <span key={i} className="flex">
                        <span className="w-9 shrink-0 pr-3 text-right text-muted-foreground/50 select-none">{i + 1}</span>
                        <span className="whitespace-pre">{highlighted[i]}</span>
                      </span>
                    ))}
                  </code>
                </pre>
                <AnimatePresence>
                  {writes && (demo ? view.lines >= lines.length : true) && (
                    <motion.div
                      initial={reduce ? false : { opacity: 0, y: 16 }}
                      animate={{ opacity: 1, y: 0 }}
                      exit={{ opacity: 0 }}
                      transition={{ type: "spring", stiffness: 380, damping: 30, delay: 0.2 }}
                      className="m-2 mt-0 flex items-center gap-2.5 rounded-xl border border-red-500/25 bg-red-500/[0.06] p-2 pl-2.5 text-xs"
                      role="alert"
                    >
                      <ShieldAlert className="size-4 shrink-0 text-red-600 dark:text-red-400" aria-hidden />
                      <span className="min-w-0 flex-1 leading-snug">
                        <span className="font-medium">UPDATE changes ~{affectedRows.toLocaleString("en-US")} rows.</span>{" "}
                        <span className="text-muted-foreground">Needs approval before it runs.</span>
                      </span>
                      <button
                        type="button"
                        onClick={() => {
                          setTouched(true);
                          setApproved(true);
                          onApproveWrite?.();
                        }}
                        disabled={approved}
                        className="inline-flex h-7 shrink-0 cursor-pointer items-center gap-1 rounded-md bg-foreground px-2.5 text-[11px] font-medium text-background outline-none hover:opacity-90 focus-visible:ring-2 focus-visible:ring-ring disabled:opacity-60"
                      >
                        {approved ? <Check className="size-3" aria-hidden /> : <Play className="size-3 fill-current" aria-hidden />}
                        {approved ? "Approved" : "Approve & run"}
                      </button>
                    </motion.div>
                  )}
                </AnimatePresence>
              </motion.div>
            ) : (
              <motion.div
                key="r"
                initial={reduce ? false : { opacity: 0, x: 10 }}
                animate={{ opacity: 1, x: 0 }}
                exit={reduce ? undefined : { opacity: 0, x: 10 }}
                transition={{ duration: 0.25, ease: EASE }}
                className="h-full overflow-auto"
              >
                <table className="w-full text-left text-xs">
                  <thead className="sticky top-0 bg-card">
                    <tr className="border-b text-[10px] text-muted-foreground uppercase">
                      {columns.map((c) => {
                        const num = typeof rows[0]?.[c] === "number";
                        return (
                          <th key={c} className={cn("px-4 py-2 font-mono font-medium tracking-wide", num && "text-right")}>
                            {c}
                          </th>
                        );
                      })}
                    </tr>
                  </thead>
                  <tbody>
                    {rows.slice(0, view.shownRows).map((row, i) => (
                      <motion.tr
                        key={i}
                        initial={reduce ? false : { opacity: 0, y: 8 }}
                        animate={{ opacity: 1, y: 0 }}
                        transition={{ duration: 0.3, ease: EASE }}
                        className="border-b border-border/60 last:border-0 hover:bg-muted/40"
                      >
                        {columns.map((c, j) => {
                          const v = row[c];
                          const num = typeof v === "number";
                          return (
                            <td key={c} className={cn("px-4 py-2 tabular-nums", j === 0 ? "font-medium" : "font-mono text-[11px]", num && "text-right")}>
                              {c === numericCol ? (
                                <span className="inline-flex items-center justify-end gap-2">
                                  <span className="h-1.5 w-14 overflow-hidden rounded-full bg-muted">
                                    <motion.span
                                      className="block h-full rounded-full bg-foreground/70"
                                      initial={reduce ? false : { width: 0 }}
                                      animate={{ width: `${(Number(v) / max) * 100}%` }}
                                      transition={{ duration: 0.6, ease: EASE, delay: 0.1 }}
                                    />
                                  </span>
                                  {fmt(v, c)}
                                </span>
                              ) : (
                                fmt(v, c)
                              )}
                            </td>
                          );
                        })}
                      </motion.tr>
                    ))}
                  </tbody>
                </table>
              </motion.div>
            )}
          </AnimatePresence>
        </div>

        {/* Footer */}
        <div className="flex items-center justify-between gap-2 border-t bg-muted/30 px-4 py-2 text-[11px] text-muted-foreground">
          <span className="inline-flex items-center gap-1.5 font-mono tabular-nums" aria-live="polite">
            {view.running !== null ? (
              <>
                <span className={cn("size-2.5 rounded-full border-[1.5px] border-foreground/25 border-t-foreground", !reduce && "animate-spin")} />
                Running…
              </>
            ) : writes ? (
              <>
                <span className="size-1.5 rounded-full bg-amber-500" />
                Not run · waiting for approval
              </>
            ) : (
              <>
                <Timer className="size-3" aria-hidden />
                {rows.length} rows · {ms}ms
              </>
            )}
          </span>
          {!writes && (
            <span className="flex gap-1">
              {(["csv", "json"] as const).map((k) => (
                <button
                  key={k}
                  type="button"
                  onClick={() => copy(k)}
                  className="inline-flex h-6 cursor-pointer items-center gap-1 rounded-md border bg-background px-2 font-mono text-[10px] uppercase text-foreground outline-none transition-colors hover:bg-muted focus-visible:ring-2 focus-visible:ring-ring"
                >
                  <AnimatePresence mode="popLayout" initial={false}>
                    <motion.span
                      key={view.copied === k ? "ok" : "copy"}
                      initial={reduce ? false : { scale: 0.4, opacity: 0 }}
                      animate={{ scale: 1, opacity: 1 }}
                      exit={reduce ? undefined : { scale: 0.4, opacity: 0 }}
                      transition={{ type: "spring", stiffness: 600, damping: 24 }}
                    >
                      {view.copied === k ? <Check className="size-3 text-emerald-600 dark:text-emerald-400" aria-hidden /> : <Copy className="size-3" aria-hidden />}
                    </motion.span>
                  </AnimatePresence>
                  {view.copied === k ? "Copied" : k}
                </button>
              ))}
            </span>
          )}
        </div>
      </div>
    </div>
  );
}

export default function SqlQueryCardDemo() {
  return (
    <div className="flex h-[360px] w-full items-start justify-center pt-2">
      <SqlQueryCard autoPlay />
    </div>
  );
}