WITH input_id AS
(
    SELECT
        ce.id AS ce_id,
        ce.sequence_number,
        ROW_NUMBER() OVER (ORDER BY sequence_number DESC) AS rn
    FROM
        administrations.contract con
        INNER JOIN administrations.contract_edition ce ON con.id = ce.contract_id
    WHERE
        -- Вставить идентификатор договора сюда. Выгрузится последняя редакция
        con.id = 'fc3eeb29-4e6a-47fd-b96b-2ae2fe4ef75a'
        AND ce.status = 'Approved')
SELECT
    '<?xml version="1.0" encoding="utf-8"?>' ||
    '<Contract xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"' ||
    ' Number="' || ce_first."number"  || '" Date="' || ce_first.date_conclusion || '"' ||
    CASE WHEN ce.sequence_number > 1 THEN
    ' SupplementaryAgreementNumber="'|| COALESCE(ce."number", 'б/н') || '" SupplementaryAgreementDate="' || ce.date_conclusion || '"' ELSE '' END ||
    ' SchoolNumber="' || eo.code || '" SchoolName="' || REPLACE(o.full_title, '"', '&quot;') || '" ' ||
    ' FeedOrganizationName="' || REPLACE(feed_o.full_title, '"', '&quot;') || '" ' ||
    ' StartDate="' || ce.date_from || '" EndDate="' || ce.date_to || '" xmlns="urn:ekburg.ru:feed:Contract:1.0">' ||

    '<School RegistrationName="' || REPLACE(o.full_title, '"', '&quot;') || '" ShortName="' || REPLACE(COALESCE(o.short_title, 'не указано'), '"', '&quot;') ||
    '" Address="' || REPLACE(o.address, '"', '&quot;') || '" INN="' || o.inn || '" KPP="' || o.kpp ||
    '" PayeeName="' || eo.parent_payment_info || '" AccountNumber="03234643657010006200" BIK="016577551" CorrespondenceNumber="40102810645370000054" Bank="Уральское ГУ Банка России//УФК по Свердловской области г. Екатеринбург" />' ||

    '<FeedOrganization RegistrationName="' || REPLACE(feed_o.full_title, '"', '&quot;') || '" ShortName="' || REPLACE(COALESCE(feed_o.short_title, 'не указано'), '"', '&quot;') ||
    '" Address="' || REPLACE(feed_o.address, '"', '&quot;') || '" INN="' || feed_o.inn ||
    '" KPP="' || COALESCE(feed_o.kpp, '000000000') ||
    '" PayeeName="' || REPLACE(COALESCE(feed_o.short_title, feed_o.full_title), '"', '&quot;') || '" AccountNumber="' || bank_account.account_number ||
    '" BIK="' || bank_account.bik || '" CorrespondenceNumber="' || bank_account.corr_account_number || '" Bank="' || REPLACE(bank_account.bank_name, '"', '&quot;') || '" />' ||

    '' || fo.fo_list ||
    '</Contract>'
FROM
    input_id
    INNER JOIN administrations.contract_edition ce ON ce.id = input_id.ce_id AND input_id.rn = 1
    INNER JOIN administrations.contract con ON con.id = ce.contract_id
    INNER JOIN administrations.bank_account ON bank_account.id = ce.bank_account_id
    INNER JOIN administrations.contract_edition ce_first ON ce_first.contract_id = con.id AND ce_first.sequence_number = 1
    INNER JOIN administrations.organization o ON con.educational_organization_id = o.id
    INNER JOIN administrations.educational_organization eo ON o.educational_id = eo.id
    INNER JOIN administrations.organization feed_o ON feed_o.id = con.catering_organization_id
    INNER JOIN LATERAL (
        SELECT
            STRING_AGG (DISTINCT
                '<FeedComplex Name="' || cfo.name || '" ' ||
                ' ParentPrice="' || COALESCE(cfo.parent_money, 0) || '" BudgetPrice="' || COALESCE(cfo.subsidy_money, 0) || '" ' ||
                CASE WHEN cfo.products_cost IS NOT NULL
                    THEN ' ProductSet="' || cfo.products_cost || '" Markup="' || cfo.margin || '" '
                    ELSE  ''
                END ||
                ' ClassFrom="' || cfo.minimum_class || '" ClassTo="' || cfo.maximum_class || '" ' ||
                CASE WHEN cfo.subsidy_money IS NOT NULL
                    THEN ' IsCorrection="' || CASE WHEN subsidy_pc.is_two_meals THEN 'true' ELSE 'false' END ||
                        '" IsInvalid="' || CASE WHEN subsidy_pc.invalid = 1 THEN 'true' ELSE 'false' END || '" ' ||
                        ' IsLargeFamily="' || CASE WHEN subsidy_pc.large_family = 1 THEN 'true' ELSE 'false' END || '" ' ||
                        ' IsLowIncome="' ||  CASE WHEN subsidy_pc.swo + subsidy_pc.ukraine_resident + subsidy_pc.low_income + subsidy_pc.military_children >= 1 THEN 'true' ELSE 'false' END || '" ' ||
                        ' IsOrphan="' || CASE WHEN subsidy_pc.orphan = 1 THEN 'true' ELSE 'false' END || '"'
                    ELSE ''
                END ||
                ' PortionCount="' || cfo.max_portion_count || '" />',
                ''
            ) as fo_list
        FROM
            administrations.contract_feeding_option cfo
            LEFT JOIN (
                SELECT
                    subsidy.id AS subsidy_id,
                    subsidy.is_two_meals,
                    MAX(CASE WHEN privilege_category.code = 'LargeFamily' THEN 1 ELSE 0 END) AS large_family,
                    MAX(CASE WHEN privilege_category.code = 'UkraineResident' THEN 1 ELSE 0 END) AS ukraine_resident,
                    MAX(CASE WHEN privilege_category.code = 'LowIncome' THEN 1 ELSE 0 END) AS low_income,
                    MAX(CASE WHEN privilege_category.code = 'MilitaryChildren' THEN 1 ELSE 0 END) AS military_children,
                    MAX(CASE WHEN privilege_category.code = 'Invalid' THEN 1 ELSE 0 END) AS invalid,
                    MAX(CASE WHEN privilege_category.code = 'Orphan' THEN 1 ELSE 0 END) AS orphan,
                    MAX(CASE WHEN privilege_category.code = 'SWO' THEN 1 ELSE 0 END) AS swo
                FROM
                    administrations.subsidy
                    INNER JOIN administrations.subsidy_privilege_category subsidy_pc ON subsidy_pc.subsidy_id = subsidy.id
                    INNER JOIN administrations.privilege_category ON privilege_category.id = subsidy_pc.privilege_category_id
                GROUP BY
                    subsidy.id
            ) subsidy_pc ON subsidy_pc.subsidy_id = cfo.subsidy_id
        WHERE
            cfo.contract_edition_id = ce.id
        GROUP BY
            cfo.contract_edition_id
    ) fo ON TRUE;