/**
 * Query layer for the `email_verifications` table.
 *
 * Per the project query-layer rule, all Drizzle reads/writes for this entity
 * live here. Higher layers (auth/email_verification.ts, API routes) never
 * touch the Drizzle client directly for this table.
 */

import { firstExecuteRow } from '../execute-rows.js';
import { and, eq, gt, isNull, lt, sql } from 'drizzle-orm';
import type { DrizzleClient } from '../client.js';
import { emailVerifications } from '../schema.js';
import { decryptExpr } from '../crypto.js';

export interface InsertEmailVerificationRow {
  userId: string;
  tokenHash: string;
  emailEncrypted: string;
  purpose: 'signup' | 'change';
  expiresAt: Date;
}

export async function insert(
  db: DrizzleClient,
  row: InsertEmailVerificationRow,
): Promise<{ id: string }> {
  const [created] = await db
    .insert(emailVerifications)
    .values(row)
    .returning({ id: emailVerifications.id });
  if (!created) {
    throw new Error('email_verifications insert returned no row');
  }
  return created;
}

export async function markConsumed(db: DrizzleClient, id: string, at: Date): Promise<void> {
  await db.update(emailVerifications).set({ consumedAt: at }).where(eq(emailVerifications.id, id));
}

export interface TokenRowWithEmail {
  id: string;
  userId: string;
  tokenHash: string;
  email: string;
  purpose: 'signup' | 'change';
  expiresAt: Date;
  consumedAt: Date | null;
}

/**
 * Fetch a row by token hash and decrypt the emailEncrypted column in-query
 * using pgp_sym_decrypt. Returns null if no matching row found.
 */
type TokenRowDb = {
  id: string;
  userId: string;
  tokenHash: string;
  email: string;
  purpose: string;
  expiresAt: string | Date;
  consumedAt: string | Date | null;
};

export async function findByTokenHashWithEmail(
  db: DrizzleClient,
  tokenHash: string,
  piiKey: string,
): Promise<TokenRowWithEmail | null> {
  const result = await db.execute<TokenRowDb>(sql`
    SELECT
      id,
      user_id         AS "userId",
      token_hash      AS "tokenHash",
      ${decryptExpr('email_encrypted', piiKey)} AS email,
      purpose,
      expires_at      AS "expiresAt",
      consumed_at     AS "consumedAt"
    FROM email_verifications
    WHERE token_hash = ${tokenHash}
    LIMIT 1
  `);
  const row = firstExecuteRow<TokenRowDb>(result) ?? null;
  if (!row) return null;
  return {
    id: row.id,
    userId: row.userId,
    tokenHash: row.tokenHash,
    email: row.email,
    purpose: row.purpose as TokenRowWithEmail['purpose'],
    expiresAt: new Date(row.expiresAt),
    consumedAt: row.consumedAt ? new Date(row.consumedAt) : null,
  };
}

/** Retention grace period: keep rows up to 7 days past expiry for audit trail. */
export const EXPIRED_VERIFICATION_GRACE_MS = 7 * 24 * 60 * 60 * 1000;

/**
 * Delete email_verifications rows whose expires_at is more than 7 days in
 * the past. Returns the number of rows deleted.
 */
export async function purgeExpired(db: DrizzleClient, now: Date = new Date()): Promise<number> {
  const cutoff = new Date(now.getTime() - EXPIRED_VERIFICATION_GRACE_MS);
  const result = await db
    .delete(emailVerifications)
    .where(lt(emailVerifications.expiresAt, cutoff))
    .returning({ id: emailVerifications.id });
  return result.length;
}

export async function findActiveForUser(db: DrizzleClient, userId: string, now: Date) {
  const [row] = await db
    .select()
    .from(emailVerifications)
    .where(
      and(
        eq(emailVerifications.userId, userId),
        isNull(emailVerifications.consumedAt),
        gt(emailVerifications.expiresAt, now),
      ),
    )
    .limit(1);
  return row ?? null;
}