/**
 * Drizzle ORM schema - Multideal (מולטידיל) database
 *
 * All 13 FDS data models + supporting tables.
 * snake_case column names (enforced by drizzle.config.ts casing: 'snake_case').
 * Every timestamp uses { withTimezone: true }.
 * Every enum uses pgEnum.
 *
 * PII columns (phone, email, address, token) are stored as encrypted text via pgcrypto.
 * Blind-index columns provide deterministic lookup without decryption.
 */

import {
  pgTable,
  pgEnum,
  uuid,
  text,
  boolean,
  integer,
  bigint,
  jsonb,
  timestamp,
  date,
  index,
  uniqueIndex,
  check,
  primaryKey,
  smallint,
} from 'drizzle-orm/pg-core';
import { sql } from 'drizzle-orm';
import { ledgerEntries } from '@platform-modules/ledger';
import { AFFILIATE_STRIPE_STATUS } from '@/lib/enums/affiliate-stripe-status';
import type { PayoutId, ReferralId } from '@/server/platform-seams/ids.js';

import { users } from './core.js';
import { deals } from './deal-variants.js';
import { referralLinks, referrals } from './referrals.js';

// ─── Affiliate Enrollments ───────────────────────────────────────────────────

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

export const affiliateStripeStatusEnum = pgEnum('affiliate_stripe_status', AFFILIATE_STRIPE_STATUS);

export const affiliateEnrollments = pgTable(
  'affiliate_enrollments',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    userId: uuid('user_id')
      .notNull()
      .unique()
      .references(() => users.id, { onDelete: 'restrict' }),
    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(),
    notes: text('notes'),
  },
  (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 type AffiliateEnrollment = typeof affiliateEnrollments.$inferSelect;
export type NewAffiliateEnrollment = typeof affiliateEnrollments.$inferInsert;

// ─── Referral Settings (single-row config table) ─────────────────────────────

export const referralSettings = pgTable(
  'referral_settings',
  {
    id: integer('id').primaryKey(),
    affiliatePct: integer('affiliate_pct').notNull().default(30),
    affiliateWindowDays: integer('affiliate_window_days').notNull().default(90),
    affiliateMaxOrders: integer('affiliate_max_orders').notNull().default(50),
    cookieDays: integer('cookie_days').notNull().default(14),
    rewardAgorot: integer('reward_agorot').notNull().default(2000),
    refereeDiscountAgorot: integer('referee_discount_agorot').notNull().default(2000),
    holdDays: integer('hold_days').notNull().default(30),
    withdrawalMinAgorot: integer('withdrawal_min_agorot').notNull().default(30000),
    autoApproveLifetimeAgorot: integer('auto_approve_lifetime_agorot').notNull().default(50000),
    brandKeywordBlocklist: text('brand_keyword_blocklist')
      .array()
      .notNull()
      .default(sql`ARRAY['multideal','מולטידיל','multi.deal']::text[]`),
    disallowedRefererHosts: text('disallowed_referer_hosts')
      .array()
      .notNull()
      .default(sql`'{}'::text[]`),
    tosVersion: text('tos_version').notNull().default('v1-2026-05-28'),
    tier1Pct: smallint('tier1_pct').notNull().default(3),
    tier2Pct: smallint('tier2_pct').notNull().default(4),
    tier3Pct: smallint('tier3_pct').notNull().default(5),
    tier2MinSales: integer('tier2_min_sales').notNull().default(20),
    tier3MinSales: integer('tier3_min_sales').notNull().default(50),
    referralPct: smallint('referral_pct').notNull().default(2),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
    updatedByUserId: uuid('updated_by_user_id').references(() => users.id),
    /** JSON config for fraud-detection adapters (velocity, device, ml thresholds). */
    fraudConfig: jsonb('fraud_config').notNull().default({}),
    /** Days a dispute window stays open for quarantined referrals. */
    disputeWindowDays: integer('dispute_window_days').notNull().default(120),
    /** Reserve percentage held back from payouts during dispute window (basis points 0-100). */
    reservePct: integer('reserve_pct').notNull().default(0),
    /** Days after which reserve is auto-released. */
    reserveReleaseDays: integer('reserve_release_days').notNull().default(120),
  },
  (t) => [
    // PLATFORM_FEE_PCT=10 — referral reward must stay strictly below fee so margin stays positive.
    check('referral_pct_below_fee', sql`${t.referralPct} >= 1 AND ${t.referralPct} < 10`),
  ],
);

export type ReferralSettingsRow = typeof referralSettings.$inferSelect;

// ─── Affiliate Payouts ────────────────────────────────────────────────────────

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

export const shareChannelEnum = pgEnum('share_channel', [
  'whatsapp',
  'instagram',
  'facebook',
  'twitter',
  'telegram',
  'copy',
  'email',
  'sms',
  'other',
  'direct',
]);

export const affiliatePayouts = pgTable(
  'affiliate_payouts',
  {
    id: uuid('id').$type<PayoutId>().primaryKey().defaultRandom(),
    userId: uuid('user_id')
      .notNull()
      .references(() => users.id),
    enrollmentId: uuid('enrollment_id')
      .notNull()
      .references(() => affiliateEnrollments.id),
    amountAgorot: bigint('amount_agorot', { mode: 'number' }).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').references(() => users.id),
    processingAt: timestamp('processing_at', { withTimezone: true }),
    paidAt: timestamp('paid_at', { withTimezone: true }),
    settlementRail: text('settlement_rail'),
    settlementReference: text('settlement_reference'),
    settledAt: timestamp('settled_at', { withTimezone: true }),
    settledByUserId: uuid('settled_by_user_id').references(() => users.id),
    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(() => ledgerEntries.id),
  },
  (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')`),
    uniqueIndex('affiliate_payouts_settlement_evidence_uq').on(
      t.settlementRail,
      t.settlementReference,
    ),
  ],
);

export type AffiliatePayout = typeof affiliatePayouts.$inferSelect;
export type NewAffiliatePayout = typeof affiliatePayouts.$inferInsert;

// ─── Affiliate Admin Actions (audit log) ─────────────────────────────────────

export const affiliateAdminActions = pgTable(
  'affiliate_admin_actions',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    ts: timestamp('ts', { withTimezone: true }).notNull().defaultNow(),
    adminUserId: uuid('admin_user_id').references(() => users.id),
    targetUserId: uuid('target_user_id').references(() => users.id),
    action: text('action').notNull(),
    payload: jsonb('payload').notNull(),
    reason: text('reason'),
  },
  (t) => [index('affiliate_admin_actions_target_idx').on(t.targetUserId, t.ts)],
);

