import frappe, json
from custom_ui.db_utils import process_query_conditions, build_datatable_dict, get_count_or_filters, build_success_response, build_error_response, process_sorting
from custom_ui.services import AddressService, ClientService, ServiceAppointmentService, ProjectService
from frappe.utils import getdate

# ===============================================================================
# JOB MANAGEMENT API METHODS
# ===============================================================================


@frappe.whitelist()
def get_jobs_in_queue_count(company):
    try:
        filters = {
            'company': company,
            'is_scheduled': True,
        }
        count = frappe.db.count("Project", filters=filters)
        return build_success_response([count])
    except Exception as e:
        return build_error_response(str(e), 500)


@frappe.whitelist()
def get_jobs_in_progress_count(company):
    try:
        today = getdate()
        filters = {
            'company': company,
            'invoice_status': 'Not Ready',
            'expected_start_date': ['<=', today],
            'expected_end_date': ['>=', today],
        }
        count = frappe.db.count("Project", filters=filters)
        return build_success_response([count])
    except Exception as e:
        return build_error_response(str(e), 500)


@frappe.whitelist()
def get_jobs_late_count(company):
    try:
        today = getdate()
        filters = {
            'company': company,
            'invoice_status': 'Not Ready',
            'expected_end_date': ['<', today]
        }
        count = frappe.db.count("Project", filters=filters)
        return build_success_response([count])
    except Exception as e:
        return build_error_response(str(e), 500)


@frappe.whitelist()
def get_jobs_to_invoice_count(company):
    try:
        filters = {
            'company': company,
            'invoice_status': 'Ready to Invoice',
        }
        count = frappe.db.count("Project", filters=filters)
        return build_success_response([count])
    except Exception as e:
        return build_error_response(str(e), 500)


@frappe.whitelist()
def get_job_templates(company=None):
    """Get list of job (project) templates."""
    filters = {}
    if company:
        filters["company"] = company
    try:
        templates = frappe.get_all("Project Template", fields=["*"], filters=filters)
        return build_success_response(templates)
    except Exception as e:
        return build_error_response(str(e), 500)


@frappe.whitelist()
def create_job_from_sales_order(sales_order_name):
    """Create a Job (Project) from a given Sales Order"""
    try:
        sales_order = frappe.get_doc("Sales Order", sales_order_name)
        project_template = frappe.get_doc("Project Template", "SNW Install")
        new_project = frappe.get_doc({
            "doctype": "Project",
            "custom_address": sales_order.custom_job_address,
            # "custom_installation_address": sales_order.custom_installation_address,
            "project_name": sales_order.custom_job_address,
            "project_template": project_template,
            "custom_warranty_duration_days": 90,
            "sales_order": sales_order,
            "custom_company": sales_order.company
        })
        new_project.insert()
        for sales_order_item in sales_order.items:
            new_task = frappe.get_doc({
                "doctype": "Task",
                "project": new_project.name,
                "company": sales_order.company,
                "custom_property": sales_order.custom_job_address,
                "subject": sales_order_item.description,
            })
            new_task.insert()
        # Iterate through new tasks (if any) and set customer, address
        # job_tasks = frappe.get_all("Task", filters={"Project": new_job.name})
        # for task in job_tasks:
        #     task.custom_property = new_job.job_address
        #     task.save()
        return build_success_response(new_project.as_dict())
    except Exception as e:
        return build_error_response(str(e), 500)


@frappe.whitelist()
def get_job(job_id=""):
    """Get particular Job from DB"""
    print("DEBUG: Loading Job from database:", job_id)
    try:
        project = ProjectService.get_full_project_details(job_id)
        return build_success_response(project)
    except Exception as e:
        return build_error_response(str(e), 500)


@frappe.whitelist()
def get_job_task_table_data(filters={}, sortings={}, page=1, page_size=10):
    """Get paginated job tasks table data with filtering and sorting support."""
    print("DEBUG: raw task options received:", filters, sortings, page, page_size)

    processed_filters, processed_sortings, is_or, page, page_size = process_query_conditions(filters, sortings, page, page_size)

    print("DEBUG: Processed Filters:", processed_filters)

    if is_or:
        count = frappe.db.sql(*get_count_or_filters("Task", filters))[0][0]
    else:
        count = frappe.db.count("Task", filters=filters)

    print(f"DEBUG: Number of tasks returned: {count}")

    tasks = frappe.db.get_all(
        "Task",
        fields=["*"],
        filters=filters,
        limit=page_size,
        start=(page - 1) * page_size,
        order_by=processed_sortings
    )


    tableRows = []
    for task in tasks:
        address_name = frappe.get_value("Project", task.project, "job_address")
        full_address = frappe.get_value("Address", address_name, "full_address")
        tableRow = {}
        tableRow["id"] = task["name"]
        tableRow["subject"] = task["subject"]
        tableRow["address"] = full_address
        tableRow["status"] = task.get("status", "")
        tableRows.append(tableRow)

    table_data_dict = build_datatable_dict(data=tableRows, count=count, page=page, page_size=page_size)
    return build_success_response(table_data_dict)


