/**
 * 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,
  time,
  index,
  uniqueIndex,
  unique,
  check,
  primaryKey,
} from 'drizzle-orm/pg-core';
import { sql } from 'drizzle-orm';
import { VARIANT_AXIS_KIND } from '@/lib/enums/variant-axis-kind';
import type { StockReservationId } from '@/server/platform-seams/ids.js';

import {
  dealTypeEnum,
  dealStateEnum,
  appealStatusEnum,
  approvedByEnum,
  imageApprovalStatusEnum,
  reviewTypeEnum,
  removalStatusEnum,
  removedByEnum,
  adminTargetTypeEnum,
  adminActionEnum,
  senderTypeEnum,
} from './enums.js';
import { users, vendors, dealCategories, dealTags } from './core.js';
import { languages } from './languages.js';
import { orderLine } from '../../../../node_modules/@platform-modules/commerce-orders/dist/index.js';

// ─── Deal Variants ────────────────────────────────────────────────────────────

export const variantAxisKindEnum = pgEnum('variant_axis_kind', VARIANT_AXIS_KIND);

export const dealVariantAxes = pgTable(
  'deal_variant_axes',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    dealId: uuid('deal_id')
      .notNull()
      .references(() => deals.id, { onDelete: 'restrict' }),
    axisOrder: integer('axis_order').notNull(),
    kind: variantAxisKindEnum('kind').notNull(),
    nameHe: text('name_he').notNull(),
    nameEn: text('name_en').notNull(),
    isActive: boolean('is_active').notNull().default(true),
    isVisualAxis: boolean('is_visual_axis').notNull().default(false),
    createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
  },
  (t) => [
    unique('deal_variant_axes_deal_order_uniq').on(t.dealId, t.axisOrder),
    uniqueIndex('deal_variant_axes_deal_visual_uniq')
      .on(t.dealId)
      .where(sql`${t.isVisualAxis} = true`),
  ],
);

export const dealVariantOptions = pgTable(
  'deal_variant_options',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    axisId: uuid('axis_id')
      .notNull()
      .references(() => dealVariantAxes.id, { onDelete: 'restrict' }),
    optionOrder: integer('option_order').notNull(),
    valueCode: text('value_code').notNull(),
    labelHe: text('label_he').notNull(),
    labelEn: text('label_en').notNull(),
    slotStart: timestamp('slot_start', { withTimezone: true }),
    slotEnd: timestamp('slot_end', { withTimezone: true }),
    swatchHex: text('swatch_hex'),
    isActive: boolean('is_active').notNull().default(true),
    createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
  },
  (t) => [unique('deal_variant_options_axis_code_uniq').on(t.axisId, t.valueCode)],
);

export const dealSkus = pgTable(
  'deal_skus',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    dealId: uuid('deal_id')
      .notNull()
      .references(() => deals.id, { onDelete: 'cascade' }),
    optionIds: uuid('option_ids').array().notNull(),
    optionIdsHash: text('option_ids_hash').notNull(),
    originalPrice: numeric('original_price', { precision: 10, scale: 2 }).notNull(),
    discountPercent: integer('discount_percent').notNull(),
    discountedPrice: numeric('discounted_price', { precision: 10, scale: 2 }).notNull(),
    quantityTotal: integer('quantity_total').notNull(),
    quantitySold: integer('quantity_sold').notNull().default(0),
    reservedQty: integer('reserved_qty').notNull().default(0),
    isActive: boolean('is_active').notNull().default(true),
    createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
  },
  (t) => [
    unique('deal_skus_deal_combo_uniq').on(t.dealId, t.optionIdsHash),
    check('sku_sold_le_qty', sql`quantity_sold <= quantity_total`),
    check('sku_discount_percent_range', sql`discount_percent BETWEEN 0 AND 100`),
  ],
);

/** 3.6 Deal */
export const deals = pgTable(
  'deals',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    vendorId: uuid('vendor_id')
      .notNull()
      .references(() => vendors.id),
    dealType: dealTypeEnum('deal_type').notNull(),
    title: text('title').notNull(),
    description: text('description').notNull().default(''),
    categoryId: uuid('category_id').references(() => dealCategories.id),
    category: text('category'),
    isVoucher: boolean('is_voucher').notNull().default(false),
    windowStart: timestamp('window_start', { withTimezone: true }),
    windowEnd: timestamp('window_end', { withTimezone: true }),
    pickupStart: time('pickup_start'),
    pickupEnd: time('pickup_end'),
    pickupAddress: text('pickup_address').notNull().default(''),
    specialInstructions: text('special_instructions'),
    dealState: dealStateEnum('deal_state').notNull().default('DRAFT'),
    rejectionReason: text('rejection_reason'),
    rejectionDetail: text('rejection_detail'),
    commissionRate: numeric('commission_rate', { precision: 4, scale: 3 })
      .notNull()
      .default('0.100'),
    isPersonalDeal: boolean('is_personal_deal').notNull().default(false),
    personalDealForUserId: uuid('personal_deal_for_user_id').references(() => users.id),
    approvedAt: timestamp('approved_at', { withTimezone: true }),
    approvedBy: approvedByEnum('approved_by'),
    /** SHA-256 hash of vendorId + normalized title + description + discountPercent for dedup. */
    contentHash: text('content_hash'),
    /** Appeal lifecycle when deal is REJECTED and vendor disputes the decision. */
    appealStatus: appealStatusEnum('appeal_status'),
    appealReason: text('appeal_reason'),
    appealAt: timestamp('appeal_at', { withTimezone: true }),
    appealDecidedAt: timestamp('appeal_decided_at', { withTimezone: true }),
    /** Set when an SLA alert has been sent to prevent duplicate alerts. */
    slaAlertedAt: timestamp('sla_alerted_at', { withTimezone: true }),
    /**
     * Maximum units a single user may purchase for this deal.
     * Null = no per-user cap (only clamped by remaining stock).
     */
    maxPerUser: integer('max_per_user'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    soldOutAt: timestamp('sold_out_at', { withTimezone: true }),
    soldOutGoldExpiresAt: timestamp('sold_out_gold_expires_at', { withTimezone: true }),
    /** ISO 639-1 code of the language the vendor originally authored this deal in. */
    sourceLanguage: text('source_language')
      .notNull()
      .default('he')
      .references(() => languages.code),
    /** Auto-translation pipeline state: NOT_STARTED | PENDING | PARTIAL | COMPLETE | FAILED */
    translationStatus: text('translation_status').notNull().default('NOT_STARTED'),
    /** Free-form metadata (e.g. {source: "baseline-seed"} for E2E protection markers). */
    metadata: jsonb('metadata').$type<Record<string, unknown>>().notNull().default({}),
    // ── Variant cache columns (denormalized from deal_skus, nullable until Task 22) ──
    minPrice: numeric('min_price', { precision: 10, scale: 2 }),
    maxPrice: numeric('max_price', { precision: 10, scale: 2 }),
    maxDiscountPercent: integer('max_discount_percent'),
    stockRemaining: integer('stock_remaining'),
    // ── Returns / RMA ─────────────────────────────────────────────────────────
    returnWindowDays: integer('return_window_days').notNull().default(14),
    isReturnable: boolean('is_returnable').notNull().default(true),
    isPhysical: boolean('is_physical').notNull().default(false),
  },
  (t) => [
    index('deals_vendor_state_idx').on(t.vendorId, t.dealState),
    index('deals_type_state_idx').on(t.dealType, t.dealState),
    index('deals_content_hash_idx').on(t.contentHash),
    index('deals_state_created_idx').on(t.dealState, t.createdAt),
    index('deals_state_window_end_idx').on(t.dealState, t.windowEnd),
    index('deals_category_state_created_idx').on(t.categoryId, t.dealState, t.createdAt),
  ],
);

