3710 - old

DO $migrate_house_doubles$

DECLARE
    r record;

    houses text[];

    v_fias_code varchar(36);
    url_decision_guid varchar(36);

    table_timestamp text;

    i integer := 0;

BEGIN

    SELECT translate(current_date::text, '-', '') || '_' || translate(split_part(current_time::text, '.', 1), ':', '') INTO table_timestamp;
    RAISE NOTICE 'Savepoints tables marker = %', table_timestamp;
    EXECUTE format('CREATE TABLE IF NOT EXISTS savepoints.hcsint_40716_crp_decision_header' || '_' || table_timestamp ||
                    ' AS SELECT * FROM crpsm.crp_decision_header WHERE 1=2');

    FOR r IN(
        WITH decisions AS(
            SELECT
                dch.*,
                dch.house_guid AS fias_code,
                -- выбор СФФКР и замена владельца специального счета трактуются как один вид
                CASE
                    WHEN dch.decision_type_code = '7' THEN '1'
                    ELSE dch.decision_type_code
                END AS decision_type
            FROM
                crpsm.crp_decision_header dch
            WHERE
                dch.status = 'PUBLISHED'
                AND dch.om_protocol_guid IS NULL
        ),
        mapping AS(
            SELECT
                *
            FROM
                dblink('dbname=hcsnsidb host=pgf-nsi user=hcs_nsi_rw password=hcs_nsi_rw',
                    'SELECT hm.houseguid_double, hm.houseguid_actual FROM nsism.nsi_house_mapping hm WHERE hm.houseguid_double IN(SELECT unnest(string_to_array('''
                    || (SELECT string_agg(fias_code, ' ') FROM decisions)::TEXT || ''', '' '')))'
                ) AS m(
                    not_actual_guid VARCHAR(36),
                    actual_guid VARCHAR(36)
                )
        ), grouped AS (SELECT
            COUNT( decisions.fias_code ) AS doubles,
            string_agg(decisions.fias_code || ':'|| decisions.guid, ', ' ORDER BY decisions.fias_code) AS fias_to_url_guid_map,
            mapping.actual_guid AS actual_fias_code
        FROM
            decisions
        INNER JOIN mapping ON
            decisions.fias_code = mapping.not_actual_guid OR decisions.fias_code = mapping.actual_guid
        GROUP BY
            mapping.actual_guid,
            decisions.decision_type,
            decisions.effective_date
        ), grouped_filtered AS (SELECT
            *
        FROM
            grouped
        WHERE
            doubles = 1 AND POSITION(actual_fias_code IN fias_to_url_guid_map) = 0
        )
        SELECT * FROM grouped_filtered
        INNER JOIN
            -- получаем идентификаторы регионов одним запросом через dblink
            (   SELECT
                    *
                FROM
                    dblink('dbname=hcsnsidb host=pgf-nsi user=hcs_nsi_rw password=hcs_nsi_rw',
                        'DROP TABLE IF EXISTS hcsint_40716_tmp_address_store;
                        CREATE TEMPORARY TABLE hcsint_40716_tmp_address_store (
                            house_guid CHARACTER VARYING,
                            region_guid CHARACTER VARYING
                        );
                        DO $$
                        DECLARE
                            house_guids_list CHARACTER VARYING[] := ' || ( SELECT quote_literal(array_agg(DISTINCT actual_fias_code)) FROM grouped_filtered ) || ';
                            rh CHARACTER VARYING;
                        BEGIN
                            FOREACH rh IN ARRAY house_guids_list
                            LOOP
                                INSERT INTO hcsint_40716_tmp_address_store SELECT rh, fah.* FROM nsism.func_find_region_aoguid_by_houseguid(rh) as fah;
                            END LOOP;
                        END$$;
                        SELECT * FROM hcsint_40716_tmp_address_store;'
                    ) AS frg (
                        nsi_fias_code VARCHAR(36),
                        nsi_region_guid VARCHAR(36)
                    )
            ) fias_region_mapping
        ON grouped_filtered.actual_fias_code = fias_region_mapping.nsi_fias_code
    ) LOOP
        houses = string_to_array(r.fias_to_url_guid_map, ', ');

        RAISE NOTICE '===================================================================================================';

        url_decision_guid = (string_to_array(houses[1], ':'))[2];
        v_fias_code = (string_to_array(houses[1], ':'))[1];
        i := i + 1;

        IF v_fias_code = r.actual_fias_code THEN
            RAISE NOTICE 'Skip decision_guid=%, fias_code=%, actual=%', url_decision_guid, v_fias_code, r.actual_fias_code;
            CONTINUE;
        END IF;

        -- вставка нового дома в таблицу адресов, если его там еще нет
        IF NOT EXISTS (SELECT 1 FROM crpsm.crp_fias_address WHERE house_guid = r.actual_fias_code) THEN
            RAISE NOTICE 'Add fias_house with code=%, region_guid=%', r.actual_fias_code, r.nsi_region_guid;
            INSERT INTO crpsm.crp_fias_address (house_guid, region_guid) VALUES (r.actual_fias_code, r.nsi_region_guid);
        END IF;

        RAISE NOTICE 'Update Decision decision_guid=% set fias_code=%', url_decision_guid, r.actual_fias_code;
        EXECUTE ('INSERT INTO savepoints.hcsint_40716_crp_decision_header_' || table_timestamp
                || ' SELECT * FROM crpsm.crp_decision_header WHERE guid = $1') USING url_decision_guid;
        UPDATE crpsm.crp_decision_header SET house_guid = r.actual_fias_code WHERE guid = url_decision_guid;
    END LOOP;

    RAISE NOTICE 'Count of updated decisions = %', i;

END;
$migrate_house_doubles$ LANGUAGE plpgsql;