use obpm2_security;
create table tenant_employee_records
(
    id                    bigint       not null primary key AUTO_INCREMENT,
    employee_id           varchar(100) not null references tenant_employees (id),
    organization_id       varchar(50)  not null references tenant_organizations (id),
    tenant_user_id        varchar(50)  not null references tenant_users (id),
    occupation_type       tinyint(2) not null default 0,
    status                tinyint(2) not null default 0,
    operator_id           varchar(150) null,
    operator_name         varchar(200) null,
    operator_created_time datetime null,
    action_time           timestamp not null default current_timestamp,
    action_note           varchar(2000),
    position              varchar(200),
    is_domain_admin       tinyint(1) not null default 0,
    interview             varchar(255),
    department_id         varchar(255),
    department_name       varchar(200),
    created_time          timestamp    not null default current_timestamp
);

INSERT INTO `tenant_employee_records`(`employee_id`, `organization_id`, `tenant_user_id`, `occupation_type`, `status`, `operator_id`, `operator_name`, `operator_created_time`, `action_time`, `action_note`, `position`, `insure`, `is_domain_admin`, `interview`, `department_id`, `department_name`, `created_time`)
select
    ee.ID,ee.organization_id,ee.tenant_user_id,ee.occupation_type,ee.`status`,
    (case when ee.`status`=0 then ee.hired_operator_id else ee.leave_operator_id end) as operator_id,
    (case when ee.`status`=0 then ee.hired_operator_name else ee.leave_operator_name end) as operator_name,
    (case when ee.`status`=0 then ee.hired_operator_created_time else ee.leave_operator_created_time end) as operator_created_time,

    ev.event_time,ev.note,ee.position,
    IFNULL(ee.insure,0),ee.is_domain_admin,ee.interview,ee.department_id,dt.`name`,ev.created_time
from tenant_employees ee left join tenant_departments dt on ee.department_id=dt.id
                         join tenant_employee_events ev on ev.employee_id=ee.id
where ev.event_type in (0,1) and ee.organization_id is not null