<?php
include "identifica.php.cripto";

$mysqli = new mysqli("localhost", $username, $pass, $nome_base_dados);

if ($mysqli->connect_error) {
    die("Conexão falhou: " . $mysqli->connect_error);
}
echo "Conexão estabelecida com sucesso.\n";

$jsonData = json_decode(file_get_contents("v1.39-2023-12-20-ror-data.json"), true);
echo "Arquivo JSON carregado.\n";

$entityCounter = 0;
$typeCounter = 0;
$aliasCounter = 0;
$acronymCounter = 0;
$labelCounter = 0;
$ipAddressCounter = 0;
$relationshipCounter = 0;
$addressCounter = 0;
$externalIdCounter = 0;

foreach ($jsonData as $entity) {
    $stmt = $mysqli->prepare("INSERT INTO entities (id_chave_entity, name, status, wikipedia_url, email_address, established, country_name, country_code) VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
    $stmt->bind_param("sssssiis", $entity['id'], $entity['name'], $entity['status'], $entity['wikipedia_url'], $entity['email_address'], $entity['established'], $entity['country']['country_name'], $entity['country']['country_code']);
    $stmt->execute();
    $entityCounter++;

    if ($entityCounter % 100 == 0) {
        echo "Entidades inseridas: $entityCounter.\n";
    }

    if (isset($entity['types']) && is_array($entity['types'])) {
        foreach ($entity['types'] as $type) {
            $stmt = $mysqli->prepare("INSERT INTO types (id_entity, type) VALUES (?, ?)");
            $stmt->bind_param("ss", $entity['id'], $type);
            $stmt->execute();
            $typeCounter++;

            if ($typeCounter % 100 == 0) {
                echo "Tipos inseridos: $typeCounter.\n";
            }
        }
    }

    if (isset($entity['aliases']) && is_array($entity['aliases'])) {
        foreach ($entity['aliases'] as $alias) {
            $stmt = $mysqli->prepare("INSERT INTO aliases (id_entity, alias) VALUES (?, ?)");
            $stmt->bind_param("ss", $entity['id'], $alias);
            $stmt->execute();
            $aliasCounter++;

            if ($aliasCounter % 100 == 0) {
                echo "Aliases inseridos: $aliasCounter.\n";
            }
        }
    }

    if (isset($entity['acronyms']) && is_array($entity['acronyms'])) {
        foreach ($entity['acronyms'] as $acronym) {
            $stmt = $mysqli->prepare("INSERT INTO acronyms (id_entity, acronym) VALUES (?, ?)");
            $stmt->bind_param("ss", $entity['id'], $acronym);
            $stmt->execute();
            $acronymCounter++;

            if ($acronymCounter % 100 == 0) {
                echo "Acrônimos inseridos: $acronymCounter.\n";
            }
        }
    }

    if (isset($entity['labels']) && is_array($entity['labels'])) {
        foreach ($entity['labels'] as $label) {
            $stmt = $mysqli->prepare("INSERT INTO labels (id_entity, label) VALUES (?, ?)");
            $stmt->bind_param("ss", $entity['id'], $label);
            $stmt->execute();
            $labelCounter++;

            if ($labelCounter % 100 == 0) {
                echo "Rótulos inseridos: $labelCounter.\n";
            }
        }
    }

    if (isset($entity['ip_addresses']) && is_array($entity['ip_addresses'])) {
        foreach ($entity['ip_addresses'] as $ip_address) {
            $stmt = $mysqli->prepare("INSERT INTO ip_addresses (id_entity, ip_address) VALUES (?, ?)");
            $stmt->bind_param("ss", $entity['id'], $ip_address);
            $stmt->execute();
            $ipAddressCounter++;

            if ($ipAddressCounter % 100 == 0) {
                echo "Endereços IP inseridos: $ipAddressCounter.\n";
            }
        }
    }

    if (isset($entity['relationships']) && is_array($entity['relationships'])) {
        foreach ($entity['relationships'] as $relationship) {
            $stmt = $mysqli->prepare("INSERT INTO relationships (id_entity, id_related_entity, type) VALUES (?, ?, ?)");
            $stmt->bind_param("sss", $entity['id'], $relationship['id'], $relationship['type']);
            $stmt->execute();
            $relationshipCounter++;

            if ($relationshipCounter % 100 == 0) {
                echo "Relacionamentos inseridos: $relationshipCounter.\n";
            }
        }
    }

    if (isset($entity['addresses']) && is_array($entity['addresses'])) {
        foreach ($entity['addresses'] as $address) {
            $address_text = json_encode($address);
            $stmt = $mysqli->prepare("INSERT INTO addresses (id_entity, address) VALUES (?, ?)");
            $stmt->bind_param("ss", $entity['id'], $address_text);
            $stmt->execute();
            $addressCounter++;

            if ($addressCounter % 100 == 0) {
                echo "Endereços inseridos: $addressCounter.\n";
            }
        }
    }

    if (isset($entity['external_ids']) && is_array($entity['external_ids'])) {
        foreach ($entity['external_ids'] as $external_id_type => $external_ids) {
            foreach ($external_ids['all'] as $id) {
                $stmt = $mysqli->prepare("INSERT INTO external_ids (id_entity, external_id_type, external_id) VALUES (?, ?, ?)");
                $stmt->bind_param("sss", $entity['id'], $external_id_type, $id);
                $stmt->execute();
                $externalIdCounter++;

                if ($externalIdCounter % 100 == 0) {
                    echo "IDs externos inseridos: $externalIdCounter.\n";
                }
            }
        }
    }
}

$mysqli->close();
echo "Todos os dados inseridos com sucesso e conexão encerrada.\n";
?>