<?php

/**
 *
 * @author hen
 *
 */

namespace QUI\System\Console\Tools;

use Doctrine\DBAL\Exception;
use QUI;

use function array_filter;
use function count;
use function explode;
use function implode;
use function is_numeric;
use function trim;
use function var_dump;

use const OPT_DIR;

/**
 * MailQueue Console Manager
 */
class MigrationV2 extends QUI\System\Console\Tool
{
    /**
     * Constructor
     */
    public function __construct()
    {
        $this->setName('quiqqer:migration-v2')
            ->setDescription('Migration QUIQQER V1 to V2');
    }

    /**
     * @throws Exception|QUI\Database\Exception
     */
    public function execute(): void
    {
        // messages
        $this->writeLn('- Update messages table');
        QUI::getDataBaseConnection()->executeStatement(
            'ALTER TABLE `' . QUI::getDBTableName('messages') . '` CHANGE `uid` `uid` VARCHAR(50);'
        );

        // session
        $this->writeLn('- Update session table');
        QUI::getDataBaseConnection()->executeStatement(
            'ALTER TABLE `' . QUI::getDBTableName('sessions') . '` CHANGE `uid` `uid` VARCHAR(50);'
        );


        $this->users();
        $this->groups();
        $this->groupsInUsers();
        $this->projectSites();
        $this->media();
        $this->permissions();
        $this->workspaces();
        $this->loginLog();

        $this->writeLn('- Migrate root user and root group');

        $Config = QUI::getConfig('etc/conf.ini.php');
        $rootUser = $Config->getValue('globals', 'rootuser');
        $rootGroup = $Config->getValue('globals', 'root');

        try {
            $Config->setValue('globals', 'rootuser', QUI::getUsers()->get($rootUser)->getUUID());
        } catch (QUI\Exception) {
        }

        try {
            $Config->setValue('globals', 'root', QUI::getGroups()->get($rootGroup)->getUUID());
        } catch (QUI\Exception) {
        }

        $Config->save();

        QUI::getEvents()->fireEvent('quiqqerMigrationV2', [$this]);


        // migrate databases to innodb
        try {
            $this->writeLn('- Migrate database to MyISAM');

            $conn = QUI::getDataBaseConnection();
            $dbname = QUI::conf('db', 'database');

            // Alle MyISAM-Tabellen abrufen
            $sql = "
            SELECT table_name 
            FROM information_schema.tables 
            WHERE 
                table_schema = :dbname AND engine = 'MyISAM'
            ";

            $stmt = $conn->prepare($sql);
            $stmt->bindValue('dbname', $dbname);

            $result = $stmt->executeQuery();
            $tables = $result->fetchAllAssociative();

            // Speicher-Engine jeder Tabelle ändern
            foreach ($tables as $table) {
                try {
                    $tableName = $table['table_name'] ?? $table['TABLE_NAME'];
                    $conn->executeStatement("ALTER TABLE `$tableName` ENGINE=InnoDB;");
                    $this->writeLn('-> Converted ' . $tableName . ' to InnoDB');
                } catch (\Exception $exception) {
                    $this->writeLn('Error at table ' . $tableName, 'red');
                    $this->writeLn($exception->getMessage(), 'red');
                    $this->resetColor();
                }
            }

            $this->writeLn('-> Conversion complete');
        } catch (Exception $e) {
            $this->writeLn('An error occurred: ' . $e->getMessage());
        }

        $this->writeLn('Migration complete!', 'green');
        $this->resetColor();
    }

