from datetime import date, timedelta

from models.db import db_cursor
from models.schema import _table_exists, column_exists


# ── Admin Users ───────────────────────────────────────────────────────────────

def get_admin_user_by_username(username):
    query = """
        SELECT id, username, password, created_at
        FROM admin_users WHERE username = %s LIMIT 1
    """
    with db_cursor(dictionary=True) as (_, cursor):
        cursor.execute(query, ((username or "").strip(),))
        return cursor.fetchone()


def create_admin_user(username, password):
    query = "INSERT INTO admin_users (username, password) VALUES (%s, %s)"
    with db_cursor() as (_, cursor):
        cursor.execute(query, ((username or "").strip(), password))
        return cursor.lastrowid


# ── Site Assets ───────────────────────────────────────────────────────────────

def get_site_assets():
    """Fetch the latest assets from event_site_assets table."""
    try:
        with db_cursor(dictionary=True) as (_, cursor):
            cursor.execute("SELECT asset_type, image_url FROM event_site_assets")
            rows = cursor.fetchall()
            assets = {}
            for row in rows:
                atype = row["asset_type"]
                if atype == "header_image":
                    assets["header"] = {"image_path": row["image_url"]}
                elif atype == "logo_image":
                    assets["logo"] = {"image_path": row["image_url"]}
                elif atype == "footer_image":
                    assets["footer"] = {"image_path": row["image_url"]}
                elif atype == "hero_bg":
                    assets["hero_bg"] = {"image_path": row["image_url"]}
                elif atype == "cover":
                    assets["cover"] = {"image_path": row["image_url"]}
                elif atype == "watermark":
                    assets["watermark"] = {"image_path": row["image_url"]}
            return assets
    except Exception:
        return {"header": None, "logo": None, "footer": None, "hero_bg": None, "cover": None}


def update_site_asset(asset_type, image_url):
    """Update or insert an asset in event_site_assets table."""
    type_map = {
        "header": "header_image",
        "logo": "logo_image",
        "footer": "footer_image",
        "hero_bg": "hero_bg",
        "cover": "cover",
    }
    db_type = type_map.get(asset_type, asset_type)
    with db_cursor() as (_, cursor):
        cursor.execute("""
            INSERT INTO event_site_assets (asset_type, image_url)
            VALUES (%s, %s)
            ON DUPLICATE KEY UPDATE image_url = VALUES(image_url), updated_at = CURRENT_TIMESTAMP
        """, (db_type, image_url))


def create_admin_asset(asset_type, image_path):
    query = "INSERT INTO admin_assets (type, image_path) VALUES (%s, %s)"
    with db_cursor() as (_, cursor):
        cursor.execute(query, (asset_type, image_path))
        return cursor.lastrowid


def get_latest_admin_asset(asset_type):
    query = """
        SELECT id, type, image_path, uploaded_at FROM admin_assets
        WHERE type = %s ORDER BY uploaded_at DESC, id DESC LIMIT 1
    """
    with db_cursor(dictionary=True) as (_, cursor):
        cursor.execute(query, (asset_type,))
        return cursor.fetchone()


# ── Dashboard ─────────────────────────────────────────────────────────────────

def get_dashboard_counts():
    with db_cursor(dictionary=True) as (_, cursor):
        if _table_exists(cursor, "visitors_registration"):
            cursor.execute("SELECT COUNT(*) AS total_visitors FROM visitors_registration")
            total_visitors = (cursor.fetchone() or {}).get("total_visitors", 0)

            todays_visitors_supported = column_exists(cursor, "visitors_registration", "created_at")
            todays_visitors = 0
            if todays_visitors_supported:
                cursor.execute("""
                    SELECT COUNT(*) AS todays_visitors
                    FROM visitors_registration
                    WHERE DATE(created_at) = CURDATE()
                """)
                todays_visitors = (cursor.fetchone() or {}).get("todays_visitors", 0)
        else:
            total_visitors = 0
            todays_visitors = 0
            todays_visitors_supported = False

        cursor.execute("SELECT COUNT(*) AS total_registered_users FROM users")
        total_registered_users = (cursor.fetchone() or {}).get("total_registered_users", 0)

        total_emails_sent = 0
        cursor.execute("""
            SELECT 1 FROM information_schema.TABLES
            WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'email_deliveries' LIMIT 1
        """)
        if cursor.fetchone() is not None:
            cursor.execute("SELECT COUNT(*) AS total_emails_sent FROM email_deliveries")
            total_emails_sent = (cursor.fetchone() or {}).get("total_emails_sent", 0)

        return {
            "total_visitors": total_visitors,
            "total_registered_users": total_registered_users,
            "todays_visitors": todays_visitors,
            "total_emails_sent": total_emails_sent,
            "todays_visitors_supported": todays_visitors_supported,
        }


def get_dashboard_chart_data():
    with db_cursor(dictionary=True) as (_, cursor):
        if column_exists(cursor, "users", "created_at"):
            cursor.execute("""
                SELECT DATE(created_at) AS label, COUNT(*) AS value
                FROM users
                WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 6 DAY)
                GROUP BY DATE(created_at)
                ORDER BY DATE(created_at)
            """)
            rows = cursor.fetchall()
        else:
            rows = []

    points = {row["label"].strftime("%Y-%m-%d"): row["value"] for row in rows if row.get("label")}
    today = date.today()
    return [
        {
            "label": (today - timedelta(days=offset)).strftime("%Y-%m-%d"),
            "value": points.get((today - timedelta(days=offset)).strftime("%Y-%m-%d"), 0),
        }
        for offset in range(6, -1, -1)
    ]


# ── Image Links ───────────────────────────────────────────────────────────────

def get_image_links(link_type=None):
    conditions = ["is_active = TRUE"]
    params = []
    if link_type:
        conditions.append("link_type = %s")
        params.append(link_type)
    query = f"""
        SELECT id, link_type, link_url, description, is_active, created_at
        FROM image_links WHERE {' AND '.join(conditions)} ORDER BY created_at DESC
    """
    with db_cursor(dictionary=True) as (_, cursor):
        cursor.execute(query, tuple(params))
        return cursor.fetchall()


def update_image_link(link_type, link_url):
    query = """
        INSERT INTO image_links (link_type, link_url, is_active, created_at)
        VALUES (%s, %s, TRUE, NOW())
        ON DUPLICATE KEY UPDATE link_url = VALUES(link_url), updated_at = NOW()
    """
    with db_cursor() as (_, cursor):
        cursor.execute(query, (link_type, link_url))