init_database.sql

-- Complete database initialization script
-- Create database
CREATE DATABASE IF NOT EXISTS warehouse_management DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE warehouse_management;

-- ============================================
-- PART 1: CREATE TABLES
-- ============================================

-- 1. User table
CREATE TABLE IF NOT EXISTS sys_user (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(64) NOT NULL,
    real_name VARCHAR(50) NOT NULL,
    role ENUM('ADMIN', 'USER') NOT NULL DEFAULT 'USER',
    phone VARCHAR(20),
    email VARCHAR(100),
    status ENUM('ACTIVE', 'INACTIVE') NOT NULL DEFAULT 'ACTIVE',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_username (username),
    INDEX idx_role (role),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. Warehouse table
CREATE TABLE IF NOT EXISTS warehouse (
    warehouse_id INT PRIMARY KEY AUTO_INCREMENT,
    warehouse_code VARCHAR(50) NOT NULL UNIQUE,
    warehouse_name VARCHAR(100) NOT NULL,
    address VARCHAR(200),
    manager VARCHAR(50),
    phone VARCHAR(20),
    status ENUM('ACTIVE', 'INACTIVE') NOT NULL DEFAULT 'ACTIVE',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_warehouse_code (warehouse_code),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. Supplier table
CREATE TABLE IF NOT EXISTS supplier (
    supplier_id INT PRIMARY KEY AUTO_INCREMENT,
    supplier_code VARCHAR(50) NOT NULL UNIQUE,
    supplier_name VARCHAR(100) NOT NULL,
    contact_person VARCHAR(50),
    phone VARCHAR(20),
    email VARCHAR(100),
    address VARCHAR(200),
    status ENUM('ACTIVE', 'INACTIVE') NOT NULL DEFAULT 'ACTIVE',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_supplier_code (supplier_code),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4. Product category table
CREATE TABLE IF NOT EXISTS product_category (
    category_id INT PRIMARY KEY AUTO_INCREMENT,
    category_code VARCHAR(50) NOT NULL UNIQUE,
    category_name VARCHAR(100) NOT NULL,
    parent_id INT,
    description TEXT,
    status ENUM('ACTIVE', 'INACTIVE') NOT NULL DEFAULT 'ACTIVE',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (parent_id) REFERENCES product_category(category_id) ON DELETE RESTRICT,
    INDEX idx_category_code (category_code),
    INDEX idx_parent_id (parent_id),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 5. Product table
CREATE TABLE IF NOT EXISTS product (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_code VARCHAR(50) NOT NULL UNIQUE,
    product_name VARCHAR(100) NOT NULL,
    category_id INT NOT NULL,
    specification VARCHAR(100),
    unit VARCHAR(20) NOT NULL,
    min_stock INT NOT NULL DEFAULT 0,
    price DECIMAL(10, 2),
    description TEXT,
    status ENUM('ACTIVE', 'INACTIVE') NOT NULL DEFAULT 'ACTIVE',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES product_category(category_id) ON DELETE RESTRICT,
    INDEX idx_product_code (product_code),
    INDEX idx_category_id (category_id),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 6. Stock table
CREATE TABLE IF NOT EXISTS stock (
    warehouse_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL DEFAULT 0,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (warehouse_id, product_id),
    FOREIGN KEY (warehouse_id) REFERENCES warehouse(warehouse_id) ON DELETE RESTRICT,
    FOREIGN KEY (product_id) REFERENCES product(product_id) ON DELETE RESTRICT,
    INDEX idx_warehouse_id (warehouse_id),
    INDEX idx_product_id (product_id),
    CONSTRAINT chk_quantity CHECK (quantity >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 7. Inbound order table
CREATE TABLE IF NOT EXISTS inbound_order (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    order_no VARCHAR(50) NOT NULL UNIQUE,
    warehouse_id INT NOT NULL,
    supplier_id INT NOT NULL,
    order_date DATE NOT NULL,
    status ENUM('DRAFT', 'POSTED') NOT NULL DEFAULT 'DRAFT',
    total_amount DECIMAL(12, 2),
    remark TEXT,
    created_by INT NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    posted_by INT,
    posted_at DATETIME,
    FOREIGN KEY (warehouse_id) REFERENCES warehouse(warehouse_id) ON DELETE RESTRICT,
    FOREIGN KEY (supplier_id) REFERENCES supplier(supplier_id) ON DELETE RESTRICT,
    FOREIGN KEY (created_by) REFERENCES sys_user(user_id) ON DELETE RESTRICT,
    FOREIGN KEY (posted_by) REFERENCES sys_user(user_id) ON DELETE RESTRICT,
    INDEX idx_order_no (order_no),
    INDEX idx_warehouse_id (warehouse_id),
    INDEX idx_supplier_id (supplier_id),
    INDEX idx_status (status),
    INDEX idx_order_date (order_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 8. Inbound item table
CREATE TABLE IF NOT EXISTS inbound_item (
    item_id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    price DECIMAL(10, 2),
    amount DECIMAL(12, 2),
    remark VARCHAR(200),
    FOREIGN KEY (order_id) REFERENCES inbound_order(order_id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES product(product_id) ON DELETE RESTRICT,
    INDEX idx_order_id (order_id),
    INDEX idx_product_id (product_id),
    CONSTRAINT chk_inbound_quantity CHECK (quantity > 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 9. Outbound order table
CREATE TABLE IF NOT EXISTS outbound_order (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    order_no VARCHAR(50) NOT NULL UNIQUE,
    warehouse_id INT NOT NULL,
    order_date DATE NOT NULL,
    status ENUM('DRAFT', 'POSTED') NOT NULL DEFAULT 'DRAFT',
    total_amount DECIMAL(12, 2),
    recipient VARCHAR(100),
    department VARCHAR(100),
    remark TEXT,
    created_by INT NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    posted_by INT,
    posted_at DATETIME,
    FOREIGN KEY (warehouse_id) REFERENCES warehouse(warehouse_id) ON DELETE RESTRICT,
    FOREIGN KEY (created_by) REFERENCES sys_user(user_id) ON DELETE RESTRICT,
    FOREIGN KEY (posted_by) REFERENCES sys_user(user_id) ON DELETE RESTRICT,
    INDEX idx_order_no (order_no),
    INDEX idx_warehouse_id (warehouse_id),
    INDEX idx_status (status),
    INDEX idx_order_date (order_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 10. Outbound item table
CREATE TABLE IF NOT EXISTS outbound_item (
    item_id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    price DECIMAL(10, 2),
    amount DECIMAL(12, 2),
    remark VARCHAR(200),
    FOREIGN KEY (order_id) REFERENCES outbound_order(order_id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES product(product_id) ON DELETE RESTRICT,
    INDEX idx_order_id (order_id),
    INDEX idx_product_id (product_id),
    CONSTRAINT chk_outbound_quantity CHECK (quantity > 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 11. Stock ledger table
CREATE TABLE IF NOT EXISTS stock_ledger (
    ledger_id INT PRIMARY KEY AUTO_INCREMENT,
    warehouse_id INT NOT NULL,
    product_id INT NOT NULL,
    transaction_type ENUM('INBOUND', 'OUTBOUND') NOT NULL,
    order_id INT NOT NULL,
    order_no VARCHAR(50) NOT NULL,
    quantity INT NOT NULL,
    balance_before INT NOT NULL,
    balance_after INT NOT NULL,
    transaction_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by INT NOT NULL,
    FOREIGN KEY (warehouse_id) REFERENCES warehouse(warehouse_id) ON DELETE RESTRICT,
    FOREIGN KEY (product_id) REFERENCES product(product_id) ON DELETE RESTRICT,
    FOREIGN KEY (created_by) REFERENCES sys_user(user_id) ON DELETE RESTRICT,
    INDEX idx_warehouse_product (warehouse_id, product_id),
    INDEX idx_order_no (order_no),
    INDEX idx_transaction_type (transaction_type),
    INDEX idx_transaction_date (transaction_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- PART 2: CREATE VIEWS
-- ============================================

-- 1. Low stock warning view
CREATE OR REPLACE VIEW v_low_stock AS
SELECT 
    w.warehouse_id,
    w.warehouse_name,
    p.product_id,
    p.product_code,
    p.product_name,
    pc.category_name,
    p.specification,
    p.unit,
    s.quantity AS current_stock,
    p.min_stock,
    (p.min_stock - s.quantity) AS shortage_quantity,
    p.price,
    s.updated_at AS last_update_time
FROM stock s
INNER JOIN warehouse w ON s.warehouse_id = w.warehouse_id
INNER JOIN product p ON s.product_id = p.product_id
INNER JOIN product_category pc ON p.category_id = pc.category_id
WHERE s.quantity <= p.min_stock
    AND w.status = 'ACTIVE'
    AND p.status = 'ACTIVE'
ORDER BY (p.min_stock - s.quantity) DESC, w.warehouse_id, p.product_id;

-- 2. Stock summary view
CREATE OR REPLACE VIEW v_stock_summary AS
SELECT 
    w.warehouse_id,
    w.warehouse_code,
    w.warehouse_name,
    p.product_id,
    p.product_code,
    p.product_name,
    pc.category_id,
    pc.category_name,
    p.specification,
    p.unit,
    COALESCE(s.quantity, 0) AS current_stock,
    p.min_stock,
    p.price,
    COALESCE(s.quantity, 0) * COALESCE(p.price, 0) AS stock_value,
    CASE 
        WHEN COALESCE(s.quantity, 0) <= p.min_stock THEN '低库存'
        WHEN COALESCE(s.quantity, 0) <= p.min_stock * 1.5 THEN '正常'
        ELSE '充足'
    END AS stock_status,
    s.updated_at AS last_update_time
FROM warehouse w
CROSS JOIN product p
LEFT JOIN stock s ON w.warehouse_id = s.warehouse_id AND p.product_id = s.product_id
INNER JOIN product_category pc ON p.category_id = pc.category_id
WHERE w.status = 'ACTIVE' AND p.status = 'ACTIVE'
ORDER BY w.warehouse_id, pc.category_id, p.product_id;

-- 3. Inbound summary view
CREATE OR REPLACE VIEW v_inbound_summary AS
SELECT 
    io.order_id,
    io.order_no,
    io.order_date,
    w.warehouse_name,
    s.supplier_name,
    io.status,
    COUNT(ii.item_id) AS item_count,
    SUM(ii.quantity) AS total_quantity,
    io.total_amount,
    u1.real_name AS created_by_name,
    io.created_at,
    u2.real_name AS posted_by_name,
    io.posted_at,
    io.remark
FROM inbound_order io
INNER JOIN warehouse w ON io.warehouse_id = w.warehouse_id
INNER JOIN supplier s ON io.supplier_id = s.supplier_id
INNER JOIN sys_user u1 ON io.created_by = u1.user_id
LEFT JOIN sys_user u2 ON io.posted_by = u2.user_id
LEFT JOIN inbound_item ii ON io.order_id = ii.order_id
GROUP BY io.order_id
ORDER BY io.order_date DESC, io.order_id DESC;

-- 4. Outbound summary view
CREATE OR REPLACE VIEW v_outbound_summary AS
SELECT 
    oo.order_id,
    oo.order_no,
    oo.order_date,
    w.warehouse_name,
    oo.recipient,
    oo.department,
    oo.status,
    COUNT(oi.item_id) AS item_count,
    SUM(oi.quantity) AS total_quantity,
    oo.total_amount,
    u1.real_name AS created_by_name,
    oo.created_at,
    u2.real_name AS posted_by_name,
    oo.posted_at,
    oo.remark
FROM outbound_order oo
INNER JOIN warehouse w ON oo.warehouse_id = w.warehouse_id
INNER JOIN sys_user u1 ON oo.created_by = u1.user_id
LEFT JOIN sys_user u2 ON oo.posted_by = u2.user_id
LEFT JOIN outbound_item oi ON oo.order_id = oi.order_id
GROUP BY oo.order_id
ORDER BY oo.order_date DESC, oo.order_id DESC;

-- 5. Stock ledger detail view
CREATE OR REPLACE VIEW v_stock_ledger_detail AS
SELECT 
    sl.ledger_id,
    sl.transaction_date,
    sl.transaction_type,
    CASE sl.transaction_type
        WHEN 'INBOUND' THEN '入库'
        WHEN 'OUTBOUND' THEN '出库'
    END AS transaction_type_name,
    sl.order_no,
    w.warehouse_name,
    p.product_code,
    p.product_name,
    pc.category_name,
    p.specification,
    p.unit,
    sl.quantity,
    sl.balance_before,
    sl.balance_after,
    u.real_name AS operator_name
FROM stock_ledger sl
INNER JOIN warehouse w ON sl.warehouse_id = w.warehouse_id
INNER JOIN product p ON sl.product_id = p.product_id
INNER JOIN product_category pc ON p.category_id = pc.category_id
INNER JOIN sys_user u ON sl.created_by = u.user_id
ORDER BY sl.transaction_date DESC, sl.ledger_id DESC;

-- ============================================
-- PART 3: CREATE STORED PROCEDURES
-- ============================================

DELIMITER $$

-- 1. Inbound posting procedure
DROP PROCEDURE IF EXISTS sp_post_inbound$$
CREATE PROCEDURE sp_post_inbound(
    IN p_order_id INT,
    IN p_user_id INT
)
BEGIN
    DECLARE v_warehouse_id INT;
    DECLARE v_status VARCHAR(20);
    DECLARE v_item_count INT;
    DECLARE v_product_id INT;
    DECLARE v_quantity INT;
    DECLARE v_current_stock INT;
    DECLARE v_done INT DEFAULT FALSE;
    
    DECLARE item_cursor CURSOR FOR 
        SELECT product_id, quantity 
        FROM inbound_item 
        WHERE order_id = p_order_id;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '入库过账失败，事务已回滚';
    END;
    
    START TRANSACTION;
    
    SELECT warehouse_id, status INTO v_warehouse_id, v_status
    FROM inbound_order
    WHERE order_id = p_order_id
    FOR UPDATE;
    
    IF v_status IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '入库单不存在';
    END IF;
    
    IF v_status = 'POSTED' THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '入库单已过账，不能重复过账';
    END IF;
    
    SELECT COUNT(*) INTO v_item_count
    FROM inbound_item
    WHERE order_id = p_order_id;
    
    IF v_item_count = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '入库单没有明细，不能过账';
    END IF;
    
    OPEN item_cursor;
    
    read_loop: LOOP
        FETCH item_cursor INTO v_product_id, v_quantity;
        
        IF v_done THEN
            LEAVE read_loop;
        END IF;
        
        IF v_quantity <= 0 THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '入库数量必须大于0';
        END IF;
        
        SELECT COALESCE(quantity, 0) INTO v_current_stock
        FROM stock
        WHERE warehouse_id = v_warehouse_id AND product_id = v_product_id;
        
        INSERT INTO stock (warehouse_id, product_id, quantity)
        VALUES (v_warehouse_id, v_product_id, v_quantity)
        ON DUPLICATE KEY UPDATE quantity = quantity + v_quantity;
        
        INSERT INTO stock_ledger (
            warehouse_id, product_id, transaction_type, order_id, order_no,
            quantity, balance_before, balance_after, created_by
        )
        SELECT 
            v_warehouse_id, v_product_id, 'INBOUND', p_order_id, order_no,
            v_quantity, v_current_stock, v_current_stock + v_quantity, p_user_id
        FROM inbound_order
        WHERE order_id = p_order_id;
        
    END LOOP;
    
    CLOSE item_cursor;
    
    UPDATE inbound_order
    SET status = 'POSTED',
        posted_by = p_user_id,
        posted_at = NOW()
    WHERE order_id = p_order_id;
    
    COMMIT;
    
END$$

-- 2. Outbound posting procedure
DROP PROCEDURE IF EXISTS sp_post_outbound$$
CREATE PROCEDURE sp_post_outbound(
    IN p_order_id INT,
    IN p_user_id INT
)
BEGIN
    DECLARE v_warehouse_id INT;
    DECLARE v_status VARCHAR(20);
    DECLARE v_item_count INT;
    DECLARE v_product_id INT;
    DECLARE v_product_name VARCHAR(100);
    DECLARE v_quantity INT;
    DECLARE v_current_stock INT;
    DECLARE v_done INT DEFAULT FALSE;
    DECLARE v_error_msg VARCHAR(500);
    
    DECLARE item_cursor CURSOR FOR 
        SELECT product_id, quantity 
        FROM outbound_item 
        WHERE order_id = p_order_id;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '出库过账失败，事务已回滚';
    END;
    
    START TRANSACTION;
    
    SELECT warehouse_id, status INTO v_warehouse_id, v_status
    FROM outbound_order
    WHERE order_id = p_order_id
    FOR UPDATE;
    
    IF v_status IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '出库单不存在';
    END IF;
    
    IF v_status = 'POSTED' THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '出库单已过账，不能重复过账';
    END IF;
    
    SELECT COUNT(*) INTO v_item_count
    FROM outbound_item
    WHERE order_id = p_order_id;
    
    IF v_item_count = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '出库单没有明细，不能过账';
    END IF;
    
    OPEN item_cursor;
    
    read_loop: LOOP
        FETCH item_cursor INTO v_product_id, v_quantity;
        
        IF v_done THEN
            LEAVE read_loop;
        END IF;
        
        IF v_quantity <= 0 THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '出库数量必须大于0';
        END IF;
        
        SELECT p.product_name, COALESCE(s.quantity, 0)
        INTO v_product_name, v_current_stock
        FROM product p
        LEFT JOIN stock s ON s.warehouse_id = v_warehouse_id AND s.product_id = p.product_id
        WHERE p.product_id = v_product_id;
        
        IF v_current_stock < v_quantity THEN
            SET v_error_msg = CONCAT('商品【', v_product_name, '】库存不足，当前库存：', v_current_stock, '，需要出库：', v_quantity);
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = v_error_msg;
        END IF;
        
        UPDATE stock
        SET quantity = quantity - v_quantity
        WHERE warehouse_id = v_warehouse_id AND product_id = v_product_id;
        
        INSERT INTO stock_ledger (
            warehouse_id, product_id, transaction_type, order_id, order_no,
            quantity, balance_before, balance_after, created_by
        )
        SELECT 
            v_warehouse_id, v_product_id, 'OUTBOUND', p_order_id, order_no,
            v_quantity, v_current_stock, v_current_stock - v_quantity, p_user_id
        FROM outbound_order
        WHERE order_id = p_order_id;
        
    END LOOP;
    
    CLOSE item_cursor;
    
    UPDATE outbound_order
    SET status = 'POSTED',
        posted_by = p_user_id,
        posted_at = NOW()
    WHERE order_id = p_order_id;
    
    COMMIT;
    
END$$

DELIMITER ;

-- ============================================
-- PART 4: INSERT INITIAL DATA
-- ============================================

INSERT IGNORE INTO sys_user (username, password, real_name, role, phone, email, status) VALUES
('admin', '8d969eef6ecad3c29a3a629280e686cf0c3f5d5a86aff3ca12020c923adc6c92', '系统管理员', 'ADMIN', '13800138000', 'admin@example.com', 'ACTIVE'),
('user1', '8d969eef6ecad3c29a3a629280e686cf0c3f5d5a86aff3ca12020c923adc6c92', '张三', 'USER', '13800138001', 'user1@example.com', 'ACTIVE'),
('user2', '8d969eef6ecad3c29a3a629280e686cf0c3f5d5a86aff3ca12020c923adc6c92', '李四', 'USER', '13800138002', 'user2@example.com', 'ACTIVE');

INSERT IGNORE INTO warehouse (warehouse_code, warehouse_name, address, manager, phone, status) VALUES
('WH001', '主仓库', '北京市朝阳区xxx路xxx号', '王经理', '010-12345678', 'ACTIVE'),
('WH002', '分仓库A', '上海市浦东新区xxx路xxx号', '李经理', '021-87654321', 'ACTIVE');

INSERT IGNORE INTO supplier (supplier_code, supplier_name, contact_person, phone, email, address, status) VALUES
('SUP001', '供应商A', '张三', '13900139001', 'sup1@example.com', '广州市天河区xxx路xxx号', 'ACTIVE'),
('SUP002', '供应商B', '李四', '13900139002', 'sup2@example.com', '深圳市南山区xxx路xxx号', 'ACTIVE');

INSERT IGNORE INTO product_category (category_code, category_name, parent_id, description, status) VALUES
('CAT001', '电子产品', NULL, '各类电子产品', 'ACTIVE'),
('CAT002', '办公用品', NULL, '办公室日常用品', 'ACTIVE'),
('CAT003', '手机', 1, '智能手机', 'ACTIVE'),
('CAT004', '电脑', 1, '台式机和笔记本', 'ACTIVE');

INSERT IGNORE INTO product (product_code, product_name, category_id, specification, unit, min_stock, price, description, status) VALUES
('PROD001', 'iPhone 15', 3, '128GB', '台', 10, 5999.00, '苹果手机', 'ACTIVE'),
('PROD002', 'MacBook Pro', 4, '14寸 M3', '台', 5, 14999.00, '苹果笔记本', 'ACTIVE'),
('PROD003', 'A4纸', 2, '500张/包', '包', 50, 25.00, '办公用纸', 'ACTIVE');

-- Insert initial stock data
-- This is crucial for stock queries to return data
INSERT IGNORE INTO stock (warehouse_id, product_id, quantity) VALUES
-- 主仓库 (WH001, warehouse_id=1) 的库存
(1, 1, 15),  -- iPhone 15: 15台 (高于最低库存10台)
(1, 2, 3),   -- MacBook Pro: 3台 (低于最低库存5台，会触发预警)
(1, 3, 80),  -- A4纸: 80包 (高于最低库存50包)
-- 分仓库A (WH002, warehouse_id=2) 的库存
(2, 1, 8),   -- iPhone 15: 8台 (低于最低库存10台，会触发预警)
(2, 2, 6),   -- MacBook Pro: 6台 (高于最低库存5台)
(2, 3, 45);  -- A4纸: 45包 (低于最低库存50包，会触发预警)

SELECT 'Database initialized successfully! Tables, Views, Procedures and Initial Data created.' AS message;