const DEFAULT_DB_NAME = process.env.NEXT_PUBLIC_ATHENA_DB_NAME ?? "postgres";
const DEFAULT_CLIENT = process.env.NEXT_PUBLIC_ATHENA_CLIENT ?? "supabase";
const LOGGING_CLIENT = "athena_logging";

export type TableMetadata = {
  table_name: string;
  table_schema?: string;
};

export type ColumnMetadata = {
  column_name: string;
  data_type?: string | null;
  column_default?: string | null;
  is_nullable?: string;
};

export type RowValues = Record<string, unknown>;

export type MigrationMetadata = {
  version?: string | null;
  name?: string | null;
};

export type MirrorStatus = {
  url: string;
  status: string;
  latency_ms: number;
  download_bytes_per_sec: number;
  cargo_toml_version: string;
  message: string;
};

export type ClusterHealth = {
  message: string;
  version: string;
  athena_api: string;
  athena_deadpool: string;
  athena_scylladb: string;
  cargo_toml_version: string;
  mirrors: MirrorStatus[];
};

export type SavedQuery = {
  id?: string;
  name: string;
  description?: string;
  query: string;
  client_name?: string;
  schema_name?: string;
  tags?: string[];
  created_by?: string;
  created_at?: string;
  updated_at?: string;
};

export type QueryHistoryEntry = {
  id?: string;
  query: string;
  client_name?: string;
  status: string;
  execution_time_ms?: number;
  error_message?: string;
  row_count?: number;
  executed_at?: string;
};

export type RequestLogEntry = {
  id?: string;
  method: string;
  path: string;
  athena_url: string;
  client_name?: string;
  status_code?: number;
  duration_ms?: number;
  error_message?: string;
  request_body?: unknown;
  response_preview?: string;
  created_at?: string;
};

export type AuditLogEvent = {
  created_at: string;
  event_type: string;
  title: string;
  summary?: string | null;
  status?: string | null;
  client_name?: string | null;
  metadata?: Record<string, unknown> | null;
};

export type FeedbackSubmission = {
  category: "issue" | "idea";
  title?: string;
  message: string;
  page?: string;
  email?: string;
  organization_name?: string;
  status?: string;
  metadata?: Record<string, unknown>;
};

export type OrganizationRequestSubmission = {
  organization_name: string;
  organization_type?: string;
  plan?: string;
  requested_by?: string;
  notes?: string;
  status?: string;
  metadata?: Record<string, unknown>;
};

export type ProjectRequestSubmission = {
  organization_name?: string;
  project_name: string;
  region?: string;
  enable_data_api?: boolean;
  enable_automatic_rls?: boolean;
  requested_by?: string;
  status?: string;
  metadata?: Record<string, unknown>;
};

export type DbFunction = {
  function_name: string;
  schema_name: string;
  return_type: string;
  argument_types: string;
  function_type: string;
};

export type DbIndex = {
  index_name: string;
  table_name: string;
  schema_name: string;
  is_unique: boolean;
  index_definition: string;
  columns: string[];
};

export type DbExtension = {
  name: string;
  version: string;
  schema: string;
  description: string;
  installed: boolean;
};

export type DbPublication = {
  name: string;
  owner: string;
  all_tables: boolean;
  tables: string[];
};

export type DbTrigger = {
  trigger_name: string;
  event: string;
  table_name: string;
  schema_name: string;
  timing: string;
  definition: string;
  enabled: boolean;
};

export type TableStats = {
  total_tables: number;
  total_columns: number;
  total_size: string;
  schemas: { name: string; table_count: number }[];
};

export class ApiError extends Error {
  public status: number;
  public code: string;
  public retryable: boolean;

  constructor(message: string, status: number, code?: string) {
    super(message);
    this.name = "ApiError";
    this.status = status;
    this.code = code ?? "UNKNOWN";
    this.retryable =
      status >= 500 || status === 0 || status === 408 || status === 429;
  }
}

type ApiOptions = {
  driver?: "athena" | "postgresql" | "supabase";
  dbName?: string;
  clientName?: string;
};

type RequestContext = {
  baseUrl: string;
  apiKey?: string;
  localBackupApiKey?: string;
  pgDumpPath?: string;
  pgRestorePath?: string;
};

const DESKTOP_LOCAL_BACKUP_PROXY_BASE_URL = "http://127.0.0.1:5052";

function isDesktopRuntimeClient(): boolean {
  if (typeof window === "undefined") return false;
  return "__TAURI_INTERNALS__" in window || "__TAURI__" in window;
}

function backupProxyBaseUrl(ctx: RequestContext): string {
  if (isDesktopRuntimeClient()) {
    return DESKTOP_LOCAL_BACKUP_PROXY_BASE_URL;
  }
  return ctx.baseUrl;
}

function backupProxyApiKey(ctx: RequestContext): string | undefined {
  if (isDesktopRuntimeClient() && ctx.localBackupApiKey) {
    return ctx.localBackupApiKey;
  }
  return ctx.apiKey;
}

// ── IndexedDB column cache (per client+table+version) ──────────────────────────

type ColumnCacheEntry = {
  version: number;
  columns: ColumnMetadata[];
  storedAt: number;
};

const COLUMN_CACHE_DB = "athena-cache";
const COLUMN_CACHE_STORE = "columns";

function openColumnDb(): Promise<IDBDatabase> {
  return new Promise((resolve, reject) => {
    const req = indexedDB.open(COLUMN_CACHE_DB, 1);
    req.onerror = () => reject(req.error);
    req.onupgradeneeded = () => {
      const db = req.result;
      if (!db.objectStoreNames.contains(COLUMN_CACHE_STORE)) {
        db.createObjectStore(COLUMN_CACHE_STORE);
      }
    };
    req.onsuccess = () => resolve(req.result);
  });
}

async function getCachedColumns(key: string): Promise<ColumnCacheEntry | null> {
  try {
    const db = await openColumnDb();
    return await new Promise((resolve, reject) => {
      const tx = db.transaction(COLUMN_CACHE_STORE, "readonly");
      const store = tx.objectStore(COLUMN_CACHE_STORE);
      const req = store.get(key);
      req.onerror = () => reject(req.error);
      req.onsuccess = () =>
        resolve((req.result as ColumnCacheEntry | undefined) ?? null);
    });
  } catch {
    return null;
  }
}

async function setCachedColumns(
  key: string,
  entry: ColumnCacheEntry,
): Promise<void> {
  try {
    const db = await openColumnDb();
    await new Promise((resolve, reject) => {
      const tx = db.transaction(COLUMN_CACHE_STORE, "readwrite");
      const store = tx.objectStore(COLUMN_CACHE_STORE);
      const req = store.put(entry, key);
      req.onerror = () => reject(req.error);
      req.onsuccess = () => resolve(null);
    });
  } catch {
    // ignore cache failures
  }
}

