from fastapi import APIRouter, HTTPException, UploadFile, File
from pydantic import BaseModel
from typing import Optional, List
from datetime import datetime, date, time, timedelta
from decimal import Decimal
import mysql.connector
import csv
import io
from crm_notifications import create_crm_notification


router = APIRouter(
    prefix="/leads",
    tags=["Leads"]
)


# =========================================================
# MYSQL DATABASE
# =========================================================

def get_db():

    return mysql.connector.connect(
        host="localhost",
        user="kineticcosmos_kanhaherbs_25",
        password="Karina@2002",
        database="kineticcosmos_crm"
    )


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

def normalize_phone(value):

    if not value:
        return ""

    return "".join(
        c for c in str(value)
        if c.isdigit() or c == "+"
    )


def format_time_value(value):
    """
    MySQL TIME sometimes comes to Python as timedelta.
    Example:
        10:00:00 -> 36000 seconds

    Convert it back to:
        10:00:00
    """

    if value is None:
        return None

    # MySQL TIME -> timedelta
    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}:"
            f"{minutes:02d}:"
            f"{seconds:02d}"
        )

    # Python time object
    if isinstance(value, time):

        return value.strftime(
            "%H:%M:%S"
        )

    return str(value)


def format_date_value(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 serialize_lead(lead):
    """
    Convert DB field names/types into the
    camelCase format expected by frontend.
    """

    if not lead:
        return lead

    # =====================================================
    # FOLLOW-UP TIME
    # =====================================================

    if "followUpTime" in lead:

        lead["followUpTime"] = (
            format_time_value(
                lead.get("followUpTime")
            )
        )

    # =====================================================
    # VISITED DATE/TIME
    # =====================================================

    if "visitedTime" in lead:

        lead["visitedTime"] = (
            format_time_value(
                lead.get("visitedTime")
            )
        )

    if "visitedDate" in lead:

        lead["visitedDate"] = (
            format_date_value(
                lead.get("visitedDate")
            )
        )

    # =====================================================
    # BOOKING
    #
    # DB:
    # booking_date
    # booking_time
    # token_amount
    # booking_remark
    #
    # FRONTEND:
    # bookingDate
    # bookingTime
    # tokenAmount
    # bookingRemark
    # =====================================================

    if "booking_date" in lead:

        lead["bookingDate"] = (
            format_date_value(
                lead.get("booking_date")
            )
        )

    elif "bookingDate" not in lead:

        lead["bookingDate"] = None

    if "booking_time" in lead:

        lead["bookingTime"] = (
            format_time_value(
                lead.get("booking_time")
            )
        )

    elif "bookingTime" not in lead:

        lead["bookingTime"] = None

    if "token_amount" in lead:

        token = lead.get(
            "token_amount"
        )

        if isinstance(token, Decimal):

            lead["tokenAmount"] = float(
                token
            )

        elif token is None:

            lead["tokenAmount"] = None

        else:

            try:

                lead["tokenAmount"] = float(
                    token
                )

            except Exception:

                lead["tokenAmount"] = token

    elif "tokenAmount" not in lead:

        lead["tokenAmount"] = None

    if "booking_remark" in lead:

        lead["bookingRemark"] = (
            lead.get("booking_remark")
            or ""
        )

    elif "bookingRemark" not in lead:

        lead["bookingRemark"] = ""

    return lead


def serialize_leads(rows):

    return [
        serialize_lead(row)
        for row in rows
    ]


def row_dict(cursor, row):

    columns = [
        c[0]
        for c in cursor.description
    ]

    return dict(
        zip(columns, row)
    )


# =========================================================
# INIT TABLE
# =========================================================

def init_db():

    conn = get_db()
    cursor = conn.cursor()

    try:

        # =================================================
        # CREATE LEADS TABLE
        # =================================================

        cursor.execute("""
            CREATE TABLE IF NOT EXISTS leads (

                id INT AUTO_INCREMENT PRIMARY KEY,

                name VARCHAR(255) NOT NULL,

                businessName VARCHAR(255) DEFAULT '',

                phone VARCHAR(50) UNIQUE NOT NULL,

                alternatePhone VARCHAR(50) DEFAULT '',

                email VARCHAR(255) DEFAULT '',

                city VARCHAR(100) DEFAULT '',

                state VARCHAR(100) DEFAULT '',

                category VARCHAR(100) DEFAULT '',

                source VARCHAR(100) DEFAULT 'Manual Entry',

                status VARCHAR(50) DEFAULT 'New',

                priority VARCHAR(50) DEFAULT 'Medium',

                assignedTo VARCHAR(100) DEFAULT '',

                followUpDate VARCHAR(50) DEFAULT '',

                followUpTime VARCHAR(50) DEFAULT '',

                notes TEXT,

                createdAt VARCHAR(100),

                updatedAt VARCHAR(100)

            )
        """)

        # =================================================
        # VISITED / FIELD EMPLOYEE COLUMNS
        # =================================================

        visited_columns = [

            (
                "visitedDate",
                "VARCHAR(50) DEFAULT ''"
            ),

            (
                "visitedTime",
                "VARCHAR(50) DEFAULT ''"
            ),

            (
                "visitResult",
                "VARCHAR(50) DEFAULT 'Pending'"
            ),

            (
                "visitedByEmployeeId",
                "VARCHAR(100) DEFAULT ''"
            ),

            (
                "visitedByEmployeeName",
                "VARCHAR(255) DEFAULT ''"
            ),

            (
                "fieldEmployeeId",
                "VARCHAR(100) DEFAULT ''"
            ),

            (
                "fieldEmployeeName",
                "VARCHAR(255) DEFAULT ''"
            ),

        ]

        for column_name, column_definition in visited_columns:

            cursor.execute(
                """
                SELECT COUNT(*)
                FROM INFORMATION_SCHEMA.COLUMNS
                WHERE TABLE_SCHEMA = DATABASE()
                AND TABLE_NAME = 'leads'
                AND COLUMN_NAME = %s
                """,
                (column_name,)
            )

            exists = cursor.fetchone()[0]

            if exists == 0:

                cursor.execute(
                    f"""
                    ALTER TABLE leads
                    ADD COLUMN {column_name}
                    {column_definition}
                    """
                )

                print(
                    f"ADDED LEADS COLUMN: {column_name}"
                )

        conn.commit()

    except Exception as e:

        conn.rollback()

        print(
            "INIT LEADS DB ERROR:",
            e
        )

        raise

    finally:

        cursor.close()
        conn.close()


init_db()


# =========================================================
# MODELS
# =========================================================

class LeadCreate(BaseModel):

    name: str

    businessName: str = ""

    phone: str

    alternatePhone: str = ""

    email: str = ""

    city: str = ""

    state: str = ""

    category: str = ""

    source: str = "Manual Entry"

    status: str = "New"

    priority: str = "Medium"

    assignedTo: str = ""

    followUpDate: str = ""

    followUpTime: str = ""

    notes: str = ""


class LeadUpdate(BaseModel):

    name: Optional[str] = None

    businessName: Optional[str] = None

    phone: Optional[str] = None

    alternatePhone: Optional[str] = None

    email: Optional[str] = None

    city: Optional[str] = None

    state: Optional[str] = None

    category: Optional[str] = None

    source: Optional[str] = None

    status: Optional[str] = None

    priority: Optional[str] = None

    assignedTo: Optional[str] = None

    followUpDate: Optional[str] = None

    followUpTime: Optional[str] = None

    notes: Optional[str] = None

    # =====================================================
    # VISIT FIELDS
    # =====================================================

    visitedDate: Optional[str] = None

    visitedTime: Optional[str] = None

    visitResult: Optional[str] = None

    visitedByEmployeeId: Optional[str] = None

    visitedByEmployeeName: Optional[str] = None

    fieldEmployeeId: Optional[str] = None

    fieldEmployeeName: Optional[str] = None

    # =====================================================
    # BOOKING FIELDS
    # =====================================================

    bookingDate: Optional[str] = None

    bookingTime: Optional[str] = None

    tokenAmount: Optional[float] = None

    bookingRemark: Optional[str] = None


class BulkLeadItem(BaseModel):

    name: str = ""

    businessName: str = ""

    phone: str = ""

    alternatePhone: str = ""

    email: str = ""

    city: str = ""

    state: str = ""

    category: str = ""

    source: str = "Excel Import"

    status: str = "New"

    priority: str = "Medium"

    assignedTo: str = ""

    followUpDate: str = ""

    followUpTime: str = ""

    notes: str = ""


class BulkLeadImport(BaseModel):

    leads: List[BulkLeadItem] = []

    assignMode: str = "single"

    assignedTo: str = ""

    selectedEmployees: List[str] = []

    defaultPriority: str = "Medium"

    defaultStatus: str = "New"

    defaultSource: str = "Excel Import"

    preserveExcelPriority: bool = True

    preserveExcelStatus: bool = True

    preserveExcelSource: bool = True

    preserveExcelAssignee: bool = False


# =========================================================
# SALES EMPLOYEES
# =========================================================

@router.get("/employees")
def get_sales_employees():

    conn = get_db()

    cursor = conn.cursor(
        dictionary=True
    )

    try:

        cursor.execute("""
            SELECT

                e.employeeId,

                e.fullname AS name,

                e.department,

                e.designation

            FROM employees e

            JOIN departments d

                ON e.department = d.departmentCode

            WHERE LOWER(d.departmentName)
                LIKE '%sales%'

            ORDER BY e.fullname ASC
        """)

        rows = cursor.fetchall()

        return rows

    except Exception as e:

        print(
            "EMPLOYEE ERROR:",
            e
        )

        return []

    finally:

        cursor.close()
        conn.close()


# =========================================================
# INTERNAL AUTO ASSIGN
# =========================================================

def get_sales_employees_from_db(conn):

    cursor = conn.cursor(
        dictionary=True
    )

    cursor.execute("""
        SELECT

            e.employeeId,

            e.fullname AS name,

            e.department,

            e.designation

        FROM employees e

        JOIN departments d

            ON e.department = d.departmentCode

        WHERE LOWER(d.departmentName)
            LIKE '%sales%'
    """)

    data = cursor.fetchall()

    cursor.close()

    return data


# =========================================================
# GET ALL LEADS
# =========================================================

@router.get("")
def get_leads(
    search: str = "",
    status: str = "All",
    priority: str = "All",
    assignedTo: str = "All"
):

    conn = get_db()

    cursor = conn.cursor(
        dictionary=True
    )

    try:

        query = """

            SELECT *

            FROM leads

            WHERE 1=1

        """

        params = []

        if search:

            query += """

                AND (

                    LOWER(name) LIKE %s

                    OR phone LIKE %s

                    OR LOWER(email) LIKE %s

                    OR LOWER(city) LIKE %s

                )

            """

            s = f"%{search.lower()}%"

            params += [
                s,
                s,
                s,
                s
            ]

        if status != "All":

            query += """
                AND status = %s
            """

            params.append(status)

        if priority != "All":

            query += """
                AND priority = %s
            """

            params.append(priority)

        if assignedTo != "All":

            query += """
                AND assignedTo = %s
            """

            params.append(assignedTo)

        query += """
            ORDER BY id DESC
        """

        cursor.execute(
            query,
            params
        )

        data = cursor.fetchall()

        return serialize_leads(data)

    except Exception as e:

        print(
            "GET LEADS ERROR:",
            e
        )

        raise HTTPException(
            status_code=500,
            detail="Failed to fetch leads"
        )

    finally:

        cursor.close()
        conn.close()


# =========================================================
# STATS
# =========================================================

@router.get("/stats/summary")
def get_stats():

    conn = get_db()

    cursor = conn.cursor(
        dictionary=True
    )

    try:

        cursor.execute(
            "SELECT COUNT(*) total FROM leads"
        )

        total = cursor.fetchone()["total"]

        cursor.execute(
            """
            SELECT COUNT(*) total
            FROM leads
            WHERE status='New'
            """
        )

        new = cursor.fetchone()["total"]

        cursor.execute(
            """
            SELECT COUNT(*) total
            FROM leads
            WHERE status='Follow-up'
            """
        )

        follow = cursor.fetchone()["total"]

        cursor.execute(
            """
            SELECT COUNT(*) total
            FROM leads
            WHERE priority='High'
            """
        )

        high = cursor.fetchone()["total"]

        cursor.execute(
            """
            SELECT COUNT(*) total
            FROM leads
            WHERE status='Converted'
            """
        )

        converted = cursor.fetchone()["total"]

        return {

            "total": total,

            "newLeads": new,

            "followUps": follow,

            "highPriority": high,

            "converted": converted

        }

    finally:

        cursor.close()
        conn.close()


# =========================================================
# GET EMPLOYEE LEADS
# =========================================================

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

    conn = get_db()

    cursor = conn.cursor(
        dictionary=True
    )

    try:

        cursor.execute(
            """
            SELECT *
            FROM leads
            WHERE

                LOWER(TRIM(assignedTo))
                    = LOWER(TRIM(%s))

                OR

                LOWER(TRIM(fieldEmployeeId))
                    = LOWER(TRIM(%s))

            ORDER BY id DESC
            """,
            (
                employee_id,
                employee_id
            )
        )

        rows = cursor.fetchall()

        print(
            "================================"
        )

        print(
            "EMPLOYEE ID:",
            employee_id
        )

        print(
            "LEADS FOUND:",
            len(rows)
        )

        print(
            "================================"
        )

        return serialize_leads(rows)

    except Exception as e:

        print(
            "EMPLOYEE LEADS ERROR:",
            e
        )

        raise HTTPException(
            status_code=500,
            detail="Failed to fetch employee leads"
        )

    finally:

        cursor.close()
        conn.close()


# =========================================================
# GET VISITED LEADS
# =========================================================

@router.get("/visited")
def get_visited_leads(
    search: str = ""
):

    conn = get_db()

    cursor = conn.cursor(
        dictionary=True
    )

    try:

        query = """

            SELECT

                id,

                name,

                businessName,

                phone,

                alternatePhone,

                email,

                city,

                state,

                category,

                source,

                status,

                priority,

                assignedTo,

                visitedDate,

                visitedTime,

                visitResult,

                visitedByEmployeeId,

                visitedByEmployeeName,

                fieldEmployeeId,

                fieldEmployeeName,

                followUpDate,

                followUpTime,

                notes,

                createdAt,

                updatedAt,

                booking_date,

                booking_time,

                token_amount,

                booking_remark

            FROM leads

            WHERE

                visitedDate IS NOT NULL

                AND TRIM(
                    CAST(visitedDate AS CHAR)
                ) != ''

                AND CAST(
                    visitedDate AS CHAR
                ) != '0000-00-00'

        """

        params = []

        if search and search.strip():

            query += """

                AND (

                    LOWER(
                        COALESCE(name, '')
                    ) LIKE %s

                    OR LOWER(
                        COALESCE(phone, '')
                    ) LIKE %s

                    OR LOWER(
                        COALESCE(businessName, '')
                    ) LIKE %s

                    OR LOWER(
                        COALESCE(city, '')
                    ) LIKE %s

                    OR LOWER(
                        COALESCE(assignedTo, '')
                    ) LIKE %s

                    OR LOWER(
                        COALESCE(fieldEmployeeName, '')
                    ) LIKE %s

                    OR LOWER(
                        COALESCE(status, '')
                    ) LIKE %s

                    OR LOWER(
                        COALESCE(visitResult, '')
                    ) LIKE %s

                )

            """

            s = f"%{search.strip().lower()}%"

            params.extend([
                s,
                s,
                s,
                s,
                s,
                s,
                s,
                s
            ])

        query += """
            ORDER BY id DESC
        """

        cursor.execute(
            query,
            params
        )

        rows = cursor.fetchall()

        return serialize_leads(rows)

    except Exception as e:

        print(
            "VISITED LEADS ERROR:",
            e
        )

        raise HTTPException(
            status_code=500,
            detail="Failed to fetch visited leads"
        )

    finally:

        cursor.close()
        conn.close()


# =========================================================
# UPDATE LEAD
# =========================================================

@router.put("/{lead_id}")
def update_lead(
    lead_id: int,
    lead: LeadUpdate
):

    conn = get_db()

    cursor = conn.cursor(
        dictionary=True
    )

    try:

        # =================================================
        # GET OLD LEAD
        # =================================================

        cursor.execute(
            """
            SELECT *
            FROM leads
            WHERE id = %s
            """,
            (lead_id,)
        )

        old = cursor.fetchone()

        if not old:

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

        # =================================================
        # UPDATE DATA
        # =================================================

        update = lead.model_dump(
            exclude_unset=True
        )

        data = old.copy()

        data.update(update)

        # =================================================
        # PHONE
        # =================================================

        phone = normalize_phone(
            data.get("phone")
        )

        if not phone:

            raise HTTPException(
                status_code=400,
                detail="Phone required"
            )

        # =================================================
        # CURRENT TIME
        #
        # ONLY updatedAt.
        #
        # NOT USED FOR VISIT DATE/TIME.
        # =================================================

        now = datetime.now()

        # =================================================
        # OLD VISIT DATA
        # =================================================

        old_visited_date = old.get(
            "visitedDate"
        )

        old_visited_time = old.get(
            "visitedTime"
        )

        # =================================================
        # CHECK EXISTING VISIT
        # =================================================

        has_visited = (

            old_visited_date

            and str(
                old_visited_date
            ).strip()
            not in [
                "",
                "0000-00-00",
                "None"
            ]

            and old_visited_time

            and str(
                old_visited_time
            ).strip()
            not in [
                "",
                "00:00:00",
                "None"
            ]

        )

        # =================================================
        # VISITED DATE / TIME
        #
        # NEVER AUTO-GENERATE CURRENT DATE/TIME.
        # =================================================

        if data.get("status") == "Visited":

            visited_date = (
                data.get("visitedDate")
                or old_visited_date
                or ""
            )

            visited_time = (
                data.get("visitedTime")
                or old_visited_time
                or ""
            )

            if not str(
                visited_date
            ).strip():

                raise HTTPException(
                    status_code=400,
                    detail="Visit date is required"
                )

            if not str(
                visited_time
            ).strip():

                raise HTTPException(
                    status_code=400,
                    detail="Visit time is required"
                )

        else:

            # Preserve existing visit data
            visited_date = (
                old_visited_date
                or ""
            )

            visited_time = (
                old_visited_time
                or ""
            )

        # =================================================
        # VISIT RESULT
        # =================================================

        visit_result = (
            old.get("visitResult")
            or "Pending"
        )

        requested_visit_result = (
            data.get("visitResult")
        )

        # =================================================
        # STATUS BASED VISIT RESULT
        # =================================================

        if data.get("status") == "Converted":

            if has_visited:

                visit_result = "Converted"

        elif data.get("status") == "Not Interested":

            if has_visited:

                visit_result = "Not Interested"

        # =================================================
        # EXPLICIT VISIT RESULT
        # =================================================

        if requested_visit_result:

            if requested_visit_result != "Pending":

                visit_result = (
                    requested_visit_result
                )

            elif not has_visited:

                visit_result = "Pending"

        # =================================================
        # VISITED BY EMPLOYEE
        # =================================================

        visited_by_employee_id = (
            data.get("visitedByEmployeeId")
            or old.get("visitedByEmployeeId")
            or None
        )

        visited_by_employee_name = (
            data.get("visitedByEmployeeName")
            or old.get("visitedByEmployeeName")
            or None
        )

        # =================================================
        # FIELD EMPLOYEE
        # =================================================

        field_employee_id = (
            data.get("fieldEmployeeId")
            or old.get("fieldEmployeeId")
            or None
        )

        field_employee_name = (
            data.get("fieldEmployeeName")
            or old.get("fieldEmployeeName")
            or None
        )

        # =================================================
        # BOOKING DATA
        #
        # DB COLUMNS:
        # booking_date
        # booking_time
        # token_amount
        # booking_remark
        #
        # FRONTEND:
        # bookingDate
        # bookingTime
        # tokenAmount
        # bookingRemark
        # =================================================

        old_booking_date = old.get(
            "booking_date"
        )

        old_booking_time = old.get(
            "booking_time"
        )

        old_token_amount = old.get(
            "token_amount"
        )

        old_booking_remark = old.get(
            "booking_remark"
        )

        # =================================================
        # IMPORTANT:
        #
        # If frontend sends booking values, use them.
        # Otherwise preserve old booking values.
        #
        # This prevents booking data from being erased
        # when lead status is changed later.
        # =================================================

        incoming_booking_date = (
            update.get("bookingDate")
        )

        incoming_booking_time = (
            update.get("bookingTime")
        )

        incoming_token_amount = (
            update.get("tokenAmount")
        )

        incoming_booking_remark = (
            update.get("bookingRemark")
        )

        # =================================================
        # BOOKING DATE
        # =================================================

        if (
            incoming_booking_date
            not in [None, ""]
        ):

            booking_date = (
                incoming_booking_date
            )

        else:

            booking_date = (
                old_booking_date
            )

        # =================================================
        # BOOKING TIME
        # =================================================

        if (
            incoming_booking_time
            not in [None, ""]
        ):

            booking_time = (
                incoming_booking_time
            )

        else:

            booking_time = (
                old_booking_time
            )

        # =================================================
        # TOKEN
        # =================================================

        if (
            incoming_token_amount
            is not None
        ):

            booking_token = (
                incoming_token_amount
            )

        else:

            booking_token = (
                old_token_amount
            )

        # =================================================
        # BOOKING REMARK
        # =================================================

        if (
            incoming_booking_remark
            is not None
        ):

            booking_remark = (
                incoming_booking_remark
            )

        else:

            booking_remark = (
                old_booking_remark
            )

        # =================================================
        # UPDATE DATABASE
        # =================================================

        cursor.execute(
            """
            UPDATE leads
            SET

                name = %s,

                businessName = %s,

                phone = %s,

                alternatePhone = %s,

                email = %s,

                city = %s,

                state = %s,

                category = %s,

                source = %s,

                status = %s,

                priority = %s,

                assignedTo = %s,

                followUpDate = %s,

                followUpTime = %s,

                notes = %s,

                visitedDate = %s,

                visitedTime = %s,

                visitResult = %s,

                visitedByEmployeeId = %s,

                visitedByEmployeeName = %s,

                fieldEmployeeId = %s,

                fieldEmployeeName = %s,

                booking_date = %s,

                booking_time = %s,

                token_amount = %s,

                booking_remark = %s,

                updatedAt = %s

            WHERE id = %s

            """,
            (

                data.get("name", ""),

                data.get(
                    "businessName",
                    ""
                ),

                phone,

                data.get(
                    "alternatePhone",
                    ""
                ),

                data.get(
                    "email",
                    ""
                ),

                data.get(
                    "city",
                    ""
                ),

                data.get(
                    "state",
                    ""
                ),

                data.get(
                    "category",
                    ""
                ),

                data.get(
                    "source",
                    "Manual Entry"
                ),

                data.get(
                    "status",
                    "New"
                ),

                data.get(
                    "priority",
                    "Medium"
                ),

                data.get(
                    "assignedTo",
                    ""
                ),

                data.get(
                    "followUpDate",
                    ""
                ),

                data.get(
                    "followUpTime",
                    ""
                ),

                data.get(
                    "notes",
                    ""
                ),

                # VISIT DATE
                visited_date,

                # VISIT TIME
                visited_time,

                # VISIT RESULT
                visit_result,

                # VISITED EMPLOYEE
                visited_by_employee_id,

                visited_by_employee_name,

                # FIELD EMPLOYEE
                field_employee_id,

                field_employee_name,

                # BOOKING DATE
                booking_date,

                # BOOKING TIME
                booking_time,

                # TOKEN
                booking_token,

                # BOOKING REMARK
                booking_remark,

                # UPDATED AT
                now,

                lead_id

            )
        )

        conn.commit()

        # =================================================
        # GET UPDATED LEAD
        # =================================================

        cursor.execute(
            """
            SELECT *
            FROM leads
            WHERE id = %s
            """,
            (lead_id,)
        )

        result = cursor.fetchone()

        return serialize_lead(result)

    except HTTPException:

        raise

    except Exception as e:

        conn.rollback()

        print(
            "UPDATE LEAD ERROR:",
            e
        )

        raise HTTPException(
            status_code=500,
            detail="Failed to update lead"
        )

    finally:

        cursor.close()
        conn.close()


# =========================================================
# CREATE LEAD
# =========================================================

@router.post("")
def create_lead(
    lead: LeadCreate
):

    phone = normalize_phone(
        lead.phone
    )

    if not phone:

        raise HTTPException(
            status_code=400,
            detail="Phone required"
        )

    conn = get_db()

    cursor = conn.cursor(
        dictionary=True
    )

    try:

        cursor.execute(
            """
            SELECT id
            FROM leads
            WHERE phone = %s
            """,
            (phone,)
        )

        exist = cursor.fetchone()

        if exist:

            raise HTTPException(
                status_code=409,
                detail="Phone already exists"
            )

        now = datetime.now()

        assigned_to = str(
            lead.assignedTo or ""
        ).strip()

        cursor.execute(
            """
            INSERT INTO leads(

                name,

                businessName,

                phone,

                alternatePhone,

                email,

                city,

                state,

                category,

                source,

                status,

                priority,

                assignedTo,

                followUpDate,

                followUpTime,

                notes,

                createdAt,

                updatedAt

            )

            VALUES(

                %s,%s,%s,%s,%s,

                %s,%s,%s,%s,%s,

                %s,%s,%s,%s,%s,

                %s,%s

            )
            """,
            (

                lead.name,

                lead.businessName,

                phone,

                normalize_phone(
                    lead.alternatePhone
                ),

                lead.email,

                lead.city,

                lead.state,

                lead.category,

                lead.source,

                lead.status,

                lead.priority,

                assigned_to,

                lead.followUpDate,

                lead.followUpTime,

                lead.notes,

                now,

                now

            )
        )

        conn.commit()
        # =================================================
# CRM NOTIFICATION - NEW LEAD ASSIGNED
# =================================================

        if assigned_to:

         try:

            create_crm_notification(
            employee_id=assigned_to,
            title="New Lead Assigned",
            message=f"New lead assigned to you: {lead.name}",
            notification_type="lead"
           )

         except Exception as notification_error:

          print(
            "LEAD CRM NOTIFICATION ERROR:",
            str(notification_error)
        )
        


        new_id = cursor.lastrowid

        cursor.execute(
            """
            SELECT *
            FROM leads
            WHERE id = %s
            """,
            (new_id,)
        )

        result = cursor.fetchone()

        return serialize_lead(result)

    except HTTPException:

        conn.rollback()

        raise

    except Exception as e:

        conn.rollback()

        print(
            "CREATE LEAD ERROR:",
            e
        )

        raise HTTPException(
            status_code=500,
            detail="Failed to create lead"
        )

    finally:

        cursor.close()
        conn.close()


# =========================================================
# DELETE LEAD
# =========================================================

@router.delete("/{lead_id}")
def delete_lead(
    lead_id: int
):

    conn = get_db()

    cursor = conn.cursor()

    try:

        cursor.execute(
            """
            DELETE FROM leads
            WHERE id = %s
            """,
            (lead_id,)
        )

        conn.commit()

        deleted = cursor.rowcount

        if deleted == 0:

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

        return {

            "success": True,

            "message":
                "Lead deleted successfully"

        }

    except HTTPException:

        raise

    except Exception as e:

        conn.rollback()

        print(
            "DELETE LEAD ERROR:",
            e
        )

        raise HTTPException(
            status_code=500,
            detail="Failed to delete lead"
        )

    finally:

        cursor.close()
        conn.close()


# =========================================================
# BULK IMPORT
# =========================================================

@router.post("/bulk")
def bulk_import(
    data: BulkLeadImport
):

    conn = get_db()

    cursor = conn.cursor(
        dictionary=True
    )

    imported = 0

    duplicate = 0

    invalid = 0

    sales = []

    try:

        if data.assignMode not in [
            "single",
            "auto",
            "multiple"
        ]:

            raise HTTPException(
                status_code=400,
                detail="Invalid assign mode"
            )

        if data.assignMode == "auto":

            sales = get_sales_employees_from_db(
                conn
            )

            if not sales:

                raise HTTPException(
                    status_code=400,
                    detail="Sales employees not found"
                )

        for index, lead in enumerate(
            data.leads
        ):

            phone = normalize_phone(
                lead.phone
            )

            if not lead.name or not phone:

                invalid += 1

                continue

            cursor.execute(
                """
                SELECT id
                FROM leads
                WHERE phone = %s
                """,
                (phone,)
            )

            if cursor.fetchone():

                duplicate += 1

                continue

            assigned = ""

            # =============================================
            # SINGLE PERSON
            # =============================================

            if data.assignMode == "single":

                assigned = str(
                    data.assignedTo or ""
                ).strip()

                if not assigned:

                    raise HTTPException(
                        status_code=400,
                        detail="Employee ID required for assignment"
                    )

            # =============================================
            # AUTO DISTRIBUTE
            # =============================================

            elif data.assignMode == "auto":

                emp = sales[
                    imported % len(sales)
                ]

                assigned = emp[
                    "employeeId"
                ]

            # =============================================
            # MULTIPLE PERSON
            # =============================================

            elif data.assignMode == "multiple":

                if not data.selectedEmployees:

                    raise HTTPException(
                        status_code=400,
                        detail="Select multiple sales persons"
                    )

                assigned = str(
                    data.selectedEmployees[
                        imported % len(
                            data.selectedEmployees
                        )
                    ]
                ).strip()

            now = datetime.now()

            cursor.execute(
                """
                INSERT INTO leads(

                    name,

                    businessName,

                    phone,

                    email,

                    city,

                    state,

                    category,

                    source,

                    status,

                    priority,

                    assignedTo,

                    notes,

                    createdAt,

                    updatedAt

                )

                VALUES(

                    %s,%s,%s,%s,%s,%s,%s,

                    %s,%s,%s,%s,%s,%s,%s

                )
                """,
                (

                    lead.name,

                    lead.businessName,

                    phone,

                    lead.email,

                    lead.city,

                    lead.state,

                    lead.category,

                    lead.source,

                    lead.status,

                    lead.priority,

                    assigned,

                    lead.notes,

                    now,

                    now

                )
            )
            if assigned:

              try:

               create_crm_notification(
                 employee_id=assigned,
                 title="New Lead Assigned",
                 message=f"New lead assigned to you: {lead.name}",
                 notification_type="lead"
                )

              except Exception as notification_error:

               print(
            "BULK LEAD CRM NOTIFICATION ERROR:",
            str(notification_error)
              )


            imported += 1

        conn.commit()

        return {

            "success": True,

            "imported": imported,

            "duplicate": duplicate,

            "invalid": invalid

        }

    except HTTPException:

        conn.rollback()

        raise

    except Exception as e:

        conn.rollback()

        print(
            "BULK IMPORT ERROR:",
            e
        )

        raise HTTPException(
            status_code=500,
            detail="Failed to import leads"
        )

    finally:

        cursor.close()
        conn.close()


# =========================================================
# CSV IMPORT
# =========================================================

@router.post("/import")
async def import_csv(
    file: UploadFile = File(...)
):

    content = await file.read()

    text = content.decode(
        "utf-8-sig"
    )

    reader = csv.DictReader(
        io.StringIO(text)
    )

    conn = get_db()

    cursor = conn.cursor()

    imported = 0

    try:

        for row in reader:

            phone = normalize_phone(
                row.get("phone")
            )

            if not phone:

                continue

            now = datetime.now()

            cursor.execute(
                """
                INSERT IGNORE INTO leads

                (

                    name,

                    businessName,

                    phone,

                    email,

                    city,

                    state,

                    category,

                    source,

                    status,

                    priority,

                    assignedTo,

                    createdAt,

                    updatedAt

                )

                VALUES

                (

                    %s,%s,%s,%s,%s,

                    %s,%s,%s,%s,%s,

                    %s,%s,%s

                )
                """,
                (

                    row.get(
                        "name",
                        ""
                    ),

                    row.get(
                        "businessName",
                        ""
                    ),

                    phone,

                    row.get(
                        "email",
                        ""
                    ),

                    row.get(
                        "city",
                        ""
                    ),

                    row.get(
                        "state",
                        ""
                    ),

                    row.get(
                        "category",
                        ""
                    ),

                    "Excel Import",

                    "New",

                    "Medium",

                    "",

                    now,

                    now

                )
            )

            if cursor.rowcount > 0:

                imported += 1

        conn.commit()

        return {

            "success": True,

            "imported": imported

        }

    except Exception as e:

        conn.rollback()

        print(
            "CSV IMPORT ERROR:",
            e
        )

        raise HTTPException(
            status_code=500,
            detail="Failed to import CSV"
        )

    finally:

        cursor.close()
        conn.close()


# =========================================================
# FIELD EMPLOYEES
# =========================================================

@router.get("/employees/field")
def get_field_employees():

    conn = get_db()

    cursor = conn.cursor(
        dictionary=True
    )

    try:

        cursor.execute(
            """
            SELECT

                e.employeeId,

                e.fullname AS name,

                e.fullname AS fullName,

                e.department,

                e.designation

            FROM employees e

            LEFT JOIN departments d

                ON e.department =
                   d.departmentCode

            WHERE LOWER(
                COALESCE(
                    d.departmentName,
                    ''
                )
            ) LIKE '%sales%'

            ORDER BY e.fullname ASC
            """
        )

        rows = cursor.fetchall()

        return rows

    except Exception as e:

        print(
            "FIELD EMPLOYEE ERROR:",
            e
        )

        raise HTTPException(
            status_code=500,
            detail="Failed to fetch sales employees"
        )

    finally:

        cursor.close()
        conn.close()


# =========================================================
# ASSIGN FIELD EMPLOYEE
# =========================================================

class FieldAssignment(BaseModel):

    fieldEmployeeId: str = ""

    fieldEmployeeName: str = ""


@router.put("/{lead_id}/field-assignment")
def assign_field_employee(
    lead_id: int,
    assignment: FieldAssignment
):

    conn = get_db()

    cursor = conn.cursor(
        dictionary=True
    )

    try:

        cursor.execute(
            """
            SELECT 
              id,
              name
            FROM leads
            WHERE id = %s
            """,
            (lead_id,)
        )

        lead = cursor.fetchone()

        if not lead:

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

        cursor.execute(
            """
            UPDATE leads

            SET

                fieldEmployeeId = %s,

                fieldEmployeeName = %s,

                updatedAt = %s

            WHERE id = %s
            """,
            (

                assignment.fieldEmployeeId,

                assignment.fieldEmployeeName,

                datetime.now(),

                lead_id

            )
        )

        conn.commit()
        if assignment.fieldEmployeeId:

         try:

           create_crm_notification(
             employee_id=assignment.fieldEmployeeId,
             title="Field Visit Assigned",
             message=(
                f"You have been assigned a field visit "
                f"for lead: {lead['name']}"
            ),
            notification_type="field_assignment"
            )

         except Exception as notification_error:

          print(
            "FIELD ASSIGNMENT CRM NOTIFICATION ERROR:",
            str(notification_error)
        )


        cursor.execute(
            """
            SELECT *
            FROM leads
            WHERE id = %s
            """,
            (lead_id,)
        )

        updated_lead = cursor.fetchone()

        return {

            "success": True,

            "message":
                "Field employee assigned successfully",

            "lead":
                serialize_lead(
                    updated_lead
                )

        }

    except HTTPException:

        raise

    except Exception as e:

        conn.rollback()

        print(
            "FIELD ASSIGNMENT ERROR:",
            e
        )

        raise HTTPException(
            status_code=500,
            detail="Failed to assign field employee"
        )

    finally:

        cursor.close()
        conn.close()


# =========================================================
# UPDATE FIELD VISIT RESULT
# =========================================================

class VisitResultUpdate(BaseModel):

    visitResult: str


@router.put("/{lead_id}/visit-result")
def update_visit_result(
    lead_id: int,
    data: VisitResultUpdate
):

    conn = get_db()

    cursor = conn.cursor(
        dictionary=True
    )

    try:

        # =================================================
        # CHECK LEAD
        # =================================================

        cursor.execute(
            """
            SELECT *
            FROM leads
            WHERE id = %s
            """,
            (lead_id,)
        )

        lead = cursor.fetchone()

        if not lead:

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

        # =================================================
        # RESULT
        # =================================================

        result = (
            data.visitResult.strip()
        )

        allowed_results = [
            "Pending",
            "Converted",
            "Not Converted"
        ]

        if result not in allowed_results:

            raise HTTPException(
                status_code=400,
                detail="Invalid visit result"
            )

        # =================================================
        # CURRENT TIME
        #
        # ONLY updatedAt.
        # =================================================

        now = datetime.now()

        # =================================================
        # EXISTING VISIT DATE/TIME
        #
        # DO NOT CREATE CURRENT DATE/TIME.
        # =================================================

        visited_date = lead.get(
            "visitedDate"
        )

        visited_time = lead.get(
            "visitedTime"
        )

        # =================================================
        # VISIT DATE REQUIRED
        # =================================================

        if (
            not visited_date
            or str(
                visited_date
            ).strip()
            in [
                "",
                "0000-00-00",
                "None"
            ]
        ):

            raise HTTPException(
                status_code=400,
                detail=(
                    "Visit date is required "
                    "before updating visit result"
                )
            )

        # =================================================
        # VISIT TIME REQUIRED
        # =================================================

        if (
            not visited_time
            or str(
                visited_time
            ).strip()
            in [
                "",
                "00:00:00",
                "None"
            ]
        ):

            raise HTTPException(
                status_code=400,
                detail=(
                    "Visit time is required "
                    "before updating visit result"
                )
            )

        # =================================================
        # STATUS
        # =================================================

        status = (
            lead.get("status")
            or "Visited"
        )

        if result == "Converted":

            status = "Converted"

        elif result == "Not Converted":

            status = "Visited"

        elif result == "Pending":

            status = "Visited"

        # =================================================
        # SAVE
        # =================================================

        cursor.execute(
            """
            UPDATE leads

            SET

                status = %s,

                visitedDate = %s,

                visitedTime = %s,

                visitResult = %s,

                updatedAt = %s

            WHERE id = %s

            """,
            (

                status,

                visited_date,

                visited_time,

                result,

                now,

                lead_id

            )
        )

        conn.commit()

        # =================================================
        # RETURN UPDATED LEAD
        # =================================================

        cursor.execute(
            """
            SELECT *
            FROM leads
            WHERE id = %s
            """,
            (lead_id,)
        )

        updated = cursor.fetchone()

        return serialize_lead(updated)

    except HTTPException:

        raise

    except Exception as e:

        conn.rollback()

        print(
            "VISIT RESULT ERROR:",
            e
        )

        raise HTTPException(
            status_code=500,
            detail="Failed to update visit result"
        )

    finally:

        cursor.close()
        conn.close()
# =========================================================
# MANAGER BOOKING
# =========================================================

@router.get("/bookings")
def get_booking_leads(
    month: str = "",
    search: str = "",
    salesEmployee: str = ""
):
    """
    Manager Booking API

    Examples:

    GET /leads/bookings?month=2026-09

    GET /leads/bookings?month=2026-09&search=rahul

    GET /leads/bookings?month=2026-09&salesEmployee=KC5757

    Returns:
        items
        totalBookings
        totalPayment
        month
    """

    conn = get_db()

    cursor = conn.cursor(
        dictionary=True
    )

    try:

        # =====================================================
        # MONTH
        # =====================================================

        if not month:
            month = datetime.now().strftime(
                "%Y-%m"
            )

        try:

            month_start = datetime.strptime(
                month + "-01",
                "%Y-%m-%d"
            ).date()

        except ValueError:

            raise HTTPException(
                status_code=400,
                detail="Invalid month. Use YYYY-MM"
            )

        # =====================================================
        # NEXT MONTH
        # =====================================================

        if month_start.month == 12:

            next_month = date(
                month_start.year + 1,
                1,
                1
            )

        else:

            next_month = date(
                month_start.year,
                month_start.month + 1,
                1
            )

        # =====================================================
        # WHERE CONDITIONS
        # =====================================================

        conditions = [

            """
            booking_date IS NOT NULL
            """,

            """
            booking_date >= %s
            """,

            """
            booking_date < %s
            """
        ]

        params = [

            month_start,
            next_month
        ]

        # =====================================================
        # SEARCH
        # NAME / BUSINESS / PHONE
        # =====================================================

        if search and search.strip():

            search_value = (
                f"%{search.strip()}%"
            )

            conditions.append(
                """
                (
                    name LIKE %s
                    OR businessName LIKE %s
                    OR phone LIKE %s
                    OR alternatePhone LIKE %s
                )
                """
            )

            params.extend([
                search_value,
                search_value,
                search_value,
                search_value
            ])

        # =====================================================
        # SALES EMPLOYEE FILTER
        # =====================================================

        if (
            salesEmployee
            and salesEmployee.strip()
        ):

            conditions.append(
                """
                assignedTo = %s
                """
            )

            params.append(
                salesEmployee.strip()
            )

        # =====================================================
        # BUILD WHERE
        # =====================================================

        where_sql = " AND ".join(
            conditions
        )

        # =====================================================
        # GET BOOKINGS
        # =====================================================

        query = f"""
            SELECT

                id,

                name,

                businessName,

                phone,

                alternatePhone,

                email,

                city,

                state,

                category,

                source,

                status,

                priority,

                assignedTo,

                booking_date,

                booking_time,

                token_amount,

                booking_remark,

                createdAt,

                updatedAt

            FROM leads

            WHERE {where_sql}

            ORDER BY

                booking_date DESC,

                booking_time DESC,

                id DESC
        """

        cursor.execute(
            query,
            params
        )

        rows = cursor.fetchall()

        # =====================================================
        # TOTALS
        # =====================================================

        total_query = f"""
            SELECT

                COUNT(*) AS totalBookings,

                COALESCE(
                    SUM(
                        COALESCE(
                            token_amount,
                            0
                        )
                    ),
                    0
                ) AS totalPayment

            FROM leads

            WHERE {where_sql}
        """

        cursor.execute(
            total_query,
            params
        )

        totals = (
            cursor.fetchone()
            or {}
        )

        # =====================================================
        # FORMAT BOOKINGS
        # =====================================================

        items = []

        for row in rows:

            item = dict(row)

            # -----------------------------------------------
            # BOOKING DATE
            # -----------------------------------------------

            item["bookingDate"] = (
                format_date_value(
                    row.get(
                        "booking_date"
                    )
                )
            )

            # -----------------------------------------------
            # BOOKING TIME
            # -----------------------------------------------

            item["bookingTime"] = (
                format_time_value(
                    row.get(
                        "booking_time"
                    )
                )
            )

            # -----------------------------------------------
            # TOKEN AMOUNT
            # -----------------------------------------------

            token = row.get(
                "token_amount"
            )

            if isinstance(
                token,
                Decimal
            ):

                item["tokenAmount"] = float(
                    token
                )

            elif token is None:

                item["tokenAmount"] = 0

            else:

                try:

                    item["tokenAmount"] = float(
                        token
                    )

                except Exception:

                    item["tokenAmount"] = 0

            # -----------------------------------------------
            # REMARK
            # -----------------------------------------------

            item["bookingRemark"] = (
                row.get(
                    "booking_remark"
                )
                or ""
            )

            # -----------------------------------------------
            # SALES EMPLOYEE
            # -----------------------------------------------

            item["salesEmployee"] = (
                row.get(
                    "assignedTo"
                )
                or ""
            )

            items.append(
                item
            )

        # =====================================================
        # TOTAL PAYMENT FORMAT
        # =====================================================

        total_payment = (
            totals.get(
                "totalPayment"
            )
            or 0
        )

        if isinstance(
            total_payment,
            Decimal
        ):

            total_payment = float(
                total_payment
            )

        else:

            try:

                total_payment = float(
                    total_payment
                )

            except Exception:

                total_payment = 0

        # =====================================================
        # RESPONSE
        # =====================================================

        return {

            "success": True,

            "month": month,

            "items": items,

            "totalBookings": int(
                totals.get(
                    "totalBookings"
                )
                or 0
            ),

            "totalPayment": total_payment
        }

    except HTTPException:

        raise

    except Exception as e:

        print(
            "BOOKINGS API ERROR:",
            repr(e)
        )

        raise HTTPException(
            status_code=500,
            detail=(
                f"Failed to fetch bookings: {str(e)}"
            )
        )

    finally:

        cursor.close()

        conn.close()