// ── Recently Viewed ──────────────────────────────────────────────────────────

export const userRecentlyViewed = pgTable(
  'user_recently_viewed',
  {
    userId: uuid('user_id')
      .notNull()
      .references(() => users.id, { onDelete: 'cascade' }),
    dealId: uuid('deal_id')
      .notNull()
      .references(() => deals.id, { onDelete: 'cascade' }),
    viewedAt: timestamp('viewed_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    primaryKey({ columns: [t.userId, t.dealId] }),
    index('user_recently_viewed_user_time_idx').on(t.userId, t.viewedAt.desc()),
  ],
);

/** Many-to-many: deals ↔ tags */
export const dealTagAssignments = pgTable(
  'deal_tag_assignments',
  {
    dealId: uuid('deal_id')
      .notNull()
      .references(() => deals.id, { onDelete: 'cascade' }),
    tagId: uuid('tag_id')
      .notNull()
      .references(() => dealTags.id, { onDelete: 'cascade' }),
  },
  (t) => [
    primaryKey({ columns: [t.dealId, t.tagId] }),
    index('deal_tag_assignments_tag_idx').on(t.tagId),
    index('deal_tag_assignments_deal_idx').on(t.dealId),
  ],
);

/** Deal drafts — vendor in-progress deal forms saved before submission. */
export const dealDrafts = pgTable(
  'deal_drafts',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    vendorId: uuid('vendor_id')
      .notNull()
      .references(() => vendors.id, { onDelete: 'cascade' }),
    payload: jsonb('payload').$type<Record<string, unknown>>().notNull(),
    title: text('title'),
    isStarred: boolean('is_starred').notNull().default(false),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    index('deal_drafts_vendor_updated_idx').on(t.vendorId, t.updatedAt.desc()),
    index('deal_drafts_purge_idx')
      .on(t.updatedAt)
      .where(sql`${t.isStarred} = false`),
  ],
);
export type DealDraft = typeof dealDrafts.$inferSelect;
export type NewDealDraft = typeof dealDrafts.$inferInsert;

