Spaces:
Sleeping
Sleeping
File size: 1,492 Bytes
f0c0e92 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 | -- Migration: payment + participant columns
-- Run against the configured PostgreSQL. Idempotent: every statement is
-- safe to re-run.
ALTER TABLE payments ADD COLUMN IF NOT EXISTS currency TEXT DEFAULT 'NPR';
ALTER TABLE payments ADD COLUMN IF NOT EXISTS esewa_ref_id TEXT;
ALTER TABLE payments ADD COLUMN IF NOT EXISTS raw_response JSONB;
ALTER TABLE payments ADD COLUMN IF NOT EXISTS verified_at TIMESTAMPTZ;
ALTER TABLE payments ADD COLUMN IF NOT EXISTS created_at TIMESTAMPTZ DEFAULT now();
ALTER TABLE payments ALTER COLUMN status SET DEFAULT 'pending';
CREATE INDEX IF NOT EXISTS idx_payments_user ON payments (user_id);
CREATE INDEX IF NOT EXISTS idx_payments_tournament ON payments (tournament_id);
CREATE INDEX IF NOT EXISTS idx_payments_user_tournament_status
ON payments (user_id, tournament_id, status);
ALTER TABLE tournament_participants
ADD COLUMN IF NOT EXISTS status TEXT DEFAULT 'pending';
ALTER TABLE tournament_participants
ADD COLUMN IF NOT EXISTS payment_pid TEXT;
ALTER TABLE tournament_participants
ADD COLUMN IF NOT EXISTS joined_at TIMESTAMPTZ;
CREATE UNIQUE INDEX IF NOT EXISTS uq_tournament_participant_per_tournament
ON tournament_participants (tournament_id, user_id);
CREATE OR REPLACE VIEW v_user_payments AS
SELECT
p.pid,
p.user_id,
p.tournament_id,
t.title AS tournament_title,
p.amount,
p.currency,
p.status,
p.esewa_ref_id,
p.created_at,
p.verified_at
FROM payments p
LEFT JOIN tournaments t ON t.id = p.tournament_id;
|