Create Functions

USE martech;

DELIMITER //
CREATE OR REPLACE FUNCTION date_add_dynamic(_dt DATETIME(6), _interval enum('second','minute','hour','day','week','month')) RETURNS DATETIME(6) AS
   BEGIN
       RETURN CASE _interval
           WHEN "second" THEN _dt + INTERVAL 1 SECOND
           WHEN "minute" THEN _dt + INTERVAL 1 MINUTE
           WHEN "hour" THEN _dt + INTERVAL 1 HOUR
           WHEN "day" THEN _dt + INTERVAL 1 DAY
           WHEN "week" THEN _dt + INTERVAL 1 WEEK
           WHEN "month" THEN _dt + INTERVAL 1 MONTH
       END;
   END //

CREATE OR REPLACE FUNCTION date_sub_dynamic(_dt DATETIME(6) NULL, _interval enum('second','minute','hour','day','week','month') CHARACTER SET utf8 COLLATE utf8_general_ci NULL) RETURNS DATETIME(6) NULL AS
 BEGIN
   RETURN CASE _interval
     WHEN "second" THEN _dt - INTERVAL 1 SECOND
     WHEN "minute" THEN _dt - INTERVAL 1 MINUTE
     WHEN "hour" THEN _dt - INTERVAL 1 HOUR
     WHEN "day" THEN _dt - INTERVAL 1 DAY
     WHEN "week" THEN _dt - INTERVAL 1 WEEK
     WHEN "month" THEN _dt - INTERVAL 1 MONTH
   END;
 END //


CREATE OR REPLACE FUNCTION encode_open_location_code(_lonlat geographypoint NULL, codeLength int(11) NULL DEFAULT 12)
   RETURNS text CHARACTER SET utf8 COLLATE utf8_general_ci NULL
   AS
   DECLARE
       SEPARATOR_ text = '+';
       SEPARATOR_POSITION_ int = 8;
       PADDING_CHARACTER_ text = '0';
       CODE_ALPHABET_ text = '23456789CFGHJMPQRVWX';
       ENCODING_BASE_ int = CHARACTER_LENGTH(CODE_ALPHABET_);
       LATITUDE_MAX_ int = 90;
       LONGITUDE_MAX_ int = 180;
       MAX_DIGIT_COUNT_ int = 15;
       PAIR_CODE_LENGTH_ int = 10;
       PAIR_PRECISION_ decimal = POWER(ENCODING_BASE_, 3);
       GRID_CODE_LENGTH_ int = MAX_DIGIT_COUNT_ - PAIR_CODE_LENGTH_;
       GRID_COLUMNS_ int = 4;
       GRID_ROWS_ int = 5;
       FINAL_LAT_PRECISION_ decimal = PAIR_PRECISION_ * POWER(GRID_ROWS_, MAX_DIGIT_COUNT_ - PAIR_CODE_LENGTH_);
       FINAL_LNG_PRECISION_ decimal = PAIR_PRECISION_ * POWER(GRID_COLUMNS_, MAX_DIGIT_COUNT_ - PAIR_CODE_LENGTH_);
       latitude double = geography_latitude(_lonlat);
       longitude double = geography_longitude(_lonlat);
    code text = '';
       latVal decimal = 0;
       lngVal decimal = 0;
       latDigit smallint;
       lngDigit smallint;
       ndx smallint;
       i_ smallint;
   BEGIN
       IF ((codeLength < 2) OR ((codeLength < PAIR_CODE_LENGTH_) AND (codeLength % 2 = 1)) OR (codeLength > MAX_DIGIT_COUNT_)) THEN
           RAISE USER_EXCEPTION(CONCAT('Invalid Open Location Code length - ', codeLength));
       END IF;
        latVal = floor(round((latitude + LATITUDE_MAX_) * FINAL_LAT_PRECISION_, 6));
       lngVal = floor(round((longitude + LONGITUDE_MAX_) * FINAL_LNG_PRECISION_, 6));
        IF (codeLength > PAIR_CODE_LENGTH_) THEN
           i_ = 0;
       WHILE (i_ < (MAX_DIGIT_COUNT_ - PAIR_CODE_LENGTH_)) LOOP
           latDigit = latVal % GRID_ROWS_;
           lngDigit = lngVal % GRID_COLUMNS_;
           ndx = (latDigit * GRID_COLUMNS_) + lngDigit;
           code = concat(substr(CODE_ALPHABET_, ndx + 1, 1), code);
           latVal = latVal DIV GRID_ROWS_;
           lngVal = lngVal DIV GRID_COLUMNS_;
           i_ = i_ + 1;
       END LOOP;
       ELSE
           latVal = latVal DIV power(GRID_ROWS_, GRID_CODE_LENGTH_);
           lngVal = lngVal DIV power(GRID_COLUMNS_, GRID_CODE_LENGTH_);
       END IF;
        i_ = 0;
       WHILE (i_ < (PAIR_CODE_LENGTH_ / 2)) LOOP
           code = concat(substr(CODE_ALPHABET_, (lngVal % ENCODING_BASE_) + 1, 1), code);
           code = concat(substr(CODE_ALPHABET_, (latVal % ENCODING_BASE_) + 1, 1), code);
           latVal = latVal DIV ENCODING_BASE_;
           lngVal = lngVal DIV ENCODING_BASE_;
           i_ = i_ + 1;
       END LOOP;
        code = concat(
           substr(code, 1, SEPARATOR_POSITION_),
           SEPARATOR_,
           substr(code, SEPARATOR_POSITION_ + 1)
       );
        IF (codeLength > SEPARATOR_POSITION_) THEN
           RETURN substr(code, 1, codeLength+1);
       ELSE
           RETURN substr(code, 1, codeLength);
       END IF;
   END //
