-- 部门表
CREATE TABLE sys_department (
    id NUMBER(19) PRIMARY KEY,
    dept_code VARCHAR2(50 CHAR) NOT NULL UNIQUE,
    dept_name VARCHAR2(100 CHAR) NOT NULL,
    parent_code VARCHAR2(50 CHAR) DEFAULT '0',
    sort_order NUMBER(10) DEFAULT 0,
    description VARCHAR2(500 CHAR),
    status NUMBER(1) DEFAULT 1,
    create_time DATE DEFAULT SYSDATE,
    update_time DATE DEFAULT SYSDATE,
    deleted NUMBER(1) DEFAULT 0,
    version NUMBER(10) DEFAULT 1
);

-- 用户表
CREATE TABLE sys_user (
    id NUMBER(19) PRIMARY KEY,
    username VARCHAR2(50 CHAR) NOT NULL UNIQUE,
    real_name VARCHAR2(100 CHAR),
    password VARCHAR2(100 CHAR) NOT NULL,
    email VARCHAR2(100 CHAR),
    phone VARCHAR2(20 CHAR),
    dept_code VARCHAR2(50 CHAR),
    status NUMBER(1) DEFAULT 1,
    create_time DATE DEFAULT SYSDATE,
    update_time DATE DEFAULT SYSDATE,
    deleted NUMBER(1) DEFAULT 0,
    version NUMBER(10) DEFAULT 1
);

-- Excel数据表（交易流水数据）
CREATE TABLE data_excel (
    id NUMBER(19) PRIMARY KEY,
    sort_no NUMBER(10),
    seq_no NUMBER(10),
    secondary_unit VARCHAR2(100 CHAR),
    unit_code VARCHAR2(50 CHAR),
    unit_name VARCHAR2(200 CHAR),
    transaction_no VARCHAR2(100 CHAR),
    summary VARCHAR2(500 CHAR),
    amount NUMBER(19, 2),
    accounting_period VARCHAR2(20 CHAR),
    voucher_no VARCHAR2(50 CHAR),
    bp_contract_no VARCHAR2(100 CHAR),
    contract_center_no VARCHAR2(100 CHAR),
    bp_invoice_no VARCHAR2(100 CHAR),
    gk_invoice_no VARCHAR2(100 CHAR),
    business_relation VARCHAR2(200 CHAR),
    remark VARCHAR2(500 CHAR),
    status NUMBER(1) DEFAULT 1,
    create_time DATE DEFAULT SYSDATE,
    update_time DATE DEFAULT SYSDATE,
    deleted NUMBER(1) DEFAULT 0,
    version NUMBER(10) DEFAULT 1
);

-- 创建序列
CREATE SEQUENCE seq_sys_department START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE seq_sys_user START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE seq_data_excel START WITH 1 INCREMENT BY 1;

-- 创建触发器（自动填充ID）
CREATE OR REPLACE TRIGGER trg_sys_department
BEFORE INSERT ON sys_department
FOR EACH ROW
BEGIN
    SELECT seq_sys_department.NEXTVAL INTO :NEW.id FROM DUAL;
END;
/

CREATE OR REPLACE TRIGGER trg_sys_user
BEFORE INSERT ON sys_user
FOR EACH ROW
BEGIN
    SELECT seq_sys_user.NEXTVAL INTO :NEW.id FROM DUAL;
END;
/

CREATE OR REPLACE TRIGGER trg_data_excel
BEFORE INSERT ON data_excel
FOR EACH ROW
BEGIN
    SELECT seq_data_excel.NEXTVAL INTO :NEW.id FROM DUAL;
END;
/

-- 插入初始化数据
INSERT INTO sys_department (dept_code, dept_name, parent_code, sort_order, description, status) VALUES
('001', '总公司', '0', 1, '总公司', 1);

INSERT INTO sys_department (dept_code, dept_name, parent_code, sort_order, description, status) VALUES
('001001', '技术部', '001', 1, '技术研发部门', 1);

INSERT INTO sys_department (dept_code, dept_name, parent_code, sort_order, description, status) VALUES
('001002', '市场部', '001', 2, '市场营销部门', 1);

-- 创建默认用户 sysadmin，密码 sysadmin (MD5加密)
INSERT INTO sys_user (username, real_name, password, email, phone, dept_code, status) VALUES
('sysadmin', '系统管理员', '7c4a8d09ca3762af61e59520943dc26494f8941b', 'sysadmin@example.com', '13800138000', '001', 1);

COMMIT;