/**
 * Payment method query helpers.
 *
 * All functions take DrizzleClient as first arg per project convention.
 * IDOR safety: every write helper requires userId to scope operations.
 */

import { and, count, desc, eq, type SQL } from 'drizzle-orm';
import type { DrizzleClient } from '../client.js';
import { paymentMethods } from '../schema.js';
import { type PaymentBrand } from '@/lib/enums/payment-brand';

export type { PaymentBrand };

export interface UserPaymentMethod {
  id: string;
  last4: string;
  brand: PaymentBrand;
  isDefault: boolean;
}

/**
 * List a user's saved payment methods, most recently added first.
 * Returns up to 10 rows. Tokens are NOT returned (never needed client-side).
 */
export async function listUserPaymentMethods(
  db: DrizzleClient,
  userId: string,
): Promise<UserPaymentMethod[]> {
  return db
    .select({
      id: paymentMethods.id,
      last4: paymentMethods.last4,
      brand: paymentMethods.brand,
      isDefault: paymentMethods.isDefault,
    })
    .from(paymentMethods)
    .where(eq(paymentMethods.userId, userId))
    .orderBy(desc(paymentMethods.id))
    .limit(10);
}

export interface NewPaymentMethod {
  userId: string;
  last4: string;
  brand: PaymentBrand;
  /** pgp_sym_encrypt SQL fragment — use encrypt() from @/server/db/crypto. */
  token: SQL;
  /** HMAC-SHA-256 of Stripe card fingerprint (keyed by PII_KEY). Undefined if unavailable. */
  cardFingerprint?: string;
}

/**
 * Insert a new payment method for a user.
 * If this is the user's first card, marks it as default automatically.
 * Returns the inserted row id, last4, brand, isDefault.
 */
export async function addPaymentMethod(
  db: DrizzleClient,
  input: NewPaymentMethod,
): Promise<{ id: string; last4: string; brand: PaymentBrand; isDefault: boolean }> {
  const countRows = await db
    .select({ existing: count() })
    .from(paymentMethods)
    .where(eq(paymentMethods.userId, input.userId));

  const isDefault = (countRows[0]?.existing ?? 0) === 0;

  const [row] = await db
    .insert(paymentMethods)
    .values({
      userId: input.userId,
      last4: input.last4,
      brand: input.brand,
      token: input.token,
      isDefault,
      ...(input.cardFingerprint ? { cardFingerprint: input.cardFingerprint } : {}),
    })
    .returning({
      id: paymentMethods.id,
      last4: paymentMethods.last4,
      brand: paymentMethods.brand,
      isDefault: paymentMethods.isDefault,
    });

  return row!;
}

/**
 * Delete a payment method by id, scoped to the owning user (IDOR-safe).
 *
 * If the deleted method was the default, the most recently-added remaining
 * method (if any) is automatically promoted to default.
 *
 * Returns true if the method was found and deleted; false if not found (not owned
 * by this user or does not exist).
 */
export async function deletePaymentMethod(
  db: DrizzleClient,
  id: string,
  userId: string,
): Promise<boolean> {
  // Ownership check — select scoped to the user.
  const [existing] = await db
    .select()
    .from(paymentMethods)
    .where(and(eq(paymentMethods.id, id), eq(paymentMethods.userId, userId)))
    .limit(1);

  if (!existing) {
    return false;
  }

  await db.delete(paymentMethods).where(eq(paymentMethods.id, id));

  // If the deleted method was the default, promote the next most recent one.
  if (existing.isDefault) {
    const [next] = await db
      .select({ id: paymentMethods.id })
      .from(paymentMethods)
      .where(eq(paymentMethods.userId, userId))
      .orderBy(desc(paymentMethods.id))
      .limit(1);
    if (next) {
      await db
        .update(paymentMethods)
        .set({ isDefault: true })
        .where(eq(paymentMethods.id, next.id));
    }
  }

  return true;
}