// ───────────────────────────────────────────────────────────────────────────────

function buildHeaders(
  ctx: RequestContext,
  clientName?: string,
  extra?: Record<string, string>,
  apiKeyOverride?: string,
): Record<string, string> {
  const isLocalDesktopBaseUrl =
    /:\/\/(127\.0\.0\.1|localhost):(5052|4052)\b/i.test(ctx.baseUrl);
  const resolvedApiKey =
    apiKeyOverride ??
    (isLocalDesktopBaseUrl
      ? (ctx.localBackupApiKey ?? ctx.apiKey)
      : (ctx.apiKey ?? ctx.localBackupApiKey));
  const headers: Record<string, string> = {
    "Content-Type": "application/json",
    "X-Athena-Client": clientName ?? DEFAULT_CLIENT,
  };
  if (resolvedApiKey) {
    headers["apikey"] = resolvedApiKey;
    headers["X-Athena-Key"] = resolvedApiKey;
    headers["X-API-Key"] = resolvedApiKey;
    headers["Authorization"] = `Bearer ${resolvedApiKey}`;
  }
  if (ctx.pgDumpPath) {
    headers["X-Athena-Pg-Dump-Path"] = ctx.pgDumpPath;
  }
  if (ctx.pgRestorePath) {
    headers["X-Athena-Pg-Restore-Path"] = ctx.pgRestorePath;
  }
  if (extra) {
    for (const [k, v] of Object.entries(extra)) {
      if (v && v.length > 0) headers[k] = v;
    }
  }
  return headers;
}

async function executeApiRequest<T>(
  url: string,
  init: RequestInit,
  errorMessage: string,
  signal?: AbortSignal,
): Promise<T> {
  let response: Response;
  try {
    const timeout = AbortSignal.timeout(15000);
    const combined =
      signal && typeof AbortSignal.any === "function"
        ? AbortSignal.any([signal, timeout])
        : (signal ?? timeout);

    response = await fetch(url, { ...init, signal: combined });
  } catch (err) {
    if (err instanceof DOMException && err.name === "TimeoutError") {
      throw new ApiError(
        "Request timed out — the server may be unreachable",
        0,
        "TIMEOUT",
      );
    }
    if (err instanceof DOMException && err.name === "AbortError") {
      throw new ApiError("Request canceled", 0, "ABORTED");
    }
    if (err instanceof TypeError) {
      throw new ApiError(
        "Network error — check your connection and base URL",
        0,
        "NETWORK_ERROR",
      );
    }
    throw new ApiError(
      err instanceof Error ? err.message : "Unknown request error",
      0,
      "FETCH_ERROR",
    );
  }

  const body = await response.json().catch(() => null);

  if (!response.ok) {
    const serverMsg =
      (body && typeof body === "object" && "error" in body
        ? (body as Record<string, unknown>).error
        : null) ??
      (body && typeof body === "object" && "message" in body
        ? (body as Record<string, unknown>).message
        : null);

    const msg = serverMsg ? String(serverMsg) : errorMessage;
    throw new ApiError(msg, response.status, `HTTP_${response.status}`);
  }

  return body;
}

export async function runSql<T = unknown>(
  ctx: RequestContext,
  query: string,
  options?: ApiOptions,
): Promise<T> {
  const payload = {
    query,
    driver: options?.driver ?? "postgresql",
    db_name: options?.dbName ?? DEFAULT_DB_NAME,
  };

  const result = await executeApiRequest<unknown>(
    `${ctx.baseUrl}/query/sql`,
    {
      method: "POST",
      headers: buildHeaders(ctx, options?.clientName),
      body: JSON.stringify(payload),
    },
    "Failed to execute SQL",
  );

  if (Array.isArray(result)) return result as T;

  if (result && typeof result === "object") {
    if ("data" in result) {
      const data = (result as Record<string, unknown>).data;
      if (Array.isArray(data)) return data as T;
      if (data && typeof data === "object" && "rows" in (data as object)) {
        return (data as Record<string, unknown>).rows as T;
      }
      return data as T;
    }
  }

  return result as T;
}

export async function fetchClients(ctx: RequestContext): Promise<string[]> {
  const url = `${ctx.baseUrl}/schema/clients`;

  const response = await executeApiRequest<{ clients?: string[] }>(
    url,
    { method: "GET", headers: buildHeaders(ctx) },
    "Failed to fetch clients",
  );

  if (response && typeof response === "object" && "clients" in response) {
    return Array.isArray(response.clients) ? response.clients : [];
  }
  return [];
}

export async function fetchTables(
  ctx: RequestContext,
  clientName?: string,
): Promise<TableMetadata[]> {
  const resolvedClient = clientName ?? DEFAULT_CLIENT;
  const response = await executeApiRequest<{ tables?: TableMetadata[] }>(
    `${ctx.baseUrl}/schema/tables`,
    { method: "GET", headers: buildHeaders(ctx, resolvedClient) },
    `Failed to fetch tables for ${resolvedClient}`,
  );

  if (response && typeof response === "object" && "tables" in response) {
    return Array.isArray(response.tables) ? response.tables : [];
  }
  return [];
}

export async function fetchMigrations(
  ctx: RequestContext,
  clientName?: string,
): Promise<{ migrations: MigrationMetadata[]; message?: string }> {
  const resolvedClient = clientName ?? DEFAULT_CLIENT;
  const response = await executeApiRequest<{
    migrations?: MigrationMetadata[];
    message?: string;
  }>(
    `${ctx.baseUrl}/schema/migrations`,
    { method: "GET", headers: buildHeaders(ctx, resolvedClient) },
    `Failed to fetch migrations for ${resolvedClient}`,
  );

  if (response && typeof response === "object" && "migrations" in response) {
    return {
      migrations: Array.isArray(response.migrations) ? response.migrations : [],
      message: response.message,
    };
  }
  return { migrations: [] };
}

export async function fetchColumns(
  ctx: RequestContext,
  tableName: string,
  clientName?: string,
  signal?: AbortSignal,
  cacheVersion = 0,
  useCache = true,
  schemaName?: string,
  tableSchema?: string,
): Promise<ColumnMetadata[]> {
  const resolvedClient = clientName ?? DEFAULT_CLIENT;
  const cacheKey = `${resolvedClient}:${tableSchema ?? "public"}:${tableName}`;

  if (useCache) {
    const cached = await getCachedColumns(cacheKey);
    if (cached && cached.version === cacheVersion) {
      return cached.columns;
    }
  }

  const params = new URLSearchParams({ table_name: tableName });
  if (schemaName?.trim()) params.set("table_schema", schemaName.trim());
  const response = await executeApiRequest<{ columns?: ColumnMetadata[] }>(
    `${ctx.baseUrl}/schema/columns?${params.toString()}`,
    { method: "GET", headers: buildHeaders(ctx, resolvedClient) },
    `Failed to fetch columns for ${tableName}`,
    signal,
  );

  if (response && typeof response === "object" && "columns" in response) {
    const cols = Array.isArray(response.columns) ? response.columns : [];
    if (useCache) {
      void setCachedColumns(cacheKey, {
        version: cacheVersion,
        columns: cols,
        storedAt: Date.now(),
      });
    }
    return cols;
  }
  return [];
}

