import folium
import pandas as pd
from sqlalchemy import text
from database import engine
from datetime import datetime

# =====================================================
# COLOR PALETTE FOR DIFFERENT DATES
# =====================================================

COLOR_PALETTE = [
    '#FF6B6B',  # Red
    '#4ECDC4',  # Teal
    '#45B7D1',  # Blue
    '#FFA07A',  # Light Salmon
    '#98D8C8',  # Mint
    '#F7DC6F',  # Yellow
    '#BB8FCE',  # Purple
    '#85C1E2',  # Sky Blue
    '#F8B88B',  # Peach
    '#A9CCE3'   # Light Blue
]

# =====================================================
# LOCATION COORDINATES - EXACT LOCATIONS
# =====================================================

LOCATION_COORDINATES = {
    "Adyar_GandhiNagar": (13.0125, 80.2520),
    "Adyar_IndiraNagar": (12.9967, 80.2531),
    "Adyar_KasturibaiNagar": (13.0062, 80.2535),
    "AnnaNagar_2ndAvenue": (13.0851, 80.2198),
    "AnnaNagar_Roundtana": (13.0843, 80.2125),
    "Guindy_GSTRoad": (13.0076, 80.2132),
    "Kotturpuram_AnnaUniversity": (13.0131, 80.2364),
    "Koyambedu_Market": (13.0691, 80.1915),
    "Mylapore_Temple": (13.0334, 80.2694),
    "Nungambakkam_HighRoad": (13.0617, 80.2458),
    "Thiruvanmiyur_Junction": (12.9877, 80.2573),
    "Tnagar_PondyBazaar": (13.0410, 80.2337),
    "Tnagar_UsmanRoad": (13.0354, 80.2323),
    "Velachery_MainRoad": (12.9868, 80.2221),
    "default": (13.0827, 80.2707),
}

# =====================================================
# HELPERS
# =====================================================

def normalize_plate(plate):
    return str(plate).replace(" ", "").replace("-", "").upper().strip()


def get_coordinates(location):

    if not location:
        return LOCATION_COORDINATES["default"]

    location = str(location).strip()
    
    # Exact match first
    if location in LOCATION_COORDINATES:
        return LOCATION_COORDINATES[location]
    
    # Partial match as fallback
    location_lower = location.lower()
    for key in LOCATION_COORDINATES:
        if key.lower() in location_lower or location_lower in key.lower():
            return LOCATION_COORDINATES[key]

    return LOCATION_COORDINATES["default"]


def map_to_html(m):
    """Convert folium map to HTML"""
    return m._repr_html_()


# =====================================================
# DEFAULT MAP
# =====================================================

def default_vehicle_map():

    m = folium.Map(
        location=[13.0827, 80.2707],
        zoom_start=11,
        tiles="OpenStreetMap"
    )

    folium.Marker(
        [13.0827, 80.2707],
        tooltip="ActionSync",
        popup="Vehicle Intelligence",
        icon=folium.Icon(color="blue", icon="car")
    ).add_to(m)

    return map_to_html(m)


# =====================================================
# SEARCH VEHICLE ROUTE
# =====================================================

