-- 用户表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    phone VARCHAR(20),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- 商品分类
CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    parent_id INTEGER REFERENCES categories(id),
    level INTEGER DEFAULT 1,
    sort_order INTEGER DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE
);

-- 商品表
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    sku VARCHAR(50) UNIQUE NOT NULL,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    category_id INTEGER REFERENCES categories(id),
    price NUMERIC(10,2) NOT NULL CHECK (price >= 0),
    cost NUMERIC(10,2),
    stock INTEGER DEFAULT 0 CHECK (stock >= 0),
    attributes JSONB,  -- 商品属性(颜色、尺寸等)
    images TEXT[],  -- 商品图片URLs
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    CONSTRAINT chk_price_cost CHECK (price >= cost)
);

-- 购物车
CREATE TABLE cart_items (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    product_id INTEGER REFERENCES products(id),
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE (user_id, product_id)
);

-- 订单表
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    order_number VARCHAR(50) UNIQUE NOT NULL,
    user_id INTEGER REFERENCES users(id),
    total_amount NUMERIC(12,2) NOT NULL CHECK (total_amount > 0),
    status VARCHAR(20) DEFAULT 'pending',
    payment_method VARCHAR(20),
    shipping_address JSONB,
    tracking_number VARCHAR(100),
    notes TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    CONSTRAINT chk_order_status CHECK (status IN ('pending', 'paid', 'processing', 'shipped', 'delivered', 'cancelled'))
);

-- 订单明细
CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INTEGER REFERENCES orders(id) ON DELETE CASCADE,
    product_id INTEGER REFERENCES products(id),
    product_snapshot JSONB,  -- 商品快照
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    price NUMERIC(10,2) NOT NULL CHECK (price >= 0),
    subtotal NUMERIC(12,2) NOT NULL CHECK (subtotal >= 0),
    CONSTRAINT chk_subtotal CHECK (subtotal = price * quantity)
);

-- 优惠券
CREATE TABLE coupons (
    id SERIAL PRIMARY KEY,
    code VARCHAR(50) UNIQUE NOT NULL,
    discount_type VARCHAR(20),  -- percentage, fixed
    discount_value NUMERIC(10,2),
    min_order_amount NUMERIC(10,2),
    max_discount_amount NUMERIC(10,2),
    valid_from TIMESTAMPTZ,
    valid_until TIMESTAMPTZ,
    usage_limit INTEGER,
    used_count INTEGER DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE
);

