Hands-on Tutorial: E-commerce Mini Program

-- ═══════════════════════════════════════════════════
-- 商品表
-- ═══════════════════════════════════════════════════
CREATE TABLE public.products (
    id          serial        PRIMARY KEY,
    name        text          NOT NULL,
    description text,
    price       numeric(10,2) NOT NULL,
    stock       int           DEFAULT 0,
    category    text,
    is_active   boolean       DEFAULT true,      -- true=上架, false=下架
    created_at  timestamptz   DEFAULT now()
);

-- ═══════════════════════════════════════════════════
-- 订单表
-- ═══════════════════════════════════════════════════
CREATE TABLE public.orders (
    id           serial        PRIMARY KEY,
    product_id   int           NOT NULL REFERENCES public.products(id),
    -- ⭐ buyer_id 自动从 JWT 中获取当前用户的 sub（auth.users.id 类型为 varchar(64)）
    buyer_id     varchar(64)   NOT NULL
                   DEFAULT (current_setting('request.jwt.claims', true)::json->>'sub'),
    quantity     int           NOT NULL DEFAULT 1,
    total_price  numeric(10,2) NOT NULL,
    status       text          NOT NULL DEFAULT 'pending',  -- pending → paid → shipped → completed
    address      jsonb,                                     -- 收货地址（结构化 JSON）
    created_at   timestamptz   DEFAULT now(),
    updated_at   timestamptz   DEFAULT now()
);

CREATE INDEX idx_orders_buyer_id ON public.orders(buyer_id);

ALTER TABLE public.products ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.orders   ENABLE ROW LEVEL SECURITY;

-- ═══════════════════════════════════════════════════
-- products（商品表）— 所有人可读，仅管理员可写
-- ═══════════════════════════════════════════════════

-- 匿名用户：只能查看
GRANT SELECT ON public.products TO anon;

-- 已认证用户：也只能查看（普通用户不能操作商品）
GRANT SELECT ON public.products TO authenticated;

-- 管理员：全部权限（BYPASSRLS 自动绕过 RLS）
GRANT ALL ON public.products TO service_role;
GRANT USAGE, SELECT ON SEQUENCE public.products_id_seq TO service_role;


-- ═══════════════════════════════════════════════════
-- orders（订单表）— 买家可查可下单，不可改不可删
-- ═══════════════════════════════════════════════════

-- 已认证用户：可查看 + 可下单，但不能修改和删除
GRANT SELECT, INSERT ON public.orders TO authenticated;
GRANT USAGE, SELECT ON SEQUENCE public.orders_id_seq TO authenticated;

-- 管理员：全部权限
GRANT ALL ON public.orders TO service_role;
GRANT USAGE, SELECT ON SEQUENCE public.orders_id_seq TO service_role;

-- 匿名用户：不授予 orders 任何权限（未登录不能操作订单）

-- ═══════════════════════════════════════════════════
-- products 的 RLS Policy
-- ═══════════════════════════════════════════════════

-- 所有人只能看到上架商品（is_active = true）
CREATE POLICY products_select
    ON public.products
    FOR SELECT
    USING (is_active = true);

-- ⚠️ 不需要创建 INSERT/UPDATE/DELETE 的 Policy
-- 因为 anon 和 authenticated 在表级权限上就没有写权限
-- service_role 拥有 BYPASSRLS，自动绕过所有 Policy


-- ═══════════════════════════════════════════════════
-- orders 的 RLS Policy
-- ═══════════════════════════════════════════════════

-- SELECT: 买家只能看到自己的订单
CREATE POLICY orders_select
    ON public.orders
    FOR SELECT
    TO authenticated
    USING (
        buyer_id = (current_setting('request.jwt.claims', true)::json->>'sub')
    );

-- INSERT: 买家下单时，buyer_id 必须是自己
CREATE POLICY orders_insert
    ON public.orders
    FOR INSERT
    TO authenticated
    WITH CHECK (
        buyer_id = (current_setting('request.jwt.claims', true)::json->>'sub')
    );

-- ⚠️ 故意不给 authenticated 创建 UPDATE/DELETE Policy
-- 配合表级权限（也没授予 UPDATE/DELETE），实现"订单不可改"的业务规则

-- 先删子表，再删父表（CASCADE 会自动删除关联的 Policy）
DROP TABLE IF EXISTS public.orders   CASCADE;
DROP TABLE IF EXISTS public.products CASCADE;