INSERT INTO experience_extranet_ticket.ticket_category_table_mapping (experience_category,                                                  mapping_table_name,                                                      mapping_upsert_function_name)
VALUES ('UMRAH',
        'experience_extranet_ticket.ticket_umrah',
        'experience_extranet_ticket_v2_helper.upsert_ticket_umrah_additional_data($1, $2)')
ON CONFLICT (experience_category) DO UPDATE
  SET
    mapping_table_name           = EXCLUDED.mapping_table_name,
    mapping_upsert_function_name = EXCLUDED.mapping_upsert_function_name;

INSERT INTO experience_extranet_ticket.ticket_category_table_mapping (experience_category,                                                mapping_table_name,                                                mapping_upsert_function_name)
VALUES ('LEISURE',
        'experience_extranet_ticket.ticket_leisure',
        'experience_extranet_ticket_v2_helper.upsert_ticket_leisure_additional_data($1, $2)')
ON CONFLICT (experience_category) DO UPDATE
  SET
    mapping_table_name           = EXCLUDED.mapping_table_name,
    mapping_upsert_function_name = EXCLUDED.mapping_upsert_function_name;

CREATE TABLE IF NOT EXISTS experience_extranet_ticket.ticket_umrah
(
  ticket_id TEXT NOT NULL PRIMARY KEY
    CONSTRAINT ticket_umrah_ticket_id_fkey
    REFERENCES experience_extranet_ticket.ticket
    ON UPDATE CASCADE ON DELETE RESTRICT,
  "_lut" TIMESTAMP WITH TIME ZONE NOT NULL
);

CREATE INDEX IF NOT EXISTS experience_extranet_ticket_umrah__lut__idx
  ON experience_extranet_ticket.ticket_umrah ("_lut");

DO $$
BEGIN
  CREATE TRIGGER populate_lut_ticket_umrah
    BEFORE INSERT OR UPDATE
    ON experience_extranet_ticket.ticket_umrah
    FOR EACH ROW
  EXECUTE PROCEDURE experience_extranet_ticket.lut_ticket();
  EXCEPTION
  WHEN duplicate_object
    THEN RAISE NOTICE 'the trigger already exists';
END;
$$;

CREATE TABLE IF NOT EXISTS experience_extranet_ticket.ticket_leisure
(
  ticket_id TEXT NOT NULL PRIMARY KEY
    CONSTRAINT ticket_leisure_ticket_id_fkey
    REFERENCES experience_extranet_ticket.ticket
    ON UPDATE CASCADE ON DELETE RESTRICT,
  "_lut" TIMESTAMP WITH TIME ZONE NOT NULL
);

CREATE INDEX IF NOT EXISTS experience_extranet_ticket_leisure__lut__idx
  ON experience_extranet_ticket.ticket_leisure ("_lut");

DO $$
BEGIN
  CREATE TRIGGER populate_lut_ticket_leisure
    BEFORE INSERT OR UPDATE
    ON experience_extranet_ticket.ticket_leisure
    FOR EACH ROW
  EXECUTE PROCEDURE experience_extranet_ticket.lut_ticket();
  EXCEPTION
  WHEN duplicate_object
    THEN RAISE NOTICE 'the trigger already exists';
END;
$$;


CREATE OR REPLACE FUNCTION experience_extranet_management_v2_helper.get_experience_umrah_hotels_by_experience_id(experience_id_spec text) returns jsonb
	security definer
	language sql
as $$
SELECT jsonb_agg(to_jsonb(result))
FROM (
       SELECT *
       FROM experience_extranet_experience.experience_umrah_hotel
       WHERE experience_id = experience_id_spec
     ) AS result
$$;

CREATE OR REPLACE FUNCTION experience_extranet_management_v2_helper.get_experience_umrah_flights_by_experience_id(experience_id_spec text) returns jsonb
	security definer
	language sql
as $$
SELECT jsonb_agg(to_jsonb(result))
FROM (
       SELECT *
       FROM experience_extranet_experience.experience_umrah_flight
       WHERE experience_id = experience_id_spec
     ) AS result
$$;

CREATE OR REPLACE FUNCTION experience_extranet_management_v2_helper.get_experience_umrah_travel_agent_by_experience_id(experience_id_spec text) returns jsonb
	security definer
	language sql
as $$
SELECT jsonb_agg(to_jsonb(result))
FROM (
       SELECT *
       FROM experience_extranet_experience.experience_umrah_travel_agent
       WHERE experience_id = experience_id_spec
     ) AS result
$$;

CREATE OR REPLACE FUNCTION experience_extranet_management_v2_helper.get_experience_umrah_additional_data(
  filter_experience_id TEXT)
  RETURNS JSONB
SECURITY DEFINER
LANGUAGE SQL
AS $$
WITH get_umrah_result AS (SELECT *
                          FROM experience_extranet_experience.experience_umrah
                          WHERE experience_id = filter_experience_id),
     get_travel_agent_result AS (SELECT to_jsonb(result)
                                 FROM (SELECT *
                                       FROM experience_extranet_experience.experience_umrah_travel_agent
                                       WHERE experience_id = filter_experience_id) result),
     get_hotels_result AS (SELECT jsonb_agg(to_jsonb(result))
                           FROM (SELECT *
                                 FROM experience_extranet_experience.experience_umrah_hotel
                                 WHERE experience_id = filter_experience_id) result),
     get_flights_result AS (SELECT jsonb_agg(to_jsonb(result))
                            FROM (SELECT *
                                  FROM experience_extranet_experience.experience_umrah_flight
                                  WHERE experience_id = filter_experience_id) result),
     get_itinerary_description_result AS (SELECT *
                                          FROM experience_extranet_experience.experience_itinerary_description eid
                                          WHERE EXISTS(
                                                  SELECT ei.experience_itinerary_id
                                                  FROM experience_extranet_experience.experience_itinerary ei
                                                  WHERE ei.experience_id = filter_experience_id
                                                    AND ei.experience_itinerary_id = eid.experience_itinerary_id
                                                    )),
     get_itinerary_result AS (SELECT array_agg(itinerary_result) AS result
                              FROM (SELECT *, (SELECT array_agg(res) AS descriptions
                                               FROM (SELECT *, (SELECT array_agg(img) AS images
                                                                FROM (SELECT *
                                                                      FROM experience_extranet_experience.experience_itinerary_image iti
                                                                      WHERE iti.itinerary_description_id = des.itinerary_description_id) img)
                                                     FROM get_itinerary_description_result des
                                                     WHERE ei.experience_itinerary_id = des.experience_itinerary_id
                                                     ORDER BY des.time) res)
                                    FROM experience_extranet_experience.experience_itinerary ei
                                    WHERE experience_id = filter_experience_id
                                    ORDER BY ei.day) itinerary_result),
     get_tour_meeting_point_result AS (SELECT to_jsonb(result)
                                       FROM (SELECT *
                                             FROM experience_extranet_experience.experience_tour_meeting_point mp
                                             WHERE mp.experience_id = filter_experience_id) AS result),
     get_tour_content_result AS (SELECT *
                                 FROM experience_extranet_content.experience_tour_content
                                 WHERE experience_id = filter_experience_id)
