-- 1. 用户管理
-- 创建用户表
CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,  -- 用户ID
    username VARCHAR(100) UNIQUE NOT NULL,   -- 用户名
    password VARCHAR(255) NOT NULL,          -- 明文密码
    phone VARCHAR(20),                       -- 电话号码
    address VARCHAR(255),                    -- 地址
    privacy ENUM('public', 'private') DEFAULT 'public',  -- 隐私设置
    registration_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP  -- 注册时间
);

-- 2. 商品管理
-- 创建商品表
CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,  -- 商品ID
    user_id INT,                               -- 发布商品的用户ID
    category ENUM('electronics', 'books', 'daily_necessities') NOT NULL,  -- 商品类别
    title VARCHAR(255) NOT NULL,               -- 商品标题
    description TEXT,                          -- 商品描述
    price DECIMAL(10, 2) NOT NULL,             -- 商品价格
    status ENUM('new', 'used', 'negotiable') DEFAULT 'used',  -- 商品状态
    location VARCHAR(255),                     -- 商品所在位置
    publish_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  -- 发布日期
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

-- 创建商品图片表
CREATE TABLE product_images (
    image_id INT AUTO_INCREMENT PRIMARY KEY,   -- 图片ID
    product_id INT,                            -- 商品ID
    image_url VARCHAR(255),                   -- 图片链接
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

-- 创建商品标签表
CREATE TABLE product_tags (
    product_id INT,                            -- 商品ID
    tag VARCHAR(50),                           -- 商品标签
    PRIMARY KEY (product_id, tag),            -- 复合主键
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

-- 3. 交易管理
-- 创建交易表
CREATE TABLE transactions (
    transaction_id INT AUTO_INCREMENT PRIMARY KEY,  -- 交易ID
    buyer_id INT,                                  -- 买家ID
    seller_id INT,                                 -- 卖家ID
    product_id INT,                                -- 商品ID
    price DECIMAL(10, 2) NOT NULL,                 -- 交易价格
    status ENUM('pending', 'completed', 'cancelled', 'refunded') DEFAULT 'pending',  -- 交易状态
    payment_method ENUM('credit', 'debit', 'paypal', 'other'),  -- 支付方式
    transaction_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  -- 交易时间
    FOREIGN KEY (buyer_id) REFERENCES users(user_id),
    FOREIGN KEY (seller_id) REFERENCES users(user_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

-- 创建支付记录表
CREATE TABLE payment_records (
    payment_id INT AUTO_INCREMENT PRIMARY KEY,    -- 支付ID
    transaction_id INT,                            -- 关联的交易ID
    payment_method ENUM('alipay', 'wechat', 'paypal', 'stripe') NOT NULL,  -- 支付方式
    payment_status ENUM('pending', 'completed', 'failed') DEFAULT 'pending',  -- 支付状态
    amount DECIMAL(10, 2),                         -- 支付金额
    payment_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  -- 支付时间
    FOREIGN KEY (transaction_id) REFERENCES transactions(transaction_id)
);

-- 创建退款退货表
CREATE TABLE refund_requests (
    refund_id INT AUTO_INCREMENT PRIMARY KEY,     -- 退款申请ID
    transaction_id INT,                           -- 关联的交易ID
    request_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  -- 退款申请时间
    reason TEXT,                                  -- 退款原因
    status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',  -- 退款状态
    FOREIGN KEY (transaction_id) REFERENCES transactions(transaction_id)
);

-- 4. 评价与信用系统
-- 创建评价表
CREATE TABLE reviews (
    review_id INT AUTO_INCREMENT PRIMARY KEY,      -- 评价ID
    transaction_id INT,                            -- 交易ID
    reviewer_id INT,                               -- 评价者ID
    rating INT CHECK(rating BETWEEN 1 AND 5),     -- 评分 (1-5)
    comment TEXT,                                  -- 评价内容
    review_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  -- 评价时间
    FOREIGN KEY (transaction_id) REFERENCES transactions(transaction_id),
    FOREIGN KEY (reviewer_id) REFERENCES users(user_id)
);

-- 创建用户信用分表
CREATE TABLE user_credit_score (
    user_id INT PRIMARY KEY,                       -- 用户ID
    score INT DEFAULT 1000,                       -- 初始信用分数
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

-- 存储过程：修改用户信用分
DELIMITER //
CREATE PROCEDURE update_credit_score(
    IN p_user_id INT, 
    IN p_score_change INT
)
BEGIN
    UPDATE user_credit_score
    SET score = score + p_score_change
    WHERE user_id = p_user_id;
END //
DELIMITER ;

-- 5. 搜索与推荐
-- 创建商品搜索视图
CREATE VIEW product_search AS
SELECT 
    p.product_id, 
    p.title, 
    p.description, 
    p.price, 
    p.publish_date, 
    u.username AS seller_name, 
    p.category
FROM products p
JOIN users u ON p.user_id = u.user_id
WHERE p.status != 'sold';

-- 创建用户行为表
CREATE TABLE user_behavior (
    behavior_id INT AUTO_INCREMENT PRIMARY KEY,   -- 行为ID
    user_id INT,                                  -- 用户ID
    action_type ENUM('view', 'like', 'add_to_cart', 'purchase'),  -- 行为类型
    product_id INT,                               -- 商品ID
    action_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  -- 行为时间
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

-- 6. 数据分析与报表
-- 创建交易统计视图
CREATE VIEW transaction_statistics AS
SELECT 
    COUNT(*) AS total_transactions, 
    SUM(price) AS total_revenue, 
    AVG(price) AS average_transaction_value, 
    DATE(transaction_date) AS transaction_day
FROM transactions
GROUP BY DATE(transaction_date);

-- 创建热销商品统计视图
CREATE VIEW hot_products AS
SELECT 
    p.product_id, 
    p.title, 
    COUNT(t.transaction_id) AS total_sales
FROM products p
LEFT JOIN transactions t ON p.product_id = t.product_id AND t.status = 'completed'
GROUP BY p.product_id
ORDER BY total_sales DESC;

-- 创建用户交易分析视图
CREATE VIEW user_transaction_stats AS
SELECT 
    buyer_id AS user_id, 
    COUNT(transaction_id) AS total_transactions, 
    SUM(price) AS total_spent,
    AVG(price) AS average_spent
FROM transactions
GROUP BY buyer_id;

-- 7. 权限设计
-- 创建管理员角色
CREATE ROLE admin;

-- 创建普通用户角色
CREATE ROLE user;

-- 给管理员角色授予所有权限
GRANT ALL PRIVILEGES ON *.* TO admin WITH GRANT OPTION;

-- 给普通用户角色授予部分权限
GRANT SELECT, INSERT, UPDATE ON users TO user;  -- 普通用户可以查看和修改自己的个人信息
GRANT SELECT, INSERT, UPDATE ON products TO user;  -- 普通用户可以查看和发布商品
GRANT SELECT, INSERT, UPDATE ON transactions TO user;  -- 普通用户可以查看和修改自己的交易记录
GRANT SELECT, INSERT, UPDATE ON reviews TO user;  -- 普通用户可以进行商品评价

-- 限制普通用户只能访问自己的数据
CREATE TRIGGER user_data_access
AFTER SELECT ON users
FOR EACH ROW
BEGIN
    IF OLD.user_id != CURRENT_USER THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Permission denied: Cannot access other users\' data';
    END IF;
END;

-- 8. 触发器设计
-- 触发器：检查商品发布的合法性
DELIMITER //
CREATE TRIGGER check_product_price
BEFORE INSERT ON products
FOR EACH ROW
BEGIN
    IF NEW.price <= 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Price must be greater than zero.';
    END IF;
    
    IF NEW.category NOT IN ('electronics', 'books', 'daily_necessities') THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Invalid category.';
    END IF;
END //
DELIMITER ;

-- 触发器：更新用户信用分
DELIMITER //
CREATE TRIGGER update_user_credit_score
AFTER INSERT ON reviews
FOR EACH ROW
BEGIN
    DECLARE score_change INT;

    -- 评分系统：每次评价改变信用分
    SET score_change = CASE
                        WHEN NEW.rating = 5 THEN 10
                        WHEN NEW.rating = 4 THEN 5
                        WHEN NEW.rating = 3 THEN 0
                        WHEN NEW.rating = 2 THEN -5
                        WHEN NEW.rating = 1 THEN -10
                      END;
    
    -- 更新用户信用分
    CALL update_credit_score(NEW.reviewer_id, score_change);
END //
DELIMITER ;

-- 触发器：检查退款请求
DELIMITER //
CREATE TRIGGER check_refund_request
BEFORE INSERT ON refund_requests
FOR EACH ROW
BEGIN
    DECLARE transaction_status VARCHAR(20);

    -- 获取交易状态
    SELECT status INTO transaction_status
    FROM transactions
    WHERE transaction_id = NEW.transaction_id;
    
    -- 判断交易是否已完成且未退款
    IF transaction_status != 'completed' THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Refund can only be requested for completed transactions.';
    END IF;
END //
DELIMITER ;

-- 9. 数据分析与报告
-- 创建日常交易统计视图
CREATE VIEW daily_transaction_report AS
SELECT 
    DATE(transaction_date) AS transaction_day, 
    COUNT(*) AS total_transactions, 
    SUM(price) AS total_revenue,
    AVG(price) AS average_transaction_value
FROM transactions
GROUP BY DATE(transaction_date);

-- 创建用户行为数据分析视图
CREATE VIEW user_behavior_analysis AS
SELECT 
    user_id, 
    COUNT(*) AS total_actions,
    SUM(CASE WHEN action_type = 'view' THEN 1 ELSE 0 END) AS total_views,
    SUM(CASE WHEN action_type = 'like' THEN 1 ELSE 0 END) AS total_likes,
    SUM(CASE WHEN action_type = 'add_to_cart' THEN 1 ELSE 0 END) AS total_adds_to_cart,
    SUM(CASE WHEN action_type = 'purchase' THEN 1 ELSE 0 END) AS total_purchases
FROM user_behavior
GROUP BY user_id;

-- 创建商品类别统计视图
CREATE VIEW category_statistics AS
SELECT 
    category,
    COUNT(*) AS total_products,
    SUM(price) AS total_sales
FROM products
GROUP BY category;

-- 10. 购物车管理
-- 创建购物车表
CREATE TABLE shopping_cart (
    cart_id INT AUTO_INCREMENT PRIMARY KEY,  -- 购物车ID
    user_id INT,                             -- 用户ID
    product_id INT,                          -- 商品ID
    added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  -- 商品加入时间
    status ENUM('active', 'purchased', 'removed') DEFAULT 'active',  -- 商品状态
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

-- 创建购物车视图
CREATE VIEW shopping_cart_view AS
SELECT 
    sc.cart_id,
    sc.user_id,
    sc.product_id,
    p.title AS product_title,
    p.price AS product_price,
    sc.status AS cart_status,
    sc.added_at,
    u.username AS seller_name
FROM shopping_cart sc
JOIN products p ON sc.product_id = p.product_id
JOIN users u ON p.user_id = u.user_id
WHERE sc.status = 'active';

-- 创建触发器限制用户只能访问自己的购物车数据
DELIMITER //
CREATE TRIGGER user_cart_access
AFTER SELECT ON shopping_cart
FOR EACH ROW
BEGIN
    IF OLD.user_id != CURRENT_USER THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Permission denied: Cannot access other users\' shopping cart';
    END IF;
END //
DELIMITER ;
将类似'active', 'purchased', 'removed'，带有 '   '里面的英文换成中文