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

import { shareLinks } from './affiliate.js';

// ─── Order model (commerce-orders module + host companion tables) ─────────────
import {
  order,
  orderLine,
  vendorSplit,
  orderStep,
  refundIntent,
} from '../../../../node_modules/@platform-modules/commerce-orders/dist/index.js';
export { order, orderLine, vendorSplit, orderStep, refundIntent };
export {
  voucher,
  shipment,
  accessGrant,
  carrierWebhookEvent,
} from '../../../../node_modules/@platform-modules/commerce-fulfillment/dist/index.js';
export {
  inventoryItem,
  stockReservation,
} from '../../../../node_modules/@platform-modules/commerce-inventory/dist/index.js';
export { review } from '../../../../node_modules/@platform-modules/commerce-reviews/dist/index.js';

export const commandRecords = pgTable(
  'command_records',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    commandKey: text('command_key').notNull(),
    commandType: text('command_type').notNull(),
    aggregateType: text('aggregate_type').notNull(),
    aggregateId: uuid('aggregate_id').notNull(),
    payloadHash: text('payload_hash').notNull(),
    payload: jsonb('payload').notNull(),
    status: text('status').notNull().default('CLAIMED'),
    claimedBy: text('claimed_by').notNull(),
    claimedAt: timestamp('claimed_at', { withTimezone: true }).notNull().defaultNow(),
    completedAt: timestamp('completed_at', { withTimezone: true }),
    failedAt: timestamp('failed_at', { withTimezone: true }),
    resultPayload: jsonb('result_payload'),
    failureCode: text('failure_code'),
    failureMessage: text('failure_message'),
    claimGeneration: bigint('claim_generation', { mode: 'number' }).notNull().default(1),
    leaseExpiresAt: timestamp('lease_expires_at', { withTimezone: true })
      .notNull()
      .default(sql`CURRENT_TIMESTAMP + interval '10 minutes'`),
  },
  (t) => [
    uniqueIndex('command_records_command_key_uq').on(t.commandKey),
    index('command_records_status_lease_expires_at_idx').on(t.status, t.leaseExpiresAt),
    check('command_records_status_ck', sql`${t.status} IN ('CLAIMED', 'COMPLETED', 'FAILED')`),
  ],
);

/** Encrypted guest PII — BuyerRef.guestEmail in order carries an opaque host guest-token only; real email/phone stored here. */
export const orderGuestContact = pgTable(
  'order_guest_contact',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    orderId: uuid('order_id').notNull(),
    emailEnc: text('email_enc').notNull(),
    phoneEnc: text('phone_enc'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [uniqueIndex('order_guest_contact_order_id_uniq').on(t.orderId)],
);

/** First-touch share link attribution — one row per order when checkout reads __sh cookie. */
export const orderShareAttribution = pgTable('order_share_attribution', {
  id: uuid('id').primaryKey().defaultRandom(),
  orderId: uuid('order_id').notNull().unique(),
  shareSlugId: uuid('share_slug_id')
    .notNull()
    .references(() => shareLinks.id, { onDelete: 'cascade' }),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
});

export const orderLineVoucherExt = pgTable('order_line_voucher_ext', {
  voucherId: text('voucher_id').primaryKey(),
  lineId: text('line_id').notNull(),
  qrTokenHash: text('qr_token_hash'),
  qrPngUrl: text('qr_png_url'),
  reviewEligible: boolean('review_eligible').notNull().default(false),
  guestAccessTokenHash: text('guest_access_token_hash'),
  guestAccessTokenExpiresAt: timestamp('guest_access_token_expires_at', { withTimezone: true }),
});

/** Per-refund line ownership. commerce-orders keeps refund_intent headless; host-owned line linkage lives here. */
export const refundIntentLineExt = pgTable(
  'refund_intent_line_ext',
  {
    refundIntentId: uuid('refund_intent_id')
      .primaryKey()
      .references(() => refundIntent.id, { onDelete: 'cascade' }),
    orderLineId: uuid('order_line_id')
      .notNull()
      .references(() => orderLine.id, { onDelete: 'restrict' }),
  },
  (t) => [index('refund_intent_line_ext_order_line_idx').on(t.orderLineId)],
);

/**
 * Processor fee per order — one row per order per kind ('charge'; 'dispute' addable later).
 * stripe_fee_agorot is nullable: written null at webhook time if balance tx not yet ready,
 * backfilled to a real value on the next reconciler pass (ON CONFLICT sticky-non-null upsert).
 */
export const orderPaymentFees = pgTable(
  'order_payment_fees',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    orderId: uuid('order_id').notNull(),
    kind: text('kind').notNull().default('charge'),
    stripeFeeAgorot: integer('stripe_fee_agorot'),
    providerPaymentId: text('provider_payment_id'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [uniqueIndex('order_payment_fees_order_kind_uq').on(t.orderId, t.kind)],
);