SELECT jsonb_build_object(
         'duration_day', (SELECT duration_day FROM get_umrah_result),
         'all_day_long', (SELECT all_day_long FROM get_umrah_result),
         'travel_agent', (SELECT * FROM get_travel_agent_result),
         'flights', (SELECT * FROM get_flights_result),
         'hotels', (SELECT * FROM get_hotels_result),
         'itineraries', (SELECT * FROM get_itinerary_result),
         'pickup_price', (SELECT pickup_price FROM get_tour_content_result),
         'pickup_address', (SELECT pickup_address FROM get_tour_content_result),
         'drop_off_point', (SELECT drop_off_point FROM get_tour_content_result),
         'meeting_point_available', (SELECT CASE WHEN get_tour_meeting_point_result IS NULL THEN FALSE ELSE TRUE END
                                     FROM get_tour_meeting_point_result),
         'meeting_point', (SELECT * FROM get_tour_meeting_point_result)
           );
$$;


CREATE OR REPLACE FUNCTION experience_extranet_management_v2_helper.upsert_experience_umrah_additional_data(filter_experience_id text, additional_data_spec jsonb) returns boolean
	security definer
	language plpgsql
as $$
DECLARE
  travel_agent_id_spec BIGINT;
  itinerary_id BIGINT;
  itinerary JSONB;
  description JSONB;
BEGIN
  INSERT INTO experience_extranet_experience.experience_umrah (experience_id, duration_day, all_day_long)
  VALUES (
    filter_experience_id,
    (COALESCE(NULLIF(additional_data_spec->>'duration_day', ''), '0'))::INTEGER,
    (COALESCE(NULLIF(additional_data_spec->>'all_day_long', ''), 'FALSE')) :: BOOLEAN
  )
  ON CONFLICT (experience_id) DO UPDATE
    SET
      duration_day = EXCLUDED.duration_day,
      all_day_long = EXCLUDED.all_day_long;

  travel_agent_id_spec := additional_data_spec -> 'travel_agent' ->> 'travel_agent_id';
  IF (travel_agent_id_spec = 0)
  THEN
    travel_agent_id_spec := nextval('experience_extranet_experience.experience_umrah_travel_agent_travel_agent_id_seq');
  END IF;

  INSERT INTO experience_extranet_experience.experience_umrah_travel_agent (travel_agent_id, experience_id, travel_agent_name, license_number)
  VALUES (
    travel_agent_id_spec,
    filter_experience_id,
    additional_data_spec->'travel_agent'->>'travel_agent_name',
    additional_data_spec->'travel_agent'->>'license_number'
  )
  ON CONFLICT (travel_agent_id) DO UPDATE
    SET
      experience_id = EXCLUDED.experience_id,
      travel_agent_name = EXCLUDED.travel_agent_name,
      license_number = EXCLUDED.license_number;

  DELETE
  FROM experience_extranet_experience.experience_umrah_hotel
  WHERE hotel_id NOT IN
        (SELECT hotel_id FROM jsonb_array_elements(additional_data_spec->'hotels') AS elem
         WHERE hotel_id = (elem->>'hotel_id') :: BIGINT) AND experience_id = filter_experience_id;

  INSERT INTO experience_extranet_experience.experience_umrah_hotel (hotel_id, experience_id, country, city, rating, name, photos, distance_from_main_mosque)
  SELECT
         (hotel ->> 'hotel_id') :: BIGINT,
         filter_experience_id,
         hotel ->> 'country',
         hotel ->> 'city',
         (hotel ->> 'rating') :: INTEGER,
         hotel ->> 'name',
         hotel -> 'photos',
         hotel ->> 'distance_from_main_mosque'
  FROM jsonb_array_elements(additional_data_spec->'hotels') AS hotel
  WHERE (hotel ->> 'hotel_id') :: BIGINT <> 0
  ON CONFLICT (hotel_id) DO UPDATE
    SET country = EXCLUDED.country,
      city = EXCLUDED.city,
      name = EXCLUDED.name,
      photos = EXCLUDED.photos,
      distance_from_main_mosque = EXCLUDED.distance_from_main_mosque;

  INSERT INTO experience_extranet_experience.experience_umrah_hotel (experience_id, country, city, rating, name, photos, distance_from_main_mosque)
  SELECT
         filter_experience_id,
         hotel ->> 'country',
         hotel ->> 'city',
         (hotel ->> 'rating') :: INTEGER,
         hotel ->> 'name',
         hotel -> 'photos',
         hotel ->> 'distance_from_main_mosque'
  FROM jsonb_array_elements(additional_data_spec->'hotels') AS hotel
  WHERE (hotel ->> 'hotel_id') :: BIGINT = 0;


  DELETE
  FROM experience_extranet_experience.experience_umrah_flight
  WHERE flight_id NOT IN
        (SELECT flight_id
         FROM jsonb_array_elements(additional_data_spec->'flights') AS elem
         WHERE flight_id = (elem->>'flight_id') :: BIGINT) AND experience_id = filter_experience_id;

  INSERT INTO experience_extranet_experience.experience_umrah_flight (experience_id, flight_order, airline_brand, port)
  SELECT filter_experience_id,
         (flight ->> 'flight_order') :: INTEGER,
         flight ->> 'airline_brand',
         flight ->> 'port'
  FROM jsonb_array_elements(additional_data_spec->'flights') AS flight
  WHERE (flight ->> 'flight_id') :: BIGINT = 0;

  INSERT INTO experience_extranet_experience.experience_umrah_flight (flight_id, experience_id, flight_order, airline_brand, port)
  SELECT (flight ->> 'flight_id') :: BIGINT,
         filter_experience_id,
         (flight ->> 'flight_order') :: INTEGER,
         flight ->> 'airline_brand',
         flight ->> 'port'
  FROM jsonb_array_elements(additional_data_spec->'flights') AS flight
  WHERE (flight ->> 'flight_id') :: BIGINT <> 0
  ON CONFLICT (flight_id) DO UPDATE
    SET flight_order = EXCLUDED.flight_order, airline_brand = EXCLUDED.airline_brand, port = EXCLUDED.port;

  DELETE FROM experience_extranet_experience.experience_itinerary
  WHERE NOT EXISTS (
    SELECT experience_itinerary_id
    FROM jsonb_array_elements(additional_data_spec->'itineraries') AS elem
    WHERE experience_itinerary_id = (elem->>'experience_itinerary_id')::BIGINT
  ) AND experience_id = filter_experience_id;

  FOR itinerary IN SELECT jsonb_array_elements(additional_data_spec->'itineraries') LOOP
    IF ((SELECT COUNT(*) FROM experience_extranet_experience.experience_itinerary WHERE experience_itinerary_id = (itinerary->>'experience_itinerary_id')::BIGINT) > 0)THEN
      itinerary_id := (itinerary->>'experience_itinerary_id')::BIGINT;
    ELSE
      itinerary_id := nextval('experience_extranet_experience.experience_itinerary_seq');
    END IF;

    PERFORM experience_extranet_management_v2_helper.upsert_experience_itinerary(itinerary_id, filter_experience_id, itinerary);

    DELETE FROM experience_extranet_experience.experience_itinerary_description eid
    WHERE NOT EXISTS(
      SELECT *
      FROM jsonb_array_elements(itinerary->'descriptions') AS elem
      WHERE eid.itinerary_description_id = (elem->>'itinerary_description_id')::BIGINT
    ) AND eid.experience_itinerary_id = itinerary_id;

    FOR description IN SELECT jsonb_array_elements(itinerary->'descriptions') LOOP
      PERFORM experience_extranet_management_v2_helper.upsert_experience_itinerary_description(itinerary_id, description);
    END LOOP;

  END LOOP;

  IF (additional_data_spec ->> 'meeting_point' IS NULL OR (additional_data_spec ->> 'meeting_point_available') :: BOOLEAN IS FALSE) THEN
    DELETE FROM experience_extranet_experience.experience_tour_meeting_point
    WHERE experience_id = filter_experience_id;
  ELSE
    PERFORM experience_extranet_management_v2_helper.upsert_tour_meeting_point(filter_experience_id, additional_data_spec -> 'meeting_point');
  END IF;

  PERFORM experience_extranet_content_v2_helper.upsert_experience_tour_content(filter_experience_id, additional_data_spec);

  RETURN FOUND;
