-- 酒瓶寄存管理系统数据库
-- 创建时间: 2025-01-XX
-- 版本: 2.0 (移动端优化版本)

-- 创建数据库
CREATE DATABASE IF NOT EXISTS bottle_storage 
DEFAULT CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

USE bottle_storage;

-- 2. 客户表
CREATE TABLE IF NOT EXISTS customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL COMMENT '客户姓名',
    phone VARCHAR(20) NOT NULL COMMENT '电话号码',
    vip_level ENUM('普通', '银卡', '金卡', '钻石') DEFAULT '普通' COMMENT 'VIP等级',
    total_bottles INT DEFAULT 0 COMMENT '总存放瓶数',
    current_bottles INT DEFAULT 0 COMMENT '当前存放数',
    notes TEXT COMMENT '备注',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX idx_name (name),
    INDEX idx_phone (phone),
    INDEX idx_vip_level (vip_level)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='客户表';

-- 3. 酒水类型表
CREATE TABLE IF NOT EXISTS bottle_types (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL COMMENT '酒水名称',
    category VARCHAR(50) NOT NULL COMMENT '类别(白酒/红酒/洋酒等)',
    capacity VARCHAR(20) DEFAULT '750ml' COMMENT '容量',
    price DECIMAL(10, 2) COMMENT '参考价格',
    icon VARCHAR(50) COMMENT '图标',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    INDEX idx_category (category),
    INDEX idx_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='酒水类型表';

-- 4. 酒瓶存放记录表
CREATE TABLE IF NOT EXISTS bottles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL COMMENT '客户ID',
    bottle_type_id INT COMMENT '酒水类型ID',
    bottle_name VARCHAR(100) NOT NULL COMMENT '酒水名称',
    category VARCHAR(50) NOT NULL COMMENT '类别',
    capacity VARCHAR(20) DEFAULT '750ml' COMMENT '容量',
    remaining_amount VARCHAR(20) DEFAULT '100%' COMMENT '剩余量',
    remaining_percent DECIMAL(5, 2) DEFAULT 100.00 COMMENT '剩余百分比',
    batch_number VARCHAR(50) COMMENT '批次号(多瓶时使用)',
    bottle_sequence INT DEFAULT 1 COMMENT '瓶子序号(同批次)',
    storage_date DATE NOT NULL COMMENT '存放日期',
    expiry_date DATE COMMENT '过期日期',
    status ENUM('存放中', '已取出') DEFAULT '存放中' COMMENT '状态',
    storage_location VARCHAR(50) COMMENT '存放位置',
    notes TEXT COMMENT '备注',
    created_by INT COMMENT '创建人ID',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    retrieved_at TIMESTAMP NULL COMMENT '取出时间',
    retrieved_by INT COMMENT '取出人ID',
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
    FOREIGN KEY (bottle_type_id) REFERENCES bottle_types(id) ON DELETE SET NULL,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (retrieved_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_customer (customer_id),
    INDEX idx_status (status),
    INDEX idx_storage_date (storage_date),
    INDEX idx_batch (batch_number),
    INDEX idx_expiry_date (expiry_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='酒瓶存放记录表';

-- 5. 操作日志表
CREATE TABLE IF NOT EXISTS operation_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT COMMENT '操作人ID',
    action VARCHAR(50) NOT NULL COMMENT '操作类型',
    module VARCHAR(50) NOT NULL COMMENT '模块',
    description TEXT COMMENT '操作描述',
    ip_address VARCHAR(45) COMMENT 'IP地址',
    user_agent TEXT COMMENT '浏览器信息',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '操作时间',
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_user (user_id),
    INDEX idx_action (action),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='操作日志表';

-- 插入常见酒水类型
INSERT INTO bottle_types (name, category, capacity, price, icon) VALUES 
('茅台酒', '白酒', '500ml', 2999.00, '🍶'),
('五粮液', '白酒', '500ml', 1399.00, '🍶'),
('拉菲红酒', '红酒', '750ml', 3999.00, '🍷'),
('轩尼诗XO', '洋酒', '700ml', 1599.00, '🥃'),
('马爹利', '洋酒', '700ml', 1299.00, '🥃'),
('威士忌', '洋酒', '750ml', 899.00, '🥃'),
('人头马', '洋酒', '700ml', 1099.00, '🥃'),
('剑南春', '白酒', '500ml', 599.00, '🍶');

-- 插入测试客户数据
INSERT INTO customers (name, phone, vip_level, total_bottles, current_bottles, notes) VALUES 
('张三', '13900000001', '金卡', 5, 3, 'VIP客户,长期合作'),
('李四', '13900000002', '银卡', 2, 1, ''),
('王五', '13900000003', '普通', 1, 1, ''),
('赵六', '13900000004', '钻石', 10, 5, '重要客户');

-- 插入测试酒瓶数据
INSERT INTO bottles (customer_id, bottle_type_id, bottle_name, category, capacity, 
                     remaining_amount, remaining_percent, storage_date, expiry_date, 
                     status, created_by) VALUES 
(1, 1, '茅台酒', '白酒', '500ml', '80%', 80.00, '2025-01-01', '2026-01-01', '存放中', 1),
(1, 3, '拉菲红酒', '红酒', '750ml', '100%', 100.00, '2025-01-05', '2026-01-05', '存放中', 1),
(1, 4, '轩尼诗XO', '洋酒', '700ml', '50%', 50.00, '2024-12-20', '2025-12-20', '存放中', 1),
(2, 2, '五粮液', '白酒', '500ml', '100%', 100.00, '2025-01-10', '2026-01-10', '存放中', 1),
(3, 5, '马爹利', '洋酒', '700ml', '60%', 60.00, '2025-01-15', '2026-01-15', '存放中', 1),
(4, 1, '茅台酒', '白酒', '500ml', '100%', 100.00, '2025-01-20', '2026-01-20', '存放中', 1);

-- 创建视图：客户存酒统计
CREATE OR REPLACE VIEW customer_bottle_stats AS
SELECT 
    c.id,
    c.name,
    c.phone,
    c.vip_level,
    COUNT(b.id) as total_bottles,
    SUM(CASE WHEN b.status = '存放中' THEN 1 ELSE 0 END) as current_bottles,
    SUM(CASE WHEN b.status = '已取出' THEN 1 ELSE 0 END) as retrieved_bottles,
    AVG(b.remaining_percent) as avg_remaining_percent,
    MAX(b.storage_date) as last_storage_date
FROM customers c
LEFT JOIN bottles b ON c.id = b.customer_id
GROUP BY c.id, c.name, c.phone, c.vip_level;

-- 创建视图：即将过期酒瓶
CREATE OR REPLACE VIEW expiring_bottles AS
SELECT 
    b.id,
    c.name as customer_name,
    c.phone as customer_phone,
    b.bottle_name,
    b.category,
    b.remaining_amount,
    b.storage_date,
    b.expiry_date,
    DATEDIFF(b.expiry_date, CURDATE()) as days_to_expire
FROM bottles b
INNER JOIN customers c ON b.customer_id = c.id
WHERE b.status = '存放中' 
  AND b.expiry_date IS NOT NULL
  AND DATEDIFF(b.expiry_date, CURDATE()) BETWEEN 0 AND 30
ORDER BY days_to_expire ASC;

-- 创建触发器：更新客户统计
DELIMITER //

CREATE TRIGGER after_bottle_insert
AFTER INSERT ON bottles
FOR EACH ROW
BEGIN
    UPDATE customers 
    SET total_bottles = total_bottles + 1,
        current_bottles = current_bottles + 1
    WHERE id = NEW.customer_id;
END//

CREATE TRIGGER after_bottle_update
AFTER UPDATE ON bottles
FOR EACH ROW
BEGIN
    IF OLD.status = '存放中' AND NEW.status = '已取出' THEN
        UPDATE customers 
        SET current_bottles = current_bottles - 1
        WHERE id = NEW.customer_id;
    END IF;
    
    IF OLD.status = '已取出' AND NEW.status = '存放中' THEN
        UPDATE customers 
        SET current_bottles = current_bottles + 1
        WHERE id = NEW.customer_id;
    END IF;
END//

CREATE TRIGGER after_bottle_delete
AFTER DELETE ON bottles
FOR EACH ROW
BEGIN
    UPDATE customers 
    SET total_bottles = total_bottles - 1
    WHERE id = OLD.customer_id;
    
    IF OLD.status = '存放中' THEN
        UPDATE customers 
        SET current_bottles = current_bottles - 1
        WHERE id = OLD.customer_id;
    END IF;
END//

DELIMITER ;

-- 完成
SELECT '数据库创建完成！' as message;
SELECT '默认管理员账户: admin / admin123' as info;
SELECT '默认员工账户: staff / admin123' as info;
