CREATE PROCEDURE [dbo].[create_insert_dim_customer_package]
AS
    Begin
        drop table if exists dim_customer_package;
        create table dim_customer_package
        (
            crp_id int not null,
            parent_id  int not null,
            package_id varchar(255),
            account_id int not null,
            ignore_from_outstanding bit default 0,
            customer_id int not null,
            crp_title varchar(255),
            technology_id int not null,
            sub_technology_id int null,
            resource_technology_id int null,
            currency_id int not null,
            is_active bit default 0,
            is_outside bit default 0,
            region_type_id int not null ,
            sales_type_id int null,
            shift_id int null,
            working_hours_id int null,
            location_id int null,
            month_working_day_id int null,
            billing_method_id int not null,
            billing_type_id int null,
            week_working_day_id int null,
            week_other_days varchar(255) null,
            package_rate money null,
            old_package_rate money null,
            per_day_rate money null,
            monthly_rate money null,
            inr_rate float null,
            usd_rate float null,
            billing_sub_type_id int null,
            previous_pkg_id int null,
            pkg_replacement_reason_id int null,
            prev_balance_leaves float null,
            ot_type_id int null,
            package_status_id int null,
            actual_start_date int null,
            new_start_date int null,
            start_date int null,
            end_date int null,
            start_note text null,
            end_note text null,
            dc_reason_id int null,
            dc_other_reason text null,
            dc_sub_reason_id int null,
            why_no_replacement text null,
            is_lost_entire bit null ,
            actual_end_crp_id int null,
            actual_end_date int null,
            actual_dc_reason_id int null,
            actual_dc_other_reason text null,
            actual_dc_sub_reason_id int null,
            actual_why_no_replacement text null,
            actual_is_lost_entire bit null,
            user_id int null,
            res_tech_id int null,
            alias_name varchar(255) null,
            resource_name varchar(255) null,
            resource_type_id int null,
            resource_contribution_factor decimal(10,2) null,
            created_at int null,
            pkg_tech_bu_id int null, res_tech_bu_id int null
        )
        create index dim_customer_package_crp_id
        on dim_customer_package (crp_id)
         create index dim_customer_package_parent_id
        on dim_customer_package (parent_id);
        create index dim_customer_package_customer_id__index
	on dim_customer_package (customer_id);
        truncate table dim_customer_package;
        insert into dim_customer_package
        select
pkg.crp_id,
IIF(pkg.parent_id IS NULL, pkg.crp_id, pkg.parent_id) as parent_id,
pkg.package_id,
pkg.account_id,
acc.ignore_from_outstanding,
pkg.customer_id,
pkg.crp_title,
pkg.technology_id,
pkg.sub_technology_id,
pkg.resource_technology_id,
pkg.currency_id,
pkg.is_active,
pkg.is_outside,
pkg.crp_region,
null,
pkg.shift_id,
dpwh.hours_id,
pkg.location_id,
dmwd.day_id,
pkg.billing_method_id,
pkg.billing_type,
dwwd.working_day_id,
pkg.other_working_days,
pkg.crp_rate,
pkg.old_crp_rate,
CASE WHEN pkg.billing_method_id = 1 THEN (pkg.crp_rate * 20)/30 WHEN pkg.billing_method_id = 2 THEN (pkg.crp_rate * pkg.working_hours * 20)/30 ELSE pkg.crp_rate/30 END,
CASE WHEN pkg.billing_method_id = 1 THEN (pkg.crp_rate * 20) WHEN pkg.billing_method_id = 2 THEN (pkg.crp_rate * pkg.working_hours * 20) ELSE pkg.crp_rate END,
pkg.inr_rate,
pkg.usd_rate,
pkg.billing_sub_type,
pkg.prev_package,
pkg.replacement_reason_id,
pkg.prev_balance_leaves,
pkg.ot_type,
dps.status_id,
               null as actual_start_date,
               null as new_start_date,
convert(varchar,stats.date,112) as start_date,
convert(varchar,stats.end_date,112) ,
stats.note as start_note,
stats.end_note as end_note,
stats.dc_reason_id,
stats.other_reason,
stats.dc_sub_reason_id,
stats.why_no_replacement,
CASE WHEN stats.is_lost_entire = 'Yes' THEN 1 WHEN stats.is_lost_entire = 'No' THEN 0 ELSE null END,
prev.crp_id as actual_end_crp_id,
(case when prev.crp_id is null and (stats.status = 'restarted' or stats.status = 'start') then convert(varchar,stats.end_date,112) else convert(varchar,prev.end_date,112) end) as actual_end_date,
prev.dc_reason_id as actual_reason_id,
prev.other_reason as actual_other_reason,
prev.dc_sub_reason_id as actual_sub_reason_id,
prev.why_no_replacement as actual_no_replacement,
CASE WHEN prev.is_lost_entire = 'Yes' THEN 1 WHEN prev.is_lost_entire = 'No' THEN 0 ELSE null END,
               rd.user_id,
               res_user.technology_id,
               rd.alias_name,
               rd.resource_name,
                pkg.type_id,
               rtm.contribution_factor,
               convert(varchar,pkg.created_at,112),
               mbt_pkg.bu_id,
               mbt_res.bu_id
