max / makenotwork

2.4 KB · 56 lines History Blame Raw

1	-- Unified promo_codes table replacing discount_codes + download_codes.
2	-- Supports three code purposes:
3	-- discount — percentage or fixed price reduction
4	-- free_access — grants free access to item (replaces download_codes)
5	-- free_trial — N days free on a subscription tier
6
7	CREATE TABLE promo_codes (
8	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
9	creator_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
10	code TEXT NOT NULL,
11	code_purpose TEXT NOT NULL CHECK (code_purpose IN ('discount', 'free_access', 'free_trial')),
12	-- Discount fields (required when purpose = 'discount')
13	discount_type TEXT CHECK (discount_type IN ('percentage', 'fixed')),
14	discount_value INT,
15	min_price_cents INT NOT NULL DEFAULT 0,
16	-- Trial fields (required when purpose = 'free_trial')
17	trial_days INT,
18	-- Scope
19	item_id UUID REFERENCES items(id) ON DELETE CASCADE,
20	project_id UUID REFERENCES projects(id) ON DELETE CASCADE,
21	tier_id UUID REFERENCES subscription_tiers(id) ON DELETE CASCADE,
22	-- Usage
23	max_uses INT,
24	use_count INT NOT NULL DEFAULT 0,
25	expires_at TIMESTAMPTZ,
26	created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
27	-- Integrity
28	CONSTRAINT chk_discount_fields CHECK (
29	code_purpose != 'discount' OR (discount_type IS NOT NULL AND discount_value > 0)
30	),
31	CONSTRAINT chk_trial_fields CHECK (
32	code_purpose != 'free_trial' OR (trial_days IS NOT NULL AND trial_days > 0)
33	)
34	);
35
36	CREATE UNIQUE INDEX idx_promo_codes_creator_code ON promo_codes(creator_id, upper(code));
37	CREATE INDEX idx_promo_codes_item ON promo_codes(item_id);
38	CREATE INDEX idx_promo_codes_project ON promo_codes(project_id);
39	CREATE INDEX idx_promo_codes_tier ON promo_codes(tier_id);
40
41	-- Migrate existing data
42	INSERT INTO promo_codes (id, creator_id, code, code_purpose, discount_type, discount_value,
43	min_price_cents, item_id, project_id, max_uses, use_count, expires_at, created_at)
44	SELECT id, seller_id, code, 'discount', discount_type, discount_value,
45	min_price_cents, item_id, project_id, max_uses, use_count, expires_at, created_at
46	FROM discount_codes;
47
48	INSERT INTO promo_codes (id, creator_id, code, code_purpose, item_id, max_uses,
49	use_count, expires_at, created_at)
50	SELECT id, created_by_id, code, 'free_access', item_id, max_uses,
51	use_count, expires_at, created_at
52	FROM download_codes;
53
54	DROP TABLE download_codes;
55	DROP TABLE discount_codes;
56