CREATE PROCEDURE [dbo].[create_insert_dim_customer]
AS
    BEGIN
        DROP TABLE IF EXISTS dim_customer;
        create table dim_customer
        (
            customer_id INT,
            company_name VARCHAR(255) NOT NULL,
            created_on INT NULL,
            first_start_pkg_on VARCHAR(255) NULL,
            first_start_pkg_count VARCHAR(255) NULL,
            currency_id int NULL,
            currency_symbol VARCHAR(8) NULL,
            num_of_account INT Null,
            region_id INT Null,
            region_type_id INT Null,
            country_id INT Null,
            sales_person_id INT Null,
            shared_with INT Null,
            temp_shared_with INT Null,
            leadgen_id INT Null,
            mentor_id INT Null,
            client_grade_id int null,
            active_package_cnt int null,
            last_package_end_date varchar(255) null,
            active_project_cnt int null,
            first_project_started_on varchar(255) null,
            last_project_delivered_on varchar(255) null,
            customer_start_date int default null,
            customer_end_date int default null,
            relationship_id int default 0,
            invoices_count int default 0
        )
        create index dim_customer_customer_id_index
            on dim_customer (customer_id)
       INSERT INTO dim_customer (customer_id, company_name, created_on, first_start_pkg_on,first_start_pkg_count, currency_id, currency_symbol, num_of_account, region_id, region_type_id, country_id,sales_person_id,shared_with,temp_shared_with,leadgen_id,mentor_id,client_grade_id,active_package_cnt,active_project_cnt,first_project_started_on,relationship_id)
        SELECT cm.customer_id,customer_name,convert(varchar,created_at,112) as created_at,null,NULL,cm.currency_id,mcum.currency_symbol,Null,NULL,cm.region_id,country_id,
       sp.sales_person_id,sp.shared_with,sp.temp_shared_with,sp.leadgen_id,sp.mentor_id,
               (select top 1 dim_customer_accounts.client_grade_id from dim_customer_accounts
               left join dim_client_grade_master as gm on gm.grade_id = dim_customer_accounts.client_grade_id
               where cm.customer_id = dim_customer_accounts.customer_id
                   and dim_customer_accounts.client_grade_id is not null
               order by sort_order desc),
               (select count(distinct pm.resource_package_id) from mis_customer_package_master pm
               join mis_ClientResourcePackage_status mCRPs on pm.crp_id = mCRPs.crp_id
               where cm.customer_id = pm.customer_id and mCRPs.end_date is null ),
               (select count(distinct proj.client_project_id) from mis_client_project_master proj where proj.customer_id = cm.customer_id and closed_date is not null and Actual_delivery_date is null),
               (select dbo.convertDateToInt(min(closed_date)) from mis_client_project_master where cm.customer_id = customer_id and closed_date is not null),
               case when cm.relationship_id IS NULL THEN 0 ELSE cm.relationship_id END,
               cr.id
       from mis_customer_master as cm
        left join mis_currency_master mcum on cm.currency_id = mcum.currency_id
        left join mis_client_sales_person sp on cm.customer_id = sp.customer_id
        left join dim_customer_relationship as cr on cr.relationship_value = (case when cm.relationship_id IS NULL THEN 0 ELSE cm.relationship_id END);
        IF OBJECT_ID('dim_country', 'U') IS NOT NULL
        BEGIN
            -- Perform query here
            update dim_customer set dim_customer.region_id = dim_country.region_id from dim_customer left join dim_country on dim_country.country_id = dim_customer.country_id;
            update dim_country set num_of_customer = (select count(*) from dim_customer as dc where dim_country.country_id = dc.country_id);
        END
        IF OBJECT_ID('dim_region', 'U') IS NOT NULL
        BEGIN
            -- Perform query here
            update dim_region set num_of_customer = (select count(*) from dim_customer as dc where dim_region.region_id = dc.region_id) where 1=1;
        END
        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 region_type_id = (case when region_type_id = 2 then 1 when region_type_id = 3 then 2 end);

        update dim_customer
        set dim_customer.last_project_delivered_on = (select dbo.convertDateToInt(max(actual_delivery_date)) from mis_client_project_master where mis_client_project_master.customer_id = dim_customer.customer_id)
        where dim_customer.active_project_cnt = 0;

        UPDATE last_updated_at_history set last_updated_at = getdate() where sp_name = 'create_insert_dim_customer';

    END;
go

