/**
 * 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,
  timestamp,
  index,
  uniqueIndex,
  check,
  smallint,
} from 'drizzle-orm/pg-core';
import { sql } from 'drizzle-orm';
import type { ReferralId } from '@/server/platform-seams/ids.js';

import { users } from './core.js';
import { promo } from './promotions.js';

// ─── Referral / Affiliate ─────────────────────────────────────────────────────

export const referralKindEnum = pgEnum('referral_kind', ['referral', 'affiliate']);
export const referralStatusEnum = pgEnum('referral_status', [
  'pending',
  'qualified',
  'rejected',
  'quarantined',
]);
export const creditEntryTypeEnum = pgEnum('credit_entry_type', [
  'referral_reward',
  'affiliate_commission',
  'redemption',
  'refund_clawback',
  'adjustment',
  'return_shipping_reimbursement',
]);
export const kycStatusEnum = pgEnum('kyc_status', ['none', 'pending', 'verified', 'rejected']);

export const referralLinks = pgTable(
  'referral_links',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    ownerUserId: uuid('owner_user_id')
      .notNull()
      .references(() => users.id),
    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 referrals = pgTable(
  'referrals',
  {
    id: uuid('id').$type<ReferralId>().primaryKey().defaultRandom(),
    referrerUserId: uuid('referrer_user_id')
      .notNull()
      .references(() => users.id),
    refereeUserId: uuid('referee_user_id')
      .notNull()
      .unique()
      .references(() => users.id),
    linkId: uuid('link_id')
      .notNull()
      .references(() => referralLinks.id),
    kind: referralKindEnum('kind').notNull(),
    status: referralStatusEnum('status').notNull().default('pending'),
    qualifiedAt: timestamp('qualified_at', { withTimezone: true }),
    /** Timestamp of the last referral click before signup — commission window origin. */
    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').references(() => promo.id),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    /** Device/browser fingerprint of the referrer at link-click time. */
    visitorIdReferrer: text('visitor_id_referrer'),
    /** Device/browser fingerprint of the referee at signup time. */
    visitorIdReferee: text('visitor_id_referee'),
    /** SHA-256 hash of referee IP at signup (privacy-safe dedup). */
    ipHashReferee: text('ip_hash_referee'),
    /** Set when referral is moved to quarantined status by fraud engine. */
    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 creditLedger = pgTable(
  'credit_ledger',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    userId: uuid('user_id')
      .notNull()
      .references(() => users.id),
    amountAgorot: bigint('amount_agorot', { mode: 'number' }).notNull(),
    entryType: creditEntryTypeEnum('entry_type').notNull(),
    sourceType: text('source_type').notNull(),
    sourceId: text('source_id').notNull(),
    referralId: uuid('referral_id')
      .$type<ReferralId | null>()
      .references(() => referrals.id),
    memo: text('memo'),
    resolvedPct: smallint('resolved_pct'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    matureAt: timestamp('mature_at', { withTimezone: true }).notNull(),
    sweptAt: timestamp('swept_at', { withTimezone: true }),
    withdrawableAt: timestamp('withdrawable_at', { withTimezone: true }),
  },
  (t) => [
    index('credit_ledger_user_idx').on(t.userId),
    uniqueIndex('credit_ledger_idem_uq').on(t.entryType, t.sourceType, t.sourceId),
  ],
);

export const walletBalances = pgTable(
  'wallet_balances',
  {
    userId: uuid('user_id')
      .primaryKey()
      .references(() => users.id),
    balanceAgorot: bigint('balance_agorot', { mode: 'number' }).notNull().default(0),
    lifetimeEarnedAgorot: bigint('lifetime_earned_agorot', { mode: 'number' }).notNull().default(0),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
    pendingAgorot: bigint('pending_agorot', { mode: 'number' }).notNull().default(0),
    maturedAgorot: bigint('matured_agorot', { mode: 'number' }).notNull().default(0),
    withdrawableAgorot: bigint('withdrawable_agorot', { mode: 'number' }).notNull().default(0),
  },
  (t) => [check('wallet_balances_pending_agorot_nonneg', sql`${t.pendingAgorot} >= 0`)],
);

export const taxDocuments = pgTable(
  'tax_documents',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    orderLineId: uuid('order_line_id').notNull(),
    side: text('side').notNull(), // 'vendor' | 'platform'
    providerTaxDocId: text('provider_tax_doc_id').notNull(),
    idempotencyKey: text('idempotency_key').notNull(), // taxdoc:${orderLineId}:${side}
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [uniqueIndex('tax_documents_purchase_side_uq').on(t.orderLineId, t.side)],
);

export const kycVerifications = pgTable('kyc_verifications', {
  id: uuid('id').primaryKey().defaultRandom(),
  userId: uuid('user_id')
    .notNull()
    .unique()
    .references(() => users.id),
  status: kycStatusEnum('status').notNull().default('none'),
  submittedAt: timestamp('submitted_at', { withTimezone: true }),
  verifiedAt: timestamp('verified_at', { withTimezone: true }),
});