多级分销系统数据库

-- 用户表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 关系表
CREATE TABLE relationships (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    referrer_id INT NOT NULL,
    level INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (referrer_id) REFERENCES users(id)
);

-- 佣金表
CREATE TABLE commissions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

WITH RECURSIVE commission_tree AS (
    SELECT 
        u.id,
        u.username,
        r.level,
        0 AS commission
    FROM 
        users u
    JOIN 
        relationships r ON u.id = r.user_id
    WHERE 
        r.referrer_id = :referrer_id

    UNION ALL

    SELECT 
        ct.id,
        ct.username,
        r.level,
        ct.commission + :commission_amount AS commission
    FROM 
        commission_tree ct
    JOIN 
        relationships r ON ct.id = r.user_id
)
SELECT * FROM commission_tree;

DELIMITER //

CREATE TRIGGER prevent_cycle_before_insert
BEFORE INSERT ON relationships
FOR EACH ROW
BEGIN
    DECLARE cycle INT DEFAULT 0;
    SELECT COUNT(*) INTO cycle
    FROM relationships r
    WHERE r.user_id = NEW.referrer_id
      AND r.referrer_id IN (
          SELECT r2.user_id
          FROM relationships r2
          WHERE r2.referrer_id = NEW.user_id
      );
    IF cycle > 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '循环推荐关系不允许';
    END IF;
END //

DELIMITER ;