class GenerateQuery:
    def __init__(self, property_id):
         self.property_id = property_id
         pass

    def getJourneyExplore(self, user_pseudo_id:str):
        query = f"""
          SELECT 
              eventName as action, 
              source as channel, 
              max(eventTimeStamp) as date 
          FROM `customer-360-profile.client_{self.property_id}.event`
          WHERE user_pseudo_id = "{user_pseudo_id}"
          GROUP BY 1,2
          ORDER BY 3 DESC
          LIMIT 5;
        """
        return query
    
    def getChannelRatio(self, user_pseudo_id:str):
        query = f"""
          SELECT
              source as channel, 
              count(distinct(eventId)) as value
          FROM `customer-360-profile.client_{self.property_id}.event`
          WHERE user_pseudo_id = "{user_pseudo_id}"
          GROUP BY 1
          ORDER BY 1 DESC;
        """
        return query
    
    def getLastEngageAds(self, user_pseudo_id:str):
        query = f"""
          SELECT 
            eventTimeStamp as date,
            user_pseudo_id,
            referral.ads_context_data.photo_url as photo_url 
          FROM `customer-360-profile.client_{self.property_id}.event`
          WHERE user_pseudo_id = "{user_pseudo_id}"
          AND eventName = 'click_messenger_ad'
          GROUP BY 1,2,3;
        """
        return query
    
    def getConversation(self, user_pseudo_id:str):
        query = f"""
          SELECT 
            main.eventTimeStamp as date,
            main.source,
            ep.value as user_message
          FROM `customer-360-profile.client_{self.property_id}.event` as main
          LEFT JOIN UNNEST(eventProperty) as ep
          WHERE main.user_pseudo_id = "{user_pseudo_id}"
          AND main.eventName = 'user_message'
          AND ep.key = 'message'
          GROUP BY 1,2,3
          ORDER BY main.eventTimeStamp DESC;
        """
        return query
    
    def getDetailJourney(self, user_pseudo_id:str):
        query = f"""
          SELECT 
            main.eventId,
            main.eventTimeStamp as date,
            main.eventName as action, 
            main.source as channel,
            main.pageId as page_id,
            ep.key as ep_key,
            ep.value as detail,
            main.referral.ads_context_data.ad_title
          FROM `customer-360-profile.client_{self.property_id}.event` as main
          LEFT JOIN UNNEST(eventProperty) as ep
          WHERE user_pseudo_id = "{user_pseudo_id}"
          and main.eventName NOT LIKE "%remove%"
          GROUP BY 1,2,3,4,5,6,7,8;
        """
        return query
    
    def getUserLastActive7Days(self):
        query = f"""
          SELECT 
            user_pseudo_id
          FROM `customer-360-profile.client_{self.property_id}.event` 
          WHERE eventTimeStamp >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
          GROUP BY 1
          LIMIT 500;
        """
        return query
      
    def getUserLastActive7Days_includeOffline(self):
        query = f"""
          WITH latest_500 AS (
            SELECT *
            FROM (
              -- use the first CTE above
              SELECT user_pseudo_id, eventTimeStamp AS evt_dt, 'event' AS source
              FROM `customer-360-profile.client_{self.property_id}.event`
              WHERE eventTimeStamp >= DATETIME_SUB(CURRENT_DATETIME('Asia/Bangkok'), INTERVAL 7 DAY)
              UNION ALL
              SELECT user_pseudo_id, eventTimeStamp AS evt_dt, 'event_offline' AS source
              FROM `customer-360-profile.client_{self.property_id}.event_offline`
              WHERE eventTimeStamp >= DATETIME_SUB(CURRENT_DATETIME('Asia/Bangkok'), INTERVAL 7 DAY)
            )
            ORDER BY evt_dt DESC
            LIMIT 1000
          )
          SELECT DISTINCT user_pseudo_id
          FROM latest_500;
          """
        return query
    
    def getAutomationCalendar(self, date_start, date_end, status:list):
        if status == 'all':
          query = f"""
            SELECT 
            datetime_trigger AT TIME ZONE 'Asia/Bangkok' AS datetime_trigger,
            automation_id,
            automation_name,
            status,
            active_status
            FROM automation_trigger
            WHERE
                (datetime_trigger AT TIME ZONE 'Asia/Bangkok') BETWEEN '{date_start} 00:00:00' AND '{date_end} 23:59:59'
                AND property_id = '{self.property_id}';
          """
          return query
        else:
          query = f"""
            SELECT 
            datetime_trigger AT TIME ZONE 'Asia/Bangkok' AS datetime_trigger,
            automation_id,
            automation_name,
            status,
            active_status
            FROM automation_trigger
            WHERE
                (datetime_trigger AT TIME ZONE 'Asia/Bangkok') BETWEEN '{date_start} 00:00:00' AND '{date_end} 23:59:59'
                AND property_id = '{self.property_id}'
                AND status IN ({','.join(f"'{s}'" for s in status)});
          """
          return query
    def getAutomationCalendarV2(self, date_start, date_end, status:list, timezone:str='Asia/Bangkok'):
        if status == 'all':
          query = f"""
            SELECT 
              main.datetime_trigger AT TIME ZONE 'UTC' AT TIME ZONE '{timezone}' AS datetime_trigger,
              main.automation_id as automation_id,
              au.automation_name as automation_name,
              main.status as status,
              main.automation_status as active_status
            FROM trigger_manager as main
            LEFT JOIN automation_context as au
              ON au.automation_id = main.automation_id
            WHERE
                (main.datetime_trigger AT TIME ZONE 'UTC' AT TIME ZONE '{timezone}') BETWEEN '{date_start} 00:00:00' AND '{date_end} 23:59:59'
                AND main.property_id = '{self.property_id}';
          """
          return query
        else:
          query = f"""
            SELECT 
              main.datetime_trigger AT TIME ZONE 'UTC' AT TIME ZONE '{timezone}' AS datetime_trigger,
              main.automation_id as automation_id,
              au.automation_name as automation_name,
              main.status as status,
              main.automation_status as active_status
            FROM trigger_manager as main
            LEFT JOIN automation_context as au
              ON au.automation_id = main.automation_id
            WHERE
                (main.datetime_trigger AT TIME ZONE 'UTC' AT TIME ZONE '{timezone}') BETWEEN '{date_start} 00:00:00' AND '{date_end} 23:59:59'
                AND main.property_id = '{self.property_id}'
                AND main.status IN ({','.join(f"'{s}'" for s in status)});
          """
          return query

    def updateAutomationStatus(self, automation_id:str, status:bool):
        query = f"""
        UPDATE automation_trigger
        SET active_status = {status}
        WHERE automation_id = '{automation_id}'
        AND property_id = '{self.property_id}';
        """
        return query
    
    def schedualAutomation(self, datetime:str):
        query = f"""
          SELECT
            log_id,
            datetime_trigger AT TIME ZONE 'Asia/Bangkok' AS datetime_trigger,
            automation_id,
            automation_name,
            status,
            active_status
          FROM automation_trigger
          WHERE
            (datetime_trigger AT TIME ZONE 'Asia/Bangkok') = '{datetime}'
            AND property_id = '{self.property_id}'
            AND active_status = True
            AND status = 'pending';
        """
        return query
    def updateAutomationLog(self, automation_id:str, log_id:str):
        query = f"""
        UPDATE automation_trigger
        SET status = 'pushed'
        WHERE automation_id = '{automation_id}'
        AND property_id = '{self.property_id}'
        AND log_id = '{log_id}';
        """
        return query
    
    def getNextSchedual(self, automation_id:str, now:str):
        query = f"""
        SELECT
          MIN((datetime_trigger AT TIME ZONE 'Asia/Bangkok')) as datetime_trigger
        FROM automation_trigger
        WHERE 
          automation_id = '{automation_id}'
          AND status = 'pending'
          AND active_status = true
          AND (datetime_trigger AT TIME ZONE 'Asia/Bangkok') > '{now}';
        """
        return query