<?php
require 'db_config/config.php';

header("Content-Type: application/json; charset=UTF-8");


// Function to handle errors
function send_error($message, $code = 400) {
    echo json_encode(["message" => $message,"code" => 400]);
    exit();
}

// Function to send success response
function send_success($message, $data = null) {
    
    $response = ["message" => $message,"code" => 200];
    if ($data !== null) {
        $response["data"] = $data;
    }
    echo json_encode($response);
    exit();
}

// Validate and escape input
function validate_input($con, $data) {
    return mysqli_real_escape_string($con, trim($data));
}

function copyDBWithData($olddb, $db_name1, $DB_HOST, $DB_USER, $DB_PASS)
{
    $mysqli = new mysqli($DB_HOST, $DB_USER, $DB_PASS) or die($mysqli->error);

    $database = $mysqli->query("SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME='$db_name1'") or die($mysqli->error);
    if ($database->num_rows == 0) {
        $response['code'] = 1; // Error
        $response['message'] = $db_name1 . ' - DATABASE NOT CREATED';
        echo json_encode($response);
        exit;
    }

    $tables = $mysqli->query("SHOW TABLES FROM $olddb") or die($mysqli->error);
    while ($table = $tables->fetch_array()) {
        $TABLE = $table[0];
        $mysqli->query("CREATE TABLE $db_name1.$TABLE LIKE $olddb.$TABLE") or die($mysqli->error);
        $mysqli->query("INSERT INTO $db_name1.$TABLE SELECT * FROM $olddb.$TABLE") or die($mysqli->error);
    }
}

function createDB($DB_HOST, $DB_USER, $DB_PASS, $cpanelUsername, $cpanelPassword, $databaseName, $mainDatabase)
{
    global $databaseUser;
    global $databaseUser2;
    $phpMyAdminUrl = "https://pumpmanager.in:2083/cpsess1161737911/frontend/jupiter/sql/addb.html";

    $loginData = array(
        'pma_username' => $cpanelUsername,
        'pma_password' => $cpanelPassword,
    );

    $curlOptions = array(
        CURLOPT_URL => $phpMyAdminUrl,
        CURLOPT_POST => true,
        CURLOPT_POSTFIELDS => http_build_query($loginData),
        CURLOPT_RETURNTRANSFER => true,
        CURLOPT_USERPWD => "$cpanelUsername:$cpanelPassword",
    );

    $ch = curl_init();
    curl_setopt_array($ch, $curlOptions);

    $response = curl_exec($ch);

    if (strpos($response, 'phpMyAdmin') !== false) {
        $createDatabaseData = array('db' => $databaseName);

        $createDatabaseOptions = array(
            CURLOPT_URL => $phpMyAdminUrl,
            CURLOPT_POST => true,
            CURLOPT_POSTFIELDS => http_build_query($createDatabaseData),
            CURLOPT_RETURNTRANSFER => true,
            CURLOPT_FOLLOWLOCATION => true,
        );

        curl_setopt_array($ch, $createDatabaseOptions);
        $createDatabaseResponse = curl_exec($ch);
        // var_dump($createDatabaseResponse);
        if (strpos($createDatabaseResponse, 'Added the database') !== false) {
            // echo "Database created successfully.";

            // ... (Code to add user privileges to the database)
            $lastUrl=curl_getinfo($ch, CURLINFO_EFFECTIVE_URL); 
            //$db_name1=$user.'_'.$newdb;
            $myvars = 'user='.$databaseUser.'&db='.$databaseName.'&privileges=ALL';
            $url1 = "https://pumpmanager.in:2083/cpsess1161737911/frontend/jupiter/sql/addusertodb.html";
            $ch = curl_init($url1);
            curl_setopt( $ch, CURLOPT_POST, 1);
            curl_setopt( $ch, CURLOPT_POSTFIELDS, $myvars);
            curl_setopt( $ch, CURLOPT_FOLLOWLOCATION, 1);
            curl_setopt( $ch, CURLOPT_HEADER, 0); 
            curl_setopt( $ch, CURLOPT_RETURNTRANSFER, 1);
            curl_setopt( $ch, CURLOPT_USERPWD, "$cpanelUsername:$cpanelPassword");
            $lastUrl1=curl_getinfo($ch, CURLINFO_EFFECTIVE_URL);
            $response1 = curl_exec( $ch );
            if(curl_exec($ch) === false){
                $response['code'] = 500; // Error
                $response['message'] = 'Curl error: ' . curl_error($ch);
                exit;
            }else{
                // $response['code'] = 200; // Error
                // $response['message'] = 'Application user added to database';
            }
            
            
            $lastUrl=curl_getinfo($ch, CURLINFO_EFFECTIVE_URL); 
            //$db_name1=$user.'_'.$newdb;
            $myvars = 'user='.$databaseUser2.'&db='.$databaseName.'&privileges=ALL';
            $url1 = "https://pumpmanager.in:2083/cpsess1161737911/frontend/jupiter/sql/addusertodb.html";
            $ch = curl_init($url1);
            curl_setopt( $ch, CURLOPT_POST, 1);
            curl_setopt( $ch, CURLOPT_POSTFIELDS, $myvars);
            curl_setopt( $ch, CURLOPT_FOLLOWLOCATION, 1);
            curl_setopt( $ch, CURLOPT_HEADER, 0); 
            curl_setopt( $ch, CURLOPT_RETURNTRANSFER, 1);
            curl_setopt( $ch, CURLOPT_USERPWD, "$cpanelUsername:$cpanelPassword");
            $lastUrl1=curl_getinfo($ch, CURLINFO_EFFECTIVE_URL);
            $response1 = curl_exec( $ch );
            
            if(curl_exec($ch) === false){
                $response['code'] = 500; // Error
                $response['message'] = 'Curl error: ' . curl_error($ch);
                exit;
            }else{
                // $response['code'] = 200; // Error
                // $response['message'] = 'Desktop user added to database';
            }
            curl_close($ch);

            copyDBWithData($mainDatabase, $databaseName,$DB_HOST, $DB_USER, $DB_PASS);
        } else {
            $response['code'] = 1; // Error
            $response['message'] = "Failed to create the database.";
            echo json_encode($response);
        }
    } else {
        $response['code'] = 1; // Error
        $response['message'] = "Login failed.";
        echo json_encode($response);
    }

    curl_close($ch);
}