export async function fetchRows(
  ctx: RequestContext,
  tableName: string,
  columns: string[] = ["*"],
  clientName?: string,
  signal?: AbortSignal,
  noCache?: boolean,
): Promise<RowValues[]> {
  const resolvedClient = clientName ?? DEFAULT_CLIENT;
  const extra: Record<string, string> = {};
  if (noCache) extra["Cache-Control"] = "no-cache";
  const response = await executeApiRequest<{
    data?: RowValues[];
    rows?: RowValues[];
  }>(
    `${ctx.baseUrl}/gateway/fetch`,
    {
      method: "POST",
      headers: buildHeaders(ctx, resolvedClient, extra),
      body: JSON.stringify({ table_name: tableName, columns }),
    },
    `Failed to fetch rows for ${tableName}`,
    signal,
  );

  const payload = response.data ?? response.rows;
  return Array.isArray(payload) ? payload : [];
}

export async function fetchAuditLogEvents(
  ctx: RequestContext,
  clientName?: string,
  limit = 150,
): Promise<AuditLogEvent[]> {
  const resolvedClient = clientName ?? DEFAULT_CLIENT;
  const safeLimit = Number.isFinite(limit)
    ? Math.max(1, Math.min(500, Math.floor(limit)))
    : 150;

  const events: AuditLogEvent[] = [];

  try {
    const uiRows = await runSql<AuditLogEvent[]>(
      ctx,
      `SELECT
         created_at,
         'ui_request'::text as event_type,
         CONCAT(COALESCE(method, 'REQUEST'), ' ', COALESCE(path, '')) as title,
         COALESCE(error_message, response_preview, '') as summary,
         COALESCE(status_code::text, '') as status,
         client_name,
         jsonb_build_object('path', path, 'method', method, 'status_code', status_code, 'duration_ms', duration_ms) as metadata
       FROM public.ui_request_log
       WHERE client_name = '${resolvedClient.replace(/'/g, "''")}'
       ORDER BY created_at DESC
       LIMIT ${safeLimit};`,
      { clientName: resolvedClient },
    );
    events.push(...uiRows);
  } catch {
    // table may not exist on older clients
  }

  try {
    const feedbackRows = await runSql<AuditLogEvent[]>(
      ctx,
      `SELECT
         created_at,
         'feedback'::text as event_type,
         COALESCE(NULLIF(title, ''), CONCAT('Feedback: ', category)) as title,
         message as summary,
         status,
         client_name,
         metadata
       FROM public.feedback
       ORDER BY created_at DESC
       LIMIT ${safeLimit};`,
      { clientName: resolvedClient },
    );
    events.push(...feedbackRows);
  } catch {
    // table may not exist on older clients
  }

  try {
    const orgRows = await runSql<AuditLogEvent[]>(
      ctx,
      `SELECT
         created_at,
         'organization_request'::text as event_type,
         CONCAT('Organization: ', organization_name) as title,
         COALESCE(notes, CONCAT('Type ', COALESCE(organization_type, '-'), ' · Plan ', COALESCE(plan, '-'))) as summary,
         status,
         client_name,
         metadata
       FROM public.organization_requests
       ORDER BY created_at DESC
       LIMIT ${safeLimit};`,
      { clientName: resolvedClient },
    );
    events.push(...orgRows);
  } catch {
    // table may not exist on older clients
  }

  try {
    const projectRows = await runSql<AuditLogEvent[]>(
      ctx,
      `SELECT
         created_at,
         'project_request'::text as event_type,
         CONCAT('Project: ', project_name) as title,
         CONCAT('Region ', COALESCE(region, '-'), ' · Data API ', enable_data_api::text, ' · RLS ', enable_automatic_rls::text) as summary,
         status,
         client_name,
         metadata
       FROM public.project_requests
       ORDER BY created_at DESC
       LIMIT ${safeLimit};`,
      { clientName: resolvedClient },
    );
    events.push(...projectRows);
  } catch {
    // table may not exist on older clients
  }

  return events
    .sort(
      (a, b) =>
        new Date(b.created_at).getTime() - new Date(a.created_at).getTime(),
    )
    .slice(0, safeLimit);
}

function getAuthHeaders(): Record<string, string> {
  return {
    "X-User-Id": process.env.NEXT_PUBLIC_ATHENA_USER_ID ?? "",
    "X-Company-Id": process.env.NEXT_PUBLIC_ATHENA_COMPANY_ID ?? "",
    "X-Organization-Id": process.env.NEXT_PUBLIC_ATHENA_ORGANIZATION_ID ?? "",
  };
}

export async function insertRow(
  ctx: RequestContext,
  tableName: string,
  insertBody: RowValues,
  clientName?: string,
): Promise<RowValues> {
  const resolvedClient = clientName ?? DEFAULT_CLIENT;
  const response = await executeApiRequest<{ data?: RowValues }>(
    `${ctx.baseUrl}/gateway/insert`,
    {
      method: "PUT",
      headers: buildHeaders(ctx, resolvedClient, getAuthHeaders()),
      body: JSON.stringify({ table_name: tableName, insert_body: insertBody }),
    },
    `Failed to insert row into ${tableName}`,
  );

  if (response && typeof response === "object" && "data" in response) {
    return response.data ?? insertBody;
  }
  return insertBody;
}

export async function submitFeedback(
  ctx: RequestContext,
  payload: FeedbackSubmission,
): Promise<void> {
  const body: RowValues = {
    category: payload.category,
    title: payload.title ?? null,
    message: payload.message,
    page: payload.page ?? null,
    email: payload.email ?? null,
    organization_name: payload.organization_name ?? null,
    status: payload.status ?? "new",
    metadata: payload.metadata ?? {},
  };
  await insertRow(ctx, "feedback", body, LOGGING_CLIENT);
}

export async function submitOrganizationRequest(
  ctx: RequestContext,
  payload: OrganizationRequestSubmission,
): Promise<void> {
  const body: RowValues = {
    organization_name: payload.organization_name,
    organization_type: payload.organization_type ?? null,
    plan: payload.plan ?? null,
    requested_by: payload.requested_by ?? null,
    notes: payload.notes ?? null,
    status: payload.status ?? "new",
    metadata: payload.metadata ?? {},
  };
  await insertRow(ctx, "organization_requests", body, LOGGING_CLIENT);
}

