CREATE TABLE IF NOT EXISTS sys_user (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(100) NOT NULL,
    real_name VARCHAR(50),
    phone VARCHAR(20),
    email VARCHAR(100),
    dept_id BIGINT DEFAULT 0,
    employee_type VARCHAR(20) DEFAULT 'common_user',
    status TINYINT DEFAULT 0,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS sys_role (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    role_code VARCHAR(50) NOT NULL UNIQUE,
    role_name VARCHAR(50) NOT NULL,
    role_scope VARCHAR(20) DEFAULT 'tenant',
    parent_id BIGINT DEFAULT 0,
    dept_id BIGINT DEFAULT 0,
    username VARCHAR(50),
    password VARCHAR(100),
    status TINYINT DEFAULT 1,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS sys_menu (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    menu_name VARCHAR(50) NOT NULL,
    menu_type TINYINT,
    permission VARCHAR(100),
    parent_id BIGINT DEFAULT 0,
    sort_order INT DEFAULT 0,
    component VARCHAR(200),
    icon VARCHAR(50),
    status TINYINT DEFAULT 1
);

CREATE TABLE IF NOT EXISTS sys_user_role (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    role_id BIGINT NOT NULL,
    UNIQUE KEY uk_user_role (user_id, role_id)
);

CREATE TABLE IF NOT EXISTS sys_role_menu (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    role_id BIGINT NOT NULL,
    menu_id BIGINT NOT NULL,
    UNIQUE KEY uk_role_menu (role_id, menu_id)
);

CREATE TABLE IF NOT EXISTS sys_dept (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    dept_name VARCHAR(50) NOT NULL,
    dept_code VARCHAR(50),
    parent_id BIGINT DEFAULT 0,
    leader_id BIGINT,
    sort_order INT DEFAULT 0,
    status TINYINT DEFAULT 1,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS bpm_process_definition (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    process_code VARCHAR(50) NOT NULL UNIQUE,
    process_name VARCHAR(100) NOT NULL,
    category VARCHAR(50),
    form_id BIGINT,
    model_json TEXT NOT NULL,
    version INT DEFAULT 1,
    status TINYINT DEFAULT 0,
    description VARCHAR(500),
    create_by BIGINT,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_by BIGINT,
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    deleted TINYINT DEFAULT 0
);

CREATE TABLE IF NOT EXISTS bpm_process_instance (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    instance_no VARCHAR(50) NOT NULL UNIQUE,
    process_definition_id BIGINT NOT NULL,
    version INT NOT NULL,
    title VARCHAR(200),
    applicant_id BIGINT NOT NULL,
    applicant_dept_id BIGINT,
    form_data TEXT,
    current_node_id VARCHAR(50),
    status TINYINT DEFAULT 0,
    result TINYINT,
    start_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    end_time TIMESTAMP,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    deleted TINYINT DEFAULT 0
);

CREATE TABLE IF NOT EXISTS bpm_approval_task (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    instance_id BIGINT NOT NULL,
    node_id VARCHAR(50) NOT NULL,
    node_name VARCHAR(100),
    node_type VARCHAR(20),
    assignee_id BIGINT,
    assignee_type VARCHAR(20),
    task_status TINYINT DEFAULT 0,
    approval_result TINYINT,
    approval_comment TEXT,
    attachment_urls TEXT,
    timeout_time TIMESTAMP,
    timeout_action VARCHAR(20),
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    handle_time TIMESTAMP,
    deleted TINYINT DEFAULT 0
);

CREATE TABLE IF NOT EXISTS bpm_approval_record (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    task_id BIGINT NOT NULL,
    instance_id BIGINT NOT NULL,
    node_id VARCHAR(50) NOT NULL,
    node_name VARCHAR(100),
    operator_id BIGINT NOT NULL,
    operator_name VARCHAR(50),
    action_type VARCHAR(20) NOT NULL,
    action_result VARCHAR(20),
    comment TEXT,
    attachment_urls TEXT,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    deleted TINYINT DEFAULT 0
);
