CREATE OR REPLACE FUNCTION sl_confirm_group_arisan(bigint, bigint,character varying)
  RETURNS integer AS
$BODY$
DECLARE
     pGroupArisanId         ALIAS FOR $1;
     pUserId                ALIAS FOR $2;
     pDatetime              ALIAS FOR $3;

     vStatusArisanRelease   character varying;
     vNullValue             bigint;
     vSessionId             character varying;
BEGIN

    vStatusArisanRelease :='R';
    vNullValue:=-99;


    SELECT f_make_uid() INTO vSessionId;

    /*
        1. update group, statusnya
        2. generate schedule balance
     */

    UPDATE sl_group_arisan
    SET status_arisan = vStatusArisanRelease,
        update_datetime = pDatetime,
        update_user_id =pUserId
    WHERE group_arisan_id =pGroupArisanId;

    -- untuk generate order dari pemenang
    INSERT INTO tt_temp_order_schedule_arisan(session,group_arisan_id,group_arisan_member_id,rnd)
    SELECT vSessionId,group_arisan_id,group_arisan_member_id,(floor(random() * (99+1))::int) as rnd
    FROM sl_group_arisan_member
    WHERE group_arisan_id = pGroupArisanId
    AND ketua_arisan_id =vNullValue;

    INSERT INTO tt_temp_order_schedule_arisan(session_id,group_arisan_id,group_arisan_member_id,rnd)
    SELECT vSessionId,group_arisan_id,group_arisan_member_id,1000::int as rnd
    FROM sl_group_arisan_member
    WHERE group_arisan_id = pGroupArisanId
    AND ketua_arisan_id <>vNullValue;

    --insert data dasar untuk schedule
    WITH raw AS(
        SELECT  A.group_arisan_id,C.wallet_client_id,C.member_id AS ketua_member_id,
            A.group_arisan_member_id AS winner_group_arisan_member_id,
            A.group_arisan_member_name AS winner_name,
            A.group_arisan_member_phone AS winner_phone,
            vNo AS flg_input_member_code,
            vNo AS flg_is_member_or_not,
            '' AS member_code,
            '' AS payment_due_date,
            vNo AS payment_status,
            vNo AS shipment_status,
            0 AS payment_amount,
            B.periode_arisan_code ,
            (ROW_NUMBER() OVER (PARTITION BY A.group_arisan_id ORDER BY D.rnd) -1)AS line_no,
            B.first_shake_date,
            vNo AS shake_status
        FROM sl_group_arisan_member A
        JOIN sl_group_arisan B ON A.group_arisan_id =B.group_arisan_id
        JOIN mlm_ketua_arisan C ON C.ketua_arisan_id =B.ketua_arisan_id
        JOIN tt_temp_order_schedule_arisan D ON D.group_arisan_member_Id =A.group_arisan_member_id AND D.session_id =vSessionId
        WHERE A.group_arisan_id =pGroupArisanId
    )
    INSERT INTO sl_group_arisan_balance_schedule(
        group_arisan_id,wallet_client_id,ketua_member_id,line_no,shake_date,shake_status,
        winner_group_arisan_member_id,winner_name,winner_phone,flg_input_member_code,
        flg_is_member_or_not,member_code,payment_due_date,payment_date,payment_status,shipment_status,
        payment_status,create_datetime,create_user_id,update_datetime,update_user_id,version
    )
    SELECT group_arisan_id,wallet_client_id,ketua_member_id,line_no+1 AS line_no,
            CASE WHEN periode_arisan_code ='1WEEK' THEN
            to_char(to_timestamp(first_shake_date,'YYYYMMDD')+ line_no*(INTERVAL '7 Day'),'YYYYMMDD')
              WHEN periode_arisan_code = '2WEEK' THEN
            to_char(to_timestamp(first_shake_date,'YYYYMMDD')+ line_no*(INTERVAL '14 Day'),'YYYYMMDD')
              WHEN periode_arisan_code = '1MONTH' THEN
            to_char(to_timestamp(first_shake_date,'YYYYMMDD')+ line_no*(INTERVAL '1 month'),'YYYYMMDD')
            END AS shake_date,
         shake_status,winner_group_arisan_member_id,winner_name,winner_phone,flg_input_member_code,
         flg_is_member_or_not,member_code,payment_due_date,' ' AS payment_date,payment_status,shipment_status,
         payment_amount,pDatetime,pUserId,pDatetime,pUserId,0
    FROM raw;

    -- update payment_due_date, shake_date + 1
    UPDATE sl_group_arisan_balance_schedule
    SET payment_due_date = to_char(to_timestamp(shake_date,'YYYYMMDD') + INTERVALS '1 Day','YYYYMMDD')
    WHERE group_arisan_id =pGroupArisanId;

    SELECT member_count INTO vMemberCount
    FROM sl_group_arisan WHERE group_arisan_id =pGroupArisanId;

    SELECT SUM(total_catalog_amount) INTO vTotalAmount
    FROM sl_group_arisan_member WHERE group_arisan_id =pGroupArisanId;

    -- update payment amount
    UPDATE sl_group_arisan_balance_schedule
    SET payment_amount = TRUNC(vTotalAmount/vMemberCount,0)
    WHERE group_arisan_id =pGroupArisanId
    AND line_no < vMemberCount;

    SELECT SUM(payment_amount) INTO vTotalScheduleAmount
    FROM sl_group_arisan_balance_schedule WHERE group_arisan_id =pGroupArisanId;

    UPDATE sl_group_arisan_balance_schedule
    SET payment_amount = vTotalAmount - vTotalScheduleAmount
    WHERE group_arisan_id =pGroupArisanId
    AND payment_amount = 0;

    -- insert log
    INSERT INTO sl_log_group_arisan_balance_schedule(
    group_arisan_schedule_balance_id,group_arisan_id,wallet_client_id,ketua_member_id,line_no,shake_date,shake_status,
    winner_group_arisan_member_id,winner_name,winner_phone,flg_input_member_code,flg_is_member_or_not,member_code,payment_due_date,
    payment_date,payment_status,payment_amount,shipment_status,create_datetime,create_user_id,update_datetime,update_user_id,version
    )
    SELECT group_arisan_schedule_balance_id,group_arisan_id,wallet_client_id,ketua_member_id,line_no,shake_date,shake_status,
    winner_group_arisan_member_id,winner_name,winner_phone,flg_input_member_code,flg_is_member_or_not,member_code,payment_due_date,
    payment_date,payment_status,payment_amount,shipment_status,create_datetime,create_user_id,update_datetime,update_user_id,version
    FROM sl_group_arisan_balance_schedule
    WHERE group_arisan_id = pGroupArisanId;



    return 0;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
/