TechShop — সম্পূর্ণ schema (শিক্ষার project)

CREATE TABLE categories (
  id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name  text NOT NULL UNIQUE
);

CREATE TABLE users (
  id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text NOT NULL UNIQUE,
  name  text NOT NULL,
  role  text NOT NULL DEFAULT 'customer'
);

CREATE TABLE products (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  category_id bigint REFERENCES categories(id),
  name        text NOT NULL,
  price       numeric(10,2) NOT NULL,
  qty         integer NOT NULL DEFAULT 0,
  spec        jsonb
);

CREATE TABLE orders (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id    bigint NOT NULL REFERENCES users(id),
  total      numeric(10,2) NOT NULL DEFAULT 0,
  status     text NOT NULL DEFAULT 'pending',
  created_at timestamptz DEFAULT now()
);

CREATE TABLE order_items (
  order_id   bigint NOT NULL REFERENCES orders(id),
  product_id bigint NOT NULL REFERENCES products(id),
  qty        integer NOT NULL,
  price      numeric(10,2) NOT NULL,
  PRIMARY KEY (order_id, product_id)
);