WITH NumberedClients AS (
    SELECT
        CONCAT('"', c.ClientIdSubId, '"') AS FormattedId,
        (ROW_NUMBER() OVER (ORDER BY c.ClientIdSubId) - 1) / 1500 AS ChunkNumber
    FROM {CONNECTION_ADDON}[Client] c
    WHERE c.DeleteFlag = 'F'
  	{CLIENT_EXCLUSION_CONDITION}
),
AiwynRecord AS (
    SELECT
  		TOP(1)
        c2.ClientIdSubId           AS OriginatingClientID,
        cc.CLIENTCONTACTFIRSTNAME  AS FirstName,
        cc.CLIENTCONTACTLASTNAME   AS LastName,
        cc.ClientContactHonorificCode AS Honorific,
        cc.ClientContactSuffixCode AS Suffix,
        cc.ClientContactMiddleName AS MiddleName,
        cc.FIRMCONTACTCATEGORYNAME AS FirmCategory,
        NULL AS Salutation,
        NULL AS JobTitle,
        ce.FirmEmailTypeName       AS ClientContactEmailsTypes,
        ce.EmailAddress            AS ClientContactEmails,
        NULL AS ClientContactPhoneNumbersSameAsClient,
        NULL AS ClientContactPhoneNumberTypes,
        NULL AS ClientContactPhoneNumbers,
        NULL AS ClientContactAddressesSameAsClient,
        NULL AS ClientContactAddressesTypes,
        NULL AS ClientContactAddresses,
        NULL AS ClientContactInstantMessengersTypes,
        NULL AS ClientContactInstantMessengers,
        NULL AS ClientContactInstantMessengerProviders
    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
    o.OriginatingClientID,
    o.FirstName,
    o.LastName,
        STRING_AGG(
        CAST(nc.FormattedId AS NVARCHAR(MAX)),
        ', '
    ) AS ClientIDAssignments,
    o.Honorific,
    o.Suffix,
    o.MiddleName,
    o.FirmCategory,
    o.Salutation,
    o.JobTitle,
    o.ClientContactEmailsTypes,
    o.ClientContactEmails,
    o.ClientContactPhoneNumbersSameAsClient,
    o.ClientContactPhoneNumberTypes,
    o.ClientContactPhoneNumbers,
    o.ClientContactAddressesSameAsClient,
    o.ClientContactAddressesTypes,
    o.ClientContactAddresses,
    o.ClientContactInstantMessengersTypes,
    o.ClientContactInstantMessengers,
    o.ClientContactInstantMessengerProviders

FROM NumberedClients nc
CROSS JOIN AiwynRecord o
GROUP BY
	nc.ChunkNumber,
    o.OriginatingClientID,
    o.FirstName,
    o.LastName,
    o.Honorific,
    o.Suffix,
    o.MiddleName,
    o.FirmCategory,
    o.Salutation,
    o.JobTitle,
    o.ClientContactEmailsTypes,
    o.ClientContactEmails,
    o.ClientContactPhoneNumbersSameAsClient,
    o.ClientContactPhoneNumberTypes,
    o.ClientContactPhoneNumbers,
    o.ClientContactAddressesSameAsClient,
    o.ClientContactAddressesTypes,
    o.ClientContactAddresses,
    o.ClientContactInstantMessengersTypes,
    o.ClientContactInstantMessengers,
    o.ClientContactInstantMessengerProviders
ORDER BY nc.ChunkNumber