from mis_customer_package_master as pkg
join mis_customer_accounts as acc on acc.account_id = pkg.account_id
left join dim_package_sales_type dpst on pkg.crp_sales_type = dpst.type_value
left join dim_package_working_hours dpwh on dpwh.hours_value = pkg.working_hours
left join dim_monthly_working_days as dmwd on dmwd.day_value = pkg.working_days_id
left join dim_week_working_days as dwwd on dwwd.working_day_desc = pkg.working_days
left join mis_ClientResourcePackage_status as stats on stats.crp_id = pkg.crp_id
left join dim_package_status as dps on dps.status_value = stats.status
left join mis_resourceleasingtype_master as rtm on rtm.resourceleasingtype_id = pkg.type_id
left join mis_bu_technology as mbt_pkg on mbt_pkg.technology_id = pkg.technology_id
left join mis_ClientResourcePackage_status as next_restart on next_restart.discontinue_id = (
            select top 1 m1.discontinue_id
            from mis_ClientResourcePackage_status as m1 where (m1.parent_id = (IIF(stats.parent_id IS NULL, stats.crp_id, stats.parent_id)))
                                                        and m1.discontinue_id > stats.discontinue_id and status = 'restarted'
            ) and (stats.status = 'start' or stats.status = 'restarted')
left join mis_ClientResourcePackage_status as prev on prev.discontinue_id = (
            select top 1 m2.discontinue_id
            from mis_ClientResourcePackage_status as m2 where (m2.parent_id = (IIF(stats.parent_id IS NULL, stats.crp_id, stats.parent_id)))and
                                                       ((m2.discontinue_id < next_restart.discontinue_id)
                                                            or next_restart.discontinue_id is null
                                                            )
                                                          and status != 'restarted' and status != 'start'
                                                          and m2.discontinue_id > stats.discontinue_id
            order by discontinue_id desc
                                                        ) and (stats.status = 'start' or stats.status = 'restarted')
left join mis_ClientResourceDetails rd on rd.client_resource_package_id = pkg.crp_id
        left join dim_users as res_user on res_user.user_id = rd.user_id
        left join mis_bu_technology as mbt_res on mbt_res.technology_id = res_user.technology_id
        where pkg.is_deleted = 0
        order by stats.discontinue_id;

        update p1
set p1.actual_end_date = p2.actual_end_date
       from dim_customer_package p1
       left join dim_customer_package as p2 on p2.crp_id = (
           select top 1 p3.crp_id from dim_customer_package p3 where p3.crp_id < p1.crp_id
                                                           and p3.parent_id =  p1.parent_id
           and p3.actual_end_date is not null
           order by p3.start_date desc
           )
where p1.actual_end_date is null and p1.start_date < p2.actual_end_date;

         update cp
    set cp.actual_start_date = dbo.convertDateToInt(pm.date)
    from dim_customer_package as cp
join mis_ClientResourcePackage_status as pm on cp.parent_id = pm.crp_id;

    update cp
    set cp.new_start_date = (select top 1 pm.start_date from dim_customer_package as pm where pm.parent_id = cp.parent_id and pm.start_date <= cp.start_date and package_status_id in (1,2)
                             order by start_date desc)
    from dim_customer_package as cp ;

        UPDATE p
        SET p.sales_type_id = (case when p.actual_start_date between c.first_start_pkg_on and dbo.convertDateToInt(dateadd(month,3,first_pkg_date.Date))
        then 1 else 2 end)
        FROM dim_customer_package as p
join dim_customer as c on c.customer_id = p.customer_id
join dim_date as first_pkg_date on first_pkg_date.DateKey = c.first_start_pkg_on
where  p.start_date is not null
and crp_id = parent_id;
    UPDATE p1
    SET p1.sales_type_id = p2.sales_type_id
    FROM dim_customer_package as p1
    JOIN dim_customer_package as p2 ON p1.parent_id = p2.crp_id
    WHERE p1.crp_id != p1.parent_id and p1.sales_type_id is null;

    update dim_customer_package
        set region_type_id = (case when region_type_id = 2 then 1 when region_type_id = 3 then 2 end);
        -- update last package end date
    update dim_customer
    set last_package_end_date = (select max(end_date) from dim_customer_package where dim_customer_package.customer_id = dim_customer.customer_id)
    where active_package_cnt = 0;

     IF OBJECT_ID('dim_customer_package', 'U') IS NOT NULL
        BEGIN
            -- Perform query here
            update dim_customer set dim_customer.first_start_pkg_on = dcp.start_date, dim_customer.first_start_pkg_count = dcp.package_count
            from dim_customer
                left join (select start_date,customer_id,
                                  count(crp_id) over ( partition by customer_id,start_date order by start_date asc) as package_count,
                                  row_number() over ( partition by customer_id order by start_date asc) as row_number
                                  from dim_customer_package as cp where parent_id = crp_id and start_date is not null) as dcp
                    on dim_customer.customer_id = dcp.customer_id and row_number = 1;
        END

    update dim_customer
    set dim_customer.customer_start_date = (
        iif(first_start_pkg_on is not null and first_project_started_on is not null,
            (
                iif(first_start_pkg_on > first_project_started_on, first_project_started_on, first_start_pkg_on)),
                iif(first_start_pkg_on is null and first_project_started_on is not null, first_project_started_on, first_start_pkg_on) ))
    where first_start_pkg_on is not null or first_project_started_on is not null;

    update dim_customer
    set dim_customer.customer_end_date = (
        iif(last_package_end_date is not null and last_project_delivered_on is not null,
            (iif(last_package_end_date > last_project_delivered_on, last_package_end_date, last_project_delivered_on)),
            (iif(last_package_end_date is null and first_start_pkg_on is null, last_project_delivered_on , last_package_end_date))))
    where last_package_end_date is not null or last_project_delivered_on is not null;



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