def search_vehicle_route(
    plate,
    date_from=None,
    date_to=None
):

    try:

        if not plate:

            return (
                default_vehicle_map(),
                pd.DataFrame(),
                {"error": "Enter plate number"}
            )

        clean_plate = normalize_plate(plate)

        # Convert date objects to strings if needed
        if date_from:
            if hasattr(date_from, 'strftime'):  # datetime.date or datetime.datetime
                date_from = date_from.strftime('%Y-%m-%d')
            elif isinstance(date_from, str) and len(date_from) > 10:  # datetime string
                date_from = date_from[:10]

        if date_to:
            if hasattr(date_to, 'strftime'):  # datetime.date or datetime.datetime
                date_to = date_to.strftime('%Y-%m-%d')
            elif isinstance(date_to, str) and len(date_to) > 10:  # datetime string
                date_to = date_to[:10]

        query = """
        SELECT
            plate,
            state,
            vehicle_type,
            vehicle_conf,
            location,
            date,
            timestamp
        FROM vehicle_logs
        WHERE REPLACE(REPLACE(UPPER(plate), ' ', ''), '-', '') = :plate
        """

        params = {
            "plate": clean_plate
        }

        if date_from:
            query += " AND date >= :date_from"
            params["date_from"] = date_from

        if date_to:
            query += " AND date <= :date_to"
            params["date_to"] = date_to

        query += """
        ORDER BY date ASC,
                 timestamp ASC
        """

        with engine.connect() as conn:

            result = conn.execute(
                text(query),
                params
            )

            rows = result.fetchall()

            df = pd.DataFrame(
                rows,
                columns=result.keys()
            )

        # =====================================================
        # NO DATA
        # =====================================================

        if df.empty:

            return (
                default_vehicle_map(),
                pd.DataFrame(),
                {
                    "error": f"No detections found for {clean_plate}"
                }
            )

        # =====================================================
        # ADD COORDINATES
        # =====================================================

        df["latitude"] = df["location"].apply(
            lambda x: get_coordinates(x)[0]
        )

        df["longitude"] = df["location"].apply(
            lambda x: get_coordinates(x)[1]
        )

        # =====================================================
        # GET UNIQUE DATES AND ASSIGN COLORS
        # =====================================================

        unique_dates = sorted(df["date"].unique())
        date_colors = {date: COLOR_PALETTE[i % len(COLOR_PALETTE)] for i, date in enumerate(unique_dates)}

        # =====================================================
        # CREATE MAP
        # =====================================================

        center_lat = df["latitude"].mean()
        center_lon = df["longitude"].mean()

        m = folium.Map(
            location=[center_lat, center_lon],
            zoom_start=12,
            tiles="OpenStreetMap"
        )

        # =====================================================
        # DETERMINE VIEW MODE
        # =====================================================

        has_date_filter = date_from is not None or date_to is not None

        if not has_date_filter:
            # =====================================================
            # MODE 1: NO DATE FILTER - SHOW SINGLE MARKER PER LOCATION
            # =====================================================

            location_groups = df.groupby("location")

            for location, group in location_groups:
                
                lat = group["latitude"].iloc[0]
                lon = group["longitude"].iloc[0]

                location_dates = sorted(group["date"].unique())
                total_detections = len(group)

                # Build popup showing all dates visited
                popup_html = f"""
                <div style="width:320px; max-height:400px; overflow-y:auto; font-family: Arial, sans-serif;">
                <h4 style="margin-bottom: 10px; color: #333;">📍 {location}</h4>
                <p style="margin: 5px 0;"><b>Plate:</b> {clean_plate}</p>
                <p style="margin: 5px 0;"><b>Vehicle:</b> {str(group.iloc[0]['vehicle_type'])}</p>
                <p style="margin: 5px 0;"><b>State:</b> {str(group.iloc[0]['state'])}</p>
                <hr style="margin: 10px 0;">
                <p style="margin: 5px 0;"><b>Total Visits:</b> {total_detections}</p>
                <b style="color: #333;">📅 Dates Visited:</b><br>
                """

                for date in location_dates:
                    date_detections = len(group[group["date"] == date])
                    
                    popup_html += f"""
                    <div style="margin: 8px 0; padding: 6px; background-color: #f5f5f5; border-left: 3px solid {date_colors[date]};">
                        <b>{date}</b> - {date_detections} detection(s)
                    </div>
                    """

                popup_html += "</div>"

                folium.Marker(
                    [lat, lon],
                    popup=folium.Popup(popup_html, max_width=350),
                    tooltip=f"{location} ({len(location_dates)} dates, {total_detections} detections)",
                    icon=folium.Icon(
                        color="blue",
                        icon="map-marker",
                        prefix="fa"
                    )
                ).add_to(m)

        else:
            # =====================================================
            # MODE 2: DATE FILTER - SHOW COLORED MARKERS FOR EACH LOCATION
            # =====================================================

            location_groups = df.groupby("location")

            for location, group in location_groups:
                
                lat = group["latitude"].iloc[0]
                lon = group["longitude"].iloc[0]
                
                location_dates = sorted(group["date"].unique())
                primary_color = date_colors[location_dates[0]]  # Color of first date
                total_detections = len(group)

                # Build popup showing date summary
                popup_html = f"""
                <div style="width:340px; max-height:450px; overflow-y:auto; font-family: Arial, sans-serif;">
                <h4 style="margin-bottom: 10px; color: #333;">📍 {location}</h4>
                <p style="margin: 5px 0;"><b>Plate:</b> {clean_plate}</p>
                <p style="margin: 5px 0;"><b>Vehicle:</b> {str(group.iloc[0]['vehicle_type'])}</p>
                <p style="margin: 5px 0;"><b>State:</b> {str(group.iloc[0]['state'])}</p>
                <hr style="margin: 10px 0;">
                <b style="color: #333;">📅 Detections by Date:</b><br>
                """

                # Show date summary
                for date in location_dates:
                    date_group = group[group["date"] == date]
                    date_count = len(date_group)
                    color = date_colors[date]
                    
                    popup_html += f"""
                    <div style="margin: 8px 0; padding: 6px; background-color: #f5f5f5; border-left: 3px solid {color};">
                        <b>{date}</b> - {date_count} detection(s)
                    </div>
                    """

                popup_html += "<hr style='margin: 10px 0;'><b>Timestamps:</b><br>"

                # Show all timestamps
                for idx, row in group.iterrows():
                    popup_html += f"""
                    <div style="margin: 6px 0; padding: 4px; background-color: #fafafa; font-size: 12px;">
                        <b>{row['timestamp']}</b> (Conf: {round(row['vehicle_conf'], 3)})<br>
                    </div>
                    """

                popup_html += "</div>"

                folium.Marker(
                    [lat, lon],
                    popup=folium.Popup(popup_html, max_width=360),
                    tooltip=f"{location} - {len(location_dates)} dates, {total_detections} detections",
                    icon=folium.Icon(
                        color=primary_color,
                        icon="map-marker",
                        prefix="fa"
                    )
                ).add_to(m)

        info = {
            "plate": clean_plate,
            "total_detections": int(len(df)),
            "unique_locations": int(df["location"].nunique()),
            "unique_dates": int(len(unique_dates)),
            "date_range": f"{unique_dates[0]} to {unique_dates[-1]}" if len(unique_dates) > 1 else unique_dates[0],
            "vehicle_type": str(df.iloc[0]["vehicle_type"]),
            "state": str(df.iloc[0]["state"]),
            "view_mode": "Single location markers" if not has_date_filter else "Color-coded by date"
        }

        return (
            map_to_html(m),
            df,
            info
        )

    except Exception as e:

        import traceback
        traceback.print_exc()

        return (
            default_vehicle_map(),
            pd.DataFrame(),
            {
                "error": str(e)
            }
        )