    public function users(): void
    {
        $this->writeLn('- Update users table');

        QUI\Users\Install::user();

        $DataBase = QUI::getDataBase();
        $userTable = QUI\Users\Manager::table();

        // uuid extreme indexes patch
        $Stmt = $DataBase->getPDO()->prepare(
            "SHOW INDEXES FROM `$userTable`
            WHERE 
                non_unique = 0 AND Key_name != 'PRIMARY';"
        );

        $Stmt->execute();

        $columns = $Stmt->fetchAll();
        $dropSql = [];

        foreach ($columns as $column) {
            if (str_starts_with($column['Key_name'], 'uuid_')) {
                $dropSql[] = "ALTER TABLE `users` DROP INDEX `{$column['Key_name']}`;";
            }
        }

        if (!empty($dropSql)) {
            try {
                // foreach because of PDO::MYSQL_ATTR_USE_BUFFERED_QUERY
                foreach ($dropSql as $sql) {
                    $Stmt = $DataBase->getPDO()->prepare($sql);
                    $Stmt->execute();
                }
            } catch (\Exception $Exception) {
                QUI\System\Log::writeRecursive($dropSql);
                QUI\System\Log::writeException($Exception);
            }
        }

        // users with no uuid
        $usersWithoutUuid = QUI::getDataBase()->fetch([
            'from' => $userTable,
            'where' => [
                'uuid' => ''
            ]
        ]);

        foreach ($usersWithoutUuid as $entry) {
            $DataBase->update(
                $userTable,
                ['uuid' => QUI\Utils\Uuid::get()],
                ['id' => $entry['id']]
            );
        }

        $DataBase->table()->setUniqueColumns($userTable, 'uuid');

        // addresses
        $this->writeLn('- Migrate users addresses');

        $tableAddresses = QUI::getUsers()->tableAddress();
        $setAddressUuidColumnToUnique = false;

        if (!$DataBase->table()->existColumnInTable($tableAddresses, 'uuid')) {
            $DataBase->table()->addColumn(
                $tableAddresses,
                ['uuid' => 'VARCHAR(50) NOT NULL']
            );

            $setAddressUuidColumnToUnique = true;
        }

        if (!$DataBase->table()->existColumnInTable($tableAddresses, 'userUuid')) {
            $DataBase->table()->addColumn(
                $tableAddresses,
                ['userUuid' => 'VARCHAR(50) NOT NULL']
            );
        }

        $usersAddressColumn = $DataBase->table()->getColumn($userTable, 'address');

        if (!str_contains($usersAddressColumn['Type'], 'varchar')) {
            $sql = "ALTER TABLE `$userTable` MODIFY `address` VARCHAR(50) NOT NULL";
            $DataBase->execSQL($sql);
        }

        $addressesWithoutUuid = QUI::getDataBase()->fetch([
            'select' => ['id'],
            'from' => $tableAddresses,
            'where' => [
                'uuid' => ''
            ]
        ]);

        $this->writeLn('-- Found addresses without UUID: ' . count($addressesWithoutUuid));
        $this->writeLn('-- Start migration ...');

        foreach ($addressesWithoutUuid as $entry) {
            $addressUuid = QUI\Utils\Uuid::get();

            $DataBase->update(
                $tableAddresses,
                ['uuid' => $addressUuid],
                ['id' => $entry['id']]
            );
        }

        // MIGRATE DEFAULT ADDRESS
        $users = QUI::getDataBase()->fetch([
            'from' => $userTable
        ]);

        foreach ($users as $user) {
            $standardAddress = $user['address'];

            if (is_numeric($standardAddress)) {
                $addressData = QUI::getDataBase()->fetch([
                    'from' => $tableAddresses,
                    'where' => [
                        'id' => $standardAddress
                    ]
                ]);

                if (!count($addressData)) {
                    continue;
                }

                // Update references in users table
                $DataBase->update(
                    $userTable,
                    ['address' => $addressData[0]['uuid']],
                    ['id' => $user['id']]
                );
            }
        }


        if ($setAddressUuidColumnToUnique) {
            $DataBase->table()->setUniqueColumns($tableAddresses, 'uuid');
        }

        $addressesWithoutUserUuid = QUI::getDataBase()->fetch([
            'select' => ['id', 'uid'],
            'from' => $tableAddresses,
            'where' => [
                'userUuid' => ''
            ]
        ]);

        $this->writeLn('-- Found addresses without user UUID: ' . count($addressesWithoutUserUuid));
        $this->writeLn('-- Start migration ...');

        foreach ($addressesWithoutUserUuid as $entry) {
            $result = $DataBase->fetch([
                'select' => ['uuid'],
                'from' => $userTable,
                'where' => [
                    'id' => $entry['uid']
                ],
                'limit' => 1
            ]);

            if (empty($result)) {
                $this->writeLn(
                    "-> Found orphaned address ID #{$entry['id']}. User #{$entry['uid']}" . " referenced by address does not exist.",
                    'yellow'
                );
                $this->resetColor();
                continue;
            }

            // Update user uuid
            $DataBase->update(
                $tableAddresses,
                ['userUuid' => $result[0]['uuid']],
                ['id' => $entry['id']]
            );
        }
    }

