实战项目：电商数据库设计

-- 创建数据库
CREATE DATABASE ecommerce;
\c ecommerce

-- ============================
-- 1. 用户模块
-- ============================
CREATE TABLE es_users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(200) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    phone VARCHAR(20),
    avatar_url TEXT,
    member_level VARCHAR(10) DEFAULT '普通' 
        CHECK (member_level IN ('普通', '银卡', '金卡', '钻石')),
    status VARCHAR(10) DEFAULT 'active'
        CHECK (status IN ('active', 'locked', 'deleted')),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- 用户地址表
CREATE TABLE es_user_addresses (
    address_id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES es_users(user_id),
    receiver_name VARCHAR(50) NOT NULL,
    receiver_phone VARCHAR(20) NOT NULL,
    province VARCHAR(50),
    city VARCHAR(50),
    district VARCHAR(50),
    detail_address TEXT NOT NULL,
    is_default BOOLEAN DEFAULT false,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- ============================
-- 2. 商品模块
-- ============================
CREATE TABLE es_categories (
    category_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    parent_id INTEGER REFERENCES es_categories(category_id),
    sort_order INTEGER DEFAULT 0,
    is_active BOOLEAN DEFAULT true
);

CREATE TABLE es_products (
    product_id SERIAL PRIMARY KEY,
    category_id INTEGER REFERENCES es_categories(category_id),
    name VARCHAR(200) NOT NULL,
    description TEXT,
    price NUMERIC(10,2) NOT NULL CHECK (price > 0),
    stock INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0),
    sales_count INTEGER DEFAULT 0,
    rating NUMERIC(2,1) DEFAULT 0 CHECK (rating >= 0 AND rating <= 5),
    status VARCHAR(10) DEFAULT 'online'
        CHECK (status IN ('online', 'offline', 'deleted')),
    attributes JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE es_product_images (
    image_id SERIAL PRIMARY KEY,
    product_id INTEGER NOT NULL REFERENCES es_products(product_id),
    url TEXT NOT NULL,
    sort_order SMALLINT DEFAULT 0,
    is_cover BOOLEAN DEFAULT false
);

-- 商品 SKU（库存单位）
CREATE TABLE es_product_skus (
    sku_id SERIAL PRIMARY KEY,
    product_id INTEGER NOT NULL REFERENCES es_products(product_id),
    spec JSONB NOT NULL DEFAULT '{}',  -- e.g. {"color": "黑色", "size": "42"}
    price NUMERIC(10,2) NOT NULL,
    stock INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0),
    sku_code VARCHAR(50) UNIQUE
);

-- ============================
-- 3. 购物车模块
-- ============================
CREATE TABLE es_cart_items (
    cart_id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES es_users(user_id),
    product_id INTEGER NOT NULL REFERENCES es_products(product_id),
    sku_id INTEGER REFERENCES es_product_skus(sku_id),
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE (user_id, sku_id)
);

