<?php

// phpcs:disable PSR1.Files.SideEffects,Generic.Files.LineLength.TooLong

/*
 * This script generates sales and purchase agreements for the latest delivered and invoiced lots.
 * Used to easily generate agreements for testing purposes and updating tickets in Gitlab.
 * Files are stored in the `examples/pdf` directory and can be uploaded to Gitlab when the `--gitlab-token` option is provided.
 *
 * Make sure you move the agreement routes in backoffice out of the `auth` middleware in the `routes/web.php` file.
 *
 * Usage:
 *    php generate-agreements.php
 *      [--create-markdown-table] Create a markdown table with links to the generated PDFs.
 *      [--gitlab-token="GITLAB_TOKEN"] The Gitlab token to upload the PDFs to Gitlab. When not provided the PDFs are stored locally.
 *      [--gitlab-url="GITLAB_URL"] The Gitlab URL to upload the PDFs to. Defaults to `https://git.dealerdirect.io/dd/sd/uploads`.
 *      [--with-checklists] Include checklists in the generated agreements.
 */

use Cloudinary\Cloudinary;
use Dealerdirect\Agreements\Factories\PurchaseAgreementFactory;
use Dealerdirect\Agreements\Factories\SalesAgreementFactory;
use Dealerdirect\Agreements\Utils\Translator;
use Dealerdirect\Generic\Category\Site;
use Dealerdirect\Generic\Category\VehicleTypeCarBodyType;
use Doctrine\DBAL\ArrayParameterType;
use Doctrine\DBAL\Configuration;
use Doctrine\DBAL\Connection;
use Doctrine\DBAL\DriverManager;
use Doctrine\DBAL\ParameterType;
use Illuminate\Filesystem\FilesystemAdapter;
use League\Flysystem\Filesystem;
use League\Flysystem\Local\LocalFilesystemAdapter;
use MaddHatter\MarkdownTable\Builder;

require_once dirname(__DIR__) . '/vendor/autoload.php';

$options = getopt('', [
    'gitlab-token::',
    'gitlab-url::',
    'with-checklists::',
    'create-markdown-table',
]);

$gitlabToken = $options['gitlab-token'] ?? null;
$createMarkdownTable = isset($options['create-markdown-table']);
$uploadUrl = $options['gitlab-url'] ?? 'https://git.dealerdirect.io/api/v4/projects/154/uploads';
$withChecklists = isset($options['with-checklists']);

$storage = getStorage('/tmp/verkoopovereenkomsten');

$connection = DriverManager::getConnection([
    'dbname' => 'dealer01_ddmain',
    'user' => 'vagrant',
    'password' => 'vagrant',
    'host' => 'aio.test',
    'driver' => 'pdo_mysql',
    'charset' => 'utf8mb4',
], new Configuration());

$cloudinary = new Cloudinary([
    'cloud' => [
        'cloud_name' => 'dealerdirect',
        'api_key' => '744898468352785',
        'api_secret' => 'N5lsVBg-W7OqGgvK4hsrqsHW0CU',
        'secure' => true,
        'secure_distribution' => 'images.dealerdirect.eu',
    ],
]);

$siteReferences = [
    Site::REF_CAR_NL,
    Site::REF_CAR_BE_NL,
    Site::REF_CAR_MARKTPLAATS_NL,
    Site::REF_MOTO_NL,
    Site::REF_MOTO_BE,
    Site::REF_MOTO_DE,
    Site::REF_SCOOTER_NL,
    Site::REF_BICYCLE_NL,
];

