damir
f7b5114f58
nginx (sport.rinet.one): - proxy_read_timeout 60s → 300s - proxy_send_timeout 300s - proxy_buffering off (PDF stream) - client_max_body_size 50M → 100M Endpoints: - /api/v2/klubovi/financirani: +with_data filter (samo s potporama/godišnjakom/HNS) - /api/v2/sportasi/filtered: +samo_priority +samo_s_hns Frontend: - PDF link target=_blank rel=noopener - window._klub_only_priority = true (default) - window._sportas_only_priority = true (default) DB View: - pgz_sport.v_nogomet_priority (prima_potpore, u_godisnjaku, ima_hns_roster)
79 lines
3.1 KiB
PL/PgSQL
79 lines
3.1 KiB
PL/PgSQL
-- ============================================================================
|
||
-- 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.
|