CREATE TABLE users
(
    id           INT AUTO_INCREMENT PRIMARY KEY,
    account      VARCHAR(255) UNIQUE NOT NULL,
    name         VARCHAR(255)        NOT NULL,
    birth        DATE,
    email        VARCHAR(255),
    password     VARCHAR(255)        NOT NULL,
    phone        VARCHAR(20),
    status       ENUM (
        'active',
        'inactive',
        'suspended',
        'deleted',
        'locked',
        'pending verification'
        )                 DEFAULT 'active',
    created_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS posts
(
    id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '文章ID',
    title        VARCHAR(255) NOT NULL COMMENT '标题', -- 将长度扩展为255，适应更长的标题
    content      TEXT COMMENT '内容',
    link         VARCHAR(200) COMMENT 'URL地址',       -- 添加唯一索引，避免重复链接
    popularity   VARCHAR(100) COMMENT '热度',          -- 假设热度是一个数值，使用 INT 类型
    created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX idx_title (title),                           -- 为 title 添加索引，提高查询效率
    INDEX idx_link (link)                              -- 为 link 添加索引，避免 URL 查找慢
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_unicode_ci COMMENT ='文章表';



#     pub id: Option<i32>,
#     pub title: Option<String>,
#     pub content: Option<String>,
#     pub link: Option<String>,
#     pub popularity: Option<String>,
#     pub created_time: Option<String>,
#     pub updated_time: Option<String>,


INSERT INTO test_server.users (id, account, name, birth, password)
VALUES (0, '123', 'hyh', '2000-0-03', '1');

INSERT INTO test_server.users (account, name, password)
VALUES ('123', 'hyh', '1');