export async function submitProjectRequest(
  ctx: RequestContext,
  payload: ProjectRequestSubmission,
): Promise<void> {
  const body: RowValues = {
    organization_name: payload.organization_name ?? null,
    project_name: payload.project_name,
    region: payload.region ?? null,
    enable_data_api: payload.enable_data_api ?? true,
    enable_automatic_rls: payload.enable_automatic_rls ?? false,
    requested_by: payload.requested_by ?? null,
    status: payload.status ?? "new",
    metadata: payload.metadata ?? {},
  };
  await insertRow(ctx, "project_requests", body, LOGGING_CLIENT);
}

export async function updateRow(
  ctx: RequestContext,
  tableName: string,
  conditions: { eq_column: string; eq_value: string }[],
  setValues: Record<string, unknown>,
  clientName?: string,
): Promise<void> {
  const resolvedClient = clientName ?? DEFAULT_CLIENT;
  await executeApiRequest(
    `${ctx.baseUrl}/gateway/update`,
    {
      method: "POST",
      headers: buildHeaders(ctx, resolvedClient, getAuthHeaders()),
      body: JSON.stringify({
        table_name: tableName,
        conditions,
        set: setValues,
      }),
    },
    `Failed to update row in ${tableName}`,
  );
}

export async function deleteRow(
  ctx: RequestContext,
  tableName: string,
  resourceId: string,
  clientName?: string,
): Promise<void> {
  const resolvedClient = clientName ?? DEFAULT_CLIENT;
  await executeApiRequest(
    `${ctx.baseUrl}/gateway/delete`,
    {
      method: "DELETE",
      headers: buildHeaders(ctx, resolvedClient, getAuthHeaders()),
      body: JSON.stringify({ table_name: tableName, resource_id: resourceId }),
    },
    `Failed to delete row from ${tableName}`,
  );
}

export async function runDdl(
  ctx: RequestContext,
  statement: string,
  clientName?: string,
): Promise<void> {
  await runSql(ctx, statement, { driver: "postgresql", clientName });
}

export async function fetchClusterHealth(
  ctx: RequestContext,
): Promise<ClusterHealth> {
  return executeApiRequest<ClusterHealth>(
    `${ctx.baseUrl}/health/cluster`,
    { method: "GET", headers: buildHeaders(ctx) },
    "Failed to fetch cluster health",
  );
}

export async function fetchFunctions(
  ctx: RequestContext,
  clientName?: string,
  schemaName?: string,
): Promise<DbFunction[]> {
  const resolvedClient = clientName ?? DEFAULT_CLIENT;
  const schema = schemaName ?? "public";
  const query = `SELECT routine_name as function_name, routine_schema as schema_name, data_type as return_type, external_language as function_type, COALESCE((SELECT string_agg(parameter_name || ' ' || data_type, ', ' ORDER BY ordinal_position) FROM information_schema.parameters p WHERE p.specific_name = r.specific_name AND p.specific_schema = r.specific_schema), '') as argument_types FROM information_schema.routines r WHERE routine_schema = '${schema}' AND routine_type = 'FUNCTION' ORDER BY routine_name;`;
  try {
    return await runSql<DbFunction[]>(ctx, query, {
      clientName: resolvedClient,
    });
  } catch {
    return [];
  }
}

export async function fetchTriggers(
  ctx: RequestContext,
  clientName?: string,
  schemaName?: string,
): Promise<DbTrigger[]> {
  const resolvedClient = clientName ?? DEFAULT_CLIENT;
  const schema = schemaName ?? "public";
  const query = `SELECT trigger_name, event_manipulation as event, event_object_table as table_name, trigger_schema as schema_name, action_timing as timing, action_statement as definition, true as enabled FROM information_schema.triggers WHERE trigger_schema = '${schema}' ORDER BY trigger_name;`;
  try {
    return await runSql<DbTrigger[]>(ctx, query, {
      clientName: resolvedClient,
    });
  } catch {
    return [];
  }
}

export async function fetchExtensions(
  ctx: RequestContext,
  clientName?: string,
): Promise<DbExtension[]> {
  const resolvedClient = clientName ?? DEFAULT_CLIENT;
  const query = `SELECT e.extname as name, e.extversion as version, n.nspname as schema, COALESCE(c.description, '') as description, true as installed FROM pg_extension e JOIN pg_namespace n ON e.extnamespace = n.oid LEFT JOIN pg_description c ON c.objoid = e.oid ORDER BY e.extname;`;
  try {
    return await runSql<DbExtension[]>(ctx, query, {
      clientName: resolvedClient,
    });
  } catch {
    return [];
  }
}

export async function fetchPublications(
  ctx: RequestContext,
  clientName?: string,
): Promise<DbPublication[]> {
  const resolvedClient = clientName ?? DEFAULT_CLIENT;
  const query = `SELECT pubname as name, pg_get_userbyid(pubowner) as owner, puballtables as all_tables, COALESCE((SELECT array_agg(schemaname || '.' || tablename) FROM pg_publication_tables pt WHERE pt.pubname = p.pubname), '{}') as tables FROM pg_publication p ORDER BY pubname;`;
  try {
    return await runSql<DbPublication[]>(ctx, query, {
      clientName: resolvedClient,
    });
  } catch {
    return [];
  }
}

export async function fetchIndexes(
  ctx: RequestContext,
  clientName?: string,
  tableName?: string,
  schemaName?: string,
): Promise<DbIndex[]> {
  const resolvedClient = clientName ?? DEFAULT_CLIENT;
  const schema = schemaName ?? "public";
  let query = `SELECT i.relname as index_name, t.relname as table_name, n.nspname as schema_name, ix.indisunique as is_unique, pg_get_indexdef(i.oid) as index_definition, array_agg(a.attname ORDER BY array_position(ix.indkey, a.attnum)) as columns FROM pg_index ix JOIN pg_class i ON i.oid = ix.indexrelid JOIN pg_class t ON t.oid = ix.indrelid JOIN pg_namespace n ON n.oid = t.relnamespace JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum = ANY(ix.indkey) WHERE n.nspname = '${schema}'`;
  if (tableName) query += ` AND t.relname = '${tableName}'`;
  query += ` GROUP BY i.relname, t.relname, n.nspname, ix.indisunique, i.oid ORDER BY t.relname, i.relname;`;
  try {
    return await runSql<DbIndex[]>(ctx, query, { clientName: resolvedClient });
  } catch {
    return [];
  }
}

