33 lines
1.5 KiB
SQL
33 lines
1.5 KiB
SQL
-- 006: Coupons table (issuer-service)
|
|
CREATE TABLE IF NOT EXISTS coupons (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
chain_token_id BIGINT UNIQUE,
|
|
issuer_id UUID NOT NULL REFERENCES issuers(id),
|
|
name VARCHAR(200) NOT NULL,
|
|
description TEXT,
|
|
image_url VARCHAR(500),
|
|
face_value NUMERIC(12,2) NOT NULL CHECK (face_value > 0),
|
|
current_price NUMERIC(12,2),
|
|
issue_price NUMERIC(12,2),
|
|
total_supply INTEGER NOT NULL DEFAULT 1,
|
|
remaining_supply INTEGER NOT NULL DEFAULT 1,
|
|
expiry_date DATE NOT NULL,
|
|
coupon_type VARCHAR(10) NOT NULL DEFAULT 'utility' CHECK (coupon_type IN ('utility', 'security')),
|
|
category VARCHAR(50),
|
|
status VARCHAR(20) NOT NULL DEFAULT 'minted' CHECK (status IN ('minted', 'listed', 'sold', 'in_circulation', 'redeemed', 'expired', 'recalled')),
|
|
owner_user_id UUID REFERENCES users(id),
|
|
resale_count SMALLINT NOT NULL DEFAULT 0,
|
|
max_resale_count SMALLINT NOT NULL DEFAULT 3,
|
|
is_transferable BOOLEAN NOT NULL DEFAULT true,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX idx_coupons_issuer_id ON coupons(issuer_id);
|
|
CREATE INDEX idx_coupons_status ON coupons(status);
|
|
CREATE INDEX idx_coupons_coupon_type ON coupons(coupon_type);
|
|
CREATE INDEX idx_coupons_category ON coupons(category);
|
|
CREATE INDEX idx_coupons_owner_user_id ON coupons(owner_user_id);
|
|
CREATE INDEX idx_coupons_expiry_date ON coupons(expiry_date);
|
|
CREATE INDEX idx_coupons_name_trgm ON coupons USING gin (name gin_trgm_ops);
|