-- ============================================================================
-- notifications_20260505.sql
-- Author : Damir Radulić — dradulic@outlook.com / damir@rinet.one
-- Date   : 2026-05-05
-- Purpose: Extend pgz_sport.notifications for /api/v2/notif (notification
--          center reachable from /app#notif). Existing table has channel/
--          subject/body/status/read_at, FK to users; we add kind/title/link/
--          is_read/created_at (additive — backwards compatible with CRM).
--          Adds composite index + seeds 3 demo InApp notifications.
-- ============================================================================

BEGIN;

-- 1) Additive columns (NO TYPE CHANGES) -------------------------------------
ALTER TABLE pgz_sport.notifications
  ADD COLUMN IF NOT EXISTS kind        text,
  ADD COLUMN IF NOT EXISTS title       text,
  ADD COLUMN IF NOT EXISTS link        text,
  ADD COLUMN IF NOT EXISTS is_read     boolean,
  ADD COLUMN IF NOT EXISTS created_at  timestamptz DEFAULT now();

-- Default kind to channel-derived value where null
UPDATE pgz_sport.notifications
   SET kind = COALESCE(kind, 'info')
 WHERE kind IS NULL;

-- Mirror title <- subject for legacy rows
UPDATE pgz_sport.notifications
   SET title = COALESCE(title, subject, 'Notifikacija')
 WHERE title IS NULL;

-- Backfill is_read from read_at
UPDATE pgz_sport.notifications
   SET is_read = (read_at IS NOT NULL)
 WHERE is_read IS NULL;

-- Backfill created_at from scheduled_at where null
UPDATE pgz_sport.notifications
   SET created_at = COALESCE(created_at, scheduled_at, now())
 WHERE created_at IS NULL;

-- Set sane defaults going forward
ALTER TABLE pgz_sport.notifications
  ALTER COLUMN is_read SET DEFAULT false;

-- 2) Index for the notif center query --------------------------------------
CREATE INDEX IF NOT EXISTS idx_notif_user_isread_created
  ON pgz_sport.notifications (user_id, is_read, created_at DESC);

-- 3) Seed demo InApp notifications -----------------------------------------
-- System-wide (user_id NULL) + targeted (user_id=1, super_admin damir).
INSERT INTO pgz_sport.notifications
       (user_id, channel, kind, title, subject, body, link, is_read, status, created_at, scheduled_at)
VALUES
  (NULL, 'inapp', 'info',
   'Dobrodošli u PGŽ Sport',
   'Dobrodošli u PGŽ Sport',
   'Notifikacijski centar je aktiviran. Ovdje ćete dobivati obavijesti o članarinama, liječničkim pregledima, putnim nalozima i auditu.',
   '/app#dashboard',
   false, 'pending', now() - interval '2 hours', now() - interval '2 hours'),

  (1, 'inapp', 'warning',
   'Liječnički pregled ističe za 14 dana',
   'Liječnički pregled ističe za 14 dana',
   'Sportašima u tvom klubu uskoro istječe liječnički — pokreni Scan isteke u CRM modulu.',
   '/crm#lijecnicki',
   false, 'pending', now() - interval '30 minutes', now() - interval '30 minutes'),

  (NULL, 'inapp', 'success',
   'Putni nalog #5 odobren',
   'Putni nalog #5 odobren',
   'Putni nalog za "Rijeka → Zagreb" (2026-05-15 – 2026-05-16) odobren je u iznosu od €116,54.',
   '/erp#putni',
   false, 'pending', now() - interval '5 minutes', now() - interval '5 minutes');

COMMIT;

-- Done.