DELIMITER ;

USE martech;

CREATE OR REPLACE FUNCTION dynamic_subscriber_segments_locations(_since DATETIME(6) NULL, _until DATETIME(6) NULL) RETURNS TABLE AS
   RETURN
       SELECT locations.city_id AS city_id,
           locations.subscriber_id AS subscriber_id,
           segments.segment_id AS segment_id,
           MAX(date_add_dynamic(locations.ts,segments.valid_interval)) AS expires_at
       FROM (segments as segments  JOIN locations as locations )
       WHERE ((segments.filter_kind = 'olc_8')
           AND
               (segments.filter_value = locations.olc_8)
           AND
               (locations.ts >= date_sub_dynamic( NOW(6),segments.valid_interval)) AND (locations.ts >= _since) AND (locations.ts < _until))
       GROUP BY 1, 2, 3;
  

CREATE OR REPLACE FUNCTION dynamic_subscriber_segments_requests(_since DATETIME(6) NULL, _until DATETIME(6) NULL) RETURNS TABLE AS
   RETURN
       SELECT requests.city_id AS city_id,
           requests.subscriber_id AS subscriber_id,
           segments.segment_id AS segment_id,
           MAX(date_add_dynamic(requests.ts,segments.valid_interval)) AS expires_at
       FROM (segments as segments  JOIN requests as requests )
       WHERE ((segments.filter_kind = 'request')
           AND
               (segments.filter_value = requests.domain)
           AND
               (requests.ts >= date_sub_dynamic( NOW(6),segments.valid_interval))
           AND
               (requests.ts >= _since) AND (requests.ts < _until))
       GROUP BY 1, 2, 3;

CREATE OR REPLACE FUNCTION dynamic_subscriber_segments_purchases(_since DATETIME(6) NULL, _until DATETIME(6) NULL) RETURNS TABLE AS
   RETURN
       SELECT purchases.city_id AS city_id,
           purchases.subscriber_id AS subscriber_id,
           segments.segment_id AS segment_id,
           MAX(date_add_dynamic(purchases.ts,segments.valid_interval)) AS expires_at
       FROM (segments as segments  JOIN purchases as purchases )
       WHERE ((segments.filter_kind = 'purchase')
           AND
               (segments.filter_value = purchases.vendor)
           AND
               (purchases.ts >= date_sub_dynamic( NOW(6),segments.valid_interval)) AND (purchases.ts >= _since) AND (purchases.ts < _until))
       GROUP BY 1, 2, 3;


CREATE OR REPLACE FUNCTION dynamic_subscriber_segments(_since DATETIME(6) NULL, _until DATETIME(6) NULL) RETURNS TABLE AS
   RETURN
       SELECT dynamic_subscriber_segments_locations.city_id AS city_id,
           dynamic_subscriber_segments_locations.subscriber_id AS subscriber_id,
           dynamic_subscriber_segments_locations.segment_id AS segment_id,
           dynamic_subscriber_segments_locations.expires_at AS expires_at
       FROM dynamic_subscriber_segments_locations(_since, _until) as dynamic_subscriber_segments_locations 
       UNION ALL
       SELECT dynamic_subscriber_segments_requests.city_id AS city_id,
           dynamic_subscriber_segments_requests.subscriber_id AS subscriber_id,
           dynamic_subscriber_segments_requests.segment_id AS segment_id,
           dynamic_subscriber_segments_requests.expires_at AS expires_at
       FROM dynamic_subscriber_segments_requests(_since, _until) as dynamic_subscriber_segments_requests 
       UNION ALL
       (SELECT dynamic_subscriber_segments_purchases.city_id AS city_id,
           dynamic_subscriber_segments_purchases.subscriber_id AS subscriber_id,
           dynamic_subscriber_segments_purchases.segment_id AS segment_id,
           dynamic_subscriber_segments_purchases.expires_at AS expires_at
       FROM dynamic_subscriber_segments_purchases(_since, _until) as dynamic_subscriber_segments_purchases );

