<?php

$projectName = "cleanup";
$projectKey = "ACL";
$commentText = "";


/// Get data from Crowdin
$curl = curl_init();
curl_setopt_array($curl, array(
    CURLOPT_RETURNTRANSFER => 1,
    CURLOPT_URL => 'https://api.crowdin.com/api/project/' . $projectName . '/status?key=43febe0d89685a8919eeb69d953a8e24&json',
));
$resp = curl_exec($curl);


$commentText.="<br><strong>CROWDIN report for " . $projectName . "</strong><br>";
$commentText.= "--------------------------------------------<br>";

$someArray = json_decode($resp, true);

foreach ($someArray as $key => $value) {
    if (($value["words"] - $value["words_translated"]) == 0) {
        $commentText.= $value["name"] . " is translated <strong>completely.</strong><br>";
    } else {
        $commentText.= "In " . $value["name"] . " language, <strong>" . ($value["words"] - $value["words_translated"]) . "</strong> words NOT translated<br>";
    }
}
$commentText.= "<br><a href=\"https://crowdin.com/project/" . $projectName . "\">Check project in CROWDIN for more informations</a>";




/// List of release issues
$curl = curl_init();

curl_setopt_array($curl, array(
    CURLOPT_USERPWD => 'cadek:BmPUTQxC8XA*',
    CURLOPT_RETURNTRANSFER => 1,
    CURLOPT_FOLLOWLOCATION => 1,
    CURLOPT_SSL_VERIFYPEER => 0,
    CURLOPT_SSL_VERIFYHOST => 0,
    CURLOPT_URL => 'https://butr.avast.com/rest/api/2/search?jql=project%20%3D%20' . $projectKey . '%20AND%20issuetype%20%3D%20Release',
));

$resp = curl_exec($curl);

$someArray = json_decode($resp, true);

foreach ($someArray['issues'] as $issue) {
    echo getStatus($issue['key']);
    if (trim(getStatus($issue['key'])) == "In QA Review") {
        echo "Ticket " .$issue['key']. " is in QA review<br>";
        /*if (!isInDb($issue['key'])) {
            echo "Ticket " .$issue['key']. " is not in DB<br>";
            echo "Posting comment";
            insertIntoDB($issue['key']);
        } else {
            echo "Ticket " .$issue['key']. " is IN DB<br>";
        }*/
    } else {
            echo "Ticket " .$issue['key']. " is NOT in QA review<br>";
    }
    echo "---------------<br>";
}

function insertIntoDB($ticketId) {
    $servername = "localhost";
    $username = "root";
    $password = "000000000X";
    $dbname = "ams5-sauron";

    $conn = mysqli_connect($servername, $username, $password, $dbname);
    if (!$conn) {
        die("Connection failed: " . mysqli_connect_error());
    }

    $sql = "INSERT INTO stringNotif (ticketKey) VALUES"
            . " (\"" . $ticketId . "\")";

    if (mysqli_query($conn, $sql)) {
        //echo "New record created successfully " . $ticketId. "<br>";
    } else {
        echo "Error: " . $sql . "<br>" . mysqli_error($conn);
    }

    mysqli_close($conn);
}

function isInDb($ticketId) {
    $servername = "localhost";
    $username = "root";
    $password = "000000000X";
    $dbname = "ams5-sauron";

    $conn = mysqli_connect($servername, $username, $password, $dbname);
    if (!$conn) {
        die("Connection failed: " . mysqli_connect_error());
    }

    $sql = "SELECT ticketId FROM stringNotif";

    if (mysqli_query($conn, $sql)) {
        echo "New record created successfully";
    } else {
        echo "Error: " . $sql . "<br>" . mysqli_error($conn);
    }

    $result = $conn->query($sql);
    if ($result->num_rows > 0) {
        return true;
    } else {
        return false;
    }

    mysqli_close($conn);
}

function getStatus($issueKey) {
    $curl = curl_init();

    curl_setopt_array($curl, array(
        CURLOPT_USERPWD => 'cadek:BmPUTQxC8XA*',
        CURLOPT_RETURNTRANSFER => 1,
        CURLOPT_FOLLOWLOCATION => 1,
        CURLOPT_SSL_VERIFYPEER => 0,
        CURLOPT_SSL_VERIFYHOST => 0,
        CURLOPT_URL => 'https://butr.avast.com/rest/api/2/issue/' . $issueKey . '?fields=status',
    ));

    $resp = curl_exec($curl);
    $someArray = json_decode($resp, true);

    foreach ($someArray['fields'] as $field) {
        return $field['name'] . "<br>\n";
    }

    curl_close($curl);
}