CREATE DATABASE IF NOT EXISTS ecommerce
    DEFAULT CHARACTER SET utf8mb4
    COLLATE utf8mb4_0900_ai_ci;

USE ecommerce;

-- ===================== 用户模块 =====================
CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    phone VARCHAR(20) NOT NULL DEFAULT '',
    password_hash VARCHAR(255) NOT NULL,
    nickname VARCHAR(50) DEFAULT '',
    avatar_url VARCHAR(500) DEFAULT '',
    gender TINYINT UNSIGNED DEFAULT 0,
    birthday DATE DEFAULT NULL,
    status TINYINT UNSIGNED NOT NULL DEFAULT 1,
    last_login_at DATETIME DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at DATETIME DEFAULT NULL,
    INDEX idx_phone (phone),
    INDEX idx_status (status),
    INDEX idx_created (created_at)
) ENGINE=InnoDB COMMENT='用户表';

-- ===================== 商品模块 =====================
CREATE TABLE categories (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    parent_id INT UNSIGNED DEFAULT 0,
    name VARCHAR(100) NOT NULL,
    sort_order INT DEFAULT 0,
    status TINYINT DEFAULT 1,
    INDEX idx_parent (parent_id)
) ENGINE=InnoDB COMMENT='商品分类表';

CREATE TABLE products (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    category_id INT UNSIGNED NOT NULL,
    name VARCHAR(200) NOT NULL,
    subtitle VARCHAR(500) DEFAULT '',
    main_image VARCHAR(500) DEFAULT '',
    price DECIMAL(10,2) NOT NULL COMMENT '售价',
    cost_price DECIMAL(10,2) DEFAULT NULL COMMENT '成本价',
    stock INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '库存',
    sales INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '销量',
    status TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '1=上架 0=下架',
    attributes JSON DEFAULT NULL COMMENT '商品属性',
    detail TEXT DEFAULT NULL COMMENT '商品详情',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at DATETIME DEFAULT NULL,
    INDEX idx_category (category_id),
    INDEX idx_status_sales (status, sales),
    FULLTEXT INDEX ft_name (name)
) ENGINE=InnoDB COMMENT='商品表';

CREATE TABLE product_skus (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    product_id BIGINT UNSIGNED NOT NULL,
    sku_code VARCHAR(50) NOT NULL UNIQUE COMMENT 'SKU编码',
    spec_values JSON NOT NULL COMMENT '规格值，如{"颜色":"红","尺码":"M"}',
    price DECIMAL(10,2) NOT NULL,
    stock INT UNSIGNED NOT NULL DEFAULT 0,
    INDEX idx_product (product_id)
) ENGINE=InnoDB COMMENT='商品SKU表';

-- ===================== 订单模块 =====================
CREATE TABLE orders (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_no VARCHAR(32) NOT NULL UNIQUE,
    user_id BIGINT UNSIGNED NOT NULL,
    total_amount DECIMAL(12,2) NOT NULL,
    pay_amount DECIMAL(12,2) NOT NULL,
    freight_amount DECIMAL(10,2) DEFAULT 0,
    discount_amount DECIMAL(12,2) DEFAULT 0,
    status TINYINT UNSIGNED NOT NULL DEFAULT 0
        COMMENT '0=待支付 1=已支付 2=已发货 3=已完成 4=已取消 5=已退款',
    pay_type TINYINT UNSIGNED DEFAULT NULL COMMENT '1=微信 2=支付宝 3=银行卡',
    pay_time DATETIME DEFAULT NULL,
    ship_time DATETIME DEFAULT NULL,
    receive_time DATETIME DEFAULT NULL,
    receiver_name VARCHAR(50) NOT NULL,
    receiver_phone VARCHAR(20) NOT NULL,
    receiver_address VARCHAR(500) NOT NULL,
    remark VARCHAR(500) DEFAULT '',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at DATETIME DEFAULT NULL,
    INDEX idx_user_id (user_id),
    INDEX idx_status (status),
    INDEX idx_created (created_at),
    INDEX idx_user_status (user_id, status)
) ENGINE=InnoDB COMMENT='订单表';