$generated = [];
foreach ($siteReferences as $siteReference) {
    $label = referenceToLabel($siteReference);
    echo "Processing site reference: {$label} ($siteReference)\n";

    // Reset the translator for each site reference
    Translator::create($siteReference);

    [$rows, $table, $surveyId] = match ($siteReference) {
        Site::REF_CAR_NL,
        Site::REF_CAR_BE_NL,
        Site::REF_CAR_MARKTPLAATS_NL => [
            carQuery($connection, $siteReference),
            'dealer01_ddmain.verzamel_base',
            61,
        ],

        Site::REF_MOTO_NL,
        Site::REF_MOTO_BE,
        Site::REF_MOTO_DE,
        Site::REF_SCOOTER_NL,
        Site::REF_BICYCLE_NL => [
            motorQuery($connection, $siteReference),
            'dealer01_ddmoto.BO_Motorfiets',
            41,
        ],
    };

    if (Site::REF_CAR_NL === $siteReference) {
        $rows[] = [
            'lot_id' => 12662491,
            'deliver_id' => 1315941,
            'extra_data' => null,
        ];
    }

    if (!is_dir(__DIR__ . '/pdf')) {
        mkdir(__DIR__ . '/pdf');
    }

    foreach ($rows as $row) {
        if ($withChecklists) {
            addChecklistToLot($connection, $table, $row, $surveyId, $siteReference);
        }

        $model = Dealerdirect\Agreements\Factory::getModelByReference($siteReference, $connection, $row['deliver_id']);
        $files = [
            'sales' => createSalesAgreement(
                Dealerdirect\Agreements\Factory::forSalesAgreement($storage, $model)
            ),
            'purchase' => createPurchaseAgreement(
                Dealerdirect\Agreements\Factory::forPurchaseAgreement($storage, $model, $cloudinary)
            ),
        ];

        $generated[] = [
            'label' => $label,
            'lot_id' => $row['lot_id'],
            'files' => $files,
        ];
    }

    echo "Done processing site reference: {$label} ($siteReference)\n\n";
}

if ($createMarkdownTable) {
    $headers = ['Label', 'Verkoopovereenkomst', 'Aankoopovereenkomst'];
    $rows = fn ($row) => [
        createLink($row['files']['sales'], "{$row['label']}_{$row['lot_id']}_sales-agreement.pdf"),
        createLink($row['files']['purchase'], "{$row['label']}_{$row['lot_id']}_purchase-agreement.pdf"),
    ];

    $content = (new Builder())
        ->headers(array_values($headers))
        ->rows(array_map(
            fn ($row) => array_merge([
                sprintf('%s (%d)', implode(' ', array_map(
                    fn ($word) => ucfirst($word),
                    explode('-', $row['label'])
                )), $row['lot_id']),
            ], $rows($row)),
            $generated
        ))
        ->render()
    ;

    file_put_contents(__DIR__ . '/pdf/agreements.md', $content);

    echo "\nMarkdown table created in examples/pdf/agreements.md\n";
}

echo "Done!\n";


/**
 * @param array<string, mixed> $row
 */
function addChecklistToLot(Connection $connection, string $table, array $row, int $surveyId, int $siteReference): void
{
    $extraData = json_decode($row['extra_data'] ?? '{}', true, 512, JSON_THROW_ON_ERROR);
    if (!empty($extraData['checklist_id']) || Site::REF_BICYCLE_NL === $siteReference) {
        return;
    }

    $resultLength = 61 === $surveyId ? 1000 : 800;
    $checklistId = $connection->fetchOne(
        <<<SQL
            SELECT id
            FROM dealer01_ddmain.survey_results
            WHERE survey_id = :surveyId
                AND char_length(result) > :resultLength
            ORDER BY RAND()
            LIMIT 1
            SQL,
        compact('surveyId', 'resultLength')
    );

    if (!$checklistId) {
        echo "No checklist found for surveyId: {$surveyId}, lotId: {$row['lot_id']}, reference: {$siteReference}\n";

        return;
    }

    echo "Adding checklist for lot: {$row['lot_id']}, checklistId: {$checklistId}, reference {$siteReference}\n";
    $extraData['checklist_id'] = $checklistId;
    $connection->update(
        $table,
        ['extra_data' => json_encode($extraData, JSON_THROW_ON_ERROR)],
        ['id' => $row['lot_id']]
    );
}

function createLink(string $path, string $filename): string
{
    global $uploadUrl, $gitlabToken;

    if ($gitlabToken) {
        $path = realpath($path);
        if (!$path) {
            throw new RuntimeException("File not found: {$path}");
        }

        $response = `curl --request POST --header "PRIVATE-TOKEN: {$gitlabToken}" --form "file=@{$path}" "{$uploadUrl}"`;

        return json_decode($response, true)['markdown'];
    }

    return sprintf('[%s](%s)', $filename, $path);
}

function createPurchaseAgreement(
    PurchaseAgreementFactory $purchaseAgreementFactory
): string {
    $purchaseAgreementFactory->delete();

    $path = __DIR__ . sprintf(
        '/pdf/%s_%d_purchase-agreement.pdf',
        referenceToLabel($purchaseAgreementFactory->data->siteReference),
        $purchaseAgreementFactory->data->lotId,
    );

    file_put_contents($path, $purchaseAgreementFactory->show());

    return $path;
}

