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

import {
  avatarTypeEnum,
  accountStateUserEnum,
  paymentBrandEnum,
  accountStateVendorEnum,
  vendorTierEnum,
  imageApprovalStatusEnum,
} from './enums.js';

// ─── Core Tables ─────────────────────────────────────────────────────────────

/** 3.1 User */
export const users = pgTable(
  'users',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    /** Encrypted with pgcrypto. Nullable for email-only accounts. */
    phone: text('phone'),
    /** Deterministic HMAC-SHA256 blind index for phone lookup. Nullable for email-only accounts. */
    phoneIndex: text('phone_index').unique(),
    /** Last 3 digits of phone, plain text (not PII). Authoritative source for mh cookie `ph` field. */
    phoneHint: text('phone_hint'),
    /** Encrypted. Nullable if user has no email. */
    email: text('email'),
    /** Deterministic blind index for email lookup. */
    emailIndex: text('email_index').unique(),
    avatarType: avatarTypeEnum('avatar_type').notNull().default('ICON'),
    avatarValue: text('avatar_value').notNull().default(''),
    /** PBKDF2 hash for email+password auth. Null for OTP-only accounts. */
    passwordHash: text('password_hash'),
    email2faEnabled: boolean('email_2fa_enabled').notNull().default(true),
    purchaseCount: integer('purchase_count').notNull().default(0),
    /** Auto-built preference profile JSON. */
    preferencesProfile: jsonb('preferences_profile'),
    accountState: accountStateUserEnum('account_state').notNull().default('ACTIVE'),
    isAdmin: boolean('is_admin').notNull().default(false),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    deletionRequestedAt: timestamp('deletion_requested_at', { withTimezone: true }),
    /** Incremented on logout-all / password change. JWT must carry matching sv. Added in M1. */
    sessionVersion: integer('session_version').notNull().default(0),
    /** Incremented on profile/avatar/display-name changes. mh cookie + JWT mhv claim carry this for cache invalidation. */
    mhVersion: integer('mh_version').notNull().default(1),
    /** User-set display name. Nullable — most users set via profile page after signup. */
    displayName: text('display_name'),
    /** Set when user completes email verification. Null = unverified. */
    emailVerifiedAt: timestamp('email_verified_at', { withTimezone: true }),
    // ── Avatar approval triplet ───────────────────────────────────────────────
    avatarApprovalStatus: imageApprovalStatusEnum('avatar_approval_status')
      .notNull()
      .default('APPROVED'),
    pendingAvatarValue: text('pending_avatar_value'),
    avatarRejectReasonCode: text('avatar_reject_reason_code'),
    // ── Onboarding fields ────────────────────────────────────────────────────
    /** User's city (free-text display label). */
    city: text('city'),
    /** Standardised city code for lookups/filtering. */
    cityCode: text('city_code'),
    /** User-chosen preferred city code for feed filtering. Null = auto (cookie → IP-geo → TA). */
    preferredCityCode: text('preferred_city_code'),
    /** Birth month 1-12. Nullable — collected during onboarding. */
    birthMonth: integer('birth_month'),
    /** Birth day 1-31. Nullable — collected during onboarding. */
    birthDay: integer('birth_day'),
    /** Set when user completes the post-auth onboarding modal. Null = not yet onboarded. */
    onboardingCompletedAt: timestamp('onboarding_completed_at', { withTimezone: true }),
    /** Per-user notification preferences. Keys are event names; value false = opted out. */
    notifPrefs: jsonb('notif_prefs').notNull().default({}).$type<{
      optional?: Record<string, boolean>;
      marketing?: Record<string, boolean>;
    }>(),
    /** User-set preferences JSON (e.g. { dealAlerts: bool, clubAlerts: bool }). */
    preferences: jsonb('preferences').$type<Record<string, unknown>>().notNull().default({}),
    // ── Stripe saved-payment-method bootstrap ────────────────────────────────
    stripeCustomerId: text('stripe_customer_id'),
    defaultPaymentMethodId: text('default_payment_method_id'),
    /** Canonical normalised email for dedup/fraud checks (no dots/plus aliases). */
    emailCanonicalIndex: text('email_canonical_index'),
  },
  (t) => [
    index('users_phone_index_idx').on(t.phoneIndex),
    index('users_email_index_idx').on(t.emailIndex),
    uniqueIndex('users_stripe_customer_id_uniq')
      .on(t.stripeCustomerId)
      .where(sql`stripe_customer_id IS NOT NULL`),
    check(
      'users_birth_month_range_check',
      sql`${t.birthMonth} IS NULL OR (${t.birthMonth} BETWEEN 1 AND 12)`,
    ),
    check(
      'users_birth_day_range_check',
      sql`${t.birthDay} IS NULL OR (${t.birthDay} BETWEEN 1 AND 31)`,
    ),
    check(
      'users_phone_cols_consistent',
      sql`(${t.phone} IS NULL AND ${t.phoneIndex} IS NULL AND ${t.phoneHint} IS NULL)
     OR (${t.phone} IS NOT NULL AND ${t.phoneIndex} IS NOT NULL AND ${t.phoneHint} IS NOT NULL)`,
    ),
    check(
      'users_email_cols_consistent',
      sql`(${t.email} IS NULL AND ${t.emailIndex} IS NULL)
     OR (${t.email} IS NOT NULL AND ${t.emailIndex} IS NOT NULL)`,
    ),
  ],
);

