/**
 * Admin users resource — read-only queries.
 *
 * - listUsers: filtered + paginated user list with purchase count.
 * - getUserDetail: full user profile with recent purchases, reviews,
 *   tickets (filed + targeting), and admin actions.
 *
 * Bulk listings (listUsers) decrypt email and realName server-side via
 * safeDecryptExpr — NULL means undecryptable (legacy/old-key) PII, rendered
 * blank. getUserDetail exposes only existence flags (hasPhone/hasEmail), never
 * plaintext.
 */

import {
  and,
  count,
  eq,
  gte,
  lte,
  inArray,
  isNotNull,
  isNull,
  or,
  sql,
  desc,
  type SQL,
} from 'drizzle-orm';
import type { DrizzleClient } from '@/server/db/client.js';
import {
  users,
  deals,
  reviews,
  reportTickets,
  adminActions,
  vendorTranslations,
  vendors,
  affiliateEnrollments,
  shippingAddresses,
  orderLine,
  dealSkus,
} from '@/server/db/schema.js';
import { order } from '@platform-modules/commerce-orders';
import { lineRedemptionStatusSql } from '@/server/fulfillment/voucher-line-state.js';
import { safeDecryptExpr } from '@/server/db/crypto.js';
import { VENDOR_DEFAULT_LOCALE } from '@/server/db/queries/vendors.js';
import { buildUserSearchCondition } from '@/server/admin/user-search-match.js';
import { formatAgorotPlain } from '@/lib/money';
import type {
  ListUsersFilter,
  ListUsersResult,
  UserDetail,
  UserDetailTicket,
  UserRole,
} from './types.js';

const roleExists: Record<Exclude<UserRole, 'customer'>, SQL> = {
  admin: sql`${users.isAdmin} = true`,
  vendor: sql`EXISTS (SELECT 1 FROM ${vendors} v WHERE v.owner_user_id = ${users.id})`,
  affiliate: sql`EXISTS (SELECT 1 FROM ${affiliateEnrollments} ae WHERE ae.user_id = ${users.id} AND ae.status IN ('active','suspended'))`,
};

export async function buildListUsersConditions(
  filter: ListUsersFilter,
  piiKey: string,
): Promise<SQL[]> {
  const c: SQL[] = [];
  if (filter.states?.length) c.push(inArray(users.accountState, filter.states));
  if (filter.roles?.length) {
    const parts: SQL[] = [];
    for (const r of filter.roles) {
      if (r === 'customer') {
        // "customer" = holds no elevated role: not admin AND not a vendor owner AND not enrolled affiliate
        parts.push(
          sql`(${users.isAdmin} = false AND NOT ${roleExists.vendor} AND NOT ${roleExists.affiliate})`,
        );
      } else {
        parts.push(roleExists[r]);
      }
    }
    c.push(parts.length === 1 ? parts[0]! : (or(...parts) as SQL));
  }
  if (filter.search && filter.search.trim().length > 0) {
    c.push(await buildUserSearchCondition(filter.search, piiKey));
  }
  if (filter.joinedFrom) c.push(gte(users.createdAt, new Date(filter.joinedFrom)));
  if (filter.joinedTo) c.push(lte(users.createdAt, new Date(`${filter.joinedTo}T23:59:59.999Z`)));
  if (filter.purchaseActivity === 'has') c.push(sql`${users.purchaseCount} > 0`);
  if (filter.purchaseActivity === 'none') c.push(sql`${users.purchaseCount} = 0`);
  if (typeof filter.minPurchases === 'number')
    c.push(gte(users.purchaseCount, filter.minPurchases));
  if (filter.hasEmail === true) c.push(isNotNull(users.email));
  if (filter.hasEmail === false) c.push(isNull(users.email));
  if (filter.emailVerified === true) c.push(isNotNull(users.emailVerifiedAt));
  if (filter.emailVerified === false) c.push(isNull(users.emailVerifiedAt));
  if (filter.hasShippingAddress === true)
    c.push(sql`EXISTS (SELECT 1 FROM ${shippingAddresses} sa WHERE sa.user_id = ${users.id})`);
  if (filter.hasShippingAddress === false)
    c.push(sql`NOT EXISTS (SELECT 1 FROM ${shippingAddresses} sa WHERE sa.user_id = ${users.id})`);
  if (filter.cityCode)
    c.push(
      sql`EXISTS (SELECT 1 FROM ${shippingAddresses} sa WHERE sa.user_id = ${users.id} AND sa.is_default = true AND sa.city_code = ${filter.cityCode})`,
    );
  return c;
}

