SELECT
    first_name,
    last_name,
    `tcomps`.`name` as `company`,
    `temails`.`email` as `email`,
    `tstates`.`abbr` as `state`,
    `ttitles`.`title`,
    IF(
        `tcomps`.`employees_min` = `tcomps`.`employees_max`,
        `tcomps`.`employees_min`,
        IF(
            `tcomps`.`employees_max` = 0
            AND `tcomps`.`employees_min` != 0,
            CONCAT(`tcomps`.`employees_min`, '+'),
            CONCAT(
                " ",
                `tcomps`.`employees_min`,
                '-',
                `tcomps`.`employees_max`
            )
        )
    ) as `employees`,
    IF(
        `tcomps`.`revenue_min` = `tcomps`.`revenue_max`,
        `tcomps`.`revenue_min`,
        IF(
            `tcomps`.`revenue_max` = 0
            AND `tcomps`.`revenue_min` != 0,
            CONCAT(`tcomps`.`revenue_min`, '+'),
            CONCAT(
                " ",
                `tcomps`.`revenue_min`,
                '-',
                `tcomps`.`revenue_max`
            )
        )
    ) as `revenue`,
    `temails`.`id` as `id`
FROM
    `lists`.`query_221585` AS `tref`
    JOIN `app_system`.`emails_status` AS `temails` ON `tref`.`id` = `temails`.`id`
    JOIN `app_system`.`contacts` AS `tsrcs` ON `temails`.`contact_id` = `tsrcs`.`id`
    JOIN `app_system`.`titles` AS `ttitles` ON `tsrcs`.`title_id` = `ttitles`.`id`
    JOIN `app_system`.`companies` AS `tcomps` ON `tsrcs`.`company_id` = `tcomps`.`id`
    LEFT JOIN `app_system`.`companies_locations` AS `tcompslocs` ON `tcompslocs`.`id` = (
        SELECT
            `id`
        FROM
            `app_system`.`companies_locations`
        WHERE
            `company_id` = `tcomps`.`id`
        LIMIT
            1
    )
    LEFT JOIN `app_system`.`countries_abbreviations` AS `tcounabbrs` ON `tcompslocs`.`country_id` = `tcounabbrs`.`country_id`
    LEFT JOIN `app_system`.`titles_job_areas` AS `ttitlesareas` ON `ttitlesareas`.`id` = (
        SELECT
            `id`
        FROM
            `app_system`.`titles_job_areas`
        WHERE
            `title_id` = `ttitles`.`id`
        LIMIT
            1
    )
    LEFT JOIN `app_system`.`states` AS `tstates` ON `tstates`.`id` = `tsrcs`.`state_id`
    LEFT JOIN `app_system`.`companies_industries` AS `tcompsinds_pri` ON `tcompsinds_pri`.`id` = (
        SELECT
            `id`
        FROM
            `app_system`.`companies_industries`
        WHERE
            `company_id` = `tcomps`.`id`
            AND `is_general` = 1
            AND (
                FALSE
                OR FALSE
            )
        LIMIT
            1
    )
    LEFT JOIN `app_system`.`industries` AS `tindstr_pri` ON `tcompsinds_pri`.`industry_id` = `tindstr_pri`.`id`
    LEFT JOIN `app_system`.`industries` AS `tsubindstr_pri` ON `tcompsinds_pri`.`subindustry_id` = `tsubindstr_pri`.`id`
    LEFT JOIN `app_system`.`companies_industries` AS `tcompsinds` ON `tcompsinds`.`id` = (
        SELECT
            `id`
        FROM
            `app_system`.`companies_industries`
        WHERE
            `company_id` = `tcomps`.`id`
            AND (
                FALSE
                OR FALSE
            )
        LIMIT
            1
    )
    LEFT JOIN `app_system`.`industries` AS `tindstr` ON `tcompsinds`.`industry_id` = `tindstr`.`id`
    LEFT JOIN `app_system`.`industries` AS `tsubindstr` ON `tcompsinds`.`subindustry_id` = `tsubindstr`.`id`
    LEFT JOIN `app_system`.`companies_industries` AS `tcompsinds_any_pri` ON `tcompsinds_any_pri`.`id` = (
        SELECT
            `id`
        FROM
            `app_system`.`companies_industries`
        WHERE
            `company_id` = `tcomps`.`id`
            AND `is_general` = 1
        LIMIT
            1
    )
    LEFT JOIN `app_system`.`industries` AS `tindstr_any_pri` ON `tindstr_any_pri`.`id` = `tcompsinds_any_pri`.`industry_id`
    LEFT JOIN `app_system`.`industries` AS `tsubindstr_any_pri` ON `tcompsinds_any_pri`.`subindustry_id` = `tsubindstr_any_pri`.`id`
    LEFT JOIN `app_system`.`companies_industries` AS `tcompsinds_any` ON `tcompsinds_any`.`id` = (
        SELECT
            `id`
        FROM
            `app_system`.`companies_industries`
        WHERE
            `company_id` = `tcomps`.`id`
        LIMIT
            1
    )
    LEFT JOIN `app_system`.`industries` AS `tindstr_any` ON `tcompsinds_any`.`industry_id` = `tindstr_any`.`id`
    LEFT JOIN `app_system`.`industries` AS `tsubindstr_any` ON `tcompsinds_any`.`subindustry_id` = `tsubindstr_any`.`id`
    LEFT JOIN `app_system`.`countries` AS `tcountries` ON `tcountries`.`id` = `tsrcs`.`country_id`
    LEFT JOIN `app_system`.`job_areas` AS `tjareas` ON `ttitlesareas`.`area_id` = `tjareas`.`id`
    LEFT JOIN `app_system`.`job_areas` AS `tjfuncs` ON `ttitlesareas`.`function_id` = `tjfuncs`.`id`
    LEFT JOIN `app_system`.`job_levels` AS `tjlvls` ON `ttitles`.`level` = `tjlvls`.`id`
    LEFT JOIN `app_system`.`prooflinks` AS `tplinks` ON `tplinks`.`contact_id` = `tsrcs`.`id`
    LEFT JOIN `app_system`.`contacts_verification_status` AS `cvs` ON `cvs`.`contact_id` = `temails`.`contact_id`
    LEFT JOIN `app_system`.`companies_verification_status` AS `comp_v_s` ON `comp_v_s`.`company_id` = `tcomps`.`id`
    LEFT JOIN `app_system`.`stats_priorities` AS `tpriority` ON `tpriority`.`list_id` = 221585
    AND `tpriority`.`email_id` = `tref`.`id`
    LEFT JOIN `app_system`.`domains_status` AS `ds` ON `ds`.`id` = `tcomps`.`main_domain_id`
    LEFT JOIN `app_system`.`email_dpo_verifications` AS `edpov` ON `edpov`.`email_id` = `temails`.`id`
    AND `edpov`.`list_id` = 221585
    LEFT JOIN `app_system`.`gdpr_contacts` AS `tgdprconts` ON `tgdprconts`.`contact_id` = `tsrcs`.`id`
    LEFT JOIN `app_system`.`gdpr_contact_phones` AS `tgdprphones` ON `tgdprphones`.`contact_id` = `tsrcs`.`id`
