E-commerce store

CREATE TYPE order_status AS ENUM ('pending', 'paid', 'shipped', 'delivered', 'cancelled', 'refunded');

CREATE TABLE customers (
    id bigserial PRIMARY KEY,
    email text NOT NULL UNIQUE,
    full_name text NOT NULL,
    created_at timestamptz DEFAULT now() NOT NULL
);

CREATE TABLE addresses (
    id bigserial PRIMARY KEY,
    customer_id bigint NOT NULL,
    line1 text NOT NULL,
    line2 text,
    city text NOT NULL,
    region text,
    postal_code text NOT NULL,
    country_code char(2) NOT NULL
);

CREATE TABLE categories (
    id serial PRIMARY KEY,
    parent_id integer,
    name text NOT NULL,
    slug text NOT NULL UNIQUE
);

CREATE TABLE products (
    id bigserial PRIMARY KEY,
    category_id integer,
    sku text NOT NULL UNIQUE,
    name text NOT NULL,
    description text,
    price_cents integer NOT NULL,
    active boolean DEFAULT true NOT NULL,
    CHECK (price_cents >= 0)
);
CREATE INDEX products_category_idx ON products (category_id);

CREATE TABLE orders (
    id bigserial PRIMARY KEY,
    customer_id bigint NOT NULL,
    shipping_address_id bigint,
    status order_status DEFAULT 'pending' NOT NULL,
    total_cents integer DEFAULT 0 NOT NULL,
    placed_at timestamptz DEFAULT now() NOT NULL
);
CREATE INDEX orders_customer_idx ON orders (customer_id, placed_at DESC);

CREATE TABLE order_items (
    order_id bigint NOT NULL,
    product_id bigint NOT NULL,
    quantity integer NOT NULL,
    unit_price_cents integer NOT NULL,
    PRIMARY KEY (order_id, product_id),
    CHECK (quantity > 0)
);

CREATE TABLE payments (
    id bigserial PRIMARY KEY,
    order_id bigint NOT NULL,
    provider text NOT NULL,
    provider_ref text NOT NULL,
    amount_cents integer NOT NULL,
    paid_at timestamptz
);

ALTER TABLE addresses ADD FOREIGN KEY (customer_id) REFERENCES customers (id) ON DELETE CASCADE;

ALTER TABLE categories ADD FOREIGN KEY (parent_id) REFERENCES categories (id) ON DELETE SET NULL;

ALTER TABLE products ADD FOREIGN KEY (category_id) REFERENCES categories (id) ON DELETE SET NULL;

ALTER TABLE orders ADD FOREIGN KEY (customer_id) REFERENCES customers (id);

ALTER TABLE orders ADD FOREIGN KEY (shipping_address_id) REFERENCES addresses (id);

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

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

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