<?php

declare(strict_types=1);

namespace DoctrineMigrations;

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

/**
 * Auto-generated Migration: Please modify to your needs!
 */
final class Version20231114134717 extends AbstractMigration
{
    public function getDescription() : string
    {
        return '';
    }

    public function up(Schema $schema) : void
    {
        $this->addSql('
        create view webapp.view_data_business_object as
select concat(\'i\', i.id) as fake_id,
       p.name            as platform,
       e.name            as experiment,
       b.number          as block,
       sb.number         as sub_block,
       null              as surfacic_unit_plot,
       up.number         as unit_plot,
       i.number          as individual,
       null::int         as experiment_target_id,
       null::int         as block_target_id,
       null::int         as sub_block_target_id,
       null::int         as surfacic_unit_plot_target_id,
       null::int         as unit_plot_target_id,
       i.id              as individual_target_id,
       i.dead            as dead,
       i.appeared        as appeared,
       i.disappeared     as disappeared,
       up.treatment_id   as treatment_id,
       i.x               as x,
       i.y               as y,
       i.identifier      as identifier
from webapp.individual i
         left outer join webapp.unit_plot up on up.id = i.unit_plot_id
         left outer join webapp.sub_block sb on up.sub_block_id = sb.id
         left outer join webapp.block b on up.block_id = b.id or sb.block_id = b.id
         left outer join webapp.experiment e on b.experiment_id = e.id
         left outer join webapp.platform p on p.id = e.platform_id
UNION ALL
select concat(\'up\', up.id) as fake_id,
       p.name              as platform,
       e.name              as experiment,
       b.number            as block,
       sb.number           as sub_block,
       null                as surfacic_unit_plot,
       up.number           as unit_plot,
       null                as individual,
       null::int           as experiment_target_id,
       null::int           as block_target_id,
       null::int           as sub_block_target_id,
       null::int           as surfacic_unit_plot_target_id,
       up.id               as unit_plot_target_id,
       null::int           as individual_target_id,
       null                as dead,
       null                as appeared,
       null                as disappeared,
       up.treatment_id     as treatment_id,
       null                as x,
       null                as y,
       null                as identifier
from webapp.unit_plot up
         left outer join webapp.sub_block sb on up.sub_block_id = sb.id
         left outer join webapp.block b on up.block_id = b.id or sb.block_id = b.id
         left outer join webapp.experiment e on b.experiment_id = e.id
         left outer join webapp.platform p on p.id = e.platform_id
UNION ALL
select concat(\'sup\', sup.id) as fake_id,
       p.name                as platform,
       e.name                as experiment,
       b.number              as block,
       sb.number             as sub_block,
       sup.number            as surfacic_unit_plot,
       null                  as unit_plot,
       null                  as individual,
       null::int             as experiment_target_id,
       null::int             as block_target_id,
       null::int             as sub_block_target_id,
       sup.id                as surfacic_unit_plot_target_id,
       null::int             as unit_plot_target_id,
       null::int             as individual_target_id,
       sup.dead              as dead,
       sup.appeared          as appeared,
       sup.disappeared       as disappeared,
       sup.treatment_id      as treatment_id,
       sup.x                 as x,
       sup.y                 as y,
       sup.identifier        as identifier
from webapp.surfacic_unit_plot sup
         left outer join webapp.sub_block sb on sup.sub_block_id = sb.id
         left outer join webapp.block b on sup.block_id = b.id or sb.block_id = b.id
         left outer join webapp.experiment e on b.experiment_id = e.id
         left outer join webapp.platform p on p.id = e.platform_id
UNION ALL
select concat(\'sb\', sb.id) as fake_id,
       p.name              as platform,
       e.name              as experiment,
       b.number            as block,
       sb.number           as sub_block,
       null                as surfacic_unit_plot,
       null                as unit_plot,
       null                as individual,
       null::int           as experiment_target_id,
       null::int           as block_target_id,
       sb.id               as sub_block_target_id,
       null::int           as surfacic_unit_plot_target_id,
       null::int           as unit_plot_target_id,
       null::int           as individual_target_id,
       null                as dead,
       null                as appeared,
       null                as disappeared,
       null                as treatment_id,
       null                as x,
       null                as y,
       null                as identifier
from webapp.sub_block sb
         left outer join webapp.block b on sb.block_id = b.id
         left outer join webapp.experiment e on b.experiment_id = e.id
         left outer join webapp.platform p on p.id = e.platform_id
UNION ALL
select concat(\'b\', b.id) as fake_id,
       p.name            as platform,
       e.name            as experiment,
       b.number          as block,
       null              as sub_block,
       null              as surfacic_unit_plot,
       null              as unit_plot,
       null              as individual,
       null::int         as experiment_target_id,
       b.id              as block_target_id,
       null::int         as sub_block_target_id,
       null::int         as surfacic_unit_plot_target_id,
       null::int         as unit_plot_target_id,
       null::int         as individual_target_id,
       null              as dead,
       null              as appeared,
       null              as disappeared,
       null              as treatment_id,
       null              as x,
       null              as y,
       null              as identifier
from webapp.block b
         left outer join webapp.experiment e on b.experiment_id = e.id
         left outer join webapp.platform p on p.id = e.platform_id
UNION ALL
select concat(\'e\', e.id) as fake_id,
       p.name            as platform,
       e.name            as experiment,
       null              as block,
       null              as sub_block,
       null              as surfacic_unit_plot,
       null              as unit_plot,
       null              as individual,
       e.id              as experiment_target_id,
       null::int         as block_target_id,
       null::int         as sub_block_target_id,
       null::int         as surfacic_unit_plot_target_id,
       null::int         as unit_plot_target_id,
       null::int         as individual_target_id,
       null              as dead,
       null              as appeared,
       null              as disappeared,
       null              as treatment_id,
       null              as x,
       null              as y,
       null              as identifier
from webapp.experiment e
         left outer join webapp.platform p on p.id = e.platform_id;
        ');

        $this->addSql('
        create view webapp.view_data_item as
select pd.id as project_data_id,
       s.id  as session_id,
       fm.id as field_measure_id,
       fm.experiment_target_id,
       fm.block_target_id,
       fm.sub_block_target_id,
       fm.surfacic_unit_plot_target_id,
       fm.unit_plot_target_id,
       fm.individual_target_id,
       fm.simple_variable_id,
       fm.generator_variable_id,
       fm.semi_automatic_variable_id,
       m.repetition,
       m.value,
       sc.code,
       m.timestamp,
       u.username
from webapp.session_data s
         inner join webapp.project_data pd on pd.id = s.project_data_id
         inner join webapp.field_measure fm on s.id = fm.session_id
         inner join webapp.measure m on fm.id = m.form_field_id
         left outer join webapp.state_code sc on sc.id = m.state_id
         left outer join shared.ado_user u on coalesce(s.user_id, pd.user_id) = u.id
        ');

    }

    public function down(Schema $schema) : void
    {

        $this->addSql('DROP VIEW webapp.view_data_item');
        $this->addSql('DROP VIEW webapp.view_data_business_object');
    }
}
