CREATE OR REPLACE FUNCTION public.pd_get_product_prices(
    p_limit integer,
    p_offset integer,
    p_title character varying DEFAULT NULL,
    p_status type_productstatus DEFAULT NULL,
    p_in_stock boolean DEFAULT NULL,
    p_sku character varying DEFAULT NULL,
    p_sale_price_from type_money DEFAULT NULL,
    p_sale_price_till type_money DEFAULT NULL,
    p_rating_from integer DEFAULT NULL,
    p_rating_till integer DEFAULT NULL,
    p_category_list integer[] DEFAULT NULL
)

 RETURNS TABLE(id integer, title character varying, status type_productstatus, sku character varying, user_id smallint, regular_price type_money, sale_price type_money, in_stock boolean, short_description character varying, virtual boolean, rating_count integer, rating_summary integer, rating integer, created_at timestamp without time zone, product_image character varying, product_categories character varying[], product_categories_id smallint[], product_tags character varying[], product_tags_id smallint[], product_attributes jsonb)
 LANGUAGE sql
AS $function$

select * from ( SELECT p.id, p.title, p.status, p.sku, p.user_id, p.regular_price, p.sale_price, p.in_stock, p.short_description, p.virtual, p.rating_count, p.rating_summary,
CAST( floor( p.rating_summary / NULLIF(p.rating_count,0)  ) AS INTEGER ) as rating, p.created_at,
(select pi.image from pd_product_image as pi where pi.product_id = p.id and pi.is_main = true ) as product_image,
( select array_agg(c.name) from pd_category as c, pd_product_category as pd where c.id= pd.category_id and pd.product_id = p.id) as product_categories,
( select array_agg(c.id) from pd_category as c, pd_product_category as pd where c.id= pd.category_id and pd.product_id = p.id) as product_categories_id,
( select array_agg(t.name) from pd_tag as t, pd_product_tag as pt where t.id= pt.tag_id and pt.product_id = p.id) as product_tags,
( select array_agg(t.id) from pd_tag as t, pd_product_tag as pt where t.id= pt.tag_id and pt.product_id = p.id) as product_tags_id,
( select pa.attributes_data from pd_product_attribute as pa where pa.product_id = p.id) as product_attributes


FROM pd_product AS p LEFT JOIN  pd_product_category AS pc ON pc.product_id = p.id
       WHERE
           ( p.status= p_status OR p_status IS NULL ) AND
           ( p.in_stock = p_in_stock OR p_in_stock IS NULL )  AND
           ( p.sku like p_sku OR p_sku IS NULL )  AND
           ( p.title like p_title OR p_title IS NULL ) AND
           ( ( p.sale_price >= p_sale_price_from AND p.sale_price <= p_sale_price_till ) OR ( p_sale_price_from IS NULL OR p_sale_price_till IS null ) ) AND
          ( pc.category_id = ANY (p_category_list) OR p_category_list IS NULL )
) as rows

       WHERE( (   rows.rating >= p_rating_from AND rows.rating <= p_rating_till ) OR ( p_rating_from IS NULL OR p_rating_till IS null ) )

       ORDER BY rows.sale_price asc
       LIMIT p_limit  OFFSET p_offset;

$function$


CREATE OR REPLACE FUNCTION public.pd_delete_product(p_id integer)
 RETURNS integer
 LANGUAGE plpgsql
AS $function$
  begin

      DELETE FROM pd_product_attribute where product_id= p_id;

      DELETE FROM pd_product_category where product_id= p_id;

      DELETE FROM pd_product_comment where product_id= p_id;

      DELETE FROM pd_product_downloadable where product_id= p_id;

      DELETE FROM pd_product_image where product_id= p_id;

      DELETE FROM pd_product_tag where product_id= p_id;

      DELETE FROM pd_product where id= p_id;

      RETURN p_id;

  end
$function$



CREATE OR REPLACE FUNCTION public.pd_delete_product(p_id integer)
 RETURNS integer
 LANGUAGE plpgsql
AS $function$
  begin

      DELETE FROM pd_product_attribute where product_id= p_id;

      DELETE FROM pd_product_category where product_id= p_id;

      DELETE FROM pd_product_comment where product_id= p_id;

      DELETE FROM pd_product_downloadable where product_id= p_id;

      DELETE FROM pd_product_image where product_id= p_id;

      DELETE FROM pd_product_tag where product_id= p_id;

      DELETE FROM pd_product where id= p_id;

      RETURN p_id;

  end
