<?php

declare(strict_types=1);

ini_set('display_errors', '1');
error_reporting(E_ALL);

use System\Contracts\DatabaseInterface;

$app = require dirname(__DIR__) . '/bootstrap/app.php';

$database = $app->container()->make(
    DatabaseInterface::class
);

$pdo = $database->getConnection();

echo '<pre>';

echo "LOCATION IMPORTER\n";
echo "=================\n\n";

$csvFile = dirname(__DIR__)
    . '/database/data/south_sudan_locations.csv';

if (!file_exists($csvFile)) {
    exit(
        "CSV file not found:\n"
        . $csvFile
    );
}

$handle = fopen($csvFile, 'r');

if ($handle === false) {
    exit('Cannot open CSV file.');
}

$header = fgetcsv($handle);

if ($header === false) {
    exit('CSV header not found.');
}

$requiredColumns = [
    'state_code',
    'state_name',
    'administrative_type',
    'county_code',
    'county_name',
    'payam_code',
    'payam_name'
];

foreach ($requiredColumns as $column) {
    if (!in_array($column, $header, true)) {
        exit(
            'Missing CSV column: '
            . $column
        );
    }
}

$stateStatement = $pdo->prepare(
    'INSERT INTO states (
        state_code,
        state_name,
        administrative_type,
        status,
        created_at,
        updated_at
    )
    VALUES (?, ?, ?, 1, NOW(), NOW())
    ON DUPLICATE KEY UPDATE
        state_name = VALUES(state_name),
        administrative_type =
            VALUES(administrative_type),
        updated_at = NOW()'
);

$findStateStatement = $pdo->prepare(
    'SELECT id
     FROM states
     WHERE state_code = ?
     LIMIT 1'
);

$countyStatement = $pdo->prepare(
    'INSERT INTO counties (
        state_id,
        county_code,
        county_name,
        status,
        created_at,
        updated_at
    )
    VALUES (?, ?, ?, 1, NOW(), NOW())
    ON DUPLICATE KEY UPDATE
        state_id = VALUES(state_id),
        county_name = VALUES(county_name),
        updated_at = NOW()'
);

$findCountyStatement = $pdo->prepare(
    'SELECT id
     FROM counties
     WHERE county_code = ?
     LIMIT 1'
);

$payamStatement = $pdo->prepare(
    'INSERT INTO payams (
        county_id,
        payam_code,
        payam_name,
        status,
        created_at,
        updated_at
    )
    VALUES (?, ?, ?, 1, NOW(), NOW())
    ON DUPLICATE KEY UPDATE
        county_id = VALUES(county_id),
        payam_name = VALUES(payam_name),
        updated_at = NOW()'
);

$stateCount = 0;
$countyCount = 0;
$payamCount = 0;

$processedStates = [];
$processedCounties = [];

$pdo->beginTransaction();

try {

    while (
        ($row = fgetcsv($handle)) !== false
    ) {

        if (count($row) !== count($header)) {
            continue;
        }

        $data = array_combine(
            $header,
            $row
        );

        if ($data === false) {
            continue;
        }

        $stateCode = trim(
            $data['state_code']
        );

        $stateName = trim(
            $data['state_name']
        );

        $type = trim(
            $data['administrative_type']
        );

        $countyCode = trim(
            $data['county_code']
        );

        $countyName = trim(
            $data['county_name']
        );

        $payamCode = trim(
            $data['payam_code']
        );

        $payamName = trim(
            $data['payam_name']
        );

        if (
            $stateCode === ''
            || $stateName === ''
        ) {
            continue;
        }

        if (
            !isset(
                $processedStates[$stateCode]
            )
        ) {

            $stateStatement->execute([
                $stateCode,
                $stateName,
                $type !== ''
                    ? $type
                    : 'STATE'
            ]);

            $processedStates[$stateCode] =
                true;

            $stateCount++;
        }

        $findStateStatement->execute([
            $stateCode
        ]);

        $stateId = (int)
            $findStateStatement->fetchColumn();

        if (
            $countyCode === ''
            || $countyName === ''
        ) {
            continue;
        }

        if (
            !isset(
                $processedCounties[
                    $countyCode
                ]
            )
        ) {

            $countyStatement->execute([
                $stateId,
                $countyCode,
                $countyName
            ]);

            $processedCounties[
                $countyCode
            ] = true;

            $countyCount++;
        }

        $findCountyStatement->execute([
            $countyCode
        ]);

        $countyId = (int)
            $findCountyStatement
                ->fetchColumn();

        if (
            $payamCode === ''
            || $payamName === ''
        ) {
            continue;
        }

        $payamStatement->execute([
            $countyId,
            $payamCode,
            $payamName
        ]);

        $payamCount++;
    }

    $pdo->commit();

    fclose($handle);

    echo "IMPORT COMPLETE\n";
    echo "===============\n\n";

    echo "States processed: "
        . $stateCount
        . "\n";

    echo "Counties processed: "
        . $countyCount
        . "\n";

    echo "Payams processed: "
        . $payamCount
        . "\n\n";

    echo "Database totals:\n";

    echo "States: "
        . $pdo
            ->query(
                'SELECT COUNT(*) FROM states'
            )
            ->fetchColumn()
        . "\n";

    echo "Counties: "
        . $pdo
            ->query(
                'SELECT COUNT(*) FROM counties'
            )
            ->fetchColumn()
        . "\n";

    echo "Payams: "
        . $pdo
            ->query(
                'SELECT COUNT(*) FROM payams'
            )
            ->fetchColumn()
        . "\n";

} catch (Throwable $exception) {

    $pdo->rollBack();

    fclose($handle);

    echo "IMPORT FAILED\n\n";

    echo $exception->getMessage();
}

echo '</pre>';