<?php

declare(strict_types=1);

/*
 * Chill is a software for social workers
 *
 * For the full copyright and license information, please view
 * the LICENSE file that was distributed with this source code.
 */

namespace Chill\Migrations\Main;

use Doctrine\DBAL\Schema\Schema;
use Doctrine\Migrations\AbstractMigration;

final class Version20250417135712 extends AbstractMigration
{
    public function getDescription(): string
    {
        return 'Add the role CHILL_MAIN_COMPOSE_EXPORT to the existing permissions groups which contains already a permission related to list or stat';
    }

    public function up(Schema $schema): void
    {
        $this->addSql(
            <<<'SQL'
            CREATE TEMPORARY TABLE to_create AS (
                        SELECT DISTINCT permissionsgroup_rolescope.permissionsgroup_id, 'CHILL_MAIN_COMPOSE_EXPORT' AS role
                        FROM permissionsgroup_rolescope
                        JOIN public.role_scopes rs on permissionsgroup_rolescope.rolescope_id = rs.id
                        WHERE role LIKE '%STATS%' or role LIKE '%LIST%'
                    )
            SQL
        );

        $this->addSql(
            <<<'SQL'
            INSERT INTO role_scopes(id, scope_id, role)
                        SELECT nextval('role_scopes_id_seq'), null, 'CHILL_MAIN_COMPOSE_EXPORT'
                        WHERE NOT EXISTS (SELECT 1 FROM role_scopes s WHERE role like 'CHILL_MAIN_COMPOSE_EXPORT')
            SQL
        );

        $this->addSql('ALTER TABLE to_create ADD COLUMN rolescope_id INT');

        $this->addSql(
            <<<'SQL'
            UPDATE to_create SET rolescope_id = (
                    SELECT id FROM role_scopes
                    WHERE to_create.role = role_scopes.role)
            SQL
        );

        $this->addSql(
            <<<'SQL'
            INSERT INTO permissionsgroup_rolescope (permissionsgroup_id, rolescope_id)
                    SELECT to_create.permissionsgroup_id, to_create.rolescope_id FROM to_create
            SQL
        );
    }

    public function down(Schema $schema): void
    {
        $this->throwIrreversibleMigrationException();
    }
}