export async function fetchTableStats(
  ctx: RequestContext,
  clientName?: string,
): Promise<TableStats> {
  const resolvedClient = clientName ?? DEFAULT_CLIENT;
  try {
    const sizeQuery = `SELECT pg_size_pretty(pg_database_size(current_database())) as total_size;`;
    const sizeResult = await runSql<{ total_size: string }[]>(ctx, sizeQuery, {
      clientName: resolvedClient,
    });
    const totalSize = sizeResult?.[0]?.total_size ?? "N/A";

    const colQuery = `SELECT COUNT(*) as count FROM information_schema.columns WHERE table_schema NOT IN ('pg_catalog', 'information_schema');`;
    const colResult = await runSql<{ count: string }[]>(ctx, colQuery, {
      clientName: resolvedClient,
    });
    const totalColumns = parseInt(colResult?.[0]?.count ?? "0", 10);

    const schemaQuery = `SELECT table_schema as name, COUNT(*) as table_count FROM information_schema.tables WHERE table_schema NOT IN ('pg_catalog', 'information_schema') AND table_type = 'BASE TABLE' GROUP BY table_schema ORDER BY table_schema;`;
    const schemas = await runSql<{ name: string; table_count: string }[]>(
      ctx,
      schemaQuery,
      { clientName: resolvedClient },
    );

    const totalTables = schemas.reduce(
      (sum, s) => sum + parseInt(s.table_count, 10),
      0,
    );

    return {
      total_tables: totalTables,
      total_columns: totalColumns,
      total_size: totalSize,
      schemas: schemas.map((s) => ({
        name: s.name,
        table_count: parseInt(s.table_count, 10),
      })),
    };
  } catch {
    return {
      total_tables: 0,
      total_columns: 0,
      total_size: "N/A",
      schemas: [],
    };
  }
}

export async function fetchSchemas(
  ctx: RequestContext,
  clientName?: string,
): Promise<string[]> {
  const resolvedClient = clientName ?? DEFAULT_CLIENT;
  try {
    const result = await runSql<{ schema_name: string }[]>(
      ctx,
      `SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN ('pg_catalog', 'information_schema', 'pg_toast') ORDER BY schema_name;`,
      { clientName: resolvedClient },
    );
    return result.map((r) => r.schema_name);
  } catch {
    return ["public"];
  }
}

export async function fetchTableDdl(
  ctx: RequestContext,
  tableName: string,
  schemaName: string,
  clientName?: string,
  existingColumns?: ColumnMetadata[],
): Promise<string> {
  if (existingColumns && existingColumns.length > 0) {
    const colDefs = existingColumns.map((c) => {
      let def = `  ${c.column_name} ${c.data_type ?? "text"}`;
      if (c.is_nullable === "NO") def += " NOT NULL";
      if (c.column_default) def += ` DEFAULT ${c.column_default}`;
      return def;
    });
    return `CREATE TABLE ${schemaName}.${tableName} (\n${colDefs.join(",\n")}\n);`;
  }

  const resolvedClient = clientName ?? DEFAULT_CLIENT;
  try {
    const result = await runSql<
      {
        column_name: string;
        data_type: string;
        is_nullable: string;
        column_default: string | null;
      }[]
    >(
      ctx,
      `SELECT column_name, data_type, is_nullable, column_default FROM information_schema.columns WHERE table_name = '${tableName}' AND table_schema = '${schemaName}' ORDER BY ordinal_position;`,
      { clientName: resolvedClient },
    );

    const cols = Array.isArray(result) ? result : [];

    if (cols.length === 0) {
      return `-- No columns found for ${schemaName}.${tableName}`;
    }

    const colDefs = cols.map((c) => {
      let def = `  ${c.column_name} ${c.data_type}`;
      if (c.is_nullable === "NO") def += " NOT NULL";
      if (c.column_default) def += ` DEFAULT ${c.column_default}`;
      return def;
    });

    return `CREATE TABLE ${schemaName}.${tableName} (\n${colDefs.join(",\n")}\n);`;
  } catch {
    return `-- Unable to generate DDL for ${schemaName}.${tableName}`;
  }
}

export async function fetchDbConfig(
  ctx: RequestContext,
  clientName?: string,
): Promise<{ name: string; setting: string; category: string }[]> {
  const resolvedClient = clientName ?? DEFAULT_CLIENT;
  try {
    return await runSql<{ name: string; setting: string; category: string }[]>(
      ctx,
      `SELECT name, setting, category FROM pg_settings WHERE context IN ('user', 'superuser') ORDER BY category, name LIMIT 200;`,
      { clientName: resolvedClient },
    );
  } catch {
    return [];
  }
}

// ── API Key Management ──

export type ApiKeyRecord = {
  id?: number;
  api_keys_id?: string;
  public_id?: string;
  name: string;
  description?: string | null;
  client_name?: string | null;
  active?: boolean;
  expires_at?: string | null;
  created_at?: string | null;
  updated_at?: string | null;
  rights?: string[];
};

export type ApiKeyCreateResponse = {
  status: string;
  message: string;
  data?: {
    api_keys_id: string;
    public_id: string;
    secret: string;
    name: string;
  };
};

export async function listApiKeys(
  ctx: RequestContext,
): Promise<ApiKeyRecord[]> {
  const resp = await executeApiRequest<{
    data?: { api_keys?: ApiKeyRecord[] } | ApiKeyRecord[];
  }>(
    `${ctx.baseUrl}/admin/api-keys`,
    { method: "GET", headers: buildHeaders(ctx) },
    "Failed to list API keys",
  );
  if (Array.isArray(resp?.data)) return resp.data;
  const inner =
    resp?.data && typeof resp.data === "object" && "api_keys" in resp.data
      ? (resp.data as { api_keys?: ApiKeyRecord[] }).api_keys
      : undefined;
  return Array.isArray(inner) ? inner : [];
}

export async function createApiKey(
  ctx: RequestContext,
  body: {
    name: string;
    description?: string;
    client_name?: string;
    rights?: string[];
    expires_at?: string;
  },
): Promise<ApiKeyCreateResponse> {
  return executeApiRequest<ApiKeyCreateResponse>(
    `${ctx.baseUrl}/admin/api-keys`,
    {
      method: "POST",
      headers: buildHeaders(ctx),
      body: JSON.stringify(body),
    },
    "Failed to create API key",
  );
}

export async function updateApiKey(
  ctx: RequestContext,
  id: string,
  body: {
    name?: string;
    description?: string;
    client_name?: string | null;
    active?: boolean;
    rights?: string[];
  },
): Promise<void> {
  await executeApiRequest(
    `${ctx.baseUrl}/admin/api-keys/${encodeURIComponent(id)}`,
    {
      method: "PATCH",
      headers: buildHeaders(ctx),
      body: JSON.stringify(body),
    },
    "Failed to update API key",
  );
}

export async function deleteApiKey(
  ctx: RequestContext,
  id: string,
): Promise<void> {
  await executeApiRequest(
    `${ctx.baseUrl}/admin/api-keys/${encodeURIComponent(id)}`,
    { method: "DELETE", headers: buildHeaders(ctx) },
    "Failed to delete API key",
  );
}