END;
$$;

CREATE OR REPLACE FUNCTION experience_extranet_management_v2_helper.get_experience_leisure_additional_data(filter_experience_id TEXT)
  RETURNS JSONB
SECURITY DEFINER
LANGUAGE SQL
AS $$
WITH get_leisure_result AS (
    SELECT * FROM experience_extranet_experience.experience_leisure WHERE experience_id = filter_experience_id
    )
    SELECT jsonb_build_object('experience_id',
        (SELECT experience_id FROM get_leisure_result)
     );
$$;


CREATE OR REPLACE FUNCTION experience_extranet_management_v2_helper.upsert_experience_leisure_additional_data(
  filter_experience_id TEXT, additional_data_spec JSONB)
  RETURNS BOOLEAN
SECURITY DEFINER
LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO experience_extranet_experience.experience_leisure (experience_id) VALUES (filter_experience_id)
  ON CONFLICT (experience_id) DO NOTHING;

  RETURN FOUND;
END;
$$;



DO $$
  BEGIN
    ALTER TABLE IF EXISTS experience_extranet_ticket.ticket_refund_policy
      ADD COLUMN num_of_days_prior_to INTEGER;
    EXCEPTION
      WHEN duplicate_column THEN RAISE NOTICE 'column num_of_days_prior_to already exists';
  END;
$$;

CREATE OR REPLACE FUNCTION experience_extranet_ticket_v1_helper.get_ticket_refund_policy(filter_ticket_id TEXT)
  RETURNS JSONB
SECURITY DEFINER
LANGUAGE SQL
AS $$
SELECT to_jsonb(array_agg(res))
FROM (SELECT *, jsonb_build_object('days_from', trp.num_of_days_prior,
                                   'days_to', COALESCE(trp.num_of_days_prior_to, trp.num_of_days_prior)) AS num_of_days_range_prior
      FROM experience_extranet_ticket.ticket_refund_policy trp
      WHERE trp.ticket_id = filter_ticket_id
        AND trp.active = TRUE
      ORDER BY trp.num_of_days_prior) AS res;
$$;


CREATE OR REPLACE FUNCTION experience_extranet_ticket_v2_api.insert_ticket(ticket_spec JSONB)
  RETURNS TEXT
SECURITY DEFINER
LANGUAGE plpgsql
AS $$
DECLARE
  policy            jsonb;
  time_slot         jsonb;
  exp_ticket_id     TEXT;
  group_list        jsonb;
  group_detail_list jsonb;
  redeemable        jsonb;
