import { sql } from 'drizzle-orm'
import {
  bigint,
  boolean,
  index,
  integer,
  jsonb,
  pgEnum,
  pgTable,
  smallint,
  text,
  timestamp,
  uniqueIndex,
  uuid,
} from 'drizzle-orm/pg-core'

export const referralKindEnum = pgEnum('referral_kind', ['referral', 'affiliate'])

export const referralStatusEnum = pgEnum('referral_status', [
  'pending',
  'qualified',
  'rejected',
  'quarantined',
])

export const referralLinksTable = pgTable(
  'referral_links',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    ownerUserId: uuid('owner_user_id').notNull(),
    code: text('code').notNull().unique(),
    kind: referralKindEnum('kind').notNull().default('referral'),
    commissionPctOverride: integer('commission_pct_override'),
    commissionWindowDaysOverride: integer('commission_window_days_override'),
    active: boolean('active').notNull().default(true),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [index('referral_links_owner_idx').on(t.ownerUserId)],
)

export const referralsTable = pgTable(
  'referrals',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    referrerUserId: uuid('referrer_user_id').notNull(),
    refereeUserId: uuid('referee_user_id').notNull().unique(),
    linkId: uuid('link_id')
      .notNull()
      .references(() => referralLinksTable.id),
    kind: referralKindEnum('kind').notNull(),
    status: referralStatusEnum('status').notNull().default('pending'),
    qualifiedAt: timestamp('qualified_at', { withTimezone: true }),
    clickedAt: timestamp('clicked_at', { withTimezone: true }),
    commissionPct: integer('commission_pct'),
    commissionWindowEndsAt: timestamp('commission_window_ends_at', { withTimezone: true }),
    commissionOrdersRemaining: integer('commission_orders_remaining'),
    refereePromoCodeId: uuid('referee_promo_code_id'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    visitorIdReferrer: text('visitor_id_referrer'),
    visitorIdReferee: text('visitor_id_referee'),
    ipHashReferee: text('ip_hash_referee'),
    quarantinedAt: timestamp('quarantined_at', { withTimezone: true }),
  },
  (t) => [
    index('referrals_referrer_idx').on(t.referrerUserId),
    index('referrals_referrer_status_idx').on(t.referrerUserId, t.status),
  ],
)

export const affiliateEntryTypeEnum = pgEnum('affiliate_entry_type', [
  'affiliate_commission',
  'referral_reward',
  'refund_clawback',
  'adjustment',
  'redemption', // affiliate WITHDRAWAL/payout entry type — payout debit writes it, settlement verifies it (§3.3); NOT a commerce clone
])

export const affiliateEntriesTable = pgTable(
  'affiliate_entries',
  {
    entryId: uuid('entry_id').primaryKey(), // → ledgerEntries.id (cross-package; NO .references())
    ownerId: text('owner_id').notNull(),
    entryType: affiliateEntryTypeEnum('entry_type').notNull(),
    sourceType: text('source_type').notNull(),
    sourceId: text('source_id').notNull(),
    referralId: uuid('referral_id').references(() => referralsTable.id), // intra-package FK — OK
    resolvedPct: smallint('resolved_pct'),
    memo: text('memo'),
    consumedAt: timestamp('consumed_at', { withTimezone: true }),
  },
  (t) => [
    uniqueIndex('affiliate_entries_idem_uq').on(t.entryType, t.sourceType, t.sourceId),
    index('affiliate_entries_source_idx').on(t.sourceType, t.sourceId),
    index('affiliate_entries_unconsumed_idx').on(t.sourceType, t.sourceId).where(sql`${t.consumedAt} IS NULL`),
  ],
)
export type AffiliateEntry = typeof affiliateEntriesTable.$inferSelect
export type NewAffiliateEntry = typeof affiliateEntriesTable.$inferInsert

export const affiliateEnrollmentStatusEnum = pgEnum('affiliate_enrollment_status', [
  'active',
  'suspended',
  'revoked',
])

export const affiliateStripeStatusEnum = pgEnum('affiliate_stripe_status', [
  'none',
  'pending',
  'restricted',
  'enabled',
  'rejected',
])

export const affiliateEnrollmentsTable = pgTable(
  'affiliate_enrollments',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    userId: uuid('user_id').notNull().unique(),
    status: affiliateEnrollmentStatusEnum('status').notNull(),
    commissionPct: integer('commission_pct'),
    enrolledAt: timestamp('enrolled_at', { withTimezone: true }).notNull().defaultNow(),
    suspendedAt: timestamp('suspended_at', { withTimezone: true }),
    suspendedReason: text('suspended_reason'),
    stripeAccountId: text('stripe_account_id').unique(),
    stripeStatus: affiliateStripeStatusEnum('stripe_status').notNull().default('none'),
    stripePayoutsEnabled: boolean('stripe_payouts_enabled').notNull().default(false),
    stripeUpdatedAt: timestamp('stripe_updated_at', { withTimezone: true }),
    tosAcceptedAt: timestamp('tos_accepted_at', { withTimezone: true }).notNull(),
    tosVersion: text('tos_version').notNull(),
  },
  (t) => [
    index('affiliate_enrollments_status_idx').on(t.status),
    index('affiliate_enrollments_stripe_acct_idx')
      .on(t.stripeAccountId)
      .where(sql`${t.stripeAccountId} IS NOT NULL`),
  ],
)

