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

final class Version20250822123819 extends AbstractMigration
{
    public function getDescription(): string
    {
        return 'Add person identifier tables: chill_person_identifier_definition and chill_person_identifier with FKs to person and definition; create supporting sequences and indexes.';
    }

    public function up(Schema $schema): void
    {
        $this->addSql('CREATE SEQUENCE chill_person_identifier_id_seq INCREMENT BY 1 MINVALUE 1 START 1');
        $this->addSql('CREATE SEQUENCE chill_person_identifier_definition_id_seq INCREMENT BY 1 MINVALUE 1 START 1000');
        $this->addSql(
            <<<'SQL'
                CREATE TABLE chill_person_identifier (
                id INT NOT NULL,
                person_id INT NOT NULL,
                definition_id INT NOT NULL,
                value JSONB NOT NULL DEFAULT '[]'::jsonb,
                canonical TEXT NOT NULL DEFAULT '',
                PRIMARY KEY(id)
                )
                SQL
        );
        $this->addSql('CREATE INDEX IDX_BCA5A36B217BBB47 ON chill_person_identifier (person_id)');
        $this->addSql('CREATE INDEX IDX_BCA5A36BD11EA911 ON chill_person_identifier (definition_id)');
        $this->addSql(
            <<<'SQL'
                CREATE TABLE chill_person_identifier_definition (
                id INT NOT NULL,
                label JSON DEFAULT '[]' NOT NULL,
                engine VARCHAR(100) NOT NULL,
                is_searchable BOOLEAN DEFAULT false NOT NULL,
                is_editable_by_users BOOLEAN DEFAULT false NOT NULL,
                data JSONB DEFAULT '[]' NOT NULL,
                active BOOLEAN DEFAULT true NOT NULL,
                PRIMARY KEY(id))
                SQL
        );
        $this->addSql('ALTER TABLE chill_person_identifier ADD CONSTRAINT FK_BCA5A36B217BBB47 FOREIGN KEY (person_id) REFERENCES chill_person_person (id) ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE');
        $this->addSql('ALTER TABLE chill_person_identifier ADD CONSTRAINT FK_BCA5A36BD11EA911 FOREIGN KEY (definition_id) REFERENCES chill_person_identifier_definition (id) ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE');
    }

    public function down(Schema $schema): void
    {
        $this->addSql('DROP SEQUENCE chill_person_identifier_id_seq CASCADE');
        $this->addSql('DROP SEQUENCE chill_person_identifier_definition_id_seq CASCADE');
        $this->addSql('ALTER TABLE chill_person_identifier DROP CONSTRAINT FK_BCA5A36B217BBB47');
        $this->addSql('ALTER TABLE chill_person_identifier DROP CONSTRAINT FK_BCA5A36BD11EA911');
        $this->addSql('DROP TABLE chill_person_identifier');
        $this->addSql('DROP TABLE chill_person_identifier_definition');
    }
}