-- ============================
-- 4. 订单模块
-- ============================
CREATE TABLE es_orders (
    order_id SERIAL PRIMARY KEY,
    order_no VARCHAR(50) UNIQUE NOT NULL,
    user_id INTEGER NOT NULL REFERENCES es_users(user_id),
    address_id INTEGER REFERENCES es_user_addresses(address_id),
    total_amount NUMERIC(12,2) NOT NULL,
    payment_amount NUMERIC(12,2) NOT NULL,
    discount_amount NUMERIC(12,2) DEFAULT 0,
    status VARCHAR(20) DEFAULT 'pending_payment'
        CHECK (status IN ('pending_payment', 'paid', 'shipped', 'delivered', 'completed', 'cancelled', 'refunding', 'refunded')),
    payment_method VARCHAR(20),
    payment_time TIMESTAMPTZ,
    shipping_time TIMESTAMPTZ,
    delivery_time TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- 订单明细
CREATE TABLE es_order_items (
    item_id SERIAL PRIMARY KEY,
    order_id INTEGER NOT NULL REFERENCES es_orders(order_id),
    product_id INTEGER NOT NULL REFERENCES es_products(product_id),
    sku_id INTEGER REFERENCES es_product_skus(sku_id),
    product_name VARCHAR(200) NOT NULL,
    spec_desc VARCHAR(200),
    unit_price NUMERIC(10,2) NOT NULL,
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    subtotal NUMERIC(12,2) NOT NULL
);

-- ============================
-- 5. 评价模块
-- ============================
CREATE TABLE es_reviews (
    review_id SERIAL PRIMARY KEY,
    order_id INTEGER NOT NULL REFERENCES es_orders(order_id),
    product_id INTEGER NOT NULL REFERENCES es_products(product_id),
    user_id INTEGER NOT NULL REFERENCES es_users(user_id),
    rating INTEGER NOT NULL CHECK (rating >= 1 AND rating <= 5),
    content TEXT,
    images TEXT[],
    is_anonymous BOOLEAN DEFAULT false,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 创建订单号生成函数
CREATE OR REPLACE FUNCTION generate_order_no()
RETURNS VARCHAR(50)
LANGUAGE SQL
IMMUTABLE
AS $$
    SELECT TO_CHAR(NOW(), 'YYYYMMDD') || LPAD(FLOOR(RANDOM() * 999999)::TEXT, 6, '0')
$$;

-- 下单存储过程（关键业务逻辑）
CREATE OR REPLACE PROCEDURE sp_create_order(
    p_user_id INT,
    p_address_id INT,
    p_cart_item_ids INT[]  -- 购物车项 ID 列表
)
LANGUAGE plpgsql
AS $$
DECLARE
    new_order_id INT;
    new_order_no VARCHAR(50);
    item RECORD;
    total NUMERIC(12,2) := 0;
BEGIN
    -- 生成订单号
    new_order_no := generate_order_no();

    -- 创建订单头
    INSERT INTO es_orders (order_no, user_id, address_id, total_amount, payment_amount)
    VALUES (new_order_no, p_user_id, p_address_id, 0, 0)
    RETURNING order_id INTO new_order_id;

    -- 逐个处理购物车项
    FOR item IN 
        SELECT ci.cart_id, ci.product_id, ci.sku_id, ci.quantity,
               COALESCE(s.price, p.price) AS unit_price,
               p.name AS product_name,
               s.spec AS spec
        FROM es_cart_items ci
        JOIN es_products p ON ci.product_id = p.product_id
        LEFT JOIN es_product_skus s ON ci.sku_id = s.sku_id
        WHERE ci.cart_id = ANY(p_customer_item_ids)
          AND ci.user_id = p_user_id
    LOOP
        -- 插入订单明细
        INSERT INTO es_order_items (order_id, product_id, sku_id, 
            product_name, spec_desc, unit_price, quantity, subtotal)
        VALUES (new_order_id, item.product_id, item.sku_id,
            item.product_name, 
            CASE WHEN item.spec IS NOT NULL THEN item.spec::TEXT ELSE NULL END,
            item.unit_price, item.quantity, 
            item.unit_price * item.quantity);

        -- 扣减库存
        IF item.sku_id IS NOT NULL THEN
            UPDATE es_product_skus 
            SET stock = stock - item.quantity
            WHERE sku_id = item.sku_id AND stock >= item.quantity;

            IF NOT FOUND THEN
                RAISE EXCEPTION 'SKU % 库存不足', item.sku_id;
            END IF;
        ELSE
            UPDATE es_products
            SET stock = stock - item.quantity
            WHERE product_id = item.product_id AND stock >= item.quantity;

            IF NOT FOUND THEN
                RAISE EXCEPTION '商品 % 库存不足', item.product_id;
            END IF;
        END IF;

        total := total + item.unit_price * item.quantity;

        -- 从购物车中删除
        DELETE FROM es_cart_items WHERE cart_id = item.cart_id;
    END LOOP;

    -- 更新订单总额
    UPDATE es_orders 
    SET total_amount = total, payment_amount = total
    WHERE order_id = new_order_id;

    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
$$;

-- 为商品搜索创建 tsvector 列
ALTER TABLE es_products ADD COLUMN search_vector TSVECTOR;

-- 更新搜索向量的函数
CREATE OR REPLACE FUNCTION update_product_search_vector()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    NEW.search_vector := 
        setweight(to_tsvector('simple', COALESCE(NEW.name, '')), 'A') ||
        setweight(to_tsvector('simple', COALESCE(NEW.description, '')), 'B');
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_products_search_vector
    BEFORE INSERT OR UPDATE ON es_products
    FOR EACH ROW
    EXECUTE FUNCTION update_product_search_vector();

CREATE INDEX idx_products_fts ON es_products USING GIN (search_vector);

-- 搜索商品
SELECT product_id, name, price,
       ts_rank(search_vector, query) AS relevance
FROM es_products, 
     plainto_tsquery('simple', '笔记本 电脑 高性能') AS query
WHERE search_vector @@ query
  AND status = 'online'
ORDER BY relevance DESC
LIMIT 20;

-- 用户最近 5 笔订单
SELECT * FROM (
    SELECT 
        user_id,
        order_no,
        total_amount,
        status,
        created_at,
        ROW_NUMBER() OVER (
            PARTITION BY user_id ORDER BY created_at DESC
        ) AS rn
    FROM es_orders
) ranked
WHERE rn <= 5
ORDER BY user_id, rn;

-- 月度销售趋势
WITH monthly_stats AS (
    SELECT 
        DATE_TRUNC('month', created_at) AS month,
        COUNT(DISTINCT order_id) AS order_count,
        SUM(payment_amount) AS revenue,
        COUNT(DISTINCT user_id) AS paying_users,
        SUM(payment_amount) / NULLIF(COUNT(DISTINCT user_id), 0) AS arpu
    FROM es_orders
    WHERE status IN ('paid', 'shipped', 'delivered', 'completed')
    GROUP BY 1
)
SELECT 
    month,
    order_count,
    revenue,
    paying_users,
    ROUND(arpu, 2) AS arpu,
    ROUND((revenue - LAG(revenue) OVER (ORDER BY month)) / 
          NULLIF(LAG(revenue) OVER (ORDER BY month), 0) * 100, 2) AS revenue_growth
FROM monthly_stats
ORDER BY month;

-- 评价后更新商品评分
CREATE OR REPLACE FUNCTION update_product_rating()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE es_products
    SET rating = (
        SELECT ROUND(AVG(rating), 1)
        FROM es_reviews
        WHERE product_id = NEW.product_id
    )
    WHERE product_id = NEW.product_id;
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_review_rating
    AFTER INSERT OR DELETE ON es_reviews
    FOR EACH ROW
    EXECUTE FUNCTION update_product_rating();

-- 未支付订单 30 分钟后自动取消
CREATE OR REPLACE FUNCTION cancel_timeout_orders()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
    timeout_interval INTERVAL := '30 minutes';
BEGIN
    IF NEW.status = 'pending_payment' AND OLD.status IS DISTINCT FROM 'pending_payment' THEN
        -- 取消超时未支付订单（在实际项目中，应该用 pg_cron 或定时任务扫描）
        -- 这里展示一个简单的基于触发器的检查
        IF NEW.created_at < NOW() - timeout_interval THEN
            RAISE WARNING '订单 % 超时未支付，需取消', NEW.order_id;
            UPDATE es_orders 
            SET status = 'cancelled', updated_at = NOW()
            WHERE order_id = NEW.order_id 
              AND status = 'pending_payment';
            -- 释放库存逻辑略
        END IF;
    END IF;
    RETURN NEW;
END;
$$;

WITH user_rfm AS (
    SELECT 
        user_id,
        COUNT(DISTINCT order_id) AS frequency,
        SUM(payment_amount) AS monetary,
        EXTRACT(DAY FROM NOW() - MAX(created_at)) AS recency_days
    FROM es_orders
    WHERE status IN ('paid', 'shipped', 'delivered', 'completed')
    GROUP BY user_id
),
rfm_scores AS (
    SELECT *,
        NTILE(5) OVER (ORDER BY recency_days DESC) AS r_score,
        NTILE(5) OVER (ORDER BY frequency) AS f_score,
        NTILE(5) OVER (ORDER BY monetary) AS m_score
    FROM user_rfm
)
SELECT 
    user_id,
    rfm_scores.r_score || rfm_scores.f_score || rfm_scores.m_score AS rfm_code,
    CASE 
        WHEN r_score >= 4 AND f_score >= 4 AND m_score >= 4 THEN '重要价值'
        WHEN r_score >= 4 AND f_score >= 4 THEN '重要发展'
        WHEN r_score >= 2 AND f_score >= 4 THEN '重要保持'
        WHEN r_score <= 2 AND f_score <= 2 THEN '一般流失'
        WHEN r_score >= 4 THEN '新客户'
        ELSE '一般客户'
    END AS user_segment
FROM rfm_scores
ORDER BY user_id;

-- 统计购买了商品 A 的用户还买了哪些商品
WITH bought_orders AS (
    SELECT DISTINCT o.user_id, oi.product_id
    FROM es_orders o
    JOIN es_order_items oi ON o.order_id = oi.order_id
    WHERE EXISTS (
        SELECT 1 FROM es_order_items oi2
        JOIN es_orders o2 ON oi2.order_id = o2.order_id
        WHERE o2.user_id = o.user_id
          AND oi2.product_id = 1  -- 商品 A
    )
)
SELECT 
    oi2.product_id,
    p.name AS 商品名,
    COUNT(DISTINCT o.user_id) AS 关联购买用户数,
    COUNT(*) AS 关联购买次数
FROM es_orders o
JOIN es_order_items oi2 ON o.order_id = oi2.order_id
JOIN es_products p ON oi2.product_id = p.product_id
WHERE o.user_id IN (SELECT user_id FROM bought_orders)
  AND oi2.product_id != 1  -- 排除商品 A 自身
GROUP BY oi2.product_id, p.name
ORDER BY 关联购买用户数 DESC
LIMIT 10;

CREATE MATERIALIZED VIEW mv_daily_report AS
WITH daily_orders AS (
    SELECT 
        DATE_TRUNC('day', created_at) AS report_date,
        COUNT(DISTINCT order_id) AS order_count,
        SUM(payment_amount) AS total_revenue,
        COUNT(DISTINCT user_id) AS paying_users,
        SUM(payment_amount) / NULLIF(COUNT(DISTINCT user_id), 0) AS arpu
    FROM es_orders
    WHERE status IN ('paid', 'shipped', 'delivered', 'completed')
    GROUP BY 1
),
daily_new_users AS (
    SELECT DATE_TRUNC('day', created_at) AS report_date,
           COUNT(*) AS new_users
    FROM es_users
    GROUP BY 1
)
SELECT 
    COALESCE(d.report_date, n.report_date) AS report_date,
    COALESCE(d.order_count, 0) AS order_count,
    COALESCE(d.total_revenue, 0) AS total_revenue,
    COALESCE(d.paying_users, 0) AS paying_users,
    COALESCE(d.arpu, 0) AS arpu,
    COALESCE(n.new_users, 0) AS new_users
FROM daily_orders d
FULL JOIN daily_new_users n ON d.report_date = n.report_date
ORDER BY report_date DESC;

CREATE UNIQUE INDEX idx_mv_daily ON mv_daily_report (report_date);
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_report;

-- 用户查询索引
CREATE INDEX idx_orders_user_status ON es_orders(user_id, status);
CREATE INDEX idx_orders_created ON es_orders(created_at);
CREATE INDEX idx_orders_status_amount ON es_orders(status, payment_amount);

-- 商品搜索索引
CREATE INDEX idx_products_category ON es_products(category_id, status);
CREATE INDEX idx_products_price ON es_products(price) WHERE status = 'online';
CREATE INDEX idx_products_name ON es_products USING GIN (name gin_trgm_ops);  -- 需要 pg_trgm 扩展

-- 购物车索引
CREATE INDEX idx_cart_user ON es_cart_items(user_id);
CREATE INDEX idx_reviews_product ON es_reviews(product_id, rating);