// Handle different request methods
$method = $_SERVER['REQUEST_METHOD'];

if ($method === 'POST') {
    $data = json_decode(file_get_contents("php://input"), true);
    if(!empty($data["username"]) && !empty($data["password"]) && !empty($data["password2"]) && !empty($data["company_name"])){
        // Validate input data
        $username = validate_input($con, $data["username"] ?? '');
        $company_name = validate_input($con, $data["company_name"] ?? '');
        $password = validate_input($con, $data["password"] ?? '');
        $password2 = validate_input($con, $data["password2"] ?? '');
        $contact_no = validate_input($con, $data["contact_no"] ?? '');
        $address = validate_input($con, $data["address"] ?? '');
        $date = date('Y-m-d');
        $exp_date = date('Y-m-d',strtotime('+ 15 Days'));
        $status = validate_input($con, $data["status"] ?? '');
        $brand = validate_input($con, $data["brand"] ?? '');
        $gst = validate_input($con, $data["gst"] ?? '');
        $vat = validate_input($con, $data["vat"] ?? '');
        $email = validate_input($con, $data["email"] ?? '');
    
        // Check for duplicate entry
        $check_query = "SELECT * FROM tbl_login WHERE Usr_Name='$username'";
        $check_result = mysqli_query($con, $check_query);
        if (mysqli_num_rows($check_result) > 0) {
            send_error("Duplicate entry: User with this name already exists.");
        }
    
        // Insert data into the database
        $newdb = "pumpmanager_" . $username;
        
        $insert_query = "INSERT INTO tbl_login 
                            (Usr_Name, Usr_db, cName, Password, password2, Usr_Phone, Usr_Address, date, expDate, status, brand, Usr_Gst, Usr_Vat, Usr_email,clienthostname) 
                         VALUES 
                            ('$username', '$newdb', '$company_name', '$password', '$password2', '$contact_no', '$address', '$date', '$exp_date', 'Active', '$brand', '$gst', '$vat', '$email','$username')";
        
        if (mysqli_query($con, $insert_query)) {
            $lastinsert_id1 = mysqli_insert_id($con);
            $permission_query = "INSERT INTO tbl_permission (desi, Usr_id, cType, delete_flag) VALUES ('$username', '$lastinsert_id1', '18 HR', 0)";
            mysqli_query($con, $permission_query);
    
            // Replicate permissions
            $lastinsert_id = mysqli_insert_id($con);
            $result = mysqli_query($con, "SHOW COLUMNS FROM tbl_permission WHERE FIELD NOT IN ('id', 'Usr_id', 'desi', 'delete_flag', 'cType')");
            while ($row = mysqli_fetch_array($result)) {
                $result1 = mysqli_query($con, "SELECT * FROM tbl_permission WHERE cType='18 HR' AND desi='qiadmin' ORDER BY id ASC LIMIT 1");
                while ($row1 = mysqli_fetch_array($result1)) {
                    mysqli_query($con, "UPDATE tbl_permission SET " . $row['Field'] . "='" . $row1[$row['Field']] . "' WHERE id='$lastinsert_id'");
                }
            }
            
            //================================ create database ================================================
            $cpanelUsername = 'pumpmanager';
            $cpanelPassword = 'Moresh@2787';
            $databaseUser = "pumpmanager_test_user"; // database user 1
            $databasePassword = "Moresh@2787";
            
            $DB_HOST = 'localhost';
            $DB_USER = $databaseUser;
            $DB_PASS = $databasePassword;
            $database_new = $newdb;
            $database_old = 'pumpmanager_raw_db_structure';
            
            createDB($DB_HOST, $DB_USER, $DB_PASS, $cpanelUsername, $cpanelPassword, $database_new, $database_old);
           
        
            send_success("User added successfully.", ["Usr_id" => $lastinsert_id1]);
        } else {
            send_error("Database error: " . mysqli_error($con));
        }
    } else {
        send_error("Invalid request method.", 405);
    }
   
}else if ($method === 'PUT') {
    // Handle update request
    $data = json_decode(file_get_contents("php://input"), true);
    $id = validate_input($con, $data["client_id"] ?? '');
    
    if ($id == '') {
        send_error("Client ID is required for update.");
    }

    $username = validate_input($con, $data["username"] ?? '');
    $dbname = validate_input($con, $data["dbname"] ?? '');
    $company_name = validate_input($con, $data["company_name"] ?? '');
    $password = validate_input($con, $data["password"] ?? '');
    $password2 = validate_input($con, $data["password2"] ?? '');
    $contact_no = validate_input($con, $data["contact_no"] ?? '');
    $address = validate_input($con, $data["address"] ?? '');
    $status = validate_input($con, $data["status"] ?? '');
    $brand = validate_input($con, $data["brand"] ?? '');
    $gst = validate_input($con, $data["gst"] ?? '');
    $vat = validate_input($con, $data["vat"] ?? '');
    $email = validate_input($con, $data["email"] ?? '');

    $update_query = "UPDATE tbl_login SET 
                        Usr_Name='$username', 
                        cName='$company_name', 
                        Password='$password', 
                        password2='$password2', 
                        Usr_Phone='$contact_no', 
                        Usr_Address='$address', 
                        brand='$brand', 
                        Usr_Gst='$gst', 
                        Usr_Vat='$vat', 
                        Usr_email = '$email' 
                    WHERE Usr_id='$id'";
    
    if (mysqli_query($con, $update_query)) {
        $permission_update_query = "UPDATE tbl_permission SET desi='$username' WHERE Usr_id='$id'";
        mysqli_query($con, $permission_update_query);

        send_success("User updated successfully.");
    } else {
        send_error("Database error: " . mysqli_error($con));
    }
}else{
    $response = ["message" => 'Required fields are missing',"code" => 201];
    
    echo json_encode($response);
}
?>
