-- 创建数据库
CREATE DATABASE IF NOT EXISTS inventory_monitor;
USE inventory_monitor;

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

-- 产品表
CREATE TABLE IF NOT EXISTS products (
    sku VARCHAR(50) PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    category VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 监控项表
CREATE TABLE IF NOT EXISTS monitor_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    sku VARCHAR(50) NOT NULL,
    stock INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (sku) REFERENCES products(sku),
    UNIQUE KEY user_sku_unique (user_id, sku)
);

-- 库存历史记录表
CREATE TABLE IF NOT EXISTS stock_history (
    id INT PRIMARY KEY AUTO_INCREMENT,
    monitor_item_id INT NOT NULL,
    sku VARCHAR(50) NOT NULL,
    stock INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (monitor_item_id) REFERENCES monitor_items(id)
);

-- 创建示例用户
INSERT INTO users (username, password_hash, email)
VALUES ('admin', '$2y$10$vGJ2JqfYIqXhH8vJZJZJZJZJZJZJZJZJZJZJZJZJZJZJZJZJ', 'admin@example.com')
ON DUPLICATE KEY UPDATE username = username;

-- 创建示例产品
INSERT INTO products (sku, name, description, category)
VALUES 
('N653P264946A', '智能手机 A', '高端智能手机', '电子设备'),
('N653P264946G', '智能手机 G', '中端智能手机', '电子设备'),
('N653P264946P', '智能手机 P', '入门级智能手机', '电子设备')
ON DUPLICATE KEY UPDATE sku = sku;

-- 为admin用户添加监控项
INSERT INTO monitor_items (user_id, sku, stock)
SELECT 1, sku, FLOOR(RAND() * 500)
FROM products
ON DUPLICATE KEY UPDATE user_id = user_id;    