init_db_direct.sql

-- 直接连接postgres数据库并创建wms_db
\connect postgres

DROP DATABASE IF EXISTS wms_db;
CREATE DATABASE wms_db ENCODING = 'UTF8';

-- 连接到wms_db
\connect wms_db

-- 创建Schema
CREATE SCHEMA IF NOT EXISTS wms;
CREATE SCHEMA IF NOT EXISTS sys;

-- 用户表
CREATE TABLE sys.sys_user (
    id BIGSERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    real_name VARCHAR(50),
    email VARCHAR(100),
    phone VARCHAR(20),
    status INTEGER DEFAULT 1,
    deleted INTEGER DEFAULT 0,
    created_by VARCHAR(50),
    created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_by VARCHAR(50),
    updated_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入管理员 (密码: admin123)
INSERT INTO sys.sys_user (username, password, real_name, status) VALUES
('admin', '$2a$10$N.zmdr9k7uOCQb376NoUnuTJ8iAt6Z5EHsM8lE9lBOsl7iKTVKIUi', '系统管理员', 1);

-- 仓库表
CREATE TABLE wms.wms_warehouse (
    id BIGSERIAL PRIMARY KEY,
    warehouse_code VARCHAR(50) NOT NULL UNIQUE,
    warehouse_name VARCHAR(100) NOT NULL,
    warehouse_type INTEGER DEFAULT 1,
    province VARCHAR(50),
    city VARCHAR(50),
    district VARCHAR(50),
    address VARCHAR(255),
    contact_person VARCHAR(50),
    contact_phone VARCHAR(20),
    total_area DECIMAL(10,2),
    usable_area DECIMAL(10,2),
    status INTEGER DEFAULT 1,
    enabled INTEGER DEFAULT 1,
    deleted INTEGER DEFAULT 0,
    created_by VARCHAR(50),
    created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_by VARCHAR(50),
    updated_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO wms.wms_warehouse (warehouse_code, warehouse_name, province, city, district, address, contact_person, contact_phone, total_area, usable_area, status) VALUES
('WH001', '杭州中心仓', '浙江省', '杭州市', '余杭区', '文一西路xxx号', '张三', '13800138000', 5000.00, 4500.00, 1);

SELECT 'Database initialized!' as status;