-- Query 1 for steps 1:4

WITH contacts_cte AS (
    SELECT
        c.clientidsubid AS AssignmentClientId,
        c.ClientIdent,
        ccbe.StatementFlg AS Statement_Flag,
        ccbe.InvoiceFlg AS Invoice_Flag,
  		concat(c.ClientIdent , cc.ClientContactIdent) as ContactIndex,
        'Client_Contact' AS Origin,
  		cc.ClientContactHonorificCode,
  		cc.CLIENTCONTACTFIRSTNAME,
    	cc.ClientContactMiddleName,
  		cc.CLIENTCONTACTLASTNAME,
		cc.ClientContactSuffixCode,
  		c2.ClientidSubId as OriginatingClientId,
  		ce.FirmEmailTypeName,
		ce.EmailAddress,
  		ccbe.LastUpdatedDateTime,
  		rank() over (partition by concat(c.ClientIdent , cc.ClientContactIdent) order by ccbe.lastupdateddatetime desc) as rn
    FROM {CONNECTION_ADDON}client c
    INNER JOIN {CONNECTION_ADDON}CLIENTCLIENTREFBRIDGE ref ON c.ClientIdent = ref.ClientIdent
    INNER JOIN {CONNECTION_ADDON}CLIENTCONTACT cc ON ref.ClientReferenceIdent = cc.ClientContactIdent
    inner JOIN {CONNECTION_ADDON}CLIENTEMAIL ce ON ce.ReferenceIdent = cc.ClientContactIdent
    INNER JOIN {CONNECTION_ADDON}CLIENTCONTACTBILLINGEMAIL ccbe ON ccbe.ClientContactIdent = ref.ClientReferenceIdent AND ccbe.ClientIdent = c.ClientIdent and ccbe.ClientContactEmailIdent = ce.EmailIdent
    
   inner join {CONNECTION_ADDON}client c2 on c2.ClientIdent = cc.Originatingclientintident
    WHERE 1=1
    AND ce.ISCLIENTCONTACTBILLINGADDRESSFLAG = 'true'
    AND (ce.EmailAddress <> '{SCHEMA_NAME}@inbound.aiwyn.ai' OR ce.EmailAddress IS NULL) 
    and cc.DeleteFlag <> 'T'
    and ce.DeleteFlag <> 'T'
    and c.DeleteFlag <> 'T'
  	and c2.DeleteFlag <> 'T'
 	 {CLIENT_EXCLUSION_CONDITION} 
  
    UNION 
  
    SELECT 
        c.clientidsubid AS AssignmentClientId,
        c.ClientIdent,
        cbe.StatementFlag AS Statement_Flag,
        cbe.InvoiceFlag AS Invoice_Flag,
  		NULL as ContactIndex,
        'CBE' AS Origin,
  		NULL AS ClientContactHonorificCode,
    	NULL AS CLIENTCONTACTFIRSTNAME,
  		NULL AS ClientContactMiddleName,
  		NULL AS CLIENTCONTACTLASTNAME,
  		NULL AS ClientContactSuffixCode,
  		NULL AS OriginatingClientId,
  		ce.FirmEmailTypeName,
  		ce.EmailAddress,
		cbe.LastUpdatedDateTime,
  		1 as rn
    FROM {CONNECTION_ADDON}CLIENT c
    inner JOIN {CONNECTION_ADDON}CLIENTBILLINGEMAIL cbe ON cbe.ClientIdent = c.ClientIdent
    inner JOIN {CONNECTION_ADDON}CLIENTEMAIL ce ON ce.EmailIdent = cbe.ClientEmailIdent
    WHERE 1=1
    AND ce.ISCLIENTBILLINGADDRESSFLAG = 'true'
    AND (ce.EmailAddress <> '{SCHEMA_NAME}@inbound.aiwyn.ai' OR ce.EmailAddress IS NULL) 
    and c.DeleteFlag <> 'T'
    and ce.DeleteFlag <> 'T'
    {CLIENT_EXCLUSION_CONDITION}
),

