-- Формируем список решений собственников по протоколу
-- Обогащаем следующими данными:
-- 1. Порядковый номер в рамках совместной собственности и вопроса.
-- 2. Порядковый номер в рамках номера права и вопроса.
-- 3. Площадь с учетом доли.
DROP TABLE IF EXISTS tmp_decision;
CREATE TEMPORARY TABLE tmp_decision AS
SELECT
    vq.guid AS question_guid,
    vq.question_number,
    vmd.member_decision,
    vs.*,
    ROUND(CASE vs.right_type_code
        WHEN '001001000000' THEN vs.total_square
        WHEN '001002000000' THEN vs.total_square * COALESCE(vs.size_num, 1) / COALESCE(vs.size_denom, 1)
        WHEN '001003000000' THEN vs.total_square
    END, 4) AS adjusted_area,
    ROW_NUMBER() OVER (PARTITION BY vq.guid, vs.share_guid ORDER BY vs.total_square) AS join_ownership_number,
    ROW_NUMBER() OVER (PARTITION BY vq.guid, vs.registration_number, vs.registration_date, vs.premise_number ORDER BY vs.total_square) AS reg_order_number
FROM
    vtsm.vt_voting_version vv
    INNER JOIN vtsm.vt_question vq ON vq.voting_version_guid = vv.guid
    INNER JOIN vtsm.vt_member_decision vmd ON vmd.question_guid = vq.guid
    INNER JOIN vtsm.vt_member_decision_shares vmds ON vmds.voting_member_decision_guid = vmd.guid
    INNER JOIN vtsm.vt_shares vs ON vs.guid = vmds.share_guid
WHERE
    vv.guid='9961f908-9e7f-4957-8f83-cffe3c3a3dbe';

DROP TABLE IF EXISTS tmp_egrp;
CREATE TEMPORARY TABLE tmp_egrp AS
SELECT
    *
FROM
    dblink('host=pgf-rr dbname=hcsrrdb user=hcs_rr_rw password=hcs_rr_rw', 'SELECT open_registration_number, MAX(open_registration_date), MAX(close_registration_number), MAX(close_registration_date), MAX(share_numerator), MAX(share_denominator)
    FROM rrsm.rr_egrp_realties_rights_info_v WHERE open_registration_number IN (' || (SELECT STRING_AGG(DISTINCT '''' || registration_number || '''', ', ') FROM tmp_decision) || ') GROUP BY open_registration_number')
    AS
    (
        open_registration_number varchar(45),
        open_registration_date timestamp without time zone,
        close_registration_number varchar(45),
        close_registration_date timestamp without time zone,
        size_num_egrp numeric(22),
        size_denom_egrp numeric(22)
    );

DROP TABLE IF EXISTS tmp_decision_with_egrp;
CREATE TEMPORARY TABLE tmp_decision_with_egrp AS
SELECT
    tmpd.*,
    tmpe.*,
    (SELECT protocol_date FROM vtsm.vt_voting_version WHERE guid='9961f908-9e7f-4957-8f83-cffe3c3a3dbe') BETWEEN
         (COALESCE(tmpe.open_registration_date, '1900-01-01'::date))
         AND COALESCE(tmpe.close_registration_date, '2099-01-01'::date) AS actual_right
FROM
    tmp_decision tmpd
    LEFT JOIN tmp_egrp tmpe ON tmpe.open_registration_number = tmpd.registration_number;

SELECT
    question_number,
    member_decision,
    premise_number,
    total_square,
    size_num,
    size_denom,
    adjusted_area,
    size_num_egrp,
    size_denom_egrp,
    ROUND(CASE right_type_code
        WHEN '001001000000' THEN total_square
        WHEN '001002000000' THEN total_square * COALESCE(size_num_egrp, 1) / COALESCE(size_denom_egrp, 1)
        WHEN '001003000000' THEN total_square
    END, 4) AS egrp_adjusted_area,
    right_type_code,
    registration_number AS vt_registration_number,
    registration_date AS vt_registration_date,
    open_registration_number,
    open_registration_date,
    close_registration_number,
    close_registration_date,
    actual_right,
    join_ownership_number,
    CASE join_ownership_number WHEN 1 THEN TRUE ELSE FALSE END AS join_actual,
    reg_order_number,
    CASE reg_order_number WHEN 1 THEN TRUE ELSE FALSE END AS reg_latest
FROM
    tmp_decision_with_egrp;

SELECT
    registration_number,
    registration_date,
    premise_number,
    MAX(adjusted_area),
    MIN(adjusted_area),
    COUNT(DISTINCT question_number)
FROM
    tmp_decision_with_egrp
GROUP BY
    registration_number,
    registration_date,
    premise_number
HAVING
    MAX(adjusted_area) <> MIN(adjusted_area)
    OR COUNT(DISTINCT question_number)<11;

SELECT
    vq.*,
    COALESCE(SUM(tmpd.adjusted_area) FILTER (WHERE tmpd.member_decision = 'TRUE' AND tmpd.reg_order_number=1 AND tmpd.join_ownership_number=1 AND COALESCE(tmpd.actual_right, TRUE)), 0) AS vote_true_new,
    COALESCE(SUM(tmpd.adjusted_area) FILTER (WHERE tmpd.member_decision = 'TRUE' AND tmpd.join_ownership_number=1), 0) AS vote_true,
    vq.voting_result_true,
    COALESCE(SUM(tmpd.adjusted_area) FILTER (WHERE tmpd.member_decision = 'FALSE' AND tmpd.reg_order_number=1 AND tmpd.join_ownership_number=1 AND COALESCE(tmpd.actual_right, TRUE)), 0) AS vote_false_new,
    COALESCE(SUM(tmpd.adjusted_area) FILTER (WHERE tmpd.member_decision = 'FALSE' AND tmpd.join_ownership_number=1), 0) AS vote_false,
    vq.voting_result_false,
    COALESCE(SUM(tmpd.adjusted_area) FILTER (WHERE tmpd.member_decision = 'ABSTENT' AND tmpd.reg_order_number=1 AND tmpd.join_ownership_number=1 AND COALESCE(tmpd.actual_right, TRUE)), 0) AS vote_abstent_new,
    COALESCE(SUM(tmpd.adjusted_area) FILTER (WHERE tmpd.member_decision = 'ABSTENT' AND tmpd.join_ownership_number=1), 0) AS vote_abstent,
    vq.voting_result_abstent,
    COALESCE(SUM(tmpd.adjusted_area) FILTER (WHERE tmpd.reg_order_number=1 AND tmpd.join_ownership_number=1), 0) AS vote_total_new,
    COALESCE(SUM(tmpd.adjusted_area) FILTER (WHERE tmpd.join_ownership_number=1), 0) AS vote_total,
    vq.voting_result_true+voting_result_false+voting_result_abstent AS voting_result_total
FROM
    vtsm.vt_voting_version vv
    INNER JOIN vtsm.vt_question vq ON vq.voting_version_guid = vv.guid
    INNER JOIN tmp_decision_with_egrp tmpd ON tmpd.question_guid = vq.guid
WHERE
    vv.guid='9961f908-9e7f-4957-8f83-cffe3c3a3dbe'
GROUP BY
    vq.guid
ORDER BY
    vq.question_number;