/** DealImage */
export const dealImages = pgTable(
  'deal_images',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    dealId: uuid('deal_id')
      .notNull()
      .references(() => deals.id, { onDelete: 'cascade' }),
    draftId: uuid('draft_id').references(() => dealDrafts.id, { onDelete: 'cascade' }),
    skuId: uuid('sku_id').references(() => dealSkus.id, { onDelete: 'cascade' }),
    url: text('url').notNull(),
    isPrimary: boolean('is_primary').notNull().default(false),
    sortOrder: integer('sort_order').notNull().default(0),
    approvalStatus: imageApprovalStatusEnum('approval_status').notNull().default('PENDING'),
  },
  (t) => [
    index('deal_images_deal_idx').on(t.dealId),
    index('deal_images_sku_idx').on(t.skuId),
    uniqueIndex('deal_images_deal_primary_uniq')
      .on(t.dealId)
      .where(sql`${t.isPrimary} = true AND ${t.skuId} IS NULL`),
    uniqueIndex('deal_images_sku_primary_uniq')
      .on(t.skuId)
      .where(sql`${t.isPrimary} = true AND ${t.skuId} IS NOT NULL`),
  ],
);

/** Mock payment event ledger — records every MockPaymentProvider operation (dev/test only). */
export const mockPaymentEvents = pgTable('mock_payment_events', {
  id: uuid('id').primaryKey().defaultRandom(),
  op: text('op').notNull(),
  orderLineId: uuid('order_line_id'),
  reservationId: uuid('reservation_id').$type<StockReservationId | null>(),
  vendorId: uuid('vendor_id'),
  scenario: text('scenario').notNull(),
  outcomeCode: text('outcome_code').notNull(),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
});

/** 3.8 Review */
export const reviews = pgTable(
  'reviews',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    orderLineId: uuid('order_line_id')
      .notNull()
      .unique()
      .references(() => orderLine.id),
    userId: uuid('user_id')
      .notNull()
      .references(() => users.id),
    vendorId: uuid('vendor_id')
      .notNull()
      .references(() => vendors.id),
    dealId: uuid('deal_id')
      .notNull()
      .references(() => deals.id),
    reviewType: reviewTypeEnum('review_type').notNull().default('STANDARD'),
    /** 1-5 for STANDARD reviews, null for TECHNICAL. */
    rating: integer('rating'),
    body: text('body').notNull(),
    vendorReply: text('vendor_reply'),
    vendorReplyAt: timestamp('vendor_reply_at', { withTimezone: true }),
    removalRequested: boolean('removal_requested').notNull().default(false),
    removalReason: text('removal_reason'),
    removalStatus: removalStatusEnum('removal_status').notNull().default('NONE'),
    removedBy: removedByEnum('removed_by'),
    isVisible: boolean('is_visible').notNull().default(true),
    language: text('language').notNull().default('und'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    check('reviews_rating_range', sql`${t.rating} IS NULL OR (${t.rating} BETWEEN 1 AND 5)`),
    index('reviews_vendor_visible_idx').on(t.vendorId, t.isVisible),
    index('reviews_deal_visible_idx').on(t.dealId, t.isVisible),
  ],
);

// prettier-ignore
export const dealAnnotations = pgTable(
  'deal_annotations',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    dealId: uuid('deal_id')
      .notNull()
      .references(() => deals.id),
    authorId: uuid('author_id').notNull(),
    authorRole: text('author_role').notNull(), // 'vendor' | 'admin'
    body: text('body').notNull(),
    isVisible: boolean('is_visible').notNull().default(true),
    createdAt: timestamp('created_at', { withTimezone: true, precision: 3 }).notNull().defaultNow(),
  },
  (t) => [index('deal_annotations_deal_idx').on(t.dealId, t.isVisible)],
);

/** 3.9 ClubMembership */
export const clubMemberships = pgTable(
  'club_memberships',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    userId: uuid('user_id')
      .notNull()
      .references(() => users.id, { onDelete: 'cascade' }),
    vendorId: uuid('vendor_id')
      .notNull()
      .references(() => vendors.id),
    joinedViaOrderLineId: uuid('joined_via_order_line_id')
      .notNull()
      .references(() => orderLine.id),
    joinedAt: timestamp('joined_at', { withTimezone: true }).notNull().defaultNow(),
    isActive: boolean('is_active').notNull().default(true),
  },
  (t) => [
    unique('club_user_vendor_unique').on(t.userId, t.vendorId),
    index('club_vendor_idx').on(t.vendorId),
  ],
);

/** 3.12 AdminAction - immutable audit log */
export const adminActions = pgTable('admin_actions', {
  id: uuid('id').primaryKey().defaultRandom(),
  /** Null when action was taken by AI_AGENT. */
  adminId: uuid('admin_id').references(() => users.id),
  targetType: adminTargetTypeEnum('target_type').notNull(),
  targetId: uuid('target_id').notNull(),
  action: adminActionEnum('action').notNull(),
  note: text('note'),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
});

/** 3.13 PurchaseMessage */
export const purchaseMessages = pgTable('purchase_messages', {
  id: uuid('id').primaryKey().defaultRandom(),
  orderLineId: uuid('order_line_id')
    .notNull()
    .references(() => orderLine.id, { onDelete: 'cascade' }),
  senderType: senderTypeEnum('sender_type').notNull(),
  senderId: uuid('sender_id').notNull(),
  body: text('body').notNull(),
  readAt: timestamp('read_at', { withTimezone: true }),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
});