<?php
$app->post('/app/report_filter_params', function() use ($app, $conn) {

    $post_data = $app->request->post();
    $result = array(
        "mdm" => array(),
        "territory" => array(),
        "hq" => array(),
        "fa" => array(),
    );

    date_default_timezone_set('Asia/Kolkata');

    if(!empty($post_data)) {

        $mdmid = isset($post_data['mdmid']) ? intval($post_data['mdmid']) : 0;
        $territory_id = isset($post_data['territory_id']) ? intval($post_data['territory_id']) : 0;
	$headquarter_id = isset($post_data['headquarter_id']) ? intval($post_data['headquarter_id']) : 0;

        if ($mdmid > 0) {

            /* =========================
               1. MDM
            ========================= */
            $sql = "SELECT id, name FROM mdm WHERE id = $mdmid";
            $sql_res = mysqli_query($conn, $sql);

            while ($row = mysqli_fetch_assoc($sql_res)) {
                $result['mdm'][] = (object) $row;
            }

            /* =========================
               2. TERRITORY
            ========================= */
            $territory_sql = "
                SELECT DISTINCT t.id, t.name, m.id as mdmid
                FROM territories t
                INNER JOIN mdm m ON m.region = t.region
                WHERE m.id = $mdmid
            ";

            $territory_res = mysqli_query($conn, $territory_sql);

            while ($row = mysqli_fetch_assoc($territory_res)) {
                $result['territory'][] = (object) $row;
            }

            /* =========================
               3. HQ (FILTER BY TERRITORY)
            ========================= */
            $hq_sql = "
                SELECT h.id, h.name, h.territory 
		FROM headquarter h 
		LEFT JOIN mdm_headquarter mh on mh.headquarter_id  = h.id
		LEFT JOIN territories t on t.id = h.territory 
            ";

            // Apply territory filter ONLY if passed
            if ($territory_id > 0) {
                $hq_sql .= " where t.id = $territory_id or mh.mdm_id = $mdmid";
            }

            $hq_res = mysqli_query($conn, $hq_sql);

            while ($row = mysqli_fetch_assoc($hq_res)) {
                $result['hq'][] = (object) $row;
            }

            /* =========================
               4. FA (FILTER BY TERRITORY + HQ)
            ========================= */
            $fa_sql = "
                SELECT 
                    fa.id, 
                    fa.name, 
                    $mdmid as mdmid,
                    fa.headquarter as hid,
                    h.territory as territory_id
                FROM fa
                INNER JOIN headquarter h ON h.id = fa.headquarter
                INNER JOIN mdm_headquarter mh ON mh.headquarter_id = h.id
                WHERE mh.mdm_id = $mdmid
            ";

            if ($headquarter_id > 0) {
                $fa_sql .= " AND h.id = $headquarter_id ";
            }

            $fa_res = mysqli_query($conn, $fa_sql);

            while ($row = mysqli_fetch_assoc($fa_res)) {
                $result['fa'][] = (object) $row;
            }
        }
    }

    echo json_encode([
        "success" => 1,
        "result" => $result
    ]);
});