<?php

require_once "../common/db.php";

$xmlPath = __DIR__ . "/../DataSource/snipCode.xml";

if (!file_exists($xmlPath)) {

    die("XML non trovato\n");
}

$xml = simplexml_load_file($xmlPath);

if (!$xml) {

    die("Errore apertura XML\n");
}


// ========================================
// HELPERS
// ========================================

function attr($node, $name)
{
    $attrs = $node->attributes();

    return isset($attrs[$name])

        ? (string)$attrs[$name]

        : null;
}


// ========================================
// FIX DUPLICATI
// ========================================

$idMap = [

    12 => 103,

    13 => 104,

    14 => 105,

    21 => 106

];

foreach ($xml->item as $item) {

    $id = intval(

        attr(

            $item,

            "snipCodeIdKey"

        ) ?? -1
    );

    $cat = attr(

        $item,

        "category"

    );

    if ($id == 12 && $cat == "Reverse Shells") {

        $item["snipCodeIdKey"] = "103";
    }

    if ($id == 13 && $cat == "Reverse Shells") {

        $item["snipCodeIdKey"] = "104";
    }

    if ($id == 14 && $cat == "Reverse Shells") {

        $item["snipCodeIdKey"] = "105";
    }

    if ($id == 21 && $cat == "Enumerazione Iniziale") {

        $item["snipCodeIdKey"] = "106";
    }
}


// ========================================
// CLEAN TABLES
// ========================================

$db->query(

    "SET FOREIGN_KEY_CHECKS=0"

);

$db->query(

    "TRUNCATE TABLE snip_code_relations"

);

$db->query(

    "TRUNCATE TABLE snip_categories"

);

$db->query(

    "TRUNCATE TABLE snip_codes"

);

$db->query(

    "SET FOREIGN_KEY_CHECKS=1"

);


// ========================================
// IMPORT CATEGORIES
// ========================================

foreach (

    $xml->itemAccessory

    as

    $itemAccessory

) {

    $list =

        attr(

            $itemAccessory,

            "categoryList"

        );

    if (!$list) {

        continue;
    }

    $categories =

        explode(

            ",",

            $list

        );

    $order = 0;

    $stmt =

        $db->prepare(

            "

            INSERT INTO

            snip_categories

            (

                name,

                sort_order

            )

            VALUES

            (

                ?,?

            )

            "

        );

    foreach (

        $categories

        as

        $category

    ) {

        $category =

            trim(

                $category

            );

        if (

            $category == ""

        ) {

            continue;
        }

        $stmt->bind_param(

            "si",

            $category,

            $order

        );

        $stmt->execute();

        $order++;
    }

    break;
}


// ========================================
// IMPORT SNIPS
// ========================================

$relatedCache = [];

$count = 0;

$stmt =

    $db->prepare(

        "

        INSERT INTO

        snip_codes

        (

            id,

            category,

            language,

            description,

            code,

            order_in_cat,

            is_main,

            pen_element_related_id

        )

        VALUES

        (

            ?,?,?,?,?,?,?,?

        )

        "

    );

foreach (

    $xml->item

    as

    $item

) {

    $id =

        attr(

            $item,

            "snipCodeIdKey"

        );

    if ($id === null) {

        continue;
    }

    $id = intval($id);

    $category =

        attr(

            $item,

            "category"

        ) ?? "";

    $language =

        attr(

            $item,

            "snipCodeTypeKey"

        ) ?? "";

    $description =

        attr(

            $item,

            "description"

        );

    $code =

        attr(

            $item,

            "string"

        ) ?? "";

    $order =

        intval(

            attr(

                $item,

                "orderInCatKey"

            ) ?? 0

        );

    $isMain =

        attr(

            $item,

            "snipCodeIsMainKey"

        ) === "true"

        ? 1

        : 0;

    $penElement =

        attr(

            $item,

            "penElementRelatedIdKey"

        );

    $penElement =

        $penElement === null

        ? null

        : intval(

            $penElement

        );


    $stmt->bind_param(

        "issssiii",

        $id,

        $category,

        $language,

        $description,

        $code,

        $order,

        $isMain,

        $penElement

    );

    $stmt->execute();

    $related =

        attr(

            $item,

            "snipCodeRelatedIdsKey"

        );

    if (

        $related

        &&

        trim($related) != ""

    ) {

        $relatedCache[$id] =

            explode(

                ",",

                $related

            );
    }

    $count++;
}


// ========================================
// IMPORT RELATIONS
// ========================================

$relationCount = 0;

$stmtInsert =

    $db->prepare(

        "

        INSERT INTO

        snip_code_relations

        (

            snip_id,

            related_snip_id

        )

        VALUES

        (

            ?,?

        )

        "

    );

$stmtExists =

    $db->prepare(

        "

        SELECT id

        FROM snip_codes

        WHERE id=?

        "

    );

foreach (

    $relatedCache

    as

    $snipId => $list

) {

    foreach (

        $list

        as

        $relatedId

    ) {

        $relatedId =

            intval(

                trim(

                    $relatedId

                )

            );

        // fix duplicati

        if (

            isset(

                $idMap[$relatedId]

            )

        ) {

            $relatedId =

                $idMap[$relatedId];
        }

        if (

            $relatedId <= 0

        ) {

            continue;
        }

        // esiste?

        $stmtExists->bind_param(

            "i",

            $relatedId

        );

        $stmtExists->execute();

        $res =

            $stmtExists->get_result();

        if (

            $res->num_rows == 0

        ) {

            echo

                "Ignorata relazione "

                . $snipId

                . " -> "

                . $relatedId

                . "\n";

            continue;
        }

        $stmtInsert->bind_param(

            "ii",

            $snipId,

            $relatedId

        );

        $stmtInsert->execute();

        $relationCount++;
    }
}
// ========================================

echo "\n";

echo "Import completato\n";

echo "Snip: "

    . $count

    . "\n";

echo "Categorie: "

    . $db->query(

        "SELECT COUNT(*) c FROM snip_categories"

    )->fetch_assoc()["c"]

    . "\n";

echo "Relazioni: "

    . $relationCount

    . "\n";

echo "\n";