CREATE OR REPLACE FUNCTION match_offers_to_subscribers(_interval enum('second','minute','hour','day','week','month') CHARACTER SET utf8 COLLATE utf8_general_ci NULL)
   RETURNS TABLE
   AS
   RETURN WITH phase_1 AS
   (SELECT offers.offer_id AS offer_id,
       offers.customer AS customer,
       offers.enabled AS enabled,
       offers.notification_zone AS notification_zone,
       offers.segment_ids AS segment_ids,
       offers.notification_content AS notification_content,
       offers.notification_target AS notification_target,
       offers.maximum_bid_cents AS maximum_bid_cents,
       subscribers.city_id AS city_id,
       subscribers.subscriber_id AS subscriber_id,
       subscribers.current_location AS current_location
   FROM (offers as offers  JOIN (subscribers as subscribers
       LEFT JOIN subscribers_last_notification as subscribers_last_notification
       WITH (table_convert_subselect = TRUE)
       ON ((subscribers.city_id = subscribers_last_notification.city_id)
       AND (subscribers.subscriber_id = subscribers_last_notification.subscriber_id))))
       WHERE ((offers.enabled = 1) AND  GEOGRAPHY_CONTAINS(offers.notification_zone,subscribers.current_location)
       AND ( ISNULL(subscribers_last_notification.last_notification)
       OR (subscribers_last_notification.last_notification < date_sub_dynamic( NOW(),_interval)))
       AND (NOT  EXISTS( SELECT n.ts AS ts, n.city_id AS city_id, n.subscriber_id AS subscriber_id,
           n.offer_id AS offer_id, n.cost_cents AS cost_cents,
           n.lonlat AS lonlat FROM notifications as n 
           WHERE ((n.ts > date_sub_dynamic( NOW(),_interval))
           AND (offers.offer_id = n.offer_id) AND (subscribers.city_id = n.city_id)
           AND (subscribers.subscriber_id = n.subscriber_id)) LIMIT 1 )))), phase_2 AS (SELECT phase_1.offer_id AS offer_id,
               phase_1.customer AS customer, phase_1.enabled AS enabled, phase_1.notification_zone AS notification_zone,
               phase_1.segment_ids AS segment_ids, phase_1.notification_content AS notification_content,
               phase_1.notification_target AS notification_target, phase_1.maximum_bid_cents AS maximum_bid_cents,
               phase_1.city_id AS city_id, phase_1.subscriber_id AS subscriber_id, phase_1.current_location AS current_location, 
               ROW_NUMBER() OVER (PARTITION BY phase_1.city_id, phase_1.offer_id, phase_1.subscriber_id) AS num_matching_segments
           FROM (( phase_1 AS phase_1 JOIN  TABLE( JSON_TO_ARRAY(phase_1.segment_ids)) AS segment_ids)
               LEFT  JOIN subscriber_segments as segment 
               ON ((phase_1.city_id = segment.city_id) AND (phase_1.subscriber_id = segment.subscriber_id)
               AND ((segment_ids.table_col:>bigint(20) NULL) = segment.segment_id))))
               SELECT phase_2.city_id AS city_id, phase_2.subscriber_id AS subscriber_id, 
                   LAST_VALUE(phase_2.offer_id) OVER (PARTITION BY phase_2.city_id, phase_2.subscriber_id ORDER BY phase_2.maximum_bid_cents) AS best_offer_id, 
                   LAST_VALUE(phase_2.maximum_bid_cents) OVER (PARTITION BY phase_2.city_id, phase_2.subscriber_id ORDER BY phase_2.maximum_bid_cents) AS cost_cents,
                   phase_2.current_location AS current_location
               FROM  phase_2 AS phase_2
               WHERE ( JSON_LENGTH(phase_2.segment_ids) = phase_2.num_matching_segments)
               GROUP BY 1, 2;