/**
 * User queries - typed async functions for the users table.
 *
 * All functions take a DrizzleClient as first argument.
 * PII lookups use blind-index columns; decryption happens at the application layer.
 */

import { eq, sql } from 'drizzle-orm';
import type { SQL } from 'drizzle-orm';
import type { DrizzleClient } from '../client.js';
import { users } from '../schema.js';
import type { accountStateUserEnum } from '../schema.js';
import { type AvatarType } from '@/lib/enums/avatar-type';
import { setUserPhone, setUserEmail } from '../pii-write.js';

type AccountState = (typeof accountStateUserEnum.enumValues)[number];

export async function findById(db: DrizzleClient, id: string) {
  const [row] = await db.select().from(users).where(eq(users.id, id)).limit(1);
  return row ?? null;
}

/**
 * Lookup by blind-index (pass the pre-computed HMAC-SHA256 hex of the raw phone).
 * Never pass the raw phone directly.
 */
export async function findByPhone(db: DrizzleClient, phoneIndex: string) {
  const [row] = await db.select().from(users).where(eq(users.phoneIndex, phoneIndex)).limit(1);
  return row ?? null;
}

/**
 * Lookup by email blind-index (pass the pre-computed HMAC-SHA256 hex of the raw email).
 */
export async function findByEmail(db: DrizzleClient, emailIndex: string) {
  const [row] = await db.select().from(users).where(eq(users.emailIndex, emailIndex)).limit(1);
  return row ?? null;
}

export type CreateUserInput = {
  id?: string;
  /** E.164 phone (e.g. '+972501234567'). */
  phone: string;
  email?: string | null;
  piiKey: string;
  avatarType?: AvatarType;
  avatarValue?: string;
};

export async function createUser(db: DrizzleClient, input: CreateUserInput) {
  const phonePatch = await setUserPhone(input.phone, input.piiKey);
  const emailPatch =
    input.email != null
      ? await setUserEmail(input.email, input.piiKey)
      : { email: null, emailIndex: null };
  const [row] = await db
    .insert(users)
    .values({
      ...(input.id ? { id: input.id } : {}),
      ...phonePatch,
      ...emailPatch,
      avatarType: input.avatarType ?? 'ICON',
      avatarValue: input.avatarValue ?? '',
    })
    .returning();
  return row!;
}

export type CreateUserRecordInput = Omit<typeof users.$inferInsert, 'email' | 'phone'> & {
  email?: SQL | string | null;
  phone?: SQL | string | null;
};

export async function createUserRecord(
  db: DrizzleClient,
  values: CreateUserRecordInput,
): Promise<{ id: string }> {
  const [row] = await db.insert(users).values(values).returning({ id: users.id });
  return row!;
}

export async function updateAccountState(
  db: DrizzleClient,
  id: string,
  state: AccountState,
  extra?: { deletionRequestedAt?: Date },
) {
  const [row] = await db
    .update(users)
    .set({
      accountState: state,
      ...(extra?.deletionRequestedAt !== undefined
        ? { deletionRequestedAt: extra.deletionRequestedAt }
        : {}),
    })
    .where(eq(users.id, id))
    .returning();
  return row ?? null;
}

/**
 * Persist a new password hash WITHOUT bumping sessionVersion. Used by the
 * lazy upgrade-on-login path to migrate a legacy pep1/pbkdf2 hash to argon2id —
 * the user just authenticated, so existing sessions must remain valid (unlike
 * credentials.updatePassword, which revokes them for password-change flows).
 */
export async function updatePasswordHash(
  db: DrizzleClient,
  id: string,
  passwordHash: string,
): Promise<void> {
  await db.update(users).set({ passwordHash }).where(eq(users.id, id));
}

export async function updatePasswordHashAndAdmin(
  db: DrizzleClient,
  id: string,
  passwordHash: string,
): Promise<void> {
  await db.update(users).set({ passwordHash, isAdmin: true }).where(eq(users.id, id));
}