BEGIN
  exp_ticket_id := concat(ticket_spec ->> 'experience_id', '.',
                          nextval('experience_extranet_ticket.experience_ticket_seq'));

  INSERT INTO experience_extranet_ticket.ticket (
    ticket_id,
    experience_id,
    ticket_type,
    ticket_barcode_type,
    ticket_name,
    ticket_description,
    maximum_adult_size,
    minimum_adult_size,
    maximum_child_size,
    minimum_child_size,
    group_size,
    refundable,
    active,
    total_inventory_qty,
    voucher_format,
    voucher_fulfillment,
    issuance_approval,
    ticket_guest_type,
    agent_url,
    hold_booking_time,
    deleted,
    voucher_usage_validity,
    voucher_sales_validity,
    date_of_birth,
    reservation,
    inventory_type,
    voucher_type,
    term_condition,
    voucher_code_format,
    reservation_day,
    best_deal,
    subtitle,
    direct_entrance,
    ticket_has_commission,
    commission_rate,
    multi_supplier,
    how_to_redeem_description,
    need_self_identifier,
    self_identifier_required_type,
    self_identifier_type,
    price_include,
    price_exclude,
    reservation_time,
    min_reservation_day,
    need_booking_options,
    pushed_to_aggregator
  )
  VALUES (
    exp_ticket_id,
    ticket_spec ->> 'experience_id',
    ticket_spec ->> 'ticket_type',
    ticket_spec ->> 'ticket_barcode_type',
    ticket_spec ->> 'ticket_name',
    ticket_spec ->> 'ticket_description',
    (ticket_spec ->> 'maximum_adult_size') :: INTEGER,
    (ticket_spec ->> 'minimum_adult_size') :: INTEGER,
    (ticket_spec ->> 'maximum_child_size') :: INTEGER,
    (ticket_spec ->> 'minimum_child_size') :: INTEGER,
    case (ticket_spec ->> 'group_size') :: INTEGER
    when 0
      then 1
    else (ticket_spec ->> 'group_size') :: INTEGER end,
    (ticket_spec ->> 'refundable') :: BOOLEAN,
    (ticket_spec ->> 'active') :: BOOLEAN,
    (ticket_spec ->> 'total_inventory_qty') :: INTEGER,
    COALESCE(ticket_spec ->> 'voucher_format', 'BARCODE'),
    COALESCE(ticket_spec ->> 'voucher_fulfillment', 'AUTOMATIC'),
    COALESCE((ticket_spec ->> 'issuance_approval') :: BOOLEAN, FALSE),
    'SPECIFIC',
    ticket_spec ->> 'agent_url',
    (ticket_spec ->> 'hold_booking_time') :: BIGINT,
    FALSE,
    CASE WHEN (ticket_spec -> 'voucher_usage_validity' IS NULL OR ticket_spec -> 'voucher_usage_validity' = '{}')
      THEN NULL
    ELSE
        row (ticket_spec -> 'voucher_usage_validity' ->> 'validity_days', ticket_spec -> 'voucher_usage_validity' ->>
                                                                          'validity_type',
        ticket_spec -> 'voucher_usage_validity' ->> 'validity_period_from', ticket_spec -> 'voucher_usage_validity' ->>
                                                                            'validity_period_to') :: experience_extranet_ticket.voucher_validity_policy
    END,
    CASE WHEN (ticket_spec -> 'voucher_sales_validity' IS NULL OR ticket_spec -> 'voucher_sales_validity' = '{}')
      THEN NULL
    ELSE
        row (ticket_spec -> 'voucher_sales_validity' ->> 'validity_days', ticket_spec -> 'voucher_sales_validity' ->>
                                                                          'validity_type',
        ticket_spec -> 'voucher_sales_validity' ->> 'validity_period_from', ticket_spec -> 'voucher_sales_validity' ->>
                                                                            'validity_period_to') :: experience_extranet_ticket.voucher_validity_policy
    END,
    ticket_spec ->> 'date_of_birth',
    ticket_spec ->> 'reservation',
    ticket_spec ->> 'inventory_type',
    ticket_spec ->> 'voucher_type',
    ticket_spec ->> 'term_condition',
    ticket_spec ->> 'voucher_code_format',
    (ticket_spec ->> 'reservation_day') :: INTEGER,
    (ticket_spec ->> 'best_deal') :: BOOLEAN,
    ticket_spec ->> 'subtitle',
    (ticket_spec ->> 'direct_entrance') :: BOOLEAN,
    COALESCE((ticket_spec ->> 'ticket_has_commission') :: BOOLEAN, FALSE),
    (ticket_spec ->> 'commission_rate') :: REAL,
    (ticket_spec ->> 'multi_supplier') :: BOOLEAN,
    ticket_spec ->> 'how_to_redeem_description',
    COALESCE((ticket_spec ->> 'need_self_identifier') :: BOOLEAN, FALSE),
    ticket_spec ->> 'self_identifier_required_type',
    ticket_spec ->> 'self_identifier_type',
    ticket_spec ->> 'price_include',
    ticket_spec ->> 'price_exclude',
    ROW(ticket_spec -> 'reservation_time' ->> 'hour', ticket_spec -> 'reservation_time' ->> 'minute')
    :: experience_extranet_experience.hour_minute,
    COALESCE((ticket_spec ->> 'min_reservation_day') :: INTEGER, 0),
    COALESCE((ticket_spec ->> 'need_booking_options') :: BOOLEAN, FALSE),
    COALESCE((ticket_spec ->> 'pushed_to_aggregator') :: BOOLEAN, FALSE)
  );

  FOR policy IN SELECT jsonb_array_elements(ticket_spec -> 'refund_policies') LOOP
    INSERT INTO experience_extranet_ticket.ticket_refund_policy (
      ticket_id,
      num_of_days_prior,
      num_of_days_prior_to,
      percentage,
      active
    )
    VALUES (
      exp_ticket_id,
      (COALESCE(policy->'num_of_days_range_prior'->>'days_from', policy->>'num_of_days_prior'))::INTEGER,
      (COALESCE(policy->'num_of_days_range_prior'->>'days_to', policy->>'num_of_days_prior'))::INTEGER,
      (policy ->> 'percentage') :: DOUBLE PRECISION,
      (policy ->> 'active') :: BOOLEAN
    );
  END LOOP;

  FOR time_slot IN SELECT jsonb_array_elements(ticket_spec -> 'time_slots') LOOP
    INSERT INTO experience_extranet_ticket.ticket_time_slot (
      ticket_id,
      open_time,
      close_time
    )
    VALUES (
      exp_ticket_id,
      row (time_slot -> 'open_time' ->> 'hour', time_slot -> 'open_time' ->>
                                                'minute') :: experience_extranet_experience.hour_minute,
      row (time_slot -> 'close_time' ->> 'hour', time_slot -> 'close_time' ->>
                                                'minute') :: experience_extranet_experience.hour_minute
    );
  END LOOP;

  FOR group_list IN SELECT jsonb_array_elements(ticket_spec -> 'ticket_customer_group_list') LOOP
    FOR group_detail_list IN SELECT jsonb_array_elements(group_list -> 'ticket_customer_group_detail_list') LOOP
      INSERT INTO experience_extranet_content.ticket_customer_group_mapping (
        ticket_id,
        group_unit_id,
        group_unit,
        group_id,
        group_name,
        group_description,
        maximum_size,
        minimum_size,
        quantifier
      )
      VALUES (
        exp_ticket_id,
        COALESCE(group_list ->> 'group_unit_id', REPLACE(UPPER(group_list ->> 'group_unit'), ' ', '_')),
        group_list ->> 'group_unit',
        COALESCE(group_detail_list ->> 'group_id', REPLACE(UPPER(group_detail_list ->> 'group_name'), ' ', '_')),
        group_detail_list ->> 'group_name',
        group_detail_list ->> 'group_description',
        (group_detail_list ->> 'maximum_size') :: INTEGER,
        (group_detail_list ->> 'minimum_size') :: INTEGER,
        (COALESCE(group_detail_list->>'quantifier', '1')) :: INTEGER
      );
    END LOOP;
  END LOOP;

  FOR redeemable IN SELECT jsonb_array_elements(ticket_spec->'redeemable_location_list') LOOP
    INSERT INTO experience_extranet_ticket.ticket_redeemable_mapping (
      redeemable_mapping_id,
      ticket_id,
      experience_id
    )
    VALUES (
      nextval('experience_extranet_ticket.ticket_redeemable_seq'),
      exp_ticket_id,
      redeemable->>'experience_id'
    );
  END LOOP;

  PERFORM experience_extranet_ticket_v2_helper.upsert_ticket_additional_data(exp_ticket_id, ticket_spec);

  PERFORM experience_extranet_ticket_v2_helper.upsert_ticket_term_condition(exp_ticket_id, ticket_spec -> 'ticket_term_condition');

  PERFORM experience_extranet_ticket_v2_helper.upsert_ticket_redemption(exp_ticket_id, ticket_spec -> 'ticket_redemption');

  IF jsonb_typeof(ticket_spec -> 'booking_options') = 'array'
  THEN
    PERFORM experience_extranet_ticket_v2_helper.upsert_booking_options(exp_ticket_id, ticket_spec);
  ELSE END IF;

  -- TODO: delete this when proper feature to map SF config is made
  INSERT INTO experience_extranet_mapping.ticket_crm_message_mapping (ticket_id, template_id, _lut)
  SELECT ticket_id, 1, now()
    FROM experience_extranet_experience.experience e
    JOIN experience_extranet_ticket.ticket t
      ON e.experience_id = t.experience_id
    WHERE (e.tags :: TEXT) LIKE '%EASY_RESERVATION%'
      AND t.ticket_id = exp_ticket_id
  ON CONFLICT DO NOTHING;

  RETURN exp_ticket_id;
END;
$$;


CREATE OR REPLACE FUNCTION experience_extranet_ticket_v2_api.update_ticket(ticket_spec JSONB)
  RETURNS BOOLEAN
SECURITY DEFINER
LANGUAGE plpgsql
AS $$
DECLARE
    refund_policies jsonb;
