// BEGIN CHANGE: item 8 - console SQL do admin (somente schema do portal, com auditoria)
"use client";

import { useState } from "react";
import { apiUrl } from "@/lib/api";

type SqlResult = {
  ok: boolean;
  readOnly: boolean;
  command: string;
  columns?: string[];
  rows?: Record<string, unknown>[];
  rowCount?: number;
  affectedRows?: number;
  elapsedMs?: number;
};

// BEGIN CHANGE: item 8 (2026) - manual + exemplos baseados no schema ot_new
const SQL_EXAMPLES: { label: string; sql: string }[] = [
  { label: "Contas admin (page_access)", sql: "SELECT id, name, page_access, premium_points FROM accounts WHERE page_access > 0 ORDER BY page_access DESC LIMIT 50;" },
  { label: "Top 20 players por level", sql: "SELECT name, level, vocation, reset FROM players WHERE deleted = 0 ORDER BY level DESC LIMIT 20;" },
  { label: "Players online agora", sql: "SELECT name, level, vocation FROM players WHERE online > 0 ORDER BY level DESC;" },
  { label: "Buscar conta por nome", sql: "SELECT id, name, email, page_access FROM accounts WHERE name LIKE '%NOME%' LIMIT 20;" },
  { label: "Chars de uma conta", sql: "SELECT p.id, p.name, p.level FROM players p WHERE p.account_id = 1 ORDER BY p.level DESC;" },
  { label: "Fila do shop (entrega)", sql: "SELECT id, name, type, action, delete_it FROM z_ots_comunication ORDER BY id DESC LIMIT 30;" },
  { label: "Guilds e donos", sql: "SELECT g.id, g.name, p.name AS owner FROM guilds g LEFT JOIN players p ON p.id = g.ownerid ORDER BY g.id LIMIT 30;" },
  { label: "Ultimas doacoes (MercadoPago)", sql: "SELECT id, account_name, amount, points, processed FROM mercadopago_transactions ORDER BY id DESC LIMIT 20;" },
  { label: "Estrutura de uma tabela", sql: "DESCRIBE players;" },
  { label: "Ajustar premium_points (escrita)", sql: "UPDATE accounts SET premium_points = premium_points + 0 WHERE id = 1;" },
];

const SQL_SCHEMA_HINT: { table: string; cols: string }[] = [
  { table: "accounts", cols: "id, name, email, password, salt, page_access, premium_points, backup_points, guild_points, premdays, blocked" },
  { table: "players", cols: "id, account_id, name, level, vocation, experience, reset, salvar, online, deleted, town_id, group_id, balance" },
  { table: "guilds", cols: "id, name, ownerid, creationdata, real_castle, favela" },
  { table: "z_ots_comunication", cols: "id, name, type, action, param1..param7, delete_it" },
  { table: "z_shop_offer", cols: "id, catalog, points, offer_type, offer_name, sort_order, pacc_days, active" },
  { table: "z_shop_offer_group", cols: "id, offer_id, label, sort_order, min_select, max_select" },
  { table: "z_shop_history_item", cols: "id, catalog, to_name, price, offer_id, trans_state, item_name" },
  { table: "z_news_tickers", cols: "date, author, text, image_id, hide_ticker" },
  { table: "mercadopago_transactions", cols: "id, account_name, amount, points, processed" },
  { table: "paypal_transactions", cols: "txn_id, account_id, amount, points, processed" },
];
// END CHANGE