    public function groups(): void
    {
        $this->writeLn('- Migrate groups table');
        QUI\Users\Install::groups();


        // migrate group parents
        $Root = QUI::getGroups()->get(QUI::conf('globals', 'root'));
        $rootUUID = $Root->getUUID();
        $groupTable = QUI\Groups\Manager::table();

        $result = QUI::getDataBase()->fetch([
            'from' => $groupTable
        ]);

        foreach ($result as $entry) {
            $groupUUID = $entry['uuid'];

            if ($groupUUID == 0 || $groupUUID == 1 || $groupUUID == $rootUUID) {
                continue;
            }

            $parent = $entry['parent'];

            try {
                if ($parent == 0) {
                    QUI::getDataBase()->update(
                        $groupTable,
                        ['parent' => $rootUUID],
                        ['id' => $entry['id']]
                    );
                } else {
                    QUI::getDataBase()->update(
                        $groupTable,
                        ['parent' => QUI::getGroups()->get($parent)->getUUID()],
                        ['id' => $entry['id']]
                    );
                }
            } catch (QUI\Exception $exception) {
                $this->writeLn($exception->getMessage(), 'red');
                $this->resetColor();
            }
        }
    }

    public function groupsInUsers(): void
    {
        $this->writeLn('- Migrate groups in users');
        $table = QUI\Users\Manager::table();

        $result = QUI::getDataBase()->fetch([
            'from' => $table
        ]);

        foreach ($result as $entry) {
            $userGroups = $entry['usergroup'];
            $newGroups = [];

            $userGroups = trim($userGroups, ',');
            $userGroups = explode(',', $userGroups);

            foreach ($userGroups as $groupId) {
                try {
                    $newGroups[] = QUI::getGroups()->get($groupId)->getUUID();
                } catch (QUI\Exception) {
                    $newGroups[] = $groupId;
                }
            }

            try {
                QUI::getDataBase()->update(
                    $table,
                    ['usergroup' => ',' . implode(',', $newGroups) . ','],
                    ['id' => $entry['id']]
                );
            } catch (QUI\Exception) {
            }
        }
    }

    public function projectSites(): void
    {
        $this->writeLn('- Migrate project & sites');
        $projects = QUI::getProjectManager()->getProjects(true);

        /* @var $Project QUI\Projects\Project */
        foreach ($projects as $Project) {
            $Project->setup([
                'executePackagesSetup' => false
            ]);

            $languages = $Project->getLanguages();
            $name = $Project->getName();

            foreach ($languages as $language) {
                $table = QUI::getProject($name, $language)->table();
                $sites = QUI::getDataBase()->fetch([
                    'from' => $table
                ]);

                foreach ($sites as $site) {
                    $cUser = $site['c_user'];
                    $eUser = $site['e_user'];

                    if (is_numeric($cUser)) {
                        $cUser = $this->getUserHash($cUser);
                    }

                    if (is_numeric($eUser)) {
                        $eUser = $this->getUserHash($eUser);
                    }

                    QUI::getDataBase()->update(
                        $table,
                        [
                            'c_user' => $cUser,
                            'e_user' => $eUser,
                        ],
                        ['id' => $site['id']]
                    );
                }
            }
        }
    }

    public function media(): void
    {
        $this->writeLn('- Migrate media');
        $projects = QUI::getProjectManager()->getProjects(true);

        /* @var $Project QUI\Projects\Project */
        foreach ($projects as $Project) {
            $Media = $Project->getMedia();
            $table = $Media->getTable();

            $files = QUI::getDataBase()->fetch([
                'from' => $table
            ]);

            foreach ($files as $file) {
                $cUser = $file['c_user'];
                $eUser = $file['e_user'];

                if (is_numeric($cUser)) {
                    $cUser = $this->getUserHash($cUser);
                }

                if (is_numeric($eUser)) {
                    $eUser = $this->getUserHash($eUser);
                }

                QUI::getDataBase()->update(
                    $table,
                    [
                        'c_user' => $cUser,
                        'e_user' => $eUser,
                    ],
                    ['id' => $file['id']]
                );
            }
        }
    }