BEGIN

  UPDATE experience_extranet_ticket.ticket
  SET
    --experience_id         = ticket_spec->>'experience_id',        --must not be updated
    --ticket_type           = ticket_spec->>'ticket_type',          --must not be updated
    --ticket_barcode_type   = ticket_spec->>'ticket_barcode_type',  --must not be updated
    ticket_name            = ticket_spec ->> 'ticket_name',
    ticket_description     = ticket_spec ->> 'ticket_description',
    maximum_adult_size     = (ticket_spec ->> 'maximum_adult_size') :: INTEGER,
    minimum_adult_size     = (ticket_spec ->> 'minimum_adult_size') :: INTEGER,
    maximum_child_size     = (ticket_spec ->> 'maximum_child_size') :: INTEGER,
    minimum_child_size     = (ticket_spec ->> 'minimum_child_size') :: INTEGER,
    group_size             = case (ticket_spec ->> 'group_size') :: INTEGER
                             when 0
                               then 1
                             else (ticket_spec ->> 'group_size') :: INTEGER end,
    refundable             = (ticket_spec ->> 'refundable') :: BOOLEAN,
    active                 = (ticket_spec ->> 'active') :: BOOLEAN,
    total_inventory_qty    = (ticket_spec ->> 'total_inventory_qty') :: INTEGER,
    --voucher_format        = COALESCE(ticket_spec->>'voucher_format', 'BARCODE'),        --must not be updated
    --voucher_fulfillment   = COALESCE(ticket_spec->>'voucher_fulfillment', 'AUTOMATIC'), --must not be updated
    issuance_approval      = COALESCE((ticket_spec ->> 'issuance_approval') :: BOOLEAN, FALSE),
    --ticket_guest_type     = COALESCE(ticket_spec->>'ticket_guest_type', 'GENERIC'),     --must not be updated
    agent_url              = ticket_spec ->> 'agent_url',
    hold_booking_time      = (ticket_spec ->> 'hold_booking_time') :: BIGINT,
    voucher_usage_validity = CASE WHEN (ticket_spec -> 'voucher_usage_validity' IS NULL OR
                                        ticket_spec -> 'voucher_usage_validity' = '{}')
      THEN NULL
                             ELSE
                                 row (ticket_spec -> 'voucher_usage_validity' ->> 'validity_days',
                                 ticket_spec -> 'voucher_usage_validity' ->> 'validity_type',
                                 ticket_spec -> 'voucher_usage_validity' ->> 'validity_period_from',
                                 ticket_spec -> 'voucher_usage_validity' ->>
                                 'validity_period_to') :: experience_extranet_ticket.voucher_validity_policy
                             END,
    voucher_sales_validity = CASE WHEN (ticket_spec -> 'voucher_sales_validity' IS NULL OR
                                        ticket_spec -> 'voucher_sales_validity' = '{}')
      THEN NULL
                             ELSE
                                 row (ticket_spec -> 'voucher_sales_validity' ->> 'validity_days',
                                 ticket_spec -> 'voucher_sales_validity' ->> 'validity_type',
                                 ticket_spec -> 'voucher_sales_validity' ->> 'validity_period_from',
                                 ticket_spec -> 'voucher_sales_validity' ->>
                                 'validity_period_to') :: experience_extranet_ticket.voucher_validity_policy
                             END,
    date_of_birth          = ticket_spec ->> 'date_of_birth',
    reservation            = ticket_spec ->> 'reservation',
    inventory_type         = ticket_spec ->> 'inventory_type',
    voucher_type           = ticket_spec ->> 'voucher_type',
    term_condition         = ticket_spec ->> 'term_condition',
    voucher_code_format    = ticket_spec ->> 'voucher_code_format',
    reservation_day        = (ticket_spec ->> 'reservation_day') :: INTEGER,
    best_deal              = (ticket_spec ->> 'best_deal') :: BOOLEAN,
    subtitle               = ticket_spec ->> 'subtitle',
    direct_entrance        = (ticket_spec ->> 'direct_entrance') :: BOOLEAN,
--  ticket_has_commission  = COALESCE((ticket_spec ->> 'ticket_has_commission') :: BOOLEAN, FALSE),
    commission_rate        = (ticket_spec ->> 'commission_rate') :: DOUBLE PRECISION,
    multi_supplier         = (ticket_spec ->> 'multi_supplier') :: BOOLEAN,
    how_to_redeem_description = ticket_spec ->> 'how_to_redeem_description',
    need_self_identifier   = COALESCE((ticket_spec ->> 'need_self_identifier') :: BOOLEAN, FALSE),
    self_identifier_required_type = ticket_spec ->> 'self_identifier_required_type',
    self_identifier_type   = ticket_spec ->> 'self_identifier_type',
    price_include         = ticket_spec ->> 'price_include',
    price_exclude         = ticket_spec ->> 'price_exclude',
    reservation_time      = ROW(ticket_spec -> 'reservation_time' ->> 'hour', ticket_spec -> 'reservation_time' ->> 'minute')
                            :: experience_extranet_experience.hour_minute,
    min_reservation_day   = COALESCE((ticket_spec ->> 'min_reservation_day') :: INTEGER, 0),
    need_booking_options   = COALESCE((ticket_spec ->> 'need_booking_options') :: BOOLEAN, FALSE),
    pushed_to_aggregator   = COALESCE((ticket_spec ->> 'pushed_to_aggregator') :: BOOLEAN, FALSE)
  WHERE experience_extranet_ticket.ticket.ticket_id = (ticket_spec ->> 'ticket_id');

  DELETE FROM experience_extranet_ticket.ticket_refund_policy
  WHERE NOT EXISTS(
      SELECT num_of_days_prior
      FROM jsonb_array_elements(ticket_spec -> 'refund_policies') AS elem
      WHERE num_of_days_prior = (elem ->> 'num_of_days_prior') :: INTEGER
  ) AND ticket_id = ticket_spec ->> 'ticket_id';

  FOR refund_policies IN SELECT jsonb_array_elements(ticket_spec -> 'refund_policies') LOOP
    INSERT INTO experience_extranet_ticket.ticket_refund_policy (
    ticket_id,
    num_of_days_prior,
    num_of_days_prior_to,
    percentage,
    active
  )
    SELECT
      ticket_spec ->> 'ticket_id',
      COALESCE(refund_policies -> 'num_of_days_range_prior' ->> 'days_from', refund_policies ->> 'num_of_days_prior')::INTEGER,
      COALESCE(refund_policies -> 'num_of_days_range_prior' ->> 'days_to', refund_policies ->> 'num_of_days_prior')::INTEGER,
      (refund_policies ->> 'percentage') :: DOUBLE PRECISION,
      (refund_policies ->> 'active') :: BOOLEAN

  ON CONFLICT (ticket_id, num_of_days_prior)
    DO UPDATE
      SET
        percentage = EXCLUDED.percentage,
        num_of_days_prior_to = EXCLUDED.num_of_days_prior_to,
        active     = EXCLUDED.active;
  END LOOP;

  DELETE FROM experience_extranet_ticket.ticket_time_slot
  WHERE ticket_id = ticket_spec ->> 'ticket_id';


  INSERT INTO experience_extranet_ticket.ticket_time_slot (
    ticket_id,
    open_time,
    close_time
  )
    SELECT
      ticket_id,
        row (open_time_hour, open_time_minute) :: experience_extranet_experience.hour_minute,
        row (close_time_hour, close_time_minute) :: experience_extranet_experience.hour_minute
    FROM (
           SELECT
             ticket_spec ->> 'ticket_id' AS "ticket_id",
             (jsonb_array_elements(ticket_spec -> 'time_slots') -> 'open_time' ->>
              'hour') :: INTEGER         AS "open_time_hour",
             (jsonb_array_elements(ticket_spec -> 'time_slots') -> 'open_time' ->>
              'minute') :: INTEGER       AS "open_time_minute",
             (jsonb_array_elements(ticket_spec -> 'time_slots') -> 'close_time' ->>
              'hour') :: INTEGER         AS "close_time_hour",
             (jsonb_array_elements(ticket_spec -> 'time_slots') -> 'close_time' ->>
              'minute') :: INTEGER       AS "close_time_minute"
         ) ticket_time_slot

  ON CONFLICT DO NOTHING;

  PERFORM experience_extranet_ticket_v2_helper.upsert_ticket_additional_data(ticket_spec ->> 'ticket_id', ticket_spec);

  PERFORM experience_extranet_ticket_v2_helper.upsert_ticket_customer_group(ticket_spec);

  PERFORM experience_extranet_ticket_v2_helper.upsert_ticket_redeemable_mapping(ticket_spec);

  PERFORM experience_extranet_ticket_v2_helper.upsert_ticket_term_condition(ticket_spec ->> 'ticket_id', ticket_spec -> 'ticket_term_condition');

  PERFORM experience_extranet_ticket_v2_helper.upsert_ticket_redemption(ticket_spec ->> 'ticket_id', ticket_spec -> 'ticket_redemption');

  IF jsonb_typeof(ticket_spec -> 'booking_options') = 'array'
  THEN
    PERFORM experience_extranet_ticket_v2_helper.upsert_booking_options(ticket_spec ->> 'ticket_id', ticket_spec);
  ELSE END IF;

  RETURN FOUND;

