CREATE PROCEDURE [dbo].[create_insert_dim_users]
AS
    BEGIN
        DROP TABLE IF EXISTS dim_users;
        create table dim_users
        (
            user_id int not null,
            joined_on int,
            left_on int,
            emp_total_working_month int,
            emp_previous_experience int,
            user_dob int,
            user_status int,
            is_rejoined int,
            first_name varchar(100),
            middle_name varchar(100),
            last_name varchar(100),
            display_name varchar(200),
            nick_name varchar(100),
            career_portal_id varchar(100),
            email varchar(100),
            employee_code int,
            user_gender varchar(10),
            department_id int,
            technology_id int,
            team_lead_id int null,
            sales_team_lead_id int null,
            team_id int null,
            is_team_lead bit default 0,
            my_team_id int null,
            my_team_tech_id int null,
            my_team_dept_id int null,
            emp_bu_id int,
             emp_type              bigint,
             service_id int,
        l1_round_grade              varchar(10),
        l2_round_grade              varchar(10)
        )
        create index dim_users_user_id_index
            on dim_users (user_id)
        insert into dim_users
        SELECT um.user_id,
              convert(varchar,um.useremployment_datejoining ,112) as joined_on,
              convert(varchar,um.employeeleft_date ,112) as left_on,
              DATEDIFF(month, um.useremployment_datejoining, ISNULL(um.employeeleft_date, getdate())) as emp_total_working_month,
               um.user_previous_exp as emp_previous_experience,
              convert(varchar,um.user_dob ,112) as user_dob,
              user_status,
              0,
               user_firstname,user_middlename,user_lastname,
               iif(user_nickname is null,concat(user_firstname,' ',user_lastname),concat(user_firstname,' ',user_lastname,' (',user_nickname,')')),
               user_nickname,
               candidate_id,
               user_emailaddress,
               employee_code,
               user_gender,
               user_departmentid,
               user_type_id,
               case when user_status = 0 then ex_employee_team_lead else tm.user_id end,
                CASE WHEN um.user_status = 1 THEN (IIF(tm.user_id = 317 AND team_lead.team_id IS NOT NULL, um.user_id, tm.user_id))
               ELSE (IIF(um.ex_employee_team_lead = 317 and exteam_data.members > 0, um.user_id, um.ex_employee_team_lead))
                   END,
               tmm.team_id,
               iif(team_lead.user_id is not null or (exteam_data.members > 0),1,0),
               team_lead.team_id,
               team_lead.team_type,
               team_tech.department_id,
               bt.bu_id,
               employee_type,
               um.service_id,
               (select
    (case when cis.round_num = 1 then candidate_recommendation end) as l1
            from mis_hrms_candidate_interview_process ip
left join mis_hrms_candidate_interview_schedule cis
    on cis.process_id = ip.process_id
where cis.schedule_round = 'Technical' and cis.round_num = 1 and ip.candidate_id = um.candidate_id and ip.process_id in
                    (select top 1 process_id from mis_hrms_candidate_interview_process where candidate_id = um.candidate_id order by process_id desc)
                    ),
              (select
    (case when cis.round_num = 2 then candidate_recommendation end) as l2
            from mis_hrms_candidate_interview_process ip
left join mis_hrms_candidate_interview_schedule cis
    on cis.process_id = ip.process_id
where cis.schedule_round = 'Technical' and cis.round_num = 2 and ip.candidate_id = um.candidate_id and ip.process_id in
                    (select top 1 process_id from mis_hrms_candidate_interview_process where candidate_id = um.candidate_id order by process_id desc)
                    )
        from view_user_master as um
        left join mis_teams_member_master tmm on tmm.user_id = um.user_id
        left join mis_teams_master tm on tm.team_id = tmm.team_id
        left join mis_teams_master team_lead on team_lead.team_id = (select top 1  team_id from mis_teams_master where um.user_id = user_id order by team_id)
        left join mis_resourceleasingcategory_master team_tech on team_tech.resource_leasing_category_id = team_lead.team_type
            left join mis_bu_technology as bt on bt.technology_id = um.user_type_id
        left join (select ex_employee_team_lead, count(user_id) as members from view_user_master where ex_employee_team_lead is not null group by ex_employee_team_lead) as exteam_data
            on exteam_data.ex_employee_team_lead = um.user_id

        UPDATE last_updated_at_history set last_updated_at = getdate() where sp_name = 'create_insert_dim_users';
    END;
go

