/**
 * Test-only FraudHostStore over the PGLite oracle + pgTable stubs for harness drizzle schema.
 * Never exported from any package entrypoint; never imported by src/fraud/** runtime code.
 */
import { date, integer, pgTable, text, timestamp, uuid } from 'drizzle-orm/pg-core'
import { eq, inArray, sql } from 'drizzle-orm'
import type { FraudDb } from '../fraud/types.js'
import type { AdapterConfig } from '../fraud/types.js'
import type { FraudHostStore } from '../fraud/host-store.js'

/** PGLite harness drizzle stubs — mirror host-owned tables, test-only. */
export const usersTable = pgTable('users', {
  id: uuid('id').primaryKey(),
  email: text('email'),
  emailIndex: text('email_index'),
  phoneIndex: text('phone_index'),
  emailCanonicalIndex: text('email_canonical_index'),
  createdAt: timestamp('created_at', { withTimezone: true }),
})

export const paymentMethodsTable = pgTable('payment_methods', {
  id: uuid('id').primaryKey(),
  userId: uuid('user_id').notNull(),
  cardFingerprint: text('card_fingerprint'),
})

export const purchasesTable = pgTable('purchases', {
  id: uuid('id').primaryKey(),
  userId: uuid('user_id'),
  dealId: uuid('deal_id'),
  createdAt: timestamp('created_at', { withTimezone: true }),
})

/** Host-resident analytics rollup — test-only stub, never in affiliate schema. */
export const referralLinkStatsDailyTable = pgTable('referral_link_stats_daily', {
  linkId: uuid('link_id').notNull(),
  day: date('day').notNull(),
  clicks: integer('clicks').notNull().default(0),
  signups: integer('signups').notNull().default(0),
})

export const hostHarnessTables = {
  usersTable,
  paymentMethodsTable,
  purchasesTable,
  referralLinkStatsDailyTable,
}

export function createPgliteFraudHostStore(db: FraudDb): FraudHostStore {
  return {
    async findExistingUserIdByEmailCanonical(canonical) {
      const rows = await db
        .select({ id: usersTable.id })
        .from(usersTable)
        .where(eq(usersTable.emailCanonicalIndex, canonical))
        .limit(1)
      return rows[0]?.id
    },

    async listReferrerCardFingerprints(referrerUserId) {
      const rows = await db
        .select({ fingerprint: paymentMethodsTable.cardFingerprint })
        .from(paymentMethodsTable)
        .where(eq(paymentMethodsTable.userId, referrerUserId))
      return rows.map((row) => row.fingerprint)
    },

    async getUserCardFingerprint(userId) {
      const rows = await db
        .select({ fingerprint: paymentMethodsTable.cardFingerprint })
        .from(paymentMethodsTable)
        .where(eq(paymentMethodsTable.userId, userId))
        .limit(1)
      return rows[0]?.fingerprint
    },

    async countDistinctReferrersForRefereeCardFingerprint(cardFingerprint, excludeRefereeUserId) {
      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 != ${excludeRefereeUserId}
        LIMIT 1
      `)) as { rows: Array<{ cnt: string }> }
      return Number(result.rows[0]?.cnt ?? 0)
    },

    async getPurchaseCreatedAt(purchaseId) {
      const rows = await db
        .select({ createdAt: purchasesTable.createdAt })
        .from(purchasesTable)
        .where(eq(purchasesTable.id, purchaseId))
        .limit(1)
      return rows[0]?.createdAt ?? null
    },

    async getUserIdentityRingFields(userId) {
      const rows = await db
        .select({
          phoneIndex: usersTable.phoneIndex,
          emailCanonicalIndex: usersTable.emailCanonicalIndex,
        })
        .from(usersTable)
        .where(eq(usersTable.id, userId))
        .limit(1)
      return rows[0] ?? null
    },

    async getUserPhoneIndex(userId) {
      const rows = await db
        .select({ phoneIndex: usersTable.phoneIndex })
        .from(usersTable)
        .where(eq(usersTable.id, userId))
        .limit(1)
      return rows[0]?.phoneIndex
    },

    async countSignupsByEmailDomainInWindow(domain, windowHours) {
      const result = (await db.execute(sql`
        SELECT COUNT(*) AS cnt
        FROM users
        WHERE email_canonical_index ILIKE ${'%@' + domain}
          AND created_at >= NOW() - (INTERVAL '1 hour' * ${windowHours})
      `)) as { rows: Array<{ cnt: string }> }
      return Number(result.rows[0]?.cnt ?? 0)
    },

    async getLinkConversionStats(referrerUserId, windowDays) {
      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 = ${referrerUserId}
          AND ls.day BETWEEN (CURRENT_DATE - ${windowDays}::int) AND (CURRENT_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) {
      if (userIds.length === 0) return []
      const rows = await db
        .select({ userId: usersTable.id, phoneBlindIndex: usersTable.phoneIndex })
        .from(usersTable)
        .where(inArray(usersTable.id, userIds))
      return rows.map((row) => ({ userId: row.userId, phoneBlindIndex: row.phoneBlindIndex ?? null }))
    },

    async getFraudConfig() {
      const result = (await db.execute(sql`
        SELECT fraud_config AS "fraudConfig"
        FROM referral_settings
        WHERE id = 1
        LIMIT 1
      `)) as { rows: Array<{ fraudConfig: Record<string, AdapterConfig> | null }> }
      if (result.rows.length === 0) return null
      return result.rows[0]!.fraudConfig ?? {}
    },
  }
}