export const affiliatePayoutStatusEnum = pgEnum('affiliate_payout_status', [
  'requested',
  'approved',
  'processing',
  'paid',
  'failed',
  'cancelled',
])

export const affiliatePayoutsTable = pgTable(
  'affiliate_payouts',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    userId: uuid('user_id').notNull(),
    enrollmentId: uuid('enrollment_id')
      .notNull()
      .references(() => affiliateEnrollmentsTable.id),
    amountAgorot: bigint('amount_agorot', { mode: 'bigint' }).notNull(),
    status: affiliatePayoutStatusEnum('status').notNull(),
    requestedAt: timestamp('requested_at', { withTimezone: true }).notNull().defaultNow(),
    approvedAt: timestamp('approved_at', { withTimezone: true }),
    approvedByUserId: uuid('approved_by_user_id'),
    processingAt: timestamp('processing_at', { withTimezone: true }),
    paidAt: timestamp('paid_at', { withTimezone: true }),
    failedAt: timestamp('failed_at', { withTimezone: true }),
    failureReason: text('failure_reason'),
    stripeTransferId: text('stripe_transfer_id').unique(),
    stripePayoutId: text('stripe_payout_id').unique(),
    idempotencyKey: text('idempotency_key').notNull().unique(),
    ledgerEntryId: uuid('ledger_entry_id')
      .unique()
      .references(() => affiliateEntriesTable.entryId),
  },
  (t) => [
    index('affiliate_payouts_user_idx').on(t.userId, t.requestedAt),
    index('affiliate_payouts_status_idx').on(t.status),
    uniqueIndex('affiliate_payouts_one_active_idx')
      .on(t.userId)
      .where(sql`${t.status} IN ('requested', 'approved', 'processing')`),
  ],
)

/** Immutable audit log of every fraud-engine decision point. */
export const fraudEventsTable = pgTable(
  'fraud_events',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    userId: uuid('user_id').notNull(),
    referralId: uuid('referral_id').references(() => referralsTable.id),
    decisionPoint: text('decision_point').notNull(),
    adapter: text('adapter').notNull(),
    action: text('action').notNull(),
    codes: text('codes')
      .array()
      .notNull()
      .default(sql`'{}'::text[]`),
    detail: jsonb('detail'),
    resolvedBy: uuid('resolved_by'),
    resolvedAt: timestamp('resolved_at', { withTimezone: true }),
    ts: timestamp('ts', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    index('fraud_events_user_ts_idx').on(t.userId, t.ts.desc()),
    index('fraud_events_resolved_ts_idx').on(t.resolvedAt, t.ts.desc()),
    index('fraud_events_referral_idx')
      .on(t.referralId)
      .where(sql`${t.referralId} IS NOT NULL`),
    uniqueIndex('fraud_events_open_uq')
      .on(t.userId, t.referralId, t.decisionPoint, t.adapter)
      .where(sql`${t.referralId} IS NOT NULL AND ${t.resolvedAt} IS NULL`),
    uniqueIndex('fraud_events_open_noref_uq')
      .on(t.userId, t.decisionPoint, t.adapter)
      .where(sql`${t.referralId} IS NULL AND ${t.resolvedAt} IS NULL`),
  ],
)

export const affiliateSchema = {
  referralLinksTable,
  referralsTable,
  affiliateEntriesTable,
  affiliateEnrollmentsTable,
  affiliatePayoutsTable,
  fraudEventsTable,
}

export type AffiliateSchema = typeof affiliateSchema

export type ReferralLink = typeof referralLinksTable.$inferSelect
export type NewReferralLink = typeof referralLinksTable.$inferInsert
export type Referral = typeof referralsTable.$inferSelect
export type NewReferral = typeof referralsTable.$inferInsert
export type AffiliateEnrollment = typeof affiliateEnrollmentsTable.$inferSelect
export type NewAffiliateEnrollment = typeof affiliateEnrollmentsTable.$inferInsert
export type AffiliatePayout = typeof affiliatePayoutsTable.$inferSelect
export type NewAffiliatePayout = typeof affiliatePayoutsTable.$inferInsert
export type FraudEvent = typeof fraudEventsTable.$inferSelect
export type NewFraudEvent = typeof fraudEventsTable.$inferInsert