-- 用户优惠券
CREATE TABLE user_coupons (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    coupon_id INTEGER REFERENCES coupons(id),
    used_at TIMESTAMPTZ,
    order_id INTEGER REFERENCES orders(id),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 商品评价
CREATE TABLE reviews (
    id SERIAL PRIMARY KEY,
    product_id INTEGER REFERENCES products(id),
    user_id INTEGER REFERENCES users(id),
    order_id INTEGER REFERENCES orders(id),
    rating INTEGER CHECK (rating BETWEEN 1 AND 5),
    comment TEXT,
    images TEXT[],
    is_verified BOOLEAN DEFAULT FALSE,
    helpful_count INTEGER DEFAULT 0,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 用户表索引
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_phone ON users(phone);
CREATE INDEX idx_users_created ON users(created_at);

-- 商品表索引
CREATE INDEX idx_products_category ON products(category_id);
CREATE INDEX idx_products_price ON products(price);
CREATE INDEX idx_products_sku ON products(sku);
CREATE INDEX idx_products_name_gin ON products USING GIN (to_tsvector('english', name));
CREATE INDEX idx_products_attrs ON products USING GIN (attributes);

-- 订单表索引
CREATE INDEX idx_orders_user ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_created ON orders(created_at DESC);
CREATE INDEX idx_orders_number ON orders(order_number);

-- 订单明细索引
CREATE INDEX idx_order_items_order ON order_items(order_id);
CREATE INDEX idx_order_items_product ON order_items(product_id);

CREATE OR REPLACE FUNCTION create_order(
    p_user_id INTEGER,
    p_items JSONB,  -- [{"product_id": 1, "quantity": 2}, ...]
    p_coupon_code VARCHAR DEFAULT NULL
)
RETURNS INTEGER AS $$
DECLARE
    v_order_id INTEGER;
    v_order_number VARCHAR;
    v_total NUMERIC := 0;
    v_item JSONB;
    v_product products%ROWTYPE;
    v_coupon coupons%ROWTYPE;
BEGIN
    -- 生成订单号
    v_order_number := 'ORD' || TO_CHAR(NOW(), 'YYYYMMDD') || LPAD(nextval('order_seq')::TEXT, 6, '0');

    -- 创建订单
    INSERT INTO orders (order_number, user_id, total_amount)
    VALUES (v_order_number, p_user_id, 0)
    RETURNING id INTO v_order_id;

    -- 处理订单项
    FOR v_item IN SELECT * FROM jsonb_array_elements(p_items) LOOP
        -- 检查商品
        SELECT * INTO v_product FROM products
        WHERE id = (v_item->>'product_id')::INTEGER AND is_active = TRUE;

        IF NOT FOUND THEN
            RAISE EXCEPTION 'Product not found or inactive: %', v_item->>'product_id';
        END IF;

        -- 检查库存
        IF v_product.stock < (v_item->>'quantity')::INTEGER THEN
            RAISE EXCEPTION 'Insufficient stock for product: %', v_product.name;
        END IF;

        -- 插入订单项
        INSERT INTO order_items (order_id, product_id, product_snapshot, quantity, price, subtotal)
        VALUES (
            v_order_id,
            v_product.id,
            to_jsonb(v_product),
            (v_item->>'quantity')::INTEGER,
            v_product.price,
            v_product.price * (v_item->>'quantity')::INTEGER
        );

        -- 扣减库存
        UPDATE products SET stock = stock - (v_item->>'quantity')::INTEGER
        WHERE id = v_product.id;

        -- 累计总额
        v_total := v_total + (v_product.price * (v_item->>'quantity')::INTEGER);
    END LOOP;

    -- 应用优惠券
    IF p_coupon_code IS NOT NULL THEN
        SELECT * INTO v_coupon FROM coupons
        WHERE code = p_coupon_code
          AND is_active = TRUE
          AND NOW() BETWEEN valid_from AND valid_until
          AND (usage_limit IS NULL OR used_count < usage_limit);

        IF FOUND AND v_total >= v_coupon.min_order_amount THEN
            -- 计算折扣
            IF v_coupon.discount_type = 'percentage' THEN
                v_total := v_total * (1 - v_coupon.discount_value / 100);
            ELSIF v_coupon.discount_type = 'fixed' THEN
                v_total := GREATEST(v_total - v_coupon.discount_value, 0);
            END IF;

            -- 记录使用
            INSERT INTO user_coupons (user_id, coupon_id, order_id)
            VALUES (p_user_id, v_coupon.id, v_order_id);

            UPDATE coupons SET used_count = used_count + 1 WHERE id = v_coupon.id;
        END IF;
    END IF;

    -- 更新订单总额
    UPDATE orders SET total_amount = v_total WHERE id = v_order_id;

    RETURN v_order_id;
END;
$$ LANGUAGE plpgsql;

-- 日销售报表
CREATE MATERIALIZED VIEW daily_sales_report AS
SELECT
    DATE(created_at) AS sale_date,
    COUNT(DISTINCT id) AS order_count,
    COUNT(DISTINCT user_id) AS unique_customers,
    SUM(total_amount) AS revenue,
    AVG(total_amount) AS avg_order_value
FROM orders
WHERE status IN ('paid', 'processing', 'shipped', 'delivered')
GROUP BY DATE(created_at);

CREATE INDEX idx_daily_sales_date ON daily_sales_report(sale_date);

-- 商品销售排行
CREATE VIEW product_sales_ranking AS
SELECT
    p.id,
    p.name,
    p.category_id,
    COUNT(DISTINCT oi.order_id) AS times_ordered,
    SUM(oi.quantity) AS total_quantity,
    SUM(oi.subtotal) AS total_revenue
FROM products p
LEFT JOIN order_items oi ON p.id = oi.product_id
GROUP BY p.id, p.name, p.category_id;