WITH onboarding_table AS (
SELECT  DISTINCT
        onboarding.onboarding_typeforminitialdata.guid AS guid,
        MAX(onboarding.onboarding_typeforminitialdata.phone) AS ini_phone,
        MAX(onboarding.onboarding_typeforminitialdata.owner_name)ini_name,
        MAX(CASE WHEN onboarding.onboarding_typeformfieldmap.NAME = 'business_name_correct' THEN onboarding.onboarding_typeformanswer.VALUE END) AS business_name_correct,
        COALESCE(MAX(CASE WHEN onboarding.onboarding_typeformfieldmap.NAME = 'business_name' THEN onboarding.onboarding_typeformanswer.VALUE END),MAX(onboarding.onboarding_typeforminitialdata.venue_name)) AS business_name,
        MAX(CASE WHEN onboarding.onboarding_typeformfieldmap.NAME = 'opening_hours_type' THEN onboarding.onboarding_typeformanswer.VALUE END) AS opening_hours_type,
        MAX(CASE WHEN onboarding.onboarding_typeformfieldmap.NAME = 'opening_hours_text' THEN onboarding.onboarding_typeformanswer.VALUE END) AS opening_hours_text,
        MAX(CASE WHEN onboarding.onboarding_typeformfieldmap.NAME = 'show_phone' THEN onboarding.onboarding_typeformanswer.VALUE END) AS show_phone,
        MAX(CASE WHEN onboarding.onboarding_typeformfieldmap.NAME = 'phone_correct' THEN onboarding.onboarding_typeformanswer.VALUE END) AS phone_correct,
        MAX(CASE WHEN onboarding.onboarding_typeformfieldmap.NAME = 'phone' THEN onboarding.onboarding_typeformanswer.VALUE END) AS phone,
        MAX(CASE WHEN onboarding.onboarding_typeformfieldmap.NAME = 'show_address' THEN onboarding.onboarding_typeformanswer.VALUE END) AS show_address,
        MAX(CASE WHEN onboarding.onboarding_typeformfieldmap.NAME = 'city' THEN onboarding.onboarding_typeformanswer.VALUE END) AS city,
        MAX(CASE WHEN onboarding.onboarding_typeformfieldmap.NAME = 'state' THEN onboarding.onboarding_typeformanswer.VALUE END) AS state,
        MAX(CASE WHEN onboarding.onboarding_typeformfieldmap.NAME = 'street' THEN onboarding.onboarding_typeformanswer.VALUE END) AS street,
        MAX(CASE WHEN onboarding.onboarding_typeformfieldmap.NAME = 'postcode' THEN onboarding.onboarding_typeformanswer.VALUE END) AS postcode,
        MAX(CASE WHEN onboarding.onboarding_typeformfieldmap.NAME = 'areas_covered' THEN onboarding.onboarding_typeformanswer.VALUE END) AS areas_covered,
        MAX(CASE WHEN onboarding.onboarding_typeformfieldmap.NAME = 'category' THEN onboarding.onboarding_typeformanswer.VALUE END) AS tf_category,
        LENGTH(MAX(CASE WHEN onboarding.onboarding_typeformfieldmap.NAME = 'category' THEN onboarding.onboarding_typeformanswer.VALUE END)) AS tf_category_length,
        MAX(CASE WHEN onboarding.onboarding_typeformfieldmap.NAME = 'service_upload_type' THEN onboarding.onboarding_typeformanswer.VALUE END) AS service_upload_type,
        MAX(CASE WHEN onboarding.onboarding_typeformfieldmap.NAME = 'service_image' THEN onboarding.onboarding_typeformanswer.VALUE END) AS service_image,
        MAX(CASE WHEN onboarding.onboarding_typeformfieldmap.NAME = 'service_text' THEN onboarding.onboarding_typeformanswer.VALUE END) AS service_text,
        LENGTH(MAX(CASE WHEN onboarding.onboarding_typeformfieldmap.NAME = 'service_text' THEN onboarding.onboarding_typeformanswer.VALUE END)) AS service_text_length,
        MAX(CASE WHEN onboarding.onboarding_typeformfieldmap.NAME = 'upload_images' THEN onboarding.onboarding_typeformanswer.VALUE END) AS upload_images,
        MAX(CASE WHEN onboarding.onboarding_typeformfieldmap.NAME = 'logo' THEN onboarding.onboarding_typeformanswer.VALUE END) AS logo,
        MAX(CASE WHEN onboarding.onboarding_typeformfieldmap.NAME = 'storefront' AND onboarding.onboarding_typeformfieldmap.type_string = 'image' THEN onboarding.onboarding_typeformanswer.VALUE END) AS storefront_image,
        COUNT(CASE WHEN onboarding.onboarding_typeformfieldmap.NAME IN ('image_1','image_2','image_3','image_4','image_5','image_6') AND onboarding.onboarding_typeformanswer.VALUE IS NOT NULL THEN 1 END) AS image_count_max_6,
        MAX(CASE WHEN onboarding.onboarding_typeformfieldmap.NAME = 'promotion_title' THEN onboarding.onboarding_typeformanswer.VALUE END) AS promotion_title,
        MAX(CASE WHEN onboarding.onboarding_typeformfieldmap.NAME = 'promotion_description' THEN onboarding.onboarding_typeformanswer.VALUE END) AS promotion_description,
        MAX(CASE WHEN onboarding.onboarding_typeformfieldmap.NAME IN ('year_established','years of service') THEN onboarding.onboarding_typeformanswer.VALUE END) AS established,
        MAX(CASE WHEN onboarding.onboarding_typeformfieldmap.NAME IN ('Helped by','team','team size','team_size') THEN
            (CASE WHEN onboarding.onboarding_typeformanswer.VALUE LIKE '%team of 2-3 people%' THEN '2-3'
            WHEN onboarding.onboarding_typeformanswer.VALUE = 'de tu equipo de 2-3 personas' THEN '2-3'
            WHEN onboarding.onboarding_typeformanswer.VALUE = 'you' THEN '1'
                WHEN onboarding.onboarding_typeformanswer.VALUE = 'me' THEN '1'
                    WHEN onboarding.onboarding_typeformanswer.VALUE = 'tuya' THEN '1'
            WHEN onboarding.onboarding_typeformanswer.VALUE LIKE '%4+ people%' THEN '4+'
            WHEN onboarding.onboarding_typeformanswer.VALUE = 'de tu equipo de 4 o más personas' THEN '4+'
                ELSE onboarding.onboarding_typeformanswer.VALUE END) END) AS team_capacity,
            MAX(CASE WHEN onboarding.onboarding_typeformfieldmap.NAME = 'social_media' THEN onboarding.onboarding_typeformanswer.VALUE END) AS social_platforms,
            MAX(CASE WHEN onboarding.onboarding_typeformfieldmap.NAME = 'social_handle' THEN onboarding.onboarding_typeformanswer.VALUE END) AS social_handle,
            MAX(CASE WHEN onboarding.onboarding_typeformfieldmap.NAME = 'website' THEN onboarding.onboarding_typeformanswer.VALUE END) AS website,
            MAX(CASE WHEN onboarding.onboarding_typeformfieldmap.NAME = 'certifications' THEN onboarding.onboarding_typeformanswer.VALUE END) AS certifications,
            MAX(CASE WHEN onboarding.onboarding_typeformfieldmap.NAME = 'business_description' THEN onboarding.onboarding_typeformanswer.VALUE END) AS business_description,
            LENGTH(MAX(CASE WHEN onboarding.onboarding_typeformfieldmap.NAME = 'business_description' THEN onboarding.onboarding_typeformanswer.VALUE END)) AS business_description_length,
            MAX(CASE WHEN onboarding.onboarding_typeformfieldmap.NAME = 'amenities' THEN onboarding.onboarding_typeformanswer.VALUE END) AS amenities
             FROM onboarding.onboarding_typeformanswer
LEFT JOIN
        onboarding.onboarding_typeformfieldmap
    ON onboarding.onboarding_typeformfieldmap.typeform_id = onboarding.onboarding_typeformanswer.field_id
LEFT JOIN
        onboarding.onboarding_typeforminitialdata
    ON onboarding.onboarding_typeforminitialdata.form_submission_id = onboarding.onboarding_typeformanswer.form_submission_id
GROUP BY 1
),
venues_table AS (SELECT primary_guid,
       content_creation_status,
       locale,
       description,
       NAME,
       created_at,
       office_number,
       mobile_number
                FROM venues.venues_venue
                INNER JOIN venues.venues_content
                ON venues.venues_content.venue_id = venues.venues_venue.id
                left JOIN venues.venues_contact
                on venues.venues_contact.venue_id = venues.venues_venue.id
                )
SELECT DISTINCT ON (1) venues_table.primary_guid,
        venues_table.content_creation_status,
        venues_table.locale,
        venues_table.created_at,
        venues_table.office_number,
        venues_table.mobile_number,
        onboarding_table.tf_category,
        onboarding_table.service_text,
        onboarding_table.service_image,
        onboarding_table.business_description,
        venues_table.NAME
        FROM venues_table
        LEFT JOIN onboarding_table
        ON venues_table.primary_guid = onboarding_table.guid
        WHERE content_creation_status IN ('potentially-illegal','not-enough-info')
        AND created_at >= '2019-12-01'
        AND created_at < '2020-01-14'
        GROUP BY 1,2,3,4,5,6,7,8,9,10,11