/**
 * Support message encryption helpers.
 *
 * Messages are stored in support_messages.body_encrypted as
 * pgp_sym_encrypt ciphertext. Encryption is done inside the SQL statement
 * (via the @/server/db/crypto helper) so plaintext never leaves the request
 * boundary as a logged bind param.
 *
 * Decryption is performed via pgp_sym_decrypt in a SELECT projection. Both
 * single-row and batch helpers are exported.
 */

import { sql, eq, and, inArray } from 'drizzle-orm';
import type { SQL } from 'drizzle-orm';
import { supportMessages } from '@/server/db/schema';
import type { DrizzleClient } from '@/server/db/client';

export function encryptMessageBody(plaintext: string, key: string): SQL {
  return sql`pgp_sym_encrypt(${plaintext}, ${key})`;
}

export async function decryptMessageBody(
  db: DrizzleClient,
  messageId: string,
  key: string,
): Promise<string | null> {
  const rows = await db
    .select({
      body: sql<string>`pgp_sym_decrypt(CASE WHEN substring(${supportMessages.bodyEncrypted}::text, 1, 2) = '\\x' THEN ${supportMessages.bodyEncrypted}::bytea ELSE decode(${supportMessages.bodyEncrypted}::text, 'base64') END, ${key})::text`,
    })
    .from(supportMessages)
    .where(eq(supportMessages.id, messageId))
    .limit(1);
  return rows[0]?.body ?? null;
}

export interface DecryptedMessage {
  id: string;
  parentType: 'ticket' | 'case' | 'return' | 'pending_return';
  parentId: string;
  authorType: string;
  authorId: string | null;
  visibility: 'public' | 'vendor_internal' | 'site_internal';
  body: string;
  createdAt: string;
  editedAt: string | null;
  deletedAt: string | null;
}

export async function listDecryptedByParent(
  db: DrizzleClient,
  parentType: 'ticket' | 'case',
  parentId: string,
  key: string,
  opts: {
    includeDeleted?: boolean;
    visibilities?: Array<'public' | 'vendor_internal' | 'site_internal'>;
  } = {},
): Promise<DecryptedMessage[]> {
  const conds = [
    eq(supportMessages.parentType, parentType),
    eq(supportMessages.parentId, parentId),
  ];
  if (opts.visibilities && opts.visibilities.length > 0) {
    conds.push(inArray(supportMessages.visibility, opts.visibilities));
  }
  const rows = await db
    .select({
      id: supportMessages.id,
      parentType: supportMessages.parentType,
      parentId: supportMessages.parentId,
      authorType: supportMessages.authorType,
      authorId: supportMessages.authorId,
      visibility: supportMessages.visibility,
      body: sql<string>`pgp_sym_decrypt(CASE WHEN substring(${supportMessages.bodyEncrypted}::text, 1, 2) = '\\x' THEN ${supportMessages.bodyEncrypted}::bytea ELSE decode(${supportMessages.bodyEncrypted}::text, 'base64') END, ${key})::text`,
      createdAt: supportMessages.createdAt,
      editedAt: supportMessages.editedAt,
      deletedAt: supportMessages.deletedAt,
    })
    .from(supportMessages)
    .where(and(...conds))
    .orderBy(supportMessages.createdAt);

  return rows
    .filter((r) => opts.includeDeleted || !r.deletedAt)
    .map((r) => ({
      id: r.id,
      parentType: r.parentType,
      parentId: r.parentId,
      authorType: r.authorType,
      authorId: r.authorId,
      visibility: r.visibility,
      body: r.body,
      createdAt: r.createdAt.toISOString(),
      editedAt: r.editedAt?.toISOString() ?? null,
      deletedAt: r.deletedAt?.toISOString() ?? null,
    }));
}