<?php

// Validate export user
function validateUser($user)
{
    if (empty($user)) return '';

    if (!is_numeric($user)) {
        $_SESSION['return'] = 3;
        $_SESSION['return_message'] = 'Utilisateur non valide.';
        return false;
    }
    return $user;
}

function validateAll($all)
{
    return empty($all) ? '' : $all;
}

// Validate export type
function validateExportType($type)
{
    $allowedTypes = ['etablissement', 'departement', 'charge_affaire'];
    if (!in_array($type, $allowedTypes)) {
        $_SESSION['return'] = 3;
        $_SESSION['return_message'] = 'Type d\'Export non valide.';
        return false;
    }
    return $type;
}

// Validate date input
function validateDate($date)
{
    if (empty($date)) return '';

    if (DateTime::createFromFormat('Y-m-d', $date) === false) {
        $_SESSION['return'] = 3;
        $_SESSION['return_message'] = 'Date non valide.';
        return false;
    }
    return $date;
}

// Validate Year
function validateYear($year)
{
    if (empty($year)) return '';

    if (!preg_match('/^\d{4}$/', $year)) {
        $_SESSION['return'] = 3;
        $_SESSION['return_message'] = 'Année non valide.';
        return false;
    }
    return (int)$year;
}

// Validate margin
function validateMargin($margin)
{
    if (empty($margin)) return '';

    if (!is_numeric($margin)) {
        $_SESSION['return'] = 3;
        $_SESSION['return_message'] = 'Marge saisie non valide.';
        return false;
    }
    return (float)$margin;
}

// Validate for message display
function canExport($type, $startDate, $endDate, $year, $minMarge, $maxMarge, $user, $all)
{
    if ($type === false) return false;
    if ($startDate === false) return false;
    if ($endDate === false) return false;
    if ($year === false) return false;
    if ($minMarge === false) return false;
    if ($maxMarge === false) return false;

    if (!empty($startDate) && !empty($endDate) && $startDate > $endDate) {
        $_SESSION['return'] = 3;
        $_SESSION['return_message'] = 'Date non valide, date de début doit être avant la date de fin.';
        return false;
    }

    if (!empty($minMarge) && !empty($maxMarge) && $minMarge > $maxMarge) {
        $_SESSION['return'] = 3;
        $_SESSION['return_message'] = 'Marge non valide, marge minimale doit être inférieure à la marge maximale.';
        return false;
    }

    if (empty($user) && empty($all)) {
        $_SESSION['return'] = 3;
        $_SESSION['return_message'] = 'Utilisateur non valide.';
        return false;
    }

    return true;
}

