83 lines
2.9 KiB
PL/PgSQL
83 lines
2.9 KiB
PL/PgSQL
BEGIN;
|
|
|
|
INSERT INTO system_config (key, value, description)
|
|
VALUES ('auth', '{"email_verification_required": true}', '认证功能开关')
|
|
ON CONFLICT (key) DO NOTHING;
|
|
|
|
ALTER TABLE usage_periods
|
|
ADD COLUMN IF NOT EXISTS grant_used_units INTEGER NOT NULL DEFAULT 0;
|
|
|
|
DO $$ BEGIN
|
|
ALTER TABLE usage_periods
|
|
ADD CONSTRAINT usage_periods_grant_used_units_nonnegative
|
|
CHECK (grant_used_units >= 0 AND grant_used_units <= used_units);
|
|
EXCEPTION
|
|
WHEN duplicate_object THEN NULL;
|
|
END $$;
|
|
|
|
CREATE TABLE IF NOT EXISTS redemption_codes (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
code_hash VARCHAR(64) NOT NULL UNIQUE,
|
|
code_hint VARCHAR(32) NOT NULL,
|
|
benefit_kind VARCHAR(16) NOT NULL CHECK (benefit_kind IN ('plan', 'units')),
|
|
plan_id UUID REFERENCES plans(id),
|
|
units INTEGER,
|
|
duration_days INTEGER NOT NULL,
|
|
redeem_before TIMESTAMPTZ,
|
|
is_active BOOLEAN NOT NULL DEFAULT true,
|
|
note TEXT,
|
|
created_by UUID NOT NULL REFERENCES users(id),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
|
|
CHECK (duration_days BETWEEN 1 AND 3650),
|
|
CHECK (
|
|
(benefit_kind = 'plan' AND plan_id IS NOT NULL AND units IS NULL)
|
|
OR
|
|
(benefit_kind = 'units' AND plan_id IS NULL AND units BETWEEN 1 AND 10000000)
|
|
)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_redemption_codes_created_at
|
|
ON redemption_codes(created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_redemption_codes_active_deadline
|
|
ON redemption_codes(is_active, redeem_before);
|
|
|
|
CREATE TABLE IF NOT EXISTS redemption_records (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
code_id UUID NOT NULL UNIQUE REFERENCES redemption_codes(id),
|
|
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
benefit_kind VARCHAR(16) NOT NULL CHECK (benefit_kind IN ('plan', 'units')),
|
|
plan_id UUID REFERENCES plans(id),
|
|
units INTEGER,
|
|
benefit_starts_at TIMESTAMPTZ NOT NULL,
|
|
benefit_expires_at TIMESTAMPTZ NOT NULL,
|
|
redeemed_ip INET,
|
|
redeemed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
|
|
CHECK (benefit_expires_at > benefit_starts_at)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_redemption_records_user_time
|
|
ON redemption_records(user_id, redeemed_at DESC);
|
|
|
|
CREATE TABLE IF NOT EXISTS unit_grants (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
redemption_record_id UUID NOT NULL UNIQUE REFERENCES redemption_records(id) ON DELETE CASCADE,
|
|
total_units INTEGER NOT NULL CHECK (total_units > 0),
|
|
remaining_units INTEGER NOT NULL CHECK (remaining_units >= 0 AND remaining_units <= total_units),
|
|
starts_at TIMESTAMPTZ NOT NULL,
|
|
expires_at TIMESTAMPTZ NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
|
|
CHECK (expires_at > starts_at)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_unit_grants_available
|
|
ON unit_grants(user_id, expires_at, created_at)
|
|
WHERE remaining_units > 0;
|
|
|
|
COMMIT;
|