Inventory and warehouse

CREATE TYPE movement_kind AS ENUM ('receipt', 'shipment', 'transfer', 'adjustment');

CREATE TYPE po_status AS ENUM ('draft', 'sent', 'partial', 'received', 'cancelled');

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

CREATE TABLE products (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    sku text NOT NULL UNIQUE,
    name text NOT NULL,
    unit text DEFAULT 'each' NOT NULL,
    reorder_level int DEFAULT 0 NOT NULL
);

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

CREATE TABLE bins (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    warehouse_id uuid NOT NULL,
    label text NOT NULL,
    UNIQUE (warehouse_id, label)
);

CREATE TABLE stock_levels (
    product_id uuid NOT NULL,
    bin_id uuid NOT NULL,
    quantity int DEFAULT 0 NOT NULL,
    PRIMARY KEY (product_id, bin_id),
    CHECK (quantity >= 0)
);
CREATE INDEX stock_levels_bin_id_idx ON stock_levels (bin_id);

CREATE TABLE purchase_orders (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    supplier_id uuid NOT NULL,
    warehouse_id uuid NOT NULL,
    status po_status DEFAULT 'draft' NOT NULL,
    ordered_at timestamptz,
    expected_at date
);
CREATE INDEX purchase_orders_supplier_id_idx ON purchase_orders (supplier_id);
CREATE INDEX purchase_orders_warehouse_id_idx ON purchase_orders (warehouse_id);

CREATE TABLE purchase_order_lines (
    purchase_order_id uuid NOT NULL,
    product_id uuid NOT NULL,
    quantity int NOT NULL,
    unit_cost numeric(12,4) NOT NULL,
    PRIMARY KEY (purchase_order_id, product_id),
    CHECK (quantity > 0)
);
CREATE INDEX purchase_order_lines_product_id_idx ON purchase_order_lines (product_id);

CREATE TABLE stock_movements (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    product_id uuid NOT NULL,
    from_bin_id uuid,
    to_bin_id uuid,
    kind movement_kind NOT NULL,
    quantity int NOT NULL,
    moved_at timestamptz DEFAULT now() NOT NULL,
    CHECK (quantity > 0)
);
CREATE INDEX stock_movements_product_idx ON stock_movements (product_id, moved_at);
CREATE INDEX stock_movements_from_bin_id_idx ON stock_movements (from_bin_id);
CREATE INDEX stock_movements_to_bin_id_idx ON stock_movements (to_bin_id);

ALTER TABLE bins ADD FOREIGN KEY (warehouse_id) REFERENCES warehouses (id) ON DELETE CASCADE;

ALTER TABLE stock_levels ADD FOREIGN KEY (product_id) REFERENCES products (id) ON DELETE CASCADE;

ALTER TABLE stock_levels ADD FOREIGN KEY (bin_id) REFERENCES bins (id) ON DELETE CASCADE;

ALTER TABLE purchase_orders ADD FOREIGN KEY (supplier_id) REFERENCES suppliers (id);

ALTER TABLE purchase_orders ADD FOREIGN KEY (warehouse_id) REFERENCES warehouses (id);

ALTER TABLE purchase_order_lines ADD FOREIGN KEY (purchase_order_id) REFERENCES purchase_orders (id) ON DELETE CASCADE;

ALTER TABLE purchase_order_lines ADD FOREIGN KEY (product_id) REFERENCES products (id);

ALTER TABLE stock_movements ADD FOREIGN KEY (product_id) REFERENCES products (id);

ALTER TABLE stock_movements ADD FOREIGN KEY (from_bin_id) REFERENCES bins (id);

ALTER TABLE stock_movements ADD FOREIGN KEY (to_bin_id) REFERENCES bins (id);