<?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 Version20260213132759 extends AbstractMigration
{
    public function getDescription(): string
    {
        return 'Create view_article and populate article table';
    }

    public function up(Schema $schema): void
    {
        // Создаем представление view_article, если его еще нет
        $this->addSql('CREATE OR REPLACE VIEW public.view_article
AS WITH article_data AS (
         SELECT el.id AS article_id,
            TRIM(TRAILING \'-\'::text FROM el.code) AS group_code,
            el.id,
            el.name,
            el.preview_picture,
            f.subdir,
            f.file_name,
            el.active,
            el.iblock_id,
            el.iblock_section_id,
            el.code,
            el.preview_text,
            el.detail_text,
            el.timestamp_x,
            vap_doctors.value AS doctors,
            vap_services.value AS services
           FROM b_iblock_element el
             JOIN b_file f ON f.id = el.preview_picture
             LEFT JOIN view_article_props vap_doctors ON el.id = vap_doctors.id AND vap_doctors.code = \'LINK_STAFF\'::text
             LEFT JOIN view_article_props vap_services ON el.id = vap_services.id AND vap_services.code = \'LINK_SERVICES\'::text
          WHERE el.iblock_id = ANY (ARRAY[69, 70, 71, 149, 179, 231])
        ), grouped_articles AS (
         SELECT d.group_code,
            COALESCE(max(d.id), NULL::integer) AS id,
            COALESCE(NULLIF(TRIM(BOTH FROM max(d.name)), \'\'::text), NULL::text) AS name,
            COALESCE(max(
                CASE
                    WHEN d.preview_picture IS NOT NULL THEN concat_ws(\'/\'::text, COALESCE(\'/upload\'::text, \'\'::text), COALESCE(d.subdir, \'\'::text), COALESCE(d.file_name, \'\'::text))
                    ELSE NULL::text
                END), NULL::text) AS preview_picture,
            COALESCE(
                CASE
                    WHEN max(
                    CASE
                        WHEN d.active = true THEN 1
                        ELSE 0
                    END) = 1 THEN true
                    ELSE false
                END, NULL::boolean) AS active,
                CASE
                    WHEN count(d.doctors) FILTER (WHERE d.doctors IS NOT NULL AND TRIM(BOTH FROM d.doctors) <> \'\'::text) > 0 THEN jsonb_agg(DISTINCT TRIM(BOTH FROM d.doctors)) FILTER (WHERE d.doctors IS NOT NULL AND TRIM(BOTH FROM d.doctors) <> \'\'::text)
                    ELSE NULL::jsonb
                END AS doctors,
                CASE
                    WHEN count(d.services) FILTER (WHERE d.services IS NOT NULL AND TRIM(BOTH FROM d.services) <> \'\'::text) > 0 THEN jsonb_agg(DISTINCT TRIM(BOTH FROM d.services)) FILTER (WHERE d.services IS NOT NULL AND TRIM(BOTH FROM d.services) <> \'\'::text)
                    ELSE NULL::jsonb
                END AS services,
            COALESCE(max(
                CASE d.iblock_id
                    WHEN 69 THEN 91
                    WHEN 149 THEN 91
                    WHEN 179 THEN 91
                    WHEN 70 THEN 92
                    WHEN 71 THEN 93
                    WHEN 231 THEN 94
                    ELSE d.iblock_id
                END), NULL::integer) AS region_id,
            COALESCE(max(d.code), NULL::text) AS alias,
            COALESCE(max(d.preview_text), NULL::text) AS anons,
            COALESCE(max(d.detail_text), NULL::text) AS content,
            COALESCE(max(d.timestamp_x), NULL::timestamp without time zone) AS update_at
           FROM article_data d
          GROUP BY d.group_code
        )
 SELECT ga.id,
    ga.name,
    ga.preview_picture,
    ga.active,
    ga.doctors,
    ga.services,
    ga.region_id,
    ga.alias,
    ga.anons,
    ga.content,
    ga.update_at
   FROM grouped_articles ga
  ORDER BY ga.id');

        // Наполняем таблицу article данными из представления
        $this->addSql('INSERT INTO article (id, name, preview_picture, active, doctors, services, region_id, alias, anons, content, update_at)
            SELECT id, name, preview_picture, active, doctors, services, region_id, alias, anons, content, update_at
            FROM view_article');
    }

    public function down(Schema $schema): void
    {
        $this->addSql('TRUNCATE TABLE article');
        $this->addSql('DROP VIEW IF EXISTS public.view_article');
    }
}