// Build Query
function buildParameterizedQuery($type, $startDate, $endDate, $year, $minMarge, $maxMarge, $user, $all)
{
    $whereClauses = [];
    $condRemisClt = [];
    $condCommande = [];
    $params = [];

    // User condition
    if (!empty($user) && !$all) {
        $whereClauses[] = "( t_structure.id = :user OR t_structure.parent_id = :user )";
        $params[':user'] = $user;
    }

    // Year condition
    if (!empty($year)) {
        $condRemisClt[] = "YEAR(t_devis.date_update) = :year";
        $condCommande[] = "YEAR(t_devis.date_validate) = :year";
        $params[':year'] = $year;
    }

    // Date conditions
    if (!empty($startDate)) {
        $condRemisClt[] = "t_devis.date_update >= :start_date";
        $condCommande[] = "t_devis.date_validate >= :start_date";
        $params[':start_date'] = $startDate;
    }

    if (!empty($endDate)) {
        $condRemisClt[] = "t_devis.date_update <= :end_date";
        $condCommande[] = "t_devis.date_validate <= :end_date";
        $params[':end_date'] = $endDate;
    }

    // Margin conditions
    if (!empty($minMarge)) {
        $whereClauses[] = "t_devis.marge_tx > :min_marge";
        $params[':min_marge'] = $minMarge;
    }

    if (!empty($maxMarge)) {
        $whereClauses[] = "t_devis.marge_tx < :max_marge";
        $params[':max_marge'] = $maxMarge;
    }

    // Combine all clauses
    $whereSql = !empty($whereClauses) ? " AND " . implode(" AND ", $whereClauses) : "";
    $whereSqlRemis = !empty($condRemisClt) ? " AND " . implode(" AND ", $condRemisClt) : "";
    $whereSqlCommande = !empty($condCommande) ? " AND " . implode(" AND ", $condCommande) : "";

    // Build the appropriate query based on type
    switch ($type) {
        case 'etablissement':
            $query = "SELECT
                        sub.ETABLISSEMENT,
                        sub.NB_DEVIS_REMIS,
                        sub.CA_POTENTIEL,
                        sub.NB_DEVIS_COMMANDÉ,
                        sub.CA_COMMANDÉ,
                        sub.CMD_NON_PROGRAMMÉE,
                        sub.CA_NON_PROGRAMMÉ,
                        ROUND(100 * (sub.MARGE_VALEUR / NULLIF(sub.CA_COMMANDÉ, 0)), 2) AS 'MARGE_%',
                        sub.MARGE_VALEUR
                    FROM (
                        SELECT
                            t_structure.libelle AS 'ETABLISSEMENT',
                            SUM(CASE WHEN t_devis.enabled = 1 AND t_devis.etat = 1 AND t_devis.validation_client = 0 $whereSqlRemis THEN 1 ELSE 0 END) AS 'NB_DEVIS_REMIS',
                            ROUND(SUM(CASE WHEN t_devis.enabled = 1 AND t_devis.etat = 1 AND t_devis.validation_client = 0 $whereSqlRemis THEN t_devis.total_ht ELSE 0 END),2) AS 'CA_POTENTIEL',
                            SUM(CASE WHEN t_devis.enabled = 1 AND t_devis.etat = 1 AND t_devis.validation_client = 1 $whereSqlCommande THEN 1 ELSE 0 END) AS 'NB_DEVIS_COMMANDÉ',
                            ROUND(SUM(CASE WHEN t_devis.enabled = 1 AND t_devis.etat = 1 AND t_devis.validation_client = 1 $whereSqlCommande THEN t_devis.total_ht ELSE 0 END),2) AS 'CA_COMMANDÉ',
                            SUM(CASE WHEN t_devis.enabled = 1 AND t_devis.etat = 1 AND t_devis.validation_client = 1 $whereSqlCommande AND t_planning_devis.id_devis IS NULL THEN 1 ELSE 0 END) AS 'CMD_NON_PROGRAMMÉE',
                            ROUND(SUM(CASE WHEN t_devis.enabled = 1 AND t_devis.etat = 1 AND t_devis.validation_client = 1 $whereSqlCommande AND t_planning_devis.id_devis IS NULL THEN t_devis.total_ht ELSE 0 END),2) AS 'CA_NON_PROGRAMMÉ',
                            ROUND(SUM(CASE WHEN t_devis.enabled = 1 AND t_devis.etat = 1 AND t_devis.validation_client = 1 $whereSqlCommande THEN (COALESCE(NULLIF(marge_tx, 0), 35) * t_devis.total_ht) / 100 ELSE 0 END),2) AS 'MARGE_VALEUR'
                        FROM t_devis
                            RIGHT JOIN t_structure ON t_structure.id = t_devis.id_etablissement 
                            LEFT JOIN (SELECT DISTINCT id_devis FROM t_planning) AS t_planning_devis ON t_devis.id = t_planning_devis.id_devis 
                        WHERE t_structure.niveau_id = 3 AND t_structure.enabled = 1 $whereSql
                        GROUP BY t_structure.libelle
                    ) AS sub
                    ORDER BY sub.ETABLISSEMENT ASC";
            break;

        case 'departement':
            $query = "SELECT 
                            sub.DEP,
                            sub.NB_DEVIS_REMIS,
                            sub.CA_POTENTIEL,
                            sub.NB_DEVIS_COMMANDÉ,
                            sub.CA_COMMANDÉ,
                            sub.CMD_NON_PROGRAMMÉE,
                            sub.CA_NON_PROGRAMMÉ,
                            ROUND(100 * (sub.MARGE_VALEUR / NULLIF(sub.CA_COMMANDÉ, 0)), 2) AS 'MARGE_%',
                            sub.MARGE_VALEUR
                        FROM (
                            SELECT 
                                LEFT(COALESCE(t_location_devis_site.cp, t_location.cp), 2) AS DEP,
                                COUNT(DISTINCT CASE WHEN t_devis.enabled = 1 AND t_devis.etat = 1 AND t_devis.validation_client = 0 $whereSqlRemis THEN t_devis.id END) AS NB_DEVIS_REMIS,
                                ROUND(SUM(CASE WHEN t_devis.enabled = 1 AND t_devis.etat = 1 AND t_devis.validation_client = 0 $whereSqlRemis THEN 
                                    CASE WHEN t_devis.id_contrat_entretien IS NOT NULL THEN t_devis_site.total ELSE t_devis.total_ht END 
                                ELSE 0 END), 2) AS CA_POTENTIEL,
                                COUNT(DISTINCT CASE WHEN t_devis.enabled = 1 AND t_devis.etat = 1 AND t_devis.validation_client = 1 $whereSqlCommande THEN t_devis.id END) AS NB_DEVIS_COMMANDÉ,
                                ROUND(SUM(CASE WHEN t_devis.enabled = 1 AND t_devis.etat = 1 AND t_devis.validation_client = 1 $whereSqlCommande THEN 
                                    CASE WHEN t_devis.id_contrat_entretien IS NOT NULL THEN t_devis_site.total ELSE t_devis.total_ht END 
                                ELSE 0 END), 2) AS CA_COMMANDÉ,
                                COUNT(DISTINCT CASE WHEN t_devis.enabled = 1 AND t_devis.etat = 1 AND t_devis.validation_client = 1 $whereSqlCommande AND t_planning_devis.id_devis IS NULL THEN t_devis.id END) AS CMD_NON_PROGRAMMÉE,
                                ROUND(SUM(CASE WHEN t_devis.enabled = 1 AND t_devis.etat = 1 AND t_devis.validation_client = 1 $whereSqlCommande AND t_planning_devis.id_devis IS NULL THEN 
                                    CASE WHEN t_devis.id_contrat_entretien IS NOT NULL THEN t_devis_site.total ELSE t_devis.total_ht END 
                                ELSE 0 END), 2) AS CA_NON_PROGRAMMÉ,
                                ROUND(SUM(CASE WHEN t_devis.enabled = 1 AND t_devis.etat = 1 AND t_devis.validation_client = 1 $whereSqlCommande THEN 
                                    (COALESCE(NULLIF(marge_tx, 0), 35) * 
                                        CASE WHEN t_devis.id_contrat_entretien IS NOT NULL THEN t_devis_site.total ELSE t_devis.total_ht END) / 100
                                END), 2) AS MARGE_VALEUR
                            FROM 
                                t_devis
                                LEFT JOIN t_devis_location ON t_devis_location.id_devis = t_devis.id
                                INNER JOIN t_location ON t_devis_location.id_location = t_location.id
                                INNER JOIN t_structure ON t_structure.id = t_devis.id_etablissement
                                LEFT JOIN t_devis_site ON t_devis_site.id_devis = t_devis.id
                                INNER JOIN t_location AS t_location_devis_site ON t_devis_site.id_location = t_location_devis_site.id
                                LEFT JOIN (SELECT DISTINCT id_devis FROM t_planning) AS t_planning_devis ON t_devis.id = t_planning_devis.id_devis
                            WHERE 1=1 $whereSql 
                            GROUP BY LEFT(COALESCE(t_location_devis_site.cp, t_location.cp), 2)
                        ) AS sub
                        ORDER BY sub.DEP ASC";
            break;

        case 'charge_affaire':
            $query = "SELECT 
                        sub.CHARGÉ_AFFAIRE,
                        sub.ID,
                        sub.NB_DEVIS_REMIS,
                        sub.CA_POTENTIEL,
                        sub.NB_DEVIS_COMMANDÉ,
                        sub.CA_COMMANDÉ,
                        sub.CMD_NON_PROGRAMMÉE,
                        sub.CA_NON_PROGRAMMÉ,
                        ROUND(100 * (sub.MARGE_VALEUR / NULLIF(sub.CA_COMMANDÉ, 0)), 2) AS 'MARGE_%',
                        sub.MARGE_VALEUR
                    FROM (
                        SELECT 
                            CONCAT(t_user.prenom,' ',t_user.nom) AS CHARGÉ_AFFAIRE,
                            t_user.id AS ID,
                            SUM(CASE WHEN t_devis.enabled = 1 AND t_devis.etat = 1 AND t_devis.validation_client = 0 $whereSqlRemis THEN 1 ELSE 0 END) AS NB_DEVIS_REMIS,
                            ROUND(SUM(CASE WHEN t_devis.enabled = 1 AND t_devis.etat = 1 AND t_devis.validation_client = 0 $whereSqlRemis THEN t_devis.total_ht ELSE 0 END), 2) AS CA_POTENTIEL,
                            SUM(CASE WHEN t_devis.enabled = 1 AND t_devis.etat = 1 AND t_devis.validation_client = 1 $whereSqlCommande THEN 1 ELSE 0 END) AS NB_DEVIS_COMMANDÉ,
                            ROUND(SUM(CASE WHEN t_devis.enabled = 1 AND t_devis.etat = 1 AND t_devis.validation_client = 1 $whereSqlCommande THEN t_devis.total_ht ELSE 0 END), 2) AS CA_COMMANDÉ,
                            SUM(CASE WHEN t_devis.enabled = 1 AND t_devis.etat = 1 AND t_devis.validation_client = 1 $whereSqlCommande AND t_planning_devis.id_devis IS NULL THEN 1 ELSE 0 END) AS CMD_NON_PROGRAMMÉE,
                            ROUND(SUM(CASE WHEN t_devis.enabled = 1 AND t_devis.etat = 1 AND t_devis.validation_client = 1 $whereSqlCommande AND t_planning_devis.id_devis IS NULL THEN t_devis.total_ht ELSE 0 END), 2) AS CA_NON_PROGRAMMÉ,
                            ROUND(SUM(CASE WHEN t_devis.enabled = 1 AND t_devis.etat = 1 AND t_devis.validation_client = 1 $whereSqlCommande THEN 
                                (COALESCE(NULLIF(marge_tx, 0), 35) * t_devis.total_ht) / 100 END), 2) AS MARGE_VALEUR
                        FROM t_devis
                            INNER JOIN t_structure ON t_structure.id = t_devis.id_etablissement 
                            INNER JOIN t_user ON t_user.id = t_devis.id_user_referent
                            LEFT JOIN (SELECT DISTINCT id_devis FROM t_planning) AS t_planning_devis ON t_devis.id = t_planning_devis.id_devis
                        WHERE 
                            t_structure.niveau_id = 3 
                            AND t_user.enabled = 1 
                            AND t_user.id_role_saspro NOT IN (5, 6)
                            $whereSql 
                        GROUP BY CONCAT(t_user.prenom,' ',t_user.nom), t_user.id
                    ) AS sub
                    ORDER BY sub.CHARGÉ_AFFAIRE ASC";
            break;
    }

    // die($query);

    return [
        'query' => $query,
        'params' => $params
    ];
}
