"use client";

import { useEffect, useMemo, useState } from "react";

type Column = {
  name: string;
  dataType: string;
  columnType: string;
  nullable: boolean;
  defaultValue: unknown;
  primary: boolean;
  generated: boolean;
  autoIncrement: boolean;
  editable: boolean;
};

type TableDefinition = {
  name: string;
  category: "referentiel" | "metier" | "systeme";
  protected: boolean;
  exportable: boolean;
  columns: Column[];
  primaryKey: string;
  hasActiveColumn: boolean;
};

type Row = Record<string, unknown>;

const PAGE_SIZE = 50;

function isBoolean(column: Column) {
  return column.columnType.toLowerCase() === "tinyint(1)";
}

function isLongText(column: Column) {
  return ["text", "tinytext", "mediumtext", "longtext", "json"].includes(
    column.dataType,
  );
}

function valueForInput(value: unknown, column: Column) {
  if (value === null || value === undefined) return "";
  const text = String(value);
  if (column.dataType === "date") return text.slice(0, 10);
  if (["datetime", "timestamp"].includes(column.dataType)) {
    return text.replace("Z", "").slice(0, 16);
  }
  return text;
}

function preview(value: unknown) {
  if (value === null || value === undefined) return "NULL";
  if (typeof value === "object") return JSON.stringify(value);
  const text = String(value);
  return text.length > 90 ? `${text.slice(0, 87)}…` : text;
}

