

-- 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 '图标',
    is_active TINYINT(1) DEFAULT 1 COMMENT '是否启用',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    INDEX idx_category (category),
    INDEX idx_is_active (is_active)
) 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 NOT NULL COMMENT '酒水类型ID',
    bottle_name VARCHAR(100) NOT NULL COMMENT '酒水名称',
    category VARCHAR(50) NOT NULL COMMENT '类别',
    capacity VARCHAR(20) DEFAULT '750ml' COMMENT '容量',
    percentage DECIMAL(4, 1) COMMENT '剩余百分比',
    storage_location VARCHAR(100) COMMENT '存放位置',
    status ENUM('存放中', '已取出', '已过期') DEFAULT '存放中' COMMENT '状态',
    storage_date DATE NOT NULL COMMENT '存放日期',
    expiry_date DATE COMMENT '到期日期',
    pickup_date DATE 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 '更新时间',
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE RESTRICT,
    FOREIGN KEY (bottle_type_id) REFERENCES bottle_types(id) ON DELETE RESTRICT,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_customer_id (customer_id),
    INDEX idx_bottle_type_id (bottle_type_id),
    INDEX idx_status (status),
    INDEX idx_storage_date (storage_date),
    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',
    operation_type VARCHAR(50) NOT NULL COMMENT '操作类型',
    table_name VARCHAR(50) COMMENT '表名',
    record_id INT COMMENT '记录ID',
    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_id (user_id),
    INDEX idx_operation_type (operation_type),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='操作日志表';

-- 创建视图：客户酒瓶统计
CREATE OR REPLACE VIEW customer_bottle_stats AS
SELECT 
    c.id,
    c.name,
    c.phone,
    c.vip_level,
    c.total_bottles,
    c.current_bottles,
    COUNT(DISTINCT CASE WHEN b.status = '存放中' THEN b.id END) as active_bottles,
    COUNT(DISTINCT CASE WHEN b.status = '已取出' THEN b.id END) as picked_bottles,
    MIN(CASE WHEN b.status = '存放中' THEN b.storage_date END) as earliest_storage_date,
    MAX(CASE WHEN b.status = '存放中' THEN b.storage_date END) as latest_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, c.total_bottles, c.current_bottles;

-- 创建视图：即将到期的酒瓶
CREATE OR REPLACE VIEW expiring_bottles AS
SELECT 
    b.*,
    c.name as customer_name,
    c.phone as customer_phone,
    c.vip_level,
    DATEDIFF(b.expiry_date, CURDATE()) as days_until_expiry
FROM bottles b
INNER JOIN customers c ON b.customer_id = c.id
WHERE b.status = '存放中' 
AND b.expiry_date IS NOT NULL
AND b.expiry_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 30 DAY)
ORDER BY b.expiry_date ASC;

-- 创建触发器：更新客户统计（插入）
DELIMITER $$
CREATE TRIGGER update_customer_stats_after_insert
AFTER INSERT ON bottles
FOR EACH ROW
BEGIN
    UPDATE customers 
    SET 
        total_bottles = total_bottles + 1,
        current_bottles = current_bottles + CASE WHEN NEW.status = '存放中' THEN 1 ELSE 0 END
    WHERE id = NEW.customer_id;
END$$
DELIMITER ;

-- 创建触发器：更新客户统计（更新）
DELIMITER $$
CREATE TRIGGER update_customer_stats_after_update
AFTER UPDATE ON bottles
FOR EACH ROW
BEGIN
    IF OLD.status != NEW.status THEN
        UPDATE customers 
        SET current_bottles = current_bottles + 
            CASE 
                WHEN NEW.status = '存放中' THEN 1
                WHEN OLD.status = '存放中' THEN -1
                ELSE 0
            END
        WHERE id = NEW.customer_id;
    END IF;
END$$
DELIMITER ;