export type AffiliateAdminAction = typeof affiliateAdminActions.$inferSelect;

// ─── Fraud Events ─────────────────────────────────────────────────────────────

/** Immutable audit log of every fraud-engine decision point. */
export const fraudEvents = pgTable(
  'fraud_events',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    userId: uuid('user_id')
      .notNull()
      .references(() => users.id),
    referralId: uuid('referral_id')
      .$type<ReferralId | null>()
      .references(() => referrals.id),
    /** Which stage in the pipeline fired (e.g. "referral.signup", "referral.qualify"). */
    decisionPoint: text('decision_point').notNull(),
    /** Adapter that produced this event (e.g. "velocity", "device", "ip", "ml"). */
    adapter: text('adapter').notNull(),
    /** Action taken: "allow" | "flag" | "quarantine" | "reject". */
    action: text('action').notNull(),
    /** Rule/signal codes that contributed to this decision. */
    codes: text('codes')
      .array()
      .notNull()
      .default(sql`'{}'::text[]`),
    /** Arbitrary adapter-specific detail payload. */
    detail: jsonb('detail'),
    resolvedBy: uuid('resolved_by').references(() => users.id),
    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 type FraudEvent = typeof fraudEvents.$inferSelect;
export type NewFraudEvent = typeof fraudEvents.$inferInsert;

// ─── Referral Link Stats Daily (rollup from AE) ───────────────────────────────

export const referralLinkStatsDaily = pgTable(
  'referral_link_stats_daily',
  {
    linkId: uuid('link_id')
      .notNull()
      .references(() => referralLinks.id),
    day: date('day').notNull(),
    clicks: integer('clicks').notNull().default(0),
    clicksSuspicious: integer('clicks_suspicious').notNull().default(0),
    signups: integer('signups').notNull().default(0),
    purchases: integer('purchases').notNull().default(0),
    commissionAgorot: bigint('commission_agorot', { mode: 'number' }).notNull().default(0),
    rewardAgorot: bigint('reward_agorot', { mode: 'number' }).notNull().default(0),
  },
  (t) => [
    primaryKey({ columns: [t.linkId, t.day] }),
    index('referral_link_stats_daily_day_idx').on(t.day),
  ],
);

export type ReferralLinkStatsDaily = typeof referralLinkStatsDaily.$inferSelect;

// ─── Share Links ──────────────────────────────────────────────────────────────

export const shareLinks = pgTable('share_links', {
  id: uuid('id').primaryKey().defaultRandom(),
  slug: text('slug').notNull().unique(),
  targetUrl: text('target_url').notNull(),
  dealId: uuid('deal_id').references(() => deals.id, { onDelete: 'set null' }),
  ownerUserId: uuid('owner_user_id').references(() => users.id, { onDelete: 'set null' }),
  channel: shareChannelEnum('channel').notNull().default('other'),
  campaignName: text('campaign_name'),
  utmSource: text('utm_source').notNull(),
  utmMedium: text('utm_medium').notNull().default('social'),
  utmCampaign: text('utm_campaign').notNull(),
  isActive: boolean('is_active').notNull().default(true),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  expiresAt: timestamp('expires_at', { withTimezone: true }),
});

export const shareLinkStatsDaily = pgTable(
  'share_link_stats_daily',
  {
    linkId: uuid('link_id')
      .notNull()
      .references(() => shareLinks.id, { onDelete: 'cascade' }),
    day: date('day').notNull(),
    clicks: integer('clicks').notNull().default(0),
    clicksSuspicious: integer('clicks_suspicious').notNull().default(0),
    dealId: uuid('deal_id'),
    channel: shareChannelEnum('channel'),
  },
  (t) => [primaryKey({ columns: [t.linkId, t.day] })],
);

export const shareAudienceDaily = pgTable(
  'share_audience_daily',
  {
    day: date('day').notNull(),
    device: text('device').notNull().default(''),
    country: text('country').notNull().default(''),
    clicks: integer('clicks').notNull().default(0),
    clicksSuspicious: integer('clicks_suspicious').notNull().default(0),
  },
  (t) => [
    primaryKey({ columns: [t.day, t.device, t.country] }),
    index('share_audience_daily_day_idx').on(t.day),
  ],
);

export type ShareAudienceDaily = typeof shareAudienceDaily.$inferSelect;

export const marketingTriggerState = pgTable(
  'marketing_trigger_state',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    userId: uuid('user_id')
      .notNull()
      .references(() => users.id, { onDelete: 'cascade' }),
    triggerKey: text('trigger_key').notNull(),
    firedAt: timestamp('fired_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [uniqueIndex('mts_user_trigger_uidx').on(t.userId, t.triggerKey)],
);

export const syncCursors = pgTable('sync_cursors', {
  key: text('key').primaryKey(),
  value: text('value').notNull(),
  updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
});

export const ledgerReconciliationRepairs = pgTable(
  'ledger_reconciliation_repairs',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    userId: uuid('user_id').notNull(),
    expectedBalanceAgorot: bigint('expected_balance_agorot', { mode: 'number' }).notNull(),
    actualBalanceAgorot: bigint('actual_balance_agorot', { mode: 'number' }).notNull(),
    balanceDeltaAgorot: bigint('balance_delta_agorot', { mode: 'number' }).notNull(),
    expectedLifetimeAgorot: bigint('expected_lifetime_agorot', { mode: 'number' }).notNull(),
    actualLifetimeAgorot: bigint('actual_lifetime_agorot', { mode: 'number' }).notNull(),
    repairedColumns: text('repaired_columns').notNull(),
    recurrence: boolean('recurrence').notNull().default(false),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    index('ledger_recon_repairs_user_idx').on(t.userId),
    index('ledger_recon_repairs_created_idx').on(t.createdAt),
  ],
);

export const cronCursors = pgTable('cron_cursors', {
  jobKey: text('job_key').primaryKey(),
  lastProcessedAt: timestamp('last_processed_at', { withTimezone: true }).notNull(),
  updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
});

export type NewShareAudienceDaily = typeof shareAudienceDaily.$inferInsert;

export { ledgerEntries };
export {
  ledgerEntryVesting,
  walletVesting,
} from '../../../../node_modules/@platform-modules/ledger/dist/vesting.js';
export {
  affiliateEntriesTable,
  affiliateEntryTypeEnum,
} from '../../../../node_modules/@platform-modules/affiliate/dist/schema.js';