import { FormEvent, useCallback, useEffect, useMemo, useState } from "react";
import {
  ColumnMetadata,
  CreateTableRequest,
  EditTableRequest,
  RowValues,
  TableMetadata,
  createManagedIndex,
  createManagedTable,
  deleteRow,
  dropManagedIndex,
  dropManagedTable,
  editManagedTable,
  fetchColumns,
  fetchRows,
  fetchSchemaClients,
  fetchTables,
  insertRow,
  runSql,
  updateRows,
} from "@/app/lib/api";
import { RunAction } from "@/app/hooks/useActionFeedback";

const preferredClient = process.env.NEXT_PUBLIC_ATHENA_CLIENT ?? "supabase";
const defaultDriver = (process.env.NEXT_PUBLIC_ATHENA_SQL_DRIVER ??
  "supabase") as "athena" | "postgresql" | "supabase";
const defaultDbName = process.env.NEXT_PUBLIC_ATHENA_DB_NAME ?? "postgres";

function parseCellInput(raw: string): unknown {
  const value = raw.trim();
  if (!value.length) return "";
  if (value === "null") return null;
  if (value === "true") return true;
  if (value === "false") return false;
  if (!Number.isNaN(Number(value)) && value !== "") return Number(value);
  if (
    (value.startsWith("{") && value.endsWith("}")) ||
    (value.startsWith("[") && value.endsWith("]"))
  ) {
    try {
      return JSON.parse(value);
    } catch {
      return raw;
    }
  }
  return raw;
}

type UseDatabaseAdminProps = {
  adminApiKey: string;
  gatewayApiKey: string;
  managementApiKey: string;
  runAction: RunAction;
};

