· 5 years ago · Mar 08, 2020, 12:22 AM
1--
2-- PostgreSQL database dump
3--
4
5-- Dumped from database version 12.1
6-- Dumped by pg_dump version 12.1
7
8-- Started on 2020-03-08 02:19:17
9
10SET statement_timeout = 0;
11SET lock_timeout = 0;
12SET idle_in_transaction_session_timeout = 0;
13SET client_encoding = 'UTF8';
14SET standard_conforming_strings = on;
15SELECT pg_catalog.set_config('search_path', '', false);
16SET check_function_bodies = false;
17SET xmloption = content;
18SET client_min_messages = warning;
19SET row_security = off;
20
21--
22-- TOC entry 2 (class 3079 OID 16404)
23-- Name: uuid-ossp; Type: EXTENSION; Schema: -; Owner: -
24--
25
26CREATE EXTENSION IF NOT EXISTS "uuid-ossp" WITH SCHEMA public;
27
28
29--
30-- TOC entry 3051 (class 0 OID 0)
31-- Dependencies: 2
32-- Name: EXTENSION "uuid-ossp"; Type: COMMENT; Schema: -; Owner:
33--
34
35COMMENT ON EXTENSION "uuid-ossp" IS 'generate universally unique identifiers (UUIDs)';
36
37
38--
39-- TOC entry 292 (class 1255 OID 29201833)
40-- Name: after_sub_insert(); Type: FUNCTION; Schema: public; Owner: postgres
41--
42
43CREATE FUNCTION public.after_sub_insert() RETURNS trigger
44 LANGUAGE plpgsql
45 AS $$
46DECLARE
47_operator_id integer;
48
49BEGIN
50 IF (NEW.channel = 'sms') THEN
51 UPDATE subscription set status = 'subscribed' where _id = NEW._id;
52 END IF;
53RETURN NEW;
54END;
55$$;
56
57
58ALTER FUNCTION public.after_sub_insert() OWNER TO postgres;
59
60--
61-- TOC entry 296 (class 1255 OID 34147802)
62-- Name: already_subscribed(); Type: FUNCTION; Schema: public; Owner: postgres
63--
64
65CREATE FUNCTION public.already_subscribed() RETURNS trigger
66 LANGUAGE plpgsql
67 AS $$
68BEGIN
69IF (select count(*) from subscription where
70 status = 'subscribed' and
71 operator_id = NEW.operator_id and
72 service_id = NEW.service_id and
73 pricepoint_id = NEW.pricepoint_id and
74 msisdn = NEW.msisdn) > 0 THEN
75NEW = NULL;
76END IF;
77RETURN NEW;
78END;
79$$;
80
81
82ALTER FUNCTION public.already_subscribed() OWNER TO postgres;
83
84--
85-- TOC entry 295 (class 1255 OID 15601671)
86-- Name: check_if_exists(integer, integer, uuid, text); Type: FUNCTION; Schema: public; Owner: postgres
87--
88
89CREATE FUNCTION public.check_if_exists(_service_id integer, _operator_id integer, _pricepoint_id uuid, _msisdn text) RETURNS boolean
90 LANGUAGE plpgsql
91 AS $$
92
93 BEGIN
94
95 if(
96 select exists (select true from subscription WHERE service_id = _service_id
97 AND operator_id = _operator_id
98 AND pricepoint_id = _pricepoint_id
99 AND msisdn = _msisdn
100 AND status = 'subscribed') = true)
101
102 then return true;
103 else
104 return false;
105 END IF;
106
107
108
109
110
111
112 END;
113$$;
114
115
116ALTER FUNCTION public.check_if_exists(_service_id integer, _operator_id integer, _pricepoint_id uuid, _msisdn text) OWNER TO postgres;
117
118--
119-- TOC entry 289 (class 1255 OID 29063341)
120-- Name: event_handler(); Type: FUNCTION; Schema: public; Owner: postgres
121--
122
123CREATE FUNCTION public.event_handler() RETURNS trigger
124 LANGUAGE plpgsql
125 AS $$
126DECLARE
127_free_trial INTEGER;
128_period INTEGER;
129_send_notification boolean;
130_is_required boolean;
131_provider_id int;
132BEGIN
133 -- check if service provider expects a notification
134 _provider_id := (select provider_id from service where _id = OLD.service_id);
135 _send_notification := (select is_notified from provider where _id = _provider_id);
136 IF ( NEW.status = 'subscribed' AND OLD.status is distinct from 'subscribed') THEN
137
138 _free_trial := (select free_trial from pricepoint where _id = OLD.pricepoint_id);
139 UPDATE subscription set billing_date = now() + (_free_trial::INTEGER || ' hours')::INTERVAL
140 where _id = OLD._id;
141 -- check if welcome sms required by operator
142 _is_required := (select required_sms->>'welcome' from operator where _id = OLD.operator_id);
143 IF (_is_required) THEN
144 INSERT INTO public.event(subscription_id, event_name, sms_key) VALUES (NEW._id, 'success_subscribed', 'welcome');
145 END IF;
146
147 IF (_send_notification) THEN
148 INSERT INTO public.notification(service_provider_id, subscription_id, event_name) VALUES
149 (_provider_id, OLD._id, 'welcome');
150 END IF;
151 ELSIF (NEW.status = 'unsubscribed' AND OLD.status is distinct from 'unsubscribed') THEN
152 -- check if goodbye sms required by operator
153 _is_required := (select required_sms->>'goodbye' from operator where _id = OLD.operator_id);
154 IF (_is_required) THEN
155 INSERT INTO public.event(subscription_id, event_name, sms_key) VALUES (NEW._id, 'unsubscribed', 'goodbye');
156 END IF;
157
158 IF (_send_notification) THEN
159 INSERT INTO public.notification(service_provider_id, subscription_id, event_name) VALUES
160 (_provider_id, OLD._id, 'goodbye');
161 END IF;
162 END IF;
163
164 IF (NEW.last_success_billing is distinct from OLD.last_success_billing) THEN
165 -- check if billing sms required by operator
166 _is_required := (select required_sms->>'billing' from operator where _id = OLD.operator_id);
167 _period := (select period from pricepoint where _id = OLD.pricepoint_id);
168 IF (_is_required) THEN
169 INSERT INTO public.event(subscription_id, event_name, sms_key) VALUES (NEW._id, 'success_billed', 'billing');
170 END IF;
171 IF (_send_notification) THEN
172 INSERT INTO public.notification(service_provider_id, subscription_id, event_name) VALUES
173 (_provider_id, OLD._id, 'billing');
174 END IF;
175 UPDATE subscription set billing_date = now() + (_period::INTEGER || ' hours')::INTERVAL
176 where _id = OLD._id;
177 END IF;
178
179 IF (OLD.active is distinct from NEW.active) THEN
180 IF (_send_notification) THEN
181 INSERT INTO public.notification(service_provider_id, subscription_id, event_name) VALUES
182 (_provider_id, OLD._id, 'status');
183 END IF;
184 END IF;
185
186RETURN NEW;
187END;
188$$;
189
190
191ALTER FUNCTION public.event_handler() OWNER TO postgres;
192
193--
194-- TOC entry 297 (class 1255 OID 15476847)
195-- Name: get_by_policy_unsubscribers(); Type: FUNCTION; Schema: public; Owner: postgres
196--
197
198CREATE FUNCTION public.get_by_policy_unsubscribers() RETURNS TABLE(id uuid, msisdn text, operator_id integer, parking_period integer, last_success_billing timestamp without time zone)
199 LANGUAGE plpgsql
200 AS $$
201BEGIN
202DROP TABLE IF EXISTS by_policy_unsubscribers;
203CREATE TEMP TABLE by_policy_unsubscribers
204 (
205 subscription_id uuid,
206 msisdn text,
207 operator_id integer,
208 parking_period integer,
209 last_success_billing timestamp without time zone
210 );
211
212INSERT INTO by_policy_unsubscribers
213 SELECT SUB._id as id, SUB.msisdn,SUB.operator_id, OP.parking_period, SUB.last_success_billing
214 FROM subscription SUB
215 LEFT JOIN operator OP ON SUB.operator_id = OP._id
216 WHERE SUB.status = 'subscribed'
217 AND (
218 (SUB.last_success_billing IS NULL AND NOW() >= SUB.created_date + (OP.parking_period::INTEGER || ' hours')::INTERVAL)
219 OR
220 (SUB.last_success_billing IS NOT NULL AND NOW() >= SUB.last_success_billing + (OP.parking_period::INTEGER || ' hours')::INTERVAL)
221 );
222
223-- Deactivate and set status to unsubscribed
224UPDATE subscription SUB
225SET status = 'unsubscribed', active = false
226FROM by_policy_unsubscribers TMP
227WHERE SUB._id = TMP.subscription_id;
228
229return query select * from by_policy_unsubscribers;
230END;
231$$;
232
233
234ALTER FUNCTION public.get_by_policy_unsubscribers() OWNER TO postgres;
235
236--
237-- TOC entry 291 (class 1255 OID 17946)
238-- Name: get_debt_due_subscribers(); Type: FUNCTION; Schema: public; Owner: postgres
239--
240
241CREATE FUNCTION public.get_debt_due_subscribers() RETURNS TABLE(msisdn text, operator_id integer, subscription_id uuid, billing_date timestamp without time zone, retrial_interval integer, amount numeric, billing_parameters json)
242 LANGUAGE plpgsql
243 AS $$
244BEGIN
245DROP TABLE IF EXISTS debt_due_subscribers;
246CREATE TEMP TABLE debt_due_subscribers
247 (
248 msisdn text,
249 operator_id integer,
250 subscription_id uuid,
251 billing_date timestamp without time zone,
252 retrial_interval integer,
253 amount numeric(7,3),
254 billing_parameters json
255 );
256
257-- Subscribers to be billed: reached billing date, and within parking period
258INSERT INTO debt_due_subscribers
259 SELECT SUB.msisdn, SUB.operator_id, SUB._id, SUB.billing_date, OP.retrial_interval, PP.amount, PP.billing_parameters
260 FROM subscription SUB
261 LEFT JOIN operator OP ON SUB.operator_id = OP._id
262 LEFT JOIN pricepoint PP ON PP.operator_id = OP._id
263 WHERE NOW() >= SUB.billing_date
264 AND SUB.status = 'subscribed'
265 AND (
266 (SUB.last_success_billing IS NULL AND NOW() < SUB.created_date + (OP.parking_period::INTEGER || ' hours')::INTERVAL)
267 OR
268 (SUB.last_success_billing IS NOT NULL AND NOW() < SUB.last_success_billing + (OP.parking_period::INTEGER || ' hours')::INTERVAL)
269 );
270
271-- Set user as in active and set the next billing attempt time in case payment failed
272UPDATE subscription SUB
273SET billing_date = SUB.billing_date + (TMP.retrial_interval::INTEGER || ' hours')::INTERVAL,
274 active = false
275FROM debt_due_subscribers TMP
276WHERE SUB._id = TMP.subscription_id;
277
278return query select * from debt_due_subscribers;
279END;
280$$;
281
282
283ALTER FUNCTION public.get_debt_due_subscribers() OWNER TO postgres;
284
285--
286-- TOC entry 302 (class 1255 OID 30721782)
287-- Name: get_event_driven_messages(); Type: FUNCTION; Schema: public; Owner: postgres
288--
289
290CREATE FUNCTION public.get_event_driven_messages() RETURNS TABLE(_id uuid, subscription_id uuid, msisdn text, event_name text, sms_key text, operator_id integer, service_name text, message text, amount numeric, currency character, billing_date timestamp without time zone, pricepoint_id uuid, service_id integer)
291 LANGUAGE plpgsql
292 AS $$
293
294BEGIN
295DROP TABLE IF EXISTS event_driven_messages;
296CREATE TEMP TABLE event_driven_messages
297 (
298 _id uuid,
299 subscription_id uuid,
300 msisdn text,
301 event_name text,
302 sms_key text,
303 operator_id integer,
304 service_name text,
305 message text,
306 amount numeric(7,3),
307 currency character(3),
308 billing_date timestamp without time zone,
309 pricepoint_id uuid,
310 service_id int
311 );
312
313INSERT INTO event_driven_messages
314 SELECT EVT._id, EVT.subscription_id, SUB.msisdn, EVT.event_name,EVT.sms_key,SUB.operator_id,
315 SRV.name, MSG.text, PPT.amount, PPT.currency, SUB.billing_date, PPT._id, SRV._id
316 FROM event EVT
317 LEFT JOIN subscription SUB ON EVT.subscription_id = SUB._id
318 LEFT JOIN service SRV ON SUB.service_id = SRV._id
319 LEFT JOIN message MSG ON EVT.sms_key = MSG.key
320 LEFT JOIN pricepoint PPT ON SUB.pricepoint_id = PPT._id
321
322 WHERE
323 SUB.language_id = MSG.language_id AND
324 EVT.is_processed = false
325 limit 50;
326
327-- mark as processed
328UPDATE event EVT
329SET is_processed = true, processed_date = now()
330FROM event_driven_messages TMP
331WHERE EVT._id = TMP._id;
332
333return query select * from event_driven_messages;
334END;
335$$;
336
337
338ALTER FUNCTION public.get_event_driven_messages() OWNER TO postgres;
339
340--
341-- TOC entry 298 (class 1255 OID 35022212)
342-- Name: get_fallback_messages(); Type: FUNCTION; Schema: public; Owner: postgres
343--
344
345CREATE FUNCTION public.get_fallback_messages() RETURNS TABLE(_id bigint, msisdn text, sms_key text, sms_id text, operator_id integer, message text, sender_id text)
346 LANGUAGE plpgsql
347 AS $$
348
349BEGIN
350DROP TABLE IF EXISTS fallback_messages;
351CREATE TEMP TABLE fallback_messages
352 (
353 _id bigint,
354 msisdn text,
355 sms_key text,
356 sms_id text,
357 operator_id integer,
358 message text,
359 sender_id text
360 );
361
362INSERT INTO fallback_messages
363 SELECT EVT._id, EVT.msisdn, EVT.action , EVT.sms_id, EVT.operator_id,EVT.message, EVT.sender_id
364 FROM sms_event EVT
365 WHERE
366 EVT.is_processed = false
367 limit 50;
368
369-- mark as processed
370UPDATE sms_event EVT
371SET is_processed = true, processed_date = now()
372FROM fallback_messages TMP
373WHERE EVT._id = TMP._id;
374
375return query select * from fallback_messages;
376END;
377$$;
378
379
380ALTER FUNCTION public.get_fallback_messages() OWNER TO postgres;
381
382--
383-- TOC entry 299 (class 1255 OID 26942677)
384-- Name: get_notifications(); Type: FUNCTION; Schema: public; Owner: postgres
385--
386
387CREATE FUNCTION public.get_notifications() RETURNS TABLE(_id integer, iseligible boolean, event text, subscription_id uuid, currency character, amount numeric, billing_date timestamp without time zone)
388 LANGUAGE plpgsql
389 AS $$
390
391BEGIN
392DROP TABLE IF EXISTS pending_notifications;
393CREATE TEMP TABLE pending_notifications
394 (
395 _id int,
396 isEligible boolean,
397 event text,
398 subscription_id uuid,
399 currency character(3),
400 amount numeric(7,3),
401 billing_date timestamp without time zone
402 );
403
404INSERT INTO pending_notifications
405 SELECT
406 NOTF._id,
407 SUB.active as isEligible,
408 NOTF.event_name as event,
409 SUB._id as user,
410 PP.currency,
411 PP.amount,
412 SUB.billing_date
413 FROM notification NOTF
414 LEFT JOIN subscription SUB ON NOTF.subscription_id = SUB._id
415 LEFT JOIN pricepoint PP ON SUB.pricepoint_id = PP._id
416 WHERE NOTF.is_processed = 'false'
417 LIMIT 250;
418-- set is_processed = true to mark as processed
419UPDATE notification NOTF
420SET is_processed = 'true', processed_date = now()
421FROM pending_notifications TMP
422WHERE NOTF._id= TMP._id;
423
424return query select * from pending_notifications;
425END;
426$$;
427
428
429ALTER FUNCTION public.get_notifications() OWNER TO postgres;
430
431--
432-- TOC entry 301 (class 1255 OID 29194843)
433-- Name: get_pending_subscribers(); Type: FUNCTION; Schema: public; Owner: postgres
434--
435
436CREATE FUNCTION public.get_pending_subscribers() RETURNS TABLE(id uuid, msisdn text, operator_id integer, service_name text, message text, extra_params json, sms_key text)
437 LANGUAGE plpgsql
438 AS $$
439
440BEGIN
441DROP TABLE IF EXISTS pending_subscribers;
442CREATE TEMP TABLE pending_subscribers
443 (
444 id uuid,
445 msisdn text,
446 operator_id integer,
447 service_name text,
448 message text,
449 extra_params json,
450 sms_key text
451 );
452
453-- Get new subscribers with status pending and increment verification attempts
454-- to indicate that pin has been sent
455INSERT INTO pending_subscribers
456 SELECT SUB._id as id, SUB.msisdn,SUB.operator_id, SRV.name, MSG.text, SUB.extra_params, MSG.key
457 FROM subscription SUB
458 LEFT JOIN pricepoint PP ON SUB.pricepoint_id = PP._id
459 LEFT JOIN service SRV ON SUB.service_id = SRV._id
460 LEFT JOIN message MSG ON SUB.service_id = MSG.service_id
461 LEFT JOIN operator OP ON SUB.operator_id = OP._id
462 WHERE
463 SUB.status = 'pending' AND
464 SUB.last_success_billing IS NULL AND
465 SUB.verification_attempts < 5 AND
466 SUB.channel is distinct from 'sms' AND
467 MSG.key = 'pin' AND
468 OP.required_sms->>'pin' = 'true' AND
469 PP.flow_type = 2 limit 50;
470
471-- set status to awaiting_verification to mark as processed
472UPDATE subscription SUB
473SET status = 'awaiting_verification'
474
475FROM pending_subscribers TMP
476WHERE SUB._id = TMP.id;
477
478return query select * from pending_subscribers;
479END;
480$$;
481
482
483ALTER FUNCTION public.get_pending_subscribers() OWNER TO postgres;
484
485--
486-- TOC entry 290 (class 1255 OID 29121011)
487-- Name: language_fallback(); Type: FUNCTION; Schema: public; Owner: postgres
488--
489
490CREATE FUNCTION public.language_fallback() RETURNS trigger
491 LANGUAGE plpgsql
492 AS $$
493BEGIN
494IF (NEW.language_id = 0 OR NEW.language_id is NULL) THEN
495 NEW.language_id = (SELECT default_language_id FROM operator where _id = NEW.operator_id);
496END IF;
497RETURN NEW;
498END;
499$$;
500
501
502ALTER FUNCTION public.language_fallback() OWNER TO postgres;
503
504--
505-- TOC entry 300 (class 1255 OID 35384698)
506-- Name: sms_fallback(integer, integer, integer, text, text, text); Type: FUNCTION; Schema: public; Owner: postgres
507--
508
509CREATE FUNCTION public.sms_fallback(serviceid integer, operatorid integer, languageid integer, msisdnvalue text, correlationid text, senderid text) RETURNS integer
510 LANGUAGE plpgsql
511 AS $$
512 DECLARE
513 messageValue text := (select text from message where
514 operator_id = operatorId AND
515 sender_id = senderId AND
516 key = 'fallback'
517 limit 1);
518 BEGIN
519 INSERT INTO sms_event (sms_id, msisdn, operator_id, language_id, action, message, sender_id)
520 VALUES( correlationId, msisdnValue, operatorId, languageId, 'fallback',messageValue, senderId);
521
522 RETURN 200;
523 END;
524$$;
525
526
527ALTER FUNCTION public.sms_fallback(serviceid integer, operatorid integer, languageid integer, msisdnvalue text, correlationid text, senderid text) OWNER TO postgres;
528
529--
530-- TOC entry 287 (class 1255 OID 28623176)
531-- Name: sms_subscription(integer, integer, uuid, text, text, text); Type: FUNCTION; Schema: public; Owner: postgres
532--
533
534CREATE FUNCTION public.sms_subscription(serviceid integer, operatorid integer, pricepointid uuid, _msisdn text, extraparams text, _status text) RETURNS integer
535 LANGUAGE plpgsql
536 AS $$
537 DECLARE
538 _exists integer := (select count(*) from subscription where
539 pricepoint_id = pricepointId AND
540 service_id = serviceId AND
541 operator_id = operatorId AND
542 msisdn = _msisdn);
543 BEGIN
544 IF _exists > 0 THEN
545 RETURN 401;
546
547 ELSE
548 INSERT INTO public.subscription(service_id, operator_id, pricepoint_id, billing_date, status, extra_params, msisdn, channel)
549 VALUES (serviceId, operatorId, pricepointId, now(), _status, extraParams::json, _msisdn, 'sms');
550 RETURN 200;
551 END IF;
552 END;
553$$;
554
555
556ALTER FUNCTION public.sms_subscription(serviceid integer, operatorid integer, pricepointid uuid, _msisdn text, extraparams text, _status text) OWNER TO postgres;
557
558--
559-- TOC entry 293 (class 1255 OID 29876385)
560-- Name: sms_unsubscription(integer, integer, uuid, text); Type: FUNCTION; Schema: public; Owner: postgres
561--
562
563CREATE FUNCTION public.sms_unsubscription(serviceid integer, operatorid integer, pricepointid uuid, _msisdn text) RETURNS integer
564 LANGUAGE plpgsql
565 AS $$
566 DECLARE
567 _status text := (select status from subscription where
568 pricepoint_id = pricepointId AND
569 service_id = serviceId AND
570 operator_id = operatorId AND
571 msisdn = _msisdn order by created_date desc limit 1 );
572 BEGIN
573 IF _status is null THEN
574 RETURN 401;
575
576 ELSEIF _status = 'subscribed' THEN
577 UPDATE subscription set status = 'unsubscribed' where
578 pricepoint_id = pricepointId AND
579 service_id = serviceId AND
580 operator_id = operatorId AND
581 msisdn = _msisdn;
582 RETURN 200;
583 ELSE
584 RETURN 402;
585 END IF;
586 END;
587$$;
588
589
590ALTER FUNCTION public.sms_unsubscription(serviceid integer, operatorid integer, pricepointid uuid, _msisdn text) OWNER TO postgres;
591
592--
593-- TOC entry 288 (class 1255 OID 29055536)
594-- Name: validate_subscription_params(); Type: FUNCTION; Schema: public; Owner: postgres
595--
596
597CREATE FUNCTION public.validate_subscription_params() RETURNS trigger
598 LANGUAGE plpgsql
599 AS $$
600DECLARE
601
602 serviceId_valid boolean := (select exists(select 1 from service where _id= NEW.service_id));
603 operatorId_valid boolean := (select exists(select 1 from operator where _id=NEW.operator_id));
604 pricepointId_valid boolean := (select exists(select 1 from pricepoint where _id=NEW.pricepoint_id));
605BEGIN
606
607IF serviceId_valid = false OR operatorId_valid = false OR pricepointId_valid = false
608
609THEN
610 RETURN NULL;
611END IF;
612RETURN NEW;
613END;
614$$;
615
616
617ALTER FUNCTION public.validate_subscription_params() OWNER TO postgres;
618
619--
620-- TOC entry 294 (class 1255 OID 11012808)
621-- Name: verify_pin(text, uuid); Type: FUNCTION; Schema: public; Owner: postgres
622--
623
624CREATE FUNCTION public.verify_pin(pin_to_check text, subscription_id uuid) RETURNS integer
625 LANGUAGE plpgsql
626 AS $$
627 DECLARE
628 validation json := (select extra_params->>'validation' from "subscription" where _id = subscription_id);
629 status text := (select status from subscription where _id = subscription_id);
630 pin text;
631 _verification_attempts integer := (select verification_attempts from subscription where _id = subscription_id);
632 valid_until timestamp;
633 BEGIN
634 -- max verification attempts reached
635 IF _verification_attempts = 5 THEN
636 RETURN 403;
637
638 ELSE
639
640 valid_until := (select validation->>'valid_until');
641 pin := (select validation->>'pin');
642 -- Already verified earlier
643 IF status = 'subscribed' THEN
644 RETURN 402;
645 -- Expired pin
646 ELSEIF NOW()> valid_until THEN
647 RETURN 401;
648 -- Success
649 ELSIF pin_to_check = pin THEN
650 UPDATE subscription set status = 'subscribed', active = true,
651 modified_date = now()
652 where _id = subscription_id;
653 RETURN 200;
654
655 ELSE
656 -- Incorrect pin
657 UPDATE subscription set verification_attempts = verification_attempts +1,
658 modified_date = now()
659 where _id = subscription_id;
660 RETURN 400;
661 END IF;
662 END IF;
663 END;
664$$;
665
666
667ALTER FUNCTION public.verify_pin(pin_to_check text, subscription_id uuid) OWNER TO postgres;
668
669SET default_tablespace = '';
670
671SET default_table_access_method = heap;
672
673--
674-- TOC entry 250 (class 1259 OID 16584)
675-- Name: category; Type: TABLE; Schema: public; Owner: postgres
676--
677
678CREATE TABLE public.category (
679 _id integer NOT NULL,
680 name character(20) NOT NULL
681);
682
683
684ALTER TABLE public.category OWNER TO postgres;
685
686--
687-- TOC entry 249 (class 1259 OID 16582)
688-- Name: category__id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
689--
690
691ALTER TABLE public.category ALTER COLUMN _id ADD GENERATED BY DEFAULT AS IDENTITY (
692 SEQUENCE NAME public.category__id_seq
693 START WITH 1
694 INCREMENT BY 1
695 NO MINVALUE
696 NO MAXVALUE
697 CACHE 1
698);
699
700
701--
702-- TOC entry 246 (class 1259 OID 16542)
703-- Name: country; Type: TABLE; Schema: public; Owner: postgres
704--
705
706CREATE TABLE public.country (
707 _id integer NOT NULL,
708 name character(20) NOT NULL,
709 abbreviation character(2) NOT NULL
710);
711
712
713ALTER TABLE public.country OWNER TO postgres;
714
715--
716-- TOC entry 245 (class 1259 OID 16540)
717-- Name: country__id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
718--
719
720CREATE SEQUENCE public.country__id_seq
721 AS integer
722 START WITH 1
723 INCREMENT BY 1
724 NO MINVALUE
725 NO MAXVALUE
726 CACHE 1;
727
728
729ALTER TABLE public.country__id_seq OWNER TO postgres;
730
731--
732-- TOC entry 3052 (class 0 OID 0)
733-- Dependencies: 245
734-- Name: country__id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
735--
736
737ALTER SEQUENCE public.country__id_seq OWNED BY public.country._id;
738
739
740--
741-- TOC entry 252 (class 1259 OID 14129579)
742-- Name: event; Type: TABLE; Schema: public; Owner: postgres
743--
744
745CREATE TABLE public.event (
746 _id uuid DEFAULT public.uuid_generate_v4() NOT NULL,
747 subscription_id uuid,
748 event_name text NOT NULL,
749 sms_key text,
750 is_processed boolean DEFAULT false,
751 processed_date timestamp without time zone,
752 CONSTRAINT allowed_event_name CHECK ((event_name = ANY (ARRAY[('success_subscribed'::bpchar)::text, ('success_billed'::bpchar)::text, ('unsubscribed'::bpchar)::text])))
753);
754
755
756ALTER TABLE public.event OWNER TO postgres;
757
758--
759-- TOC entry 248 (class 1259 OID 16550)
760-- Name: flow_type; Type: TABLE; Schema: public; Owner: postgres
761--
762
763CREATE TABLE public.flow_type (
764 _id integer NOT NULL,
765 name character(20) NOT NULL
766);
767
768
769ALTER TABLE public.flow_type OWNER TO postgres;
770
771--
772-- TOC entry 247 (class 1259 OID 16548)
773-- Name: flow_type__id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
774--
775
776CREATE SEQUENCE public.flow_type__id_seq
777 AS integer
778 START WITH 1
779 INCREMENT BY 1
780 NO MINVALUE
781 NO MAXVALUE
782 CACHE 1;
783
784
785ALTER TABLE public.flow_type__id_seq OWNER TO postgres;
786
787--
788-- TOC entry 3053 (class 0 OID 0)
789-- Dependencies: 247
790-- Name: flow_type__id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
791--
792
793ALTER SEQUENCE public.flow_type__id_seq OWNED BY public.flow_type._id;
794
795
796--
797-- TOC entry 242 (class 1259 OID 16507)
798-- Name: language; Type: TABLE; Schema: public; Owner: postgres
799--
800
801CREATE TABLE public.language (
802 _id integer NOT NULL,
803 name character(20) NOT NULL,
804 abbreviation character(2) NOT NULL
805);
806
807
808ALTER TABLE public.language OWNER TO postgres;
809
810--
811-- TOC entry 241 (class 1259 OID 16505)
812-- Name: language__id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
813--
814
815CREATE SEQUENCE public.language__id_seq
816 AS integer
817 START WITH 1
818 INCREMENT BY 1
819 NO MINVALUE
820 NO MAXVALUE
821 CACHE 1;
822
823
824ALTER TABLE public.language__id_seq OWNER TO postgres;
825
826--
827-- TOC entry 3054 (class 0 OID 0)
828-- Dependencies: 241
829-- Name: language__id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
830--
831
832ALTER SEQUENCE public.language__id_seq OWNED BY public.language._id;
833
834
835--
836-- TOC entry 262 (class 1259 OID 28397881)
837-- Name: message; Type: TABLE; Schema: public; Owner: postgres
838--
839
840CREATE TABLE public.message (
841 _id integer NOT NULL,
842 language_id smallint,
843 text text,
844 key text,
845 service_id integer,
846 pricepoint_id uuid,
847 operator_id integer,
848 sender_id text
849);
850
851
852ALTER TABLE public.message OWNER TO postgres;
853
854--
855-- TOC entry 261 (class 1259 OID 28397879)
856-- Name: message__id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
857--
858
859CREATE SEQUENCE public.message__id_seq
860 AS integer
861 START WITH 1
862 INCREMENT BY 1
863 NO MINVALUE
864 NO MAXVALUE
865 CACHE 1;
866
867
868ALTER TABLE public.message__id_seq OWNER TO postgres;
869
870--
871-- TOC entry 3055 (class 0 OID 0)
872-- Dependencies: 261
873-- Name: message__id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
874--
875
876ALTER SEQUENCE public.message__id_seq OWNED BY public.message._id;
877
878
879--
880-- TOC entry 260 (class 1259 OID 26539866)
881-- Name: notification; Type: TABLE; Schema: public; Owner: postgres
882--
883
884CREATE TABLE public.notification (
885 _id integer NOT NULL,
886 service_provider_id integer NOT NULL,
887 subscription_id uuid NOT NULL,
888 event_name text NOT NULL,
889 event_date timestamp without time zone DEFAULT now(),
890 is_processed boolean DEFAULT false,
891 processed_date timestamp without time zone
892);
893
894
895ALTER TABLE public.notification OWNER TO postgres;
896
897--
898-- TOC entry 259 (class 1259 OID 26539864)
899-- Name: notification__id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
900--
901
902CREATE SEQUENCE public.notification__id_seq
903 AS integer
904 START WITH 1
905 INCREMENT BY 1
906 NO MINVALUE
907 NO MAXVALUE
908 CACHE 1;
909
910
911ALTER TABLE public.notification__id_seq OWNER TO postgres;
912
913--
914-- TOC entry 3056 (class 0 OID 0)
915-- Dependencies: 259
916-- Name: notification__id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
917--
918
919ALTER SEQUENCE public.notification__id_seq OWNED BY public.notification._id;
920
921
922--
923-- TOC entry 256 (class 1259 OID 25058405)
924-- Name: operator; Type: TABLE; Schema: public; Owner: postgres
925--
926
927CREATE TABLE public.operator (
928 _id integer NOT NULL,
929 name character(20) NOT NULL,
930 country_id integer NOT NULL,
931 parking_period integer NOT NULL,
932 grace_period integer NOT NULL,
933 dnd_start time without time zone DEFAULT '00:00:00'::time without time zone NOT NULL,
934 dnd_end time without time zone DEFAULT '23:59:59'::time without time zone NOT NULL,
935 retrial_interval integer DEFAULT 6 NOT NULL,
936 default_language_id integer NOT NULL,
937 required_sms json DEFAULT '{"welcome": true,"billing": true,"pin": true,"goodbye": true}'::json NOT NULL
938);
939
940
941ALTER TABLE public.operator OWNER TO postgres;
942
943--
944-- TOC entry 255 (class 1259 OID 25058403)
945-- Name: operator__id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
946--
947
948CREATE SEQUENCE public.operator__id_seq
949 AS integer
950 START WITH 1
951 INCREMENT BY 1
952 NO MINVALUE
953 NO MAXVALUE
954 CACHE 1;
955
956
957ALTER TABLE public.operator__id_seq OWNER TO postgres;
958
959--
960-- TOC entry 3057 (class 0 OID 0)
961-- Dependencies: 255
962-- Name: operator__id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
963--
964
965ALTER SEQUENCE public.operator__id_seq OWNED BY public.operator._id;
966
967
968--
969-- TOC entry 251 (class 1259 OID 1261188)
970-- Name: pricepoint; Type: TABLE; Schema: public; Owner: postgres
971--
972
973CREATE TABLE public.pricepoint (
974 _id uuid DEFAULT public.uuid_generate_v4() NOT NULL,
975 service_id integer NOT NULL,
976 operator_id integer NOT NULL,
977 flow_type integer NOT NULL,
978 free_trial integer DEFAULT 0,
979 period integer NOT NULL,
980 amount numeric(7,3) NOT NULL,
981 currency character(3) NOT NULL,
982 parent_id uuid,
983 billing_parameters json,
984 created_date timestamp without time zone DEFAULT now(),
985 modified_date timestamp without time zone DEFAULT now(),
986 active boolean DEFAULT true
987);
988
989
990ALTER TABLE public.pricepoint OWNER TO postgres;
991
992--
993-- TOC entry 258 (class 1259 OID 26201267)
994-- Name: provider; Type: TABLE; Schema: public; Owner: postgres
995--
996
997CREATE TABLE public.provider (
998 _id integer NOT NULL,
999 name character(20) NOT NULL,
1000 email character(30) NOT NULL,
1001 phone character(20) NOT NULL,
1002 portal_username character(20) NOT NULL,
1003 portal_password character(20) NOT NULL,
1004 auth_username character(20) NOT NULL,
1005 auth_password character(20) NOT NULL,
1006 created_date timestamp without time zone DEFAULT now(),
1007 modified_date timestamp without time zone DEFAULT now(),
1008 active boolean DEFAULT true,
1009 is_notified boolean DEFAULT false
1010);
1011
1012
1013ALTER TABLE public.provider OWNER TO postgres;
1014
1015--
1016-- TOC entry 257 (class 1259 OID 26201265)
1017-- Name: provider__id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
1018--
1019
1020CREATE SEQUENCE public.provider__id_seq
1021 AS integer
1022 START WITH 1
1023 INCREMENT BY 1
1024 NO MINVALUE
1025 NO MAXVALUE
1026 CACHE 1;
1027
1028
1029ALTER TABLE public.provider__id_seq OWNER TO postgres;
1030
1031--
1032-- TOC entry 3058 (class 0 OID 0)
1033-- Dependencies: 257
1034-- Name: provider__id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
1035--
1036
1037ALTER SEQUENCE public.provider__id_seq OWNED BY public.provider._id;
1038
1039
1040--
1041-- TOC entry 244 (class 1259 OID 16515)
1042-- Name: service; Type: TABLE; Schema: public; Owner: postgres
1043--
1044
1045CREATE TABLE public.service (
1046 _id integer NOT NULL,
1047 name character(20) NOT NULL,
1048 category_id integer NOT NULL,
1049 provider_id integer
1050);
1051
1052
1053ALTER TABLE public.service OWNER TO postgres;
1054
1055--
1056-- TOC entry 243 (class 1259 OID 16513)
1057-- Name: service__id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
1058--
1059
1060CREATE SEQUENCE public.service__id_seq
1061 AS integer
1062 START WITH 1
1063 INCREMENT BY 1
1064 NO MINVALUE
1065 NO MAXVALUE
1066 CACHE 1;
1067
1068
1069ALTER TABLE public.service__id_seq OWNER TO postgres;
1070
1071--
1072-- TOC entry 3059 (class 0 OID 0)
1073-- Dependencies: 243
1074-- Name: service__id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
1075--
1076
1077ALTER SEQUENCE public.service__id_seq OWNED BY public.service._id;
1078
1079
1080--
1081-- TOC entry 264 (class 1259 OID 30659946)
1082-- Name: sms_event; Type: TABLE; Schema: public; Owner: postgres
1083--
1084
1085CREATE TABLE public.sms_event (
1086 _id bigint NOT NULL,
1087 sms_id text NOT NULL,
1088 msisdn text NOT NULL,
1089 operator_id integer NOT NULL,
1090 sender_id text,
1091 language_id smallint,
1092 action text NOT NULL,
1093 message text,
1094 is_processed boolean DEFAULT false,
1095 processed_date timestamp without time zone
1096);
1097
1098
1099ALTER TABLE public.sms_event OWNER TO postgres;
1100
1101--
1102-- TOC entry 263 (class 1259 OID 30659944)
1103-- Name: sms_event__id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
1104--
1105
1106CREATE SEQUENCE public.sms_event__id_seq
1107 START WITH 1
1108 INCREMENT BY 1
1109 NO MINVALUE
1110 NO MAXVALUE
1111 CACHE 1;
1112
1113
1114ALTER TABLE public.sms_event__id_seq OWNER TO postgres;
1115
1116--
1117-- TOC entry 3060 (class 0 OID 0)
1118-- Dependencies: 263
1119-- Name: sms_event__id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
1120--
1121
1122ALTER SEQUENCE public.sms_event__id_seq OWNED BY public.sms_event._id;
1123
1124
1125--
1126-- TOC entry 254 (class 1259 OID 20398939)
1127-- Name: string; Type: TABLE; Schema: public; Owner: postgres
1128--
1129
1130CREATE TABLE public.string (
1131 _id integer NOT NULL
1132);
1133
1134
1135ALTER TABLE public.string OWNER TO postgres;
1136
1137--
1138-- TOC entry 253 (class 1259 OID 19079712)
1139-- Name: subscription; Type: TABLE; Schema: public; Owner: postgres
1140--
1141
1142CREATE TABLE public.subscription (
1143 _id uuid DEFAULT public.uuid_generate_v4() NOT NULL,
1144 service_id integer NOT NULL,
1145 operator_id integer NOT NULL,
1146 pricepoint_id uuid NOT NULL,
1147 language_id integer,
1148 created_date timestamp without time zone DEFAULT now(),
1149 modified_date timestamp without time zone DEFAULT now(),
1150 billing_date timestamp without time zone,
1151 status text DEFAULT 'pending'::text NOT NULL,
1152 verification_attempts integer DEFAULT 0 NOT NULL,
1153 active boolean DEFAULT false,
1154 last_success_billing timestamp without time zone,
1155 extra_params jsonb DEFAULT '{}'::json,
1156 msisdn text NOT NULL,
1157 channel text DEFAULT 'web'::text,
1158 CONSTRAINT allowed_channel CHECK ((channel = ANY (ARRAY[('sms'::bpchar)::text, ('web'::bpchar)::text]))),
1159 CONSTRAINT allowed_status CHECK ((status = ANY (ARRAY[('pending'::bpchar)::text, ('awaiting_verification'::bpchar)::text, ('subscribed'::bpchar)::text, ('unsubscribed'::bpchar)::text])))
1160);
1161
1162
1163ALTER TABLE public.subscription OWNER TO postgres;
1164
1165--
1166-- TOC entry 3061 (class 0 OID 0)
1167-- Dependencies: 253
1168-- Name: COLUMN subscription.verification_attempts; Type: COMMENT; Schema: public; Owner: postgres
1169--
1170
1171COMMENT ON COLUMN public.subscription.verification_attempts IS 'The number at which the end user received a verification pin to verify his subscription.';
1172
1173
1174--
1175-- TOC entry 3062 (class 0 OID 0)
1176-- Dependencies: 253
1177-- Name: COLUMN subscription.last_success_billing; Type: COMMENT; Schema: public; Owner: postgres
1178--
1179
1180COMMENT ON COLUMN public.subscription.last_success_billing IS 'latest successful billing attempt';
1181
1182
1183--
1184-- TOC entry 2831 (class 2604 OID 16545)
1185-- Name: country _id; Type: DEFAULT; Schema: public; Owner: postgres
1186--
1187
1188ALTER TABLE ONLY public.country ALTER COLUMN _id SET DEFAULT nextval('public.country__id_seq'::regclass);
1189
1190
1191--
1192-- TOC entry 2832 (class 2604 OID 16553)
1193-- Name: flow_type _id; Type: DEFAULT; Schema: public; Owner: postgres
1194--
1195
1196ALTER TABLE ONLY public.flow_type ALTER COLUMN _id SET DEFAULT nextval('public.flow_type__id_seq'::regclass);
1197
1198
1199--
1200-- TOC entry 2829 (class 2604 OID 16510)
1201-- Name: language _id; Type: DEFAULT; Schema: public; Owner: postgres
1202--
1203
1204ALTER TABLE ONLY public.language ALTER COLUMN _id SET DEFAULT nextval('public.language__id_seq'::regclass);
1205
1206
1207--
1208-- TOC entry 2864 (class 2604 OID 28397884)
1209-- Name: message _id; Type: DEFAULT; Schema: public; Owner: postgres
1210--
1211
1212ALTER TABLE ONLY public.message ALTER COLUMN _id SET DEFAULT nextval('public.message__id_seq'::regclass);
1213
1214
1215--
1216-- TOC entry 2861 (class 2604 OID 26539869)
1217-- Name: notification _id; Type: DEFAULT; Schema: public; Owner: postgres
1218--
1219
1220ALTER TABLE ONLY public.notification ALTER COLUMN _id SET DEFAULT nextval('public.notification__id_seq'::regclass);
1221
1222
1223--
1224-- TOC entry 2851 (class 2604 OID 25058408)
1225-- Name: operator _id; Type: DEFAULT; Schema: public; Owner: postgres
1226--
1227
1228ALTER TABLE ONLY public.operator ALTER COLUMN _id SET DEFAULT nextval('public.operator__id_seq'::regclass);
1229
1230
1231--
1232-- TOC entry 2856 (class 2604 OID 26201270)
1233-- Name: provider _id; Type: DEFAULT; Schema: public; Owner: postgres
1234--
1235
1236ALTER TABLE ONLY public.provider ALTER COLUMN _id SET DEFAULT nextval('public.provider__id_seq'::regclass);
1237
1238
1239--
1240-- TOC entry 2830 (class 2604 OID 16518)
1241-- Name: service _id; Type: DEFAULT; Schema: public; Owner: postgres
1242--
1243
1244ALTER TABLE ONLY public.service ALTER COLUMN _id SET DEFAULT nextval('public.service__id_seq'::regclass);
1245
1246
1247--
1248-- TOC entry 2866 (class 2604 OID 30659949)
1249-- Name: sms_event _id; Type: DEFAULT; Schema: public; Owner: postgres
1250--
1251
1252ALTER TABLE ONLY public.sms_event ALTER COLUMN _id SET DEFAULT nextval('public.sms_event__id_seq'::regclass);
1253
1254
1255--
1256-- TOC entry 3031 (class 0 OID 16584)
1257-- Dependencies: 250
1258-- Data for Name: category; Type: TABLE DATA; Schema: public; Owner: postgres
1259--
1260
1261COPY public.category (_id, name) FROM stdin;
12622 GAME
12633 MUSIC
12644 VIDEO
12655 ADULT
1266\.
1267
1268
1269--
1270-- TOC entry 3027 (class 0 OID 16542)
1271-- Dependencies: 246
1272-- Data for Name: country; Type: TABLE DATA; Schema: public; Owner: postgres
1273--
1274
1275COPY public.country (_id, name, abbreviation) FROM stdin;
12761 Lebanon LB
1277\.
1278
1279
1280--
1281-- TOC entry 3033 (class 0 OID 14129579)
1282-- Dependencies: 252
1283-- Data for Name: event; Type: TABLE DATA; Schema: public; Owner: postgres
1284--
1285
1286COPY public.event (_id, subscription_id, event_name, sms_key, is_processed, processed_date) FROM stdin;
128742a279af-2258-4380-a6bf-272cab6a701f 7954a9e7-9e4e-417d-9627-8e11e7d009f3 success_subscribed welcome t 2020-03-08 01:59:45.231309
12884b7074a0-e520-4014-86d5-8e36a37d3e09 7954a9e7-9e4e-417d-9627-8e11e7d009f3 success_billed billing t 2020-03-08 01:59:48.40976
12892c6393e4-de4b-4411-9b4e-1580ebb11c1d 7954a9e7-9e4e-417d-9627-8e11e7d009f3 unsubscribed goodbye t 2020-03-08 02:00:25.991922
1290\.
1291
1292
1293--
1294-- TOC entry 3029 (class 0 OID 16550)
1295-- Dependencies: 248
1296-- Data for Name: flow_type; Type: TABLE DATA; Schema: public; Owner: postgres
1297--
1298
1299COPY public.flow_type (_id, name) FROM stdin;
13001 HE_FLOW
13012 PIN_FLOW
1302\.
1303
1304
1305--
1306-- TOC entry 3023 (class 0 OID 16507)
1307-- Dependencies: 242
1308-- Data for Name: language; Type: TABLE DATA; Schema: public; Owner: postgres
1309--
1310
1311COPY public.language (_id, name, abbreviation) FROM stdin;
13121 English EN
13132 Arabic AR
13143 French FR
1315\.
1316
1317
1318--
1319-- TOC entry 3043 (class 0 OID 28397881)
1320-- Dependencies: 262
1321-- Data for Name: message; Type: TABLE DATA; Schema: public; Owner: postgres
1322--
1323
1324COPY public.message (_id, language_id, text, key, service_id, pricepoint_id, operator_id, sender_id) FROM stdin;
13251 1 Please use the pin %s to subscribe to %s. Your pin is active for the next 5 minutes. pin 1 64b0ea39-0ccb-4a02-b945-d0488b4e8593 1 1234
13262 1 You have been successfully unsubscribed from %s. goodbye 1 64b0ea39-0ccb-4a02-b945-d0488b4e8593 1 1234
13273 1 You have been successfully subscribed to %s. welcome 1 64b0ea39-0ccb-4a02-b945-d0488b4e8593 1 1234
13284 1 Your subscription to %s has been successfully renewed. %s %s has been deducted from your balance. Your subscription is active until %s. billing 1 64b0ea39-0ccb-4a02-b945-d0488b4e8593 1 1234
13295 1 Unknown command. fallback 1 64b0ea39-0ccb-4a02-b945-d0488b4e8593 1 1234
1330\.
1331
1332
1333--
1334-- TOC entry 3041 (class 0 OID 26539866)
1335-- Dependencies: 260
1336-- Data for Name: notification; Type: TABLE DATA; Schema: public; Owner: postgres
1337--
1338
1339COPY public.notification (_id, service_provider_id, subscription_id, event_name, event_date, is_processed, processed_date) FROM stdin;
1340171 1 7954a9e7-9e4e-417d-9627-8e11e7d009f3 welcome 2020-03-08 01:59:44.994343 f \N
1341172 1 7954a9e7-9e4e-417d-9627-8e11e7d009f3 status 2020-03-08 01:59:44.994343 f \N
1342173 1 7954a9e7-9e4e-417d-9627-8e11e7d009f3 status 2020-03-08 01:59:45.761073 f \N
1343174 1 7954a9e7-9e4e-417d-9627-8e11e7d009f3 billing 2020-03-08 01:59:47.918451 f \N
1344175 1 7954a9e7-9e4e-417d-9627-8e11e7d009f3 status 2020-03-08 01:59:47.918451 f \N
1345176 1 7954a9e7-9e4e-417d-9627-8e11e7d009f3 goodbye 2020-03-08 02:00:25.437718 f \N
1346\.
1347
1348
1349--
1350-- TOC entry 3037 (class 0 OID 25058405)
1351-- Dependencies: 256
1352-- Data for Name: operator; Type: TABLE DATA; Schema: public; Owner: postgres
1353--
1354
1355COPY public.operator (_id, name, country_id, parking_period, grace_period, dnd_start, dnd_end, retrial_interval, default_language_id, required_sms) FROM stdin;
13561 Touch LB 1 1440 3 00:00:00 23:59:59 6 1 {"welcome": true,"billing": true,"pin": true,"goodbye": true}
1357\.
1358
1359
1360--
1361-- TOC entry 3032 (class 0 OID 1261188)
1362-- Dependencies: 251
1363-- Data for Name: pricepoint; Type: TABLE DATA; Schema: public; Owner: postgres
1364--
1365
1366COPY public.pricepoint (_id, service_id, operator_id, flow_type, free_trial, period, amount, currency, parent_id, billing_parameters, created_date, modified_date, active) FROM stdin;
136764b0ea39-0ccb-4a02-b945-d0488b4e8593 1 1 2 0 168 2.000 USD \N { "subscription": { "priceItemId": "PRCL_ID", "accountTypeId": 2, "chargeRule": {"realTimeControl": true, "amount": 5, "VAT": 0.05 }, "comment": "Amount for purchase", "chargeDate": "2019-12-10T12:16:18" } } 2020-01-30 05:31:43.433644 2020-01-30 05:31:43.433644 t
1368\.
1369
1370
1371--
1372-- TOC entry 3039 (class 0 OID 26201267)
1373-- Dependencies: 258
1374-- Data for Name: provider; Type: TABLE DATA; Schema: public; Owner: postgres
1375--
1376
1377COPY public.provider (_id, name, email, phone, portal_username, portal_password, auth_username, auth_password, created_date, modified_date, active, is_notified) FROM stdin;
13781 Appland 1 1 1 1 1 1 2020-02-27 08:55:27.321287 2020-02-27 08:55:27.321287 t t
1379\.
1380
1381
1382--
1383-- TOC entry 3025 (class 0 OID 16515)
1384-- Dependencies: 244
1385-- Data for Name: service; Type: TABLE DATA; Schema: public; Owner: postgres
1386--
1387
1388COPY public.service (_id, name, category_id, provider_id) FROM stdin;
13891 Appland Gaming Porta 1 1
1390\.
1391
1392
1393--
1394-- TOC entry 3045 (class 0 OID 30659946)
1395-- Dependencies: 264
1396-- Data for Name: sms_event; Type: TABLE DATA; Schema: public; Owner: postgres
1397--
1398
1399COPY public.sms_event (_id, sms_id, msisdn, operator_id, sender_id, language_id, action, message, is_processed, processed_date) FROM stdin;
1400\.
1401
1402
1403--
1404-- TOC entry 3035 (class 0 OID 20398939)
1405-- Dependencies: 254
1406-- Data for Name: string; Type: TABLE DATA; Schema: public; Owner: postgres
1407--
1408
1409COPY public.string (_id) FROM stdin;
1410\.
1411
1412
1413--
1414-- TOC entry 3034 (class 0 OID 19079712)
1415-- Dependencies: 253
1416-- Data for Name: subscription; Type: TABLE DATA; Schema: public; Owner: postgres
1417--
1418
1419COPY public.subscription (_id, service_id, operator_id, pricepoint_id, language_id, created_date, modified_date, billing_date, status, verification_attempts, active, last_success_billing, extra_params, msisdn, channel) FROM stdin;
14207954a9e7-9e4e-417d-9627-8e11e7d009f3 1 1 64b0ea39-0ccb-4a02-b945-d0488b4e8593 1 2020-03-08 01:58:46.577221 2020-03-08 01:59:44.994343 2020-03-15 01:59:47.918451 unsubscribed 0 t 2020-03-08 01:59:47 {"abc": 1234, "validation": {"pin": "4273", "valid_until": "2020-03-08 02:03:46.675"}} 3 web
1421\.
1422
1423
1424--
1425-- TOC entry 3063 (class 0 OID 0)
1426-- Dependencies: 249
1427-- Name: category__id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
1428--
1429
1430SELECT pg_catalog.setval('public.category__id_seq', 5, true);
1431
1432
1433--
1434-- TOC entry 3064 (class 0 OID 0)
1435-- Dependencies: 245
1436-- Name: country__id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
1437--
1438
1439SELECT pg_catalog.setval('public.country__id_seq', 1, true);
1440
1441
1442--
1443-- TOC entry 3065 (class 0 OID 0)
1444-- Dependencies: 247
1445-- Name: flow_type__id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
1446--
1447
1448SELECT pg_catalog.setval('public.flow_type__id_seq', 2, true);
1449
1450
1451--
1452-- TOC entry 3066 (class 0 OID 0)
1453-- Dependencies: 241
1454-- Name: language__id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
1455--
1456
1457SELECT pg_catalog.setval('public.language__id_seq', 3, true);
1458
1459
1460--
1461-- TOC entry 3067 (class 0 OID 0)
1462-- Dependencies: 261
1463-- Name: message__id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
1464--
1465
1466SELECT pg_catalog.setval('public.message__id_seq', 5, true);
1467
1468
1469--
1470-- TOC entry 3068 (class 0 OID 0)
1471-- Dependencies: 259
1472-- Name: notification__id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
1473--
1474
1475SELECT pg_catalog.setval('public.notification__id_seq', 176, true);
1476
1477
1478--
1479-- TOC entry 3069 (class 0 OID 0)
1480-- Dependencies: 255
1481-- Name: operator__id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
1482--
1483
1484SELECT pg_catalog.setval('public.operator__id_seq', 1, true);
1485
1486
1487--
1488-- TOC entry 3070 (class 0 OID 0)
1489-- Dependencies: 257
1490-- Name: provider__id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
1491--
1492
1493SELECT pg_catalog.setval('public.provider__id_seq', 3, true);
1494
1495
1496--
1497-- TOC entry 3071 (class 0 OID 0)
1498-- Dependencies: 243
1499-- Name: service__id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
1500--
1501
1502SELECT pg_catalog.setval('public.service__id_seq', 6, true);
1503
1504
1505--
1506-- TOC entry 3072 (class 0 OID 0)
1507-- Dependencies: 263
1508-- Name: sms_event__id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
1509--
1510
1511SELECT pg_catalog.setval('public.sms_event__id_seq', 91, true);
1512
1513
1514--
1515-- TOC entry 2876 (class 2606 OID 16588)
1516-- Name: category category_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
1517--
1518
1519ALTER TABLE ONLY public.category
1520 ADD CONSTRAINT category_pkey PRIMARY KEY (_id);
1521
1522
1523--
1524-- TOC entry 2872 (class 2606 OID 16547)
1525-- Name: country country_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
1526--
1527
1528ALTER TABLE ONLY public.country
1529 ADD CONSTRAINT country_pkey PRIMARY KEY (_id);
1530
1531
1532--
1533-- TOC entry 2874 (class 2606 OID 16555)
1534-- Name: flow_type flow_type_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
1535--
1536
1537ALTER TABLE ONLY public.flow_type
1538 ADD CONSTRAINT flow_type_pkey PRIMARY KEY (_id);
1539
1540
1541--
1542-- TOC entry 2868 (class 2606 OID 16512)
1543-- Name: language language_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
1544--
1545
1546ALTER TABLE ONLY public.language
1547 ADD CONSTRAINT language_pkey PRIMARY KEY (_id);
1548
1549
1550--
1551-- TOC entry 2888 (class 2606 OID 28397889)
1552-- Name: message message_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
1553--
1554
1555ALTER TABLE ONLY public.message
1556 ADD CONSTRAINT message_pkey PRIMARY KEY (_id);
1557
1558
1559--
1560-- TOC entry 2884 (class 2606 OID 25058417)
1561-- Name: operator operator_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
1562--
1563
1564ALTER TABLE ONLY public.operator
1565 ADD CONSTRAINT operator_pkey PRIMARY KEY (_id);
1566
1567
1568--
1569-- TOC entry 2878 (class 2606 OID 1261200)
1570-- Name: pricepoint pricepoint_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
1571--
1572
1573ALTER TABLE ONLY public.pricepoint
1574 ADD CONSTRAINT pricepoint_pkey PRIMARY KEY (_id);
1575
1576
1577--
1578-- TOC entry 2886 (class 2606 OID 26201276)
1579-- Name: provider provider_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
1580--
1581
1582ALTER TABLE ONLY public.provider
1583 ADD CONSTRAINT provider_pkey PRIMARY KEY (_id);
1584
1585
1586--
1587-- TOC entry 2870 (class 2606 OID 16520)
1588-- Name: service service_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
1589--
1590
1591ALTER TABLE ONLY public.service
1592 ADD CONSTRAINT service_pkey PRIMARY KEY (_id);
1593
1594
1595--
1596-- TOC entry 2890 (class 2606 OID 30659954)
1597-- Name: sms_event sms_event_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
1598--
1599
1600ALTER TABLE ONLY public.sms_event
1601 ADD CONSTRAINT sms_event_pkey PRIMARY KEY (_id);
1602
1603
1604--
1605-- TOC entry 2882 (class 2606 OID 20398943)
1606-- Name: string string_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
1607--
1608
1609ALTER TABLE ONLY public.string
1610 ADD CONSTRAINT string_pkey PRIMARY KEY (_id);
1611
1612
1613--
1614-- TOC entry 2880 (class 2606 OID 19079727)
1615-- Name: subscription subscription_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
1616--
1617
1618ALTER TABLE ONLY public.subscription
1619 ADD CONSTRAINT subscription_pkey PRIMARY KEY (_id);
1620
1621
1622--
1623-- TOC entry 2891 (class 2620 OID 29210121)
1624-- Name: subscription after_sub_insert; Type: TRIGGER; Schema: public; Owner: postgres
1625--
1626
1627CREATE TRIGGER after_sub_insert AFTER INSERT ON public.subscription FOR EACH ROW EXECUTE FUNCTION public.after_sub_insert();
1628
1629
1630--
1631-- TOC entry 2892 (class 2620 OID 34148259)
1632-- Name: subscription trg_already_subscribed; Type: TRIGGER; Schema: public; Owner: postgres
1633--
1634
1635CREATE TRIGGER trg_already_subscribed BEFORE INSERT ON public.subscription FOR EACH ROW EXECUTE FUNCTION public.already_subscribed();
1636
1637
1638--
1639-- TOC entry 2893 (class 2620 OID 29165060)
1640-- Name: subscription trg_event_handler; Type: TRIGGER; Schema: public; Owner: postgres
1641--
1642
1643CREATE TRIGGER trg_event_handler AFTER UPDATE ON public.subscription FOR EACH ROW EXECUTE FUNCTION public.event_handler();
1644
1645
1646--
1647-- TOC entry 2894 (class 2620 OID 29121108)
1648-- Name: subscription trg_language_fallback; Type: TRIGGER; Schema: public; Owner: postgres
1649--
1650
1651CREATE TRIGGER trg_language_fallback BEFORE INSERT ON public.subscription FOR EACH ROW EXECUTE FUNCTION public.language_fallback();
1652
1653
1654--
1655-- TOC entry 2895 (class 2620 OID 29072593)
1656-- Name: subscription trg_validate_subscription_params; Type: TRIGGER; Schema: public; Owner: postgres
1657--
1658
1659CREATE TRIGGER trg_validate_subscription_params BEFORE INSERT OR UPDATE ON public.subscription FOR EACH ROW EXECUTE FUNCTION public.validate_subscription_params();
1660
1661
1662-- Completed on 2020-03-08 02:19:18
1663
1664--
1665-- PostgreSQL database dump complete
1666--