END;
$$;


CREATE OR REPLACE FUNCTION experience_extranet_ticket_v2_api.insert_ticket(ticket_spec jsonb) returns text
	security definer
	language plpgsql
as $$
DECLARE
  policy            jsonb;
  time_slot         jsonb;
  exp_ticket_id     TEXT;
  group_list        jsonb;
  group_detail_list jsonb;
  redeemable        jsonb;
BEGIN
  exp_ticket_id := concat(ticket_spec ->> 'experience_id', '.',
                          nextval('experience_extranet_ticket.experience_ticket_seq'));

  INSERT INTO experience_extranet_ticket.ticket (
    ticket_id,
    experience_id,
    ticket_type,
    ticket_barcode_type,
    ticket_name,
    ticket_description,
    maximum_adult_size,
    minimum_adult_size,
    maximum_child_size,
    minimum_child_size,
    group_size,
    refundable,
    active,
    total_inventory_qty,
    voucher_format,
    voucher_fulfillment,
    issuance_approval,
    ticket_guest_type,
    agent_url,
    hold_booking_time,
    deleted,
    voucher_usage_validity,
    voucher_sales_validity,
    date_of_birth,
    reservation,
    inventory_type,
    voucher_type,
    term_condition,
    voucher_code_format,
    reservation_day,
    best_deal,
    subtitle,
    direct_entrance,
    ticket_has_commission,
    commission_rate,
    multi_supplier,
    how_to_redeem_description,
    need_self_identifier,
    self_identifier_required_type,
    self_identifier_type,
    price_include,
    price_exclude,
    reservation_time,
    min_reservation_day,
    need_booking_options,
    pushed_to_aggregator
  )
  VALUES (
    exp_ticket_id,
    ticket_spec ->> 'experience_id',
    ticket_spec ->> 'ticket_type',
    ticket_spec ->> 'ticket_barcode_type',
    ticket_spec ->> 'ticket_name',
    ticket_spec ->> 'ticket_description',
    (ticket_spec ->> 'maximum_adult_size') :: INTEGER,
    (ticket_spec ->> 'minimum_adult_size') :: INTEGER,
    (ticket_spec ->> 'maximum_child_size') :: INTEGER,
    (ticket_spec ->> 'minimum_child_size') :: INTEGER,
    case (ticket_spec ->> 'group_size') :: INTEGER
    when 0
      then 1
    else (ticket_spec ->> 'group_size') :: INTEGER end,
    (ticket_spec ->> 'refundable') :: BOOLEAN,
    (ticket_spec ->> 'active') :: BOOLEAN,
    (ticket_spec ->> 'total_inventory_qty') :: INTEGER,
    COALESCE(ticket_spec ->> 'voucher_format', 'BARCODE'),
    COALESCE(ticket_spec ->> 'voucher_fulfillment', 'AUTOMATIC'),
    COALESCE((ticket_spec ->> 'issuance_approval') :: BOOLEAN, FALSE),
    'SPECIFIC',
    ticket_spec ->> 'agent_url',
    (ticket_spec ->> 'hold_booking_time') :: BIGINT,
    FALSE,
    CASE WHEN (ticket_spec -> 'voucher_usage_validity' IS NULL OR ticket_spec -> 'voucher_usage_validity' = '{}')
      THEN NULL
    ELSE
        row (ticket_spec -> 'voucher_usage_validity' ->> 'validity_days', ticket_spec -> 'voucher_usage_validity' ->>
                                                                          'validity_type',
        ticket_spec -> 'voucher_usage_validity' ->> 'validity_period_from', ticket_spec -> 'voucher_usage_validity' ->>
                                                                            'validity_period_to') :: experience_extranet_ticket.voucher_validity_policy
    END,
    CASE WHEN (ticket_spec -> 'voucher_sales_validity' IS NULL OR ticket_spec -> 'voucher_sales_validity' = '{}')
      THEN NULL
    ELSE
        row (ticket_spec -> 'voucher_sales_validity' ->> 'validity_days', ticket_spec -> 'voucher_sales_validity' ->>
                                                                          'validity_type',
        ticket_spec -> 'voucher_sales_validity' ->> 'validity_period_from', ticket_spec -> 'voucher_sales_validity' ->>
                                                                            'validity_period_to') :: experience_extranet_ticket.voucher_validity_policy
    END,
    ticket_spec ->> 'date_of_birth',
    ticket_spec ->> 'reservation',
    ticket_spec ->> 'inventory_type',
    ticket_spec ->> 'voucher_type',
    ticket_spec ->> 'term_condition',
    ticket_spec ->> 'voucher_code_format',
    (ticket_spec ->> 'reservation_day') :: INTEGER,
    (ticket_spec ->> 'best_deal') :: BOOLEAN,
    ticket_spec ->> 'subtitle',
    (ticket_spec ->> 'direct_entrance') :: BOOLEAN,
    COALESCE((ticket_spec ->> 'ticket_has_commission') :: BOOLEAN, FALSE),
    (ticket_spec ->> 'commission_rate') :: REAL,
    (ticket_spec ->> 'multi_supplier') :: BOOLEAN,
    ticket_spec ->> 'how_to_redeem_description',
    COALESCE((ticket_spec ->> 'need_self_identifier') :: BOOLEAN, FALSE),
    ticket_spec ->> 'self_identifier_required_type',
    ticket_spec ->> 'self_identifier_type',
    ticket_spec ->> 'price_include',
    ticket_spec ->> 'price_exclude',
    ROW(ticket_spec -> 'reservation_time' ->> 'hour', ticket_spec -> 'reservation_time' ->> 'minute')
    :: experience_extranet_experience.hour_minute,
    COALESCE((ticket_spec ->> 'min_reservation_day') :: INTEGER, 0),
    COALESCE((ticket_spec ->> 'need_booking_options') :: BOOLEAN, FALSE),
    COALESCE((ticket_spec ->> 'pushed_to_aggregator') :: BOOLEAN, FALSE)
  );

  FOR policy IN SELECT jsonb_array_elements(ticket_spec -> 'refund_policies') LOOP
    INSERT INTO experience_extranet_ticket.ticket_refund_policy (
      ticket_id,
      num_of_days_prior,
      percentage,
      active
    )
    VALUES (
      exp_ticket_id,
      (policy ->> 'num_of_days_prior') :: INTEGER,
      (policy ->> 'percentage') :: DOUBLE PRECISION,
      (policy ->> 'active') :: BOOLEAN
    );
  END LOOP;

  FOR time_slot IN SELECT jsonb_array_elements(ticket_spec -> 'time_slots') LOOP
    INSERT INTO experience_extranet_ticket.ticket_time_slot (
      ticket_id,
      open_time,
      close_time
    )
    VALUES (
      exp_ticket_id,
      row (time_slot -> 'open_time' ->> 'hour', time_slot -> 'open_time' ->>
                                                'minute') :: experience_extranet_experience.hour_minute,
      row (time_slot -> 'close_time' ->> 'hour', time_slot -> 'close_time' ->>
                                                'minute') :: experience_extranet_experience.hour_minute
    );
  END LOOP;

  FOR group_list IN SELECT jsonb_array_elements(ticket_spec -> 'ticket_customer_group_list') LOOP
    FOR group_detail_list IN SELECT jsonb_array_elements(group_list -> 'ticket_customer_group_detail_list') LOOP
      INSERT INTO experience_extranet_content.ticket_customer_group_mapping (
        ticket_id,
        group_unit_id,
        group_unit,
        group_id,
        group_name,
        group_description,
        maximum_size,
        minimum_size,
        quantifier
      )
      VALUES (
        exp_ticket_id,
        COALESCE(group_list ->> 'group_unit_id', REPLACE(UPPER(group_list ->> 'group_unit'), ' ', '_')),
        group_list ->> 'group_unit',
        COALESCE(group_detail_list ->> 'group_id', REPLACE(UPPER(group_detail_list ->> 'group_name'), ' ', '_')),
        group_detail_list ->> 'group_name',
        group_detail_list ->> 'group_description',
        (group_detail_list ->> 'maximum_size') :: INTEGER,
        (group_detail_list ->> 'minimum_size') :: INTEGER,
        (COALESCE(group_detail_list->>'quantifier', '1')) :: INTEGER
      );
    END LOOP;
  END LOOP;

  FOR redeemable IN SELECT jsonb_array_elements(ticket_spec->'redeemable_location_list') LOOP
    INSERT INTO experience_extranet_ticket.ticket_redeemable_mapping (
      redeemable_mapping_id,
      ticket_id,
      experience_id
    )
    VALUES (
      nextval('experience_extranet_ticket.ticket_redeemable_seq'),
      exp_ticket_id,
      redeemable->>'experience_id'
    );
  END LOOP;

  PERFORM experience_extranet_ticket_v2_helper.upsert_ticket_additional_data(exp_ticket_id, ticket_spec);

  PERFORM experience_extranet_ticket_v2_helper.upsert_ticket_term_condition(exp_ticket_id, ticket_spec -> 'ticket_term_condition');

  PERFORM experience_extranet_ticket_v2_helper.upsert_ticket_redemption(exp_ticket_id, ticket_spec -> 'ticket_redemption');

  IF jsonb_typeof(ticket_spec -> 'booking_options') = 'array'
  THEN
    PERFORM experience_extranet_ticket_v2_helper.upsert_booking_options(exp_ticket_id, ticket_spec);
  ELSE END IF;

  -- TODO: delete this when proper feature to map SF config is made
  INSERT INTO experience_extranet_mapping.ticket_crm_message_mapping (ticket_id, template_id, _lut)
  SELECT ticket_id, 1, now()
    FROM experience_extranet_experience.experience e
    JOIN experience_extranet_ticket.ticket t
      ON e.experience_id = t.experience_id
    WHERE (e.tags :: TEXT) LIKE '%EASY_RESERVATION%'
      AND t.ticket_id = exp_ticket_id
  ON CONFLICT DO NOTHING;

  RETURN exp_ticket_id;
