from fastapi import APIRouter, HTTPException
from database import get_connection
from datetime import datetime, date, time, timedelta


router = APIRouter(
    prefix="/dashboard",
    tags=["Employee Dashboard"]
)


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

def format_time(value):
    if value is None:
        return None

    if isinstance(value, timedelta):
        total_seconds = int(value.total_seconds())

        hours = total_seconds // 3600
        minutes = (total_seconds % 3600) // 60
        seconds = total_seconds % 60

        return f"{hours:02d}:{minutes:02d}:{seconds:02d}"

    if isinstance(value, time):
        return value.strftime("%H:%M:%S")

    return str(value)


def format_date(value):
    if value is None:
        return None

    if isinstance(value, datetime):
        return value.strftime("%Y-%m-%d")

    if isinstance(value, date):
        return value.strftime("%Y-%m-%d")

    return str(value)


def format_display_time(value):
    """
    13:30:00 -> 1:30 PM
    """

    if not value:
        return ""

    try:
        if isinstance(value, timedelta):
            total_seconds = int(value.total_seconds())

            hours = total_seconds // 3600
            minutes = (total_seconds % 3600) // 60

            value = f"{hours:02d}:{minutes:02d}:00"

        value = str(value)

        parsed = datetime.strptime(
            value[:8],
            "%H:%M:%S"
        )

        return parsed.strftime("%I:%M %p").lstrip("0")

    except Exception:
        return str(value)


# =========================================================
# EMPLOYEE DASHBOARD
# =========================================================

