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

import { users, vendors } from './core.js';
import { dealSkus, deals } from './deal-variants.js';

// ─── Cart Tables ─────────────────────────────────────────────────────────────

/** Shopping cart line items for authenticated users. */
export const cartItems = pgTable(
  'cart_items',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    userId: uuid('user_id')
      .notNull()
      .references(() => users.id, { onDelete: 'cascade' }),
    dealSkuId: uuid('deal_sku_id')
      .notNull()
      .references(() => dealSkus.id, { onDelete: 'cascade' }),
    qty: integer('qty').notNull(),
    addedAt: timestamp('added_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    unique('cart_items_user_id_deal_sku_id_unique').on(t.userId, t.dealSkuId),
    index('cart_items_user_idx').on(t.userId),
  ],
);

export const cart = pgTable(
  'cart',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    currency: text('currency').notNull(),
    userId: text('user_id'),
    createdAt: timestamp('created_at', { withTimezone: true, precision: 3 }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true, precision: 3 }).notNull().defaultNow(),
  },
  (t) => [
    uniqueIndex('cart_user_id_key')
      .on(t.userId)
      .where(sql`${t.userId} IS NOT NULL`),
  ],
);

export const cartLine = pgTable(
  'cart_line',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    cartId: uuid('cart_id')
      .notNull()
      .references(() => cart.id, { onDelete: 'cascade' }),
    variantId: uuid('variant_id').notNull(),
    qty: integer('qty').notNull(),
    amount: bigint('amount', { mode: 'bigint' }).notNull(),
    currency: text('currency').notNull(),
    priceMode: text('price_mode').notNull().$type<'inclusive' | 'exclusive'>(),
    vendorId: text('vendor_id'),
  },
  (t) => [uniqueIndex('cart_line_cart_id_variant_id_key').on(t.cartId, t.variantId)],
);

// ─── Wishlist & Favorites ─────────────────────────────────────────────────────

export const wishlistItems = pgTable(
  'wishlist_items',
  {
    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' }),
    notify: boolean('notify').notNull().default(false),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    unique('wishlist_items_user_deal_unique').on(t.userId, t.dealId),
    index('wishlist_items_user_idx').on(t.userId),
    index('wishlist_items_deal_idx').on(t.dealId),
  ],
);

export const favoriteVendors = pgTable(
  'favorite_vendors',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    userId: uuid('user_id')
      .notNull()
      .references(() => users.id, { onDelete: 'cascade' }),
    vendorId: uuid('vendor_id')
      .notNull()
      .references(() => vendors.id, { onDelete: 'cascade' }),
    notify: boolean('notify').notNull().default(false),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    unique('favorite_vendors_user_vendor_unique').on(t.userId, t.vendorId),
    index('favorite_vendors_user_idx').on(t.userId),
    index('favorite_vendors_vendor_idx').on(t.vendorId),
  ],
);