Ad9WPcKy

1INSERT INTO experience_extranet_ticket.ticket_category_table_mapping (experience_category,                                                  mapping_table_name,                                                      mapping_upsert_function_name)
2VALUES ('UMRAH',
3        'experience_extranet_ticket.ticket_umrah',
4        'experience_extranet_ticket_v2_helper.upsert_ticket_umrah_additional_data($1, $2)')
5ON CONFLICT (experience_category) DO UPDATE
6  SET
7    mapping_table_name           = EXCLUDED.mapping_table_name,
8    mapping_upsert_function_name = EXCLUDED.mapping_upsert_function_name;
9
10INSERT INTO experience_extranet_ticket.ticket_category_table_mapping (experience_category,                                                mapping_table_name,                                                mapping_upsert_function_name)
11VALUES ('LEISURE',
12        'experience_extranet_ticket.ticket_leisure',
13        'experience_extranet_ticket_v2_helper.upsert_ticket_leisure_additional_data($1, $2)')
14ON CONFLICT (experience_category) DO UPDATE
15  SET
16    mapping_table_name           = EXCLUDED.mapping_table_name,
17    mapping_upsert_function_name = EXCLUDED.mapping_upsert_function_name;
18
19CREATE TABLE IF NOT EXISTS experience_extranet_ticket.ticket_umrah
20(
21  ticket_id TEXT NOT NULL PRIMARY KEY
22    CONSTRAINT ticket_umrah_ticket_id_fkey
23    REFERENCES experience_extranet_ticket.ticket
24    ON UPDATE CASCADE ON DELETE RESTRICT,
25  "_lut" TIMESTAMP WITH TIME ZONE NOT NULL
26);
27
28CREATE INDEX IF NOT EXISTS experience_extranet_ticket_umrah__lut__idx
29  ON experience_extranet_ticket.ticket_umrah ("_lut");
30
31DO $$
32BEGIN
33  CREATE TRIGGER populate_lut_ticket_umrah
34    BEFORE INSERT OR UPDATE
35    ON experience_extranet_ticket.ticket_umrah
36    FOR EACH ROW
37  EXECUTE PROCEDURE experience_extranet_ticket.lut_ticket();
38  EXCEPTION
39  WHEN duplicate_object
40    THEN RAISE NOTICE 'the trigger already exists';
41END;
42$$;
43
44CREATE TABLE IF NOT EXISTS experience_extranet_ticket.ticket_leisure
45(
46  ticket_id TEXT NOT NULL PRIMARY KEY
47    CONSTRAINT ticket_leisure_ticket_id_fkey
48    REFERENCES experience_extranet_ticket.ticket
49    ON UPDATE CASCADE ON DELETE RESTRICT,
50  "_lut" TIMESTAMP WITH TIME ZONE NOT NULL
51);
52
53CREATE INDEX IF NOT EXISTS experience_extranet_ticket_leisure__lut__idx
54  ON experience_extranet_ticket.ticket_leisure ("_lut");
55
56DO $$
57BEGIN
58  CREATE TRIGGER populate_lut_ticket_leisure
59    BEFORE INSERT OR UPDATE
60    ON experience_extranet_ticket.ticket_leisure
61    FOR EACH ROW
62  EXECUTE PROCEDURE experience_extranet_ticket.lut_ticket();
63  EXCEPTION
64  WHEN duplicate_object
65    THEN RAISE NOTICE 'the trigger already exists';
66END;
67$$;
68
69
70CREATE OR REPLACE FUNCTION experience_extranet_management_v2_helper.get_experience_umrah_hotels_by_experience_id(experience_id_spec text) returns jsonb
71	security definer
72	language sql
73as $$
74SELECT jsonb_agg(to_jsonb(result))
75FROM (
76       SELECT *
77       FROM experience_extranet_experience.experience_umrah_hotel
78       WHERE experience_id = experience_id_spec
79     ) AS result
80$$;
81
82CREATE OR REPLACE FUNCTION experience_extranet_management_v2_helper.get_experience_umrah_flights_by_experience_id(experience_id_spec text) returns jsonb
83	security definer
84	language sql
85as $$
86SELECT jsonb_agg(to_jsonb(result))
87FROM (
88       SELECT *
89       FROM experience_extranet_experience.experience_umrah_flight
90       WHERE experience_id = experience_id_spec
91     ) AS result
92$$;
93
94CREATE OR REPLACE FUNCTION experience_extranet_management_v2_helper.get_experience_umrah_travel_agent_by_experience_id(experience_id_spec text) returns jsonb
95	security definer
96	language sql
97as $$
98SELECT jsonb_agg(to_jsonb(result))
99FROM (
100       SELECT *
101       FROM experience_extranet_experience.experience_umrah_travel_agent
102       WHERE experience_id = experience_id_spec
103     ) AS result
104$$;
105
106CREATE OR REPLACE FUNCTION experience_extranet_management_v2_helper.get_experience_umrah_additional_data(
107  filter_experience_id TEXT)
108  RETURNS JSONB
109SECURITY DEFINER
110LANGUAGE SQL
111AS $$
112WITH get_umrah_result AS (SELECT *
113                          FROM experience_extranet_experience.experience_umrah
114                          WHERE experience_id = filter_experience_id),
115     get_travel_agent_result AS (SELECT to_jsonb(result)
116                                 FROM (SELECT *
117                                       FROM experience_extranet_experience.experience_umrah_travel_agent
118                                       WHERE experience_id = filter_experience_id) result),
119     get_hotels_result AS (SELECT jsonb_agg(to_jsonb(result))
120                           FROM (SELECT *
121                                 FROM experience_extranet_experience.experience_umrah_hotel
122                                 WHERE experience_id = filter_experience_id) result),
123     get_flights_result AS (SELECT jsonb_agg(to_jsonb(result))
124                            FROM (SELECT *
125                                  FROM experience_extranet_experience.experience_umrah_flight
126                                  WHERE experience_id = filter_experience_id) result),
127     get_itinerary_description_result AS (SELECT *
128                                          FROM experience_extranet_experience.experience_itinerary_description eid
129                                          WHERE EXISTS(
130                                                  SELECT ei.experience_itinerary_id
131                                                  FROM experience_extranet_experience.experience_itinerary ei
132                                                  WHERE ei.experience_id = filter_experience_id
133                                                    AND ei.experience_itinerary_id = eid.experience_itinerary_id
134                                                    )),
135     get_itinerary_result AS (SELECT array_agg(itinerary_result) AS result
136                              FROM (SELECT *, (SELECT array_agg(res) AS descriptions
137                                               FROM (SELECT *, (SELECT array_agg(img) AS images
138                                                                FROM (SELECT *
139                                                                      FROM experience_extranet_experience.experience_itinerary_image iti
140                                                                      WHERE iti.itinerary_description_id = des.itinerary_description_id) img)
141                                                     FROM get_itinerary_description_result des
142                                                     WHERE ei.experience_itinerary_id = des.experience_itinerary_id
143                                                     ORDER BY des.time) res)
144                                    FROM experience_extranet_experience.experience_itinerary ei
145                                    WHERE experience_id = filter_experience_id
146                                    ORDER BY ei.day) itinerary_result),
147     get_tour_meeting_point_result AS (SELECT to_jsonb(result)
148                                       FROM (SELECT *
149                                             FROM experience_extranet_experience.experience_tour_meeting_point mp
150                                             WHERE mp.experience_id = filter_experience_id) AS result),
151     get_tour_content_result AS (SELECT *
152                                 FROM experience_extranet_content.experience_tour_content
153                                 WHERE experience_id = filter_experience_id)
154SELECT jsonb_build_object(
155         'duration_day', (SELECT duration_day FROM get_umrah_result),
156         'all_day_long', (SELECT all_day_long FROM get_umrah_result),
157         'travel_agent', (SELECT * FROM get_travel_agent_result),
158         'flights', (SELECT * FROM get_flights_result),
159         'hotels', (SELECT * FROM get_hotels_result),
160         'itineraries', (SELECT * FROM get_itinerary_result),
161         'pickup_price', (SELECT pickup_price FROM get_tour_content_result),
162         'pickup_address', (SELECT pickup_address FROM get_tour_content_result),
163         'drop_off_point', (SELECT drop_off_point FROM get_tour_content_result),
164         'meeting_point_available', (SELECT CASE WHEN get_tour_meeting_point_result IS NULL THEN FALSE ELSE TRUE END
165                                     FROM get_tour_meeting_point_result),
166         'meeting_point', (SELECT * FROM get_tour_meeting_point_result)
167           );
168$$;
169
170
171CREATE OR REPLACE FUNCTION experience_extranet_management_v2_helper.upsert_experience_umrah_additional_data(filter_experience_id text, additional_data_spec jsonb) returns boolean
172	security definer
173	language plpgsql
174as $$
175DECLARE
176  travel_agent_id_spec BIGINT;
177  itinerary_id BIGINT;
178  itinerary JSONB;
179  description JSONB;
180BEGIN
181  INSERT INTO experience_extranet_experience.experience_umrah (experience_id, duration_day, all_day_long)
182  VALUES (
183    filter_experience_id,
184    (COALESCE(NULLIF(additional_data_spec->>'duration_day', ''), '0'))::INTEGER,
185    (COALESCE(NULLIF(additional_data_spec->>'all_day_long', ''), 'FALSE')) :: BOOLEAN
186  )
187  ON CONFLICT (experience_id) DO UPDATE
188    SET
189      duration_day = EXCLUDED.duration_day,
190      all_day_long = EXCLUDED.all_day_long;
191
192  travel_agent_id_spec := additional_data_spec -> 'travel_agent' ->> 'travel_agent_id';
193  IF (travel_agent_id_spec = 0)
194  THEN
195    travel_agent_id_spec := nextval('experience_extranet_experience.experience_umrah_travel_agent_travel_agent_id_seq');
196  END IF;
197
198  INSERT INTO experience_extranet_experience.experience_umrah_travel_agent (travel_agent_id, experience_id, travel_agent_name, license_number)
199  VALUES (
200    travel_agent_id_spec,
201    filter_experience_id,
202    additional_data_spec->'travel_agent'->>'travel_agent_name',
203    additional_data_spec->'travel_agent'->>'license_number'
204  )
205  ON CONFLICT (travel_agent_id) DO UPDATE
206    SET
207      experience_id = EXCLUDED.experience_id,
208      travel_agent_name = EXCLUDED.travel_agent_name,
209      license_number = EXCLUDED.license_number;
210
211  DELETE
212  FROM experience_extranet_experience.experience_umrah_hotel
213  WHERE hotel_id NOT IN
214        (SELECT hotel_id FROM jsonb_array_elements(additional_data_spec->'hotels') AS elem
215         WHERE hotel_id = (elem->>'hotel_id') :: BIGINT) AND experience_id = filter_experience_id;
216
217  INSERT INTO experience_extranet_experience.experience_umrah_hotel (hotel_id, experience_id, country, city, rating, name, photos, distance_from_main_mosque)
218  SELECT
219         (hotel ->> 'hotel_id') :: BIGINT,
220         filter_experience_id,
221         hotel ->> 'country',
222         hotel ->> 'city',
223         (hotel ->> 'rating') :: INTEGER,
224         hotel ->> 'name',
225         hotel -> 'photos',
226         hotel ->> 'distance_from_main_mosque'
227  FROM jsonb_array_elements(additional_data_spec->'hotels') AS hotel
228  WHERE (hotel ->> 'hotel_id') :: BIGINT <> 0
229  ON CONFLICT (hotel_id) DO UPDATE
230    SET country = EXCLUDED.country,
231      city = EXCLUDED.city,
232      name = EXCLUDED.name,
233      photos = EXCLUDED.photos,
234      distance_from_main_mosque = EXCLUDED.distance_from_main_mosque;
235
236  INSERT INTO experience_extranet_experience.experience_umrah_hotel (experience_id, country, city, rating, name, photos, distance_from_main_mosque)
237  SELECT
238         filter_experience_id,
239         hotel ->> 'country',
240         hotel ->> 'city',
241         (hotel ->> 'rating') :: INTEGER,
242         hotel ->> 'name',
243         hotel -> 'photos',
244         hotel ->> 'distance_from_main_mosque'
245  FROM jsonb_array_elements(additional_data_spec->'hotels') AS hotel
246  WHERE (hotel ->> 'hotel_id') :: BIGINT = 0;
247
248
249  DELETE
250  FROM experience_extranet_experience.experience_umrah_flight
251  WHERE flight_id NOT IN
252        (SELECT flight_id
253         FROM jsonb_array_elements(additional_data_spec->'flights') AS elem
254         WHERE flight_id = (elem->>'flight_id') :: BIGINT) AND experience_id = filter_experience_id;
255
256  INSERT INTO experience_extranet_experience.experience_umrah_flight (experience_id, flight_order, airline_brand, port)
257  SELECT filter_experience_id,
258         (flight ->> 'flight_order') :: INTEGER,
259         flight ->> 'airline_brand',
260         flight ->> 'port'
261  FROM jsonb_array_elements(additional_data_spec->'flights') AS flight
262  WHERE (flight ->> 'flight_id') :: BIGINT = 0;
263
264  INSERT INTO experience_extranet_experience.experience_umrah_flight (flight_id, experience_id, flight_order, airline_brand, port)
265  SELECT (flight ->> 'flight_id') :: BIGINT,
266         filter_experience_id,
267         (flight ->> 'flight_order') :: INTEGER,
268         flight ->> 'airline_brand',
269         flight ->> 'port'
270  FROM jsonb_array_elements(additional_data_spec->'flights') AS flight
271  WHERE (flight ->> 'flight_id') :: BIGINT <> 0
272  ON CONFLICT (flight_id) DO UPDATE
273    SET flight_order = EXCLUDED.flight_order, airline_brand = EXCLUDED.airline_brand, port = EXCLUDED.port;
274
275  DELETE FROM experience_extranet_experience.experience_itinerary
276  WHERE NOT EXISTS (
277    SELECT experience_itinerary_id
278    FROM jsonb_array_elements(additional_data_spec->'itineraries') AS elem
279    WHERE experience_itinerary_id = (elem->>'experience_itinerary_id')::BIGINT
280  ) AND experience_id = filter_experience_id;
281
282  FOR itinerary IN SELECT jsonb_array_elements(additional_data_spec->'itineraries') LOOP
283    IF ((SELECT COUNT(*) FROM experience_extranet_experience.experience_itinerary WHERE experience_itinerary_id = (itinerary->>'experience_itinerary_id')::BIGINT) > 0)THEN
284      itinerary_id := (itinerary->>'experience_itinerary_id')::BIGINT;
285    ELSE
286      itinerary_id := nextval('experience_extranet_experience.experience_itinerary_seq');
287    END IF;
288
289    PERFORM experience_extranet_management_v2_helper.upsert_experience_itinerary(itinerary_id, filter_experience_id, itinerary);
290
291    DELETE FROM experience_extranet_experience.experience_itinerary_description eid
292    WHERE NOT EXISTS(
293      SELECT *
294      FROM jsonb_array_elements(itinerary->'descriptions') AS elem
295      WHERE eid.itinerary_description_id = (elem->>'itinerary_description_id')::BIGINT
296    ) AND eid.experience_itinerary_id = itinerary_id;
297
298    FOR description IN SELECT jsonb_array_elements(itinerary->'descriptions') LOOP
299      PERFORM experience_extranet_management_v2_helper.upsert_experience_itinerary_description(itinerary_id, description);
300    END LOOP;
301
302  END LOOP;
303
304  IF (additional_data_spec ->> 'meeting_point' IS NULL OR (additional_data_spec ->> 'meeting_point_available') :: BOOLEAN IS FALSE) THEN
305    DELETE FROM experience_extranet_experience.experience_tour_meeting_point
306    WHERE experience_id = filter_experience_id;
307  ELSE
308    PERFORM experience_extranet_management_v2_helper.upsert_tour_meeting_point(filter_experience_id, additional_data_spec -> 'meeting_point');
309  END IF;
310
311  PERFORM experience_extranet_content_v2_helper.upsert_experience_tour_content(filter_experience_id, additional_data_spec);
312
313  RETURN FOUND;
314END;
315$$;
316
317CREATE OR REPLACE FUNCTION experience_extranet_management_v2_helper.get_experience_leisure_additional_data(filter_experience_id TEXT)
318  RETURNS JSONB
319SECURITY DEFINER
320LANGUAGE SQL
321AS $$
322WITH get_leisure_result AS (
323    SELECT * FROM experience_extranet_experience.experience_leisure WHERE experience_id = filter_experience_id
324    )
325    SELECT jsonb_build_object('experience_id',
326        (SELECT experience_id FROM get_leisure_result)
327     );
328$$;
329
330
331CREATE OR REPLACE FUNCTION experience_extranet_management_v2_helper.upsert_experience_leisure_additional_data(
332  filter_experience_id TEXT, additional_data_spec JSONB)
333  RETURNS BOOLEAN
334SECURITY DEFINER
335LANGUAGE plpgsql
336AS $$
337BEGIN
338  INSERT INTO experience_extranet_experience.experience_leisure (experience_id) VALUES (filter_experience_id)
339  ON CONFLICT (experience_id) DO NOTHING;
340
341  RETURN FOUND;
342END;
343$$;
344
345
346
347DO $$
348  BEGIN
349    ALTER TABLE IF EXISTS experience_extranet_ticket.ticket_refund_policy
350      ADD COLUMN num_of_days_prior_to INTEGER;
351    EXCEPTION
352      WHEN duplicate_column THEN RAISE NOTICE 'column num_of_days_prior_to already exists';
353  END;
354$$;
355
356CREATE OR REPLACE FUNCTION experience_extranet_ticket_v1_helper.get_ticket_refund_policy(filter_ticket_id TEXT)
357  RETURNS JSONB
358SECURITY DEFINER
359LANGUAGE SQL
360AS $$
361SELECT to_jsonb(array_agg(res))
362FROM (SELECT *, jsonb_build_object('days_from', trp.num_of_days_prior,
363                                   'days_to', COALESCE(trp.num_of_days_prior_to, trp.num_of_days_prior)) AS num_of_days_range_prior
364      FROM experience_extranet_ticket.ticket_refund_policy trp
365      WHERE trp.ticket_id = filter_ticket_id
366        AND trp.active = TRUE
367      ORDER BY trp.num_of_days_prior) AS res;
368$$;
369
370
371CREATE OR REPLACE FUNCTION experience_extranet_ticket_v2_api.insert_ticket(ticket_spec JSONB)
372  RETURNS TEXT
373SECURITY DEFINER
374LANGUAGE plpgsql
375AS $$
376DECLARE
377  policy            jsonb;
378  time_slot         jsonb;
379  exp_ticket_id     TEXT;
380  group_list        jsonb;
381  group_detail_list jsonb;
382  redeemable        jsonb;
383BEGIN
384  exp_ticket_id := concat(ticket_spec ->> 'experience_id', '.',
385                          nextval('experience_extranet_ticket.experience_ticket_seq'));
386
387  INSERT INTO experience_extranet_ticket.ticket (
388    ticket_id,
389    experience_id,
390    ticket_type,
391    ticket_barcode_type,
392    ticket_name,
393    ticket_description,
394    maximum_adult_size,
395    minimum_adult_size,
396    maximum_child_size,
397    minimum_child_size,
398    group_size,
399    refundable,
400    active,
401    total_inventory_qty,
402    voucher_format,
403    voucher_fulfillment,
404    issuance_approval,
405    ticket_guest_type,
406    agent_url,
407    hold_booking_time,
408    deleted,
409    voucher_usage_validity,
410    voucher_sales_validity,
411    date_of_birth,
412    reservation,
413    inventory_type,
414    voucher_type,
415    term_condition,
416    voucher_code_format,
417    reservation_day,
418    best_deal,
419    subtitle,
420    direct_entrance,
421    ticket_has_commission,
422    commission_rate,
423    multi_supplier,
424    how_to_redeem_description,
425    need_self_identifier,
426    self_identifier_required_type,
427    self_identifier_type,
428    price_include,
429    price_exclude,
430    reservation_time,
431    min_reservation_day,
432    need_booking_options,
433    pushed_to_aggregator
434  )
435  VALUES (
436    exp_ticket_id,
437    ticket_spec ->> 'experience_id',
438    ticket_spec ->> 'ticket_type',
439    ticket_spec ->> 'ticket_barcode_type',
440    ticket_spec ->> 'ticket_name',
441    ticket_spec ->> 'ticket_description',
442    (ticket_spec ->> 'maximum_adult_size') :: INTEGER,
443    (ticket_spec ->> 'minimum_adult_size') :: INTEGER,
444    (ticket_spec ->> 'maximum_child_size') :: INTEGER,
445    (ticket_spec ->> 'minimum_child_size') :: INTEGER,
446    case (ticket_spec ->> 'group_size') :: INTEGER
447    when 0
448      then 1
449    else (ticket_spec ->> 'group_size') :: INTEGER end,
450    (ticket_spec ->> 'refundable') :: BOOLEAN,
451    (ticket_spec ->> 'active') :: BOOLEAN,
452    (ticket_spec ->> 'total_inventory_qty') :: INTEGER,
453    COALESCE(ticket_spec ->> 'voucher_format', 'BARCODE'),
454    COALESCE(ticket_spec ->> 'voucher_fulfillment', 'AUTOMATIC'),
455    COALESCE((ticket_spec ->> 'issuance_approval') :: BOOLEAN, FALSE),
456    'SPECIFIC',
457    ticket_spec ->> 'agent_url',
458    (ticket_spec ->> 'hold_booking_time') :: BIGINT,
459    FALSE,
460    CASE WHEN (ticket_spec -> 'voucher_usage_validity' IS NULL OR ticket_spec -> 'voucher_usage_validity' = '{}')
461      THEN NULL
462    ELSE
463        row (ticket_spec -> 'voucher_usage_validity' ->> 'validity_days', ticket_spec -> 'voucher_usage_validity' ->>
464                                                                          'validity_type',
465        ticket_spec -> 'voucher_usage_validity' ->> 'validity_period_from', ticket_spec -> 'voucher_usage_validity' ->>
466                                                                            'validity_period_to') :: experience_extranet_ticket.voucher_validity_policy
467    END,
468    CASE WHEN (ticket_spec -> 'voucher_sales_validity' IS NULL OR ticket_spec -> 'voucher_sales_validity' = '{}')
469      THEN NULL
470    ELSE
471        row (ticket_spec -> 'voucher_sales_validity' ->> 'validity_days', ticket_spec -> 'voucher_sales_validity' ->>
472                                                                          'validity_type',
473        ticket_spec -> 'voucher_sales_validity' ->> 'validity_period_from', ticket_spec -> 'voucher_sales_validity' ->>
474                                                                            'validity_period_to') :: experience_extranet_ticket.voucher_validity_policy
475    END,
476    ticket_spec ->> 'date_of_birth',
477    ticket_spec ->> 'reservation',
478    ticket_spec ->> 'inventory_type',
479    ticket_spec ->> 'voucher_type',
480    ticket_spec ->> 'term_condition',
481    ticket_spec ->> 'voucher_code_format',
482    (ticket_spec ->> 'reservation_day') :: INTEGER,
483    (ticket_spec ->> 'best_deal') :: BOOLEAN,
484    ticket_spec ->> 'subtitle',
485    (ticket_spec ->> 'direct_entrance') :: BOOLEAN,
486    COALESCE((ticket_spec ->> 'ticket_has_commission') :: BOOLEAN, FALSE),
487    (ticket_spec ->> 'commission_rate') :: REAL,
488    (ticket_spec ->> 'multi_supplier') :: BOOLEAN,
489    ticket_spec ->> 'how_to_redeem_description',
490    COALESCE((ticket_spec ->> 'need_self_identifier') :: BOOLEAN, FALSE),
491    ticket_spec ->> 'self_identifier_required_type',
492    ticket_spec ->> 'self_identifier_type',
493    ticket_spec ->> 'price_include',
494    ticket_spec ->> 'price_exclude',
495    ROW(ticket_spec -> 'reservation_time' ->> 'hour', ticket_spec -> 'reservation_time' ->> 'minute')
496    :: experience_extranet_experience.hour_minute,
497    COALESCE((ticket_spec ->> 'min_reservation_day') :: INTEGER, 0),
498    COALESCE((ticket_spec ->> 'need_booking_options') :: BOOLEAN, FALSE),
499    COALESCE((ticket_spec ->> 'pushed_to_aggregator') :: BOOLEAN, FALSE)
500  );
501
502  FOR policy IN SELECT jsonb_array_elements(ticket_spec -> 'refund_policies') LOOP
503    INSERT INTO experience_extranet_ticket.ticket_refund_policy (
504      ticket_id,
505      num_of_days_prior,
506      num_of_days_prior_to,
507      percentage,
508      active
509    )
510    VALUES (
511      exp_ticket_id,
512      (COALESCE(policy->'num_of_days_range_prior'->>'days_from', policy->>'num_of_days_prior'))::INTEGER,
513      (COALESCE(policy->'num_of_days_range_prior'->>'days_to', policy->>'num_of_days_prior'))::INTEGER,
514      (policy ->> 'percentage') :: DOUBLE PRECISION,
515      (policy ->> 'active') :: BOOLEAN
516    );
517  END LOOP;
518
519  FOR time_slot IN SELECT jsonb_array_elements(ticket_spec -> 'time_slots') LOOP
520    INSERT INTO experience_extranet_ticket.ticket_time_slot (
521      ticket_id,
522      open_time,
523      close_time
524    )
525    VALUES (
526      exp_ticket_id,
527      row (time_slot -> 'open_time' ->> 'hour', time_slot -> 'open_time' ->>
528                                                'minute') :: experience_extranet_experience.hour_minute,
529      row (time_slot -> 'close_time' ->> 'hour', time_slot -> 'close_time' ->>
530                                                'minute') :: experience_extranet_experience.hour_minute
531    );
532  END LOOP;
533
534  FOR group_list IN SELECT jsonb_array_elements(ticket_spec -> 'ticket_customer_group_list') LOOP
535    FOR group_detail_list IN SELECT jsonb_array_elements(group_list -> 'ticket_customer_group_detail_list') LOOP
536      INSERT INTO experience_extranet_content.ticket_customer_group_mapping (
537        ticket_id,
538        group_unit_id,
539        group_unit,
540        group_id,
541        group_name,
542        group_description,
543        maximum_size,
544        minimum_size,
545        quantifier
546      )
547      VALUES (
548        exp_ticket_id,
549        COALESCE(group_list ->> 'group_unit_id', REPLACE(UPPER(group_list ->> 'group_unit'), ' ', '_')),
550        group_list ->> 'group_unit',
551        COALESCE(group_detail_list ->> 'group_id', REPLACE(UPPER(group_detail_list ->> 'group_name'), ' ', '_')),
552        group_detail_list ->> 'group_name',
553        group_detail_list ->> 'group_description',
554        (group_detail_list ->> 'maximum_size') :: INTEGER,
555        (group_detail_list ->> 'minimum_size') :: INTEGER,
556        (COALESCE(group_detail_list->>'quantifier', '1')) :: INTEGER
557      );
558    END LOOP;
559  END LOOP;
560
561  FOR redeemable IN SELECT jsonb_array_elements(ticket_spec->'redeemable_location_list') LOOP
562    INSERT INTO experience_extranet_ticket.ticket_redeemable_mapping (
563      redeemable_mapping_id,
564      ticket_id,
565      experience_id
566    )
567    VALUES (
568      nextval('experience_extranet_ticket.ticket_redeemable_seq'),
569      exp_ticket_id,
570      redeemable->>'experience_id'
571    );
572  END LOOP;
573
574  PERFORM experience_extranet_ticket_v2_helper.upsert_ticket_additional_data(exp_ticket_id, ticket_spec);
575
576  PERFORM experience_extranet_ticket_v2_helper.upsert_ticket_term_condition(exp_ticket_id, ticket_spec -> 'ticket_term_condition');
577
578  PERFORM experience_extranet_ticket_v2_helper.upsert_ticket_redemption(exp_ticket_id, ticket_spec -> 'ticket_redemption');
579
580  IF jsonb_typeof(ticket_spec -> 'booking_options') = 'array'
581  THEN
582    PERFORM experience_extranet_ticket_v2_helper.upsert_booking_options(exp_ticket_id, ticket_spec);
583  ELSE END IF;
584
585  -- TODO: delete this when proper feature to map SF config is made
586  INSERT INTO experience_extranet_mapping.ticket_crm_message_mapping (ticket_id, template_id, _lut)
587  SELECT ticket_id, 1, now()
588    FROM experience_extranet_experience.experience e
589    JOIN experience_extranet_ticket.ticket t
590      ON e.experience_id = t.experience_id
591    WHERE (e.tags :: TEXT) LIKE '%EASY_RESERVATION%'
592      AND t.ticket_id = exp_ticket_id
593  ON CONFLICT DO NOTHING;
594
595  RETURN exp_ticket_id;
596END;
597$$;
598
599
600CREATE OR REPLACE FUNCTION experience_extranet_ticket_v2_api.update_ticket(ticket_spec JSONB)
601  RETURNS BOOLEAN
602SECURITY DEFINER
603LANGUAGE plpgsql
604AS $$
605DECLARE
606    refund_policies jsonb;
607BEGIN
608
609  UPDATE experience_extranet_ticket.ticket
610  SET
611    --experience_id         = ticket_spec->>'experience_id',        --must not be updated
612    --ticket_type           = ticket_spec->>'ticket_type',          --must not be updated
613    --ticket_barcode_type   = ticket_spec->>'ticket_barcode_type',  --must not be updated
614    ticket_name            = ticket_spec ->> 'ticket_name',
615    ticket_description     = ticket_spec ->> 'ticket_description',
616    maximum_adult_size     = (ticket_spec ->> 'maximum_adult_size') :: INTEGER,
617    minimum_adult_size     = (ticket_spec ->> 'minimum_adult_size') :: INTEGER,
618    maximum_child_size     = (ticket_spec ->> 'maximum_child_size') :: INTEGER,
619    minimum_child_size     = (ticket_spec ->> 'minimum_child_size') :: INTEGER,
620    group_size             = case (ticket_spec ->> 'group_size') :: INTEGER
621                             when 0
622                               then 1
623                             else (ticket_spec ->> 'group_size') :: INTEGER end,
624    refundable             = (ticket_spec ->> 'refundable') :: BOOLEAN,
625    active                 = (ticket_spec ->> 'active') :: BOOLEAN,
626    total_inventory_qty    = (ticket_spec ->> 'total_inventory_qty') :: INTEGER,
627    --voucher_format        = COALESCE(ticket_spec->>'voucher_format', 'BARCODE'),        --must not be updated
628    --voucher_fulfillment   = COALESCE(ticket_spec->>'voucher_fulfillment', 'AUTOMATIC'), --must not be updated
629    issuance_approval      = COALESCE((ticket_spec ->> 'issuance_approval') :: BOOLEAN, FALSE),
630    --ticket_guest_type     = COALESCE(ticket_spec->>'ticket_guest_type', 'GENERIC'),     --must not be updated
631    agent_url              = ticket_spec ->> 'agent_url',
632    hold_booking_time      = (ticket_spec ->> 'hold_booking_time') :: BIGINT,
633    voucher_usage_validity = CASE WHEN (ticket_spec -> 'voucher_usage_validity' IS NULL OR
634                                        ticket_spec -> 'voucher_usage_validity' = '{}')
635      THEN NULL
636                             ELSE
637                                 row (ticket_spec -> 'voucher_usage_validity' ->> 'validity_days',
638                                 ticket_spec -> 'voucher_usage_validity' ->> 'validity_type',
639                                 ticket_spec -> 'voucher_usage_validity' ->> 'validity_period_from',
640                                 ticket_spec -> 'voucher_usage_validity' ->>
641                                 'validity_period_to') :: experience_extranet_ticket.voucher_validity_policy
642                             END,
643    voucher_sales_validity = CASE WHEN (ticket_spec -> 'voucher_sales_validity' IS NULL OR
644                                        ticket_spec -> 'voucher_sales_validity' = '{}')
645      THEN NULL
646                             ELSE
647                                 row (ticket_spec -> 'voucher_sales_validity' ->> 'validity_days',
648                                 ticket_spec -> 'voucher_sales_validity' ->> 'validity_type',
649                                 ticket_spec -> 'voucher_sales_validity' ->> 'validity_period_from',
650                                 ticket_spec -> 'voucher_sales_validity' ->>
651                                 'validity_period_to') :: experience_extranet_ticket.voucher_validity_policy
652                             END,
653    date_of_birth          = ticket_spec ->> 'date_of_birth',
654    reservation            = ticket_spec ->> 'reservation',
655    inventory_type         = ticket_spec ->> 'inventory_type',
656    voucher_type           = ticket_spec ->> 'voucher_type',
657    term_condition         = ticket_spec ->> 'term_condition',
658    voucher_code_format    = ticket_spec ->> 'voucher_code_format',
659    reservation_day        = (ticket_spec ->> 'reservation_day') :: INTEGER,
660    best_deal              = (ticket_spec ->> 'best_deal') :: BOOLEAN,
661    subtitle               = ticket_spec ->> 'subtitle',
662    direct_entrance        = (ticket_spec ->> 'direct_entrance') :: BOOLEAN,
663--  ticket_has_commission  = COALESCE((ticket_spec ->> 'ticket_has_commission') :: BOOLEAN, FALSE),
664    commission_rate        = (ticket_spec ->> 'commission_rate') :: DOUBLE PRECISION,
665    multi_supplier         = (ticket_spec ->> 'multi_supplier') :: BOOLEAN,
666    how_to_redeem_description = ticket_spec ->> 'how_to_redeem_description',
667    need_self_identifier   = COALESCE((ticket_spec ->> 'need_self_identifier') :: BOOLEAN, FALSE),
668    self_identifier_required_type = ticket_spec ->> 'self_identifier_required_type',
669    self_identifier_type   = ticket_spec ->> 'self_identifier_type',
670    price_include         = ticket_spec ->> 'price_include',
671    price_exclude         = ticket_spec ->> 'price_exclude',
672    reservation_time      = ROW(ticket_spec -> 'reservation_time' ->> 'hour', ticket_spec -> 'reservation_time' ->> 'minute')
673                            :: experience_extranet_experience.hour_minute,
674    min_reservation_day   = COALESCE((ticket_spec ->> 'min_reservation_day') :: INTEGER, 0),
675    need_booking_options   = COALESCE((ticket_spec ->> 'need_booking_options') :: BOOLEAN, FALSE),
676    pushed_to_aggregator   = COALESCE((ticket_spec ->> 'pushed_to_aggregator') :: BOOLEAN, FALSE)
677  WHERE experience_extranet_ticket.ticket.ticket_id = (ticket_spec ->> 'ticket_id');
678
679  DELETE FROM experience_extranet_ticket.ticket_refund_policy
680  WHERE NOT EXISTS(
681      SELECT num_of_days_prior
682      FROM jsonb_array_elements(ticket_spec -> 'refund_policies') AS elem
683      WHERE num_of_days_prior = (elem ->> 'num_of_days_prior') :: INTEGER
684  ) AND ticket_id = ticket_spec ->> 'ticket_id';
685
686  FOR refund_policies IN SELECT jsonb_array_elements(ticket_spec -> 'refund_policies') LOOP
687    INSERT INTO experience_extranet_ticket.ticket_refund_policy (
688    ticket_id,
689    num_of_days_prior,
690    num_of_days_prior_to,
691    percentage,
692    active
693  )
694    SELECT
695      ticket_spec ->> 'ticket_id',
696      COALESCE(refund_policies -> 'num_of_days_range_prior' ->> 'days_from', refund_policies ->> 'num_of_days_prior')::INTEGER,
697      COALESCE(refund_policies -> 'num_of_days_range_prior' ->> 'days_to', refund_policies ->> 'num_of_days_prior')::INTEGER,
698      (refund_policies ->> 'percentage') :: DOUBLE PRECISION,
699      (refund_policies ->> 'active') :: BOOLEAN
700
701  ON CONFLICT (ticket_id, num_of_days_prior)
702    DO UPDATE
703      SET
704        percentage = EXCLUDED.percentage,
705        num_of_days_prior_to = EXCLUDED.num_of_days_prior_to,
706        active     = EXCLUDED.active;
707  END LOOP;
708
709  DELETE FROM experience_extranet_ticket.ticket_time_slot
710  WHERE ticket_id = ticket_spec ->> 'ticket_id';
711
712
713  INSERT INTO experience_extranet_ticket.ticket_time_slot (
714    ticket_id,
715    open_time,
716    close_time
717  )
718    SELECT
719      ticket_id,
720        row (open_time_hour, open_time_minute) :: experience_extranet_experience.hour_minute,
721        row (close_time_hour, close_time_minute) :: experience_extranet_experience.hour_minute
722    FROM (
723           SELECT
724             ticket_spec ->> 'ticket_id' AS "ticket_id",
725             (jsonb_array_elements(ticket_spec -> 'time_slots') -> 'open_time' ->>
726              'hour') :: INTEGER         AS "open_time_hour",
727             (jsonb_array_elements(ticket_spec -> 'time_slots') -> 'open_time' ->>
728              'minute') :: INTEGER       AS "open_time_minute",
729             (jsonb_array_elements(ticket_spec -> 'time_slots') -> 'close_time' ->>
730              'hour') :: INTEGER         AS "close_time_hour",
731             (jsonb_array_elements(ticket_spec -> 'time_slots') -> 'close_time' ->>
732              'minute') :: INTEGER       AS "close_time_minute"
733         ) ticket_time_slot
734
735  ON CONFLICT DO NOTHING;
736
737  PERFORM experience_extranet_ticket_v2_helper.upsert_ticket_additional_data(ticket_spec ->> 'ticket_id', ticket_spec);
738
739  PERFORM experience_extranet_ticket_v2_helper.upsert_ticket_customer_group(ticket_spec);
740
741  PERFORM experience_extranet_ticket_v2_helper.upsert_ticket_redeemable_mapping(ticket_spec);
742
743  PERFORM experience_extranet_ticket_v2_helper.upsert_ticket_term_condition(ticket_spec ->> 'ticket_id', ticket_spec -> 'ticket_term_condition');
744
745  PERFORM experience_extranet_ticket_v2_helper.upsert_ticket_redemption(ticket_spec ->> 'ticket_id', ticket_spec -> 'ticket_redemption');
746
747  IF jsonb_typeof(ticket_spec -> 'booking_options') = 'array'
748  THEN
749    PERFORM experience_extranet_ticket_v2_helper.upsert_booking_options(ticket_spec ->> 'ticket_id', ticket_spec);
750  ELSE END IF;
751
752  RETURN FOUND;
753
754END;
755$$;
756
757
758CREATE OR REPLACE FUNCTION experience_extranet_ticket_v2_api.insert_ticket(ticket_spec jsonb) returns text
759	security definer
760	language plpgsql
761as $$
762DECLARE
763  policy            jsonb;
764  time_slot         jsonb;
765  exp_ticket_id     TEXT;
766  group_list        jsonb;
767  group_detail_list jsonb;
768  redeemable        jsonb;
769BEGIN
770  exp_ticket_id := concat(ticket_spec ->> 'experience_id', '.',
771                          nextval('experience_extranet_ticket.experience_ticket_seq'));
772
773  INSERT INTO experience_extranet_ticket.ticket (
774    ticket_id,
775    experience_id,
776    ticket_type,
777    ticket_barcode_type,
778    ticket_name,
779    ticket_description,
780    maximum_adult_size,
781    minimum_adult_size,
782    maximum_child_size,
783    minimum_child_size,
784    group_size,
785    refundable,
786    active,
787    total_inventory_qty,
788    voucher_format,
789    voucher_fulfillment,
790    issuance_approval,
791    ticket_guest_type,
792    agent_url,
793    hold_booking_time,
794    deleted,
795    voucher_usage_validity,
796    voucher_sales_validity,
797    date_of_birth,
798    reservation,
799    inventory_type,
800    voucher_type,
801    term_condition,
802    voucher_code_format,
803    reservation_day,
804    best_deal,
805    subtitle,
806    direct_entrance,
807    ticket_has_commission,
808    commission_rate,
809    multi_supplier,
810    how_to_redeem_description,
811    need_self_identifier,
812    self_identifier_required_type,
813    self_identifier_type,
814    price_include,
815    price_exclude,
816    reservation_time,
817    min_reservation_day,
818    need_booking_options,
819    pushed_to_aggregator
820  )
821  VALUES (
822    exp_ticket_id,
823    ticket_spec ->> 'experience_id',
824    ticket_spec ->> 'ticket_type',
825    ticket_spec ->> 'ticket_barcode_type',
826    ticket_spec ->> 'ticket_name',
827    ticket_spec ->> 'ticket_description',
828    (ticket_spec ->> 'maximum_adult_size') :: INTEGER,
829    (ticket_spec ->> 'minimum_adult_size') :: INTEGER,
830    (ticket_spec ->> 'maximum_child_size') :: INTEGER,
831    (ticket_spec ->> 'minimum_child_size') :: INTEGER,
832    case (ticket_spec ->> 'group_size') :: INTEGER
833    when 0
834      then 1
835    else (ticket_spec ->> 'group_size') :: INTEGER end,
836    (ticket_spec ->> 'refundable') :: BOOLEAN,
837    (ticket_spec ->> 'active') :: BOOLEAN,
838    (ticket_spec ->> 'total_inventory_qty') :: INTEGER,
839    COALESCE(ticket_spec ->> 'voucher_format', 'BARCODE'),
840    COALESCE(ticket_spec ->> 'voucher_fulfillment', 'AUTOMATIC'),
841    COALESCE((ticket_spec ->> 'issuance_approval') :: BOOLEAN, FALSE),
842    'SPECIFIC',
843    ticket_spec ->> 'agent_url',
844    (ticket_spec ->> 'hold_booking_time') :: BIGINT,
845    FALSE,
846    CASE WHEN (ticket_spec -> 'voucher_usage_validity' IS NULL OR ticket_spec -> 'voucher_usage_validity' = '{}')
847      THEN NULL
848    ELSE
849        row (ticket_spec -> 'voucher_usage_validity' ->> 'validity_days', ticket_spec -> 'voucher_usage_validity' ->>
850                                                                          'validity_type',
851        ticket_spec -> 'voucher_usage_validity' ->> 'validity_period_from', ticket_spec -> 'voucher_usage_validity' ->>
852                                                                            'validity_period_to') :: experience_extranet_ticket.voucher_validity_policy
853    END,
854    CASE WHEN (ticket_spec -> 'voucher_sales_validity' IS NULL OR ticket_spec -> 'voucher_sales_validity' = '{}')
855      THEN NULL
856    ELSE
857        row (ticket_spec -> 'voucher_sales_validity' ->> 'validity_days', ticket_spec -> 'voucher_sales_validity' ->>
858                                                                          'validity_type',
859        ticket_spec -> 'voucher_sales_validity' ->> 'validity_period_from', ticket_spec -> 'voucher_sales_validity' ->>
860                                                                            'validity_period_to') :: experience_extranet_ticket.voucher_validity_policy
861    END,
862    ticket_spec ->> 'date_of_birth',
863    ticket_spec ->> 'reservation',
864    ticket_spec ->> 'inventory_type',
865    ticket_spec ->> 'voucher_type',
866    ticket_spec ->> 'term_condition',
867    ticket_spec ->> 'voucher_code_format',
868    (ticket_spec ->> 'reservation_day') :: INTEGER,
869    (ticket_spec ->> 'best_deal') :: BOOLEAN,
870    ticket_spec ->> 'subtitle',
871    (ticket_spec ->> 'direct_entrance') :: BOOLEAN,
872    COALESCE((ticket_spec ->> 'ticket_has_commission') :: BOOLEAN, FALSE),
873    (ticket_spec ->> 'commission_rate') :: REAL,
874    (ticket_spec ->> 'multi_supplier') :: BOOLEAN,
875    ticket_spec ->> 'how_to_redeem_description',
876    COALESCE((ticket_spec ->> 'need_self_identifier') :: BOOLEAN, FALSE),
877    ticket_spec ->> 'self_identifier_required_type',
878    ticket_spec ->> 'self_identifier_type',
879    ticket_spec ->> 'price_include',
880    ticket_spec ->> 'price_exclude',
881    ROW(ticket_spec -> 'reservation_time' ->> 'hour', ticket_spec -> 'reservation_time' ->> 'minute')
882    :: experience_extranet_experience.hour_minute,
883    COALESCE((ticket_spec ->> 'min_reservation_day') :: INTEGER, 0),
884    COALESCE((ticket_spec ->> 'need_booking_options') :: BOOLEAN, FALSE),
885    COALESCE((ticket_spec ->> 'pushed_to_aggregator') :: BOOLEAN, FALSE)
886  );
887
888  FOR policy IN SELECT jsonb_array_elements(ticket_spec -> 'refund_policies') LOOP
889    INSERT INTO experience_extranet_ticket.ticket_refund_policy (
890      ticket_id,
891      num_of_days_prior,
892      percentage,
893      active
894    )
895    VALUES (
896      exp_ticket_id,
897      (policy ->> 'num_of_days_prior') :: INTEGER,
898      (policy ->> 'percentage') :: DOUBLE PRECISION,
899      (policy ->> 'active') :: BOOLEAN
900    );
901  END LOOP;
902
903  FOR time_slot IN SELECT jsonb_array_elements(ticket_spec -> 'time_slots') LOOP
904    INSERT INTO experience_extranet_ticket.ticket_time_slot (
905      ticket_id,
906      open_time,
907      close_time
908    )
909    VALUES (
910      exp_ticket_id,
911      row (time_slot -> 'open_time' ->> 'hour', time_slot -> 'open_time' ->>
912                                                'minute') :: experience_extranet_experience.hour_minute,
913      row (time_slot -> 'close_time' ->> 'hour', time_slot -> 'close_time' ->>
914                                                'minute') :: experience_extranet_experience.hour_minute
915    );
916  END LOOP;
917
918  FOR group_list IN SELECT jsonb_array_elements(ticket_spec -> 'ticket_customer_group_list') LOOP
919    FOR group_detail_list IN SELECT jsonb_array_elements(group_list -> 'ticket_customer_group_detail_list') LOOP
920      INSERT INTO experience_extranet_content.ticket_customer_group_mapping (
921        ticket_id,
922        group_unit_id,
923        group_unit,
924        group_id,
925        group_name,
926        group_description,
927        maximum_size,
928        minimum_size,
929        quantifier
930      )
931      VALUES (
932        exp_ticket_id,
933        COALESCE(group_list ->> 'group_unit_id', REPLACE(UPPER(group_list ->> 'group_unit'), ' ', '_')),
934        group_list ->> 'group_unit',
935        COALESCE(group_detail_list ->> 'group_id', REPLACE(UPPER(group_detail_list ->> 'group_name'), ' ', '_')),
936        group_detail_list ->> 'group_name',
937        group_detail_list ->> 'group_description',
938        (group_detail_list ->> 'maximum_size') :: INTEGER,
939        (group_detail_list ->> 'minimum_size') :: INTEGER,
940        (COALESCE(group_detail_list->>'quantifier', '1')) :: INTEGER
941      );
942    END LOOP;
943  END LOOP;
944
945  FOR redeemable IN SELECT jsonb_array_elements(ticket_spec->'redeemable_location_list') LOOP
946    INSERT INTO experience_extranet_ticket.ticket_redeemable_mapping (
947      redeemable_mapping_id,
948      ticket_id,
949      experience_id
950    )
951    VALUES (
952      nextval('experience_extranet_ticket.ticket_redeemable_seq'),
953      exp_ticket_id,
954      redeemable->>'experience_id'
955    );
956  END LOOP;
957
958  PERFORM experience_extranet_ticket_v2_helper.upsert_ticket_additional_data(exp_ticket_id, ticket_spec);
959
960  PERFORM experience_extranet_ticket_v2_helper.upsert_ticket_term_condition(exp_ticket_id, ticket_spec -> 'ticket_term_condition');
961
962  PERFORM experience_extranet_ticket_v2_helper.upsert_ticket_redemption(exp_ticket_id, ticket_spec -> 'ticket_redemption');
963
964  IF jsonb_typeof(ticket_spec -> 'booking_options') = 'array'
965  THEN
966    PERFORM experience_extranet_ticket_v2_helper.upsert_booking_options(exp_ticket_id, ticket_spec);
967  ELSE END IF;
968
969  -- TODO: delete this when proper feature to map SF config is made
970  INSERT INTO experience_extranet_mapping.ticket_crm_message_mapping (ticket_id, template_id, _lut)
971  SELECT ticket_id, 1, now()
972    FROM experience_extranet_experience.experience e
973    JOIN experience_extranet_ticket.ticket t
974      ON e.experience_id = t.experience_id
975    WHERE (e.tags :: TEXT) LIKE '%EASY_RESERVATION%'
976      AND t.ticket_id = exp_ticket_id
977  ON CONFLICT DO NOTHING;
978
979  RETURN exp_ticket_id;
980END;
981$$;
982
983CREATE OR REPLACE FUNCTION experience_extranet_ticket_v2_api.insert_ticket(ticket_spec jsonb) returns text
984	security definer
985	language plpgsql
986as $$
987DECLARE
988  policy            jsonb;
989  time_slot         jsonb;
990  exp_ticket_id     TEXT;
991  group_list        jsonb;
992  group_detail_list jsonb;
993  redeemable        jsonb;
994BEGIN
995  exp_ticket_id := concat(ticket_spec ->> 'experience_id', '.',
996                          nextval('experience_extranet_ticket.experience_ticket_seq'));
997
998  INSERT INTO experience_extranet_ticket.ticket (
999    ticket_id,
1000    experience_id,
1001    ticket_type,
1002    ticket_barcode_type,
1003    ticket_name,
1004    ticket_description,
1005    maximum_adult_size,
1006    minimum_adult_size,
1007    maximum_child_size,
1008    minimum_child_size,
1009    group_size,
1010    refundable,
1011    active,
1012    total_inventory_qty,
1013    voucher_format,
1014    voucher_fulfillment,
1015    issuance_approval,
1016    ticket_guest_type,
1017    agent_url,
1018    hold_booking_time,
1019    deleted,
1020    voucher_usage_validity,
1021    voucher_sales_validity,
1022    date_of_birth,
1023    reservation,
1024    inventory_type,
1025    voucher_type,
1026    term_condition,
1027    voucher_code_format,
1028    reservation_day,
1029    best_deal,
1030    subtitle,
1031    direct_entrance,
1032    ticket_has_commission,
1033    commission_rate,
1034    multi_supplier,
1035    how_to_redeem_description,
1036    need_self_identifier,
1037    self_identifier_required_type,
1038    self_identifier_type,
1039    price_include,
1040    price_exclude,
1041    reservation_time,
1042    min_reservation_day,
1043    need_booking_options,
1044    pushed_to_aggregator
1045  )
1046  VALUES (
1047    exp_ticket_id,
1048    ticket_spec ->> 'experience_id',
1049    ticket_spec ->> 'ticket_type',
1050    ticket_spec ->> 'ticket_barcode_type',
1051    ticket_spec ->> 'ticket_name',
1052    ticket_spec ->> 'ticket_description',
1053    (ticket_spec ->> 'maximum_adult_size') :: INTEGER,
1054    (ticket_spec ->> 'minimum_adult_size') :: INTEGER,
1055    (ticket_spec ->> 'maximum_child_size') :: INTEGER,
1056    (ticket_spec ->> 'minimum_child_size') :: INTEGER,
1057    case (ticket_spec ->> 'group_size') :: INTEGER
1058    when 0
1059      then 1
1060    else (ticket_spec ->> 'group_size') :: INTEGER end,
1061    (ticket_spec ->> 'refundable') :: BOOLEAN,
1062    (ticket_spec ->> 'active') :: BOOLEAN,
1063    (ticket_spec ->> 'total_inventory_qty') :: INTEGER,
1064    COALESCE(ticket_spec ->> 'voucher_format', 'BARCODE'),
1065    COALESCE(ticket_spec ->> 'voucher_fulfillment', 'AUTOMATIC'),
1066    COALESCE((ticket_spec ->> 'issuance_approval') :: BOOLEAN, FALSE),
1067    'SPECIFIC',
1068    ticket_spec ->> 'agent_url',
1069    (ticket_spec ->> 'hold_booking_time') :: BIGINT,
1070    FALSE,
1071    CASE WHEN (ticket_spec -> 'voucher_usage_validity' IS NULL OR ticket_spec -> 'voucher_usage_validity' = '{}')
1072      THEN NULL
1073    ELSE
1074        row (ticket_spec -> 'voucher_usage_validity' ->> 'validity_days', ticket_spec -> 'voucher_usage_validity' ->>
1075                                                                          'validity_type',
1076        ticket_spec -> 'voucher_usage_validity' ->> 'validity_period_from', ticket_spec -> 'voucher_usage_validity' ->>
1077                                                                            'validity_period_to') :: experience_extranet_ticket.voucher_validity_policy
1078    END,
1079    CASE WHEN (ticket_spec -> 'voucher_sales_validity' IS NULL OR ticket_spec -> 'voucher_sales_validity' = '{}')
1080      THEN NULL
1081    ELSE
1082        row (ticket_spec -> 'voucher_sales_validity' ->> 'validity_days', ticket_spec -> 'voucher_sales_validity' ->>
1083                                                                          'validity_type',
1084        ticket_spec -> 'voucher_sales_validity' ->> 'validity_period_from', ticket_spec -> 'voucher_sales_validity' ->>
1085                                                                            'validity_period_to') :: experience_extranet_ticket.voucher_validity_policy
1086    END,
1087    ticket_spec ->> 'date_of_birth',
1088    ticket_spec ->> 'reservation',
1089    ticket_spec ->> 'inventory_type',
1090    ticket_spec ->> 'voucher_type',
1091    ticket_spec ->> 'term_condition',
1092    ticket_spec ->> 'voucher_code_format',
1093    (ticket_spec ->> 'reservation_day') :: INTEGER,
1094    (ticket_spec ->> 'best_deal') :: BOOLEAN,
1095    ticket_spec ->> 'subtitle',
1096    (ticket_spec ->> 'direct_entrance') :: BOOLEAN,
1097    COALESCE((ticket_spec ->> 'ticket_has_commission') :: BOOLEAN, FALSE),
1098    (ticket_spec ->> 'commission_rate') :: REAL,
1099    (ticket_spec ->> 'multi_supplier') :: BOOLEAN,
1100    ticket_spec ->> 'how_to_redeem_description',
1101    COALESCE((ticket_spec ->> 'need_self_identifier') :: BOOLEAN, FALSE),
1102    ticket_spec ->> 'self_identifier_required_type',
1103    ticket_spec ->> 'self_identifier_type',
1104    ticket_spec ->> 'price_include',
1105    ticket_spec ->> 'price_exclude',
1106    ROW(ticket_spec -> 'reservation_time' ->> 'hour', ticket_spec -> 'reservation_time' ->> 'minute')
1107    :: experience_extranet_experience.hour_minute,
1108    COALESCE((ticket_spec ->> 'min_reservation_day') :: INTEGER, 0),
1109    COALESCE((ticket_spec ->> 'need_booking_options') :: BOOLEAN, FALSE),
1110    COALESCE((ticket_spec ->> 'pushed_to_aggregator') :: BOOLEAN, FALSE)
1111  );
1112
1113  FOR policy IN SELECT jsonb_array_elements(ticket_spec -> 'refund_policies') LOOP
1114    INSERT INTO experience_extranet_ticket.ticket_refund_policy (
1115      ticket_id,
1116      num_of_days_prior,
1117      percentage,
1118      active
1119    )
1120    VALUES (
1121      exp_ticket_id,
1122      (policy ->> 'num_of_days_prior') :: INTEGER,
1123      (policy ->> 'percentage') :: DOUBLE PRECISION,
1124      (policy ->> 'active') :: BOOLEAN
1125    );
1126  END LOOP;
1127
1128  FOR time_slot IN SELECT jsonb_array_elements(ticket_spec -> 'time_slots') LOOP
1129    INSERT INTO experience_extranet_ticket.ticket_time_slot (
1130      ticket_id,
1131      open_time,
1132      close_time
1133    )
1134    VALUES (
1135      exp_ticket_id,
1136      row (time_slot -> 'open_time' ->> 'hour', time_slot -> 'open_time' ->>
1137                                                'minute') :: experience_extranet_experience.hour_minute,
1138      row (time_slot -> 'close_time' ->> 'hour', time_slot -> 'close_time' ->>
1139                                                'minute') :: experience_extranet_experience.hour_minute
1140    );
1141  END LOOP;
1142
1143  FOR group_list IN SELECT jsonb_array_elements(ticket_spec -> 'ticket_customer_group_list') LOOP
1144    FOR group_detail_list IN SELECT jsonb_array_elements(group_list -> 'ticket_customer_group_detail_list') LOOP
1145      INSERT INTO experience_extranet_content.ticket_customer_group_mapping (
1146        ticket_id,
1147        group_unit_id,
1148        group_unit,
1149        group_id,
1150        group_name,
1151        group_description,
1152        maximum_size,
1153        minimum_size,
1154        quantifier
1155      )
1156      VALUES (
1157        exp_ticket_id,
1158        COALESCE(group_list ->> 'group_unit_id', REPLACE(UPPER(group_list ->> 'group_unit'), ' ', '_')),
1159        group_list ->> 'group_unit',
1160        COALESCE(group_detail_list ->> 'group_id', REPLACE(UPPER(group_detail_list ->> 'group_name'), ' ', '_')),
1161        group_detail_list ->> 'group_name',
1162        group_detail_list ->> 'group_description',
1163        (group_detail_list ->> 'maximum_size') :: INTEGER,
1164        (group_detail_list ->> 'minimum_size') :: INTEGER,
1165        (COALESCE(group_detail_list->>'quantifier', '1')) :: INTEGER
1166      );
1167    END LOOP;
1168  END LOOP;
1169
1170  FOR redeemable IN SELECT jsonb_array_elements(ticket_spec->'redeemable_location_list') LOOP
1171    INSERT INTO experience_extranet_ticket.ticket_redeemable_mapping (
1172      redeemable_mapping_id,
1173      ticket_id,
1174      experience_id
1175    )
1176    VALUES (
1177      nextval('experience_extranet_ticket.ticket_redeemable_seq'),
1178      exp_ticket_id,
1179      redeemable->>'experience_id'
1180    );
1181  END LOOP;
1182
1183  PERFORM experience_extranet_ticket_v2_helper.upsert_ticket_additional_data(exp_ticket_id, ticket_spec);
1184
1185  PERFORM experience_extranet_ticket_v2_helper.upsert_ticket_term_condition(exp_ticket_id, ticket_spec -> 'ticket_term_condition');
1186
1187  PERFORM experience_extranet_ticket_v2_helper.upsert_ticket_redemption(exp_ticket_id, ticket_spec -> 'ticket_redemption');
1188
1189  IF jsonb_typeof(ticket_spec -> 'booking_options') = 'array'
1190  THEN
1191    PERFORM experience_extranet_ticket_v2_helper.upsert_booking_options(exp_ticket_id, ticket_spec);
1192  ELSE END IF;
1193
1194  -- TODO: delete this when proper feature to map SF config is made
1195  INSERT INTO experience_extranet_mapping.ticket_crm_message_mapping (ticket_id, template_id, _lut)
1196  SELECT ticket_id, 1, now()
1197    FROM experience_extranet_experience.experience e
1198    JOIN experience_extranet_ticket.ticket t
1199      ON e.experience_id = t.experience_id
1200    WHERE (e.tags :: TEXT) LIKE '%EASY_RESERVATION%'
1201      AND t.ticket_id = exp_ticket_id
1202  ON CONFLICT DO NOTHING;
1203
1204  RETURN exp_ticket_id;
1205END;
1206$$;
1207
1208
1209DO $$
1210  BEGIN
1211    ALTER TABLE IF EXISTS experience_extranet_provider_ticket.ticket_refund_policy
1212      ADD COLUMN num_of_days_prior_to INTEGER;
1213    EXCEPTION
1214      WHEN duplicate_column THEN RAISE NOTICE 'column num_of_days_prior_to already exists';
1215  END;
1216$$;
1217
1218CREATE OR REPLACE FUNCTION experience_extranet_provider_ticket_v1_helper.get_ticket_refund_policy(filter_provider_ticket_id TEXT)
1219  RETURNS JSONB
1220SECURITY DEFINER
1221LANGUAGE SQL
1222AS $$
1223SELECT  to_jsonb(array_agg(res))
1224  FROM    (
1225    SELECT  *, jsonb_build_object('days_from', trp.num_of_days_prior,
1226                                  'days_to', COALESCE(trp.num_of_days_prior_to, trp.num_of_days_prior))
1227    FROM    experience_extranet_provider_ticket.ticket_refund_policy trp
1228    WHERE   provider_ticket_id = filter_provider_ticket_id
1229  ) res
1230$$;
1231
1232
1233CREATE OR REPLACE FUNCTION experience_extranet_provider_ticket_v1_helper.upsert_ticket_refund_policy(ticket_refund_policy jsonb) returns boolean
1234	security definer
1235	language sql
1236as $$
1237  WITH res AS (
1238    INSERT INTO experience_extranet_provider_ticket.ticket_refund_policy (
1239      provider_ticket_id,
1240      num_of_days_prior,
1241      num_of_days_prior_to,
1242      percentage
1243    )
1244    VALUES (
1245      ticket_refund_policy->>'provider_ticket_id',
1246      (COALESCE(ticket_refund_policy->'num_of_days_range_prior'->>'days_from', ticket_refund_policy->>'num_of_days_prior'))::INTEGER,
1247      (COALESCE(ticket_refund_policy->'num_of_days_range_prior'->>'days_to', ticket_refund_policy->>'num_of_days_prior'))::INTEGER,
1248      (ticket_refund_policy->>'percentage')::DOUBLE PRECISION
1249    )
1250    ON CONFLICT (provider_ticket_id, num_of_days_prior) DO UPDATE
1251      SET percentage = EXCLUDED.percentage
1252    RETURNING 1
1253  )
1254  SELECT COUNT(*) > 0 FROM res;
1255$$;
1256
1257CREATE OR REPLACE FUNCTION experience_extranet_supplier_v2_api.insert_supplier(supplier_spec jsonb) returns boolean
1258	security definer
1259	language sql
1260AS $$
1261WITH
1262    insert_supplier_result AS (
1263    INSERT INTO experience_extranet_supplier.supplier (
1264      supplier_id,
1265      company_name,
1266      country,
1267      currency,
1268      supplier_tax_id_no,
1269      supplier_is_pkp,
1270      owner_name,
1271      main_company_address,
1272      owner_phone_country_code,
1273      owner_phone_no,
1274      owner_email,
1275      marketing_manager_user_id,
1276      is_active,
1277      agent_code,
1278      supplier_is_pb1,
1279      supplier_is_wht,
1280      wht_rate,
1281      supplier_is_vat,
1282      vat_rate,
1283      agent_operating_license,
1284      agent_profile
1285    )
1286    VALUES (
1287      (supplier_spec ->> 'supplier_id') :: BIGINT,
1288      supplier_spec ->> 'company_name',
1289      supplier_spec ->> 'country',
1290      supplier_spec ->> 'currency',
1291      supplier_spec ->> 'supplier_tax_id_no',
1292      (supplier_spec ->> 'supplier_is_pkp') :: BOOLEAN,
1293      supplier_spec ->> 'owner_name',
1294      supplier_spec ->> 'main_company_address',
1295      supplier_spec ->> 'owner_phone_country_code',
1296      supplier_spec ->> 'owner_phone_no',
1297      supplier_spec ->> 'owner_email',
1298      supplier_spec ->> 'marketing_manager_user_id',
1299      (supplier_spec ->> 'is_active') :: BOOLEAN,
1300      supplier_spec ->> 'agent_code',
1301      (supplier_spec ->> 'supplier_is_pb1') :: BOOLEAN,
1302      (supplier_spec ->> 'supplier_is_wht') :: BOOLEAN,
1303      (supplier_spec ->> 'wht_rate') :: DOUBLE PRECISION,
1304      (supplier_spec ->> 'supplier_is_vat') :: BOOLEAN,
1305      (supplier_spec ->> 'vat_rate') :: DOUBLE PRECISION,
1306      supplier_spec ->> 'agent_operating_license',
1307      supplier_spec ->> 'agent_profile'
1308    )
1309    RETURNING 1
1310  ),
1311    insert_supplier_pic_result AS (
1312    INSERT INTO experience_extranet_supplier.supplier_pic (
1313      supplier_id,
1314      pic_name,
1315      pic_email,
1316      pic_phone_country_code,
1317      pic_phone_no,
1318      pic_position
1319    )
1320      SELECT
1321        (jsonb_array_elements(supplier_spec -> 'supplier_pic') ->> 'supplier_id') :: BIGINT,
1322        jsonb_array_elements(supplier_spec -> 'supplier_pic') ->> 'pic_name',
1323        jsonb_array_elements(supplier_spec -> 'supplier_pic') ->> 'pic_email',
1324        jsonb_array_elements(supplier_spec -> 'supplier_pic') ->> 'pic_phone_country_code',
1325        jsonb_array_elements(supplier_spec -> 'supplier_pic') ->> 'pic_phone_no',
1326        jsonb_array_elements(supplier_spec -> 'supplier_pic') ->> 'pic_position'
1327    RETURNING 1
1328  ),
1329    insert_supplier_payment_info_result AS (
1330    INSERT INTO experience_extranet_supplier.supplier_payment_info (
1331      supplier_id,
1332      payment_cycle,
1333      currency,
1334      bank_id,
1335      bank_other_id,
1336      bank_short_name,
1337      bank_name,
1338      bank_acc_no,
1339      bank_acc_beneficiary_name,
1340      bank_branch,
1341      bank_swift_code,
1342      tvlk_entity_code,
1343      payment_type,
1344      deposit_deduction_type,
1345      invoice_due_date_type,
1346      payment_cycle_day
1347    )
1348      SELECT
1349        supplier_id,
1350        payment_cycle,
1351        currency,
1352        bank_id,
1353        bank_other_id,
1354        bank_short_name,
1355        bank_name,
1356        bank_acc_no,
1357        bank_acc_beneficiary_name,
1358        bank_branch,
1359        bank_swift_code,
1360        tvlk_entity_code,
1361        CASE WHEN payment_type IS NOT NULL
1362          THEN payment_type :: experience_extranet_supplier.PAYMENT_TYPE
1363        ELSE NULL END,
1364        CASE WHEN deposit_deduction_type IS NOT NULL
1365          THEN deposit_deduction_type :: experience_extranet_supplier.DEPOSIT_DEDUCTION_TYPE
1366        ELSE NULL END,
1367        invoice_due_date_type,
1368        payment_cycle_day
1369      FROM (
1370             SELECT
1371               (jsonb_array_elements(supplier_spec -> 'supplier_payment_info') ->>
1372                'supplier_id') :: BIGINT                                                      AS "supplier_id",
1373               jsonb_array_elements(supplier_spec -> 'supplier_payment_info') ->>
1374               'payment_cycle'                                                                AS "payment_cycle",
1375               jsonb_array_elements(supplier_spec -> 'supplier_payment_info') ->>
1376               'currency'                                                                     AS "currency",
1377               jsonb_array_elements(supplier_spec -> 'supplier_payment_info') ->> 'bank_id'   AS "bank_id",
1378               jsonb_array_elements(supplier_spec -> 'supplier_payment_info') ->>
1379               'bank_other_id'                                                                AS "bank_other_id",
1380               jsonb_array_elements(supplier_spec -> 'supplier_payment_info') ->>
1381               'bank_short_name'                                                              AS "bank_short_name",
1382               jsonb_array_elements(supplier_spec -> 'supplier_payment_info') ->> 'bank_name' AS "bank_name",
1383               jsonb_array_elements(supplier_spec -> 'supplier_payment_info') ->>
1384               'bank_acc_no'                                                                  AS "bank_acc_no",
1385               jsonb_array_elements(supplier_spec -> 'supplier_payment_info') ->>
1386               'bank_acc_beneficiary_name'                                                    AS "bank_acc_beneficiary_name",
1387               jsonb_array_elements(supplier_spec -> 'supplier_payment_info') ->>
1388               'bank_branch'                                                                  AS "bank_branch",
1389               jsonb_array_elements(supplier_spec -> 'supplier_payment_info') ->>
1390               'bank_swift_code'                                                              AS "bank_swift_code",
1391               jsonb_array_elements(supplier_spec -> 'supplier_payment_info') ->>
1392               'tvlk_entity_code'                                                             AS "tvlk_entity_code",
1393               jsonb_array_elements(supplier_spec -> 'supplier_payment_info') ->>
1394               'payment_type'                                                                 AS "payment_type",
1395               jsonb_array_elements(supplier_spec -> 'supplier_payment_info') ->>
1396               'deposit_deduction_type'                                                       AS "deposit_deduction_type",
1397               jsonb_array_elements(supplier_spec -> 'supplier_payment_info') ->>
1398               'invoice_due_date_type'                                                        AS "invoice_due_date_type",
1399               (jsonb_array_elements(supplier_spec -> 'supplier_payment_info') ->>
1400               'payment_cycle_day') :: INTEGER                                                AS "payment_cycle_day"
1401           ) spi
1402    RETURNING 1
1403  ),
1404    upsert_supplier_business_category_result AS (
1405      SELECT CASE WHEN experience_extranet_supplier_v2_api.upsert_supplier_business_category(
1406          supplier_spec -> 'supplier_business_category', (supplier_spec ->> 'supplier_id') :: BIGINT
1407      )
1408        THEN 1
1409             ELSE 0 END
1410  ),
1411    upsert_supplier_market_manager_result AS (
1412      SELECT CASE WHEN experience_extranet_supplier_v2_helper.upsert_supplier_market_manager(
1413          supplier_spec -> 'supplier_market_manager', (supplier_spec ->> 'supplier_id') :: BIGINT
1414      )
1415        THEN 1
1416             ELSE 0 END
1417  ),
1418    insert_supplier_tax_id_result AS (
1419        INSERT INTO experience_extranet_supplier.supplier_tax_id (
1420      supplier_id,
1421      supplier_tax_id_no,
1422      supplier_is_pkp,
1423      legal_name,
1424      legal_address
1425    )
1426      SELECT
1427        (supplier_spec ->> 'supplier_id') :: BIGINT,
1428        jsonb_array_elements(supplier_spec -> 'supplier_tax_id_no_list') ->> 'supplier_tax_id_no',
1429        (jsonb_array_elements(supplier_spec -> 'supplier_tax_id_no_list') ->> 'supplier_is_pkp') :: BOOLEAN,
1430        jsonb_array_elements(supplier_spec -> 'supplier_tax_id_no_list') ->> 'legal_name',
1431        jsonb_array_elements(supplier_spec -> 'supplier_tax_id_no_list') ->> 'legal_address'
1432    RETURNING 1
1433  )
1434SELECT COUNT(*) >= 5
1435FROM (
1436       SELECT *
1437       FROM insert_supplier_result
1438       UNION ALL
1439       SELECT *
1440       FROM insert_supplier_pic_result
1441       UNION ALL
1442       SELECT *
1443       FROM insert_supplier_payment_info_result
1444       UNION ALL
1445       SELECT *
1446       FROM upsert_supplier_business_category_result
1447       UNION ALL
1448       SELECT *
1449       FROM upsert_supplier_market_manager_result
1450       UNION ALL
1451       SELECT * FROM insert_supplier_tax_id_result
1452     ) AS res
1453$$;
1454
1455
1456CREATE OR REPLACE FUNCTION experience_extranet_supplier_v1_api.insert_supplier(supplier_spec jsonb) returns boolean
1457	security definer
1458	language plpgsql
1459as $$
1460DECLARE
1461  pic jsonb;
1462BEGIN
1463  INSERT INTO experience_extranet_supplier.supplier(
1464    supplier_id,
1465    company_name,
1466    country,
1467    currency,
1468    supplier_tax_id_no,
1469    supplier_is_pkp,
1470    owner_name,
1471    main_company_address,
1472    owner_phone_country_code,
1473    owner_phone_no,
1474    owner_email,
1475    marketing_manager_user_id,
1476    is_active,
1477    agent_code,
1478    agent_operating_license,
1479    agent_profile
1480  )
1481  VALUES (
1482    (supplier_spec->>'supplier_id')::BIGINT,
1483    supplier_spec->>'company_name',
1484    supplier_spec->>'country',
1485    supplier_spec->>'currency',
1486    supplier_spec->>'supplier_tax_id_no',
1487    (supplier_spec->>'supplier_is_pkp')::BOOLEAN,
1488    supplier_spec->>'owner_name',
1489    supplier_spec->>'main_company_address',
1490    supplier_spec->>'owner_phone_country_code',
1491    supplier_spec->>'owner_phone_no',
1492    supplier_spec->>'owner_email',
1493    supplier_spec->>'marketing_manager_user_id',
1494    (supplier_spec->>'is_active')::BOOLEAN,
1495    supplier_spec->>'agent_code',
1496    supplier_spec->>'agent_operating_license',
1497    supplier_spec->>'agent_profile'
1498  );
1499
1500  FOR pic IN SELECT jsonb_array_elements(supplier_spec->'supplier_pic') LOOP
1501    INSERT INTO experience_extranet_supplier.supplier_pic(
1502      supplier_id,
1503      pic_name,
1504      pic_email,
1505      pic_phone_country_code,
1506      pic_phone_no,
1507      pic_position
1508    )
1509    VALUES (
1510      (pic->>'supplier_id')::BIGINT,
1511      pic->>'pic_name',
1512      pic->>'pic_email',
1513      pic->>'pic_phone_country_code',
1514      pic->>'pic_phone_no',
1515      pic->>'pic_position'
1516    );
1517  END LOOP;
1518
1519  INSERT INTO experience_extranet_supplier.supplier_payment_info(
1520    supplier_id,
1521    payment_cycle,
1522    bank_id,
1523    bank_other_id,
1524    bank_short_name,
1525    bank_name,
1526    bank_acc_no,
1527    bank_acc_beneficiary_name,
1528    bank_branch,
1529    bank_swift_code,
1530    tvlk_entity_code,
1531    payment_type,
1532    deposit_deduction_type,
1533    invoice_due_date_type
1534  )
1535  VALUES (
1536    (supplier_spec->>'supplier_id')::BIGINT,
1537    supplier_spec->'supplier_payment_info'->>'payment_cycle',
1538    supplier_spec->'supplier_payment_info'->>'bank_id',
1539    supplier_spec->'supplier_payment_info'->>'bank_other_id',
1540    supplier_spec->'supplier_payment_info'->>'bank_short_name',
1541    supplier_spec->'supplier_payment_info'->>'bank_name',
1542    supplier_spec->'supplier_payment_info'->>'bank_acc_no',
1543    supplier_spec->'supplier_payment_info'->>'bank_acc_beneficiary_name',
1544    supplier_spec->'supplier_payment_info'->>'bank_branch',
1545    supplier_spec->'supplier_payment_info'->>'bank_swift_code',
1546    supplier_spec->'supplier_payment_info'->>'tvlk_entity_code',
1547    CASE
1548      WHEN supplier_spec->'supplier_payment_info'->>'payment_type' IS NOT NULL THEN
1549        (supplier_spec->'supplier_payment_info'->>'payment_type')::experience_extranet_supplier.payment_type
1550      ELSE
1551        NULL
1552    END,
1553    CASE
1554      WHEN supplier_spec->'supplier_payment_info'->>'deposit_deduction_type' IS NOT NULL THEN
1555        (supplier_spec->'supplier_payment_info'->>'deposit_deduction_type')::experience_extranet_supplier.deposit_deduction_type
1556      ELSE
1557        NULL
1558    END,
1559    supplier_spec->'supplier_payment_info'->>'invoice_due_date_type'
1560  );
1561
1562  PERFORM experience_extranet_supplier_v1_api.upsert_supplier_business_category(supplier_spec->'supplier_business_category', (supplier_spec->>'supplier_id')::BIGINT);
1563
1564  RETURN FOUND;
1565END;
1566$$;
1567
1568CREATE OR REPLACE FUNCTION experience_extranet_supplier_v2_api.update_supplier_info(supplier_spec jsonb) returns boolean
1569	security definer
1570	language plpgsql
1571AS $$
1572DECLARE
1573  pic          JSONB;
1574  payment_info JSONB;
1575  supplier_tax_id JSONB;
1576  supplier_tax_document JSONB;
1577BEGIN
1578  UPDATE experience_extranet_supplier.supplier
1579  SET company_name            = supplier_spec ->> 'company_name',
1580    currency                  = supplier_spec ->> 'currency',
1581    country                   = supplier_spec ->> 'country',
1582    supplier_tax_id_no        = supplier_spec ->> 'supplier_tax_id_no',
1583    supplier_is_pkp           = (supplier_spec ->> 'supplier_is_pkp') :: BOOLEAN,
1584    owner_name                = supplier_spec ->> 'owner_name',
1585    main_company_address      = supplier_spec ->> 'main_company_address',
1586    owner_phone_country_code  = supplier_spec ->> 'owner_phone_country_code',
1587    owner_phone_no            = supplier_spec ->> 'owner_phone_no',
1588    owner_email               = supplier_spec ->> 'owner_email',
1589    marketing_manager_user_id = supplier_spec ->> 'marketing_manager_user_id',
1590    agent_code                = supplier_spec ->> 'agent_code',
1591    supplier_is_pb1           = (supplier_spec ->> 'supplier_is_pb1') :: BOOLEAN,
1592    supplier_is_wht           = (supplier_spec ->> 'supplier_is_wht') :: BOOLEAN,
1593    wht_rate                  = (supplier_spec ->> 'wht_rate') :: DOUBLE PRECISION,
1594    supplier_is_vat           = (supplier_spec ->> 'supplier_is_vat') :: BOOLEAN,
1595    vat_rate                  = (supplier_spec ->> 'vat_rate') :: DOUBLE PRECISION,
1596    agent_operating_license   = supplier_spec ->> 'agent_operating_license',
1597    agent_profile             = supplier_spec ->> 'agent_profile'
1598  WHERE experience_extranet_supplier.supplier.supplier_id = (supplier_spec ->> 'supplier_id') :: BIGINT;
1599
1600  CREATE TEMP TABLE deleted_supplier_pics ON COMMIT DROP AS
1601    SELECT sp.*
1602    FROM experience_extranet_supplier.supplier_pic sp
1603    WHERE NOT EXISTS(
1604      SELECT supplier_pic_id
1605      FROM jsonb_array_elements(supplier_spec -> 'supplier_pic') AS elem
1606      WHERE supplier_pic_id = (elem ->> 'supplier_pic_id') :: BIGINT
1607    ) AND supplier_id = (supplier_spec ->> 'supplier_id') :: BIGINT;
1608
1609  PERFORM experience_extranet_account_v1_helper.deactivate_account_by_user_name((
1610    SELECT array_agg(pic_email)
1611    FROM deleted_supplier_pics
1612  ));
1613
1614  PERFORM experience_extranet_mapping_v2_api.delete_supplier_product_pic_mapping_by_pic_ids((
1615    SELECT array_agg(supplier_pic_id)
1616    FROM deleted_supplier_pics
1617  ));
1618
1619  DELETE FROM experience_extranet_supplier.supplier_pic sp
1620  USING deleted_supplier_pics dspi
1621  WHERE dspi.supplier_pic_id = sp.supplier_pic_id;
1622
1623  FOR pic IN SELECT jsonb_array_elements(supplier_spec -> 'supplier_pic') LOOP
1624    PERFORM
1625      experience_extranet_supplier_v2_helper.upsert_supplier_pic(pic, (supplier_spec ->> 'supplier_id') :: BIGINT);
1626  END LOOP;
1627
1628  DELETE FROM experience_extranet_supplier.supplier_payment_info
1629  WHERE NOT EXISTS(
1630      SELECT supplier_payment_info_id
1631      FROM jsonb_array_elements(supplier_spec -> 'supplier_payment_info') AS elem
1632      WHERE supplier_payment_info_id = (elem ->> 'supplier_payment_info_id') :: BIGINT
1633  ) AND supplier_id = (supplier_spec ->> 'supplier_id') :: BIGINT;
1634
1635  FOR payment_info IN SELECT jsonb_array_elements(supplier_spec -> 'supplier_payment_info') LOOP
1636    PERFORM experience_extranet_supplier_v2_helper.upsert_supplier_payment_info(payment_info, (supplier_spec ->>
1637                                                                                               'supplier_id') :: BIGINT);
1638  END LOOP;
1639
1640  PERFORM
1641    experience_extranet_supplier_v2_api.upsert_supplier_business_category(supplier_spec -> 'supplier_business_category',
1642                                                                          (supplier_spec ->> 'supplier_id') :: BIGINT);
1643
1644  PERFORM
1645    experience_extranet_supplier_v2_helper.upsert_supplier_market_manager(supplier_spec -> 'supplier_market_manager',
1646                                                                          (supplier_spec ->> 'supplier_id') :: BIGINT);
1647
1648  DELETE FROM experience_extranet_supplier.supplier_tax_document WHERE supplier_id = (supplier_spec ->> 'supplier_id')::BIGINT;
1649  DELETE FROM experience_extranet_supplier.supplier_tax_id
1650  WHERE NOT EXISTS(
1651          SELECT supplier_tax_id_no
1652          FROM jsonb_array_elements(supplier_spec -> 'supplier_tax_id_no_list') AS elem
1653          WHERE supplier_tax_id_no = (elem ->> 'supplier_tax_id_no') :: TEXT
1654      ) AND supplier_id = (supplier_spec ->> 'supplier_id') :: BIGINT;
1655
1656  FOR supplier_tax_id IN SELECT jsonb_array_elements(supplier_spec -> 'supplier_tax_id_no_list') LOOP
1657      PERFORM experience_extranet_supplier_v2_helper.upsert_supplier_tax_id(supplier_tax_id,
1658                                                        (supplier_spec ->> 'supplier_id') :: BIGINT);
1659      FOR supplier_tax_document IN SELECT jsonb_array_elements(CASE jsonb_typeof(supplier_tax_id -> 'supplier_tax_document_list') WHEN 'array' THEN supplier_tax_id -> 'supplier_tax_document_list' ELSE '[]' END) LOOP
1660          PERFORM experience_extranet_supplier_v2_helper.upsert_supplier_tax_document(  (supplier_spec ->> 'supplier_id')::BIGINT,
1661                                                                                         supplier_tax_id ->> 'supplier_tax_id_no',
1662                                                                                         supplier_tax_document
1663          );
1664      END LOOP;
1665  END LOOP;
1666  RETURN FOUND;
1667END;
1668$$;
1669
1670
1671CREATE OR REPLACE FUNCTION experience_extranet_supplier_v1_api.insert_supplier(supplier_spec jsonb) returns boolean
1672	security definer
1673	language plpgsql
1674as $$
1675DECLARE
1676  pic jsonb;
1677BEGIN
1678  INSERT INTO experience_extranet_supplier.supplier(
1679    supplier_id,
1680    company_name,
1681    country,
1682    currency,
1683    supplier_tax_id_no,
1684    supplier_is_pkp,
1685    owner_name,
1686    main_company_address,
1687    owner_phone_country_code,
1688    owner_phone_no,
1689    owner_email,
1690    marketing_manager_user_id,
1691    is_active,
1692    agent_code,
1693    agent_operating_license,
1694    agent_profile
1695  )
1696  VALUES (
1697    (supplier_spec->>'supplier_id')::BIGINT,
1698    supplier_spec->>'company_name',
1699    supplier_spec->>'country',
1700    supplier_spec->>'currency',
1701    supplier_spec->>'supplier_tax_id_no',
1702    (supplier_spec->>'supplier_is_pkp')::BOOLEAN,
1703    supplier_spec->>'owner_name',
1704    supplier_spec->>'main_company_address',
1705    supplier_spec->>'owner_phone_country_code',
1706    supplier_spec->>'owner_phone_no',
1707    supplier_spec->>'owner_email',
1708    supplier_spec->>'marketing_manager_user_id',
1709    (supplier_spec->>'is_active')::BOOLEAN,
1710    supplier_spec->>'agent_code',
1711    supplier_spec->>'agent_operating_license',
1712    supplier_spec->>'agent_profile'
1713  );
1714
1715  FOR pic IN SELECT jsonb_array_elements(supplier_spec->'supplier_pic') LOOP
1716    INSERT INTO experience_extranet_supplier.supplier_pic(
1717      supplier_id,
1718      pic_name,
1719      pic_email,
1720      pic_phone_country_code,
1721      pic_phone_no,
1722      pic_position
1723    )
1724    VALUES (
1725      (pic->>'supplier_id')::BIGINT,
1726      pic->>'pic_name',
1727      pic->>'pic_email',
1728      pic->>'pic_phone_country_code',
1729      pic->>'pic_phone_no',
1730      pic->>'pic_position'
1731    );
1732  END LOOP;
1733
1734  INSERT INTO experience_extranet_supplier.supplier_payment_info(
1735    supplier_id,
1736    payment_cycle,
1737    bank_id,
1738    bank_other_id,
1739    bank_short_name,
1740    bank_name,
1741    bank_acc_no,
1742    bank_acc_beneficiary_name,
1743    bank_branch,
1744    bank_swift_code,
1745    tvlk_entity_code,
1746    payment_type,
1747    deposit_deduction_type,
1748    invoice_due_date_type
1749  )
1750  VALUES (
1751    (supplier_spec->>'supplier_id')::BIGINT,
1752    supplier_spec->'supplier_payment_info'->>'payment_cycle',
1753    supplier_spec->'supplier_payment_info'->>'bank_id',
1754    supplier_spec->'supplier_payment_info'->>'bank_other_id',
1755    supplier_spec->'supplier_payment_info'->>'bank_short_name',
1756    supplier_spec->'supplier_payment_info'->>'bank_name',
1757    supplier_spec->'supplier_payment_info'->>'bank_acc_no',
1758    supplier_spec->'supplier_payment_info'->>'bank_acc_beneficiary_name',
1759    supplier_spec->'supplier_payment_info'->>'bank_branch',
1760    supplier_spec->'supplier_payment_info'->>'bank_swift_code',
1761    supplier_spec->'supplier_payment_info'->>'tvlk_entity_code',
1762    CASE
1763      WHEN supplier_spec->'supplier_payment_info'->>'payment_type' IS NOT NULL THEN
1764        (supplier_spec->'supplier_payment_info'->>'payment_type')::experience_extranet_supplier.payment_type
1765      ELSE
1766        NULL
1767    END,
1768    CASE
1769      WHEN supplier_spec->'supplier_payment_info'->>'deposit_deduction_type' IS NOT NULL THEN
1770        (supplier_spec->'supplier_payment_info'->>'deposit_deduction_type')::experience_extranet_supplier.deposit_deduction_type
1771      ELSE
1772        NULL
1773    END,
1774    supplier_spec->'supplier_payment_info'->>'invoice_due_date_type'
1775  );
1776
1777  PERFORM experience_extranet_supplier_v1_api.upsert_supplier_business_category(supplier_spec->'supplier_business_category', (supplier_spec->>'supplier_id')::BIGINT);
1778
1779  RETURN FOUND;
1780END;
1781$$;
1782
1783CREATE OR REPLACE FUNCTION experience_extranet_supplier_v1_api.update_supplier_info(supplier_spec jsonb) returns boolean
1784	security definer
1785	language plpgsql
1786as $$
1787DECLARE
1788  pic jsonb;
1789BEGIN
1790  UPDATE  experience_extranet_supplier.supplier
1791  SET     company_name              = supplier_spec->>'company_name',
1792          currency                  = supplier_spec->>'currency',
1793          country                   = supplier_spec->>'country',
1794          supplier_tax_id_no        = supplier_spec->>'supplier_tax_id_no',
1795          supplier_is_pkp           = (supplier_spec->>'supplier_is_pkp')::BOOLEAN,
1796          owner_name                = supplier_spec->>'owner_name',
1797          main_company_address      = supplier_spec->>'main_company_address',
1798          owner_phone_country_code  = supplier_spec->>'owner_phone_country_code',
1799          owner_phone_no            = supplier_spec->>'owner_phone_no',
1800          owner_email               = supplier_spec->>'owner_email',
1801          marketing_manager_user_id = supplier_spec->>'marketing_manager_user_id',
1802          agent_code                = supplier_spec->>'agent_code',
1803          agent_operating_license   = supplier_spec->>'agent_operating_license',
1804          agent_profile             = supplier_spec->>'agent_profile'
1805  WHERE   experience_extranet_supplier.supplier.supplier_id = (supplier_spec->>'supplier_id')::BIGINT;
1806
1807  FOR pic IN SELECT jsonb_array_elements(supplier_spec->'supplier_pic') LOOP
1808    UPDATE  experience_extranet_supplier.supplier_pic
1809    SET     pic_name      = pic->>'pic_name',
1810            pic_email     = pic->>'pic_email',
1811            pic_phone_country_code  = pic->>'pic_phone_country_code',
1812            pic_phone_no  = pic->>'pic_phone_no',
1813            pic_position  = pic->>'pic_position'
1814    WHERE   experience_extranet_supplier.supplier_pic.supplier_pic_id = (pic->>'supplier_pic_id')::BIGINT;
1815  END LOOP;
1816
1817  UPDATE  experience_extranet_supplier.supplier_payment_info
1818  SET     payment_cycle         = supplier_spec->'supplier_payment_info'->>'payment_cycle',
1819          bank_id               = supplier_spec->'supplier_payment_info'->>'bank_id',
1820          bank_other_id         = supplier_spec->'supplier_payment_info'->>'bank_other_id',
1821          bank_short_name       = supplier_spec->'supplier_payment_info'->>'bank_short_name',
1822          bank_name             = supplier_spec->'supplier_payment_info'->>'bank_name',
1823          bank_acc_no           = supplier_spec->'supplier_payment_info'->>'bank_acc_no',
1824          bank_acc_beneficiary_name = supplier_spec->'supplier_payment_info'->>'bank_acc_beneficiary_name',
1825          bank_branch           = supplier_spec->'supplier_payment_info'->>'bank_branch',
1826          bank_swift_code       = supplier_spec->'supplier_payment_info'->>'bank_swift_code',
1827          tvlk_entity_code      = supplier_spec->'supplier_payment_info'->>'tvlk_entity_code',
1828          payment_type          = COALESCE((supplier_spec->'supplier_payment_info'->>'payment_type')::experience_extranet_supplier.payment_type, ('BILLING')::experience_extranet_supplier.payment_type),
1829          deposit_deduction_type = (supplier_spec->'supplier_payment_info'->>'deposit_deduction_type')::experience_extranet_supplier.deposit_deduction_type
1830  WHERE   supplier_id = (supplier_spec->>'supplier_id')::BIGINT;
1831
1832  PERFORM experience_extranet_supplier_v1_api.upsert_supplier_business_category(supplier_spec->'supplier_business_category', (supplier_spec->>'supplier_id')::BIGINT);
1833
1834  RETURN FOUND;
1835END;
1836$$;
1837
1838DO $$
1839BEGIN
1840  ALTER TABLE IF EXISTS experience_extranet_supplier.supplier ADD agent_operating_license TEXT NULL;
1841EXCEPTION
1842  WHEN DUPLICATE_COLUMN THEN RAISE NOTICE 'column agent_operating_license already exists';
1843END;
1844$$;
1845
1846DO $$
1847BEGIN
1848  ALTER TABLE IF EXISTS experience_extranet_supplier.supplier ADD agent_profile TEXT NULL;
1849EXCEPTION
1850  WHEN DUPLICATE_COLUMN THEN RAISE NOTICE 'column agent_profile already exists';
1851END;
1852$$;
1853
1854
1855CREATE TABLE IF NOT EXISTS experience_extranet_experience.experience_umrah
1856(
1857  experience_id TEXT NOT NULL PRIMARY KEY,
1858  duration_day  INTEGER,
1859  all_day_long BOOLEAN,
1860  _lut          TIMESTAMPTZ,
1861  FOREIGN KEY (experience_id) REFERENCES experience_extranet_experience.experience
1862);
1863
1864DO $$
1865BEGIN
1866  CREATE TRIGGER populate_lut_experience_umrah
1867    BEFORE INSERT OR UPDATE ON experience_extranet_experience.experience_umrah
1868    FOR EACH ROW
1869    EXECUTE PROCEDURE experience_extranet_experience.lut_trigger();
1870EXCEPTION
1871  WHEN duplicate_object THEN RAISE NOTICE 'the trigger already exists';
1872END;
1873$$;
1874
1875CREATE TABLE IF NOT EXISTS experience_extranet_experience.experience_leisure(
1876  experience_id TEXT NOT NULL PRIMARY KEY,
1877  _lut          TIMESTAMPTZ,
1878  FOREIGN KEY (experience_id) REFERENCES experience_extranet_experience.experience
1879);
1880
1881DO $$
1882BEGIN
1883  CREATE TRIGGER populate_lut_experience_leisure
1884    BEFORE INSERT OR UPDATE ON experience_extranet_experience.experience_leisure
1885    FOR EACH ROW
1886    EXECUTE PROCEDURE experience_extranet_experience.lut_trigger();
1887EXCEPTION
1888  WHEN duplicate_object THEN RAISE NOTICE 'the trigger already exists';
1889END;
1890$$;
1891
1892INSERT INTO experience_extranet_experience.experience_category_table_mapping (experience_category, mapping_table_name, mapping_get_function_name, mapping_upsert_function_name) VALUES ('UMRAH', 'experience_extranet_experience.experience_umrah', 'experience_extranet_management_v2_helper.get_experience_umrah_additional_data($1)', 'experience_extranet_management_v2_helper.upsert_experience_umrah_additional_data($1, $2)') ON CONFLICT (experience_category) DO UPDATE SET mapping_table_name = EXCLUDED.mapping_table_name, mapping_get_function_name = EXCLUDED.mapping_get_function_name, mapping_upsert_function_name = EXCLUDED.mapping_upsert_function_name;
1893
1894INSERT INTO experience_extranet_experience.experience_category_table_mapping (experience_category, mapping_table_name, mapping_get_function_name, mapping_upsert_function_name) VALUES ('LEISURE', 'experience_extranet_experience.experience_leisure', 'experience_extranet_management_v2_helper.get_experience_leisure_additional_data($1)', 'experience_extranet_management_v2_helper.upsert_experience_leisure_additional_data($1, $2)') ON CONFLICT (experience_category) DO UPDATE SET mapping_table_name = EXCLUDED.mapping_table_name, mapping_get_function_name = EXCLUDED.mapping_get_function_name, mapping_upsert_function_name = EXCLUDED.mapping_upsert_function_name;
1895
1896INSERT INTO experience_extranet_experience.experience_category_type_mapping (experience_category, experience_type)
1897VALUES ('UMRAH', 'REGULAR')
1898ON CONFLICT (experience_type) DO UPDATE SET experience_category = EXCLUDED.experience_category;
1899INSERT INTO experience_extranet_experience.experience_category_type_mapping (experience_category, experience_type)
1900VALUES ('UMRAH', 'PLUS')
1901ON CONFLICT (experience_type) DO UPDATE SET experience_category = EXCLUDED.experience_category;
1902INSERT INTO experience_extranet_experience.experience_category_type_mapping (experience_category, experience_type)
1903VALUES ('LEISURE', 'OTHERS')
1904ON CONFLICT (experience_type) DO UPDATE SET experience_category = EXCLUDED.experience_category;
1905
1906
1907CREATE TABLE IF NOT EXISTS experience_extranet_experience.experience_umrah_hotel
1908(
1909  hotel_id BIGSERIAL NOT NULL PRIMARY KEY,
1910  experience_id             TEXT,
1911  country                   TEXT,
1912  city                      TEXT,
1913  rating                    INTEGER,
1914  name                      TEXT,
1915  photos                    JSONB,
1916  distance_from_main_mosque TEXT,
1917  _lut                      TIMESTAMPTZ,
1918  FOREIGN KEY (experience_id) REFERENCES experience_extranet_experience.experience
1919);
1920
1921DO $$
1922BEGIN
1923  CREATE TRIGGER populate_lut_experience_umrah_hotel
1924    BEFORE INSERT OR UPDATE ON experience_extranet_experience.experience_umrah_hotel
1925    FOR EACH ROW
1926    EXECUTE PROCEDURE experience_extranet_experience.lut_trigger();
1927EXCEPTION
1928  WHEN duplicate_object THEN RAISE NOTICE 'the trigger already exists';
1929END;
1930$$;
1931
1932CREATE TABLE IF NOT EXISTS experience_extranet_experience.experience_umrah_flight
1933(
1934  flight_id BIGSERIAL NOT NULL PRIMARY KEY,
1935  experience_id TEXT,
1936  flight_order  INTEGER,
1937  airline_brand TEXT,
1938  port          TEXT,
1939  _lut          TIMESTAMPTZ,
1940  FOREIGN KEY (experience_id) REFERENCES experience_extranet_experience.experience
1941);
1942
1943DO $$
1944BEGIN
1945  CREATE TRIGGER populate_lut_experience_umrah_flight
1946    BEFORE INSERT OR UPDATE ON experience_extranet_experience.experience_umrah_flight
1947    FOR EACH ROW
1948    EXECUTE PROCEDURE experience_extranet_experience.lut_trigger();
1949EXCEPTION
1950  WHEN duplicate_object THEN RAISE NOTICE 'the trigger already exists';
1951END;
1952$$;
1953
1954CREATE TABLE IF NOT EXISTS experience_extranet_experience.experience_umrah_travel_agent
1955(
1956  travel_agent_id BIGSERIAL NOT NULL PRIMARY KEY,
1957  experience_id     TEXT,
1958  travel_agent_name TEXT,
1959  license_number    TEXT,
1960  _lut              TIMESTAMPTZ,
1961  FOREIGN KEY (experience_id) REFERENCES experience_extranet_experience.experience
1962);
1963
1964DO $$
1965BEGIN
1966  CREATE TRIGGER populate_lut_experience_umrah_travel_agent
1967    BEFORE INSERT OR UPDATE
1968    ON experience_extranet_experience.experience_umrah_travel_agent
1969    FOR EACH ROW
1970  EXECUTE PROCEDURE experience_extranet_experience.lut_trigger();
1971  EXCEPTION
1972  WHEN duplicate_object
1973    THEN RAISE NOTICE 'the trigger already exists';
1974END;
1975$$;
1976
1977CREATE TABLE IF NOT EXISTS experience_extranet_ticket.ticket_umrah
1978(
1979  ticket_id TEXT NOT NULL PRIMARY KEY
1980    CONSTRAINT ticket_umrah_ticket_id_fkey
1981    REFERENCES experience_extranet_ticket.ticket
1982    ON UPDATE CASCADE ON DELETE RESTRICT,
1983  "_lut" TIMESTAMP WITH TIME ZONE NOT NULL
1984);
1985
1986CREATE INDEX IF NOT EXISTS experience_extranet_ticket_umrah__lut__idx
1987  ON experience_extranet_ticket.ticket_umrah ("_lut");
1988
1989DO $$
1990BEGIN
1991  CREATE TRIGGER populate_lut_ticket_umrah
1992    BEFORE INSERT OR UPDATE
1993    ON experience_extranet_ticket.ticket_umrah
1994    FOR EACH ROW
1995  EXECUTE PROCEDURE experience_extranet_ticket.lut_ticket();
1996  EXCEPTION
1997  WHEN duplicate_object
1998    THEN RAISE NOTICE 'the trigger already exists';
1999END;
2000$$;
2001
2002CREATE OR REPLACE FUNCTION experience_extranet_ticket_v2_helper.upsert_ticket_umrah_additional_data(
2003  ticket_id_spec              TEXT,
2004  ticket_additional_data_spec JSONB)
2005  RETURNS BOOLEAN
2006SECURITY DEFINER
2007LANGUAGE SQL
2008AS $$
2009WITH res AS (INSERT INTO experience_extranet_ticket.ticket_umrah (
2010  ticket_id
2011)
2012VALUES (
2013  ticket_id_spec
2014)
2015ON CONFLICT (ticket_id)
2016  DO NOTHING
2017RETURNING 1)
2018SELECT count(*) > 0
2019FROM res
2020$$;
2021
2022
2023CREATE TABLE IF NOT EXISTS experience_extranet_ticket.ticket_leisure
2024(
2025  ticket_id TEXT NOT NULL PRIMARY KEY
2026    CONSTRAINT ticket_leisure_ticket_id_fkey
2027    REFERENCES experience_extranet_ticket.ticket
2028    ON UPDATE CASCADE ON DELETE RESTRICT,
2029  "_lut" TIMESTAMP WITH TIME ZONE NOT NULL
2030);
2031
2032CREATE INDEX IF NOT EXISTS experience_extranet_ticket_leisure__lut__idx
2033  ON experience_extranet_ticket.ticket_leisure ("_lut");
2034
2035DO $$
2036BEGIN
2037  CREATE TRIGGER populate_lut_ticket_leisure
2038    BEFORE INSERT OR UPDATE
2039    ON experience_extranet_ticket.ticket_leisure
2040    FOR EACH ROW
2041  EXECUTE PROCEDURE experience_extranet_ticket.lut_ticket();
2042  EXCEPTION
2043  WHEN duplicate_object
2044    THEN RAISE NOTICE 'the trigger already exists';
2045END;
2046$$;
2047
2048CREATE OR REPLACE FUNCTION experience_extranet_ticket_v2_helper.upsert_ticket_leisure_additional_data(
2049  ticket_id_spec              TEXT,
2050  ticket_additional_data_spec JSONB)
2051  RETURNS BOOLEAN
2052SECURITY DEFINER
2053LANGUAGE SQL
2054AS $$
2055WITH res AS (INSERT INTO experience_extranet_ticket.ticket_leisure (
2056  ticket_id
2057)
2058VALUES (
2059  ticket_id_spec
2060)
2061ON CONFLICT (ticket_id)
2062  DO NOTHING
2063RETURNING 1)
2064SELECT count(*) > 0
2065FROM res
2066$$;
2067
2068DO $$
2069  BEGIN
2070    ALTER TABLE IF EXISTS experience_extranet_content.ticket_customer_group_mapping ADD COLUMN quantifier INT DEFAULT 1 NOT NULL;
2071    EXCEPTION
2072      WHEN duplicate_column THEN RAISE NOTICE 'column quantifier already exists';
2073  END;
2074$$;
2075
2076DO $$
2077  BEGIN
2078    ALTER TABLE IF EXISTS experience_extranet_content.ticket_customer_group_predefined ADD COLUMN quantifier INT DEFAULT 1 NOT NULL;
2079    EXCEPTION
2080      WHEN duplicate_column THEN RAISE NOTICE 'column quantifier already exists';
2081  END;
2082$$;
2083
2084
2085CREATE OR REPLACE FUNCTION experience_extranet_content_v2_api.insert_customer_group(customer_group jsonb) returns boolean
2086	security definer
2087	language plpgsql
2088as $$
2089DECLARE
2090  group_unit_id     TEXT;
2091  group_id          TEXT;
2092  rate_type_object TEXT;
2093  rate_type_array  TEXT[];
2094  group_name       TEXT;
2095BEGIN
2096  group_unit_id := REPLACE(UPPER(customer_group->>'customer_group_unit'), ' ', '_');
2097  group_id := REPLACE(UPPER(customer_group->>'customer_group_name'), ' ', '_');
2098
2099  INSERT INTO experience_extranet_content.ticket_customer_group_unit (
2100    group_unit_id,
2101    group_unit
2102  )
2103  VALUES (
2104    group_unit_id,
2105    customer_group->>'customer_group_unit'
2106  )
2107  ON CONFLICT DO NOTHING;
2108  INSERT INTO experience_extranet_content.ticket_customer_group_predefined (
2109    group_id,
2110    group_unit_id,
2111    group_name,
2112    group_description,
2113    quantifier
2114  )
2115  VALUES (
2116    group_id,
2117    group_unit_id,
2118    customer_group->>'customer_group_name',
2119    customer_group->>'description',
2120    (COALESCE(customer_group->>'quantifier', '1')) :: INTEGER
2121  )
2122  ON CONFLICT DO NOTHING;
2123  rate_type_array := ARRAY ['CONTRACT','PUBLISH','PARITY','RECOMMENDED'];
2124FOREACH rate_type_object in ARRAY rate_type_array
2125  LOOP
2126    group_name := REPLACE(customer_group->>'customer_group_name', ' ', '_');
2127    INSERT INTO experience_extranet_content.ticket_customer_group_rate_type (
2128      group_id,
2129      rate_type
2130    )
2131    VALUES (
2132      group_id,
2133      UPPER(group_name) || '_' || rate_type_object
2134    )
2135    ON CONFLICT DO NOTHING;
2136  END LOOP;
2137  RETURN FOUND;
2138END;
2139$$;
2140
2141CREATE OR REPLACE FUNCTION experience_extranet_content_v2_helper.get_customer_group_list_by_ticket_id(ticket_id_filter text, group_unit_id_filter text) returns jsonb
2142	security definer
2143	language sql
2144as $$
2145  SELECT to_jsonb(array_agg(result))
2146  FROM (
2147    SELECT  group_id,
2148            group_unit_id,
2149            group_name,
2150            group_description,
2151            maximum_size,
2152            minimum_size,
2153            quantifier,
2154            experience_extranet_content_v2_helper.get_rate_type_by_group_id(m.group_id) as "rate_type_list"
2155    FROM    experience_extranet_content.ticket_customer_group_mapping m
2156    WHERE   m.ticket_id = ticket_id_filter AND m.group_unit_id = group_unit_id_filter
2157  ) AS result
2158$$;
2159
2160INSERT INTO experience_extranet_content.experience_type (experience_type_id, experience_type_name, experience_category)
2161VALUES ('REGULAR', 'Regular', 'UMRAH')
2162ON CONFLICT (experience_type_id) DO UPDATE SET experience_type_name = EXCLUDED.experience_type_name, experience_category = EXCLUDED.experience_category;
2163INSERT INTO experience_extranet_content.experience_type (experience_type_id, experience_type_name, experience_category)
2164VALUES ('PLUS', 'Plus', 'UMRAH')
2165ON CONFLICT (experience_type_id) DO UPDATE SET experience_type_name = EXCLUDED.experience_type_name, experience_category = EXCLUDED.experience_category;
2166INSERT INTO experience_extranet_content.experience_type (experience_type_id, experience_type_name, experience_category)
2167VALUES ('RAMADHAN', 'Ramadhan', 'UMRAH')
2168ON CONFLICT (experience_type_id) DO UPDATE SET experience_type_name = EXCLUDED.experience_type_name, experience_category = EXCLUDED.experience_category;
2169INSERT INTO experience_extranet_content.experience_type (experience_type_id, experience_type_name, experience_category)
2170VALUES ('OTHERS', 'Others', 'LEISURE')
2171ON CONFLICT (experience_type_id) DO UPDATE SET experience_type_name = EXCLUDED.experience_type_name, experience_category = EXCLUDED.experience_category;
2172
2173
2174INSERT INTO experience_extranet_content.ticket_customer_group_unit (group_unit_id, group_unit) VALUES ('UMRAH', 'UMRAH') ON CONFLICT (group_unit_id) DO UPDATE SET group_unit = EXCLUDED.group_unit;
2175INSERT INTO experience_extranet_content.ticket_customer_group_unit (group_unit_id, group_unit) VALUES ('PACKAGE_SINGLE', 'PACKAGE_SINGLE') ON CONFLICT (group_unit_id) DO UPDATE SET group_unit = EXCLUDED.group_unit;
2176INSERT INTO experience_extranet_content.ticket_customer_group_unit (group_unit_id, group_unit) VALUES ('PACKAGE_DOUBLE', 'PACKAGE_DOUBLE') ON CONFLICT (group_unit_id) DO UPDATE SET group_unit = EXCLUDED.group_unit;
2177INSERT INTO experience_extranet_content.ticket_customer_group_unit (group_unit_id, group_unit) VALUES ('PACKAGE_TRIPLE', 'PACKAGE_TRIPLE') ON CONFLICT (group_unit_id) DO UPDATE SET group_unit = EXCLUDED.group_unit;
2178INSERT INTO experience_extranet_content.ticket_customer_group_unit (group_unit_id, group_unit) VALUES ('PACKAGE_QUAD', 'PACKAGE_QUAD') ON CONFLICT (group_unit_id) DO UPDATE SET group_unit = EXCLUDED.group_unit;
2179
2180INSERT INTO experience_extranet_content.ticket_customer_group_predefined (group_id, group_unit_id, group_name, group_description) VALUES ('UMRAH_SINGLE_SHARING', 'UMRAH', 'SINGLE_SHARING', 'Group for Single Sharing') ON CONFLICT (group_id, group_unit_id) DO UPDATE SET group_name = EXCLUDED.group_name, group_description = EXCLUDED.group_description;
2181INSERT INTO experience_extranet_content.ticket_customer_group_predefined (group_id, group_unit_id, group_name, group_description) VALUES ('UMRAH_SINGLE', 'UMRAH', 'SINGLE', 'Group for Single') ON CONFLICT (group_id, group_unit_id) DO UPDATE SET group_name = EXCLUDED.group_name, group_description = EXCLUDED.group_description;
2182INSERT INTO experience_extranet_content.ticket_customer_group_predefined (group_id, group_unit_id, group_name, group_description) VALUES ('UMRAH_DOUBLE', 'UMRAH', 'DOUBLE', 'Group for Double') ON CONFLICT (group_id, group_unit_id) DO UPDATE SET group_name = EXCLUDED.group_name, group_description = EXCLUDED.group_description;
2183INSERT INTO experience_extranet_content.ticket_customer_group_predefined (group_id, group_unit_id, group_name, group_description) VALUES ('UMRAH_TRIPLE', 'UMRAH', 'TRIPLE', 'Group for Triple') ON CONFLICT (group_id, group_unit_id) DO UPDATE SET group_name = EXCLUDED.group_name, group_description = EXCLUDED.group_description;
2184INSERT INTO experience_extranet_content.ticket_customer_group_predefined (group_id, group_unit_id, group_name, group_description) VALUES ('UMRAH_QUAD', 'UMRAH', 'QUAD', 'Group for Quad') ON CONFLICT (group_id, group_unit_id) DO UPDATE SET group_name = EXCLUDED.group_name, group_description = EXCLUDED.group_description;
2185INSERT INTO experience_extranet_content.ticket_customer_group_predefined (group_id, group_unit_id, group_name, group_description) VALUES ('PACKAGE_SINGLE_ADULT', 'PACKAGE_SINGLE', 'ADULT', 'Group for Package Single Adult') ON CONFLICT (group_id, group_unit_id) DO UPDATE SET group_name = EXCLUDED.group_name, group_description = EXCLUDED.group_description;
2186INSERT INTO experience_extranet_content.ticket_customer_group_predefined (group_id, group_unit_id, group_name, group_description) VALUES ('PACKAGE_DOUBLE_ADULT', 'PACKAGE_DOUBLE', 'ADULT', 'Group for Package Double Adult') ON CONFLICT (group_id, group_unit_id) DO UPDATE SET group_name = EXCLUDED.group_name, group_description = EXCLUDED.group_description;
2187INSERT INTO experience_extranet_content.ticket_customer_group_predefined (group_id, group_unit_id, group_name, group_description) VALUES ('PACKAGE_DOUBLE_CHILD', 'PACKAGE_DOUBLE', 'CHILD', 'Group for Package Double Child') ON CONFLICT (group_id, group_unit_id) DO UPDATE SET group_name = EXCLUDED.group_name, group_description = EXCLUDED.group_description;
2188INSERT INTO experience_extranet_content.ticket_customer_group_predefined (group_id, group_unit_id, group_name, group_description) VALUES ('PACKAGE_TRIPLE_ADULT', 'PACKAGE_TRIPLE', 'ADULT', 'Group for Package Adult') ON CONFLICT (group_id, group_unit_id) DO UPDATE SET group_name = EXCLUDED.group_name, group_description = EXCLUDED.group_description;
2189INSERT INTO experience_extranet_content.ticket_customer_group_predefined (group_id, group_unit_id, group_name, group_description) VALUES ('PACKAGE_TRIPLE_CHILD', 'PACKAGE_TRIPLE', 'CHILD', 'Group for Package Child') ON CONFLICT (group_id, group_unit_id) DO UPDATE SET group_name = EXCLUDED.group_name, group_description = EXCLUDED.group_description;
2190INSERT INTO experience_extranet_content.ticket_customer_group_predefined (group_id, group_unit_id, group_name, group_description) VALUES ('PACKAGE_QUAD_ADULT', 'PACKAGE_QUAD', 'ADULT', 'Group for Package Quad Adult') ON CONFLICT (group_id, group_unit_id) DO UPDATE SET group_name = EXCLUDED.group_name, group_description = EXCLUDED.group_description;
2191INSERT INTO experience_extranet_content.ticket_customer_group_predefined (group_id, group_unit_id, group_name, group_description) VALUES ('PACKAGE_QUAD_CHILD', 'PACKAGE_QUAD', 'CHILD', 'Group for Package Quad Child') ON CONFLICT (group_id, group_unit_id) DO UPDATE SET group_name = EXCLUDED.group_name, group_description = EXCLUDED.group_description;
2192
2193
2194
2195INSERT INTO experience_extranet_content.ticket_customer_group_rate_type (group_id, rate_type) VALUES ('UMRAH_SINGLE_SHARING', 'UMRAH_SINGLE_SHARING_CONTRACT') ON CONFLICT DO NOTHING;
2196INSERT INTO experience_extranet_content.ticket_customer_group_rate_type (group_id, rate_type) VALUES ('UMRAH_SINGLE_SHARING', 'UMRAH_SINGLE_SHARING_PUBLISH') ON CONFLICT DO NOTHING;
2197INSERT INTO experience_extranet_content.ticket_customer_group_rate_type (group_id, rate_type) VALUES ('UMRAH_SINGLE_SHARING', 'UMRAH_SINGLE_SHARING_PARITY') ON CONFLICT DO NOTHING;
2198INSERT INTO experience_extranet_content.ticket_customer_group_rate_type (group_id, rate_type) VALUES ('UMRAH_SINGLE_SHARING', 'UMRAH_SINGLE_SHARING_RECOMMENDED') ON CONFLICT DO NOTHING;
2199INSERT INTO experience_extranet_content.ticket_customer_group_rate_type (group_id, rate_type) VALUES ('UMRAH_SINGLE', 'UMRAH_SINGLE_CONTRACT') ON CONFLICT DO NOTHING;
2200INSERT INTO experience_extranet_content.ticket_customer_group_rate_type (group_id, rate_type) VALUES ('UMRAH_SINGLE', 'UMRAH_SINGLE_PUBLISH') ON CONFLICT DO NOTHING;
2201INSERT INTO experience_extranet_content.ticket_customer_group_rate_type (group_id, rate_type) VALUES ('UMRAH_SINGLE', 'UMRAH_SINGLE_PARITY') ON CONFLICT DO NOTHING;
2202INSERT INTO experience_extranet_content.ticket_customer_group_rate_type (group_id, rate_type) VALUES ('UMRAH_SINGLE', 'UMRAH_SINGLE_RECOMMENDED') ON CONFLICT DO NOTHING;
2203INSERT INTO experience_extranet_content.ticket_customer_group_rate_type (group_id, rate_type) VALUES ('UMRAH_DOUBLE', 'UMRAH_DOUBLE_CONTRACT') ON CONFLICT DO NOTHING;
2204INSERT INTO experience_extranet_content.ticket_customer_group_rate_type (group_id, rate_type) VALUES ('UMRAH_DOUBLE', 'UMRAH_DOUBLE_PUBLISH') ON CONFLICT DO NOTHING;
2205INSERT INTO experience_extranet_content.ticket_customer_group_rate_type (group_id, rate_type) VALUES ('UMRAH_DOUBLE', 'UMRAH_DOUBLE_PARITY') ON CONFLICT DO NOTHING;
2206INSERT INTO experience_extranet_content.ticket_customer_group_rate_type (group_id, rate_type) VALUES ('UMRAH_DOUBLE', 'UMRAH_DOUBLE_RECOMMENDED') ON CONFLICT DO NOTHING;
2207INSERT INTO experience_extranet_content.ticket_customer_group_rate_type (group_id, rate_type) VALUES ('UMRAH_TRIPLE', 'UMRAH_TRIPLE_CONTRACT') ON CONFLICT DO NOTHING;
2208INSERT INTO experience_extranet_content.ticket_customer_group_rate_type (group_id, rate_type) VALUES ('UMRAH_TRIPLE', 'UMRAH_TRIPLE_PUBLISH') ON CONFLICT DO NOTHING;
2209INSERT INTO experience_extranet_content.ticket_customer_group_rate_type (group_id, rate_type) VALUES ('UMRAH_TRIPLE', 'UMRAH_TRIPLE_PARITY') ON CONFLICT DO NOTHING;
2210INSERT INTO experience_extranet_content.ticket_customer_group_rate_type (group_id, rate_type) VALUES ('UMRAH_TRIPLE', 'UMRAH_TRIPLE_RECOMMENDED') ON CONFLICT DO NOTHING;
2211INSERT INTO experience_extranet_content.ticket_customer_group_rate_type (group_id, rate_type) VALUES ('UMRAH_QUAD', 'UMRAH_QUAD_CONTRACT') ON CONFLICT DO NOTHING;
2212INSERT INTO experience_extranet_content.ticket_customer_group_rate_type (group_id, rate_type) VALUES ('UMRAH_QUAD', 'UMRAH_QUAD_PUBLISH') ON CONFLICT DO NOTHING;
2213INSERT INTO experience_extranet_content.ticket_customer_group_rate_type (group_id, rate_type) VALUES ('UMRAH_QUAD', 'UMRAH_QUAD_PARITY') ON CONFLICT DO NOTHING;
2214INSERT INTO experience_extranet_content.ticket_customer_group_rate_type (group_id, rate_type) VALUES ('UMRAH_QUAD', 'UMRAH_QUAD_RECOMMENDED') ON CONFLICT DO NOTHING;
2215INSERT INTO experience_extranet_content.ticket_customer_group_rate_type (group_id, rate_type) VALUES ('PACKAGE_SINGLE_ADULT', 'PACKAGE_SINGLE_ADULT_CONTRACT') ON CONFLICT DO NOTHING;
2216INSERT INTO experience_extranet_content.ticket_customer_group_rate_type (group_id, rate_type) VALUES ('PACKAGE_SINGLE_ADULT', 'PACKAGE_SINGLE_ADULT_PUBLISH') ON CONFLICT DO NOTHING;
2217INSERT INTO experience_extranet_content.ticket_customer_group_rate_type (group_id, rate_type) VALUES ('PACKAGE_SINGLE_ADULT', 'PACKAGE_SINGLE_ADULT_PARITY') ON CONFLICT DO NOTHING;
2218INSERT INTO experience_extranet_content.ticket_customer_group_rate_type (group_id, rate_type) VALUES ('PACKAGE_SINGLE_ADULT', 'PACKAGE_SINGLE_ADULT_RECOMMENDED') ON CONFLICT DO NOTHING;
2219INSERT INTO experience_extranet_content.ticket_customer_group_rate_type (group_id, rate_type) VALUES ('PACKAGE_DOUBLE_ADULT', 'PACKAGE_DOUBLE_ADULT_CONTRACT') ON CONFLICT DO NOTHING;
2220INSERT INTO experience_extranet_content.ticket_customer_group_rate_type (group_id, rate_type) VALUES ('PACKAGE_DOUBLE_ADULT', 'PACKAGE_DOUBLE_ADULT_PUBLISH') ON CONFLICT DO NOTHING;
2221INSERT INTO experience_extranet_content.ticket_customer_group_rate_type (group_id, rate_type) VALUES ('PACKAGE_DOUBLE_ADULT', 'PACKAGE_DOUBLE_ADULT_PARITY') ON CONFLICT DO NOTHING;
2222INSERT INTO experience_extranet_content.ticket_customer_group_rate_type (group_id, rate_type) VALUES ('PACKAGE_DOUBLE_ADULT', 'PACKAGE_DOUBLE_ADULT_RECOMMENDED') ON CONFLICT DO NOTHING;
2223INSERT INTO experience_extranet_content.ticket_customer_group_rate_type (group_id, rate_type) VALUES ('PACKAGE_DOUBLE_CHILD', 'PACKAGE_DOUBLE_CHILD_CONTRACT') ON CONFLICT DO NOTHING;
2224INSERT INTO experience_extranet_content.ticket_customer_group_rate_type (group_id, rate_type) VALUES ('PACKAGE_DOUBLE_CHILD', 'PACKAGE_DOUBLE_CHILD_PUBLISH') ON CONFLICT DO NOTHING;
2225INSERT INTO experience_extranet_content.ticket_customer_group_rate_type (group_id, rate_type) VALUES ('PACKAGE_DOUBLE_CHILD', 'PACKAGE_DOUBLE_CHILD_PARITY') ON CONFLICT DO NOTHING;
2226INSERT INTO experience_extranet_content.ticket_customer_group_rate_type (group_id, rate_type) VALUES ('PACKAGE_DOUBLE_CHILD', 'PACKAGE_DOUBLE_CHILD_RECOMMENDED') ON CONFLICT DO NOTHING;
2227INSERT INTO experience_extranet_content.ticket_customer_group_rate_type (group_id, rate_type) VALUES ('PACKAGE_TRIPLE_ADULT', 'PACKAGE_TRIPLE_ADULT_CONTRACT') ON CONFLICT DO NOTHING;
2228INSERT INTO experience_extranet_content.ticket_customer_group_rate_type (group_id, rate_type) VALUES ('PACKAGE_TRIPLE_ADULT', 'PACKAGE_TRIPLE_ADULT_PUBLISH') ON CONFLICT DO NOTHING;
2229INSERT INTO experience_extranet_content.ticket_customer_group_rate_type (group_id, rate_type) VALUES ('PACKAGE_TRIPLE_ADULT', 'PACKAGE_TRIPLE_ADULT_PARITY') ON CONFLICT DO NOTHING;
2230INSERT INTO experience_extranet_content.ticket_customer_group_rate_type (group_id, rate_type) VALUES ('PACKAGE_TRIPLE_ADULT', 'PACKAGE_TRIPLE_ADULT_RECOMMENDED') ON CONFLICT DO NOTHING;
2231INSERT INTO experience_extranet_content.ticket_customer_group_rate_type (group_id, rate_type) VALUES ('PACKAGE_TRIPLE_CHILD', 'PACKAGE_TRIPLE_CHILD_CONTRACT') ON CONFLICT DO NOTHING;
2232INSERT INTO experience_extranet_content.ticket_customer_group_rate_type (group_id, rate_type) VALUES ('PACKAGE_TRIPLE_CHILD', 'PACKAGE_TRIPLE_CHILD_PUBLISH') ON CONFLICT DO NOTHING;
2233INSERT INTO experience_extranet_content.ticket_customer_group_rate_type (group_id, rate_type) VALUES ('PACKAGE_TRIPLE_CHILD', 'PACKAGE_TRIPLE_CHILD_PARITY') ON CONFLICT DO NOTHING;
2234INSERT INTO experience_extranet_content.ticket_customer_group_rate_type (group_id, rate_type) VALUES ('PACKAGE_TRIPLE_CHILD', 'PACKAGE_TRIPLE_CHILD_RECOMMENDED') ON CONFLICT DO NOTHING;
2235INSERT INTO experience_extranet_content.ticket_customer_group_rate_type (group_id, rate_type) VALUES ('PACKAGE_QUAD_ADULT', 'PACKAGE_QUAD_ADULT_CONTRACT') ON CONFLICT DO NOTHING;
2236INSERT INTO experience_extranet_content.ticket_customer_group_rate_type (group_id, rate_type) VALUES ('PACKAGE_QUAD_ADULT', 'PACKAGE_QUAD_ADULT_PUBLISH') ON CONFLICT DO NOTHING;
2237INSERT INTO experience_extranet_content.ticket_customer_group_rate_type (group_id, rate_type) VALUES ('PACKAGE_QUAD_ADULT', 'PACKAGE_QUAD_ADULT_PARITY') ON CONFLICT DO NOTHING;
2238INSERT INTO experience_extranet_content.ticket_customer_group_rate_type (group_id, rate_type) VALUES ('PACKAGE_QUAD_ADULT', 'PACKAGE_QUAD_ADULT_RECOMMENDED') ON CONFLICT DO NOTHING;
2239INSERT INTO experience_extranet_content.ticket_customer_group_rate_type (group_id, rate_type) VALUES ('PACKAGE_QUAD_CHILD', 'PACKAGE_QUAD_CHILD_CONTRACT') ON CONFLICT DO NOTHING;
2240INSERT INTO experience_extranet_content.ticket_customer_group_rate_type (group_id, rate_type) VALUES ('PACKAGE_QUAD_CHILD', 'PACKAGE_QUAD_CHILD_PUBLISH') ON CONFLICT DO NOTHING;
2241INSERT INTO experience_extranet_content.ticket_customer_group_rate_type (group_id, rate_type) VALUES ('PACKAGE_QUAD_CHILD', 'PACKAGE_QUAD_CHILD_PARITY') ON CONFLICT DO NOTHING;
2242INSERT INTO experience_extranet_content.ticket_customer_group_rate_type (group_id, rate_type) VALUES ('PACKAGE_QUAD_CHILD', 'PACKAGE_QUAD_CHILD_RECOMMENDED') ON CONFLICT DO NOTHING;
2243
2244UPDATE experience_extranet_content.ticket_customer_group_predefined SET quantifier = 2 WHERE group_id = 'PACKAGE_DOUBLE_ADULT' AND group_unit_id = 'PACKAGE_DOUBLE';
2245UPDATE experience_extranet_content.ticket_customer_group_predefined SET quantifier = 4 WHERE group_id = 'PACKAGE_QUAD_CHILD' AND group_unit_id = 'PACKAGE_QUAD';
2246UPDATE experience_extranet_content.ticket_customer_group_predefined SET quantifier = 4 WHERE group_id = 'PACKAGE_QUAD_ADULT' AND group_unit_id = 'PACKAGE_QUAD';
2247UPDATE experience_extranet_content.ticket_customer_group_predefined SET quantifier = 2 WHERE group_id = 'UMRAH_DOUBLE' AND group_unit_id = 'UMRAH';
2248UPDATE experience_extranet_content.ticket_customer_group_predefined SET quantifier = 4 WHERE group_id = 'UMRAH_QUAD' AND group_unit_id = 'UMRAH';
2249UPDATE experience_extranet_content.ticket_customer_group_predefined SET quantifier = 3 WHERE group_id = 'UMRAH_TRIPLE' AND group_unit_id = 'UMRAH';
2250UPDATE experience_extranet_content.ticket_customer_group_predefined SET quantifier = 2 WHERE group_id = 'PACKAGE_DOUBLE_CHILD' AND group_unit_id = 'PACKAGE_DOUBLE';
2251UPDATE experience_extranet_content.ticket_customer_group_predefined SET quantifier = 3 WHERE group_id = 'PACKAGE_TRIPLE_CHILD' AND group_unit_id = 'PACKAGE_TRIPLE';
2252UPDATE experience_extranet_content.ticket_customer_group_predefined SET quantifier = 3 WHERE group_id = 'PACKAGE_TRIPLE_ADULT' AND group_unit_id = 'PACKAGE_TRIPLE';
2253
2254INSERT INTO experience_extranet_experience.experience_category_type_mapping (experience_category, experience_type)
2255VALUES ('UMRAH', 'REGULAR')
2256ON CONFLICT (experience_type) DO UPDATE SET experience_category = EXCLUDED.experience_category;
2257INSERT INTO experience_extranet_experience.experience_category_type_mapping (experience_category, experience_type)
2258VALUES ('UMRAH', 'PLUS')
2259ON CONFLICT (experience_type) DO UPDATE SET experience_category = EXCLUDED.experience_category;
2260INSERT INTO experience_extranet_experience.experience_category_type_mapping (experience_category, experience_type)
2261VALUES ('UMRAH', 'RAMADHAN')
2262ON CONFLICT (experience_type) DO UPDATE SET experience_category = EXCLUDED.experience_category;
2263INSERT INTO experience_extranet_experience.experience_category_type_mapping (experience_category, experience_type)
2264VALUES ('LEISURE', 'OTHERS')
2265ON CONFLICT (experience_type) DO UPDATE SET experience_category = EXCLUDED.experience_category;
2266
2267
2268
2269
2270DO $$
2271BEGIN
2272  BEGIN
2273    ALTER TABLE experience_extranet_experience.experience_itinerary
2274      ADD COLUMN all_day_long BOOLEAN DEFAULT FALSE;
2275    EXCEPTION
2276    WHEN duplicate_column
2277      THEN RAISE NOTICE 'column all_day_long already exists';
2278  END;
2279END;
2280$$;
2281
2282CREATE OR REPLACE FUNCTION experience_extranet_management_v2_helper.upsert_experience_itinerary(
2283  itinerary_id_spec BIGINT, filter_experience_id TEXT, itinerary JSONB)
2284  RETURNS BOOLEAN
2285SECURITY DEFINER
2286LANGUAGE SQL
2287AS $$
2288WITH res AS (INSERT INTO experience_extranet_experience.experience_itinerary (
2289  experience_itinerary_id,
2290  experience_id,
2291  day,
2292  all_day_long
2293)
2294VALUES (
2295  itinerary_id_spec,
2296  filter_experience_id,
2297  (itinerary ->> 'day') :: INTEGER,
2298  ( COALESCE (itinerary->>'all_day_long', 'FALSE')):: BOOLEAN
2299)
2300ON CONFLICT (experience_itinerary_id)
2301  DO UPDATE
2302    SET
2303      day          = EXCLUDED.day,
2304      all_day_long = EXCLUDED.all_day_long
2305RETURNING 1)
2306SELECT COUNT(*) > 0
2307FROM res;
2308$$;
2309
2310CREATE OR REPLACE FUNCTION experience_extranet_management_v2_helper.get_experience_itinerary(filter_experience_id TEXT)
2311  RETURNS JSONB
2312LANGUAGE SQL
2313AS $$
2314SELECT jsonb_agg(res)
2315FROM (SELECT ei.day, eid.time, eid.description, ei.all_day_long
2316      FROM experience_extranet_experience.experience_itinerary ei
2317             INNER JOIN experience_extranet_experience.experience_itinerary_description eid
2318               ON ei.experience_itinerary_id = eid.experience_itinerary_id
2319      WHERE experience_id = filter_experience_id
2320      ORDER BY ei.day, eid.time) res
2321$$;