3710 - final

-- Создаем временную таблицу - все решения, подлежащие анализу
DROP TABLE IF EXISTS tmp_decisions;
CREATE TEMPORARY TABLE tmp_decisions AS
SELECT
    dch.guid AS guid,
    dch.house_guid AS fias_code,
    -- выбор СФФКР и замена владельца специального счета трактуются как один вид
    CASE
        WHEN dch.decision_type_code = '7' THEN '1'
        ELSE dch.decision_type_code
    END AS decision_type,
    CASE
        WHEN dch.om_protocol_guid IS NULL THEN FALSE
        ELSE TRUE
    END AS protocol
FROM
    crpsm.crp_decision_header dch
WHERE
    dch.status = 'PUBLISHED';

ANALYZE tmp_decisions;

-- Создаём временную таблицу - таблица маппинга
DROP TABLE IF EXISTS tmp_mapping;
CREATE TEMPORARY TABLE tmp_mapping AS
SELECT
    actual_guid,
    not_actual_guid
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'
    ) AS m(
        not_actual_guid VARCHAR(36),
        actual_guid VARCHAR(36)
    );

ANALYZE tmp_mapping;

-- Создаём временную таблицу - таблица решений с дубликатами домов
DROP TABLE IF EXISTS tmp_grouped;
CREATE TEMPORARY TABLE tmp_grouped AS
SELECT
    MAX(decisions.guid) AS decision_guid,
    mapping.actual_guid AS actual_fias_code,
    MAX(mapping.not_actual_guid) AS not_actual_fias_code
FROM
    tmp_decisions decisions
    INNER JOIN tmp_mapping mapping ON decisions.fias_code = mapping.not_actual_guid
GROUP BY
    mapping.actual_guid,
    decisions.decision_type
HAVING
    COUNT(decisions.fias_code) = 1
    AND mapping.actual_guid != MAX(mapping.not_actual_guid)
    AND COUNT(CASE WHEN protocol='true' THEN 1 END) = 0;

ANALYZE tmp_grouped;

-- Вставка новых домов в таблицу адресов, если их там еще нет
INSERT INTO crpsm.crp_fias_address (house_guid, region_guid)
SELECT
    DISTINCT
    tmp_grouped.actual_fias_code,
    fha2.region_guid
FROM
    tmp_grouped
    LEFT JOIN crpsm.crp_fias_address fha ON fha.house_guid = tmp_grouped.actual_fias_code
    LEFT JOIN crpsm.crp_fias_address fha2 ON fha2.house_guid = tmp_grouped.not_actual_fias_code
WHERE
    fha.house_guid IS NULL;

-- Создание и заполнение таблицы в savepoints
CREATE TABLE IF NOT EXISTS savepoints.hcsint_40716_crp_decision_header AS
    SELECT
        *
    FROM
        crpsm.crp_decision_header
    WHERE
        guid IN (   SELECT
                        tmp_grouped.decision_guid
                    FROM
                        tmp_grouped
                );

-- Замена дублей гуидов домов на актуальные для найденных решений
UPDATE
    crpsm.crp_decision_header
SET house_guid = gr.actual_fias_code
FROM
    tmp_grouped gr
WHERE
    guid = gr.decision_guid;