import os
import mysql.connector
from datetime import datetime

def calculate_impressions():
    # Database connection setup
    db_config = {
        'user': os.getenv('DB_USER'),
        'password': os.getenv('DB_PASSWORD'),
        'host': os.getenv('DB_HOST'),
        'database': os.getenv('DB_NAME'),
        'port': int(os.getenv('DB_PORT', 3306))
    }

    # Connect to the database
    conn = mysql.connector.connect(**db_config)
    cursor = conn.cursor(dictionary=True)

    try:
        # Count UUIDs for each campaign_code from tbl_tracker
        cursor.execute("""
            SELECT campaign_code, COUNT(uuid) AS sales_impression
            FROM tbl_tracker
            GROUP BY campaign_code
        """)
        campaigns = cursor.fetchall()

        # Update sales_impression in tbl_campaigns
        for campaign in campaigns:
            campaign_code = campaign['campaign_code']
            sales_impression = campaign['sales_impression']
            
            # Skip if campaign_code is None
            if campaign_code is None:
                continue
            
            cursor.execute("""
                SELECT * FROM tbl_campaigns WHERE code = %s
            """, (campaign_code,))
            existing_record = cursor.fetchone()

            if existing_record:
                cursor.execute("""
                    UPDATE tbl_campaigns
                    SET sales_impression = %s
                    WHERE code = %s
                """, (sales_impression, campaign_code))
                print(f"Updated sales_impression for campaign {campaign_code}: {sales_impression}")
            else:
                print(f"Campaign {campaign_code} not found in tbl_campaigns - skipping")
        
        # Commit changes
        conn.commit()
        print("All changes committed successfully.")
    except mysql.connector.Error as err:
        print(f"Error: {err}")
        conn.rollback()
    finally:
        # Close the connection
        cursor.close()
        conn.close()
        print("Database connection closed.")

if __name__ == "__main__":
    calculate_impressions()