-- Add constraint to prevent bids on inactive listings
-- This ensures atomicity at the database level

-- Create a trigger to prevent bids on inactive listings
-- This is more reliable than CHECK constraints in SQLite
CREATE TRIGGER prevent_bid_on_inactive_listing
    BEFORE INSERT ON bids
    FOR EACH ROW
    WHEN (
        SELECT COALESCE(is_active, 0) FROM listings WHERE id = NEW.listing_id
    ) != 1
BEGIN
    SELECT RAISE(ABORT, 'Cannot place bid on inactive listing');
END;