<?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\Person;

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

/**
 * Auto-generated Migration: Please modify to your needs!
 */
final class Version20200422125935 extends AbstractMigration
{
    public function down(Schema $schema): void
    {
        $this->addSql('DROP FUNCTION public.get_last_address_isnoaddress(integer, date);');
        $this->addSql('DROP FUNCTION get_last_address(integer, date)');
        $this->addSql(
            <<<'SQL'
                CREATE OR REPLACE FUNCTION public.get_last_address (
                        pid integer,
                        before_date date)
                RETURNS TABLE(
                        person_id integer,
                	address_id integer,
                	streetaddress1 varchar(255),
                	streetaddress2 varchar(255),
                	validfrom date,
                	postcode_label varchar(255),
                	postcode_code varchar(100),
                	postcode_id integer,
                	country_name json,
                	country_code varchar(3),
                	country_id integer)
                        AS
                $BODY$
                SELECT
                    pid AS person_id,
                	chill_main_address.id AS address_id,
                	chill_main_address.streetaddress1,
                	chill_main_address.streetaddress2,
                	chill_main_address.validfrom,
                	chill_main_postal_code.label,
                	chill_main_postal_code.code,
                    chill_main_postal_code.id AS postal_code_id,
                	country.name,
                	country.countrycode,
                	country.id AS country_id
                FROM chill_main_address
                JOIN (
                	SELECT
                	chill_main_address.id AS address_id,
                	validfrom,
                	rank() OVER (PARTITION BY person_id ORDER BY validfrom DESC) as pos
                	FROM chill_person_persons_to_addresses
                	JOIN chill_main_address ON chill_person_persons_to_addresses.address_id = chill_main_address.id
                	WHERE person_id = pid
                	AND chill_main_address.validfrom <= before_date
                ) AS ranking ON ranking.address_id = chill_main_address.id
                JOIN chill_main_postal_code ON chill_main_address.postcode_id = chill_main_postal_code.id
                JOIN country ON chill_main_postal_code.country_id = country.id
                WHERE ranking.pos = 1
                $BODY$
                LANGUAGE sql VOLATILE
                COST 100;
                SQL
        );
    }

    public function up(Schema $schema): void
    {
        $this->addSql('DROP FUNCTION get_last_address(integer, date)');
        $this->addSql(
            <<<'SQL'
                CREATE OR REPLACE FUNCTION public.get_last_address (
                        pid integer,
                        before_date date)
                RETURNS TABLE(
                        person_id integer,
                	address_id integer,
                	streetaddress1 varchar(255),
                	streetaddress2 varchar(255),
                	validfrom date,
                	postcode_label varchar(255),
                	postcode_code varchar(100),
                	postcode_id integer,
                    isnoaddress boolean,
                	country_name json,
                	country_code varchar(3),
                	country_id integer)
                        AS
                $BODY$
                SELECT
                    pid AS person_id,
                	chill_main_address.id AS address_id,
                	chill_main_address.streetaddress1,
                	chill_main_address.streetaddress2,
                	chill_main_address.validfrom,
                	chill_main_postal_code.label,
                	chill_main_postal_code.code,
                    chill_main_postal_code.id AS postal_code_id,
                    chill_main_address.isnoaddress AS isnoaddress,
                	country.name,
                	country.countrycode,
                	country.id AS country_id
                FROM chill_main_address
                JOIN (
                	SELECT
                	chill_main_address.id AS address_id,
                	validfrom,
                	rank() OVER (PARTITION BY person_id ORDER BY validfrom DESC) as pos
                	FROM chill_person_persons_to_addresses
                	JOIN chill_main_address ON chill_person_persons_to_addresses.address_id = chill_main_address.id
                	WHERE person_id = pid
                	AND chill_main_address.validfrom <= before_date
                ) AS ranking ON ranking.address_id = chill_main_address.id
                JOIN chill_main_postal_code ON chill_main_address.postcode_id = chill_main_postal_code.id
                JOIN country ON chill_main_postal_code.country_id = country.id
                WHERE ranking.pos = 1
                $BODY$
                LANGUAGE sql VOLATILE
                COST 100;
                SQL
        );

        $this->addSql(
            <<<'SQL'
                CREATE OR REPLACE FUNCTION get_last_address_isnoaddress (
                	pid integer,
                	before_date date)
                RETURNS BOOL AS
                $BODY$
                SELECT isnoaddress FROM get_last_address(pid, before_date)
                $BODY$
                LANGUAGE sql volatile
                COST 100;
                SQL
        );
    }
}
