CREATE TABLE game_prizes (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    game_id UUID NOT NULL,
    name VARCHAR(255) NOT NULL,
    weight INTEGER NOT NULL,
    stock INTEGER DEFAULT 0,
    max_stock INTEGER,
    threshold BIGINT,
    fallback_prize_id UUID,
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    
    CONSTRAINT fk_game_prizes_game
        FOREIGN KEY (game_id)
        REFERENCES games(id)
        ON DELETE CASCADE,
    
    CONSTRAINT fk_game_prizes_fallback
        FOREIGN KEY (fallback_prize_id)
        REFERENCES game_prizes(id)
        ON DELETE SET NULL,
    
    CONSTRAINT chk_game_prizes_weight_positive
        CHECK (weight > 0),
    
    CONSTRAINT chk_game_prizes_stock_non_negative
        CHECK (stock >= 0),
    
    CONSTRAINT chk_game_prizes_max_stock_positive
        CHECK (max_stock IS NULL OR max_stock > 0),
    
    CONSTRAINT chk_game_prizes_threshold_non_negative
        CHECK (threshold IS NULL OR threshold >= 0),
    
    CONSTRAINT chk_game_prizes_stock_not_exceed_max
        CHECK (max_stock IS NULL OR stock <= max_stock)
);

-- Create indexes
CREATE INDEX idx_game_prizes_game_id ON game_prizes(game_id);
CREATE INDEX idx_game_prizes_weight ON game_prizes(weight);
CREATE INDEX idx_game_prizes_stock ON game_prizes(stock);
CREATE INDEX idx_game_prizes_fallback_prize_id ON game_prizes(fallback_prize_id);
CREATE INDEX idx_game_prizes_created_at ON game_prizes(created_at);