<?php
declare(strict_types=1);

use Phinx\Migration\AbstractMigration;

final class Task2401111514v2 extends AbstractMigration
{
    public function up(): void
    {
        $rsUsuarios = $this->fetchAll("
            SELECT 
                id
            FROM 
                sistema.usuarios
            WHERE 
                ds_nome_usuario IN (
                    'tais.silveira', 'gabriel.vasconcelos',
                    'karina.silva', 'henrique.alves',
                    'luciana.dellay', 'luana.beck', 'diego.cecagno',
                    'miguel.goulart', 'ana.madeira', 'karina.figueiro'
                );
        ");

        $rowTaxaCarIndex = $this->fetchRow("SELECT id FROM sistema.acao WHERE ds_action = 'index' AND ds_controller = 'taxacar'");
        $rowTaxaCarActivate = $this->fetchRow("SELECT id FROM sistema.acao WHERE ds_action = 'activate' AND ds_controller = 'taxacar'");
        $rowTaxaCarDelete = $this->fetchRow("SELECT id FROM sistema.acao WHERE ds_action = 'delete' AND ds_controller = 'taxacar'");
        $rowTaxaCarForm = $this->fetchRow("SELECT id FROM sistema.acao WHERE ds_action = 'form' AND ds_controller = 'taxacar'");
        $rowTaxaCarRelacionaCar = $this->fetchRow("SELECT id FROM sistema.acao WHERE ds_action = 'relacionacar' AND ds_controller = 'taxacar'");
        $rowRotinasImportar = $this->fetchRow("SELECT id FROM sistema.acao WHERE ds_action = 'importartaxacar' AND ds_controller = 'rotinas'");
        $rowRotinasTaxasCar = $this->fetchRow("SELECT id FROM sistema.acao WHERE ds_action = 'taxascar' AND ds_controller = 'rotinas'");
        $rowTaxaValor = $this->fetchRow("
            SELECT id FROM sistema.acao 
                WHERE 
                    ds_controller = 'taxavalor' 
                    AND id_parent = (SELECT id_parent FROM sistema.acao WHERE ds_action = 'activate' AND ds_controller = 'taxacar')
        ");
        
        foreach ($rsUsuarios as $usuario) {
            
            $this->execute(<<<SQL
                INSERT INTO sistema.usuarios_acao (id_usuario, id_acao) 
                    VALUES
                        ({$usuario['id']}, {$rowTaxaCarIndex['id']}),
                        ({$usuario['id']}, {$rowTaxaCarActivate['id']}),
                        ({$usuario['id']}, {$rowTaxaCarDelete['id']}),
                        ({$usuario['id']}, {$rowTaxaCarForm['id']}),
                        ({$usuario['id']}, {$rowTaxaCarRelacionaCar['id']}),
                        ({$usuario['id']}, {$rowRotinasTaxasCar['id']}),
                        ({$usuario['id']}, {$rowRotinasImportar['id']}),
                        ({$usuario['id']}, {$rowTaxaValor['id']});
SQL
);   
        }
    }

    public function down(): void
    {
        $rsUsuarios = $this->fetchAll("
            SELECT 
                id
            FROM 
                sistema.usuarios
            WHERE 
                ds_nome_usuario IN (
                    'tais.silveira', 'gabriel.vasconcelos',
                    'karina.silva', 'henrique.alves',
                    'luciana.dellay', 'luana.beck', 'diego.cecagno',
                    'miguel.goulart', 'ana.madeira', 'karina.figueiro'
                );
        ");

        $rowTaxaCarIndex = $this->fetchRow("SELECT id FROM sistema.acao WHERE ds_action = 'index' AND ds_controller = 'taxacar'");
        $rowTaxaCarActivate = $this->fetchRow("SELECT id FROM sistema.acao WHERE ds_action = 'activate' AND ds_controller = 'taxacar'");
        $rowTaxaCarDelete = $this->fetchRow("SELECT id FROM sistema.acao WHERE ds_action = 'delete' AND ds_controller = 'taxacar'");
        $rowTaxaCarForm = $this->fetchRow("SELECT id FROM sistema.acao WHERE ds_action = 'form' AND ds_controller = 'taxacar'");
        $rowTaxaCarRelacionaCar = $this->fetchRow("SELECT id FROM sistema.acao WHERE ds_action = 'relacionacar' AND ds_controller = 'taxacar'");
        $rowRotinasImportar = $this->fetchRow("SELECT id FROM sistema.acao WHERE ds_action = 'importartaxacar' AND ds_controller = 'rotinas'");
        $rowRotinasTaxasCar = $this->fetchRow("SELECT id FROM sistema.acao WHERE ds_action = 'taxascar' AND ds_controller = 'rotinas'");
        $rowTaxaValor = $this->fetchRow("
            SELECT id FROM sistema.acao 
                WHERE 
                    ds_controller = 'taxavalor' 
                    AND id_parent = (SELECT id_parent FROM sistema.acao WHERE ds_action = 'activate' AND ds_controller = 'taxacar')
        ");

        foreach ($rsUsuarios as $usuario) {
            $this->execute(<<<SQL
                DELETE FROM sistema.usuarios_acao
                    WHERE
                        (id_usuario = {$usuario['id']} AND id_acao = {$rowTaxaCarIndex['id']}) OR
                        (id_usuario = {$usuario['id']} AND id_acao = {$rowTaxaCarActivate['id']}) OR
                        (id_usuario = {$usuario['id']} AND id_acao = {$rowTaxaCarDelete['id']}) OR
                        (id_usuario = {$usuario['id']} AND id_acao = {$rowTaxaCarForm['id']}) OR
                        (id_usuario = {$usuario['id']} AND id_acao = {$rowTaxaCarRelacionaCar['id']}) OR
                        (id_usuario = {$usuario['id']} AND id_acao = {$rowRotinasTaxasCar['id']}) OR
                        (id_usuario = {$usuario['id']} AND id_acao = {$rowRotinasImportar['id']}) OR
                        (id_usuario = {$usuario['id']} AND id_acao = {$rowTaxaValor['id']});
SQL
);   
        }
    }
}