export async function listApiKeyRights(
  ctx: RequestContext,
): Promise<{ id: number; name: string; description?: string }[]> {
  const resp = await executeApiRequest<{
    data?: { id: number; name: string; description?: string }[];
  }>(
    `${ctx.baseUrl}/admin/api-key-rights`,
    { method: "GET", headers: buildHeaders(ctx) },
    "Failed to list API key rights",
  );
  return resp.data ?? [];
}

// ── Client Statistics ──

export type ClientStatistics = {
  client_name: string;
  total_requests: number;
  successful_requests: number;
  failed_requests: number;
  total_cached_requests: number;
  total_operations: number;
  last_request_at: string | null;
  last_operation_at: string | null;
  updated_at: string;
};

export type ClientTableStatistics = {
  client_name: string;
  table_name: string;
  operation: string;
  total_operations: number;
  error_operations: number;
  last_operation_at: string | null;
  updated_at: string;
};

export async function listClientStatistics(
  ctx: RequestContext,
): Promise<ClientStatistics[]> {
  const resp = await executeApiRequest<{
    data?: { statistics?: ClientStatistics[] };
  }>(
    `${ctx.baseUrl}/admin/clients/statistics`,
    { method: "GET", headers: buildHeaders(ctx) },
    "Failed to list client statistics",
  );
  return resp.data?.statistics ?? [];
}

export async function refreshClientStatistics(
  ctx: RequestContext,
): Promise<ClientStatistics[]> {
  const resp = await executeApiRequest<{
    data?: { statistics?: ClientStatistics[] };
  }>(
    `${ctx.baseUrl}/admin/clients/statistics/refresh`,
    { method: "POST", headers: buildHeaders(ctx) },
    "Failed to refresh client statistics",
  );
  return resp.data?.statistics ?? [];
}

export async function getClientStatistics(
  ctx: RequestContext,
  clientName: string,
): Promise<{ statistics: ClientStatistics; tables: ClientTableStatistics[] }> {
  const resp = await executeApiRequest<{
    data?: { statistics?: ClientStatistics; tables?: ClientTableStatistics[] };
  }>(
    `${ctx.baseUrl}/admin/clients/${encodeURIComponent(clientName)}/statistics`,
    { method: "GET", headers: buildHeaders(ctx) },
    `Failed to load statistics for ${clientName}`,
  );
  return {
    statistics: resp.data?.statistics ?? ({} as ClientStatistics),
    tables: resp.data?.tables ?? [],
  };
}

// ── Gateway logs (from gateway_request_log) ──

export async function fetchGatewayLogs(
  ctx: RequestContext,
  clientName?: string,
  limit: number = 100,
): Promise<RowValues[]> {
  const resolvedClient = clientName ?? DEFAULT_CLIENT;
  try {
    return await runSql<RowValues[]>(
      ctx,
      `SELECT * FROM athena_logging.gateway_request_log ORDER BY created_at DESC LIMIT ${limit};`,
      { clientName: resolvedClient },
    );
  } catch {
    try {
      return await runSql<RowValues[]>(
        ctx,
        `SELECT * FROM gateway_request_log ORDER BY created_at DESC LIMIT ${limit};`,
        { clientName: resolvedClient },
      );
    } catch {
      return [];
    }
  }
}

// ── Utilities ──

export async function pingServer(
  baseUrl: string,
): Promise<{ ok: boolean; latencyMs: number; version?: string }> {
  const start = performance.now();
  try {
    const resp = await fetch(`${baseUrl}/`, {
      signal: AbortSignal.timeout(8000),
    });
    const elapsed = performance.now() - start;
    if (!resp.ok) return { ok: false, latencyMs: elapsed };
    const data = await resp.json().catch(() => null);
    return {
      ok: true,
      latencyMs: elapsed,
      version: data?.version ?? data?.cargo_toml_version,
    };
  } catch {
    return { ok: false, latencyMs: performance.now() - start };
  }
}

// ── Backup & Restore ──

export type BackupRecord = {
  id: string;
  key: string;
  client_name: string;
  label?: string | null;
  size_bytes: number;
  created_at: string;
};

export async function listBackups(
  ctx: RequestContext,
  clientName?: string,
): Promise<BackupRecord[]> {
  const baseUrl = backupProxyBaseUrl(ctx);
  const apiKey = backupProxyApiKey(ctx);
  const url = clientName
    ? `${baseUrl}/admin/backups?client_name=${encodeURIComponent(clientName)}`
    : `${baseUrl}/admin/backups`;
  const resp = await executeApiRequest<{ data?: { backups?: BackupRecord[] } }>(
    url,
    { method: "GET", headers: buildHeaders(ctx, undefined, undefined, apiKey) },
    "Failed to list backups",
  );
  return resp.data?.backups ?? [];
}

export async function createBackup(
  ctx: RequestContext,
  body: { client_name: string; label?: string },
): Promise<{ key: string; client_name: string; label?: string | null }> {
  const baseUrl = backupProxyBaseUrl(ctx);
  const apiKey = backupProxyApiKey(ctx);
  const resp = await executeApiRequest<{
    data?: { key: string; client_name: string; label?: string | null };
  }>(
    `${baseUrl}/admin/backups`,
    {
      method: "POST",
      headers: buildHeaders(ctx, undefined, undefined, apiKey),
      body: JSON.stringify(body),
    },
    "Failed to create backup",
  );
  return resp.data ?? { key: "", client_name: body.client_name };
}

export async function restoreBackup(
  ctx: RequestContext,
  key: string,
  clientName: string,
): Promise<void> {
  const baseUrl = backupProxyBaseUrl(ctx);
  const apiKey = backupProxyApiKey(ctx);
  await executeApiRequest(
    `${baseUrl}/admin/backups/${encodeURIComponent(key)}/restore`,
    {
      method: "POST",
      headers: buildHeaders(ctx, undefined, undefined, apiKey),
      body: JSON.stringify({ client_name: clientName }),
    },
    "Failed to restore backup",
  );
}

export async function deleteBackup(
  ctx: RequestContext,
  key: string,
): Promise<void> {
  const baseUrl = backupProxyBaseUrl(ctx);
  const apiKey = backupProxyApiKey(ctx);
  await executeApiRequest(
    `${baseUrl}/admin/backups/${encodeURIComponent(key)}`,
    {
      method: "DELETE",
      headers: buildHeaders(ctx, undefined, undefined, apiKey),
    },
    "Failed to delete backup",
  );
}

export function backupDownloadUrl(ctx: RequestContext, key: string): string {
  const base = `${backupProxyBaseUrl(ctx)}/admin/backups/${encodeURIComponent(key)}/download`;
  const apiKey = backupProxyApiKey(ctx);
  return apiKey ? `${base}?api_key=${encodeURIComponent(apiKey)}` : base;
}

