<?php

declare(strict_types=1);

/*
 * This file is part of contao-weinanlieferung-bundle.
 *
 * (c) vonRotenberg
 *
 * @license commercial
 */

namespace vonRotenberg\WeinanlieferungBundle\Migration;

use Contao\CoreBundle\Migration\AbstractMigration;
use Contao\CoreBundle\Migration\MigrationResult;
use Doctrine\DBAL\Connection;
use Doctrine\DBAL\Schema\Column; // only for type hints in comments

class ReservationUnitFieldsMigration extends AbstractMigration
{
    /** @var Connection */
    private $db;

    public function __construct(Connection $db)
    {
        $this->db = $db;
    }

    public function shouldRun(): bool
    {
        $schemaManager = $this->db->createSchemaManager();

        // If the database table itself does not exist we should do nothing
        if (!$schemaManager->tablesExist(['tl_vr_wa_reservation'])) {
            return false;
        }

        $columns = $schemaManager->listTableColumns('tl_vr_wa_reservation');

        $missingUnitId = !isset($columns['unit_id']);
        $missingUnitAmount = !isset($columns['unit_amount']);

        // If either column is missing, we should run
        if ($missingUnitId || $missingUnitAmount) {
            return true;
        }

        // If both columns are present, check if we need to backfill data (legacy records)
        // We assume that if there exists any row where unit_amount = 0 AND behaelter > 0, we need to backfill.
        $needsBackfill = (bool) $this->db
            ->executeQuery('SELECT 1 FROM tl_vr_wa_reservation WHERE (unit_amount IS NULL OR unit_amount = 0) AND behaelter > 0 LIMIT 1')
            ->fetchOne();

        return $needsBackfill;
    }

    public function run(): MigrationResult
    {
        $schemaManager = $this->db->createSchemaManager();

        if (!$schemaManager->tablesExist(['tl_vr_wa_reservation'])) {
            return $this->createResult(true, 'Table tl_vr_wa_reservation does not exist, nothing to do.');
        }

        $columns = $schemaManager->listTableColumns('tl_vr_wa_reservation');
        $statements = [];

        if (!isset($columns['unit_id'])) {
            // As defined in DCA: int(10) unsigned NOT NULL default '0'
            $statements[] = "ALTER TABLE tl_vr_wa_reservation ADD unit_id INT(10) UNSIGNED NOT NULL DEFAULT '0' AFTER behaelter";
        }

        if (!isset($columns['unit_amount'])) {
            // As defined in DCA: int(4) unsigned NOT NULL default 0
            $statements[] = "ALTER TABLE tl_vr_wa_reservation ADD unit_amount INT(4) UNSIGNED NOT NULL DEFAULT 0 AFTER unit_id";
        }

        foreach ($statements as $sql) {
            $this->db->executeStatement($sql);
        }

        // Backfill data for existing reservations: unit_amount = behaelter, unit_id = 0
        // Only update rows where unit_amount is NULL or 0 to avoid overwriting already migrated data.
        $updated = $this->db->executeStatement(
            'UPDATE tl_vr_wa_reservation SET unit_amount = behaelter, unit_id = 0 WHERE (unit_amount IS NULL OR unit_amount = 0)'
        );

        $message = [];
        if (!empty($statements)) {
            $message[] = 'Added missing columns: ' . implode('; ', $statements);
        }
        $message[] = 'Backfilled reservations (rows updated): ' . $updated;

        return $this->createResult(true, implode(' | ', $message));
    }
}