/** 3.2 Address */
export const addresses = pgTable(
  'addresses',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    userId: uuid('user_id')
      .notNull()
      .references(() => users.id, { onDelete: 'cascade' }),
    label: text('label').notNull(),
    /** Encrypted full address string. */
    fullAddress: text('full_address').notNull(),
    lat: numeric('lat', { precision: 10, scale: 7 }).notNull(),
    lng: numeric('lng', { precision: 10, scale: 7 }).notNull(),
    isDefault: boolean('is_default').notNull().default(false),
    isGps: boolean('is_gps').notNull().default(false),
  },
  (t) => [index('addresses_user_default_idx').on(t.userId, t.isDefault)],
);

/** 3.3 PaymentMethod */
export const paymentMethods = pgTable('payment_methods', {
  id: uuid('id').primaryKey().defaultRandom(),
  userId: uuid('user_id')
    .notNull()
    .references(() => users.id, { onDelete: 'cascade' }),
  last4: text('last4').notNull(),
  brand: paymentBrandEnum('brand').notNull(),
  /** Encrypted provider token - not raw card data. */
  token: text('token').notNull(),
  isDefault: boolean('is_default').notNull().default(false),
  cardFingerprint: text('card_fingerprint'),
});

/** Business type classification for vendors (e.g. restaurant, salon, gym). */
export const businessTypes = pgTable('business_types', {
  id: uuid('id').primaryKey().defaultRandom(),
  nameHe: text('name_he').notNull(),
  nameEn: text('name_en').notNull(),
  sortOrder: integer('sort_order').notNull().default(0),
  isActive: boolean('is_active').notNull().default(true),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
});

/** M:N — a vendor may hold 1..5 business types (e.g. cafe + bakery). */
export const vendorBusinessTypes = pgTable(
  'vendor_business_types',
  {
    vendorId: uuid('vendor_id')
      .notNull()
      .references(() => vendors.id, { onDelete: 'cascade' }),
    businessTypeId: uuid('business_type_id')
      .notNull()
      .references(() => businessTypes.id, { onDelete: 'cascade' }),
  },
  (t) => [
    primaryKey({ columns: [t.vendorId, t.businessTypeId] }),
    index('vendor_business_types_type_idx').on(t.businessTypeId),
  ],
);
export type VendorBusinessType = typeof vendorBusinessTypes.$inferSelect;