export function AdminSql({ token }: { token: string }) {
  const [sql, setSql] = useState("");
  const [busy, setBusy] = useState(false);
  const [err, setErr] = useState("");
  const [result, setResult] = useState<SqlResult | null>(null);
  // BEGIN CHANGE: item 8 (2026) - toggle do manual
  const [showManual, setShowManual] = useState(false);
  // END CHANGE

  async function run(confirm: boolean) {
    setBusy(true);
    setErr("");
    if (!confirm) setResult(null);
    try {
      const r = await fetch(apiUrl("admin-sql.php"), {
        method: "POST",
        headers: {
          "Content-Type": "application/json",
          Authorization: `Bearer ${token}`,
        },
        body: JSON.stringify({ sql, confirm }),
      });
      const json = await r.json().catch(() => ({}));
      if (r.status === 409 && json.error === "confirm_required") {
        // comando de escrita: pede confirmacao
        if (
          window.confirm(
            "Este comando ALTERA dados no banco do portal. A execucao sera registrada no Audit Log. Confirmar?",
          )
        ) {
          await run(true);
        }
        return;
      }
      if (!r.ok) {
        setErr(json.error || "Falha ao executar.");
        return;
      }
      setResult(json as SqlResult);
    } catch {
      setErr("Falha ao executar.");
    } finally {
      setBusy(false);
    }
  }

  return (
    <div className="space-y-4">
      <div>
        <h2 className="text-xl font-semibold">Console SQL</h2>
        <p className="mt-1 text-sm text-muted">
          Executa um comando por vez no banco do portal (ot_new). SELECT/SHOW rodam direto;
          INSERT/UPDATE/DELETE pedem confirmacao. Toda execucao vai para o Audit Log. Schemas de
          sistema e comandos administrativos sao bloqueados.
        </p>
      </div>

      {err && <p className="text-sm text-red-400">{err}</p>}

      {/* BEGIN CHANGE: item 8 (2026) - manual + exemplos clicaveis */}
      <div className="rounded-lg border border-border bg-panel/60">
        <button
          type="button"
          onClick={() => setShowManual((v) => !v)}
          className="flex w-full items-center justify-between px-3 py-2 text-left text-sm font-medium"
        >
          <span>Manual e exemplos (schema ot_new)</span>
          <span className="text-muted">{showManual ? "-" : "+"}</span>
        </button>
        {showManual && (
          <div className="space-y-4 border-t border-border px-3 py-3 text-sm">
            <div>
              <p className="mb-1 font-medium">Como usar</p>
              <ul className="list-disc space-y-1 pl-5 text-muted">
                <li>Um comando por vez (nao use varios &quot;;&quot; no mesmo envio).</li>
                <li>SELECT / SHOW / DESCRIBE / EXPLAIN rodam direto e mostram a tabela abaixo.</li>
                <li>INSERT / UPDATE / DELETE pedem confirmacao e vao para o Audit Log.</li>
                <li>Bloqueado: information_schema, mysql., outros bancos, CREATE/DROP DATABASE, GRANT.</li>
                <li>Sempre use WHERE em UPDATE/DELETE para nao afetar a tabela inteira.</li>
              </ul>
            </div>
            <div>
              <p className="mb-1 font-medium">Exemplos (clique para carregar)</p>
              <div className="flex flex-wrap gap-2">
                {SQL_EXAMPLES.map((ex) => (
                  <button
                    key={ex.label}
                    type="button"
                    onClick={() => setSql(ex.sql)}
                    className="rounded border border-border px-2 py-1 text-xs hover:border-brand"
                    title={ex.sql}
                  >
                    {ex.label}
                  </button>
                ))}
              </div>
            </div>
            <div>
              <p className="mb-1 font-medium">Tabelas principais</p>
              <div className="overflow-auto rounded border border-border">
                <table className="min-w-full text-left text-xs">
                  <thead className="bg-panel text-muted">
                    <tr>
                      <th className="px-2 py-1 font-medium">Tabela</th>
                      <th className="px-2 py-1 font-medium">Colunas uteis</th>
                    </tr>
                  </thead>
                  <tbody>
                    {SQL_SCHEMA_HINT.map((s) => (
                      <tr key={s.table} className="border-t border-border/60">
                        <td className="px-2 py-1 font-mono">{s.table}</td>
                        <td className="px-2 py-1 text-muted">{s.cols}</td>
                      </tr>
                    ))}
                  </tbody>
                </table>
              </div>
              <p className="mt-1 text-xs text-muted">
                Dica: use <span className="font-mono">SHOW TABLES;</span> para listar tudo e{" "}
                <span className="font-mono">DESCRIBE nome_tabela;</span> para ver as colunas.
              </p>
            </div>
          </div>
        )}
      </div>
      {/* END CHANGE */}

      <textarea
        className="min-h-[140px] w-full rounded-lg border border-border bg-background px-3 py-2 font-mono text-sm"
        placeholder="SELECT id, name, page_access FROM accounts LIMIT 20;"
        value={sql}
        onChange={(e) => setSql(e.target.value)}
        spellCheck={false}
      />
      <div className="flex flex-wrap items-center gap-2">
        <button
          type="button"
          disabled={busy || sql.trim() === ""}
          onClick={() => run(false)}
          className="rounded-md bg-brand px-4 py-2 text-sm text-background disabled:opacity-50"
        >
          {busy ? "Executando..." : "Executar"}
        </button>
        <button
          type="button"
          disabled={busy}
          onClick={() => {
            setSql("");
            setResult(null);
            setErr("");
          }}
          className="rounded-md border border-border px-3 py-2 text-sm"
        >
          Limpar
        </button>
      </div>

      {result && (
        <div className="space-y-2">
          <p className="text-sm text-muted">
            {result.readOnly
              ? `${result.rowCount ?? 0} linha(s)`
              : `${result.affectedRows ?? 0} linha(s) afetada(s)`}
            {typeof result.elapsedMs === "number" ? ` - ${result.elapsedMs} ms` : ""}
          </p>
          {result.readOnly && result.columns && result.columns.length > 0 && (
            <div className="overflow-auto rounded-lg border border-border">
              <table className="min-w-full text-left text-xs">
                <thead className="bg-panel text-muted">
                  <tr>
                    {result.columns.map((c) => (
                      <th key={c} className="whitespace-nowrap px-3 py-2 font-medium">
                        {c}
                      </th>
                    ))}
                  </tr>
                </thead>
                <tbody>
                  {(result.rows || []).map((row, i) => (
                    <tr key={i} className="border-t border-border/60">
                      {result.columns!.map((c) => (
                        <td key={c} className="whitespace-nowrap px-3 py-1.5 font-mono">
                          {formatCell(row[c])}
                        </td>
                      ))}
                    </tr>
                  ))}
                  {(result.rows || []).length === 0 && (
                    <tr>
                      <td
                        colSpan={result.columns.length}
                        className="px-3 py-6 text-center text-muted"
                      >
                        Sem resultados.
                      </td>
                    </tr>
                  )}
                </tbody>
              </table>
            </div>
          )}
        </div>
      )}
    </div>
  );
}

function formatCell(v: unknown): string {
  if (v === null || v === undefined) return "NULL";
  if (typeof v === "object") return JSON.stringify(v);
  return String(v);
}
// END CHANGE