export default function DatabaseTablesPanel() {
  const [tables, setTables] = useState<TableDefinition[]>([]);
  const [selectedName, setSelectedName] = useState("");
  const [rows, setRows] = useState<Row[]>([]);
  const [total, setTotal] = useState(0);
  const [offset, setOffset] = useState(0);
  const [search, setSearch] = useState("");
  const [appliedSearch, setAppliedSearch] = useState("");
  const [editing, setEditing] = useState<Row | null>(null);
  const [creating, setCreating] = useState(false);
  const [form, setForm] = useState<Row>({});
  const [loading, setLoading] = useState(true);
  const [saving, setSaving] = useState(false);
  const [exporting, setExporting] = useState<string | null>(null);
  const [error, setError] = useState<string | null>(null);
  const [message, setMessage] = useState<string | null>(null);

  const selected = tables.find((table) => table.name === selectedName) ?? null;

  const grouped = useMemo(
    () =>
      (["referentiel", "metier", "systeme"] as const).map((category) => ({
        category,
        tables: tables.filter((table) => table.category === category),
      })),
    [tables],
  );

  async function api(path: string, init?: RequestInit) {
    const token = localStorage.getItem("token");
    return fetch(`${process.env.NEXT_PUBLIC_API_URL}${path}`, {
      ...init,
      cache: "no-store",
      headers: {
        Authorization: `Bearer ${token}`,
        ...(init?.body ? { "Content-Type": "application/json" } : {}),
        ...init?.headers,
      },
    });
  }

  async function loadTables() {
    const response = await api("/api/admin/tables");
    const data = await response.json();
    if (!response.ok) throw new Error(data.message ?? "Chargement impossible");
    const items = (data.items ?? []) as TableDefinition[];
    setTables(items);
    setSelectedName((current) => current || items[0]?.name || "");
  }

  async function loadRows(tableName = selectedName, start = offset) {
    if (!tableName) return;
    setLoading(true);
    setError(null);
    try {
      const query = new URLSearchParams({
        limit: String(PAGE_SIZE),
        offset: String(start),
      });
      if (appliedSearch) query.set("search", appliedSearch);
      const response = await api(
        `/api/admin/tables/${encodeURIComponent(tableName)}?${query}`,
      );
      const data = await response.json();
      if (!response.ok)
        throw new Error(data.message ?? "Chargement impossible");
      setRows(data.rows ?? []);
      setTotal(data.total ?? 0);
    } catch (reason) {
      setError(reason instanceof Error ? reason.message : "Erreur");
    } finally {
      setLoading(false);
    }
  }

  useEffect(() => {
    loadTables()
      .catch((reason) =>
        setError(reason instanceof Error ? reason.message : "Erreur"),
      )
      .finally(() => setLoading(false));
  }, []);

  useEffect(() => {
    if (!selectedName) return;
    setOffset(0);
    setSearch("");
    setAppliedSearch("");
    void loadRows(selectedName, 0);
  }, [selectedName]);

  useEffect(() => {
    if (selectedName) void loadRows(selectedName, offset);
  }, [offset, appliedSearch]);

  function openEdit(row: Row) {
    setCreating(false);
    setEditing(row);
    setForm({ ...row });
    setError(null);
    setMessage(null);
  }

  function openCreate() {
    if (!selected) return;
    const initial = Object.fromEntries(
      selected.columns
        .filter((column) => column.editable)
        .map((column) => [
          column.name,
          column.name.toLowerCase() === "actif"
            ? true
            : (column.defaultValue ?? ""),
        ]),
    );
    setCreating(true);
    setEditing(initial);
    setForm(initial);
    setError(null);
    setMessage(null);
  }

  async function save() {
    if (!selected || !editing) return;
    setSaving(true);
    setError(null);
    try {
      const id = editing[selected.primaryKey];
      const path = creating
        ? `/api/admin/tables/${encodeURIComponent(selected.name)}`
        : `/api/admin/tables/${encodeURIComponent(selected.name)}/${encodeURIComponent(String(id))}`;
      const response = await api(path, {
        method: creating ? "POST" : "PATCH",
        body: JSON.stringify(form),
      });
      const data = await response.json();
      if (!response.ok)
        throw new Error(data.message ?? "Enregistrement impossible");
      setEditing(null);
      setMessage(creating ? "Ligne créée." : "Ligne mise à jour.");
      await loadRows();
    } catch (reason) {
      setError(reason instanceof Error ? reason.message : "Erreur");
    } finally {
      setSaving(false);
    }
  }

  async function downloadCsv(
    path: string,
    fallbackFilename: string,
    exportKey: string,
  ) {
    setExporting(exportKey);
    setError(null);
    setMessage(null);
    try {
      const response = await api(path);
      if (!response.ok) {
        const contentType = response.headers.get("content-type") ?? "";
        const data = contentType.includes("application/json")
          ? ((await response.json()) as { message?: string })
          : { message: await response.text() };
        throw new Error(data.message || "Export impossible");
      }

      const disposition = response.headers.get("content-disposition") ?? "";
      const filename =
        disposition.match(/filename="([^"]+)"/i)?.[1] ?? fallbackFilename;
      const url = URL.createObjectURL(await response.blob());
      const link = document.createElement("a");
      link.href = url;
      link.download = filename;
      document.body.appendChild(link);
      link.click();
      link.remove();
      URL.revokeObjectURL(url);
      setMessage(`Export téléchargé : ${filename}`);
    } catch (reason) {
      setError(reason instanceof Error ? reason.message : "Export impossible");
    } finally {
      setExporting(null);
    }
  }

  return (
    <section className="rounded-lg bg-white p-4 shadow">
      <div className="mb-4 flex flex-wrap items-end justify-between gap-3">
        <div>
          <h2 className="text-xl font-bold">Tables et valeurs</h2>
          <p className="text-sm text-slate-500">
            Modification des données uniquement. La structure de la base n’est
            jamais modifiée ici.
          </p>
        </div>
        <div className="flex flex-wrap gap-2">
          <button
            disabled={exporting !== null}
            onClick={() =>
              void downloadCsv(
                "/api/admin/exports/employes-contrats-actifs",
                "salaries-contrats-actifs.csv",
                "active-employees",
              )
            }
            className="rounded bg-indigo-600 px-4 py-2 font-semibold text-white disabled:opacity-50"
          >
            {exporting === "active-employees"
              ? "Export en cours…"
              : "Exporter salariés + contrats actifs"}
          </button>
          {selected && !selected.protected && (
            <button
              onClick={openCreate}
              className="rounded bg-green-600 px-4 py-2 font-semibold text-white"
            >
              Ajouter une ligne
            </button>
          )}
        </div>
      </div>

      {error && (
        <p className="mb-3 rounded bg-red-50 p-3 text-red-700">{error}</p>
      )}
      {message && (
        <p className="mb-3 rounded bg-green-50 p-3 text-green-700">{message}</p>
      )}

      <div className="grid gap-4 xl:grid-cols-[280px_minmax(0,1fr)]">
        <aside className="max-h-[68vh] overflow-y-auto rounded border">
          {grouped.map(({ category, tables: categoryTables }) => (
            <div key={category}>
              <h3 className="sticky top-0 bg-slate-200 px-3 py-2 text-xs font-bold uppercase">
                {category === "referentiel"
                  ? "Référentiels"
                  : category === "metier"
                    ? "Données métier"
                    : "Système"}
              </h3>
              {categoryTables.map((table) => (
                <button
                  key={table.name}
                  onClick={() => setSelectedName(table.name)}
                  className={`flex w-full items-center justify-between border-b px-3 py-2 text-left text-sm ${selectedName === table.name ? "bg-blue-600 text-white" : "hover:bg-slate-50"}`}
                >
                  <span>{table.name}</span>
                  {table.hasActiveColumn && (
                    <span title="Colonne actif disponible">●</span>
                  )}
                </button>
              ))}
            </div>
          ))}
        </aside>

        <div className="min-w-0">
          {selected && (
            <>
              <div className="mb-3 flex flex-wrap items-center gap-2">
                <h3 className="mr-auto text-lg font-bold">{selected.name}</h3>
                {selected.protected && (
                  <span className="rounded bg-amber-100 px-2 py-1 text-xs text-amber-800">
                    Protégée — utiliser l’écran des habilitations
                  </span>
                )}
                <button
                  disabled={!selected.exportable || exporting !== null}
                  title={
                    !selected.exportable
                      ? "L’export des tables d’authentification est désactivé."
                      : `Exporter toutes les valeurs de ${selected.name}`
                  }
                  onClick={() =>
                    void downloadCsv(
                      `/api/admin/tables/${encodeURIComponent(selected.name)}/export`,
                      `${selected.name}.csv`,
                      `table:${selected.name}`,
                    )
                  }
                  className="rounded border border-emerald-700 px-3 py-2 text-sm font-semibold text-emerald-700 disabled:border-slate-300 disabled:text-slate-400"
                >
                  {exporting === `table:${selected.name}`
                    ? "Export en cours…"
                    : "Exporter les valeurs"}
                </button>
                <input
                  value={search}
                  onChange={(event) => setSearch(event.target.value)}
                  onKeyDown={(event) => {
                    if (event.key === "Enter") {
                      setOffset(0);
                      setAppliedSearch(search.trim());
                    }
                  }}
                  placeholder="Rechercher dans les textes…"
                  className="rounded border px-3 py-2 text-sm"
                />
                <button
                  onClick={() => {
                    setOffset(0);
                    setAppliedSearch(search.trim());
                  }}
                  className="rounded bg-blue-600 px-3 py-2 text-sm font-semibold text-white"
                >
                  Rechercher
                </button>
              </div>

              <div className="overflow-x-auto rounded border">
                <table className="w-full border-collapse text-xs">
                  <thead>
                    <tr className="bg-sky-100 text-left">
                      {selected.columns.map((column) => (
                        <th
                          key={column.name}
                          className="whitespace-nowrap border p-2"
                        >
                          {column.name}
                        </th>
                      ))}
                      <th className="border p-2">Action</th>
                    </tr>
                  </thead>
                  <tbody>
                    {rows.map((row) => {
                      const activeColumn = selected.columns.find(
                        (column) => column.name.toLowerCase() === "actif",
                      );
                      const inactive = activeColumn
                        ? !Boolean(row[activeColumn.name])
                        : false;
                      return (
                        <tr
                          key={String(row[selected.primaryKey])}
                          className={
                            inactive
                              ? "bg-slate-100 text-slate-400"
                              : "even:bg-slate-50"
                          }
                        >
                          {selected.columns.map((column) => (
                            <td
                              key={column.name}
                              className="max-w-[260px] truncate border p-2"
                              title={preview(row[column.name])}
                            >
                              {isBoolean(column)
                                ? Boolean(row[column.name])
                                  ? "Oui"
                                  : "Non"
                                : preview(row[column.name])}
                            </td>
                          ))}
                          <td className="border p-2">
                            <button
                              disabled={selected.protected}
                              onClick={() => openEdit(row)}
                              className="rounded bg-blue-600 px-2 py-1 font-semibold text-white disabled:bg-slate-300"
                            >
                              Modifier
                            </button>
                          </td>
                        </tr>
                      );
                    })}
                    {!loading && rows.length === 0 && (
                      <tr>
                        <td
                          colSpan={selected.columns.length + 1}
                          className="p-6 text-center text-slate-500"
                        >
                          Aucune ligne.
                        </td>
                      </tr>
                    )}
                  </tbody>
                </table>
              </div>

              <div className="mt-3 flex items-center justify-between text-sm">
                <span>{total} ligne(s)</span>
                <div className="flex gap-2">
                  <button
                    disabled={offset === 0}
                    onClick={() => setOffset(Math.max(0, offset - PAGE_SIZE))}
                    className="rounded border px-3 py-1 disabled:opacity-40"
                  >
                    Précédent
                  </button>
                  <button
                    disabled={offset + PAGE_SIZE >= total}
                    onClick={() => setOffset(offset + PAGE_SIZE)}
                    className="rounded border px-3 py-1 disabled:opacity-40"
                  >
                    Suivant
                  </button>
                </div>
              </div>
            </>
          )}
        </div>
      </div>

      {editing && selected && (
        <div className="fixed inset-0 z-50 flex items-center justify-center bg-black/50 p-4">
          <section className="max-h-[92vh] w-full max-w-4xl overflow-y-auto rounded-lg bg-white p-5 shadow-xl">
            <div className="mb-4 flex items-center justify-between">
              <h3 className="text-xl font-bold">
                {creating ? "Ajouter" : "Modifier"} — {selected.name}
              </h3>
              <button onClick={() => setEditing(null)} className="text-2xl">
                ×
              </button>
            </div>
            <div className="grid gap-3 md:grid-cols-2">
              {selected.columns
                .filter((column) => column.editable)
                .map((column) => (
                  <label
                    key={column.name}
                    className={isLongText(column) ? "md:col-span-2" : ""}
                  >
                    <span className="mb-1 block text-sm font-semibold">
                      {column.name}
                      {column.nullable ? "" : " *"}
                    </span>
                    {isBoolean(column) ? (
                      <input
                        type="checkbox"
                        checked={Boolean(form[column.name])}
                        onChange={(event) =>
                          setForm((current) => ({
                            ...current,
                            [column.name]: event.target.checked,
                          }))
                        }
                      />
                    ) : isLongText(column) ? (
                      <textarea
                        rows={4}
                        value={valueForInput(form[column.name], column)}
                        onChange={(event) =>
                          setForm((current) => ({
                            ...current,
                            [column.name]: event.target.value,
                          }))
                        }
                        className="w-full rounded border px-3 py-2"
                      />
                    ) : (
                      <input
                        type={
                          column.dataType === "date"
                            ? "date"
                            : ["datetime", "timestamp"].includes(
                                  column.dataType,
                                )
                              ? "datetime-local"
                              : [
                                    "tinyint",
                                    "smallint",
                                    "mediumint",
                                    "int",
                                    "bigint",
                                    "decimal",
                                    "float",
                                    "double",
                                  ].includes(column.dataType)
                                ? "number"
                                : "text"
                        }
                        value={valueForInput(form[column.name], column)}
                        onChange={(event) =>
                          setForm((current) => ({
                            ...current,
                            [column.name]: event.target.value,
                          }))
                        }
                        className="w-full rounded border px-3 py-2"
                      />
                    )}
                  </label>
                ))}
            </div>
            <div className="mt-5 flex justify-end gap-3">
              <button
                onClick={() => setEditing(null)}
                className="rounded border px-4 py-2"
              >
                Annuler
              </button>
              <button
                disabled={saving}
                onClick={() => void save()}
                className="rounded bg-green-600 px-4 py-2 font-semibold text-white disabled:opacity-50"
              >
                {saving ? "Enregistrement…" : "Enregistrer"}
              </button>
            </div>
          </section>
        </div>
      )}
    </section>
  );
}