EligibleClientContacts AS (  
    SELECT 
        hc.*,  
        CASE 
            WHEN MAX(
                CASE 
                    WHEN Statement_Flag = 'T' OR Invoice_Flag = 'T'
                    THEN 1 
                    ELSE 0 
                END
            ) OVER (PARTITION BY hc.ClientIdent) = 1 
            THEN 'True' 
            ELSE 'False' 
        END AS Has_Valid_Flag,
  		CASE 
            WHEN Invoice_Flag = 'F' AND Statement_Flag = 'F' 
            THEN 'True' 
            ELSE 'False' 
        END AS Both_F_Flag
    FROM contacts_cte hc
  where rn = 1
),
AiwynRecord AS (
    SELECT TOP (1)
        c2.ClientIdSubId           AS OriginatingClientId,
        cc.ClientContactHonorificCode AS Honorific,
        cc.CLIENTCONTACTFIRSTNAME  AS ContactFirstName,
        cc.CLIENTCONTACTMIDDLENAME AS MiddleName,
        cc.CLIENTCONTACTLASTNAME   AS ContactLastName,
        cc.ClientContactSuffixCode                      AS Suffix,
        ce.FirmEmailTypeName       AS EmailType,
        ce.EmailAddress            AS EmailAddress
    FROM {CONNECTION_ADDON}[CLIENTCONTACT] cc
    JOIN {CONNECTION_ADDON}[CLIENT] c2
        ON c2.ClientIdent = cc.ORIGINATINGCLIENTINTIDENT
    LEFT JOIN {CONNECTION_ADDON}[CLIENTEMAIL] ce
        ON ce.ReferenceIdent = cc.ClientContactIdent
    WHERE ce.EmailAddress = '{SCHEMA_NAME}@inbound.aiwyn.ai'
      AND LOWER(cc.CLIENTCONTACTFIRSTNAME) LIKE '%aiwyn%'
      AND LOWER(cc.CLIENTCONTACTLASTNAME)  LIKE '%inbound%'
)

SELECT
    [Assignment Client ID],
    [Honorific],
    [Contact First Name],
    [Middle Name],
    [Contact Last Name],
    [Suffix],
    [Originating Client ID],
    [Email Type],
    [Email Address],
    [Receive Invoice],
    [Receive Statement]
FROM (

    -- Existing Contacts (Invoice=FALSE, Statement=TRUE)
    SELECT
        c.AssignmentClientId         AS [Assignment Client ID],
        c.ClientContactHonorificCode AS [Honorific],
        c.CLIENTCONTACTFIRSTNAME     AS [Contact First Name],
        c.ClientContactMiddleName    AS [Middle Name],
        c.CLIENTCONTACTLASTNAME      AS [Contact Last Name],
        c.ClientContactSuffixCode    AS [Suffix],
        c.OriginatingClientId        AS [Originating Client ID],
        c.FirmEmailTypeName          AS [Email Type],
        c.EmailAddress               AS [Email Address],
        'FALSE'                      AS [Receive Invoice],
        'TRUE'                       AS [Receive Statement],
        1                            AS SortOrder
    FROM EligibleClientContacts c
    WHERE c.Has_Valid_Flag = 'TRUE'
      AND c.Both_F_Flag = 'FALSE'
      AND Origin = 'Client_Contact'

    UNION ALL

    -- Aiwyn Inbound Email (Invoice=TRUE, Statement=FALSE)
    SELECT
        vc.AssignmentClientId        AS [Assignment Client ID],
        f.Honorific                  AS [Honorific],
        f.ContactFirstName           AS [Contact First Name],
        f.MiddleName                 AS [Middle Name],
        f.ContactLastName            AS [Contact Last Name],
        f.Suffix                     AS [Suffix],
        f.OriginatingClientId        AS [Originating Client ID],
        f.EmailType                  AS [Email Type],
        f.EmailAddress               AS [Email Address],
        'TRUE'                       AS [Receive Invoice],
        'FALSE'                      AS [Receive Statement],
        2                            AS SortOrder
    FROM (
        SELECT DISTINCT AssignmentClientId
        FROM EligibleClientContacts
        WHERE Has_Valid_Flag = 'TRUE'
          AND Both_F_Flag = 'FALSE'
    ) vc
    CROSS JOIN AiwynRecord f

) FinalResult

ORDER BY
    [Assignment Client ID],
    SortOrder