-- 数据库初始化脚本
-- 创建数据库
CREATE DATABASE IF NOT EXISTS redemption_system DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE redemption_system;

-- 用户表
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    role ENUM('admin', 'user') NOT NULL DEFAULT 'user',
    disabled TINYINT(1) NOT NULL DEFAULT 0 COMMENT '0=启用 1=禁用',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 插入默认管理员 (密码: admin123)
INSERT INTO users (username, password, role) VALUES ('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin');

-- 邀请码表
CREATE TABLE IF NOT EXISTS invite_codes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(50) NOT NULL UNIQUE,
    used_by INT DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (used_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 插入默认邀请码
INSERT INTO invite_codes (code) VALUES ('DEFAULT2024');

-- 产品表
CREATE TABLE IF NOT EXISTS products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(50) NOT NULL COMMENT '产品ID',
    name VARCHAR(100) NOT NULL,
    status TINYINT(1) NOT NULL DEFAULT 1 COMMENT '1=启用 0=禁用',
    api_url VARCHAR(500) DEFAULT NULL COMMENT '接口地址',
    token VARCHAR(500) DEFAULT NULL COMMENT '接口认证Token',
    remark VARCHAR(255) DEFAULT NULL COMMENT '备注',
    updated_at DATETIME DEFAULT NULL,
    updated_by INT DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO products (code, name) VALUES ('default', '默认产品');

-- 用户-产品关联表
CREATE TABLE IF NOT EXISTS user_products (
    user_id INT NOT NULL,
    product_id INT NOT NULL,
    PRIMARY KEY (user_id, product_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 管理员关联到默认产品
INSERT INTO user_products (user_id, product_id)
SELECT u.id, p.id FROM users u, products p
WHERE u.role = 'admin' AND p.code = 'default'
AND NOT EXISTS (SELECT 1 FROM user_products up WHERE up.user_id = u.id AND up.product_id = p.id);

-- 兑换码表
CREATE TABLE IF NOT EXISTS redemption_codes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT DEFAULT NULL COMMENT '所属产品',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    user_id INT DEFAULT NULL COMMENT '创建人(管理员ID)',
    name VARCHAR(100) NOT NULL COMMENT '兑换码名称',
    batch_no VARCHAR(100) NOT NULL COMMENT '批次号',
    type INT NOT NULL DEFAULT 1 COMMENT '兑换码类型',
    code VARCHAR(200) NOT NULL COMMENT '兑换码',
    value DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '兑换码面值',
    status TINYINT NOT NULL DEFAULT 1 COMMENT '1未领取 2已领取',
    expired_at DATETIME DEFAULT NULL COMMENT '过期时间',
    claim_user_id INT DEFAULT NULL COMMENT '领取人ID',
    claimed_at DATETIME DEFAULT NULL COMMENT '领取时间',
    price_tier1 DECIMAL(10,2) DEFAULT NULL COMMENT '出货价一档',
    price_tier2 DECIMAL(10,2) DEFAULT NULL COMMENT '出货价二挡',
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (claim_user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 领取记录表
CREATE TABLE IF NOT EXISTS claim_records (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT DEFAULT NULL COMMENT '所属产品',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    user_id INT DEFAULT NULL COMMENT '用户ID',
    code_name VARCHAR(100) NOT NULL COMMENT '兑换码名称',
    batch_no VARCHAR(100) NOT NULL COMMENT '批次号',
    code_type INT NOT NULL DEFAULT 1 COMMENT '兑换码类型',
    code VARCHAR(200) NOT NULL COMMENT '兑换码',
    value DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '兑换码面值',
    status TINYINT NOT NULL DEFAULT 1 COMMENT '1未使用 2已使用 3过期',
    expired_at DATETIME DEFAULT NULL COMMENT '过期时间',
    used_at DATETIME DEFAULT NULL COMMENT '使用时间',
    updated_at DATETIME DEFAULT NULL COMMENT '更新时间',
    price_tier1 DECIMAL(10,2) DEFAULT NULL COMMENT '出货价一档',
    price_tier2 DECIMAL(10,2) DEFAULT NULL COMMENT '出货价二挡',
    claim_user VARCHAR(50) DEFAULT NULL COMMENT '领取人(用户名)',
    claimed_at DATETIME DEFAULT NULL COMMENT '领取时间',
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 工单表
CREATE TABLE IF NOT EXISTS work_orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT DEFAULT NULL COMMENT '所属产品',
    content TEXT NOT NULL COMMENT '工单内容',
    code VARCHAR(200) DEFAULT NULL COMMENT '关联兑换码',
    attachment VARCHAR(500) DEFAULT NULL COMMENT '附件路径',
    creator_id INT DEFAULT NULL COMMENT '发起人ID',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '发起时间',
    status ENUM('未处理', '已处理', '已驳回') NOT NULL DEFAULT '未处理' COMMENT '状态',
    processed_at DATETIME DEFAULT NULL COMMENT '处理时间',
    processor_id INT DEFAULT NULL COMMENT '处理人ID',
    FOREIGN KEY (creator_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (processor_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE UNIQUE INDEX idx_code ON redemption_codes(code);