function createSalesAgreement(
    SalesAgreementFactory $salesAgreementFactory,
): string {
    $lotId = $salesAgreementFactory->data->lotId;
    $siteReference = $salesAgreementFactory->data->siteReference;
    $salesAgreementFactory->delete();

    $label = referenceToLabel($siteReference);
    $path = __DIR__ . "/pdf/{$label}_{$lotId}_sales-agreement.pdf";

    file_put_contents($path, $salesAgreementFactory->show());

    return $path;
}

function getStorage(string $path): FilesystemAdapter
{
    if (!is_dir($path)) {
        mkdir($path, 0777, true);
    }

    $localFileSystem = new LocalFilesystemAdapter($path);
    $fileSystem = new Filesystem($localFileSystem);

    return new FilesystemAdapter($fileSystem, $localFileSystem);
}

function referenceToLabel(int $reference): string
{
    return match ($reference) {
        Site::REF_CAR_NL => 'car-nl',
        Site::REF_CAR_BE_NL => 'car-be',
        Site::REF_CAR_MARKTPLAATS_NL => 'marktplaats-nl',
        Site::REF_MOTO_NL => 'motor-nl',
        Site::REF_MOTO_BE => 'motor-be',
        Site::REF_MOTO_DE => 'motor-de',
        Site::REF_SCOOTER_NL => 'scooter-nl',
        Site::REF_BICYCLE_NL => 'bicycle-nl',

        default => throw new InvalidArgumentException("Unknown site reference: {$reference}"),
    };
}

/**
 * @return array<int, array<string, mixed>>
 */
function motorQuery(Connection $connection, int $reference): array
{
    $where = '';
    if (Site::REF_BICYCLE_NL === $reference) {
        $where = 'AND lot.kmstand != 0 AND lot.bouwjaar IS NOT NULL';
    }

    return $connection->fetchAllAssociative(
        <<<SQL
            SELECT
                delivery.id AS deliver_id,
                lot.id AS lot_id,
                lot.extra_data
            FROM dealer01_ddmoto.BO_Verkopen_NL delivery
                INNER JOIN dealer01_ddmoto.BO_Motorfiets lot
                    ON delivery.kavelref = lot.id
            WHERE delivery.delivered = 'delivered'
                AND delivery.invoiced_at IS NOT NULL
                AND lot.reference = :reference
                $where
            ORDER BY delivery.id DESC,
                -- JSON_CONTAINS(lot.extra_data, JSON_QUOTE('checklist_id'), '$.extra_data') DESC,
                lot.opmerkingenklant DESC,
                lot.con_opmerkingen DESC
            LIMIT 1
            SQL,
        ['reference' => $reference]
    );
}

/**
 * @return array<int, array<string, mixed>>
 */
function carQuery(Connection $connection, int $reference): array
{
    $table = match ($reference) {
        Site::REF_CAR_BE_NL => 'dealer01_ddmain.verkopen_levering_be',
        default => 'dealer01_ddmain.verkopen_levering',
    };

    return $connection->fetchAllAssociative(
        <<<SQL
            SELECT
                delivery.id AS deliver_id,
                delivery.kavelref AS lot_id,
                lot.extra_data
            FROM $table AS delivery
                LEFT JOIN dealer01_ddmain.verzamel_base AS lot
                    ON delivery.kavelref = lot.id
            WHERE delivery.delivered = 'delivered'
                AND delivery.invoiced_at IS NOT NULL
                AND lot.reference = :reference
                AND lot.carrosserie NOT IN (:recreationalTypes)
            ORDER BY delivery.id DESC
                -- JSON_CONTAINS(lot.extra_data, JSON_QUOTE('checklist_id'), '$.extra_data') DESC,
                -- lot.opmerkingklant DESC,
                -- lot.con_opmerkingen DESC
            LIMIT 1
            SQL,
        [
            'reference' => $reference,
            'recreationalTypes' => [VehicleTypeCarBodyType::CAMPER, VehicleTypeCarBodyType::CARAVAN],
        ],
        [
            'reference' => ParameterType::INTEGER,
            'recreationalTypes' => ArrayParameterType::INTEGER,
        ]
    );
}