// ── Backup Jobs (polling) ──

export type BackupJob = {
  id: number;
  job_type: string;
  client_name?: string | null;
  status: string;
  progress_pct?: number | null;
  progress_stage?: string | null;
  s3_bucket?: string | null;
  s3_key?: string | null;
  label?: string | null;
  size_bytes?: number | null;
  error_message?: string | null;
  started_at: string;
  updated_at: string;
  completed_at?: string | null;
};

export type BackupJobLog = {
  id: number;
  job_id: number;
  level: string;
  message: string;
  created_at: string;
};

export async function listBackupJobs(
  ctx: RequestContext,
  params?: {
    client_name?: string;
    status?: string;
    job_type?: string;
    limit?: number;
  },
): Promise<BackupJob[]> {
  const baseUrl = backupProxyBaseUrl(ctx);
  const apiKey = backupProxyApiKey(ctx);
  const qs = new URLSearchParams();
  if (params?.client_name) qs.set("client_name", params.client_name);
  if (params?.status) qs.set("status", params.status);
  if (params?.job_type) qs.set("job_type", params.job_type);
  if (params?.limit) qs.set("limit", String(params.limit));
  const url = `${baseUrl}/admin/backups/jobs${qs.toString() ? `?${qs}` : ""}`;
  const resp = await executeApiRequest<{ data?: { jobs?: BackupJob[] } }>(
    url,
    { method: "GET", headers: buildHeaders(ctx, undefined, undefined, apiKey) },
    "Failed to list backup jobs",
  );
  return resp.data?.jobs ?? [];
}

export async function getBackupJob(
  ctx: RequestContext,
  jobId: number,
): Promise<{ job: BackupJob; logs: BackupJobLog[] }> {
  const baseUrl = backupProxyBaseUrl(ctx);
  const apiKey = backupProxyApiKey(ctx);
  const resp = await executeApiRequest<{
    data?: { job?: BackupJob; logs?: BackupJobLog[] };
  }>(
    `${baseUrl}/admin/backups/jobs/${jobId}`,
    { method: "GET", headers: buildHeaders(ctx, undefined, undefined, apiKey) },
    "Failed to get backup job",
  );
  return {
    job: resp.data?.job ?? ({} as BackupJob),
    logs: resp.data?.logs ?? [],
  };
}

// ── Backup Schedules ──

export type BackupSchedule = {
  id: number;
  client_name: string;
  pg_uri?: string | null;
  frequency: string;
  cron_expression: string;
  time_of_day: string;
  day_of_week?: number | null;
  day_of_month?: number | null;
  label?: string | null;
  enabled: boolean;
  timeout_seconds: number;
  last_run_at?: string | null;
  last_job_id?: number | null;
  next_run_at?: string | null;
  created_at: string;
  updated_at: string;
};

export async function listBackupSchedules(
  ctx: RequestContext,
): Promise<BackupSchedule[]> {
  const baseUrl = backupProxyBaseUrl(ctx);
  const apiKey = backupProxyApiKey(ctx);
  const resp = await executeApiRequest<{
    data?: { schedules?: BackupSchedule[] };
  }>(
    `${baseUrl}/admin/backups/schedules`,
    { method: "GET", headers: buildHeaders(ctx, undefined, undefined, apiKey) },
    "Failed to list backup schedules",
  );
  return resp.data?.schedules ?? [];
}

export async function createBackupSchedule(
  ctx: RequestContext,
  body: {
    client_name: string;
    pg_uri?: string;
    frequency: string;
    time: string;
    day_of_week?: number;
    day_of_month?: number;
    label?: string;
    timeout_seconds?: number;
  },
): Promise<BackupSchedule> {
  const baseUrl = backupProxyBaseUrl(ctx);
  const apiKey = backupProxyApiKey(ctx);
  const resp = await executeApiRequest<{
    data?: { schedule?: BackupSchedule };
  }>(
    `${baseUrl}/admin/backups/schedules`,
    {
      method: "POST",
      headers: buildHeaders(ctx, undefined, undefined, apiKey),
      body: JSON.stringify(body),
    },
    "Failed to create backup schedule",
  );
  return resp.data?.schedule ?? ({} as BackupSchedule);
}

export async function updateBackupSchedule(
  ctx: RequestContext,
  id: number,
  body: {
    enabled?: boolean;
    frequency?: string;
    time?: string;
    day_of_week?: number | null;
    day_of_month?: number | null;
    label?: string | null;
    timeout_seconds?: number;
  },
): Promise<BackupSchedule> {
  const baseUrl = backupProxyBaseUrl(ctx);
  const apiKey = backupProxyApiKey(ctx);
  const resp = await executeApiRequest<{
    data?: { schedule?: BackupSchedule };
  }>(
    `${baseUrl}/admin/backups/schedules/${id}`,
    {
      method: "PATCH",
      headers: buildHeaders(ctx, undefined, undefined, apiKey),
      body: JSON.stringify(body),
    },
    "Failed to update backup schedule",
  );
  return resp.data?.schedule ?? ({} as BackupSchedule);
}

export async function deleteBackupSchedule(
  ctx: RequestContext,
  id: number,
): Promise<void> {
  const baseUrl = backupProxyBaseUrl(ctx);
  const apiKey = backupProxyApiKey(ctx);
  await executeApiRequest(
    `${baseUrl}/admin/backups/schedules/${id}`,
    {
      method: "DELETE",
      headers: buildHeaders(ctx, undefined, undefined, apiKey),
    },
    "Failed to delete backup schedule",
  );
}

// ── Admin Client creation ──

export type AthenaClientRecord = {
  id?: number;
  client_name: string;
  description?: string | null;
  pg_uri?: string | null;
  pg_uri_env_var?: string | null;
  is_active?: boolean;
  is_frozen?: boolean;
  source?: string;
  created_at?: string;
  updated_at?: string;
};

export async function createAthenaClient(
  ctx: RequestContext,
  body: {
    client_name: string;
    pg_uri?: string;
    pg_uri_env_var?: string;
    description?: string;
  },
): Promise<AthenaClientRecord> {
  const resp = await executeApiRequest<{
    data?: { client?: AthenaClientRecord };
  }>(
    `${ctx.baseUrl}/admin/clients`,
    {
      method: "POST",
      headers: buildHeaders(ctx),
      body: JSON.stringify(body),
    },
    "Failed to create client",
  );
  return (
    resp.data?.client ??
    ({ client_name: body.client_name } as AthenaClientRecord)
  );
}

// ── Table row count ──

