/**
 * W3 — read-only FraudHostStore over multideal host-resident tables.
 * Mirrors donor fraud adapter queries under server/referrals/fraud/* and attribution.ts.
 */
import { eq, inArray, sql } from 'drizzle-orm';
import type { AdapterConfig, FraudHostStore } from '@platform-modules/affiliate/fraud';
import type { DrizzleClient } from '@/server/db/client';
import { orderLine, paymentMethods, referralSettings, users } from '@/server/db/schema';
import { asOrderLineId, asUserId, toModuleRef } from '@/server/platform-seams/ids.js';

export type FraudHostStoreClock = {
  now(): Date;
};

function civilDateIso(clock: FraudHostStoreClock): string {
  return clock.now().toISOString().slice(0, 10);
}

export function createFraudHostStore(
  db: DrizzleClient,
  clock: FraudHostStoreClock,
): FraudHostStore {
  return {
    async findExistingUserIdByEmailCanonical(canonical: string): Promise<string | undefined> {
      const rows = await db
        .select({ id: users.id })
        .from(users)
        .where(eq(users.emailCanonicalIndex, canonical))
        .limit(1);
      return rows[0]?.id;
    },

    async listReferrerCardFingerprints(
      referrerUserId: string,
    ): Promise<Array<string | null | undefined>> {
      const rows = await db
        .select({ fingerprint: paymentMethods.cardFingerprint })
        .from(paymentMethods)
        .where(eq(paymentMethods.userId, toModuleRef(asUserId(referrerUserId))));
      return rows.map((row) => row.fingerprint);
    },

    async getUserCardFingerprint(userId: string): Promise<string | null | undefined> {
      const rows = await db
        .select({ cardFingerprint: paymentMethods.cardFingerprint })
        .from(paymentMethods)
        .where(eq(paymentMethods.userId, toModuleRef(asUserId(userId))))
        .limit(1);
      return rows[0]?.cardFingerprint;
    },

    async countDistinctReferrersForRefereeCardFingerprint(
      cardFingerprint: string,
      excludeRefereeUserId: string,
    ): Promise<number> {
      const result = (await db.execute(sql`
        SELECT COUNT(DISTINCT r.referrer_user_id) AS cnt
        FROM referrals r
        JOIN payment_methods pm ON pm.user_id = r.referee_user_id
        WHERE pm.card_fingerprint = ${cardFingerprint}
          AND r.referee_user_id != ${toModuleRef(asUserId(excludeRefereeUserId))}
        LIMIT 1
      `)) as { rows: Array<{ cnt: string }> };
      return Number(result.rows[0]?.cnt ?? 0);
    },

    async getPurchaseCreatedAt(purchaseId: string): Promise<Date | null | undefined> {
      const rows = await db
        .select({ createdAt: orderLine.createdAt })
        .from(orderLine)
        .where(eq(orderLine.id, toModuleRef(asOrderLineId(purchaseId))))
        .limit(1);
      return rows[0]?.createdAt ?? null;
    },

    async getUserIdentityRingFields(userId: string): Promise<{
      phoneIndex: string | null | undefined;
      emailCanonicalIndex: string | null | undefined;
    } | null> {
      const rows = await db
        .select({
          phoneIndex: users.phoneIndex,
          emailCanonicalIndex: users.emailCanonicalIndex,
        })
        .from(users)
        .where(eq(users.id, toModuleRef(asUserId(userId))))
        .limit(1);
      return rows[0] ?? null;
    },

    async getUserPhoneIndex(userId: string): Promise<string | null | undefined> {
      const rows = await db
        .select({ phoneIndex: users.phoneIndex })
        .from(users)
        .where(eq(users.id, toModuleRef(asUserId(userId))))
        .limit(1);
      return rows[0]?.phoneIndex;
    },

    async countSignupsByEmailDomainInWindow(domain: string, windowHours: number): Promise<number> {
      if (!Number.isInteger(windowHours) || windowHours <= 0 || windowHours > 8760) {
        return 0;
      }
      const cutoff = new Date(clock.now().getTime() - windowHours * 3_600_000);
      const result = (await db.execute(sql`
        SELECT COUNT(*) AS cnt
        FROM users
        WHERE email_canonical_index ILIKE ${'%@' + domain}
          AND created_at >= ${cutoff.toISOString()}
      `)) as { rows: Array<{ cnt: string }> };
      return Number(result.rows[0]?.cnt ?? 0);
    },

    async getLinkConversionStats(
      referrerUserId: string,
      windowDays: number,
    ): Promise<{ clicks: number; conversions: number }> {
      const anchorDate = civilDateIso(clock);
      const result = (await db.execute(sql`
        SELECT
          COALESCE(SUM(ls.clicks), 0)  AS clicks,
          COALESCE(SUM(ls.signups), 0) AS conversions
        FROM referral_links rl
        JOIN referral_link_stats_daily ls ON ls.link_id = rl.id
        WHERE rl.owner_user_id = ${toModuleRef(asUserId(referrerUserId))}
          AND ls.day BETWEEN (${anchorDate}::date - ${windowDays}::int) AND (${anchorDate}::date - 1)
      `)) as { rows: Array<{ conversions: string; clicks: string }> };
      const row = result.rows[0];
      return {
        clicks: Number(row?.clicks ?? 0),
        conversions: Number(row?.conversions ?? 0),
      };
    },

    async getPhoneBlindIndexes(
      userIds: string[],
    ): Promise<Array<{ userId: string; phoneBlindIndex: string | null }>> {
      if (userIds.length === 0) return [];
      const rows = await db
        .select({ id: users.id, phoneIndex: users.phoneIndex })
        .from(users)
        .where(inArray(users.id, userIds));
      return rows.map((row) => ({
        userId: toModuleRef(asUserId(row.id)),
        phoneBlindIndex: row.phoneIndex ?? null,
      }));
    },

    async getFraudConfig(): Promise<Record<string, AdapterConfig> | null> {
      const rows = await db
        .select({ fraudConfig: referralSettings.fraudConfig })
        .from(referralSettings)
        .where(eq(referralSettings.id, 1))
        .limit(1);
      if (rows.length === 0) return null;
      return (rows[0]!.fraudConfig as Record<string, AdapterConfig>) ?? {};
    },
  };
}