CREATE TABLE order_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINT UNSIGNED NOT NULL,
    product_id BIGINT UNSIGNED NOT NULL,
    sku_id BIGINT UNSIGNED DEFAULT NULL,
    product_name VARCHAR(200) NOT NULL,
    sku_spec VARCHAR(200) DEFAULT '',
    price DECIMAL(10,2) NOT NULL,
    quantity INT UNSIGNED NOT NULL,
    total_amount DECIMAL(12,2) NOT NULL,
    INDEX idx_order_id (order_id),
    INDEX idx_product_id (product_id)
) ENGINE=InnoDB COMMENT='订单商品表';

-- 查询用户的订单列表（分页）
SELECT o.id, o.order_no, o.total_amount, o.status, o.created_at,
       GROUP_CONCAT(oi.product_name SEPARATOR ', ') AS products
FROM orders o
LEFT JOIN order_items oi ON o.id = oi.order_id
WHERE o.user_id = 100 AND o.deleted_at IS NULL
GROUP BY o.id
ORDER BY o.created_at DESC
LIMIT 20 OFFSET 0;

-- 查询商品排行榜
SELECT p.id, p.name, p.price, p.sales, p.main_image
FROM products p
WHERE p.status = 1 AND p.deleted_at IS NULL
ORDER BY p.sales DESC
LIMIT 50;

-- 统计某月销售数据
SELECT 
    DATE(o.created_at) AS sale_date,
    COUNT(*) AS order_count,
    SUM(o.pay_amount) AS revenue,
    COUNT(DISTINCT o.user_id) AS unique_buyers
FROM orders o
WHERE o.status IN (1, 2, 3)
  AND o.created_at >= '2026-05-01' AND o.created_at < '2026-06-01'
GROUP BY DATE(o.created_at)
ORDER BY sale_date;

-- 乐观锁扣减库存
UPDATE product_skus 
SET stock = stock - 1 
WHERE id = 1001 AND stock > 0;
-- 检查 affected_rows，如果为 0 则库存不足

-- 关注关系表
CREATE TABLE follows (
    follower_id BIGINT UNSIGNED NOT NULL COMMENT '关注者',
    following_id BIGINT UNSIGNED NOT NULL COMMENT '被关注者',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (follower_id, following_id),
    INDEX idx_following (following_id, follower_id)
) ENGINE=InnoDB COMMENT='关注关系表';

-- 好友关系表（双向）
CREATE TABLE friendships (
    user_id BIGINT UNSIGNED NOT NULL,
    friend_id BIGINT UNSIGNED NOT NULL,
    status TINYINT UNSIGNED DEFAULT 1 COMMENT '1=正常 0=拉黑',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, friend_id),
    INDEX idx_friend (friend_id)
) ENGINE=InnoDB COMMENT='好友关系表';

-- 动态表
CREATE TABLE posts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    content TEXT NOT NULL,
    images JSON DEFAULT NULL,
    like_count INT UNSIGNED DEFAULT 0,
    comment_count INT UNSIGNED DEFAULT 0,
    visibility TINYINT DEFAULT 1 COMMENT '1=公开 2=仅好友 3=仅自己',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_time (user_id, created_at DESC),
    INDEX idx_created (created_at DESC)
) ENGINE=InnoDB COMMENT='动态表';

-- 点赞表
CREATE TABLE likes (
    user_id BIGINT UNSIGNED NOT NULL,
    target_type TINYINT UNSIGNED NOT NULL COMMENT '1=动态 2=评论',
    target_id BIGINT UNSIGNED NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, target_type, target_id),
    INDEX idx_target (target_type, target_id)
) ENGINE=InnoDB COMMENT='点赞表';

-- 查询某用户的粉丝数
SELECT COUNT(*) AS follower_count FROM follows WHERE following_id = 100;

-- 查询某用户的关注数
SELECT COUNT(*) AS following_count FROM follows WHERE follower_id = 100;

