import mysql.connector
from config import *


def get_connection():
    return mysql.connector.connect(
        host=MYSQL_HOST,
        user=MYSQL_USER,
        password=MYSQL_PASSWORD,
        database=MYSQL_DATABASE
    )


def get_campaign_performance(campaign_id):
    conn = get_connection()
    cursor = conn.cursor(dictionary=True)

    query = """
    SELECT
        c.campaignid,
        c.campaignname,
        SUM(ds.impressions) as impressions,
        SUM(ds.clicks) as clicks,
        SUM(ds.conversions) as conversions,
        SUM(ds.total_revenue) as spend,
        SUM(ds.total_revenue) as revenue,
        ROUND((SUM(ds.clicks)/SUM(ds.impressions))*100, 2) as ctr
    FROM
        rv_data_summary_ad_hourly ds
    JOIN
        rv_banners b ON ds.ad_id = b.bannerid
    JOIN
        rv_campaigns c ON b.campaignid = c.campaignid
    WHERE
        c.campaignid = %s
    GROUP BY
        c.campaignid
    """

    cursor.execute(query, (campaign_id,))

    result = cursor.fetchone()

    cursor.close()
    conn.close()

    return result