create procedure [dbo].[contact_intel_domain_matching_idl]
as
begin

SET NOCOUNT ON
drop table if exists #domainextract
drop table if exists #party
drop table if exists #customers
drop table if exists #Prospects
drop table if exists #hyphenandsuspects
drop table if exists #countrycd
select top 50 * into #domainextract from CONTACT_INTEL_STAGING_IDL_UNIQUE_DOMAIN where process_status = 'NEW' --and email_domain='accenture.com'
order by email_domain


--select top 100 * into #domainextract from CONTACT_INTEL_STAGING_IDL_UNIQUE_DOMAIN where process_status = 'NEW' 
--and email_domain='autostrade.onmicrosoft.com' --  and email_domain='accenture.com'
--order by email_domain

--select * from #domainextract

       --select * from CONTACT_INTEL_STAGING_IDL_UNIQUE_DOMAIN where batch_id = @batch_id
       
       SELECT DISTINCT country_cd
              INTO #countrycd
              FROM C_B_PARTY(NOLOCK)

              UPDATE #domainextract
              SET domain = NULL
              WHERE domain collate database_default IN (
                           SELECT country_cd
                           FROM #countrycd
                           )

              UPDATE #domainextract
              SET d2 = NULL
              WHERE d2 collate database_default IN (
                           SELECT country_cd
                           FROM #countrycd
                           )

              UPDATE #domainextract
              SET d2 = NULL
              WHERE d2 = 'uk'

              SELECT PARTY_NM
                     ,P.ROWID_OBJECT
                     ,COUNTRY_CD
                     ,PARTY_TYP_CD
                     ,SURF_ID
              INTO #party
              FROM C_B_PARTY(NOLOCK) P
              INNER JOIN C_B_ACCOUNT(NOLOCK) A ON A.PARTY_ID = P.ROWID_OBJECT
                     AND IS_MERGED IS NULL
                     AND surf_id IS NOT NULL
              WHERE P.BO_CLASS_CODE = 'Organization'
                     AND P.ACTV_FLG = '1'
                     AND P.HUB_STATE_IND = 1
                     AND SP_END_CUSTOMER IS NULL
                     AND party_nm NOT LIKE '% - %'

              SELECT PARTY_NM
                     ,P.ROWID_OBJECT
                     ,COUNTRY_CD
                     ,PARTY_TYP_CD
                     ,SURF_ID
              INTO #customers
              FROM #party p
              WHERE P.PARTY_TYP_CD IN (
                           'Customer'
                           ,'Customer - Subsidiary'
                           ,'Customer via Partner'
                           )

              SELECT PARTY_NM
                     ,P.ROWID_OBJECT
                     ,COUNTRY_CD
                     ,PARTY_TYP_CD
                     ,SURF_ID
              INTO #prospects
              FROM #party p
              WHERE P.PARTY_TYP_CD = 'Prospect'

              select * into #snawd from SERV_NOW_ACCOUNT_WEBSITE_DOMAIN

              --SELECT PARTY_NM
              --     ,P.ROWID_OBJECT
              --     ,COUNTRY_CD
              --     ,PARTY_TYP_CD
              --     ,SURF_ID
              --INTO #hyphenandsuspects
              --FROM C_B_PARTY(NOLOCK) P
              --INNER JOIN C_B_ACCOUNT(NOLOCK) A ON A.PARTY_ID = P.ROWID_OBJECT
              --     AND IS_MERGED IS NULL
              --     AND surf_id IS NOT NULL
              --WHERE P.BO_CLASS_CODE = 'Organization'
              --     AND P.ACTV_FLG = '1'
              --     AND P.HUB_STATE_IND = 1
              --     AND P.PARTY_TYP_CD IN (
              --            'Suspect'
              --            ,'Prospect'
              --            ,'Customer'
              --            ,'Customer - Subsidiary'
              --            ,'Customer via Partner'
              --            )
              --     AND PARTY_NM LIKE '% - %'
              --     AND SP_END_CUSTOMER IS NOT NULL

                     CREATE NONCLUSTERED INDEX ix_temp1 ON #customers (party_nm);
                     CREATE NONCLUSTERED INDEX ix_temp2 ON #prospects (party_nm);
                     --CREATE NONCLUSTERED INDEX ix_temp ON #hyphenandsuspects (party_nm); 
                     CREATE NONCLUSTERED INDEX ix_temp3 ON #domainExtract (domain,d2,email_domain); 
                     --CREATE NONCLUSTERED INDEX ix_temp4 ON #snawd (party_nm, website); 
                     --CREATE NONCLUSTERED INDEX ix_temp ON #domain (contact_email); 

                     drop table if exists #match
                     CREATE TABLE #MATCH
                     (
                           DOMAIN VARCHAR(100) NULL,
                           D2 VARCHAR(100) NULL,
                           EMAIL_DOMAIN VARCHAR(100) NULL,
                           PARTY_NM VARCHAR(100) NULL,
                           SURF_id VARCHAR(100) NULL

                     )
                     CREATE NONCLUSTERED INDEX ix_temp5 ON #match (SURF_id);       
                     
       
       
              declare @domain  as varchar(100)
                     declare @d2  as varchar(100)       
                     declare @email_domain  as varchar(100)          
                     declare @country1  as varchar(100) 
                     declare @country2  as varchar(100) 
                     declare @country3  as varchar(100) 
                     DECLARE db_cursor CURSOR FOR 
                     SELECT distinct  domain, d2 , email_domain, country1, country2, country3
                     FROM #domainExtract  

                     OPEN db_cursor  
                     FETCH NEXT FROM db_cursor INTO @domain, @d2  ,@email_domain, @country1, @country2, @country3

                     WHILE @@FETCH_STATUS = 0  
                     BEGIN  
                                  drop table if exists #comm
                                  drop table if exists #rel
                                  drop table if exists #acctcount1
                                  drop table if exists #finalParty

                                  SELECT comm_val
                                         ,party_id
                                         ,@email_domain domain
                                  INTO #comm
                                  FROM C_B_PARTY_COMM (NOLOCK) comm
                                  WHERE COMM_TYP_CD = 'EMAIL'
                                  --and  SUBSTRING(comm_val, CHARINDEX('@', comm_val) + 1, LEN(comm_val))  = @email_domain
                                  --AND REPLACE(comm_val,LEFT(comm_val,CHARINDEX('@',comm_val)),'') = @email_domain 
                                  AND comm_val LIKE '%'+@email_domain
                                  
                                  SELECT CHILD_PARTY_ID
                                         ,p.PARTY_NM
                                         ,COMM_VAL
                                         ,domain
                                         ,p.surf_id
                                  INTO #rel
                                  FROM C_B_PARTY_REL(NOLOCK) rel
                                  INNER JOIN #comm c ON rel.PARENT_PARTY_ID = c.PARTY_ID
                                  INNER JOIN #Party P ON p.ROWID_OBJECT = rel.CHILD_PARTY_ID
                                  --INNER JOIN #account a ON p.rowid_object = a.party_id
                                                                     
                                  SELECT DISTINCT domain
                                         ,surf_id
                                         ,count(comm_val) cnt
                                  INTO #acctcount1
                                  FROM #rel
                                  GROUP BY domain
                                         ,surf_id
                                  ORDER BY domain
                                         ,count(comm_val) DESC
                                                                           
                                  SELECT domain
                                         ,surf_id
                     
                                  INTO #finalParty
                                  FROM (
                                         SELECT *
                                                ,ROW_NUMBER() OVER (
                                                       PARTITION BY domain ORDER BY cnt DESC
                                                       ) AS rn
                                         FROM #acctcount1
                                         ) a
                                  --WHERE rn = 1
                                  ORDER BY domain
                                         ,cnt DESC

                     Insert iNTO #MATCH
                     Select distinct  @domain, @D2, @email_domain, party_nm, SURF_ID from (
                           -- website country match
                                  select c.PARTY_NM, c.SURF_ID from #snawd w
                                  inner join #customers c
                                         on c.rowid_object = w.PARTY_ROWID collate database_default
                                  where WEDSITE_DOMAIN = @email_domain 
                                  and (w.COUNTRY_CD = ISNULL(@country1,'') OR  w.COUNTRY_CD = ISNULL(@country2,'') OR  w.COUNTRY_CD = ISNULL(@country3,''))
                                  union
                                  select c.PARTY_NM, c.SURF_ID from #snawd w
                                  inner join #prospects c
                                         on c.rowid_object = w.PARTY_ROWID collate database_default
                                  where WEDSITE_DOMAIN = @email_domain 
                                  and (w.COUNTRY_CD = ISNULL(@country1,'') OR  w.COUNTRY_CD = ISNULL(@country2,'') OR  w.COUNTRY_CD = ISNULL(@country3,''))
                                  -- website  match
                                  union
                           
                                  select c.PARTY_NM, c.SURF_ID from #snawd w
                                  inner join #customers c
                                         on c.rowid_object = w.PARTY_ROWID collate database_default
                                  where WEDSITE_DOMAIN = @email_domain 
                                  union
                                  select c.PARTY_NM, c.SURF_ID from #snawd w
                                  inner join #prospects c
                                         on c.rowid_object = w.PARTY_ROWID collate database_default
                                  where WEDSITE_DOMAIN = @email_domain 
                                  -- Domain Account Match
                                  union
                                  select  c.PARTY_NM, c.SURF_ID  from #finalParty f
                                  inner join #customers c 
                                         on c.surf_id = f.surf_id
                                  union
                                  select c.PARTY_NM, c.SURF_ID  from #finalParty f
                                  inner join #prospects c 
                                         on c.surf_id = f.surf_id
       
                           
                           ) a

                     --     select * from #match

                           IF((select count(*) from #match )= 0 )
                           begin
                           -- select 1
                           
                           INSERT into #MATCH
                           select  @domain, @D2, @email_domain, * from (
                           select  PARTY_NM  , SURF_ID
                           from #customers  where party_nm like @domain + ' %'           and @domain <> ''         
                           union
                           select PARTY_NM, SURF_ID
                           from #prospects  where party_nm like @domain + ' %'           and @domain <> ''         
                           union
                           select  PARTY_NM  , SURF_ID
                           from #customers  where party_nm like @d2 + ' %'              and @d2 <> ''                                
                           union
                           select PARTY_NM, SURF_ID
                           from #prospects  where party_nm like @d2 + ' %'        and @d2 <> ''        
                           union
                           select PARTY_NM , SURF_ID
                           from #customers P  where  replace(party_nm,' ','') =  @d2 and @d2 <> ''              
                           union
                           select  PARTY_NM, SURF_ID
                           from #prospects  where  replace(party_nm,' ','') = @d2 and @d2 <> '' 
                                                       union
                           select PARTY_NM , SURF_ID
                           from #customers P  where  replace(party_nm,' ','') =  @domain  and @domain  <> ''         
                           union
                           select  PARTY_NM, SURF_ID
                           from #prospects  where  replace(party_nm,' ','') = @domain  and @domain  <> ''  
                           union
                           select PARTY_NM , SURF_ID
                           from #customers P  where  replace(party_nm,' ','') like   @d2 + '%' and @d2 <> ''
                           union
                           select PARTY_NM , SURF_ID
                           from #prospects P  where  replace(party_nm,' ','') like   @d2 + '%' and @d2 <> ''     
                           union
                           select PARTY_NM , SURF_ID
                           from #customers P  where  replace(party_nm,' ','') like   @domain + '%' and @domain <> ''
                           union
                           select PARTY_NM , SURF_ID
                           from #prospects P  where  replace(party_nm,' ','') like   @domain + '%' and @domain <> ''

                           )  b

                           end

                           FETCH NEXT FROM db_cursor INTO  @domain, @d2 ,@email_domain   , @country1, @country2, @country3                
                     END 

                     CLOSE db_cursor  
                     DEALLOCATE db_cursor 


                     --select * from #match
       
       drop table if exists #acctcount          
       select p.surf_id, count(parent_party_id) cnt into #acctcount
                     from #party p
              inner join #match m
                           on m.surf_id = p.surf_id collate database_default
              inner join  C_B_PARTY_REL (NOLOCK) rel
                           on rel.child_party_id = p.rowid_object and rel.HUB_STATE_IND = 1            
                     group by p.surf_id

              --select * from #acctcount order by cnt desc

              drop table  if exists #finalacctcnt
                     SELECT *
                           ,ROW_NUMBER() OVER (
                                  ORDER BY cnt DESC
                                  ) AS rn
                           into #finalacctcnt
                     FROM #acctcount      
       --select * from #finalacctcnt
       
       drop table if exists #countrymatchemails
       drop table if exists #allemails
              
                     select distinct  m.party_nm, m.surf_id, party_typ_cd, country_cd,de.domain, de.email_domain, de.d2,de.pkey, rn into #countrymatchemails
              from #match m
              inner join #party p on p.surf_id = m.surf_id collate database_default
              inner join #domainextract de on   m.EMAIL_DOMAIN = de.EMAIL_DOMAIN collate database_default
                           and p.country_cd  collate database_default= coalesce (de.country1,de.country2,de.country3)
              inner join #finalacctcnt a on a.surf_id = m.surf_id collate database_default
              --order by contact_email, rn 
       
       select distinct  m.party_nm, m.surf_id, party_typ_cd, country_cd,de.domain, de.email_domain, de.d2,de.pkey, rn into  #allemails
              from #match m
              inner join #party p on p.surf_id = m.surf_id collate database_default
              inner join #domainextract de on m.EMAIL_DOMAIN = de.EMAIL_DOMAIN collate database_default
              --            and p.country_cd  collate database_default= coalesce (de.country1,de.country2,de.country3)
              inner join #finalacctcnt a on a.surf_id = m.surf_id collate database_default       
       
              CREATE NONCLUSTERED INDEX ix_temp6 ON #countrymatchemails (party_typ_cd); 
              CREATE NONCLUSTERED INDEX ix_temp7 ON #allemails (party_typ_cd); 
       
       
              
              drop table if exists #finaldata
              
              select  *  into #finaldata 
       from (
       select  *, Rank() 
          over ( PARTITION BY EMAIL_DOMAIN    ORDER BY priority , rn  ) AS Rank from (
       select * , 1 priority  from #countrymatchemails where party_typ_cd <> 'Prospect'
       union 
       select *, 2 priority from #allemails where party_typ_cd <> 'Prospect'
       ) a
       ) b  where  rank <= 10
       union
       select * from (
       select  *, Rank() 
          over (PARTITION BY EMAIL_DOMAIN
                ORDER BY priority , rn  ) AS Rank from (
       select * , 1 priority  from #countrymatchemails where party_typ_cd = 'Prospect'
       union 
       select *, 2 priority from #allemails where party_typ_cd = 'Prospect'
       ) a
       ) b  where  rank <= 5
       order by email_domain
       
              

insert into CONTACT_INTEL_STAGING_DOMAIN_MATCH_ACCTS 
       (DOMAIN_KEY,PARTY_NM, SURF_ID, PARTY_TYP_CD, COUNTRY_CD, domain,email_domain,d2, priority_order)
       select distinct pkey, party_nm, surf_id, party_typ_Cd, country_cd, domain, email_domain, d2, rank from #finaldata
       
                           
              declare @batch_id as int
set @batch_id = (select max(batch_id) +1 from CONTACT_INTEL_STAGING_IDL_UNIQUE_DOMAIN)
       Update CONTACT_INTEL_STAGING_IDL_UNIQUE_DOMAIN set batch_id = @batch_id
       where pkey in (select pkey from #domainextract)
       Update CONTACT_INTEL_STAGING_IDL_UNIQUE_DOMAIN set process_status = 'COMPLETE' where batch_id = @batch_id

              --Update CONTACT_INTEL_STAGING_IDL_UNIQUE_DOMAIN set process_status = 'NEW' , batch_id = 0
              --select * from CONTACT_INTEL_STAGING_DOMAIN_MATCH_ACCTS
              
--            truncate table CONTACT_INTEL_STAGING_DOMAIN_MATCH_ACCTS

--select * from CONTACT_INTEL_STAGING_IDL_UNIQUE_DOMAIN where batch_id <>0
     
       end