42 lines
1.9 KiB
SQL
42 lines
1.9 KiB
SQL
-- Occasional gift rewards, granted from the admin panel.
|
|
--
|
|
-- A gift IS a reward grant — same catalog, same gallery, same vault — so it
|
|
-- reuses sadhna_reward_grants rather than introducing a parallel table that
|
|
-- every reader would then have to union in. What differs is only where it came
|
|
-- from, which `source` records.
|
|
--
|
|
-- `celebrated_at` already means "the devotee has seen this", which is exactly
|
|
-- the claim flag a gift needs: unclaimed gifts render blurred, and dismissing
|
|
-- the celebration is what clears them.
|
|
ALTER TABLE sadhna_reward_grants
|
|
ADD COLUMN IF NOT EXISTS source VARCHAR(8) NOT NULL DEFAULT 'streak';
|
|
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'sadhna_reward_grants_source_check') THEN
|
|
ALTER TABLE sadhna_reward_grants
|
|
ADD CONSTRAINT sadhna_reward_grants_source_check
|
|
CHECK (source IN ('streak', 'gift'));
|
|
END IF;
|
|
END $$;
|
|
|
|
-- A gift is not tied to a streak day, so it has no milestone.
|
|
ALTER TABLE sadhna_reward_grants ALTER COLUMN milestone DROP NOT NULL;
|
|
|
|
-- UNIQUE (user_id, milestone) was the streak rule: one reward per milestone.
|
|
-- It can't police gifts, which have no milestone — so it becomes a partial
|
|
-- index over streak rows, and gifts get their own rule: one of each reward
|
|
-- per person, so re-gifting is idempotent rather than a duplicate tile.
|
|
ALTER TABLE sadhna_reward_grants
|
|
DROP CONSTRAINT IF EXISTS sadhna_reward_grants_user_id_milestone_key;
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS sadhna_reward_grants_streak_uniq
|
|
ON sadhna_reward_grants (user_id, milestone) WHERE source = 'streak';
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS sadhna_reward_grants_gift_uniq
|
|
ON sadhna_reward_grants (user_id, reward_id) WHERE source = 'gift';
|
|
|
|
-- "Which gifts is this devotee still to open?" — the blurred tiles.
|
|
CREATE INDEX IF NOT EXISTS sadhna_reward_grants_unopened_idx
|
|
ON sadhna_reward_grants (user_id) WHERE celebrated_at IS NULL;
|