@frappe.whitelist()
def get_job_task_list(job_id=""):
    if job_id:
        try:
            tasks = frappe.get_all('Task', filters={"project": job_id})
            task_docs = {task_id: frappe.get_doc(task_id) for task_id in tasks}
            return build_success_response(task_docs)
        except Exception as e:
            return build_error_response(str(e), 500)


@frappe.whitelist()
def get_jobs_table_data(filters={}, sortings=[], page=1, page_size=10):
    """Get paginated job table data with filtering and sorting support."""
    print("DEBUG: Raw job options received:", filters, sortings, page, page_size)
    filters = json.loads(filters) if isinstance(filters, str) else filters
    sortings = json.loads(sortings) if isinstance(sortings, str) else sortings

    processed_filters, processed_sortings, is_or, page, page_size = process_query_conditions(filters, sortings, page, page_size)

    # Handle count with proper OR filter support
    if is_or:
        count = frappe.db.sql(*get_count_or_filters("Project", processed_filters))[0][0]
    else:
        count = frappe.db.count("Project", filters=processed_filters)

    projects = frappe.db.get_all(
        "Project",
        fields=["*"],
        filters=processed_filters if not is_or else None,
        or_filters=processed_filters if is_or else None,
        limit=page_size,
        start=page * page_size,
        order_by=processed_sortings
    )

    tableRows = []
    for project in projects:
        tableRow = {}
        tableRow["id"] = project["name"]
        tableRow["name"] = project["name"]
        tableRow["job_address"] = project["job_address"]
        tableRow["customer"] = project.get("customer", "")
        tableRow["status"] = project.get("status", "")
        tableRow["invoice_status"] = project.get("invoice_status")
        tableRow["percent_complete"] = project.get("percent_complete", 0)
        tableRows.append(tableRow)

    data_table_dict = build_datatable_dict(data=tableRows, count=count, page=page, page_size=page_size)
    return build_success_response(data_table_dict)


@frappe.whitelist()
def upsert_job(data):
    """Create or update a job (project)."""
    try:
        if isinstance(data, str):
            data = json.loads(data)

        project_id = data.get("id")
        if not project_id:
            return {"status": "error", "message": "Project ID is required"}

        project = frappe.get_doc("Project", project_id)

        if "scheduledDate" in data:
            project.expected_start_date = data["scheduledDate"]

        if "foreman" in data:
            project.custom_install_crew = data["foreman"]

        project.save()
        return {"status": "success", "data": project.as_dict()}
    except Exception as e:
        return {"status": "error", "message": str(e)}

@frappe.whitelist()
def get_projects_for_calendar(start_date, end_date, company=None, project_templates=[]):
    """Get install projects for the calendar."""
    # Parse project_templates if it's a JSON string
    if isinstance(project_templates, str):
        project_templates = json.loads(project_templates)

    # put some emojis in the print to make it stand out
    print("📅📅📅", start_date, end_date, " company:", company, "project_templates:", project_templates, "type:", type(project_templates))
    try:
        filters = {
            "company": company
        } if company else {}
        if project_templates and len(project_templates) > 0:
            filters["project_template"] = ["in", project_templates]
        unscheduled_filters = filters.copy()
        unscheduled_filters["is_scheduled"] = 0

        # add to filter for if expected_start_date is between start_date and end_date OR expected_end_date is between start_date and end_date
        filters["expected_start_date"] = ["<=", getdate(end_date)]
        filters["expected_end_date"] = [">=", getdate(start_date)]
        # If date range provided, we could filter, but for now let's fetch all open/active ones
        # or maybe filter by status not Closed/Completed if we want active ones.
        # The user said "unscheduled" are those with status "Open" (and no date).
        # extend filters into unscheduled_filters

        project_names = frappe.get_all("Project", pluck="name", filters=filters)
        print("DEBUG: Found scheduled project names:", project_names)
        unscheduled_project_names = frappe.get_all("Project", pluck="name", filters=unscheduled_filters)
        print("DEBUG: Found unscheduled project names:", unscheduled_project_names)
        projects = [frappe.get_doc("Project", name).as_dict() for name in project_names]
        unscheduled_projects = [frappe.get_doc("Project", name).as_dict() for name in unscheduled_project_names]
        return build_success_response({ "projects": projects, "unscheduled_projects": unscheduled_projects })
    except Exception as e:
        return build_error_response(str(e), 500)

@frappe.whitelist()
def update_job_scheduled_dates(job_name: str, new_start_date: str = None, new_end_date: str = None, foreman_name: str = None):
    """Update job (project) schedule dates."""
    print("DEBUG: Updating job schedule:", job_name, new_start_date, new_end_date, foreman_name)
    try:
        project = frappe.get_doc("Project", job_name)
        project.expected_start_date = getdate(new_start_date) if new_start_date else None
        project.expected_end_date = getdate(new_end_date) if new_end_date else None
        if new_start_date and new_end_date:
            project.is_scheduled = 1
        else:
            project.is_scheduled = 0
        if foreman_name:
            project.custom_foreman = foreman_name
        project.save()
        return build_success_response(project.as_dict())
    except Exception as e:
        return build_error_response(str(e), 500)