    /**
     * @throws Exception
     * @throws QUI\Database\Exception
     */
    public function permissions(): void
    {
        $this->writeLn('- Migrate permissions');

        $table2Users = QUI::getDBTableName('permissions2users');
        $table2Groups = QUI::getDBTableName('permissions2groups');

        QUI::getDataBaseConnection()->executeStatement(
            'ALTER TABLE `' . $table2Users . '` CHANGE `user_id` `user_id` VARCHAR(50) NOT NULL DEFAULT \'0\';'
        );

        QUI::getDataBaseConnection()->executeStatement(
            'ALTER TABLE `' . $table2Groups . '` CHANGE `group_id` `group_id` VARCHAR(50) NOT NULL DEFAULT \'0\';'
        );


        $permissions = QUI::getDataBase()->fetch([
            'from' => $table2Users
        ]);

        foreach ($permissions as $entry) {
            if (!is_numeric($entry['user_id'])) {
                continue;
            }

            try {
                $userUUID = QUI::getUsers()->get($entry['user_id'])->getUUID();
            } catch (QUI\Exception) {
                // nutzer existiert nicht, kann als permission gelöscht werden
                QUI::getDataBaseConnection()->delete($table2Users, [
                    'user_id' => $entry['user_id']
                ]);

                continue;
            }

            QUI::getDataBaseConnection()->insert($table2Users, [
                'user_id' => $userUUID,
                'permissions' => $entry['permissions']
            ]);

            QUI::getDataBaseConnection()->delete($table2Users, [
                'user_id' => $entry['user_id']
            ]);
        }


        $permissions = QUI::getDataBase()->fetch([
            'from' => $table2Groups
        ]);

        foreach ($permissions as $entry) {
            if (!is_numeric($entry['group_id'])) {
                continue;
            }

            try {
                $groupUUID = QUI::getGroups()->get($entry['group_id'])->getUUID();
            } catch (\Exception) {
                // gruppe existiert nicht, kann als permission gelöscht werden

                QUI::getDataBaseConnection()->delete($table2Groups, [
                    'group_id' => $entry['group_id']
                ]);
                continue;
            }

            if ($groupUUID == $entry['group_id']) {
                continue;
            }

            try {
                QUI::getDataBaseConnection()->insert($table2Groups, [
                    'group_id' => $groupUUID,
                    'permissions' => $entry['permissions']
                ]);

                QUI::getDataBaseConnection()->delete($table2Groups, [
                    'group_id' => $entry['group_id']
                ]);
            } catch (\Exception) {
            }
        }
    }

    /**
     * @throws Exception
     * @throws QUI\Database\Exception
     */
    public function workspaces(): void
    {
        $this->writeLn('- Migrate workspaces');
        $this->writeLn('> Cleanup workspaces');

        $workspaceTable = QUI\Workspace\Manager::table();
        $queryBuilder = QUI::getQueryBuilder();

        $result = $queryBuilder->select('*')->from($workspaceTable)->executeQuery();
        $count = 0;
        $progressInterval = 50;

        while ($entry = $result->fetchAssociative()) {
            $time = date('H:i:s');

            try {
                $User = QUI::getUsers()->get($entry['uid']);
            } catch (\Exception) {
                if (++$count % $progressInterval === 0) {
                    $this->writeLn(">> $time [$count] ");
                }

                // user not found -> workspace can be deleted
                QUI::getDataBaseConnection()->delete($workspaceTable, [
                    'id' => $entry['id']
                ]);

                continue;
            }

            if ($User->canUseBackend()) {
                continue;
            }

            if (++$count % $progressInterval === 0) {
                $this->writeLn(">> $time [$count] ");
            }

            QUI::getDataBaseConnection()->delete($workspaceTable, [
                'id' => $entry['id']
            ]);
        }

        $this->writeLn('> Upgrade workspaces');
        $table = QUI::getDBTableName('users_workspaces');

        QUI::getDataBaseConnection()->executeStatement(
            'ALTER TABLE `' . $table . '` CHANGE `uid` `uid` VARCHAR(50) NOT NULL;'
        );

        $workspaces = QUI::getDataBase()->fetch([
            'from' => $table
        ]);

        foreach ($workspaces as $workspace) {
            try {
                $uuid = QUI::getUsers()->get($workspace['uid'])->getUUID();
            } catch (QUI\Exception) {
                QUI::getDataBaseConnection()->delete($table, [
                    'id' => $workspace['id']
                ]);
                continue;
            }

            QUI::getDataBase()->update(
                $table,
                ['uid' => $uuid],
                ['id' => $workspace['id']]
            );
        }
    }

    /**
     * @throws Exception
     */
    public function loginLog(): void
    {
        $this->writeLn('- Migrate login log table');
        QUI::getDataBaseConnection()->executeStatement(
            'ALTER TABLE `' . QUI::getDBTableName('login_log') . '` CHANGE `uid` `uid` VARCHAR(50) NOT NULL;'
        );
    }

    protected function getUserHash(int | string $userId): string | int
    {
        try {
            return QUI::getUsers()->get($userId)->getUUID();
        } catch (QUI\Exception) {
            return $userId;
        }
    }
}
