from snowflake_connector.etl_connector import SnowflakeSQLExecutor


class SFExecutor(SnowflakeSQLExecutor):

    # FACT_CONFLICT schema is already derived from the SNOWFLAKE_SCHEMA environment variable
    def get_unresolved_es_ids_from_sf(self, env):
        """Get all elasticsearch IDs (via Snowflake)."""
        sql_select = f"""SELECT distinct(es_id)
                         FROM fact_conflict
                         WHERE es_id IS NOT NULL
                         AND es_id != ''
                         AND resolved_datetime IS NULL
                         AND conflict_id NOT IN (
                             SELECT conflict_id FROM ows_conflict_manager.%s.action
                         );""" % ('prod' if env == 'prod' else 'qa')
        return self.fetchall(sql_select)

    def set_es_ids_to_unindexed(self, es_ids):
        """Setting es_id to NULL and es_indexed to false in snowflake will cause swf-yt-conflict-elasticsearch to
        insert these ids into elasticsearch on the next run."""
        es_ids_string_placeholder = ','.join(['%s'] * len(es_ids))
        sql_update = f"""UPDATE fact_conflict	       
                         SET es_id = NULL, es_indexed = false	       
                         WHERE es_id IN (%s);""" % (es_ids_string_placeholder)  # replace all ids with %s placeholders
        self.execute(sql_update, tuple(es_ids))