/** 3.4 Vendor */
export const vendors = pgTable(
  'vendors',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    ownerUserId: uuid('owner_user_id')
      .notNull()
      .references(() => users.id),
    businessName: text('business_name').notNull(),
    displayName: text('display_name').notNull(),
    description: text('description').notNull().default(''),
    /** Encrypted vendor phone. */
    phone: text('phone').notNull(),
    /** Encrypted vendor email. */
    email: text('email').notNull(),
    logoUrl: text('logo_url'),
    heroImageUrl: text('hero_image_url'),
    heroImageR2Key: text('hero_image_r2_key'),
    heroFocalX: real('hero_focal_x').notNull().default(0.5),
    heroFocalY: real('hero_focal_y').notNull().default(0.5),
    heroImageApprovalStatus: imageApprovalStatusEnum('hero_image_approval_status')
      .notNull()
      .default('PENDING'),
    heroPendingImageUrl: text('hero_pending_image_url'),
    heroPendingImageR2Key: text('hero_pending_image_r2_key'),
    heroImageRejectReasonCode: text('hero_image_reject_reason_code'),
    website: text('website'),
    tier: vendorTierEnum('tier').notNull().default('NEW'),
    accountState: accountStateVendorEnum('account_state')
      .notNull()
      .default('PENDING_FIRST_APPROVAL'),
    flagReason: text('flag_reason'),
    rejectReason: text('reject_reason'),
    llmDecision: text('llm_decision'),
    totalSales: integer('total_sales').notNull().default(0),
    totalRevenue: numeric('total_revenue', { precision: 12, scale: 2 }).notNull().default('0'),
    reviewsScore: numeric('reviews_score', { precision: 3, scale: 2 }).notNull().default('0'),
    reviewsCount: integer('reviews_count').notNull().default(0),
    removedReviewsCount: integer('removed_reviews_count').notNull().default(0),
    dealViolationsCount: integer('deal_violations_count').notNull().default(0),
    unresolvedVoucherComplaints: integer('unresolved_voucher_complaints').notNull().default(0),
    firstVoucherComplaintAt: timestamp('first_voucher_complaint_at', { withTimezone: true }),
    /** Vendor notification + UX preference profile JSON. */
    preferencesProfile: jsonb('preferences_profile').default({}),
    /** Whether the vendor offers self-pickup at their location. */
    selfPickup: boolean('self_pickup').notNull().default(false),
    /** Human-readable pickup address string. */
    pickupAddress: text('pickup_address'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    // ── Stripe Connect Express ─────────────────────────────────────────────────
    stripeAccountId: text('stripe_account_id'),
    stripeChargesEnabled: boolean('stripe_charges_enabled').notNull().default(false),
    stripePayoutsEnabled: boolean('stripe_payouts_enabled').notNull().default(false),
    stripeOnboardingState: text('stripe_onboarding_state')
      .notNull()
      .default('not_started')
      .$type<'not_started' | 'account_created' | 'kyc_pending' | 'charges_enabled'>(),
    stripeDetailsSubmitted: boolean('stripe_details_submitted').notNull().default(false),
    stripeRequirementsCurrentlyDue: jsonb('stripe_requirements_currently_due').$type<
      string[] | null
    >(),
    // ── Logo approval triplet ──────────────────────────────────────────────────
    logoApprovalStatus: imageApprovalStatusEnum('logo_approval_status')
      .notNull()
      .default('APPROVED'),
    pendingLogoUrl: text('pending_logo_url'),
    pendingLogoR2Key: text('pending_logo_r2_key'),
    logoRejectReasonCode: text('logo_reject_reason_code'),
    // ── Mock payment provider (dev/test only) ─────────────────────────────
    mockScenario: text('mock_scenario'),
    // ── Returns / RMA ─────────────────────────────────────────────────────────
    returnAddress: jsonb('return_address').$type<{
      line1: string;
      line2?: string;
      city: string;
      postal: string;
      country: string;
    }>(),
    restockingFeePct: numeric('restocking_fee_pct', { precision: 4, scale: 3 })
      .notNull()
      .default('0'),
  },
  (t) => [
    uniqueIndex('vendors_stripe_account_id_uniq')
      .on(t.stripeAccountId)
      .where(sql`stripe_account_id IS NOT NULL`),
  ],
);