@router.get("/employee/{employee_id}")
def get_employee_dashboard(employee_id: str):

    employee_id = str(employee_id).strip()

    if not employee_id:
        raise HTTPException(
            status_code=400,
            detail="Employee ID is required."
        )

    conn = get_connection()

    try:

        cursor = conn.cursor(dictionary=True)

        # =====================================================
        # EMPLOYEE
        # =====================================================

        cursor.execute(
            """
            SELECT
                e.employeeId,
                e.fullName,
                e.email,
                e.department,
                e.designation,
                d.departmentName
            FROM employees e
            LEFT JOIN departments d
                ON e.department = d.departmentCode
            WHERE LOWER(TRIM(e.employeeId))
                = LOWER(TRIM(%s))
            LIMIT 1
            """,
            (employee_id,)
        )

        employee = cursor.fetchone()

        if not employee:

            raise HTTPException(
                status_code=404,
                detail="Employee not found."
            )

        user_name = (
            employee.get("fullName")
            or employee.get("employeeId")
            or employee_id
        )

        department_name = (
            employee.get("departmentName")
            or employee.get("department")
            or ""
        )

        designation = (
            employee.get("designation")
            or ""
        )

        # =====================================================
        # LEADS
        # =====================================================

        cursor.execute(
            """
            SELECT
                id,
                name,
                businessName,
                phone,
                email,
                city,
                state,
                category,
                source,
                status,
                priority,
                assignedTo,
                followUpDate,
                followUpTime,
                fieldEmployeeId,
                fieldEmployeeName,
                visitedDate,
                visitedTime,
                visitResult
            FROM leads
            WHERE
                LOWER(TRIM(assignedTo))
                    = LOWER(TRIM(%s))
                OR
                LOWER(TRIM(fieldEmployeeId))
                    = LOWER(TRIM(%s))
            ORDER BY id DESC
            """,
            (
                employee_id,
                employee_id
            )
        )

        lead_rows = cursor.fetchall()

        leads = []

        for lead in lead_rows:

            follow_date = format_date(
                lead.get("followUpDate")
            )

            follow_time = format_time(
                lead.get("followUpTime")
            )

            display_time = format_display_time(
                follow_time
            )

            leads.append({
                "id": lead.get("id"),
                "name": lead.get("name") or "Unknown Lead",
                "company": (
                    lead.get("businessName")
                    or lead.get("company")
                    or ""
                ),
                "phone": lead.get("phone") or "",
                "email": lead.get("email") or "",
                "city": lead.get("city") or "",
                "state": lead.get("state") or "",
                "category": lead.get("category") or "",
                "status": lead.get("status") or "New",
                "priority": lead.get("priority") or "Medium",
                "assignedTo": lead.get("assignedTo") or "",
                "followUpDate": follow_date,
                "followUpTime": follow_time,
                "time": display_time,
                "fieldEmployeeId": (
                    lead.get("fieldEmployeeId")
                    or ""
                ),
                "fieldEmployeeName": (
                    lead.get("fieldEmployeeName")
                    or ""
                ),
                "visitedDate": format_date(
                    lead.get("visitedDate")
                ),
                "visitedTime": format_time(
                    lead.get("visitedTime")
                ),
                "visitResult": (
                    lead.get("visitResult")
                    or ""
                )
            })

        # =====================================================
        # TASKS
        # =====================================================

        cursor.execute(
            """
            SELECT
                t.id,
                t.title,
                t.description,
                t.department,
                t.designation,
                t.end_date,
                t.end_time,
                t.priority,
                t.status,
                t.progress,
                t.created_at,
                t.updated_at
            FROM tasks t

            INNER JOIN task_assignees ta
                ON ta.task_id = t.id

            WHERE LOWER(TRIM(ta.employee_id))
                = LOWER(TRIM(%s))

            ORDER BY
                CASE
                    WHEN t.status IN (
                        'Completed',
                        'Done',
                        'Cancelled'
                    )
                    THEN 1
                    ELSE 0
                END ASC,
                t.end_date ASC,
                t.id DESC
            """,
            (employee_id,)
        )

        task_rows = cursor.fetchall()

        tasks = []

        for task in task_rows:

            end_date = format_date(
                task.get("end_date")
            )

            end_time = format_time(
                task.get("end_time")
            )

            tasks.append({
                "id": task.get("id"),
                "title": task.get("title") or "Untitled Task",
                "description": (
                    task.get("description")
                    or ""
                ),
                "department": (
                    task.get("department")
                    or ""
                ),
                "designation": (
                    task.get("designation")
                    or ""
                ),
                "endDate": end_date,
                "endTime": end_time,
                "priority": (
                    task.get("priority")
                    or "Medium"
                ),
                "status": (
                    task.get("status")
                    or "Pending"
                ),
                "progress": int(
                    task.get("progress") or 0
                ),
                "createdAt": (
                    task.get("created_at").isoformat()
                    if task.get("created_at")
                    else None
                ),
                "updatedAt": (
                    task.get("updated_at").isoformat()
                    if task.get("updated_at")
                    else None
                ),

                # Frontend display
                "due": (
                    f"{end_date}, "
                    f"{format_display_time(end_time)}"
                    if end_date and end_time
                    else end_date
                    or (
                        format_display_time(end_time)
                        if end_time
                        else "No deadline"
                    )
                )
            })

        # =====================================================
        # PERFORMANCE
        # =====================================================

        cursor.execute(
            """
            SELECT
                COUNT(*) AS total_tasks,
                COALESCE(
                    SUM(points),
                    0
                ) AS total_points,
                COALESCE(
                    ROUND(
                        AVG(rating),
                        2
                    ),
                    0
                ) AS average_rating
            FROM performance_records
            WHERE LOWER(TRIM(employee_id))
                = LOWER(TRIM(%s))
            """,
            (employee_id,)
        )

        performance = cursor.fetchone()

        average_rating = float(
            performance.get("average_rating") or 0
        )

        total_points = int(
            performance.get("total_points") or 0
        )

        performance_tasks = int(
            performance.get("total_tasks") or 0
        )

        # Convert 5-star rating to percentage
        performance_percent = round(
            (average_rating / 5) * 100
        )

        # =====================================================
        # EVENTS
        #
        # Events are created from:
        # 1. Lead follow-up
        # 2. Task deadline
        # =====================================================

        events = {}

        def add_event(
            event_date,
            title,
            event_time="",
            event_type="general"
        ):

            if not event_date:
                return

            if event_date not in events:
                events[event_date] = []

            events[event_date].append({
                "title": title,
                "time": event_time,
                "type": event_type
            })

        # -----------------------------------------------------
        # LEAD FOLLOW-UPS
        # -----------------------------------------------------

        for lead in leads:

            if lead.get("followUpDate"):

                lead_title = (
                    f"Follow-up — "
                    f"{lead.get('name') or 'Lead'}"
                )

                if lead.get("company"):
                    lead_title += (
                        f" — {lead.get('company')}"
                    )

                add_event(
                    lead.get("followUpDate"),
                    lead_title,
                    lead.get("time") or "",
                    "lead"
                )

        # -----------------------------------------------------
        # TASK DEADLINES
        # -----------------------------------------------------

        for task in tasks:

            if task.get("endDate"):

                add_event(
                    task.get("endDate"),
                    f"Task — {task.get('title')}",
                    format_display_time(
                        task.get("endTime")
                    ),
                    "task"
                )

        # =====================================================
        # SORT EVENTS
        # =====================================================

        events = dict(
            sorted(
                events.items(),
                key=lambda item: item[0]
            )
        )

        # =====================================================
        # LEAD ACCESS
        # =====================================================

        department_lower = department_name.lower()
        designation_lower = designation.lower()

        has_lead_access = (
            "sales" in department_lower
            or "business development" in department_lower
            or "sales" in designation_lower
            or len(leads) > 0
        )

        # =====================================================
        # TASK COUNTS
        # =====================================================

        pending_statuses = {
            "pending",
            "in progress",
            "on hold",
            "not completed"
        }

        completed_statuses = {
            "completed",
            "done"
        }

        pending_tasks = sum(
            1
            for task in tasks
            if str(
                task.get("status") or ""
            ).lower()
            in pending_statuses
        )

        completed_tasks = sum(
            1
            for task in tasks
            if str(
                task.get("status") or ""
            ).lower()
            in completed_statuses
        )

        # =====================================================
        # LEAD COUNTS
        # =====================================================

        new_leads = sum(
            1
            for lead in leads
            if str(
                lead.get("status") or ""
            ).lower()
            == "new"
        )

        follow_up_leads = sum(
            1
            for lead in leads
            if str(
                lead.get("status") or ""
            ).lower()
            in {
                "follow-up",
                "follow up"
            }
        )

        converted_leads = sum(
            1
            for lead in leads
            if str(
                lead.get("status") or ""
            ).lower()
            == "converted"
        )

        # =====================================================
        # FINAL RESPONSE
        # =====================================================

        return {
            "success": True,

            "employee": {
                "employeeId": employee.get(
                    "employeeId"
                ),
                "name": user_name,
                "email": employee.get(
                    "email"
                ),
                "department": (
                    employee.get("department")
                    or ""
                ),
                "departmentName": department_name,
                "designation": designation
            },

            "permissions": {
                "hasLeadAccess": has_lead_access
            },

            "stats": {
                "myLeads": len(leads),
                "newLeads": new_leads,
                "followUpLeads": follow_up_leads,
                "convertedLeads": converted_leads,

                "tasksTotal": len(tasks),
                "tasksPending": pending_tasks,
                "tasksCompleted": completed_tasks,

                "performance": performance_percent,
                "averageRating": average_rating,
                "performanceTasks": performance_tasks,
                "totalPoints": total_points
            },

            "leads": leads,

            "tasks": tasks,

            "events": events
        }

    except HTTPException:
        raise

    except Exception as e:

        print(
            "EMPLOYEE DASHBOARD ERROR:",
            str(e)
        )

        raise HTTPException(
            status_code=500,
            detail=str(e)
        )

    finally:

        try:
            cursor.close()
        except Exception:
            pass

        try:
            conn.close()
        except Exception:
            pass