-- apps/web/drizzle/0064_notifications.sql
-- Renamed: live_notifications (was `notifications`) — collides with existing
-- vendor dashboard `notifications` table (image-rejection system). T3 schema
-- exports `liveNotifications` symbol mapped to this physical table.
CREATE TABLE IF NOT EXISTS live_notifications (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  event text NOT NULL,
  tier text NOT NULL,
  title_he text NOT NULL,
  title_en text NOT NULL,
  body_he text,
  body_en text,
  link text,
  payload jsonb NOT NULL DEFAULT '{}'::jsonb,
  read_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now(),
  expires_at timestamptz NOT NULL DEFAULT (now() + interval '30 days')
);

CREATE INDEX IF NOT EXISTS live_notifications_user_unread_idx
  ON live_notifications (user_id, created_at DESC) WHERE read_at IS NULL;
CREATE INDEX IF NOT EXISTS live_notifications_expires_idx
  ON live_notifications (expires_at);

CREATE TABLE IF NOT EXISTS admin_assignments (
  scope text NOT NULL,
  entity_id uuid NOT NULL,
  admin_user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  assigned_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (scope, entity_id)
);
CREATE INDEX IF NOT EXISTS admin_assignments_admin_idx ON admin_assignments(admin_user_id);
