-- 0012_multi_item_orders.sql

CREATE TABLE IF NOT EXISTS order_items (
    id BIGSERIAL PRIMARY KEY,
    transaction_id VARCHAR NOT NULL REFERENCES orders(transaction_id) ON DELETE CASCADE,
    product_id VARCHAR NOT NULL,
    product_name VARCHAR NOT NULL,
    quantity INTEGER NOT NULL DEFAULT 1,
    price_at_checkout DOUBLE PRECISION NOT NULL,
    weight_at_checkout DOUBLE PRECISION NOT NULL DEFAULT 0.0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_order_items_transaction ON order_items(transaction_id);