// SQL for developer tokens incl. per-section rights, with fallbacks while migrations are missing
// (42703 = undefined column): 20261013000001 adds `scopes`, 20261012000002 adds `finance_access`.
import { query } from "@/lib/db/postgres";
import { effectiveScopes, type TokenScopes } from "@/lib/developers/scopes";

const BASE = "id, user_id, team_id, name, access_level, token_prefix, last_used_at, expires_at, revoked_at, created_at, updated_at";
const COLUMN_SETS = [`${BASE}, finance_access, scopes`, `${BASE}, finance_access`, BASE];

function isUndefinedColumn(error: unknown) {
  return typeof error === "object" && error !== null && (error as { code?: string }).code === "42703";
}

/** Adds `effective_scopes` (what the token can really do) and `scopes_supported` for the UI. */
function withScopes(row: Record<string, unknown>, columnSet: number) {
  return {
    ...row,
    effective_scopes: effectiveScopes({
      access_level: String(row.access_level),
      finance_access: (row.finance_access as string | null | undefined) ?? null,
      scopes: row.scopes ?? null,
    }),
    scopes_supported: columnSet === 0,
  };
}

export async function listTokens(where: string, values: unknown[]) {
  for (let i = 0; i < COLUMN_SETS.length; i++) {
    try {
      const { rows } = await query(
        `SELECT ${COLUMN_SETS[i]} FROM developer_api_tokens WHERE ${where} ORDER BY created_at DESC`,
        values as never[]
      );
      return rows.map((row) => withScopes(row, i));
    } catch (error) {
      if (!isUndefinedColumn(error) || i === COLUMN_SETS.length - 1) throw error;
    }
  }
  return [];
}

export async function insertToken(input: {
  userId: string;
  teamId: string | null;
  name: string;
  accessLevel: "read_only" | "read_write";
  scopes: TokenScopes | null;
  tokenPrefix: string;
  tokenHash: string;
  expiresAt: string | null;
}) {
  const base = [input.userId, input.teamId, input.name, input.accessLevel, input.tokenPrefix, input.tokenHash, input.expiresAt];
  if (input.scopes) {
    try {
      const { rows } = await query(
        `INSERT INTO developer_api_tokens (user_id, team_id, name, access_level, token_prefix, token_hash, expires_at, scopes)
         VALUES ($1, $2, $3, $4, $5, $6, $7, $8::jsonb) RETURNING ${COLUMN_SETS[0]}`,
        [...base, JSON.stringify(input.scopes)] as never[]
      );
      return rows[0] ? withScopes(rows[0], 0) : null;
    } catch (error) {
      if (!isUndefinedColumn(error)) throw error;
      throw Object.assign(new Error("Per-section rights are not available yet (database migration 20261013000001 missing)."), { status: 409 });
    }
  }
  const { rows } = await query(
    `INSERT INTO developer_api_tokens (user_id, team_id, name, access_level, token_prefix, token_hash, expires_at)
     VALUES ($1, $2, $3, $4, $5, $6, $7) RETURNING ${BASE}`,
    base as never[]
  );
  return rows[0] ? withScopes(rows[0], 2) : null;
}

export async function updateTokenScopes(id: string, userId: string, scopes: TokenScopes, accessLevel: "read_only" | "read_write") {
  const { rows } = await query(
    `UPDATE developer_api_tokens SET scopes = $3::jsonb, access_level = $4
     WHERE id = $1 AND user_id = $2 AND revoked_at IS NULL
     RETURNING ${COLUMN_SETS[0]}`,
    [id, userId, JSON.stringify(scopes), accessLevel] as never[]
  );
  return rows[0] ? withScopes(rows[0], 0) : null;
}