本來想除了管理功能外全部都在Line介面裡面解決，但做了一陣子覺得越想越不對勁，重新考量了一下思路，所以今天鴿了，說是這麼說，但還是拉了幾個表格，分別是產品(products)、購物車(shopping_cart)、購物車明細(cart_items)，說一下現在的流程想法

CREATE TABLE IF NOT EXISTS public.shopping_cart
(
    scid bigint NOT NULL DEFAULT nextval('shopping_cart_scid_seq'::regclass),
    uid text COLLATE pg_catalog."default" NOT NULL,
    createddate timestamp with time zone,
    CONSTRAINT shopping_cart_pkey PRIMARY KEY (scid),
    CONSTRAINT uid FOREIGN KEY (uid)
        REFERENCES public.customers (uid) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)

CREATE TABLE IF NOT EXISTS public.cart_items
(
    id bigint NOT NULL DEFAULT nextval('cart_items_id_seq'::regclass),
    scid bigint NOT NULL,
    productid integer,
    quantity integer,
    CONSTRAINT cart_items_pkey PRIMARY KEY (id),
    CONSTRAINT pid FOREIGN KEY (productid)
        REFERENCES public.products (pid) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        NOT VALID,
    CONSTRAINT scid FOREIGN KEY (scid)
        REFERENCES public.shopping_cart (scid) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)

CREATE TABLE IF NOT EXISTS public.products
(
    pid integer NOT NULL DEFAULT nextval('products_pid_seq'::regclass),
    product_name text COLLATE pg_catalog."default" NOT NULL,
    quantity integer NOT NULL,
    product_decp text COLLATE pg_catalog."default",
    createddate timestamp with time zone,
    expireddate timestamp with time zone,
    CONSTRAINT products_pkey PRIMARY KEY (pid)
)

CREATE TABLE IF NOT EXISTS public.orders
(
    oid bigint NOT NULL DEFAULT nextval('orders_oid_seq'::regclass),
    uid text COLLATE pg_catalog."default" NOT NULL,
    scid bigint NOT NULL,
    createddate timestamp with time zone,
    paid bigint NOT NULL,
    ostatus integer NOT NULL DEFAULT 0,
    CONSTRAINT orders_pkey PRIMARY KEY (oid),
    CONSTRAINT paid FOREIGN KEY (paid)
        REFERENCES public.payment_log (paid) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        NOT VALID,
    CONSTRAINT scid FOREIGN KEY (scid)
        REFERENCES public.shopping_cart (scid) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT uid FOREIGN KEY (uid)
        REFERENCES public.customers (uid) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)

CREATE TABLE IF NOT EXISTS public.payment_log
(
    paid bigint NOT NULL DEFAULT nextval('payment_log_paid_seq'::regclass),
    type text COLLATE pg_catalog."default",
    ispaid boolean DEFAULT false,
    CONSTRAINT payment_log_pkey PRIMARY KEY (paid)
)

建立訂單資料庫流程

1. 建立購物車shopping_cart紀錄
2. 依據products內產品類別、庫存加入購物車紀錄到cart_items
3. 使用者點選建立訂單時，payment_log建立付款紀錄
4. 付款成功後，訂單所消耗的庫存反映到products
5. 變更orders訂單狀態為待出貨
6. 出貨後變更訂單狀態為已出貨
7. 下次使用者進行新增購物車時，分配一個新的購物車

原本想說購物車與購物明細放同一個表格，但實作到一半測試起來礙手礙腳，要檢索內容，再更新，於是拉倒重新設計資料庫，改為一個購物車ID，再用這個購物車ID連結購物車明細表格，這樣購物車的內容與購物車本身分開紀錄，更容易修改內容，訂單與付款紀錄也是差不多的原因拆成兩半，今天先把大致上的流程盤點一遍確認，之後再滾動修正0rz