/**
 * 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,
  numeric,
  jsonb,
  timestamp,
  index,
  uniqueIndex,
  primaryKey,
} from 'drizzle-orm/pg-core';
import { sql } from 'drizzle-orm';
import { SHIPPING_MODE } from '@/lib/enums/shipping-mode';
import { CARRIER } from '@/lib/enums/carrier';
import { SHIPMENT_STATUS } from '@/lib/enums/shipment-status';
import { INVOICE_PROVIDER } from '@/lib/enums/invoice-provider';
import type { StockReservationId } from '@/server/platform-seams/ids.js';

import { users, vendors, vendorAddresses } from './core.js';
import { dealSkus, deals } from './deal-variants.js';
import { orderLine } from '../../../../node_modules/@platform-modules/commerce-orders/dist/index.js';

// ─── ITEM deal type & universal settlement-hold ────────────────────────────

export const shippingModeEnum = pgEnum('shipping_mode', SHIPPING_MODE);

export const shipmentStatusEnum = pgEnum('shipment_status', SHIPMENT_STATUS);

export const carrierEnum = pgEnum('carrier', CARRIER);

export const invoiceProviderEnum = pgEnum('invoice_provider', INVOICE_PROVIDER);

export const vendorPayoutReleaseStatusEnum = pgEnum('vendor_payout_release_status', [
  'held',
  'enqueued',
  'releasing',
  'released',
  'refunded',
  'cancelled',
]);

// ── 1.1 Deal-level ITEM config ──────────────────────────────────────────────

export const dealItemConfig = pgTable('deal_item_config', {
  dealId: uuid('deal_id')
    .primaryKey()
    .references(() => deals.id, { onDelete: 'cascade' }),
  handleSlaBusinessDays: integer('handle_sla_business_days').notNull(),
  shippingMode: shippingModeEnum('shipping_mode').notNull(),
  shippingFlatAgorot: integer('shipping_flat_agorot'),
  shippingFreeThresholdAgorot: integer('shipping_free_threshold_agorot'),
  pickupEnabled: boolean('pickup_enabled').notNull().default(false),
  pickupAddressId: uuid('pickup_address_id').references(() => vendorAddresses.id),
  pickupHoursJson: jsonb('pickup_hours_json'),
  pickupInstructions: text('pickup_instructions'),
  returnWindowDays: integer('return_window_days').notNull(),
  isReturnable: boolean('is_returnable').notNull().default(true),
  insuranceRequired: boolean('insurance_required').notNull().default(false),
  preferredCarrier: text('preferred_carrier'),
  createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
  updatedAt: timestamp('updated_at', { withTimezone: true }).defaultNow().notNull(),
});

// ── 1.2 Buyer shipping addresses ────────────────────────────────────────────

export const shippingAddresses = pgTable(
  'shipping_addresses',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    userId: uuid('user_id')
      .notNull()
      .references(() => users.id, { onDelete: 'cascade' }),
    recipientName: text('recipient_name').notNull(),
    recipientPhone: text('recipient_phone').notNull(),
    cityCode: text('city_code').notNull(),
    cityName: text('city_name').notNull(),
    streetCode: text('street_code'),
    streetName: text('street_name').notNull(),
    houseNumber: text('house_number').notNull(),
    apt: text('apt'),
    entrance: text('entrance'),
    floor: text('floor'),
    zip: text('zip').notNull(),
    zipValidated: boolean('zip_validated').notNull().default(false),
    notes: text('notes'),
    lat: numeric('lat', { precision: 10, scale: 7 }),
    lng: numeric('lng', { precision: 10, scale: 7 }),
    isDefault: boolean('is_default').notNull().default(false),
    createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).defaultNow().notNull(),
  },
  (t) => [
    index('shipping_addresses_user_default_idx').on(t.userId, t.isDefault),
    index('shipping_addresses_city_idx').on(t.cityCode),
  ],
);

// ── 1.3 Shipments + events ──────────────────────────────────────────────────

export const shipments = pgTable(
  'shipments',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    vendorId: uuid('vendor_id')
      .notNull()
      .references(() => vendors.id),
    buyerId: uuid('buyer_id')
      .notNull()
      .references(() => users.id),
    shippingAddressId: uuid('shipping_address_id').references(() => shippingAddresses.id),
    pickupAddressId: uuid('pickup_address_id').references(() => vendorAddresses.id),
    status: shipmentStatusEnum('status').notNull().default('pending'),
    carrier: carrierEnum('carrier'),
    trackingNumber: text('tracking_number'),
    trackingUrl: text('tracking_url'),
    carrierLabelR2Key: text('carrier_label_r2_key'),
    packingSlipR2Key: text('packing_slip_r2_key'),
    pickupCode: text('pickup_code'),
    pickupCodeExpiresAt: timestamp('pickup_code_expires_at', {
      withTimezone: true,
    }),
    handleByAt: timestamp('handle_by_at', { withTimezone: true }).notNull(),
    shippedAt: timestamp('shipped_at', { withTimezone: true }),
    deliveredAt: timestamp('delivered_at', { withTimezone: true }),
    confirmedAt: timestamp('confirmed_at', { withTimezone: true }),
    autoConfirmAt: timestamp('auto_confirm_at', { withTimezone: true }),
    settlementAt: timestamp('settlement_at', { withTimezone: true }),
    stripeTransferId: text('stripe_transfer_id'),
    shippingChargedAgorot: integer('shipping_charged_agorot').notNull().default(0),
    insurancePurchased: boolean('insurance_purchased').notNull().default(false),
    insuranceCostAgorot: integer('insurance_cost_agorot').notNull().default(0),
    createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).defaultNow().notNull(),
  },
  (t) => [
    index('shipments_status_handle_idx').on(t.status, t.handleByAt),
    index('shipments_vendor_status_idx').on(t.vendorId, t.status),
    index('shipments_buyer_status_idx').on(t.buyerId, t.status),
    index('shipments_auto_confirm_idx').on(t.autoConfirmAt),
    index('shipments_tracking_idx').on(t.carrier, t.trackingNumber),
  ],
);

export const shipmentPurchases = pgTable(
  'shipment_purchases',
  {
    shipmentId: uuid('shipment_id')
      .notNull()
      .references(() => shipments.id, { onDelete: 'cascade' }),
    orderLineId: uuid('order_line_id')
      .notNull()
      .references(() => orderLine.id, { onDelete: 'restrict' }),
  },
  (t) => [
    primaryKey({ columns: [t.shipmentId, t.orderLineId] }),
    uniqueIndex('shipment_purchases_purchase_unique').on(t.orderLineId),
  ],
);

export const shipmentEvents = pgTable(
  'shipment_events',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    shipmentId: uuid('shipment_id')
      .notNull()
      .references(() => shipments.id, { onDelete: 'cascade' }),
    fromStatus: shipmentStatusEnum('from_status'),
    toStatus: shipmentStatusEnum('to_status').notNull(),
    source: text('source').notNull(),
    actorId: uuid('actor_id'),
    carrierRawJson: jsonb('carrier_raw_json'),
    note: text('note'),
    createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
  },
  (t) => [index('shipment_events_shipment_idx').on(t.shipmentId, t.createdAt)],
);

// ── 1.4 Stock reservations ──────────────────────────────────────────────────

export const stockReservations = pgTable(
  'stock_reservations',
  {
    id: uuid('id').$type<StockReservationId>().primaryKey().defaultRandom(),
    skuId: uuid('sku_id')
      .notNull()
      .references(() => dealSkus.id),
    userId: uuid('user_id')
      .notNull()
      .references(() => users.id),
    cartTokenHash: text('cart_token_hash'),
    qty: integer('qty').notNull(),
    paymentIntentId: text('payment_intent_id'),
    expiresAt: timestamp('expires_at', { withTimezone: true }).notNull(),
    consumedAt: timestamp('consumed_at', { withTimezone: true }),
    releasedAt: timestamp('released_at', { withTimezone: true }),
    createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
  },
  (t) => [
    index('stock_reservations_sku_active_idx')
      .on(t.skuId)
      .where(sql`consumed_at IS NULL AND released_at IS NULL`),
    index('stock_reservations_expires_idx')
      .on(t.expiresAt)
      .where(sql`consumed_at IS NULL AND released_at IS NULL`),
  ],
);

// ── 1.5 Stock watchlist ─────────────────────────────────────────────────────

export const stockWatchlist = pgTable(
  'stock_watchlist',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    userId: uuid('user_id')
      .notNull()
      .references(() => users.id, { onDelete: 'cascade' }),
    dealId: uuid('deal_id')
      .notNull()
      .references(() => deals.id, { onDelete: 'cascade' }),
    skuId: uuid('sku_id').references(() => dealSkus.id, {
      onDelete: 'cascade',
    }),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    notifiedAt: timestamp('notified_at', { withTimezone: true }),
    expiresAt: timestamp('expires_at', { withTimezone: true })
      .notNull()
      .default(sql`now() + interval '30 days'`),
  },
  (t) => [
    // Uniqueness enforced by two partial unique indexes in migration DDL.
    // Drizzle unique() on a nullable column cannot model partial-index uniqueness.
    index('stock_watchlist_deal_pending_idx').on(t.dealId),
  ],
);

// ── 1.6 Invoice provider BYOK ───────────────────────────────────────────────

export const vendorInvoiceSettings = pgTable('vendor_invoice_settings', {
  vendorId: uuid('vendor_id')
    .primaryKey()
    .references(() => vendors.id, { onDelete: 'cascade' }),
  provider: invoiceProviderEnum('provider').notNull().default('self_handled'),
  credentialsCiphertext: text('credentials_ciphertext'),
  credentialsIv: text('credentials_iv'),
  credentialsTag: text('credentials_tag'),
  settingsJson: jsonb('settings_json').notNull().default({}),
  lastTestAt: timestamp('last_test_at', { withTimezone: true }),
  lastTestOk: boolean('last_test_ok'),
  lastTestError: text('last_test_error'),
  createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
  updatedAt: timestamp('updated_at', { withTimezone: true }).defaultNow().notNull(),
});

export const invoices = pgTable(
  'invoices',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    orderLineId: uuid('order_line_id')
      .notNull()
      .references(() => orderLine.id),
    vendorId: uuid('vendor_id')
      .notNull()
      .references(() => vendors.id),
    buyerId: uuid('buyer_id')
      .notNull()
      .references(() => users.id),
    provider: invoiceProviderEnum('provider').notNull(),
    providerDocumentId: text('provider_document_id'),
    providerDocumentNumber: text('provider_document_number'),
    providerDocumentUrl: text('provider_document_url'),
    status: text('status').notNull(),
    failureReason: text('failure_reason'),
    retryCount: integer('retry_count').notNull().default(0),
    grossAgorot: integer('gross_agorot').notNull(),
    vatAgorot: integer('vat_agorot').notNull(),
    netAgorot: integer('net_agorot').notNull(),
    createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).defaultNow().notNull(),
  },
  (t) => [
    uniqueIndex('invoices_purchase_provider_uniq').on(t.orderLineId, t.provider),
    index('invoices_status_retry_idx')
      .on(t.status, t.retryCount)
      .where(sql`status = 'failed'`),
  ],
);

// ── 2.2 Vendor payout releases (settlement-hold) ───────────────────────────

export const vendorPayoutReleases = pgTable(
  'vendor_payout_releases',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    orderLineId: uuid('order_line_id')
      .notNull()
      .references(() => orderLine.id),
    vendorId: uuid('vendor_id')
      .notNull()
      .references(() => vendors.id),
    vendorAcctId: text('vendor_acct_id').notNull(),
    paymentIntentId: text('payment_intent_id').notNull(),
    chargeId: text('charge_id').notNull(),
    transferId: text('transfer_id').notNull(),
    netAmountAgorot: integer('net_amount_agorot').notNull(),
    applicationFeeAgorot: integer('application_fee_agorot').notNull(),
    status: vendorPayoutReleaseStatusEnum('status').notNull().default('held'),
    triggerReason: text('trigger_reason').notNull(),
    heldAt: timestamp('held_at', { withTimezone: true }).notNull().defaultNow(),
    releaseAt: timestamp('release_at', { withTimezone: true }).notNull(),
    enqueuedAt: timestamp('enqueued_at', { withTimezone: true }),
    claimedAt: timestamp('claimed_at', { withTimezone: true }),
    releasedAt: timestamp('released_at', { withTimezone: true }),
    cancelledAt: timestamp('cancelled_at', { withTimezone: true }),
    payoutId: text('payout_id'),
    payoutIdempotencyKey: text('payout_idempotency_key'),
    payoutIdempotencyKeyCreatedAt: timestamp('payout_idempotency_key_created_at', {
      withTimezone: true,
    }),
    payoutBatchKey: text('payout_batch_key'),
    refundId: text('refund_id'),
    claimAttempts: integer('claim_attempts').notNull().default(0),
    lastError: text('last_error'),
    metadata: jsonb('metadata').notNull().default({}),
  },
  (t) => [
    uniqueIndex('vendor_payout_releases_purchase_uniq').on(t.orderLineId),
    index('vendor_payout_releases_due_idx').on(t.status, t.releaseAt),
    index('vendor_payout_releases_vendor_idx').on(t.vendorId, t.status),
    index('vendor_payout_releases_batch_idx').on(t.payoutBatchKey),
  ],
);

// ── 2.6 Payout audit log ───────────────────────────────────────────────────

export const payoutAuditLog = pgTable(
  'payout_audit_log',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    kind: text('kind').notNull(),
    vendorId: uuid('vendor_id'),
    stripeBalanceAgorot: integer('stripe_balance_agorot'),
    heldLiabilityAgorot: integer('held_liability_agorot'),
    driftAgorot: integer('drift_agorot'),
    payoutId: text('payout_id'),
    refundId: text('refund_id'),
    note: text('note'),
    createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
  },
  (t) => [
    index('payout_audit_log_kind_idx').on(t.kind, t.createdAt),
    index('payout_audit_log_vendor_idx').on(t.vendorId, t.createdAt),
  ],
);