-- Products
CREATE TABLE products
(
    product_id     BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 1000) PRIMARY KEY,
    version        BIGINT         NOT NULL DEFAULT 0,
    name           VARCHAR(100)   NOT NULL,
    description    VARCHAR(500)   NOT NULL,
    category       VARCHAR(100)   NOT NULL,
    brand          VARCHAR(100),
    sku            VARCHAR(50)    NOT NULL UNIQUE,
    stock_quantity INT            NOT NULL DEFAULT 0 CHECK (stock_quantity >= 0),
    reorder_level  INT            NOT NULL DEFAULT 5 CHECK (reorder_level >= 0),
    supplier       VARCHAR(150),
    weight_grams   DECIMAL(8, 2),
    dimensions_cm  VARCHAR(50),
    color          VARCHAR(50),
    release_date   DATE,
    price          DECIMAL(12, 2) NOT NULL CHECK (price >= 0),
    discount       DECIMAL(5, 2)           DEFAULT 0 CHECK (discount >= 0 AND discount <= 100),
    rating         DECIMAL(3, 2)           DEFAULT 0 CHECK (rating >= 0 AND rating <= 5),
    active         BOOLEAN        NOT NULL DEFAULT TRUE
);
