import os
import pymysql

from dotenv import load_dotenv

load_dotenv()


def get_creator_stats_by_date(creator: str, date: str):

    conn = pymysql.connect(
        host=os.getenv("DB_HOST"),
        port=int(os.getenv("DB_PORT", 3306)),
        user=os.getenv("DB_USER"),
        password=os.getenv("DB_PASS"),
        database=os.getenv("DB_NAME"),
        charset="utf8mb4",
        cursorclass=pymysql.cursors.DictCursor
    )

    try:
        with conn.cursor() as cursor:

            query = """
                SELECT status, COUNT(*) as total
                FROM emails
                WHERE creator = %s
                AND DATE(created_at) = %s
                GROUP BY status
                ORDER BY total DESC
            """

            cursor.execute(query, (creator, date))

            rows = cursor.fetchall()

            total_count = sum(row["total"] for row in rows)

            print(f"\nCreator: {creator}")
            print(f"Tarih: {date}")
            print(f"Toplam kayıt: {total_count}\n")

            for row in rows:
                print(f'{row["status"]}: {row["total"]}')

    finally:
        conn.close()


get_creator_stats_by_date(
    creator="dio",
    date="2026-05-08"
)