function orderByClause(sort: ListUsersFilter['sort']): SQL {
  switch (sort) {
    case 'joined_asc':
      return sql`${users.createdAt} ASC`;
    case 'purchases_desc':
      return sql`${users.purchaseCount} DESC`;
    case 'name_asc':
      return sql`${users.displayName} ASC NULLS LAST`;
    case 'joined_desc':
    default:
      return sql`${users.createdAt} DESC`;
  }
}

export async function listUsers(
  db: DrizzleClient,
  filter: ListUsersFilter,
  piiKey: string,
): Promise<ListUsersResult> {
  const conditions = await buildListUsersConditions(filter, piiKey);
  const whereClause = conditions.length > 0 ? and(...conditions) : undefined;

  const defaultAddr = sql`LATERAL (
    SELECT sa.recipient_name, sa.city_name
    FROM ${shippingAddresses} sa
    WHERE sa.user_id = ${users.id} AND sa.is_default = true
    LIMIT 1
  ) AS default_addr`;

  const [totalRow, rows] = await Promise.all([
    db
      .select({ total: count() })
      .from(users)
      .where(whereClause)
      .then((r) => r[0]?.total ?? 0),
    db
      .select({
        id: users.id,
        userDisplayName: users.displayName,
        vendorDisplayName: sql<
          string | null
        >`(SELECT vt2.display_name FROM ${vendorTranslations} vt2 INNER JOIN ${vendors} v2 ON v2.id = vt2.vendor_id AND v2.owner_user_id = ${sql.raw('"users"."id"')} WHERE vt2.locale = ${VENDOR_DEFAULT_LOCALE} LIMIT 1)`,
        // NULL = undecryptable PII (legacy/old-key row), rendered as blank
        realName: sql<string | null>`${safeDecryptExpr('default_addr.recipient_name', piiKey)}`,
        defaultCity: sql<string | null>`default_addr.city_name`,
        // NULL = undecryptable PII (legacy/old-key row), rendered as blank
        email: sql<string | null>`${safeDecryptExpr(sql`${users.email}`, piiKey)}`,
        phoneHint: users.phoneHint,
        accountState: users.accountState,
        purchaseCount: users.purchaseCount,
        isAdmin: users.isAdmin,
        createdAt: users.createdAt,
      })
      .from(users)
      .leftJoin(defaultAddr, sql`true`)
      .where(whereClause)
      .orderBy(orderByClause(filter.sort))
      .limit(filter.limit)
      .offset(filter.offset),
  ]);

  const data = rows.map(({ userDisplayName, vendorDisplayName, ...r }) => ({
    ...r,
    displayName: vendorDisplayName ?? userDisplayName ?? null,
  }));
  return { data, total: Number(totalRow) };
}

// ─── getUserDetail ────────────────────────────────────────────────────────────

