/**
 * 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,
  uuid,
  text,
  boolean,
  integer,
  bigint,
  jsonb,
  timestamp,
  index,
  uniqueIndex,
  unique,
  primaryKey,
  check,
} from 'drizzle-orm/pg-core';
import { relations, sql } from 'drizzle-orm';
import { type PromoFunder } from '@/lib/enums/promo-funder';
import { type PromoKind } from '@/lib/enums/promo-kind';
import { type PromoStatus } from '@/lib/enums/promo-status';

import { users, vendors } from './core.js';
import { type chatMessages } from './realtime.js';
import { orderLine } from '../../../../node_modules/@platform-modules/commerce-orders/dist/index.js';

// ─── Payouts ──────────────────────────────────────────────────────────────────

/** Monthly vendor settlement records — tracks payout status per vendor+month. */
export const payouts = pgTable(
  'payouts',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    vendorId: uuid('vendor_id')
      .notNull()
      .references(() => vendors.id),
    periodMonth: text('period_month').notNull(),
    amountAgorot: integer('amount_agorot').notNull(),
    platformFeeAgorot: integer('platform_fee_agorot').notNull(),
    status: text('status').notNull().default('pending'),
    markedPaidAt: timestamp('marked_paid_at', { withTimezone: true }),
    markedPaidBy: text('marked_paid_by'),
    notes: text('notes'),
    stripePayoutId: text('stripe_payout_id'),
    stripeTransferId: text('stripe_transfer_id'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [unique('payouts_vendor_period_uniq').on(t.vendorId, t.periodMonth)],
);
export type Payout = typeof payouts.$inferSelect;
export type NewPayout = typeof payouts.$inferInsert;
export type ChatMessageInsert = typeof chatMessages.$inferInsert;

// ─── Promotions ───────────────────────────────────────────────────────────────

export const promo = pgTable(
  'promo',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    code: text('code').notNull(),
    kind: text('kind').notNull().$type<PromoKind>(),
    valueBps: integer('value_bps'),
    valueAmount: bigint('value_amount', { mode: 'number' }),
    currency: text('currency'),
    maxDiscountAmount: bigint('max_discount_amount', { mode: 'bigint' }),
    bogoBuyQty: integer('bogo_buy_qty'),
    bogoGetQty: integer('bogo_get_qty'),
    scope: jsonb('scope').$type<Record<string, unknown>>().notNull(),
    eligibility: jsonb('eligibility').$type<Record<string, unknown>>().notNull(),
    funder: text('funder').notNull().$type<PromoFunder>(),
    maxUses: integer('max_uses'),
    perUserCap: integer('per_user_cap'),
    uses: integer('uses').notNull().default(0),
    startsAt: timestamp('starts_at', { withTimezone: true }),
    endsAt: timestamp('ends_at', { withTimezone: true }),
    minOrderAmount: bigint('min_order_amount', { mode: 'bigint' }),
    maxOrderAmount: bigint('max_order_amount', { mode: 'bigint' }),
    active: boolean('active').notNull().default(true),
    vendorId: text('vendor_id'),
    authorUserId: uuid('author_user_id').references(() => users.id),
    status: text('status').notNull().default('active').$type<PromoStatus>(),
    validFrom: timestamp('valid_from', { withTimezone: true }),
    validUntil: timestamp('valid_until', { withTimezone: true }),
    minSubtotal: integer('min_subtotal'),
    maxSubtotal: integer('max_subtotal'),
    totalCap: integer('total_cap'),
    redemptionCount: integer('redemption_count').notNull().default(0),
    rulesJson: jsonb('rules_json').notNull().default({}),
    description: text('description'),
    maxCapAmount: integer('max_cap_amount'),
    bogoBuy: integer('bogo_buy'),
    bogoGetFree: integer('bogo_get_free'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    uniqueIndex('promo_code_uniq').on(sql`upper(${t.code})`),
    index('promo_vendor_idx')
      .on(t.vendorId)
      .where(sql`${t.vendorId} IS NOT NULL`),
    index('promo_active_window_idx')
      .on(t.active, t.startsAt, t.endsAt)
      .where(sql`${t.active} = true`),
    check('promo_max_uses_chk', sql`${t.maxUses} IS NULL OR ${t.maxUses} > 0`),
    check('promo_per_user_cap_chk', sql`${t.perUserCap} IS NULL OR ${t.perUserCap} > 0`),
    check('promo_value_amount_chk', sql`${t.valueAmount} IS NULL OR ${t.valueAmount} > 0`),
    check(
      'promo_value_bps_chk',
      sql`${t.valueBps} IS NULL OR (${t.valueBps} > 0 AND ${t.valueBps} <= 10000)`,
    ),
  ],
);

export const promoCodes = promo;

export type PromoRow = typeof promo.$inferSelect;
export type NewPromo = typeof promo.$inferInsert;
export type PromoCodeRow = typeof promo.$inferSelect;
export type NewPromoCode = typeof promo.$inferInsert;

export const promoUserUsage = pgTable(
  'promo_user_usage',
  {
    promoId: uuid('promo_id')
      .notNull()
      .references(() => promo.id, { onDelete: 'cascade' }),
    userId: text('user_id').notNull(),
    uses: integer('uses').notNull().default(0),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [primaryKey({ columns: [t.promoId, t.userId] })],
);

export const promoAllowlist = pgTable(
  'promo_allowlist',
  {
    promoId: uuid('promo_id')
      .notNull()
      .references(() => promo.id, { onDelete: 'cascade' }),
    userId: uuid('user_id')
      .notNull()
      .references(() => users.id, { onDelete: 'cascade' }),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    primaryKey({ columns: [t.promoId, t.userId] }),
    index('promo_allowlist_user_idx').on(t.userId),
  ],
);

export const promoRedemptions = pgTable(
  'promo_redemptions',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    promoCodeId: uuid('promo_code_id')
      .notNull()
      .references(() => promoCodes.id),
    orderLineId: uuid('order_line_id')
      .notNull()
      .references(() => orderLine.id),
    userId: uuid('user_id')
      .notNull()
      .references(() => users.id),
    vendorId: uuid('vendor_id').references(() => vendors.id),
    funder: text('funder').notNull().$type<PromoFunder>(),
    discountAmount: integer('discount_amount').notNull(),
    appliedToLines: jsonb('applied_to_lines').notNull(),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    unique('promo_redemptions_purchase_id_promo_code_id_key').on(t.orderLineId, t.promoCodeId),
    index('promo_redemptions_code_idx').on(t.promoCodeId),
    index('promo_redemptions_user_code_idx').on(t.userId, t.promoCodeId),
    index('promo_redemptions_vendor_idx').on(t.vendorId, t.createdAt),
  ],
);

export const promoReservationStatus = ['pending', 'finalized', 'failed'] as const;
export type PromoReservationStatus = (typeof promoReservationStatus)[number];

export const promoReservations = pgTable(
  'promo_reservations',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    promoCodeId: uuid('promo_code_id')
      .notNull()
      .references(() => promo.id),
    userId: uuid('user_id')
      .notNull()
      .references(() => users.id),
    purchaseId: uuid('purchase_id')
      .notNull()
      .references(() => orderLine.id),
    orderLineId: uuid('order_line_id')
      .notNull()
      .references(() => orderLine.id),
    vendorId: uuid('vendor_id')
      .notNull()
      .references(() => vendors.id),
    checkoutScope: text('checkout_scope').notNull(),
    currency: text('currency').notNull(),
    snapshot: jsonb('snapshot').notNull(),
    quotaOwner: boolean('quota_owner').notNull().default(false),
    discountAgorot: integer('discount_agorot').notNull(),
    funder: text('funder').notNull().$type<PromoFunder>(),
    status: text('status').notNull().default('pending').$type<PromoReservationStatus>(),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    check('promo_reservations_status_ck', sql`${t.status} IN ('pending', 'finalized', 'failed')`),
    check('promo_reservations_discount_positive_ck', sql`${t.discountAgorot} > 0`),
    uniqueIndex('promo_reservations_active_uniq')
      .on(t.promoCodeId, t.userId, t.purchaseId)
      .where(sql`${t.status} IN ('pending', 'finalized')`),
    index('promo_reservations_promo_status_idx').on(t.promoCodeId, t.status),
    index('promo_reservations_user_idx').on(t.userId, t.createdAt),
    index('promo_reservations_purchase_idx').on(t.purchaseId),
    index('promo_reservations_checkout_idx').on(t.checkoutScope),
  ],
);