export async function incrementPurchaseCount(db: DrizzleClient, id: string) {
  const [row] = await db
    .update(users)
    .set({ purchaseCount: sql`${users.purchaseCount} + 1` })
    .where(eq(users.id, id))
    .returning({ purchaseCount: users.purchaseCount });
  return row?.purchaseCount ?? null;
}

export async function incrementPurchaseCountBy(
  db: DrizzleClient,
  id: string,
  count: number,
): Promise<void> {
  await db
    .update(users)
    .set({ purchaseCount: sql`${users.purchaseCount} + ${count}` })
    .where(eq(users.id, id));
}

/**
 * Fetch a user by ID with the email column decrypted in-DB via pgcrypto.
 * Handles both hex-bytea and base64-text storage formats.
 *
 * Returns null if no user found.
 */
export async function getUserWithDecryptedEmail(
  db: DrizzleClient,
  id: string,
  piiKey: string,
): Promise<{ id: string; email: string | null; emailVerifiedAt: Date | null } | null> {
  const [row] = await db
    .select({
      id: users.id,
      email: sql<
        string | null
      >`CASE WHEN ${users.email} IS NOT NULL THEN pgp_sym_decrypt(CASE WHEN substring(${users.email}, 1, 2) = '\\x' THEN ${users.email}::bytea ELSE decode(${users.email}, 'base64') END, ${piiKey})::text ELSE NULL END`,
      emailVerifiedAt: users.emailVerifiedAt,
    })
    .from(users)
    .where(eq(users.id, id))
    .limit(1);
  return row ?? null;
}

/** Returns true when the user has is_admin=true. */
export async function isAdminUser(db: DrizzleClient, userId: string): Promise<boolean> {
  const [u] = await db
    .select({ isAdmin: users.isAdmin })
    .from(users)
    .where(eq(users.id, userId))
    .limit(1);
  return u?.isAdmin === true;
}

/**
 * Persist user's preferred city code for feed filtering.
 * Pass null to clear (revert to auto geo-detection).
 */
export async function updatePreferredCity(
  db: DrizzleClient,
  userId: string,
  cityCode: string | null,
): Promise<void> {
  await db.update(users).set({ preferredCityCode: cityCode }).where(eq(users.id, userId));
}

export async function setUserAccountState(
  db: DrizzleClient,
  userId: string,
  accountState: AccountState,
): Promise<void> {
  await db.update(users).set({ accountState }).where(eq(users.id, userId));
}

export async function setUserAdminFlag(
  db: DrizzleClient,
  userId: string,
  isAdmin: boolean,
): Promise<void> {
  await db.update(users).set({ isAdmin }).where(eq(users.id, userId));
}

// ── Stripe payment helpers ────────────────────────────────────────────────────

/** Return the Stripe customer ID for a user, or null if not yet created. */
export async function getUserStripeCustomerId(
  db: DrizzleClient,
  userId: string,
): Promise<string | null> {
  const [row] = await db
    .select({ stripeCustomerId: users.stripeCustomerId })
    .from(users)
    .where(eq(users.id, userId))
    .limit(1);
  return row?.stripeCustomerId ?? null;
}

/** Persist the Stripe customer ID after creating a Customer object. */
export async function setUserStripeCustomerId(
  db: DrizzleClient,
  userId: string,
  stripeCustomerId: string,
): Promise<void> {
  await db.update(users).set({ stripeCustomerId }).where(eq(users.id, userId));
}

/** Persist the user's default Stripe payment method ID. */
export async function setUserDefaultPaymentMethod(
  db: DrizzleClient,
  userId: string,
  paymentMethodId: string,
): Promise<void> {
  await db
    .update(users)
    .set({ defaultPaymentMethodId: paymentMethodId })
    .where(eq(users.id, userId));
}

/** Return the user's default Stripe payment method ID, or null if not set. */
export async function getUserDefaultPaymentMethod(
  db: DrizzleClient,
  userId: string,
): Promise<string | null> {
  const [row] = await db
    .select({ defaultPaymentMethodId: users.defaultPaymentMethodId })
    .from(users)
    .where(eq(users.id, userId))
    .limit(1);
  return row?.defaultPaymentMethodId ?? null;
}