END;
$$;

CREATE OR REPLACE FUNCTION experience_extranet_ticket_v2_api.insert_ticket(ticket_spec jsonb) returns text
	security definer
	language plpgsql
as $$
DECLARE
  policy            jsonb;
  time_slot         jsonb;
  exp_ticket_id     TEXT;
  group_list        jsonb;
  group_detail_list jsonb;
  redeemable        jsonb;
BEGIN
  exp_ticket_id := concat(ticket_spec ->> 'experience_id', '.',
                          nextval('experience_extranet_ticket.experience_ticket_seq'));

  INSERT INTO experience_extranet_ticket.ticket (
    ticket_id,
    experience_id,
    ticket_type,
    ticket_barcode_type,
    ticket_name,
    ticket_description,
    maximum_adult_size,
    minimum_adult_size,
    maximum_child_size,
    minimum_child_size,
    group_size,
    refundable,
    active,
    total_inventory_qty,
    voucher_format,
    voucher_fulfillment,
    issuance_approval,
    ticket_guest_type,
    agent_url,
    hold_booking_time,
    deleted,
    voucher_usage_validity,
    voucher_sales_validity,
    date_of_birth,
    reservation,
    inventory_type,
    voucher_type,
    term_condition,
    voucher_code_format,
    reservation_day,
    best_deal,
    subtitle,
    direct_entrance,
    ticket_has_commission,
    commission_rate,
    multi_supplier,
    how_to_redeem_description,
    need_self_identifier,
    self_identifier_required_type,
    self_identifier_type,
    price_include,
    price_exclude,
    reservation_time,
    min_reservation_day,
    need_booking_options,
    pushed_to_aggregator
  )
  VALUES (
    exp_ticket_id,
    ticket_spec ->> 'experience_id',
    ticket_spec ->> 'ticket_type',
    ticket_spec ->> 'ticket_barcode_type',
    ticket_spec ->> 'ticket_name',
    ticket_spec ->> 'ticket_description',
    (ticket_spec ->> 'maximum_adult_size') :: INTEGER,
    (ticket_spec ->> 'minimum_adult_size') :: INTEGER,
    (ticket_spec ->> 'maximum_child_size') :: INTEGER,
    (ticket_spec ->> 'minimum_child_size') :: INTEGER,
    case (ticket_spec ->> 'group_size') :: INTEGER
    when 0
      then 1
    else (ticket_spec ->> 'group_size') :: INTEGER end,
    (ticket_spec ->> 'refundable') :: BOOLEAN,
    (ticket_spec ->> 'active') :: BOOLEAN,
    (ticket_spec ->> 'total_inventory_qty') :: INTEGER,
    COALESCE(ticket_spec ->> 'voucher_format', 'BARCODE'),
    COALESCE(ticket_spec ->> 'voucher_fulfillment', 'AUTOMATIC'),
    COALESCE((ticket_spec ->> 'issuance_approval') :: BOOLEAN, FALSE),
    'SPECIFIC',
    ticket_spec ->> 'agent_url',
    (ticket_spec ->> 'hold_booking_time') :: BIGINT,
    FALSE,
    CASE WHEN (ticket_spec -> 'voucher_usage_validity' IS NULL OR ticket_spec -> 'voucher_usage_validity' = '{}')
      THEN NULL
    ELSE
        row (ticket_spec -> 'voucher_usage_validity' ->> 'validity_days', ticket_spec -> 'voucher_usage_validity' ->>
                                                                          'validity_type',
        ticket_spec -> 'voucher_usage_validity' ->> 'validity_period_from', ticket_spec -> 'voucher_usage_validity' ->>
                                                                            'validity_period_to') :: experience_extranet_ticket.voucher_validity_policy
    END,
    CASE WHEN (ticket_spec -> 'voucher_sales_validity' IS NULL OR ticket_spec -> 'voucher_sales_validity' = '{}')
      THEN NULL
    ELSE
        row (ticket_spec -> 'voucher_sales_validity' ->> 'validity_days', ticket_spec -> 'voucher_sales_validity' ->>
                                                                          'validity_type',
        ticket_spec -> 'voucher_sales_validity' ->> 'validity_period_from', ticket_spec -> 'voucher_sales_validity' ->>
                                                                            'validity_period_to') :: experience_extranet_ticket.voucher_validity_policy
    END,
    ticket_spec ->> 'date_of_birth',
    ticket_spec ->> 'reservation',
    ticket_spec ->> 'inventory_type',
    ticket_spec ->> 'voucher_type',
    ticket_spec ->> 'term_condition',
    ticket_spec ->> 'voucher_code_format',
    (ticket_spec ->> 'reservation_day') :: INTEGER,
    (ticket_spec ->> 'best_deal') :: BOOLEAN,
    ticket_spec ->> 'subtitle',
    (ticket_spec ->> 'direct_entrance') :: BOOLEAN,
    COALESCE((ticket_spec ->> 'ticket_has_commission') :: BOOLEAN, FALSE),
    (ticket_spec ->> 'commission_rate') :: REAL,
    (ticket_spec ->> 'multi_supplier') :: BOOLEAN,
    ticket_spec ->> 'how_to_redeem_description',
    COALESCE((ticket_spec ->> 'need_self_identifier') :: BOOLEAN, FALSE),
    ticket_spec ->> 'self_identifier_required_type',
    ticket_spec ->> 'self_identifier_type',
    ticket_spec ->> 'price_include',
    ticket_spec ->> 'price_exclude',
    ROW(ticket_spec -> 'reservation_time' ->> 'hour', ticket_spec -> 'reservation_time' ->> 'minute')
    :: experience_extranet_experience.hour_minute,
    COALESCE((ticket_spec ->> 'min_reservation_day') :: INTEGER, 0),
    COALESCE((ticket_spec ->> 'need_booking_options') :: BOOLEAN, FALSE),
    COALESCE((ticket_spec ->> 'pushed_to_aggregator') :: BOOLEAN, FALSE)
  );

  FOR policy IN SELECT jsonb_array_elements(ticket_spec -> 'refund_policies') LOOP
    INSERT INTO experience_extranet_ticket.ticket_refund_policy (
      ticket_id,
      num_of_days_prior,
      percentage,
      active
    )
    VALUES (
      exp_ticket_id,
      (policy ->> 'num_of_days_prior') :: INTEGER,
      (policy ->> 'percentage') :: DOUBLE PRECISION,
      (policy ->> 'active') :: BOOLEAN
    );
  END LOOP;

  FOR time_slot IN SELECT jsonb_array_elements(ticket_spec -> 'time_slots') LOOP
    INSERT INTO experience_extranet_ticket.ticket_time_slot (
      ticket_id,
      open_time,
      close_time
    )
    VALUES (
      exp_ticket_id,
      row (time_slot -> 'open_time' ->> 'hour', time_slot -> 'open_time' ->>
                                                'minute') :: experience_extranet_experience.hour_minute,
      row (time_slot -> 'close_time' ->> 'hour', time_slot -> 'close_time' ->>
                                                'minute') :: experience_extranet_experience.hour_minute
    );
  END LOOP;

  FOR group_list IN SELECT jsonb_array_elements(ticket_spec -> 'ticket_customer_group_list') LOOP
    FOR group_detail_list IN SELECT jsonb_array_elements(group_list -> 'ticket_customer_group_detail_list') LOOP
      INSERT INTO experience_extranet_content.ticket_customer_group_mapping (
        ticket_id,
        group_unit_id,
        group_unit,
        group_id,
        group_name,
        group_description,
        maximum_size,
        minimum_size,
        quantifier
      )
      VALUES (
        exp_ticket_id,
        COALESCE(group_list ->> 'group_unit_id', REPLACE(UPPER(group_list ->> 'group_unit'), ' ', '_')),
        group_list ->> 'group_unit',
        COALESCE(group_detail_list ->> 'group_id', REPLACE(UPPER(group_detail_list ->> 'group_name'), ' ', '_')),
        group_detail_list ->> 'group_name',
        group_detail_list ->> 'group_description',
        (group_detail_list ->> 'maximum_size') :: INTEGER,
        (group_detail_list ->> 'minimum_size') :: INTEGER,
        (COALESCE(group_detail_list->>'quantifier', '1')) :: INTEGER
      );
    END LOOP;
  END LOOP;

  FOR redeemable IN SELECT jsonb_array_elements(ticket_spec->'redeemable_location_list') LOOP
    INSERT INTO experience_extranet_ticket.ticket_redeemable_mapping (
      redeemable_mapping_id,
      ticket_id,
      experience_id
    )
    VALUES (
      nextval('experience_extranet_ticket.ticket_redeemable_seq'),
      exp_ticket_id,
      redeemable->>'experience_id'
    );
  END LOOP;

  PERFORM experience_extranet_ticket_v2_helper.upsert_ticket_additional_data(exp_ticket_id, ticket_spec);

  PERFORM experience_extranet_ticket_v2_helper.upsert_ticket_term_condition(exp_ticket_id, ticket_spec -> 'ticket_term_condition');

  PERFORM experience_extranet_ticket_v2_helper.upsert_ticket_redemption(exp_ticket_id, ticket_spec -> 'ticket_redemption');

  IF jsonb_typeof(ticket_spec -> 'booking_options') = 'array'
  THEN
    PERFORM experience_extranet_ticket_v2_helper.upsert_booking_options(exp_ticket_id, ticket_spec);
  ELSE END IF;

  -- TODO: delete this when proper feature to map SF config is made
  INSERT INTO experience_extranet_mapping.ticket_crm_message_mapping (ticket_id, template_id, _lut)
  SELECT ticket_id, 1, now()
    FROM experience_extranet_experience.experience e
    JOIN experience_extranet_ticket.ticket t
      ON e.experience_id = t.experience_id
    WHERE (e.tags :: TEXT) LIKE '%EASY_RESERVATION%'
      AND t.ticket_id = exp_ticket_id
  ON CONFLICT DO NOTHING;

  RETURN exp_ticket_id;
END;
$$;