from flask import Blueprint, send_file, current_app, request
from pathlib import Path
from src.database import Database
from src.utils import images_extensions

api_custom = Blueprint("api_custom", __name__)


@api_custom.route("/<model>/avatar/<int:id>")
def send_avatar(model: str, id: int):
    # Ruta base de los avatares
    base_path = Path(current_app.root_path).joinpath("uploads", "avatars", model)

    # Default avatar
    avatar_src = base_path.joinpath("default_avatar.svg")

    # Buscar archivo por id y extensión
    for ext in images_extensions:
        candidate = base_path.joinpath(f"{id}.{ext}")
        if candidate.is_file():
            avatar_src = candidate
            break

    return send_file(str(avatar_src))


@api_custom.route("/jobs/<int:id>")
def get_job_with_client(id):
    """
    id: number;
    client_id: number;
    client_name: string;
    client_lastname: string;
    client_email: string;
    client_phone: string;
    client_address: string;
    client_notes: string;
    job_details: string;
    job_quantity: number;
    job_price: number;
    job_total: number;
    status: string;
    create_date: string;
    update_date: string;
    """
    fields = (
        "j.id, j.client_id, j.details job_details, j.quantity job_quantity,"
        "j.price job_price, j.total job_total, c.name client_name,"
        "c.lastname client_lastname, c.email client_email, c.phone client_phone,"
        "c.address client_address, c.notes client_notes,"
        "CASE j.status "
        "WHEN 0 THEN 'No iniciado' "
        "WHEN 1 THEN 'En proceso' "
        "WHEN 2 THEN 'Finalizado' "
        "WHEN 3 THEN 'Pagado' "
        "END status_text,"
        "j.status, j.create_date, j.update_date"
    )
    query = (
        f"SELECT {fields} FROM jobs j "
        "INNER JOIN clients c ON c.id=j.client_id "
        "WHERE j.id=%s"
    )

    with Database() as db:
        resp = db.fetchone(query, (id,))

    return resp.todict()


@api_custom.route("/jobs")
@api_custom.route("/jobs/")
def get_jobs():
    kvargs = {**request.args}
    limit = int(kvargs.pop("limit", 50))
    offset = int(kvargs.pop("skip", 0))

    where_clauses = []
    params = []

    for k, v in kvargs.items():
        v_str = str(v)
        if "*" in v_str:
            v_str = v_str.replace("*", "%")
            where_clauses.append(f"{k} LIKE %s")
        else:
            where_clauses.append(f"{k}=%s")
        params.append(v_str)

    where_sql = f"WHERE {' AND '.join(where_clauses)}" if where_clauses else ""

    query = f"""
        SELECT 
            j.id, j.client_id, j.details AS job_details, j.quantity AS job_quantity,
            j.price AS job_price, j.total AS job_total,
            c.name AS client_name, c.lastname AS client_lastname,
            c.email AS client_email, c.phone AS client_phone,
            c.address AS client_address, c.notes AS client_notes,
            CASE j.status
                WHEN 0 THEN 'No iniciado'
                WHEN 1 THEN 'En proceso'
                WHEN 2 THEN 'Finalizado'
                WHEN 3 THEN 'Pagado'
            END AS status_text,
            j.status, j.create_date, j.update_date
        FROM jobs j
        INNER JOIN clients c ON c.id = j.client_id
        {where_sql}
        LIMIT %s OFFSET %s
    """

    with Database() as db:
        resp = db.fetchall(query, params + [limit, offset])

    return resp.todict()