export async function getUserDetail(db: DrizzleClient, userId: string): Promise<UserDetail | null> {
  // Main user row
  const [user] = await db
    .select({
      id: users.id,
      displayName: sql<
        string | null
      >`(SELECT vt2.display_name FROM ${vendorTranslations} vt2 INNER JOIN ${vendors} v2 ON v2.id = vt2.vendor_id AND v2.owner_user_id = ${sql.raw('"users"."id"')} WHERE vt2.locale = ${VENDOR_DEFAULT_LOCALE} LIMIT 1)`,
      avatarType: users.avatarType,
      avatarValue: users.avatarValue,
      purchaseCount: users.purchaseCount,
      accountState: users.accountState,
      isAdmin: users.isAdmin,
      createdAt: users.createdAt,
      deletionRequestedAt: users.deletionRequestedAt,
      // We cannot decrypt phone/email; expose only whether they exist
      phone: users.phone,
      email: users.email,
    })
    .from(users)
    .where(eq(users.id, userId))
    .limit(1);

  if (!user) return null;

  // Parallel supporting queries
  const [purchaseRows, reviewRows, ticketsAsReporter, ticketsAsTarget, actionRows] =
    await Promise.all([
      // Recent order-lines (with deal title via join)
      db
        .select({
          id: orderLine.id,
          dealId: dealSkus.dealId,
          dealTitle: deals.title,
          vendorId: orderLine.vendorId,
          quantity: orderLine.qty,
          lineTotalAgorot: orderLine.lineTotal,
          paymentStatus: order.status,
          redemptionStatus: lineRedemptionStatusSql(orderLine.id),
          createdAt: orderLine.createdAt,
        })
        .from(orderLine)
        .innerJoin(order, eq(orderLine.orderId, order.id))
        .innerJoin(dealSkus, eq(orderLine.variantId, dealSkus.id))
        .innerJoin(deals, eq(dealSkus.dealId, deals.id))
        .where(eq(order.buyerUserId, userId))
        .orderBy(desc(orderLine.createdAt))
        .limit(20),

      // Reviews authored by this user
      db
        .select({
          id: reviews.id,
          vendorId: reviews.vendorId,
          rating: reviews.rating,
          body: reviews.body,
          reviewType: reviews.reviewType,
          isVisible: reviews.isVisible,
          createdAt: reviews.createdAt,
        })
        .from(reviews)
        .where(eq(reviews.userId, userId))
        .orderBy(desc(reviews.createdAt))
        .limit(20),

      // Tickets this user filed
      db
        .select({
          id: reportTickets.id,
          targetType: reportTickets.targetType,
          targetId: reportTickets.targetId,
          reason: reportTickets.reason,
          status: reportTickets.status,
          createdAt: reportTickets.createdAt,
        })
        .from(reportTickets)
        .where(eq(reportTickets.reporterUserId, userId))
        .orderBy(desc(reportTickets.createdAt))
        .limit(20),

      // Tickets targeting this user (as a USER target)
      db
        .select({
          id: reportTickets.id,
          targetType: reportTickets.targetType,
          targetId: reportTickets.targetId,
          reason: reportTickets.reason,
          status: reportTickets.status,
          createdAt: reportTickets.createdAt,
        })
        .from(reportTickets)
        .where(eq(reportTickets.targetId, userId))
        .orderBy(desc(reportTickets.createdAt))
        .limit(20),

      // Recent admin actions on this user
      db
        .select({
          id: adminActions.id,
          adminId: adminActions.adminId,
          action: adminActions.action,
          note: adminActions.note,
          createdAt: adminActions.createdAt,
        })
        .from(adminActions)
        .where(eq(adminActions.targetId, userId))
        .orderBy(desc(adminActions.createdAt))
        .limit(30),
    ]);

  // Merge tickets (reporter + target), deduplicate by id
  const ticketMap = new Map<string, UserDetailTicket>();
  for (const r of ticketsAsReporter) {
    ticketMap.set(r.id, { ...r, role: 'reporter' });
  }
  for (const r of ticketsAsTarget) {
    if (!ticketMap.has(r.id)) {
      ticketMap.set(r.id, { ...r, role: 'target' });
    }
  }
  const tickets = Array.from(ticketMap.values()).sort(
    (a, b) => b.createdAt.getTime() - a.createdAt.getTime(),
  );

  return {
    user: {
      id: user.id,
      displayName: user.displayName ?? '',
      avatarType: user.avatarType,
      avatarValue: user.avatarValue,
      purchaseCount: user.purchaseCount,
      accountState: user.accountState,
      isAdmin: user.isAdmin,
      createdAt: user.createdAt,
      deletionRequestedAt: user.deletionRequestedAt,
      hasPhone: user.phone !== null,
      hasEmail: user.email !== null,
    },
    purchases: purchaseRows.map((row) => ({
      id: row.id,
      dealId: row.dealId,
      dealTitle: row.dealTitle,
      vendorId: row.vendorId,
      quantity: row.quantity,
      amountPaid: formatAgorotPlain(Number(row.lineTotalAgorot)),
      paymentStatus: row.paymentStatus,
      redemptionStatus: row.redemptionStatus,
      createdAt: row.createdAt,
    })),
    reviews: reviewRows,
    tickets,
    recentActions: actionRows,
  };
}