export type PromoRedemptionRow = typeof promoRedemptions.$inferSelect;
export type NewPromoRedemption = typeof promoRedemptions.$inferInsert;

export const promoRelations = relations(promo, ({ one, many }) => ({
  author: one(users, { fields: [promo.authorUserId], references: [users.id] }),
  redemptions: many(promoRedemptions),
  allowlist: many(promoAllowlist),
}));

export const promoCodesRelations = promoRelations;

export const promoRedemptionsRelations = relations(promoRedemptions, ({ one }) => ({
  code: one(promo, {
    fields: [promoRedemptions.promoCodeId],
    references: [promo.id],
  }),
  orderLine: one(orderLine, {
    fields: [promoRedemptions.orderLineId],
    references: [orderLine.id],
  }),
  user: one(users, {
    fields: [promoRedemptions.userId],
    references: [users.id],
  }),
  vendor: one(vendors, {
    fields: [promoRedemptions.vendorId],
    references: [vendors.id],
  }),
}));

export const promoAllowlistRelations = relations(promoAllowlist, ({ one }) => ({
  promo: one(promo, {
    fields: [promoAllowlist.promoId],
    references: [promo.id],
  }),
  user: one(users, { fields: [promoAllowlist.userId], references: [users.id] }),
}));

export const promoUserUsageRelations = relations(promoUserUsage, ({ one }) => ({
  promo: one(promo, {
    fields: [promoUserUsage.promoId],
    references: [promo.id],
  }),
}));