export function useDatabaseAdmin({
  adminApiKey,
  gatewayApiKey,
  managementApiKey,
  runAction,
}: UseDatabaseAdminProps) {
  const [schemaClients, setSchemaClients] = useState<string[]>([]);
  const [selectedClient, setSelectedClient] = useState<string>(preferredClient);
  const [tables, setTables] = useState<TableMetadata[]>([]);
  const [selectedTable, setSelectedTable] = useState<TableMetadata | null>(null);
  const [columns, setColumns] = useState<ColumnMetadata[]>([]);
  const [rows, setRows] = useState<RowValues[]>([]);
  const [rowEdits, setRowEdits] = useState<Record<string, Record<string, string>>>({});
  const [rowConditionColumn, setRowConditionColumn] = useState("resource_id");

  const [insertPayload, setInsertPayload] = useState("{\n  \n}");
  const [deleteResourceId, setDeleteResourceId] = useState("");

  const [createTableJson, setCreateTableJson] = useState(
    JSON.stringify(
      {
        schema_name: "public",
        table_name: "new_table",
        columns: [
          { name: "resource_id", data_type: "text", nullable: false },
          { name: "name", data_type: "text", nullable: true },
        ],
      } satisfies CreateTableRequest,
      null,
      2,
    ),
  );
  const [editTableJson, setEditTableJson] = useState(
    JSON.stringify(
      {
        schema_name: "public",
        operations: [
          {
            type: "add_column",
            column: { name: "notes", data_type: "text", nullable: true },
          },
        ],
      } satisfies EditTableRequest,
      null,
      2,
    ),
  );
  const [dropSchemaName, setDropSchemaName] = useState("public");
  const [dropCascade, setDropCascade] = useState(false);
  const [createIndexJson, setCreateIndexJson] = useState(
    JSON.stringify(
      {
        schema_name: "public",
        table_name: "new_table",
        index_name: "new_table_name_idx",
        columns: ["name"],
        unique: false,
        method: "btree",
      },
      null,
      2,
    ),
  );
  const [dropIndexName, setDropIndexName] = useState("");
  const [dropIndexSchema, setDropIndexSchema] = useState("public");

  const [sql, setSql] = useState("SELECT NOW() as now;");
  const [sqlDriver, setSqlDriver] = useState<"athena" | "postgresql" | "supabase">(defaultDriver);
  const [sqlDbName, setSqlDbName] = useState(defaultDbName);
  const [sqlResult, setSqlResult] = useState<string>("");

  const selectedTableName = selectedTable?.table_name ?? "";

  const visibleColumns = useMemo(() => {
    if (columns.length > 0) return columns.map((entry) => entry.column_name);
    if (rows.length > 0) return Object.keys(rows[0]);
    return [];
  }, [columns, rows]);

  const reloadSchemaClients = useCallback(async () => {
    const clients = await fetchSchemaClients(adminApiKey || undefined);
    setSchemaClients(clients);
    if (!clients.includes(selectedClient) && clients.length > 0) {
      setSelectedClient(clients[0]);
    }
  }, [adminApiKey, selectedClient]);

  const reloadTables = useCallback(async () => {
    if (!selectedClient) return;
    const data = await fetchTables(selectedClient, adminApiKey || undefined);
    setTables(data);
    if (!data.find((item) => item.table_name === selectedTableName)) {
      setSelectedTable(data[0] ?? null);
    }
  }, [adminApiKey, selectedClient, selectedTableName]);

  const reloadColumnsAndRows = useCallback(async () => {
    if (!selectedClient || !selectedTableName) {
      setColumns([]);
      setRows([]);
      return;
    }
    const [columnData, rowData] = await Promise.all([
      fetchColumns(selectedTableName, selectedClient, adminApiKey || undefined),
      fetchRows(selectedTableName, selectedClient, ["*"], gatewayApiKey || undefined),
    ]);
    setColumns(columnData);
    setRows(rowData);
    const columnNames = columnData.map((entry) => entry.column_name);
    if (columnNames.length > 0 && !columnNames.includes(rowConditionColumn)) {
      setRowConditionColumn(columnNames[0]);
    }
  }, [
    adminApiKey,
    gatewayApiKey,
    selectedClient,
    selectedTableName,
    rowConditionColumn,
  ]);

  useEffect(() => {
    runAction("Loaded schema clients", reloadSchemaClients);
  }, [runAction, reloadSchemaClients]);

  useEffect(() => {
    if (!selectedClient) return;
    runAction(`Loaded tables for ${selectedClient}`, reloadTables);
  }, [selectedClient, runAction, reloadTables]);

  useEffect(() => {
    runAction("Loaded table details", reloadColumnsAndRows);
  }, [selectedClient, selectedTableName, runAction, reloadColumnsAndRows]);

  const handleRefresh = () =>
    runAction("Refreshed clients and tables", async () => {
      await reloadSchemaClients();
      await reloadTables();
    });

  const handleInsert = async (event: FormEvent) => {
    event.preventDefault();
    if (!selectedTableName || !selectedClient) return;
    await runAction("Inserted row", async () => {
      const body = JSON.parse(insertPayload) as RowValues;
      await insertRow(selectedTableName, body, {
        clientName: selectedClient,
        gatewayApiKey: gatewayApiKey || undefined,
      });
      await reloadColumnsAndRows();
    });
  };

  const handleDelete = async (event: FormEvent) => {
    event.preventDefault();
    if (!selectedTableName || !selectedClient || !deleteResourceId.trim()) return;
    await runAction("Deleted row", async () => {
      await deleteRow(selectedTableName, deleteResourceId.trim(), {
        clientName: selectedClient,
        gatewayApiKey: gatewayApiKey || undefined,
      });
      setDeleteResourceId("");
      await reloadColumnsAndRows();
    });
  };

  const handleUpdateRow = async (rowIndex: number, row: RowValues) => {
    if (!selectedTableName || !selectedClient) return;
    const edits = rowEdits[String(rowIndex)];
    if (!edits || Object.keys(edits).length === 0) return;
    await runAction(`Updated row ${rowIndex + 1}`, async () => {
      await updateRows(
        selectedTableName,
        rowConditionColumn,
        row[rowConditionColumn],
        Object.fromEntries(
          Object.entries(edits).map(([key, value]) => [key, parseCellInput(value)]),
        ),
        {
          clientName: selectedClient,
          gatewayApiKey: gatewayApiKey || undefined,
        },
      );
      setRowEdits((prev) => {
        const next = { ...prev };
        delete next[String(rowIndex)];
        return next;
      });
      await reloadColumnsAndRows();
    });
  };

  const handleCreateTable = () =>
    runAction("Created table", async () => {
      const body = JSON.parse(createTableJson) as CreateTableRequest;
      await createManagedTable(body, {
        clientName: selectedClient,
        managementApiKey: managementApiKey || undefined,
      });
      await reloadTables();
    });

  const handleApplyTableEdit = () =>
    runAction("Updated table", async () => {
      if (!selectedTableName) throw new Error("Select a table first");
      const body = JSON.parse(editTableJson) as EditTableRequest;
      await editManagedTable(selectedTableName, body, {
        clientName: selectedClient,
        managementApiKey: managementApiKey || undefined,
      });
      await reloadColumnsAndRows();
    });

  const handleDropTable = () =>
    runAction("Dropped table", async () => {
      if (!selectedTableName) throw new Error("Select a table first");
      await dropManagedTable(selectedTableName, dropSchemaName, dropCascade, {
        clientName: selectedClient,
        managementApiKey: managementApiKey || undefined,
      });
      await reloadTables();
    });

  const handleCreateIndex = () =>
    runAction("Created index", async () => {
      const body = JSON.parse(createIndexJson) as {
        schema_name?: string;
        table_name: string;
        index_name?: string;
        columns: string[];
        unique?: boolean;
        method?: string;
      };
      await createManagedIndex(body, {
        clientName: selectedClient,
        managementApiKey: managementApiKey || undefined,
      });
    });

  const handleDropIndex = () =>
    runAction("Dropped index", async () => {
      if (!dropIndexName.trim()) throw new Error("index_name is required");
      await dropManagedIndex(dropIndexName.trim(), dropIndexSchema.trim() || "public", {
        clientName: selectedClient,
        managementApiKey: managementApiKey || undefined,
      });
    });

  const handleRunSql = () =>
    runAction("SQL executed", async () => {
      const result = await runSql(sql, {
        driver: sqlDriver,
        dbName: sqlDbName,
        clientName: selectedClient,
        gatewayApiKey: gatewayApiKey || undefined,
      });
      setSqlResult(JSON.stringify(result, null, 2));
      await reloadTables();
      await reloadColumnsAndRows();
    });

  return {
    schemaClients,
    selectedClient,
    setSelectedClient,
    tables,
    selectedTableName,
    setSelectedTable,
    visibleColumns,
    rowConditionColumn,
    setRowConditionColumn,
    rows,
    rowEdits,
    setRowEdits,
    handleUpdateRow,
    handleRefresh,
    handleInsert,
    insertPayload,
    setInsertPayload,
    handleDelete,
    deleteResourceId,
    setDeleteResourceId,
    createTableJson,
    setCreateTableJson,
    handleCreateTable,
    editTableJson,
    setEditTableJson,
    handleApplyTableEdit,
    dropSchemaName,
    setDropSchemaName,
    dropCascade,
    setDropCascade,
    handleDropTable,
    createIndexJson,
    setCreateIndexJson,
    handleCreateIndex,
    dropIndexName,
    setDropIndexName,
    dropIndexSchema,
    setDropIndexSchema,
    handleDropIndex,
    sql,
    setSql,
    sqlDriver,
    setSqlDriver,
    sqlDbName,
    setSqlDbName,
    handleRunSql,
    sqlResult,
  };
}