$function$



CREATE OR REPLACE FUNCTION public.delete_product_comment(p_id integer)
 RETURNS integer
 LANGUAGE plpgsql
AS $function$
declare
  r pd_product_comment%rowtype;
  begin

      for r in SELECT id FROM pd_product_comment WHERE parent_product_comment_id = p_id loop
          INSERT INTO pd_d(val) VALUES(  CAST( r.id as varchar(255)  )  );
          PERFORM delete_product_comment(r.id);

      end loop;

      DELETE FROM pd_product_comment where id= p_id;

      RETURN p_id;

  end
$function$



CREATE OR REPLACE FUNCTION public.pd_delete_product(p_id integer)
 RETURNS integer
 LANGUAGE plpgsql
AS $function$
  begin

      DELETE FROM pd_product_attribute where product_id= p_id;

      DELETE FROM pd_product_category where product_id= p_id;

      DELETE FROM pd_product_comment where product_id= p_id;

      DELETE FROM pd_product_downloadable where product_id= p_id;

      DELETE FROM pd_product_image where product_id= p_id;

      DELETE FROM pd_product_tag where product_id= p_id;

      DELETE FROM pd_product where id= p_id;

      RETURN p_id;

  end
$function$

CREATE OR REPLACE FUNCTION public.pd_get_product_prices(
    p_limit integer,
    p_offset integer,
    p_title character varying DEFAULT NULL,
    p_status type_productstatus DEFAULT NULL,
    p_in_stock boolean DEFAULT NULL,
    p_sku character varying DEFAULT NULL,
    p_sale_price_from type_money DEFAULT NULL,
    p_sale_price_till type_money DEFAULT NULL,
    p_rating_from integer DEFAULT NULL,
    p_rating_till integer DEFAULT NULL,
    p_category_list integer[] DEFAULT NULL
)

 RETURNS TABLE(id integer, title character varying, status type_productstatus, sku character varying, user_id smallint, regular_price type_money, sale_price type_money, in_stock boolean, short_description character varying, virtual boolean, rating_count integer, rating_summary integer, rating integer, created_at timestamp without time zone, product_image character varying, product_categories character varying[], product_categories_id smallint[], product_tags character varying[], product_tags_id smallint[], product_attributes jsonb)
 LANGUAGE sql
AS $function$

select * from ( SELECT p.id, p.title, p.status, p.sku, p.user_id, p.regular_price, p.sale_price, p.in_stock, p.short_description, p.virtual, p.rating_count, p.rating_summary,
CAST( floor( p.rating_summary / NULLIF(p.rating_count,0)  ) AS INTEGER ) as rating, p.created_at,
(select pi.image from pd_product_image as pi where pi.product_id = p.id and pi.is_main = true ) as product_image,
( select array_agg(c.name) from pd_category as c, pd_product_category as pd where c.id= pd.category_id and pd.product_id = p.id) as product_categories,
( select array_agg(c.id) from pd_category as c, pd_product_category as pd where c.id= pd.category_id and pd.product_id = p.id) as product_categories_id,
( select array_agg(t.name) from pd_tag as t, pd_product_tag as pt where t.id= pt.tag_id and pt.product_id = p.id) as product_tags,
( select array_agg(t.id) from pd_tag as t, pd_product_tag as pt where t.id= pt.tag_id and pt.product_id = p.id) as product_tags_id,
( select pa.attributes_data from pd_product_attribute as pa where pa.product_id = p.id) as product_attributes


FROM pd_product AS p LEFT JOIN  pd_product_category AS pc ON pc.product_id = p.id
       WHERE
           ( p.status= p_status OR p_status IS NULL ) AND
           ( p.in_stock = p_in_stock OR p_in_stock IS NULL )  AND
           ( p.sku like p_sku OR p_sku IS NULL )  AND
           ( p.title like p_title OR p_title IS NULL ) AND
           ( ( p.sale_price >= p_sale_price_from AND p.sale_price <= p_sale_price_till ) OR ( p_sale_price_from IS NULL OR p_sale_price_till IS null ) ) AND
          ( pc.category_id = ANY (p_category_list) OR p_category_list IS NULL )
) as rows

       WHERE( (   rows.rating >= p_rating_from AND rows.rating <= p_rating_till ) OR ( p_rating_from IS NULL OR p_rating_till IS null ) )

       ORDER BY rows.sale_price asc
       LIMIT p_limit  OFFSET p_offset;

$function$


