Restaurant ordering

CREATE TYPE order_status AS ENUM ('open', 'sent', 'served', 'paid', 'void');

CREATE TABLE menu_categories (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    name text NOT NULL,
    position int DEFAULT 0 NOT NULL
);

CREATE TABLE menu_items (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    category_id uuid NOT NULL,
    name text NOT NULL,
    price numeric(8,2) NOT NULL,
    available boolean DEFAULT true NOT NULL,
    CHECK (price >= 0)
);
CREATE INDEX menu_items_category_id_idx ON menu_items (category_id);

CREATE TABLE modifiers (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    menu_item_id uuid NOT NULL,
    name text NOT NULL,
    extra_price numeric(8,2) DEFAULT 0 NOT NULL
);
CREATE INDEX modifiers_menu_item_id_idx ON modifiers (menu_item_id);

CREATE TABLE dining_tables (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    label text NOT NULL UNIQUE,
    seats int NOT NULL
);

CREATE TABLE orders (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    table_id uuid,
    status order_status DEFAULT 'open' NOT NULL,
    opened_at timestamptz DEFAULT now() NOT NULL,
    closed_at timestamptz
);
CREATE INDEX orders_table_id_idx ON orders (table_id);

CREATE TABLE order_lines (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    order_id uuid NOT NULL,
    menu_item_id uuid NOT NULL,
    quantity int NOT NULL,
    unit_price numeric(8,2) NOT NULL,
    note text,
    CHECK (quantity > 0)
);
CREATE INDEX order_lines_order_idx ON order_lines (order_id);
CREATE INDEX order_lines_menu_item_id_idx ON order_lines (menu_item_id);

CREATE TABLE payments (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    order_id uuid NOT NULL,
    amount numeric(10,2) NOT NULL,
    method text NOT NULL,
    paid_at timestamptz DEFAULT now() NOT NULL
);
CREATE INDEX payments_order_id_idx ON payments (order_id);

ALTER TABLE menu_items ADD FOREIGN KEY (category_id) REFERENCES menu_categories (id);

ALTER TABLE modifiers ADD FOREIGN KEY (menu_item_id) REFERENCES menu_items (id) ON DELETE CASCADE;

ALTER TABLE orders ADD FOREIGN KEY (table_id) REFERENCES dining_tables (id) ON DELETE SET NULL;

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

ALTER TABLE order_lines ADD FOREIGN KEY (menu_item_id) REFERENCES menu_items (id);

ALTER TABLE payments ADD FOREIGN KEY (order_id) REFERENCES orders (id);