CREATE OR REPLACE FUNCTION get_pwo_id(integer, character varying, character varying, character varying, character varying)
  RETURNS SETOF json AS
$BODY$
DECLARE
    product_ids integer = $1;
    site_names character varying = $2;
    guest_token character varying = $3;
    access_token character varying = $4;
    image_size character varying = $5;
    level_global INTEGER;
    json_result json;
BEGIN

    --Определяем количество уровней
    SELECT INTO level_global max(product_type_to_product_options.level) as level
    FROM product_type_to_product_options, products
    WHERE product_type_to_product_options.product_type_name = products.product_type_name AND products.id = product_ids;

    IF (level_global = 1) THEN
        with
            --Получаем продукты и их количество в корзине
            basket_pwo as (
                SELECT * from get_basket(guest_token, access_token)
            ),

            --Получаем PWO и цены
            pwo_v as (
                SELECT sp.products_with_options_id as products_with_options_id, sp.price, COALESCE(check_price(access_token), False) as is_visible_price
                FROM products_with_options as pwo, sale_price as sp
                WHERE pwo.id=sp.products_with_options_id AND pwo.product_id = product_ids AND sp.site_name= site_names
            ),

            full_pwo as (
                SELECT pwo_v.*, COALESCE(basket_pwo.quantity, 0) as quantity
                FROM pwo_v
                LEFT JOIN basket_pwo
                ON pwo_v.products_with_options_id = basket_pwo.pwo_id
            ),

            --Находим связи со значениями характеристик
            pwo_to_po_v as (
                SELECT pwo_to_pov.product_options_value_id, full_pwo.products_with_options_id,
                       full_pwo.price, full_pwo.quantity, full_pwo.is_visible_price,
                       COALESCE(get_image(product_ids, pwo_to_pov.product_options_value_id, image_size, site_names)) as image
                FROM products_with_options_to_product_options_value as pwo_to_pov, full_pwo
                WHERE pwo_to_pov.products_with_options_id=full_pwo.products_with_options_id
            ),

            --Получаем PWO_id, product_options_id, value_id, value и сортируем по полю value
            pov_v as (
                SELECT pwo_to_po_v.products_with_options_id, pov.product_options_id, pov.id as id_value,
                       value, pwo_to_po_v.price, pwo_to_po_v.quantity, pwo_to_po_v.is_visible_price, pwo_to_po_v.image
                FROM product_options_value as pov, pwo_to_po_v
                WHERE pov.id=pwo_to_po_v.product_options_value_id
                ORDER BY value
            ),

            --Получаем название характеристики
            po_v as (
                SELECT po.name as level1
                FROM pov_v, product_options as po
                WHERE pov_v.product_options_id=po.id
                LIMIT 1
            ),

            --Пихаем в JSON структуру level1
            json_level1_virtual as (
                SELECT json_agg(
                row_to_json (((
                    SELECT t FROM (SELECT products_with_options_id, id_value, value, price, quantity, is_visible_price, image)
                    as t( products_with_options_id, id_value, value, price, quantity, is_visible_price, image))))
                ) as level1
                FROM pov_v
            ),

            --Пихаем в JSON структуру name
            json_name_virtual as (
                SELECT row_to_json(po_v) as names
                FROM po_v
            ),

            --Связыем level1 и name в одну таблицу
            level1_name_virtual as (
                SELECT json_name_virtual.names, json_level1_virtual.level1
                FROM json_name_virtual, json_level1_virtual
            ),

            --Пихаем в JSON структуру products_with_options
            json_pwo_virtual as (
                SELECT row_to_json(level1_name_virtual) as products_with_options
                FROM level1_name_virtual
        )

        SELECT INTO json_result products_with_options FROM json_pwo_virtual;
        RETURN NEXT json_result;


    ELSEIF (level_global = 2) THEN
         WITH
            --Получаем продукты и их количество в корзине
            basket_pwo as (
                SELECT * from get_basket(guest_token, access_token)
            ),

            -- Получаем уровни для значений
            po_values AS (
                SELECT product_type_to_product_options.product_options_id, product_type_to_product_options.level AS level
                FROM product_type_to_product_options, products
                WHERE product_type_to_product_options.product_type_name = products.product_type_name AND products.id = product_ids AND NOT level = 0
            ),

            -- Получаем PWO и цены
            pwo_v AS (
                SELECT sp.products_with_options_id AS products_with_options_id, sp.price, COALESCE(check_price(access_token), False) as is_visible_price
                FROM products_with_options AS pwo, sale_price AS sp
                WHERE pwo.id=sp.products_with_options_id AND pwo.product_id = product_ids AND sp.site_name= site_names
            ),

            -- получаем продукты и их количество в корзине
            full_pwo as (
                SELECT pwo_v.*, COALESCE(basket_pwo.quantity, 0) as quantity
                FROM pwo_v
                LEFT JOIN basket_pwo
                ON pwo_v.products_with_options_id = basket_pwo.pwo_id
            ),

            -- Находим связи со значениями характеристик
            pwo_to_po_v AS (
                SELECT pwo_to_pov.product_options_value_id, full_pwo.products_with_options_id, full_pwo.price, full_pwo.quantity, full_pwo.is_visible_price,
                       COALESCE(get_image(product_ids, pwo_to_pov.product_options_value_id, image_size, site_names)) as image
                FROM products_with_options_to_product_options_value AS pwo_to_pov, full_pwo
                WHERE pwo_to_pov.products_with_options_id=full_pwo.products_with_options_id
            ),

            -- Получаем PWO_id, product_options_id, value_id, value и сортируем по полю value и добавляем к ним level
            pov_v AS (
                SELECT pwo_to_po_v.products_with_options_id, pov.product_options_id, pov.id AS id_value,
                       value, pwo_to_po_v.price, pwo_to_po_v.quantity, pwo_to_po_v.is_visible_price, pwo_to_po_v.image
                FROM product_options_value AS pov, pwo_to_po_v
                WHERE pov.id=pwo_to_po_v.product_options_value_id
            ),

            pov_values AS (
                SELECT pov_v.*, po_values.level
                FROM pov_v
                FULL JOIN po_values
                ON pov_v.product_options_id = po_values.product_options_id
            ),

            -- Объединение в пары двух уровней
            join_pov AS (
                SELECT p1.products_with_options_id, p1.value as val, p1.id_value as id_v,
                       p2.value as value, p2.price, p2.quantity, p2.id_value, p2.is_visible_price, p1.image as image1, p2.image as image2
                from pov_values as p1
                JOIN pov_values as p2
                ON p1.products_with_options_id = p2.products_with_options_id
                WHERE p1.level = 1 AND p2.level = 2
                ORDER BY NULLIF(regexp_replace(p2.value, E'\\^[0-9+\.]', '', 'g'), '')::float
            ),

            -- Разбивка на уровни характеристик
            pov_json AS (
                SELECT val as value, id_v as id_value, image1 as image, json_agg (
                row_to_json (
                    (SELECT t FROM (SELECT products_with_options_id, id_value, value, price, quantity, is_visible_price, image2)
                     AS t( products_with_options_id, id_value, value, price, quantity, is_visible_price, image2))
                    )
                ) as level2
                FROM join_pov
                GROUP BY val, id_v, image
            ),

            -- Пихаем в JSON структуру level1
            pov_level1_json as (
                SELECT json_agg(pov_json) AS level1
                FROM pov_json
            ),

            -- Получаем название характеристики
            po_v_1 AS (
                SELECT 1 as indx, po.name AS level1
                FROM pov_values, product_options AS po
                WHERE pov_values.product_options_id=po.id AND level=1
                LIMIT 1
            ),

            po_v_2 AS (
                SELECT 1 as indx, po.name AS level2
                FROM pov_values, product_options AS po
                WHERE pov_values.product_options_id=po.id AND level=2
                LIMIT 1
            ),

            po_v as (
                select po_v_1.level1, po_v_2.level2
                from po_v_1
                join po_v_2
                on po_v_1.indx = po_v_2.indx
            ),

            -- Пихаем в JSON структуру name
            json_name_v AS (
                SELECT row_to_json(po_v) AS names
                FROM po_v
            ),

            -- Связыем level1 и name в одну таблицу
            level1_name_virtual AS (
                SELECT json_name_v.names, pov_level1_json.level1
                FROM json_name_v, pov_level1_json
            ),

            -- Пихаем в JSON структуру products_with_options
            json_pwo_virtual AS (
                SELECT row_to_json(level1_name_virtual) AS products_with_options
                FROM level1_name_virtual
            )

        SELECT INTO json_result products_with_options FROM json_pwo_virtual;
        RETURN NEXT json_result;

    ELSE
        with
            --Получаем продукты и их количество в корзине
            basket_pwo as (
                SELECT * from get_basket(guest_token, access_token)
            ),

            --Находим PWO_id и price
            pwo_v as (
                SELECT sp.products_with_options_id as products_with_options_id, sp.price
                FROM products_with_options as pwo, sale_price as sp
                WHERE pwo.id=sp.products_with_options_id AND pwo.product_id = product_ids AND sp.site_name= site_names
            ),

            full_pwo as (
                SELECT pwo_v.*, COALESCE(basket_pwo.quantity, 0) as quantity, COALESCE(check_price(access_token), False) as is_visible_price
                FROM pwo_v
                LEFT JOIN basket_pwo
                ON pwo_v.products_with_options_id = basket_pwo.pwo_id
            ),

            -- Пихаем в JSON
            json_pwo as (
                SELECT json_agg(full_pwo) as products_with_options
                FROM full_pwo
            )
        SELECT INTO json_result products_with_options FROM json_pwo;
        RETURN NEXT json_result;
    END IF;
END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION get_pwo_id(integer, character varying, character varying, character varying, character varying)
  OWNER TO "ros-zdravnica";