from mysql.connector import Error
from ui_automation_framework.db.mysql_db_client import MySqlClient as MySqlClientBase


class MySqlClient(MySqlClientBase):
    def get_users_notifications(self, user_id):
        try:
            conn = self.connect()
            cursor = conn.cursor()

            sql = "SELECT message FROM tblApolloPushMessage WHERE userId = %s order by createdAt desc limit 20"

            cursor.execute(sql, (user_id,))
            row = cursor.fetchall()
            self.log.info(row)
        except Error as e:
            self.log.info(e)
        finally:
            cursor.close()
            conn.close()
        return row

    def set_on_boarding_flow(self, user_id, value):
        try:
            conn = self.connect()
            cursor = conn.cursor()

            sql = "UPDATE tblApolloUserOnboarding SET isMobile = %s WHERE userId = %s"

            cursor.execute(sql, (value, user_id))
            # self.log.info(sql)
            # self.log.info("records affected {}".format(cursor.rowcount))
        except Error as e:
            self.log.info(e)
        finally:
            conn.commit()
            cursor.close()
            conn.close()

    def get_user_market(self, user_id):
        try:
            conn = self.connect()
            cursor = conn.cursor()

            sql = "Select m.Name from tblApolloMarket m inner join tblApolloUserMarket Um on Um.MarketId = m.ID " "WHERE Um.UserID = %s"

            cursor.execute(sql, (user_id,))
            row = cursor.fetchone()
            # self.log.info(str(sql))
            # self.log.info(str(row))
        except Error as e:
            self.log.info(e)
        finally:
            cursor.close()
            conn.close()
        return row[0]

    def get_market_and_total_streams_for_a_specific_time_frame(self, date_from, date_to):
        try:
            conn = self.connect()
            cursor = conn.cursor()

            sql = f"SELECT Market, TotalStreams from tblSpotifyAnalyticsAccountStreamInfo " f'WHERE Date >= "{date_from}" and Date <= "{date_to}";'

            cursor.execute(sql)
            result = cursor.fetchall()
            # self.log.info(str(sql))
            # self.log.info(str(result))
        except Error as e:
            self.log.info(e)
        finally:
            cursor.close()
            conn.close()
        return result

    def get_hothits_datetime(self):
        try:
            conn = self.connect()
            cursor = conn.cursor()

            sql = "SELECT * FROM tblApolloKeyValueStorage where `Key` in ('hh_job_last_run_ts', 'hot_hits_latest_ts')"

            cursor.execute(sql)
            result = cursor.fetchall()
            # self.log.info(str(sql))
            # self.log.info(str(result))
        except Error as e:
            self.log.info(e)
        finally:
            cursor.close()
            conn.close()
        return result