实战项目：电商 shop 综合建模

CREATE SCHEMA IF NOT EXISTS core;
 
CREATE TABLE core.users (
  id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  username  text NOT NULL,
  email     text NOT NULL UNIQUE,
  phone     text,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
 
CREATE TABLE core.products (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  category   text NOT NULL,
  name       text NOT NULL,
  price      numeric(10,2) NOT NULL CHECK (price >= 0),
  stock      int  NOT NULL DEFAULT 0 CHECK (stock >= 0),
  attrs      jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
 
CREATE TABLE core.orders (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_no    text NOT NULL UNIQUE,
  user_id     bigint NOT NULL REFERENCES core.users(id),
  status      text NOT NULL DEFAULT 'pending'
               CHECK (status IN ('pending','paid','shipped','done','cancelled')),
  total_amount numeric(12,2) NOT NULL DEFAULT 0,
  created_at  timestamptz NOT NULL DEFAULT now()
);
 
CREATE TABLE core.order_items (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_id   bigint NOT NULL REFERENCES core.orders(id) ON DELETE CASCADE,
  product_id bigint NOT NULL REFERENCES core.products(id),
  quantity   int NOT NULL CHECK (quantity > 0),
  price      numeric(10,2) NOT NULL
);

CREATE INDEX idx_orders_user_status ON core.orders (user_id, status);
CREATE INDEX idx_orders_created ON core.orders (created_at);
CREATE INDEX idx_products_cat_price ON core.products (category, price DESC);
CREATE INDEX idx_products_attrs ON core.products USING gin (attrs jsonb_path_ops);

CREATE OR REPLACE FUNCTION core.touch_updated_at()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN NEW.updated_at = now(); RETURN NEW; END; $$;
 
CREATE TRIGGER trg_products_updated
  BEFORE UPDATE ON core.products
  FOR EACH ROW EXECUTE FUNCTION core.touch_updated_at();
 
-- 下单扣库存时，用触发器保证不超卖（库存不足直接报错）
CREATE OR REPLACE FUNCTION core.check_stock()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  IF (SELECT stock FROM core.products WHERE id = NEW.product_id) < NEW.quantity THEN
    RAISE EXCEPTION '库存不足: product %', NEW.product_id;
  END IF;
  UPDATE core.products SET stock = stock - NEW.quantity WHERE id = NEW.product_id;
  RETURN NEW;
END; $$;
 
CREATE TRIGGER trg_order_items_stock
  BEFORE INSERT ON core.order_items
  FOR EACH ROW EXECUTE FUNCTION core.check_stock();

WITH new_orders AS (
  INSERT INTO core.orders (order_no, user_id, status)
  VALUES ('SO' || to_char(now(),'YYYYMMDDHH24MISS'), 1, 'pending')
  RETURNING id
)
INSERT INTO core.order_items (order_id, product_id, quantity, price)
SELECT no.id, 1, 2, (SELECT price FROM core.products WHERE id=1)
FROM new_orders no;

SELECT username,
       SUM(total_amount) FILTER (WHERE status='paid') AS paid_sum,
       RANK() OVER (ORDER BY SUM(total_amount) FILTER (WHERE status='paid') DESC) AS 金额排名
FROM core.users u
JOIN core.orders o ON o.user_id = u.id
GROUP BY username
ORDER BY paid_sum DESC NULLS LAST;