use obpm2;
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),
    insure                tinyint(1) not null default 0,
    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`, `is_domain_admin`, `interview`, `department_id`, `department_name`, `created_time`)
select
    ee.ID,ee.organization_id,ee.tenant_user_id,ee.occupation_type,
    (case when ev.`event_type`=0 then 0 else 1 end) as `status`,
    (case when ev.`event_type`=0 then ee.hired_operator_id else ee.leave_operator_id end) as operator_id,
    (case when ev.`event_type`=0 then ee.hired_operator_name else ee.leave_operator_name end) as operator_name,
    (case when ev.`event_type`=0 then ev.event_time else ev.event_time end) as operator_created_time,

    ev.event_time,ev.note,ee.position,
    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 and ee.tenant_user_id in (select id from obpm2.tenant_users);

update obpm2.tenant_employee_records set operator_created_time=created_time where operator_created_time='0000-00-00'
update obpm2.tenant_employee_records set action_time=created_time where action_time='0000-00-00'