Multi-vendor marketplace

CREATE TYPE payout_status AS ENUM ('pending', 'paid', 'failed');

CREATE TABLE vendors (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    name text NOT NULL,
    email text NOT NULL UNIQUE,
    commission_percent numeric(4,1) DEFAULT 10 NOT NULL
);

CREATE TABLE buyers (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    email text NOT NULL UNIQUE,
    name text NOT NULL
);

CREATE TABLE listings (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    vendor_id uuid NOT NULL,
    title text NOT NULL,
    description text,
    price numeric(10,2) NOT NULL,
    stock int DEFAULT 0 NOT NULL,
    active boolean DEFAULT true NOT NULL,
    CHECK (price >= 0)
);
CREATE INDEX listings_vendor_id_idx ON listings (vendor_id);

CREATE TABLE orders (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    buyer_id uuid NOT NULL,
    placed_at timestamptz DEFAULT now() NOT NULL,
    total numeric(10,2) NOT NULL
);
CREATE INDEX orders_buyer_id_idx ON orders (buyer_id);

CREATE TABLE order_items (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    order_id uuid NOT NULL,
    listing_id uuid NOT NULL,
    vendor_id uuid NOT NULL,
    quantity int NOT NULL,
    unit_price numeric(10,2) NOT NULL,
    CHECK (quantity > 0)
);
CREATE INDEX order_items_vendor_idx ON order_items (vendor_id);
CREATE INDEX order_items_order_id_idx ON order_items (order_id);
CREATE INDEX order_items_listing_id_idx ON order_items (listing_id);

CREATE TABLE payouts (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    vendor_id uuid NOT NULL,
    amount numeric(12,2) NOT NULL,
    status payout_status DEFAULT 'pending' NOT NULL,
    created_at timestamptz DEFAULT now() NOT NULL
);
CREATE INDEX payouts_vendor_id_idx ON payouts (vendor_id);

CREATE TABLE reviews (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    listing_id uuid NOT NULL,
    buyer_id uuid NOT NULL,
    rating int NOT NULL,
    body text,
    UNIQUE (listing_id, buyer_id),
    CHECK (rating between 1 and 5)
);
CREATE INDEX reviews_buyer_id_idx ON reviews (buyer_id);

ALTER TABLE listings ADD FOREIGN KEY (vendor_id) REFERENCES vendors (id) ON DELETE CASCADE;

ALTER TABLE orders ADD FOREIGN KEY (buyer_id) REFERENCES buyers (id);

ALTER TABLE order_items ADD FOREIGN KEY (order_id) REFERENCES orders (id) ON DELETE CASCADE;

ALTER TABLE order_items ADD FOREIGN KEY (listing_id) REFERENCES listings (id);

ALTER TABLE order_items ADD FOREIGN KEY (vendor_id) REFERENCES vendors (id);

ALTER TABLE payouts ADD FOREIGN KEY (vendor_id) REFERENCES vendors (id);

ALTER TABLE reviews ADD FOREIGN KEY (listing_id) REFERENCES listings (id) ON DELETE CASCADE;

ALTER TABLE reviews ADD FOREIGN KEY (buyer_id) REFERENCES buyers (id) ON DELETE CASCADE;