-- 创建触发器：更新客户统计（删除）
DELIMITER $$
CREATE TRIGGER update_customer_stats_after_delete
AFTER DELETE ON bottles
FOR EACH ROW
BEGIN
    UPDATE customers 
    SET 
        total_bottles = total_bottles - 1,
        current_bottles = current_bottles - CASE WHEN OLD.status = '存放中' THEN 1 ELSE 0 END
    WHERE id = OLD.customer_id;
END$$
DELIMITER ;
-- 默认密码: admin123

-- 插入测试酒水类型
INSERT INTO bottle_types (name, category, capacity, price, icon) VALUES
('茅台飞天', '白酒', '500ml', 2999.00, 'bi-cup-straw'),
('五粮液', '白酒', '500ml', 1399.00, 'bi-cup-straw'),
('国窖1573', '白酒', '500ml', 1299.00, 'bi-cup-straw'),
('拉菲', '红酒', '750ml', 8999.00, 'bi-wine'),
('奔富', '红酒', '750ml', 599.00, 'bi-wine'),
('轩尼诗XO', '洋酒', '700ml', 1899.00, 'bi-cup-hot'),
('芝华士', '洋酒', '700ml', 399.00, 'bi-cup-hot'),
('青岛啤酒', '啤酒', '330ml', 5.00, 'bi-droplet'),
('百威', '啤酒', '330ml', 8.00, 'bi-droplet'),
('其他', '其他', '不限', 0.00, 'bi-box');

-- 插入测试客户
INSERT INTO customers (name, phone, vip_level, total_bottles, current_bottles, notes) VALUES
('张三', '13800138001', '金卡', 5, 3, 'VIP客户，长期合作'),
('李四', '13800138002', '银卡', 3, 2, '普通客户'),
('王五', '13800138003', '钻石', 10, 8, '重要客户，特殊关照'),
('赵六', '13800138004', '普通', 2, 1, '新客户');

-- 插入测试酒瓶寄存记录
INSERT INTO bottles (customer_id, bottle_type_id, bottle_name, category, capacity, percentage, storage_location, status, storage_date, expiry_date, created_by, notes) VALUES
(1, 1, '茅台飞天', '白酒', '500ml', 80.0, 'A区-001', '存放中', '2025-01-15', '2026-01-15', 1, '客户特别交代小心保管'),
(1, 2, '五粮液', '白酒', '500ml', 90.0, 'A区-002', '存放中', '2025-01-20', '2026-01-20', 1, ''),
(1, 4, '拉菲', '红酒', '750ml', 100.0, 'B区-001', '存放中', '2025-02-01', '2026-02-01', 1, '未开封'),
(2, 3, '国窖1573', '白酒', '500ml', 75.0, 'A区-003', '存放中', '2025-01-25', '2026-01-25', 1, ''),
(2, 5, '奔富', '红酒', '750ml', 85.0, 'B区-002', '存放中', '2025-02-05', '2026-02-05', 1, ''),
(3, 1, '茅台飞天', '白酒', '500ml', 70.0, 'A区-004', '存放中', '2025-01-10', '2026-01-10', 1, ''),
(3, 1, '茅台飞天', '白酒', '500ml', 95.0, 'A区-005', '存放中', '2025-01-18', '2026-01-18', 1, ''),
(3, 6, '轩尼诗XO', '洋酒', '700ml', 60.0, 'C区-001', '存放中', '2025-02-10', '2026-02-10', 1, 'VIP客户'),
(4, 8, '青岛啤酒', '啤酒', '330ml', 50.0, 'D区-001', '存放中', '2025-02-15', '2025-05-15', 1, '短期寄存');

-- 插入操作日志
INSERT INTO operation_logs (user_id, operation_type, table_name, record_id, description, ip_address) VALUES
(1, 'INSERT', 'bottles', 1, '新增酒瓶寄存记录: 茅台飞天', '127.0.0.1'),
(1, 'INSERT', 'bottles', 2, '新增酒瓶寄存记录: 五粮液', '127.0.0.1'),
(1, 'INSERT', 'customers', 1, '新增客户: 张三', '127.0.0.1');
