/***********************************************************************************************************
 * Name         : SnowflakeUtils
 * Purpose      : Utility class for Snowflake methods
 **********************************************************************************************************/
public with sharing class SnowflakeUtils {
    /**
     * @description Creates an SQL query from a list of Fan Ids
     *  to retrieve their Form Responses
     * @param  List<String> Fan Ids
     * @return String   SQL query
     */
    public static String createQueryForVendors(List<String> fanIds) {
        List<String> binds = new List<String>();
        for (String fanId : fanIds) {
            binds.add('\'' + fanId + '\'');
        }
        return 'SELECT DATE(FR.CREATED_DATE) AS \\"createdDate\\",' +
            ' FAN.ID AS \\"fanId\\",' +
            ' FR.ID AS \\"frId\\",' +
            ' FR.EMAIL_C AS \\"frEmail\\",' +
            ' FAN.EMAIL_C AS \\"fanEmail\\",' +
            ' FORM.ID AS \\"formId\\",' +
            ' FORM.FORM_TOOL_NAME_C AS \\"formToolName\\" FROM FORM_RESPONSE_C FR' +
            ' INNER JOIN FAN_C FAN' +
            ' ON FAN.ID = FR.FAN_ID_C' +
            ' INNER JOIN FORM_C FORM' +
            ' ON FR.FORM_ID_C = FORM.ID' +
            ' WHERE FAN.ID IN (' +
            String.join(binds, ',') +
            ')' +
            ' ORDER BY FAN.ID';
    }

    /**
     * @description Creates an SQL query from an Export record based on
     *  which fields the user has selected to export and the criteria
     * @param  Export__c export record
     * @return String   SQL query
     */
    public static String transformExportQuerytoSQL(Export__c exp) {
        Map<String, String> fieldsMapping = new Map<String, String>{
            'Email__c' => 'FR.Email_c',
            'First_Name__c' => 'FR.First_Name_c',
            'Last_Name__c' => 'FR.Last_Name_c',
            'Age_Range__c' => 'FR.Age_Range_c',
            'Gender__c' => 'FR.Gender_c',
            'Fan_City__c' => 'FR.City_c',
            'Fan_State__c' => 'FR.State_c',
            'Fan_Country__c' => 'FR.Country_Region_c',
            'Postal_Code_1__c' => 'FR.Postal_Code_c',
            'Birthday__c' => 'FR.Birthday_mm_dd_c',
            'Birthdate__c' => 'FR.Birthday_c',
            'Mobile_Phone__c' => 'FR.Mobile_Phone_c',
            'Twitter_Handle__c' => 'FR.Twitter_Handle_c',
            'Language__c' => 'FR.Preferred_Language_c',
            'DSP__c' => 'FR.DSP_c',
            'Preferred_Genre__c' => 'FR.Preferred_Genre_c',
            'Custom_Fields_1__c' => 'FR.Custom_Form_Field_1_c',
            'Custom_Fields_2__c' => 'FR.Custom_Form_Field_2_c',
            'Custom_Fields_3__c' => 'FR.Custom_Form_Field_3_c',
            'Custom_Fields_4__c' => 'FR.Custom_Form_Field_4_c',
            'Custom_Fields_5__c' => 'FR.Custom_Form_Field_5_c',
            'Custom_Fields_6__c' => 'FR.Custom_Form_Field_6_c',
            'Custom_Fields_7__c' => 'FR.Custom_Form_Field_7_c',
            'Custom_Fields_8__c' => 'FR.Custom_Form_Field_8_c',
            'Custom_Fields_9__c' => 'FR.Custom_Form_Field_9_c',
            'Custom_Fields_10__c' => 'FR.Custom_Form_Field_10_c',
            'Custom_Fields_11__c' => 'FR.Custom_Form_Field_11_c',
            'Custom_Fields_12__c' => 'FR.Custom_Form_Field_12_c',
            'Custom_Fields_13__c' => 'FR.Custom_Form_Field_13_c',
            'Custom_Fields_14__c' => 'FR.Custom_Form_Field_14_c',
            'Custom_Fields_15__c' => 'FR.Custom_Form_Field_15_c',
            'Custom_Fields_16__c' => 'FR.Custom_Form_Field_16_c',
            'Custom_Fields_17__c' => 'FR.Custom_Form_Field_17_c',
            'Custom_Fields_18__c' => 'FR.Custom_Form_Field_18_c',
            'Custom_Fields_19__c' => 'FR.Custom_Form_Field_19_c',
            'Custom_Fields_20__c' => 'FR.Custom_Form_Field_20_c',
            'Created_Date__c' => 'FR.Created_Date'
        };

        List<String> selectedFields = new List<String>{ 'FR.Id', 'FR.Fan_Id_c' };
        for (String key : fieldsMapping.keySet()) {
            if (exp.isSet(key) && exp.get(key) == true) {
                selectedFields.add(fieldsMapping.get(key));
            }
        }

        Map<String, String> joins = new Map<String, String>();

        if (exp.isSet('Address__c') && exp.get('Address__c') == true) {
            selectedFields.add('FR.Address_1_c');
            selectedFields.add('FR.Address_2_c');
        }

        joins.put('FAN_C', 'FAN_C FAN ON FR.FAN_ID_C = FAN.ID');

        List<String> wheres = new List<String>();
        wheres.add('FORM_ID_C = \'' + exp.get('Form_ID__c') + '\'');
        Boolean ccpa =
            exp.isSet('Exclude_CCPA_Do_Not_Sell__c') && !Boolean.valueOf(exp.get('Exclude_CCPA_Do_Not_Sell__c'));
        wheres.add('FAN.CCPA_Do_Not_Sell_c = ' + ccpa);

        if (exp.isSet('Form_Response_From_Date__c') && exp.get('Form_Response_From_Date__c') != null) {
            Date fromDate = Date.valueOf(exp.get('Form_Response_From_Date__c'));
            wheres.add(
                'FR.Created_Date >= \'' +
                    Datetime.newinstance(fromDate.year(), fromDate.month(), fromDate.day()).format('yyyy-MM-dd') +
                    '\''
            );
        }

        if (exp.isSet('Form_Response_To_Date__c') && exp.get('Form_Response_To_Date__c') != null) {
            Date toDate = Date.valueOf(exp.get('Form_Response_To_Date__c'));
            wheres.add(
                'FR.Created_Date < \'' +
                    Datetime.newinstance(toDate.year(), toDate.month(), toDate.day()).addDays(1).format('yyyy-MM-dd') +
                    '\''
            );
        }

        if (selectedFields.isEmpty()) {
            selectedFields.add('FR.Id');
        }
        String query =
            'SELECT ' +
            String.join(selectedFields, ', ') +
            ' FROM FORM_RESPONSE_C FR INNER JOIN ' +
            String.join(joins.values(), ' INNER JOIN ') +
            ' WHERE ' +
            String.join(wheres, ' AND ');
        exp.Query__c = query;
        return query;
    }
}