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

import { users } from './core.js';

// ─── Live Notifications ───────────────────────────────────────────────────────

/**
 * Per-user live notifications (bell / toast / push).
 * NOTE: Named `liveNotifications` / `live_notifications` to avoid collision with
 * the existing `notifications` table (vendor dashboard image-rejection system).
 * Downstream tasks T5/T6/T15/T19/T26 must reference `liveNotifications`.
 */
export const liveNotifications = pgTable(
  'live_notifications',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    userId: uuid('user_id')
      .notNull()
      .references(() => users.id, { onDelete: 'cascade' }),
    event: text('event').notNull(),
    tier: text('tier').notNull(),
    titleHe: text('title_he').notNull(),
    titleEn: text('title_en').notNull(),
    bodyHe: text('body_he'),
    bodyEn: text('body_en'),
    link: text('link'),
    payload: jsonb('payload').$type<Record<string, unknown>>().notNull().default({}),
    readAt: timestamp('read_at', { withTimezone: true }),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    expiresAt: timestamp('expires_at', { withTimezone: true })
      .notNull()
      .default(sql`now() + interval '30 days'`),
  },
  (t) => [
    index('live_notifications_user_unread_idx').on(t.userId, t.createdAt),
    index('live_notifications_expires_idx').on(t.expiresAt),
  ],
);

export type NotificationRow = typeof liveNotifications.$inferSelect;
export type NotificationInsert = typeof liveNotifications.$inferInsert;

/**
 * Tracks which admin user is assigned to handle a given scoped entity.
 * scope examples: 'deal', 'vendor', 'support_ticket'.
 */
export const adminAssignments = pgTable(
  'admin_assignments',
  {
    scope: text('scope').notNull(),
    entityId: uuid('entity_id').notNull(),
    adminUserId: uuid('admin_user_id')
      .notNull()
      .references(() => users.id, { onDelete: 'cascade' }),
    assignedAt: timestamp('assigned_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    primaryKey({ columns: [t.scope, t.entityId] }),
    index('admin_assignments_admin_idx').on(t.adminUserId),
  ],
);

export type AdminAssignmentRow = typeof adminAssignments.$inferSelect;

// ─── Stripe Webhook Event Dedup ───────────────────────────────────────────────

/** DB-level idempotency for Stripe webhooks. Insert on receipt with ON CONFLICT DO NOTHING. */
export const stripeWebhookEvents = pgTable('stripe_webhook_events', {
  eventId: text('event_id').primaryKey(),
  eventType: text('event_type').notNull(),
  receivedAt: timestamp('received_at', { withTimezone: true }).notNull().defaultNow(),
  claimedAt: timestamp('claimed_at', { withTimezone: true }),
  processedAt: timestamp('processed_at', { withTimezone: true }),
});
export type StripeWebhookEvent = typeof stripeWebhookEvents.$inferSelect;
export type NewStripeWebhookEvent = typeof stripeWebhookEvents.$inferInsert;

// ─── Carrier Webhook Event Dedup ─────────────────────────────────────────────

/** DB-level idempotency for carrier webhooks. Insert on receipt with ON CONFLICT DO NOTHING. */
export const carrierWebhookEvents = pgTable(
  'carrier_webhook_events',
  {
    carrier: text('carrier').notNull(),
    eventId: text('event_id').notNull(),
    receivedAt: timestamp('received_at', { withTimezone: true }).notNull().defaultNow(),
    claimedAt: timestamp('claimed_at', { withTimezone: true }),
    processedAt: timestamp('processed_at', { withTimezone: true }),
  },
  (t) => [primaryKey({ columns: [t.carrier, t.eventId] })],
);
export type CarrierWebhookEvent = typeof carrierWebhookEvents.$inferSelect;
export type NewCarrierWebhookEvent = typeof carrierWebhookEvents.$inferInsert;

// ─── Chat subsystem ────────────────────────────────────────────────────────
// Backs chat:thread:<id> and presence:thread:<id> WS channel families.
// See Docs/plans/2026-05-22-chat-subsystem.md.

export const chatThreads = pgTable(
  'chat_threads',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    title: text('title'),
    kind: text('kind').notNull().default('dm'),
    createdBy: uuid('created_by')
      .notNull()
      .references(() => users.id, { onDelete: 'cascade' }),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    lastMessageAt: timestamp('last_message_at', { withTimezone: true }).notNull().defaultNow(),
    lastMessagePreview: varchar('last_message_preview', { length: 200 }),
  },
  (t) => [index('chat_threads_last_message_idx').on(t.lastMessageAt)],
);

export type ChatThreadRow = typeof chatThreads.$inferSelect;
export type ChatThreadInsert = typeof chatThreads.$inferInsert;

export const chatThreadParticipants = pgTable(
  'chat_thread_participants',
  {
    threadId: uuid('thread_id')
      .notNull()
      .references(() => chatThreads.id, { onDelete: 'cascade' }),
    userId: uuid('user_id')
      .notNull()
      .references(() => users.id, { onDelete: 'cascade' }),
    joinedAt: timestamp('joined_at', { withTimezone: true }).notNull().defaultNow(),
    lastReadAt: timestamp('last_read_at', { withTimezone: true }),
  },
  (t) => [
    primaryKey({ columns: [t.threadId, t.userId] }),
    index('chat_thread_participants_user_idx').on(t.userId),
  ],
);

export type ChatThreadParticipantRow = typeof chatThreadParticipants.$inferSelect;
export type ChatThreadParticipantInsert = typeof chatThreadParticipants.$inferInsert;

export const chatMessages = pgTable(
  'chat_messages',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    threadId: uuid('thread_id')
      .notNull()
      .references(() => chatThreads.id, { onDelete: 'cascade' }),
    senderUserId: uuid('sender_user_id')
      .notNull()
      .references(() => users.id, { onDelete: 'cascade' }),
    body: text('body').notNull(),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [index('chat_messages_thread_created_idx').on(t.threadId, t.createdAt)],
);

export type ChatMessageRow = typeof chatMessages.$inferSelect;