<?php
require_once ('dbClass.php');
session_start();

$FormID = isset($_POST['FormID']) ? $_POST['FormID'] : '0';
$spValues = isset($_POST['sp']) ? $_POST['sp'] : [];
$companyValues = isset($_POST['company']) ? $_POST['company'] : [];
$CreatedBy = $_SESSION['user_id'] ?? 0;
$dbCompany = $_SESSION['dbCompany'] ?? 0;
$dbDivision = $_SESSION['dbDivision'] ?? 0;
$dbYear = $_SESSION['dbYear'] ?? 0;

$response = ['success' => false, 'message' => 'Unhandled failure'];

function restoreMainDbSession($dbCompany, $dbDivision, $dbYear) {
    $getCurrentUserCredentials = "
        SELECT DISTINCT CompanyID, Sql_User_Id, Database_name, Decrypted_Password, Ip, Port
        FROM Aireg
        WHERE CompanyID = $dbCompany AND DivisionID = '$dbDivision' AND Year = '$dbYear'
    ";
    $currentUserCredential = db::getInstanceMaster()->db_select($getCurrentUserCredentials);

    if (empty($currentUserCredential['result_set'])) {
        throw new Exception("No current user credentials found for CompanyID: $dbCompany");
    }

    $_SESSION['dbHost'] = $currentUserCredential['result_set'][0]['Ip'] . ',' . $currentUserCredential['result_set'][0]['Port'];
    $_SESSION['dbUser'] = $currentUserCredential['result_set'][0]['Sql_User_Id'];
    $_SESSION['dbPass'] = $currentUserCredential['result_set'][0]['Decrypted_Password'];
    $_SESSION['dbName'] = $currentUserCredential['result_set'][0]['Database_name'];
}


try{
    //  db::getInstance()->db_query("BEGIN TRANSACTION"); // Start transaction

    foreach ($companyValues as $companyID) {
        foreach ($spValues as $spName) {
            // 1. Get Procedure Definition
            $defQuery = "SELECT OBJECT_DEFINITION(OBJECT_ID('$spName')) AS Definition";
            $defResult = db::getInstance()->db_select($defQuery);
            $definition = $defResult['result_set'][0]['Definition'] ?? '';

            if (!$definition) {
                throw new Exception("No definition found for $spName");
            }

            // 2. Insert into DeveloperDataCloning
            $definitionEscaped = str_replace("'", "''", $definition); // escape for SQL insert
            $insertSQL = "
                INSERT INTO DeveloperDataCloning (
                    ObjectName, FormID, FromCompanyID, ToCompanyID,
                    DroppedObjectData, CreatedBy, CreatedAt
                )
                VALUES (
                    '$spName', $FormID, $dbCompany, $companyID,
                    '$definitionEscaped', '$CreatedBy', GETDATE()
                )
            ";
            db::getInstanceMaster()->db_insertQuery($insertSQL);

            if (!db::getInstanceMaster()->db_insertQuery($insertSQL)) {
                throw new Exception("Failed to insert into DeveloperDataCloning for $spName");
            }
            
            $getUserCredentials = "select DISTINCT CompanyID, Sql_User_Id, Database_name, Decrypted_Password, Ip, Port from Aireg where CompanyID=$companyID";
            $userCredential = db::getInstanceMaster()->db_select($getUserCredentials);

            if (empty($userCredential['result_set'])) {
                throw new Exception("No cloning credentials found for CompanyID: $companyID");
            }
           
            $_SESSION['clone_dbHost'] = trim($userCredential['result_set'][0]['Ip']).','.trim($userCredential['result_set'][0]['Port']);
            $_SESSION['clone_dbUser'] = trim($userCredential['result_set'][0]['Sql_User_Id']);
            $_SESSION['clone_dbPass'] = trim($userCredential['result_set'][0]['Decrypted_Password']);
            $_SESSION['clone_dbName'] = trim($userCredential['result_set'][0]['Database_name']);

            // 3. Drop Procedure
            $dropSQL = "IF OBJECT_ID('$spName', 'P') IS NOT NULL DROP PROCEDURE $spName";
            db::getInstanceCloning()->db_insertQuery($dropSQL);

            if (!db::getInstanceCloning()->db_insertQuery($dropSQL)) {
                throw new Exception("Failed to drop procedure $spName");
            }

            // 4. Recreate Procedure
            if (!db::getInstanceCloning()->db_create_procedure($definition)) {
                throw new Exception("Failed to recreate procedure $spName");
            }

            //Unset cloing session
            unset($_SESSION['clone_dbHost'], $_SESSION['clone_dbUser'], $_SESSION['clone_dbPass'], $_SESSION['clone_dbName']);

            // ✅ Restore main DB session after successful clone
            restoreMainDbSession($dbCompany, $dbDivision, $dbYear);
        }
    }

   
    //  db::getInstance()->db_query("COMMIT");
    $response = ['success' => true, 'message' => 'Stored procedures backed up and recreated.'];

} catch (Exception $e) {
    //Rollback & Cleanup
    db::getInstance()->db_insertQuery("ROLLBACK");
    unset($_SESSION['clone_dbHost'], $_SESSION['clone_dbUser'], $_SESSION['clone_dbPass'], $_SESSION['clone_dbName']);

    // ✅ Force-restore main DB session even after failure
    restoreMainDbSession($dbCompany, $dbDivision, $dbYear);

    $response = ['success' => false, 'message' => 'Error: ' . $e->getMessage()];
}

echo json_encode($response);



?>