<?php
require_once ('dbClass.php');
$FormID = isset($_POST['FormID']) ? $_POST['FormID'] : '0';
// $start = intval($_POST['start']);
// $limit = intval($_POST['limit']);
$ModalType = isset($_POST['ModalType']) ? $_POST['ModalType'] : '';
include 'model.php';

include("assets/forceutf8-master/src/ForceUTF8/Encoding.php");  //used for UNICODE conversion / encoding

if($ModalType == 'sql'){
    //DATABASEE DEPENDENCY
    if($dbType == 1){
        $query = "SELECT ROUTINE_NAME AS ProcedureName, ROUTINE_DEFINITION AS definition FROM information_schema.ROUTINES 
        WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_SCHEMA='".$_SESSION['dbName']."' AND ROUTINE_NAME LIKE '%".$FormID."%' ORDER BY ROUTINE_NAME";
    }else{
        $query = "SELECT name AS ProcedureName, OBJECT_DEFINITION(object_id) AS definition 
            FROM sys.procedures 
            WHERE name LIKE '%" . $FormID . "%' AND type='P' AND name NOT LIKE '%_old'
            ORDER BY name ";
    }

    $result = db::getInstance()->db_select($query);

    //DATABASEE DEPENDENCY
    // Get total count
    if($dbType == 1){
        $query1 = "SELECT COUNT(*) AS TotalCount FROM information_schema.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_SCHEMA='".$_SESSION['dbName']."' AND ROUTINE_NAME LIKE '%".$FormID."%' AND ROUTINE_NAME NOT LIKE '%_old'";
    }else{
        $query1 = "SELECT COUNT(*) AS TotalCount FROM sys.procedures WHERE name LIKE '%" . $FormID . "%' AND name NOT LIKE '%_old'";
    }
    $result1 = db::getInstance()->db_select($query1);


    // Build result
    $data = [];
    foreach ($result['result_set'] as $row) {
        $calledSPs = [];
        $procedureName = $row['ProcedureName'];

        //DATABASEE DEPENDENCY
        // Use dependency-based sub-SP lookup
        if($dbType == 1){
            $subSpQuery = "SELECT ROUTINE_NAME
                FROM information_schema.ROUTINES
                WHERE ROUTINE_TYPE = 'PROCEDURE'
                AND ROUTINE_DEFINITION LIKE '%$procedureName%';";
        }else{

            $subSpQuery = "
                SELECT b.name AS SubProcedureName
                FROM sys.sysdepends a
                LEFT JOIN sys.objects b ON a.depid = b.object_id
                WHERE a.id = OBJECT_ID('$procedureName') AND b.type = 'P'";
        }

        

        $subSpResult = db::getInstance()->db_select($subSpQuery);

        if (!empty($subSpResult['result_set'])) {
            foreach ($subSpResult['result_set'] as $subRow) {
                $calledSPs[] = $subRow['SubProcedureName'];
            }
            $calledSPs = array_unique($calledSPs);
        }

        $data[] = [
            'ProcedureName' => $row['ProcedureName'],
            'CalledSPs' => $calledSPs
        ];
    }

    // Get company group
    //DATABASE DEPENDENCY
    if($dbType == 1){
        $getCompanyGroup = "SELECT GROUP_CONCAT(Company_Group SEPARATOR ', ') AS CompanyGroups FROM (SELECT DISTINCT Company_Group FROM Form_Group WHERE formid = $FormID) AS DistinctGroups";
    }else{
        $getCompanyGroup = "SELECT STRING_AGG(Company_Group, ', ') AS CompanyGroups FROM (SELECT DISTINCT Company_Group FROM Form_Group WHERE formid = $FormID) AS DistinctGroups";
    }
    $CompanyGroups = db::getInstanceMaster()->db_select($getCompanyGroup);

    
    $getGroupCompanies = "select Companyid, Company from Form_Group WHERE formid = $FormID"; 
    $GroupCompanies = db::getInstanceMaster()->db_select($getGroupCompanies);
 

    echo json_encode([
        'success' => true,
        'data' => $data,
        'companyGroups' => $CompanyGroups['result_set'][0]['CompanyGroups'],
        'GroupCompanies' => $GroupCompanies['result_set'],
        'total' => $result1['result_set'][0]['TotalCount']
    ]);
}else{
    $getCompanyGroup = "SELECT Company_Groupid,Company_Group FROM (SELECT DISTINCT Company_Groupid,Company_Group FROM Form_Group WHERE formid = $FormID) AS DistinctGroups";
    $CompanyGroups = db::getInstanceMaster()->db_select($getCompanyGroup);

    echo json_encode([
        'success' => true,
        'companyGroups' => $CompanyGroups['result_set']
    ]);
}



?>