export async function fetchTableRowCount(
  ctx: RequestContext,
  tableName: string,
  schemaName?: string,
  clientName?: string,
): Promise<number> {
  const resolvedClient = clientName ?? DEFAULT_CLIENT;
  const schema = schemaName ?? "public";
  try {
    const result = await runSql<{ count: string }[]>(
      ctx,
      `SELECT COUNT(*) as count FROM ${schema}.${tableName}`,
      { clientName: resolvedClient },
    );
    return parseInt(result?.[0]?.count ?? "0", 10);
  } catch {
    return 0;
  }
}

// ── Pipelines ──

export type PipelineCondition = { eq_column: string; eq_value: string };

export type PipelineSource = {
  table_name: string;
  view_name?: string | null;
  columns?: string[];
  conditions?: PipelineCondition[];
  limit?: number;
};

export type PipelineTransform = {
  group_by?: string;
  time_granularity?: "day" | "hour" | "minute";
  aggregation_column?: string;
  aggregation_strategy?: string;
  aggregation_dedup?: boolean;
};

export type PipelineSink = {
  table_name: string;
};

export type PipelineRequest = {
  pipeline?: string;
  source?: PipelineSource;
  transform?: PipelineTransform;
  sink?: PipelineSink;
};

export type PipelineResult = {
  data?: unknown[];
  rows_fetched?: number;
  rows_inserted?: number;
  errors?: string[];
};

export async function runPipeline(
  ctx: RequestContext,
  body: PipelineRequest,
  clientName?: string,
): Promise<PipelineResult> {
  const resolvedClient = clientName ?? DEFAULT_CLIENT;
  const resp = await executeApiRequest<PipelineResult>(
    `${ctx.baseUrl}/pipelines`,
    {
      method: "POST",
      headers: buildHeaders(ctx, resolvedClient),
      body: JSON.stringify(body),
    },
    "Failed to run pipeline",
  );
  return resp;
}

// ── Management (DDL) ──

export type CreateTableRequest = {
  table_name: string;
  schema_name?: string;
  columns: {
    name: string;
    data_type: string;
    nullable?: boolean;
    default_value?: string;
  }[];
};

export type AlterTableRequest = {
  add_columns?: {
    name: string;
    data_type: string;
    nullable?: boolean;
    default_value?: string;
  }[];
  drop_columns?: string[];
  rename_columns?: { from: string; to: string }[];
  alter_columns?: {
    name: string;
    data_type?: string;
    nullable?: boolean;
    default_value?: string | null;
  }[];
};

export async function createTable(
  ctx: RequestContext,
  body: CreateTableRequest,
  clientName?: string,
): Promise<void> {
  const resolvedClient = clientName ?? DEFAULT_CLIENT;
  await executeApiRequest(
    `${ctx.baseUrl}/management/tables`,
    {
      method: "POST",
      headers: buildHeaders(ctx, resolvedClient),
      body: JSON.stringify(body),
    },
    "Failed to create table",
  );
}

export async function alterTable(
  ctx: RequestContext,
  tableName: string,
  body: AlterTableRequest,
  clientName?: string,
): Promise<void> {
  const resolvedClient = clientName ?? DEFAULT_CLIENT;
  await executeApiRequest(
    `${ctx.baseUrl}/management/tables/${encodeURIComponent(tableName)}`,
    {
      method: "PATCH",
      headers: buildHeaders(ctx, resolvedClient),
      body: JSON.stringify(body),
    },
    `Failed to alter table ${tableName}`,
  );
}

export async function dropTable(
  ctx: RequestContext,
  tableName: string,
  clientName?: string,
): Promise<void> {
  const resolvedClient = clientName ?? DEFAULT_CLIENT;
  await executeApiRequest(
    `${ctx.baseUrl}/management/tables/${encodeURIComponent(tableName)}`,
    {
      method: "DELETE",
      headers: buildHeaders(ctx, resolvedClient),
    },
    `Failed to drop table ${tableName}`,
  );
}

// ── S3 Storage ──

export type S3Creds = {
  endpoint: string;
  region: string;
  bucket: string;
  access_key_id: string;
  secret_key: string;
};

export type S3Object = {
  key: string;
  size: number;
  last_modified: string;
  is_folder: boolean;
  etag?: string;
  storage_class?: string;
};

export type S3ListResponse = {
  objects: S3Object[];
  common_prefixes: string[];
  is_truncated: boolean;
  next_continuation_token?: string;
  prefix?: string;
};

export async function listS3Objects(
  ctx: RequestContext,
  creds: S3Creds,
  prefix?: string,
  continuationToken?: string,
): Promise<S3ListResponse> {
  const resp = await executeApiRequest<{ data?: S3ListResponse }>(
    `${ctx.baseUrl}/storage/objects`,
    {
      method: "POST",
      headers: buildHeaders(ctx),
      body: JSON.stringify({
        ...creds,
        prefix: prefix ?? "",
        delimiter: "/",
        max_keys: 200,
        continuation_token: continuationToken,
      }),
    },
    "Failed to list S3 objects",
  );
  return resp.data ?? { objects: [], common_prefixes: [], is_truncated: false };
}

export async function getS3ObjectUrl(
  ctx: RequestContext,
  creds: S3Creds,
  key: string,
): Promise<string> {
  const resp = await executeApiRequest<{ data?: { url: string } }>(
    `${ctx.baseUrl}/storage/objects/url`,
    {
      method: "POST",
      headers: buildHeaders(ctx),
      body: JSON.stringify({ ...creds, key }),
    },
    "Failed to get object URL",
  );
  return resp.data?.url ?? "";
}

export async function deleteS3Object(
  ctx: RequestContext,
  creds: S3Creds,
  key: string,
): Promise<void> {
  await executeApiRequest(
    `${ctx.baseUrl}/storage/objects/delete`,
    {
      method: "POST",
      headers: buildHeaders(ctx),
      body: JSON.stringify({ ...creds, key }),
    },
    "Failed to delete object",
  );
}

export async function createS3Folder(
  ctx: RequestContext,
  creds: S3Creds,
  prefix: string,
): Promise<void> {
  await executeApiRequest(
    `${ctx.baseUrl}/storage/objects/folder`,
    {
      method: "POST",
      headers: buildHeaders(ctx),
      body: JSON.stringify({ ...creds, prefix }),
    },
    "Failed to create folder",
  );
}

export async function getS3UploadUrl(
  ctx: RequestContext,
  creds: S3Creds,
  key: string,
  contentType?: string,
): Promise<{ url: string; method: string }> {
  const resp = await executeApiRequest<{
    data?: { url: string; method: string };
  }>(
    `${ctx.baseUrl}/storage/objects/upload-url`,
    {
      method: "POST",
      headers: buildHeaders(ctx),
      body: JSON.stringify({ ...creds, key, content_type: contentType }),
    },
    "Failed to get upload URL",
  );
  return resp.data ?? { url: "", method: "PUT" };
}
