/**
 * 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,
  numeric,
  timestamp,
  index,
  check,
} from 'drizzle-orm/pg-core';
import { sql } from 'drizzle-orm';

import {
  groupDealStateEnum,
  reservationStatusEnum,
  fillRuleEnum,
  cancellationPolicyEnum,
} from './enums.js';
import { users, paymentMethods } from './core.js';
import { dealSkus, deals } from './deal-variants.js';
import { orderLine } from '../../../../node_modules/@platform-modules/commerce-orders/dist/index.js';

// ─── Group Deal Tables ────────────────────────────────────────────────────────

/** 1:1 extension of deals for GROUP-type specific fields. */
export const groupDeals = pgTable(
  'group_deals',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    dealId: uuid('deal_id')
      .notNull()
      .unique()
      .references(() => deals.id, { onDelete: 'cascade' }),
    fillRule: fillRuleEnum('fill_rule').notNull(),
    minGroupSize: integer('min_group_size').notNull(),
    maxGroupSize: integer('max_group_size').notNull(),
    perCustomerLimit: integer('per_customer_limit').notNull().default(1),
    cancellationPolicy: cancellationPolicyEnum('cancellation_policy')
      .notNull()
      .default('LEGAL_ONLY'),
    cancellationWindowHours: integer('cancellation_window_hours'),
    tieredPricingEnabled: boolean('tiered_pricing_enabled').notNull().default(false),
    earlyBirdEnabled: boolean('early_bird_enabled').notNull().default(false),
    earlyBirdSlots: integer('early_bird_slots'),
    earlyBirdDiscountPercent: integer('early_bird_discount_percent'),
    socialSharingEnabled: boolean('social_sharing_enabled').notNull().default(false),
    socialSharingReward: text('social_sharing_reward'),
    bulkPickupEnabled: boolean('bulk_pickup_enabled').notNull().default(false),
    bulkPickupDetails: text('bulk_pickup_details'),
    groupState: groupDealStateEnum('group_state').notNull().default('COLLECTING'),
    currentReservationCount: integer('current_reservation_count').notNull().default(0),
    thresholdMetAt: timestamp('threshold_met_at', { withTimezone: true }),
    extendedDeadline: timestamp('extended_deadline', { withTimezone: true }),
    extensionCount: integer('extension_count').notNull().default(0),
    partialDecisionDeadline: timestamp('partial_decision_deadline', { withTimezone: true }),
    vendorHonoredAt: timestamp('vendor_honored_at', { withTimezone: true }),
    executedAt: timestamp('executed_at', { withTimezone: true }),
    failedAt: timestamp('failed_at', { withTimezone: true }),
    sourceGroupDealId: uuid('source_group_deal_id'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    check('group_deals_min_max', sql`${t.minGroupSize} <= ${t.maxGroupSize}`),
    check('group_deals_min_positive', sql`${t.minGroupSize} >= 2`),
    index('group_deals_state_idx').on(t.groupState),
    index('group_deals_deal_id_idx').on(t.dealId),
  ],
);

/** Optional tiered pricing bands for a group deal. */
export const groupTiers = pgTable(
  'group_tiers',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    groupDealId: uuid('group_deal_id')
      .notNull()
      .references(() => groupDeals.id, { onDelete: 'cascade' }),
    minParticipants: integer('min_participants').notNull(),
    pricePerUnit: numeric('price_per_unit', { precision: 10, scale: 2 }).notNull(),
    discountPercent: integer('discount_percent').notNull(),
    sortOrder: integer('sort_order').notNull().default(0),
  },
  (t) => [
    index('group_tiers_group_deal_idx').on(t.groupDealId),
    check('group_tiers_discount_range', sql`${t.discountPercent} BETWEEN 0 AND 100`),
  ],
);

/** Optional quantity-break pricing bands for a single SKU. Always vendor-funded. */
export const skuQtyTiers = pgTable(
  'sku_qty_tiers',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    dealSkuId: uuid('deal_sku_id')
      .notNull()
      .references(() => dealSkus.id, { onDelete: 'cascade' }),
    minQty: integer('min_qty').notNull(),
    discountPercent: integer('discount_percent').notNull(),
    sortOrder: integer('sort_order').notNull().default(0),
  },
  (t) => [
    index('sku_qty_tiers_deal_sku_idx').on(t.dealSkuId),
    check('sku_qty_tiers_min_qty', sql`${t.minQty} >= 2`),
    check('sku_qty_tiers_discount_range', sql`${t.discountPercent} BETWEEN 1 AND 100`),
  ],
);

/** Individual participant reservations for a group deal. */
export const groupReservations = pgTable(
  'group_reservations',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    groupDealId: uuid('group_deal_id')
      .notNull()
      .references(() => groupDeals.id),
    dealId: uuid('deal_id')
      .notNull()
      .references(() => deals.id),
    userId: uuid('user_id').references(() => users.id),
    guestEmail: text('guest_email'),
    guestPhone: text('guest_phone'),
    quantity: integer('quantity').notNull().default(1),
    unitPrice: numeric('unit_price', { precision: 10, scale: 2 }).notNull(),
    totalAmount: numeric('total_amount', { precision: 10, scale: 2 }).notNull(),
    commissionAmount: numeric('commission_amount', { precision: 10, scale: 2 }).notNull(),
    vendorAmount: numeric('vendor_amount', { precision: 10, scale: 2 }).notNull(),
    status: reservationStatusEnum('status').notNull().default('HELD'),
    paymentMethodId: uuid('payment_method_id').references(() => paymentMethods.id),
    providerAuthorizationId: text('provider_authorization_id'),
    providerTransactionId: text('provider_transaction_id'),
    providerCardToken: text('provider_card_token'),
    holdExpiresAt: timestamp('hold_expires_at', { withTimezone: true }),
    holdRefreshedAt: timestamp('hold_refreshed_at', { withTimezone: true }),
    isEarlyBird: boolean('is_early_bird').notNull().default(false),
    orderLineId: uuid('order_line_id').references(() => orderLine.id),
    cancelledAt: timestamp('cancelled_at', { withTimezone: true }),
    cancellationReason: text('cancellation_reason'),
    idempotencyKey: text('idempotency_key').notNull().unique(),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),

    // Stripe holds: providerAuthorizationId = PaymentIntent.id (manual capture,
    // status=requires_capture). On capture, providerTransactionId = same PI.id
    // for traceability. holdExpiresAt = +7d per Stripe manual-capture window.
    // ── Mock payment provider (dev/test only) ─────────────────────────────
    mockScenario: text('mock_scenario'),
  },
  (t) => [
    index('group_reservations_group_deal_idx').on(t.groupDealId),
    index('group_reservations_user_idx').on(t.userId),
    index('group_reservations_status_idx').on(t.status),
  ],
);

/** Waitlist entries for sold-out or capacity-limited group deals. */
export const groupWaitlist = pgTable(
  'group_waitlist',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    groupDealId: uuid('group_deal_id')
      .notNull()
      .references(() => groupDeals.id, { onDelete: 'cascade' }),
    userId: uuid('user_id').references(() => users.id),
    guestEmail: text('guest_email'),
    guestPhone: text('guest_phone'),
    quantity: integer('quantity').notNull().default(1),
    paymentMethodId: uuid('payment_method_id').references(() => paymentMethods.id),
    position: integer('position').notNull(),
    promotedAt: timestamp('promoted_at', { withTimezone: true }),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [index('group_waitlist_group_deal_idx').on(t.groupDealId, t.position)],
);