from models.db import db_cursor


def is_delivery_logged(user_id, event_image_id):
    """Check if an email delivery has already been logged."""
    query = """
        SELECT id FROM email_deliveries
        WHERE user_id = %s AND event_image_id = %s
        LIMIT 1
    """
    with db_cursor(dictionary=True) as (_, cursor):
        cursor.execute(query, (user_id, event_image_id))
        return cursor.fetchone() is not None


def log_email_delivery(user_id, event_image_id):
    """Log a single email delivery."""
    query = """
        INSERT IGNORE INTO email_deliveries (user_id, event_image_id)
        VALUES (%s, %s)
    """
    with db_cursor() as (_, cursor):
        cursor.execute(query, (user_id, event_image_id))


def log_email_deliveries(user_id, event_image_ids):
    """Log multiple email deliveries for a user."""
    with db_cursor() as (_, cursor):
        for event_image_id in event_image_ids:
            cursor.execute(
                "INSERT IGNORE INTO email_deliveries (user_id, event_image_id) VALUES (%s, %s)",
                (user_id, event_image_id),
            )


def get_email_delivery_stats():
    """Return total, sent and remaining email delivery counts with user details."""
    with db_cursor(dictionary=True) as (_, cursor):
        # Total unique users who have at least one match
        cursor.execute("""
            SELECT COUNT(DISTINCT user_id) AS total FROM matches
        """)
        total_users = (cursor.fetchone() or {}).get("total", 0)

        # Sent: users who have received at least one delivery
        cursor.execute("""
            SELECT
                ed.user_id,
                u.name,
                u.email,
                COUNT(ed.id) AS images_sent,
                MAX(ed.sent_at) AS last_sent_at
            FROM email_deliveries ed
            JOIN users u ON u.id = ed.user_id
            GROUP BY ed.user_id, u.name, u.email
            ORDER BY last_sent_at DESC
        """)
        sent_rows = cursor.fetchall()

        # Remaining: users with matches but no delivery yet
        cursor.execute("""
            SELECT
                u.id AS user_id,
                u.name,
                u.email,
                COUNT(m.id) AS matched_images
            FROM matches m
            JOIN users u ON u.id = m.user_id
            WHERE u.id NOT IN (
                SELECT DISTINCT user_id FROM email_deliveries
            )
            GROUP BY u.id, u.name, u.email
            ORDER BY u.name ASC
        """)
        remaining_rows = cursor.fetchall()

        # Total emails count (unique deliveries)
        cursor.execute("SELECT COUNT(*) AS total FROM email_deliveries")
        total_emails = (cursor.fetchone() or {}).get("total", 0)

        return {
            "total_users_with_matches": total_users,
            "total_emails_sent": total_emails,
            "sent": sent_rows,
            "remaining": remaining_rows,
        }


def get_all_email_deliveries():
    """Fetch all email deliveries with user info."""
    query = """
        SELECT
            ed.id,
            ed.user_id,
            ed.event_image_id,
            ed.sent_at,
            u.name,
            u.email
        FROM email_deliveries ed
        JOIN users u ON u.id = ed.user_id
        ORDER BY ed.sent_at DESC
    """
    with db_cursor(dictionary=True) as (_, cursor):
        cursor.execute(query)
        return cursor.fetchall()


def get_daywise_email_stats(start_date, end_date):
    """Return per-day email delivery stats between event start and end dates."""
    with db_cursor(dictionary=True) as (_, cursor):
        # Get all days between start_date and end_date
        cursor.execute("""
            SELECT
                DATE(ed.sent_at) AS delivery_date,
                COUNT(ed.id) AS emails_sent,
                COUNT(DISTINCT ed.user_id) AS users_reached
            FROM email_deliveries ed
            WHERE DATE(ed.sent_at) BETWEEN %s AND %s
            GROUP BY DATE(ed.sent_at)
            ORDER BY DATE(ed.sent_at) ASC
        """, (start_date, end_date))
        sent_by_day = {str(r["delivery_date"]): r for r in cursor.fetchall()}

        # Total matched users
        cursor.execute("SELECT COUNT(DISTINCT user_id) AS total FROM matches")
        total_users = (cursor.fetchone() or {}).get("total", 0)

        return {
            "sent_by_day": sent_by_day,
            "total_matched_users": total_users,
        }