-- 查询共同关注
SELECT f1.following_id
FROM follows f1
INNER JOIN follows f2 
    ON f1.following_id = f2.following_id
WHERE f1.follower_id = 100 AND f2.follower_id = 200;

-- 查询关注的人的动态（信息流）
SELECT p.*
FROM posts p
INNER JOIN follows f ON p.user_id = f.following_id
WHERE f.follower_id = 100
  AND p.visibility = 1
ORDER BY p.created_at DESC
LIMIT 50;

-- 推荐可能认识的人（朋友的朋友，但还不是自己的朋友）
SELECT DISTINCT f2.following_id
FROM follows f1
INNER JOIN follows f2 ON f1.following_id = f2.follower_id
WHERE f1.follower_id = 100
  AND f2.following_id != 100
  AND f2.following_id NOT IN (
      SELECT following_id FROM follows WHERE follower_id = 100
  )
LIMIT 20;

-- 操作日志表（分区）
CREATE TABLE operation_logs (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id BIGINT UNSIGNED DEFAULT NULL,
    action VARCHAR(50) NOT NULL COMMENT '操作类型',
    resource_type VARCHAR(50) NOT NULL COMMENT '资源类型',
    resource_id VARCHAR(50) DEFAULT NULL,
    detail JSON DEFAULT NULL COMMENT '操作详情',
    ip_address VARCHAR(45) DEFAULT NULL,
    user_agent VARCHAR(500) DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id, created_at),
    INDEX idx_user_id (user_id),
    INDEX idx_action (action),
    INDEX idx_resource (resource_type, resource_id)
) ENGINE=InnoDB
PARTITION BY RANGE (TO_DAYS(created_at)) (
    PARTITION p202601 VALUES LESS THAN (TO_DAYS('2026-02-01')),
    PARTITION p202602 VALUES LESS THAN (TO_DAYS('2026-03-01')),
    PARTITION p202603 VALUES LESS THAN (TO_DAYS('2026-04-01')),
    PARTITION p202604 VALUES LESS THAN (TO_DAYS('2026-05-01')),
    PARTITION p202605 VALUES LESS THAN (TO_DAYS('2026-06-01')),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- 统计每日操作量
SELECT 
    DATE(created_at) AS day,
    action,
    COUNT(*) AS cnt
FROM operation_logs
WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
GROUP BY DATE(created_at), action
ORDER BY day DESC, cnt DESC;

-- 清理旧分区（比 DELETE 快）
ALTER TABLE operation_logs DROP PARTITION p202601;

# 使用 mysqlbinlog 实时解析
mysqlbinlog --base64-output=DECODE-ROWS -v --start-datetime="2026-05-10 00:00:00" \
  mysql-bin.000010 | grep -E "### (INSERT|UPDATE|DELETE)" | head -20

import redis
import pymysql

r = redis.Redis()
db = pymysql.connect(host='localhost', user='root', password='pass', db='myapp')

def get_user(user_id):
    # 1. 先查缓存
    cache_key = f"user:{user_id}"
    cached = r.get(cache_key)
    if cached:
        return json.loads(cached)
    
    # 2. 查数据库
    cursor = db.cursor(pymysql.cursors.DictCursor)
    cursor.execute("SELECT * FROM users WHERE id = %s", (user_id,))
    user = cursor.fetchone()
    
    if user:
        # 3. 写入缓存（设置过期时间）
        r.setex(cache_key, 3600, json.dumps(user, default=str))
    
    return user

def update_user(user_id, data):
    # 1. 更新数据库
    cursor = db.cursor()
    cursor.execute("UPDATE users SET nickname = %s WHERE id = %s", 
                   (data['nickname'], user_id))
    db.commit()
    
    # 2. 删除缓存
    r.delete(f"user:{user_id}")

-- 缓存空值示例
-- 查询不存在的用户
SELECT * FROM users WHERE id = 999999;
-- 返回 NULL，缓存空值 5 分钟
-- r.setex("user:999999", 300, "NULL")