CREATE OR REPLACE FUNCTION public.pd_update_product(p_id integer, p_title character varying, p_status type_productstatus, p_sku character varying, p_user_id integer, p_regular_price type_money, p_sale_price type_money, p_in_stock boolean, p_is_homepage boolean, p_is_featured boolean, p_short_description character varying, p_description text, p_has_attributes boolean, p_downloadable boolean, p_virtual boolean, p_category_list integer[] DEFAULT NULL::integer[], p_tag_list integer[] DEFAULT NULL::integer[], p_attribute_list jsonb DEFAULT NULL::jsonb, p_created_at timestamp without time zone DEFAULT NULL::timestamp without time zone)
 RETURNS integer
 LANGUAGE plpgsql
AS $function$
  begin
    IF p_id <= 0 THEN

      INSERT INTO pd_product ( title, status, sku, user_id, regular_price, sale_price, in_stock, is_homepage, is_featured, short_description, description, has_attributes, downloadable, virtual )
        VALUES(  p_title, p_status, p_sku, p_user_id, p_regular_price, p_sale_price, p_in_stock, p_is_homepage, p_is_featured, p_short_description, p_description, p_has_attributes, p_downloadable, p_virtual );
      p_id= lastval();

      IF (p_created_at IS NOT NULL ) THEN
        UPDATE pd_product SET created_at= p_created_at
          where id= p_id;
      END IF;

    ELSE

      UPDATE pd_product set title= p_title, status= p_status, sku= p_sku, user_id= p_user_id, regular_price= p_regular_price, sale_price= p_sale_price, in_stock= p_in_stock, is_homepage= p_is_homepage, is_featured= p_is_featured, short_description= p_short_description, description= p_description, has_attributes= p_has_attributes, downloadable= p_downloadable, virtual= p_virtual, updated_at= now()
        where id= p_id;

        IF p_downloadable = false THEN
          DELETE FROM pd_product_downloadable where product_id= p_id;
        END IF;

        IF p_has_attributes = false THEN
          DELETE FROM pd_product_attribute where product_id= p_id;
        END IF;

    END IF;

    PERFORM pd_update_product_category(p_id, p_category_list);

    PERFORM pd_update_product_tag(p_id, p_tag_list);

    PERFORM pd_update_product_attribute(p_id, p_attribute_list);

    RETURN p_id;

  end
$function$


CREATE OR REPLACE FUNCTION public.pd_delete_product(p_id integer)
 RETURNS integer
 LANGUAGE plpgsql
AS $function$
  begin

      DELETE FROM pd_product_attribute where product_id= p_id;

      DELETE FROM pd_product_category where product_id= p_id;

      DELETE FROM pd_product_comment where product_id= p_id;

      DELETE FROM pd_product_downloadable where product_id= p_id;

      DELETE FROM pd_product_image where product_id= p_id;

      DELETE FROM pd_product_tag where product_id= p_id;

      DELETE FROM pd_product where id= p_id;

      RETURN p_id;

  end
$function$



CREATE OR REPLACE FUNCTION public.pd_delete_product_comment(p_id integer)
 RETURNS integer
 LANGUAGE plpgsql
AS $function$
declare
  r pd_product_comment%rowtype;
  begin

      for r in SELECT id FROM pd_product_comment WHERE parent_product_comment_id = p_id loop
          -- INSERT INTO pd_d(val) VALUES(  CAST( r.parent_product_comment_id as varchar(255)  )  );
          PERFORM pd_delete_product_comment(r.id);

      end loop;

      DELETE FROM pd_product_comment where id= p_id;

      RETURN p_id;

  end
$function$


CREATE OR REPLACE FUNCTION public.pd_recalc_product_rating(p_id integer, p_write_to_product boolean)
 RETURNS TABLE(comment_rating_count integer, comment_rating_sum integer)
 LANGUAGE plpgsql
AS $function$
DECLARE comment_rating_count integer;
DECLARE comment_rating_sum integer;
   begin

       SELECT count("id"), sum(rating) INTO comment_rating_count, comment_rating_sum FROM pd_product_comment WHERE product_id = p_id LIMIT 1;
       IF ( p_write_to_product ) THEN
          UPDATE pd_product  SET rating_count = comment_rating_count, rating_summary= COALESCE(comment_rating_sum,0) WHERE id = p_id;
       END IF;
       RETURN QUERY SELECT comment_rating_count, COALESCE( comment_rating_sum,0 ) ;

    end
$function$