WHERE
    NOT EXISTS (
        SELECT
            *
        FROM
            `app_system`.`emails_status` AS `t_emails`
            JOIN `app_system`.`emails_status` AS `contact_emails`
            LEFT JOIN `app_system`.`stats_priorities` as `t_priorities` ON `t_priorities`.`email_id` = `contact_emails`.`id`
            AND `t_priorities`.`list_id` = 221585
            LEFT JOIN `app_system`.`stats_unsubscribed` as `t_unsubscribed` ON `t_unsubscribed`.`email_id` = `contact_emails`.`id`
            AND `t_unsubscribed`.`list_id` = 221585
            LEFT JOIN `app_system`.`stats_accepted` as `t_accepted` ON `t_accepted`.`email_id` = `contact_emails`.`id`
            AND `t_accepted`.`list_id` = 221585
            LEFT JOIN `app_system`.`stats_priorities` as `tpc_priorities` ON `tpc_priorities`.`email_id` = `contact_emails`.`id`
            AND `tpc_priorities`.`campaign_id` = 2180
            LEFT JOIN `app_system`.`stats_unsubscribed` as `tpc_unsubscribed` ON `tpc_unsubscribed`.`email_id` = `contact_emails`.`id`
            AND `tpc_unsubscribed`.`campaign_id` = 2180
            LEFT JOIN `app_system`.`stats_accepted` as `tpc_accepted` ON `tpc_accepted`.`email_id` = `contact_emails`.`id`
            AND `tpc_accepted`.`campaign_id` = 2180
            LEFT JOIN `app_system`.`stats_priorities` as `tpcl_priorities` ON `tpcl_priorities`.`email_id` = `contact_emails`.`id`
            AND `tpcl_priorities`.`campaign_id` IN (
                SELECT
                    `campaign_id`
                FROM
                    `app_system`.`list_queries`
                WHERE
                    `parent_id` = 0
                    AND `client_id` = 337
            )
            LEFT JOIN `app_system`.`stats_unsubscribed` as `tpcl_unsubscribed` ON `tpcl_unsubscribed`.`email_id` = `contact_emails`.`id`
            AND `tpcl_unsubscribed`.`campaign_id` IN (
                SELECT
                    `campaign_id`
                FROM
                    `app_system`.`list_queries`
                WHERE
                    `parent_id` = 0
                    AND `client_id` = 337
            )
            LEFT JOIN `app_system`.`stats_accepted` as `tpcl_accepted` ON `tpcl_accepted`.`email_id` = `contact_emails`.`id`
            AND `tpcl_accepted`.`campaign_id` IN (
                SELECT
                    `campaign_id`
                FROM
                    `app_system`.`list_queries`
                WHERE
                    `parent_id` = 0
                    AND `client_id` = 337
            )
            LEFT JOIN `app_system`.`stats_denied` as `tpcl_denied` ON `tpcl_denied`.`email_id` = `contact_emails`.`id`
            AND `tpcl_denied`.`campaign_id` IN (
                SELECT
                    `campaign_id`
                FROM
                    `app_system`.`list_queries`
                WHERE
                    `parent_id` = 0
                    AND `client_id` = 337
            )
            LEFT JOIN `app_system`.`domains_status` AS `t_emaildomains` ON `t_emaildomains`.`domain` = `t_emails`.`domain`
        WHERE
            `t_emails`.`id` = `tref`.`id`
            AND `contact_emails`.`contact_id` = `tsrcs`.`id`
            AND (
                `t_priorities`.`id` IS NOT NULL
                OR `t_unsubscribed`.`id` IS NOT NULL
                OR `t_accepted`.`id` IS NOT NULL
                OR `tpc_priorities`.`id` IS NOT NULL
                OR `tpc_unsubscribed`.`id` IS NOT NULL
                OR `tpc_accepted`.`id` IS NOT NULL
                OR EXISTS (
                    SELECT
                        *
                    FROM
                        campaigns_companies_restricted `tpc_com_restr`
                    WHERE
                        `tpc_com_restr`.`campaign_id` = 2180
                        AND `tpc_com_restr`.`company_id` = `tcomps`.`id`
                        AND `tpc_com_restr`.`reason_id` = 1
                )
                OR `tpcl_priorities`.`id` IS NOT NULL
                AND `tpcl_priorities`.`date_engaged` >= DATE_SUB(NOW(), INTERVAL 14 DAY)
                -- OR `tpcl_unsubscribed`.`id` IS NOT NULL
                OR `tpcl_accepted`.`id` IS NOT NULL
                OR `tpcl_denied`.`id` IS NOT NULL
                OR (
                    `t_emaildomains`.`blacklisted` != 0
                    AND `tsrcs`.`validated_by_dpo` <> 1
                )
            )
    )
ORDER BY
    `tref`.`order`
;