/**
 * Test-only MaturityHostStore over PGLite host tables (purchases/deals/referral_settings).
 * Host-coupled anchor SQL lives ONLY here — never in module runtime code.
 */
import { sql } from 'drizzle-orm'
import type { Querier } from '@platform-modules/db'
import type { HarnessSchema } from './harness-schema.js'
import type { MaturityHostStore, RedemptionFactRef } from '../maturity-sweep.js'

export function createPgliteMaturityHostStore(
  db: Querier<HarnessSchema>,
): MaturityHostStore {
  return {
    async getRedemptionFacts(refs: RedemptionFactRef[]) {
      if (refs.length === 0) return []

      const sourceIds = [...new Set(refs.map((r) => r.sourceId))]
      const result = (await db.execute(sql`
        SELECT
          p.id::text AS source_id,
          CASE WHEN d.deal_type = 'COUPON' THEN 'coupon' ELSE 'physical' END AS kind,
          p.created_at AS paid_at,
          d.window_end AS expires_at,
          p.redeemed_at,
          p.created_at AS purchase_created_at
        FROM purchases p
        JOIN deals d ON d.id = p.deal_id
        WHERE p.id::text IN (${sql.join(
          sourceIds.map((id) => sql`${id}`),
          sql`, `,
        )})
      `)) as {
        rows: Array<{
          source_id: string
          kind: string
          paid_at: string
          expires_at: string | null
          redeemed_at: string | null
          purchase_created_at: string
        }>
      }

      const rowBySourceId = new Map(result.rows.map((row) => [row.source_id, row]))

      return refs.flatMap((ref) => {
        const row = rowBySourceId.get(ref.sourceId)
        if (!row) return []

        return [
          {
            sourceType: ref.sourceType,
            sourceId: ref.sourceId,
            kind: row.kind === 'coupon' ? ('coupon' as const) : ('physical' as const),
            paidAt: new Date(row.paid_at),
            expiresAt: row.expires_at ? new Date(row.expires_at) : null,
            redeemedAt: row.redeemed_at ? new Date(row.redeemed_at) : null,
            purchaseCreatedAt: new Date(row.purchase_created_at),
          },
        ]
      })
    },

    async getReferralSettings() {
      const result = (await db.execute(sql`
        SELECT hold_days, dispute_window_days
        FROM referral_settings
        WHERE id = 1
        LIMIT 1
      `)) as {
        rows: Array<{ hold_days: number; dispute_window_days: number }>
      }
      const row = result.rows[0]
      return {
        holdDays: row?.hold_days ?? 30,
        disputeWindowDays: row?.dispute_window_days ?? 120,
      }
    },
  }
}