/** 3.5 BusinessHours */
export const businessHours = pgTable('business_hours', {
  id: uuid('id').primaryKey().defaultRandom(),
  vendorId: uuid('vendor_id')
    .notNull()
    .unique()
    .references(() => vendors.id, { onDelete: 'cascade' }),
  // Monday
  mondayOpen: time('monday_open'),
  mondayClose: time('monday_close'),
  mondayClosed: boolean('monday_closed').notNull().default(false),
  // Tuesday
  tuesdayOpen: time('tuesday_open'),
  tuesdayClose: time('tuesday_close'),
  tuesdayClosed: boolean('tuesday_closed').notNull().default(false),
  // Wednesday
  wednesdayOpen: time('wednesday_open'),
  wednesdayClose: time('wednesday_close'),
  wednesdayClosed: boolean('wednesday_closed').notNull().default(false),
  // Thursday
  thursdayOpen: time('thursday_open'),
  thursdayClose: time('thursday_close'),
  thursdayClosed: boolean('thursday_closed').notNull().default(false),
  // Friday
  fridayOpen: time('friday_open'),
  fridayClose: time('friday_close'),
  fridayClosed: boolean('friday_closed').notNull().default(false),
  // Saturday
  saturdayOpen: time('saturday_open'),
  saturdayClose: time('saturday_close'),
  saturdayClosed: boolean('saturday_closed').notNull().default(true),
  // Sunday
  sundayOpen: time('sunday_open'),
  sundayClose: time('sunday_close'),
  sundayClosed: boolean('sunday_closed').notNull().default(false),

  specialNotes: text('special_notes'),
});

/** VendorAddress */
export const vendorAddresses = pgTable('vendor_addresses', {
  id: uuid('id').primaryKey().defaultRandom(),
  vendorId: uuid('vendor_id')
    .notNull()
    .references(() => vendors.id, { onDelete: 'cascade' }),
  label: text('label').notNull(),
  fullAddress: text('full_address').notNull(),
  city: text('city').notNull().default(''),
  cityCode: text('city_code').notNull().default(''),
  streetCode: text('street_code').notNull().default(''),
  streetName: text('street_name').notNull().default(''),
  houseNumber: text('house_number').notNull().default(''),
  apt: text('apt').notNull().default(''),
  lat: numeric('lat', { precision: 10, scale: 7 }).notNull(),
  lng: numeric('lng', { precision: 10, scale: 7 }).notNull(),
  isPublic: boolean('is_public').notNull().default(false),
});

/** Deal categories — admin-managed, universal (assignable to any deal type).
 *  Display names live in `category_translations` (per-locale sidecar). */
export const dealCategories = pgTable(
  'deal_categories',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    dealType: text('deal_type').notNull(),
    sortOrder: integer('sort_order').notNull().default(0),
    isActive: boolean('is_active').notNull().default(true),
    slug: text('slug').notNull(),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    index('deal_categories_type_active_idx').on(t.dealType, t.isActive, t.sortOrder),
    uniqueIndex('deal_categories_slug_uidx').on(t.slug),
  ],
);

/** Tags — admin-managed, independent of categories.
 *  Display names live in `tag_translations` (per-locale sidecar). */
export const dealTags = pgTable(
  'deal_tags',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    slug: text('slug').notNull(),
    isActive: boolean('is_active').notNull().default(true),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [uniqueIndex('deal_tags_slug_uidx').on(t.slug)],
);