DatabaseCreation: TelekomSystem.sql

File TelekomSystem.sql, 50.8 KB (added by 231139, 10 days ago)
Line 
1
2create table customer_types
3(
4 code text primary key,
5 description text,
6 is_active boolean default true not null
7);
8
9create table customer_statuses
10(
11 code text primary key,
12 description text,
13 is_active boolean default true not null
14);
15
16create table address_types
17(
18 code text primary key,
19 description text,
20 is_active boolean default true not null
21);
22
23create table account_statuses
24(
25 code text primary key,
26 description text,
27 is_active boolean default true not null
28);
29
30create table contract_types
31(
32 code text primary key,
33 description text,
34 is_active boolean default true not null
35);
36
37create table contract_statuses
38(
39 code text primary key,
40 description text,
41 is_active boolean default true not null
42);
43
44create table department_statuses
45(
46 code text primary key,
47 description text,
48 is_active boolean default true not null
49);
50
51create table employment_statuses
52(
53 code text primary key,
54 description text,
55 is_active boolean default true not null
56);
57
58create table device_types
59(
60 code text primary key,
61 description text,
62 is_active boolean default true not null
63);
64
65create table sim_types
66(
67 code text primary key,
68 description text,
69 is_active boolean default true not null
70);
71
72create table sim_statuses
73(
74 code text primary key,
75 description text,
76 is_active boolean default true not null
77);
78
79create table service_categories
80(
81 code text primary key,
82 description text,
83 is_active boolean default true not null
84);
85
86create table product_types
87(
88 code text primary key,
89 description text,
90 is_active boolean default true not null
91);
92
93create table product_statuses
94(
95 code text primary key,
96 description text,
97 is_active boolean default true not null
98);
99
100create table overage_policy_statuses
101(
102 code text primary key,
103 description text,
104 is_active boolean default true not null
105);
106
107create table plan_statuses
108(
109 code text primary key,
110 description text,
111 is_active boolean default true not null
112);
113
114create table subscription_statuses
115(
116 code text primary key,
117 description text,
118 is_active boolean default true not null
119);
120
121create table addon_types
122(
123 code text primary key,
124 description text,
125 is_active boolean default true not null
126);
127
128create table allowance_units
129(
130 code text primary key,
131 description text,
132 is_active boolean default true not null
133);
134
135create table addon_statuses
136(
137 code text primary key,
138 description text,
139 is_active boolean default true not null
140);
141
142create table subscription_addon_statuses
143(
144 code text primary key,
145 description text,
146 is_active boolean default true not null
147);
148
149create table device_assignment_statuses
150(
151 code text primary key,
152 description text,
153 is_active boolean default true not null
154);
155
156create table network_generations
157(
158 code text primary key,
159 description text,
160 is_active boolean default true not null
161);
162
163create table network_technology_statuses
164(
165 code text primary key,
166 description text,
167 is_active boolean default true not null
168);
169
170create table network_site_types
171(
172 code text primary key,
173 description text,
174 is_active boolean default true not null
175);
176
177create table network_site_statuses
178(
179 code text primary key,
180 description text,
181 is_active boolean default true not null
182);
183
184create table tower_ownership_types
185(
186 code text primary key,
187 description text,
188 is_active boolean default true not null
189);
190
191create table tower_statuses
192(
193 code text primary key,
194 description text,
195 is_active boolean default true not null
196);
197
198create table frequency_bands
199(
200 code text primary key,
201 description text,
202 is_active boolean default true not null
203);
204
205create table tower_sector_statuses
206(
207 code text primary key,
208 description text,
209 is_active boolean default true not null
210);
211
212create table coverage_types
213(
214 code text primary key,
215 description text,
216 is_active boolean default true not null
217);
218
219create table roaming_partner_statuses
220(
221 code text primary key,
222 description text,
223 is_active boolean default true not null
224);
225
226create table network_alarm_types
227(
228 code text primary key,
229 description text,
230 is_active boolean default true not null
231);
232
233create table alarm_severities
234(
235 code text primary key,
236 description text,
237 is_active boolean default true not null
238);
239
240create table network_alarm_statuses
241(
242 code text primary key,
243 description text,
244 is_active boolean default true not null
245);
246
247create table outage_types
248(
249 code text primary key,
250 description text,
251 is_active boolean default true not null
252);
253
254create table outage_statuses
255(
256 code text primary key,
257 description text,
258 is_active boolean default true not null
259);
260
261create table call_types
262(
263 code text primary key,
264 description text,
265 is_active boolean default true not null
266);
267
268create table usage_directions
269(
270 code text primary key,
271 description text,
272 is_active boolean default true not null
273);
274
275create table sms_types
276(
277 code text primary key,
278 description text,
279 is_active boolean default true not null
280);
281
282create table invoice_statuses
283(
284 code text primary key,
285 description text,
286 is_active boolean default true not null
287);
288
289create table invoice_item_types
290(
291 code text primary key,
292 description text,
293 is_active boolean default true not null
294);
295
296create table payment_method_statuses
297(
298 code text primary key,
299 description text,
300 is_active boolean default true not null
301);
302
303create table payment_statuses
304(
305 code text primary key,
306 description text,
307 is_active boolean default true not null
308);
309
310create table billing_run_statuses
311(
312 code text primary key,
313 description text,
314 is_active boolean default true not null
315);
316
317create table crm_ticket_types
318(
319 code text primary key,
320 description text,
321 is_active boolean default true not null
322);
323
324create table ticket_priorities
325(
326 code text primary key,
327 description text,
328 is_active boolean default true not null
329);
330
331create table crm_ticket_statuses
332(
333 code text primary key,
334 description text,
335 is_active boolean default true not null
336);
337
338create table interaction_types
339(
340 code text primary key,
341 description text,
342 is_active boolean default true not null
343);
344
345create table interaction_channels
346(
347 code text primary key,
348 description text,
349 is_active boolean default true not null
350);
351
352create table employee_assignment_types
353(
354 code text primary key,
355 description text,
356 is_active boolean default true not null
357);
358
359create table employee_assignment_statuses
360(
361 code text primary key,
362 description text,
363 is_active boolean default true not null
364);
365
366create table plan_service_statuses
367(
368 code text primary key,
369 description text,
370 is_active boolean default true not null
371);
372
373create table customers
374(
375 customer_id bigserial
376 primary key,
377 customer_type text not null
378 constraint fk_customers_customer_type
379 references customer_types (code) on delete restrict on update restrict,
380 first_name text,
381 last_name text,
382 company_name text,
383 email text
384 unique,
385 phone text,
386 date_of_birth date,
387 status text default 'active'::text not null
388 constraint fk_customers_status
389 references customer_statuses (code) on delete set default on update restrict,
390 created_at timestamptz default CURRENT_TIMESTAMP not null,
391 updated_at timestamptz default CURRENT_TIMESTAMP not null,
392 constraint chk_customers_type_fields
393 check (((customer_type = 'individual'::text) AND
394 (first_name IS NOT NULL) AND (last_name IS NOT NULL)) OR
395 ((customer_type = 'business'::text) AND
396 (first_name IS NOT NULL) AND (last_name IS NOT NULL) AND
397 (company_name IS NOT NULL)))
398);
399
400create table customer_addresses
401(
402 address_id bigserial
403 primary key,
404 customer_id bigint not null
405 constraint fk_customer_addresses_customer
406 references customers on delete restrict on update restrict,
407 address_type text not null
408 constraint fk_customer_addresses_address_type
409 references address_types (code) on delete restrict on update restrict,
410 country text not null,
411 city text not null,
412 street text not null,
413 postal_code text,
414 latitude numeric,
415 longitude numeric,
416 location geometry(Point, 4326)
417 generated always as
418 (
419 case
420 when latitude is null then null
421 else ST_SetSRID(
422 ST_MakePoint(longitude::double precision, latitude::double precision),
423 4326
424 )
425 end
426 ) stored,
427 is_primary boolean default false not null,
428 created_at timestamptz default CURRENT_TIMESTAMP not null,
429 constraint chk_customer_addresses_latitude
430 check ((latitude IS NULL) OR (latitude between -90 and 90)),
431 constraint chk_customer_addresses_longitude
432 check ((longitude IS NULL) OR (longitude between -180 and 180)),
433 constraint chk_customer_addresses_coordinates_pair
434 check ((latitude IS NULL) = (longitude IS NULL))
435);
436
437create table billing_cycles
438(
439 billing_cycle_id bigserial
440 primary key,
441 cycle_name text not null
442 unique,
443 day_of_month integer not null
444 constraint chk_billing_cycles_day
445 check ((day_of_month >= 1) AND (day_of_month <= 31)),
446 grace_days integer default 0 not null
447 constraint chk_billing_cycles_grace
448 check (grace_days >= 0),
449 is_active boolean default true not null,
450 created_at timestamptz default CURRENT_TIMESTAMP not null
451);
452
453create table accounts
454(
455 account_id bigserial
456 primary key,
457 customer_id bigint not null
458 constraint fk_accounts_customer
459 references customers on delete restrict on update restrict,
460 account_number text not null
461 unique,
462 account_status text default 'active'::text not null
463 constraint fk_accounts_account_status
464 references account_statuses (code) on delete set default on update restrict,
465 credit_limit numeric default 0.00 not null
466 constraint chk_accounts_credit_limit
467 check (credit_limit >= (0)::numeric),
468 current_balance numeric default 0.00 not null
469 constraint balance_above_zero
470 check (current_balance >= (0)::numeric),
471 billing_cycle_id bigint
472 constraint fk_accounts_billing_cycle
473 references billing_cycles on delete set null on update restrict,
474 created_at timestamptz default CURRENT_TIMESTAMP not null,
475 updated_at timestamptz default CURRENT_TIMESTAMP not null,
476 constraint uq_accounts_customer
477 unique (customer_id),
478 constraint uq_accounts_account_customer
479 unique (account_id, customer_id)
480);
481
482create table contracts
483(
484 contract_id bigserial
485 primary key,
486 customer_id bigint not null
487 constraint fk_contracts_customer
488 references customers on delete restrict on update restrict,
489 account_id bigint not null
490 constraint fk_contracts_account
491 references accounts on delete restrict on update restrict,
492 contract_type text not null
493 constraint fk_contracts_contract_type
494 references contract_types (code) on delete restrict on update restrict,
495 contract_number text not null
496 unique,
497 start_date date not null,
498 end_date date,
499 auto_renew boolean default false not null,
500 status text default 'active'::text not null
501 constraint fk_contracts_status
502 references contract_statuses (code) on delete set default on update restrict,
503 signed_at timestamptz,
504 constraint chk_contracts_dates
505 check ((end_date IS NULL) OR (end_date >= start_date)),
506 constraint uq_contracts_contract_account
507 unique (contract_id, account_id),
508 constraint fk_contracts_account_customer
509 foreign key (account_id, customer_id)
510 references accounts (account_id, customer_id) on delete restrict on update restrict
511);
512
513create table departments
514(
515 department_id bigserial
516 primary key,
517 department_name text not null
518 unique,
519 location text,
520 status text default 'active'::text not null
521 constraint fk_departments_status
522 references department_statuses (code) on delete set default on update restrict
523);
524
525create table employee_roles
526(
527 role_id bigserial
528 primary key,
529 role_name text not null
530 unique,
531 role_description text not null,
532 access_level integer default 1 not null
533 constraint chk_employee_roles_access
534 check (access_level >= 1),
535 is_active boolean default true not null
536);
537
538create table employees
539(
540 employee_id bigserial
541 primary key,
542 department_id bigint not null
543 constraint fk_employees_department
544 references departments on delete restrict on update restrict,
545 role_id bigint not null
546 constraint fk_employees_role
547 references employee_roles on delete restrict on update restrict,
548 first_name text not null,
549 last_name text not null,
550 email text not null
551 unique,
552 phone text,
553 hire_date date not null,
554 employment_status text default 'active'::text not null
555 constraint fk_employees_employment_status
556 references employment_statuses (code) on delete set default on update restrict,
557 manager_id bigint
558 constraint fk_employees_manager
559 references employees on delete set null on update restrict,
560 constraint chk_employees_not_own_manager
561 check ((manager_id IS NULL) OR (manager_id <> employee_id))
562);
563
564create table devices
565(
566 device_id bigserial
567 primary key,
568 imei text not null
569 unique,
570 serial_number text not null
571 unique,
572 manufacturer text,
573 model text,
574 device_type text
575 constraint fk_devices_device_type
576 references device_types (code) on delete set null on update restrict,
577 purchase_date date
578);
579
580create table sim_cards
581(
582 sim_id bigserial
583 primary key,
584 iccid text not null
585 unique,
586 imsi text not null
587 unique,
588 msisdn text not null
589 unique,
590 sim_type text not null
591 constraint fk_sim_cards_sim_type
592 references sim_types (code) on delete restrict on update restrict,
593 pin_code text,
594 puk_code text,
595 status text default 'available'::text not null
596 constraint fk_sim_cards_status
597 references sim_statuses (code) on delete set default on update restrict,
598 issued_at timestamptz
599);
600
601create table services
602(
603 service_id bigserial
604 primary key,
605 service_name text not null
606 unique,
607 service_category text not null
608 constraint fk_services_service_category
609 references service_categories (code) on delete restrict on update restrict,
610 description text,
611 is_active boolean default true not null,
612 created_at timestamptz default CURRENT_TIMESTAMP not null
613);
614
615create table products
616(
617 product_id bigserial
618 primary key,
619 product_name text not null,
620 product_code text not null
621 unique,
622 product_type text not null
623 constraint fk_products_product_type
624 references product_types (code) on delete restrict on update restrict,
625 description text,
626 status text default 'active'::text not null
627 constraint fk_products_status
628 references product_statuses (code) on delete set default on update restrict,
629 created_at timestamptz default CURRENT_TIMESTAMP not null
630);
631
632create table overage_policies
633(
634 overage_policy_id bigserial
635 primary key,
636 policy_name text not null
637 unique,
638 voice_rate_per_min numeric default 0.0000 not null
639 constraint chk_overage_voice_rate
640 check (voice_rate_per_min >= 0),
641 sms_rate numeric default 0.0000 not null
642 constraint chk_overage_sms_rate
643 check (sms_rate >= 0),
644 data_rate_per_mb numeric default 0.0000 not null
645 constraint chk_overage_data_rate
646 check (data_rate_per_mb >= 0),
647 throttle_after_limit boolean default false not null,
648 fair_use_limit_mb numeric
649 constraint chk_overage_fair_use_limit
650 check ((fair_use_limit_mb IS NULL) OR (fair_use_limit_mb >= 0)),
651 status text default 'active'::text not null
652 constraint fk_overage_policies_status
653 references overage_policy_statuses (code) on delete set default on update restrict
654);
655
656create table plans
657(
658 plan_id bigserial
659 primary key,
660 product_id bigint not null
661 constraint fk_plans_product
662 references products on delete restrict on update restrict,
663 plan_name text not null,
664 monthly_fee numeric default 0.00 not null
665 constraint check_monthly_fee
666 check (monthly_fee >= (0)::numeric),
667 overage_policy_id bigint
668 constraint fk_plans_overage_policy
669 references overage_policies on delete set null on update restrict,
670 contract_term_months integer default 0 not null
671 constraint chk_plans_term
672 check (contract_term_months >= 0),
673 status text default 'active'::text not null
674 constraint fk_plans_status
675 references plan_statuses (code) on delete set default on update restrict
676);
677
678create table subscriptions
679(
680 subscription_id bigserial
681 primary key,
682 account_id bigint not null
683 constraint fk_subscriptions_account
684 references accounts on delete restrict on update restrict,
685 plan_id bigint not null
686 constraint fk_subscriptions_plan
687 references plans on delete restrict on update restrict,
688 contract_id bigint
689 constraint fk_subscriptions_contract
690 references contracts on delete set null on update restrict,
691 subscription_number text not null
692 unique,
693 activation_date date not null,
694 end_date date,
695 status text default 'active'::text not null
696 constraint fk_subscriptions_status
697 references subscription_statuses (code) on delete set default on update restrict,
698 billing_start_date date not null,
699 constraint chk_subscriptions_dates
700 check ((end_date IS NULL) OR (end_date >= activation_date)),
701 constraint chk_subscriptions_billing_start
702 check (billing_start_date <= activation_date),
703 constraint chk_subscriptions_end_month_end
704 check ((end_date IS NULL) OR (extract(day from (end_date + 1)) = 1)),
705 constraint uq_subscriptions_subscription_account
706 unique (subscription_id, account_id),
707 constraint fk_subscriptions_contract_account
708 foreign key (contract_id, account_id)
709 references contracts (contract_id, account_id) on delete restrict on update restrict
710);
711
712create table addons
713(
714 addon_id bigserial
715 primary key,
716 addon_name text not null,
717 addon_type text not null
718 constraint fk_addons_addon_type
719 references addon_types (code) on delete restrict on update restrict,
720 price numeric default 0.00 not null
721 constraint chk_addons_price
722 check (price >= 0),
723 allowance_value numeric
724 constraint chk_addons_allowance_value
725 check ((allowance_value IS NULL) OR (allowance_value >= 0)),
726 allowance_unit text
727 constraint fk_addons_allowance_unit
728 references allowance_units (code) on delete set null on update restrict,
729 is_recurring boolean default true not null,
730 status text default 'active'::text not null
731 constraint fk_addons_status
732 references addon_statuses (code) on delete set default on update restrict
733);
734
735create table subscription_addons
736(
737 subscription_addon_id bigserial
738 primary key,
739 subscription_id bigint not null
740 constraint fk_subscription_addons_subscription
741 references subscriptions on delete restrict on update restrict,
742 addon_id bigint not null
743 constraint fk_subscription_addons_addon
744 references addons on delete restrict on update restrict,
745 activation_date date not null,
746 deactivation_date date,
747 status text default 'active'::text not null
748 constraint fk_subscription_addons_status
749 references subscription_addon_statuses (code) on delete set default on update restrict,
750 price_at_activation numeric default 0.00 not null
751 constraint chk_subscription_addons_price
752 check (price_at_activation >= 0),
753 constraint chk_subscription_addons_dates
754 check ((deactivation_date IS NULL) OR (deactivation_date >= activation_date)),
755 constraint chk_subscription_addons_status_date
756 check ((status = 'active'::text) = (deactivation_date IS NULL))
757);
758
759create table subscription_status_history
760(
761 status_history_id bigserial
762 primary key,
763 subscription_id bigint not null
764 constraint fk_sub_status_history_subscription
765 references subscriptions on delete restrict on update restrict,
766 old_status text
767 constraint fk_subscription_status_history_old_status
768 references subscription_statuses (code) on delete set null on update restrict,
769 new_status text not null
770 constraint fk_subscription_status_history_new_status
771 references subscription_statuses (code) on delete restrict on update restrict,
772 changed_at timestamptz default CURRENT_TIMESTAMP not null,
773 changed_by_employee_id bigint
774 constraint fk_sub_status_history_employee
775 references employees on delete set null on update restrict,
776 reason text
777);
778
779create table device_assignments
780(
781 device_assignment_id bigserial
782 primary key,
783 device_id bigint not null
784 constraint fk_device_assignments_device
785 references devices on delete restrict on update restrict,
786 subscription_id bigint not null
787 constraint fk_device_assignments_subscription
788 references subscriptions on delete restrict on update restrict,
789 assigned_from timestamptz not null,
790 assigned_to timestamptz,
791 assignment_status text default 'active'::text not null
792 constraint fk_device_assignments_assignment_status
793 references device_assignment_statuses (code) on delete set default on update restrict,
794 notes text,
795 constraint chk_device_assignments_times
796 check ((assigned_to IS NULL) OR (assigned_to > assigned_from)),
797 constraint chk_device_assignments_status_time
798 check ((assignment_status = 'active'::text) = (assigned_to IS NULL)),
799 constraint ex_device_assignments_no_overlap
800 exclude using gist
801 (device_id with =,
802 tstzrange(assigned_from, coalesce(assigned_to, 'infinity'::timestamptz), '[)') with &&)
803);
804
805create table network_technologies
806(
807 technology_id bigserial
808 primary key,
809 technology_name text not null
810 unique,
811 generation text not null
812 constraint fk_network_technologies_generation
813 references network_generations (code) on delete restrict on update restrict,
814 description text,
815 status text default 'active'::text not null
816 constraint fk_network_technologies_status
817 references network_technology_statuses (code) on delete set default on update restrict
818);
819
820create table network_sites
821(
822 site_id bigserial
823 primary key,
824 site_code text not null
825 unique,
826 site_name text not null,
827 address text,
828 region text,
829 latitude numeric,
830 longitude numeric,
831 location geometry(Point, 4326)
832 generated always as
833 (
834 case
835 when latitude is null then null
836 else ST_SetSRID(
837 ST_MakePoint(longitude::double precision, latitude::double precision),
838 4326
839 )
840 end
841 ) stored,
842 site_type text
843 constraint fk_network_sites_site_type
844 references network_site_types (code) on delete set null on update restrict,
845 status text default 'active'::text not null
846 constraint fk_network_sites_status
847 references network_site_statuses (code) on delete set default on update restrict,
848 opened_at timestamptz,
849 constraint chk_network_sites_latitude
850 check ((latitude IS NULL) OR (latitude between -90 and 90)),
851 constraint chk_network_sites_longitude
852 check ((longitude IS NULL) OR (longitude between -180 and 180)),
853 constraint chk_network_sites_coordinates_pair
854 check ((latitude IS NULL) = (longitude IS NULL))
855);
856
857create table cell_towers
858(
859 tower_id bigserial
860 primary key,
861 site_id bigint not null
862 constraint fk_cell_towers_site
863 references network_sites on delete restrict on update restrict,
864 tower_code text not null
865 unique,
866 height_meters numeric
867 constraint chk_cell_towers_height
868 check ((height_meters IS NULL) OR (height_meters >= 0)),
869 ownership_type text
870 constraint fk_cell_towers_ownership_type
871 references tower_ownership_types (code) on delete set null on update restrict,
872 installation_date date,
873 status text default 'active'::text not null
874 constraint fk_cell_towers_status
875 references tower_statuses (code) on delete set default on update restrict,
876 vendor_name text
877);
878
879create table tower_sectors
880(
881 sector_id bigserial
882 primary key,
883 tower_id bigint not null
884 constraint fk_tower_sectors_tower
885 references cell_towers on delete restrict on update restrict,
886 sector_label text not null,
887 azimuth integer
888 constraint chk_tower_sectors_azimuth
889 check ((azimuth IS NULL) OR ((azimuth >= 0) AND (azimuth <= 359))),
890 beamwidth integer
891 constraint chk_tower_sectors_beamwidth
892 check ((beamwidth >= 1) AND (beamwidth <= 360)),
893 frequency_band text
894 constraint fk_tower_sectors_frequency_band
895 references frequency_bands (code) on delete set null on update restrict,
896 technology_id bigint not null
897 constraint fk_tower_sectors_technology
898 references network_technologies on delete restrict on update restrict,
899 status text default 'active'::text not null
900 constraint fk_tower_sectors_status
901 references tower_sector_statuses (code) on delete set default on update restrict,
902 constraint uq_tower_sector_label
903 unique (tower_id, sector_label)
904);
905
906create table coverage_zones
907(
908 coverage_zone_id bigserial
909 primary key,
910 sector_id bigint not null
911 constraint fk_coverage_zones_sector
912 references tower_sectors on delete restrict on update restrict,
913 coverage_type text not null
914 constraint fk_coverage_zones_coverage_type
915 references coverage_types (code) on delete restrict on update restrict,
916 zone_description text,
917 coverage_radius_m integer default 1500 not null
918 constraint chk_coverage_zones_radius
919 check (coverage_radius_m between 100 and 50000),
920 coverage_area geometry(Polygon, 4326),
921 signal_quality_score numeric
922 constraint chk_signal_quality_score
923 check ((signal_quality_score IS NULL) OR
924 ((signal_quality_score >= (0)::numeric) AND (signal_quality_score <= (100)::numeric))),
925 last_measured_at timestamptz
926);
927
928create table roaming_partners
929(
930 roaming_partner_id bigserial
931 primary key,
932 partner_name text not null,
933 country text not null,
934 mcc text,
935 mnc text,
936 agreement_start date,
937 agreement_end date,
938 status text default 'active'::text not null
939 constraint fk_roaming_partners_status
940 references roaming_partner_statuses (code) on delete set default on update restrict,
941 constraint chk_roaming_partners_dates
942 check ((agreement_end IS NULL) OR
943 ((agreement_start IS NOT NULL) AND (agreement_end >= agreement_start)))
944);
945
946create table network_alarms
947(
948 alarm_id bigserial
949 primary key,
950 tower_id bigint
951 constraint fk_network_alarms_tower
952 references cell_towers on delete set null on update restrict,
953 sector_id bigint
954 constraint fk_network_alarms_sector
955 references tower_sectors on delete set null on update restrict,
956 alarm_type text not null
957 constraint fk_network_alarms_alarm_type
958 references network_alarm_types (code) on delete restrict on update restrict,
959 severity text not null
960 constraint fk_network_alarms_severity
961 references alarm_severities (code) on delete restrict on update restrict,
962 raised_at timestamptz not null,
963 cleared_at timestamptz,
964 status text default 'open'::text not null
965 constraint fk_network_alarms_status
966 references network_alarm_statuses (code) on delete set default on update restrict,
967 description text,
968 constraint chk_network_alarms_times
969 check ((cleared_at IS NULL) OR (cleared_at >= raised_at)),
970 constraint chk_network_alarms_status_time
971 check ((status = 'cleared'::text) = (cleared_at IS NOT NULL)),
972 constraint chk_alarm_exactly_one_location
973 check (((tower_id IS NOT NULL) AND (sector_id IS NULL)) OR ((tower_id IS NULL) AND (sector_id IS NOT NULL)))
974);
975
976create table outages
977(
978 outage_id bigserial
979 primary key,
980 site_id bigint not null
981 constraint fk_outages_site
982 references network_sites on delete restrict on update restrict,
983 alarm_id bigint
984 constraint fk_outages_alarm
985 references network_alarms on delete set null on update restrict,
986 outage_type text not null
987 constraint fk_outages_outage_type
988 references outage_types (code) on delete restrict on update restrict,
989 start_time timestamptz not null,
990 end_time timestamptz,
991 status text default 'open'::text not null
992 constraint fk_outages_status
993 references outage_statuses (code) on delete set default on update restrict,
994 root_cause text,
995 constraint chk_outages_times
996 check ((end_time IS NULL) OR (end_time >= start_time)),
997 constraint chk_outages_status_time
998 check ((status = 'open'::text) = (end_time IS NULL))
999);
1000
1001create table usage_cdr_calls
1002(
1003 call_cdr_id bigserial
1004 primary key,
1005 subscription_id bigint not null
1006 constraint fk_cdr_calls_subscription
1007 references subscriptions on delete restrict on update restrict,
1008 originating_msisdn text not null,
1009 destination_msisdn text not null,
1010 sector_id bigint
1011 constraint fk_cdr_calls_sector
1012 references tower_sectors on delete set null on update restrict,
1013 roaming_partner_id bigint
1014 constraint fk_cdr_calls_roaming_partner
1015 references roaming_partners on delete set null on update restrict,
1016 event_start_time timestamptz not null,
1017 event_end_time timestamptz not null,
1018 duration_seconds integer not null
1019 constraint chk_cdr_calls_duration
1020 check (duration_seconds >= 0),
1021 call_type text not null
1022 constraint fk_usage_cdr_calls_call_type
1023 references call_types (code) on delete restrict on update restrict,
1024 direction text not null
1025 constraint fk_usage_cdr_calls_direction
1026 references usage_directions (code) on delete restrict on update restrict,
1027 charge_amount numeric default 0.0000 not null
1028 constraint chk_cdr_calls_charge_amount
1029 check (charge_amount >= 0),
1030 fraud_score numeric default 0.00
1031 constraint chk_cdr_calls_fraud_score_nonnegative
1032 check ((fraud_score IS NULL) OR (fraud_score >= 0)),
1033 constraint chk_cdr_calls_times
1034 check (event_end_time >= event_start_time)
1035);
1036
1037create table usage_cdr_sms
1038(
1039 sms_cdr_id bigserial
1040 primary key,
1041 subscription_id bigint not null
1042 constraint fk_cdr_sms_subscription
1043 references subscriptions on delete restrict on update restrict,
1044 source_msisdn text not null,
1045 destination_msisdn text not null,
1046 sector_id bigint
1047 constraint fk_cdr_sms_sector
1048 references tower_sectors on delete set null on update restrict,
1049 roaming_partner_id bigint
1050 constraint fk_cdr_sms_roaming_partner
1051 references roaming_partners on delete set null on update restrict,
1052 event_time timestamptz not null,
1053 sms_type text not null
1054 constraint fk_usage_cdr_sms_sms_type
1055 references sms_types (code) on delete restrict on update restrict,
1056 direction text not null
1057 constraint fk_usage_cdr_sms_direction
1058 references usage_directions (code) on delete restrict on update restrict,
1059 charge_amount numeric default 0.0000 not null
1060 constraint chk_cdr_sms_charge_amount
1061 check (charge_amount >= 0)
1062);
1063
1064create table usage_cdr_data
1065(
1066 data_cdr_id bigserial
1067 primary key,
1068 subscription_id bigint not null
1069 constraint fk_cdr_data_subscription
1070 references subscriptions on delete restrict on update restrict,
1071 sector_id bigint
1072 constraint fk_cdr_data_sector
1073 references tower_sectors on delete set null on update restrict,
1074 roaming_partner_id bigint
1075 constraint fk_cdr_data_roaming_partner
1076 references roaming_partners on delete set null on update restrict,
1077 session_start timestamptz not null,
1078 session_end timestamptz not null,
1079 data_used_mb numeric default 0.0000 not null
1080 constraint chk_cdr_data_used_mb
1081 check (data_used_mb >= 0),
1082 apn text,
1083 ip_address inet,
1084 charge_amount numeric default 0.0000 not null
1085 constraint chk_cdr_data_charge_amount
1086 check (charge_amount >= 0),
1087 constraint chk_cdr_data_times
1088 check (session_end >= session_start)
1089);
1090
1091create table usage_aggregates_daily
1092(
1093 usage_daily_id bigserial
1094 primary key,
1095 subscription_id bigint not null
1096 constraint fk_usage_daily_subscription
1097 references subscriptions on delete restrict on update restrict,
1098 usage_date date not null,
1099 total_call_seconds bigint default 0 not null,
1100 total_sms_count integer default 0 not null,
1101 total_data_mb numeric default 0.0000 not null,
1102 total_charge_amount numeric default 0.0000 not null,
1103 generated_at timestamptz default CURRENT_TIMESTAMP not null,
1104 constraint chk_usage_daily_call_seconds
1105 check (total_call_seconds >= 0),
1106 constraint chk_usage_daily_sms_count
1107 check (total_sms_count >= 0),
1108 constraint chk_usage_daily_data_mb
1109 check (total_data_mb >= 0),
1110 constraint chk_usage_daily_charge_amount
1111 check (total_charge_amount >= 0),
1112 constraint uq_usage_daily_subscription_date
1113 unique (subscription_id, usage_date)
1114);
1115
1116create table invoices
1117(
1118 invoice_id bigserial
1119 primary key,
1120 account_id bigint not null
1121 constraint fk_invoices_account
1122 references accounts on delete restrict on update restrict,
1123 invoice_number text not null
1124 unique,
1125 billing_period_start date not null,
1126 billing_period_end date not null,
1127 issue_date date not null,
1128 due_date date not null,
1129 total_amount numeric default 0.00 not null,
1130 tax_amount numeric default 0.00 not null,
1131 discount_amount numeric default 0.00 not null,
1132 status text default 'issued'::text not null
1133 constraint fk_invoices_status
1134 references invoice_statuses (code) on delete set default on update restrict,
1135 constraint chk_invoices_total_amount
1136 check (total_amount >= 0),
1137 constraint chk_invoices_tax_amount
1138 check (tax_amount >= 0),
1139 constraint chk_invoices_discount_amount
1140 check (discount_amount >= 0),
1141 constraint chk_invoices_period
1142 check (billing_period_end >= billing_period_start),
1143 constraint chk_invoices_due_date
1144 check (due_date >= issue_date),
1145 constraint uq_invoices_invoice_account
1146 unique (invoice_id, account_id)
1147);
1148
1149create table invoice_items
1150(
1151 invoice_item_id bigserial
1152 primary key,
1153 invoice_id bigint not null
1154 constraint fk_invoice_items_invoice
1155 references invoices on delete restrict on update restrict,
1156 subscription_id bigint
1157 constraint fk_invoice_items_subscription
1158 references subscriptions on delete set null on update restrict,
1159 item_type text not null
1160 constraint fk_invoice_items_item_type
1161 references invoice_item_types (code) on delete restrict on update restrict,
1162 description text,
1163 quantity numeric default 1 not null
1164 constraint chk_invoice_items_quantity
1165 check (quantity >= 0),
1166 unit_price numeric default 0.00 not null
1167 constraint chk_invoice_items_unit_price
1168 check (unit_price >= 0),
1169 line_amount numeric default 0.00 not null
1170 constraint chk_invoice_items_line_amount
1171 check (line_amount >= 0),
1172 tax_rate numeric default 0.00 not null
1173 constraint chk_invoice_items_tax_rate
1174 check (tax_rate >= 0)
1175);
1176
1177create table payment_methods
1178(
1179 payment_method_id bigserial
1180 primary key,
1181 method_name text not null
1182 unique,
1183 provider_name text,
1184 is_online boolean default false not null,
1185 status text default 'active'::text not null
1186 constraint fk_payment_methods_status
1187 references payment_method_statuses (code) on delete set default on update restrict
1188);
1189
1190create table payments
1191(
1192 payment_id bigserial
1193 primary key,
1194 account_id bigint not null
1195 constraint fk_payments_account
1196 references accounts on delete restrict on update restrict,
1197 invoice_id bigint
1198 constraint fk_payments_invoice
1199 references invoices on delete set null on update restrict,
1200 payment_method_id bigint
1201 constraint fk_payments_method
1202 references payment_methods on delete set null on update restrict,
1203 payment_date timestamptz not null,
1204 amount numeric not null
1205 constraint chk_payments_amount
1206 check (amount >= (0)::numeric),
1207 reference_number text,
1208 status text default 'completed'::text not null
1209 constraint fk_payments_status
1210 references payment_statuses (code) on delete set default on update restrict,
1211 constraint fk_payments_invoice_account
1212 foreign key (invoice_id, account_id)
1213 references invoices (invoice_id, account_id) on delete restrict on update restrict
1214);
1215
1216create table billing_runs
1217(
1218 billing_run_id bigserial
1219 primary key,
1220 billing_cycle_id bigint not null
1221 constraint fk_billing_runs_cycle
1222 references billing_cycles on delete restrict on update restrict,
1223 period_start date not null,
1224 period_end date not null,
1225 run_started_at timestamptz not null,
1226 run_finished_at timestamptz,
1227 status text default 'running'::text not null
1228 constraint fk_billing_runs_status
1229 references billing_run_statuses (code) on delete set default on update restrict,
1230 generated_invoices_count integer default 0 not null
1231 constraint chk_billing_runs_generated_count
1232 check (generated_invoices_count >= 0),
1233 constraint chk_billing_runs_period
1234 check (period_end >= period_start),
1235 constraint chk_billing_runs_times
1236 check ((run_finished_at IS NULL) OR (run_finished_at >= run_started_at)),
1237 constraint chk_billing_runs_status_time
1238 check ((status = 'running'::text) = (run_finished_at IS NULL))
1239);
1240
1241create table crm_tickets
1242(
1243 ticket_id bigserial
1244 primary key,
1245 customer_id bigint not null
1246 constraint fk_crm_tickets_customer
1247 references customers on delete restrict on update restrict,
1248 account_id bigint
1249 constraint fk_crm_tickets_account
1250 references accounts on delete set null on update restrict,
1251 subscription_id bigint
1252 constraint fk_crm_tickets_subscription
1253 references subscriptions on delete set null on update restrict,
1254 assigned_employee_id bigint not null
1255 constraint fk_crm_tickets_employee
1256 references employees on delete restrict on update restrict,
1257 ticket_type text not null
1258 constraint fk_crm_tickets_ticket_type
1259 references crm_ticket_types (code) on delete restrict on update restrict,
1260 subject text not null,
1261 description text,
1262 priority text default 'medium'::text not null
1263 constraint fk_crm_tickets_priority
1264 references ticket_priorities (code) on delete set default on update restrict,
1265 status text default 'open'::text not null
1266 constraint fk_crm_tickets_status
1267 references crm_ticket_statuses (code) on delete set default on update restrict,
1268 created_at timestamptz default CURRENT_TIMESTAMP not null,
1269 closed_at timestamptz,
1270 parent_ticket_id bigint
1271 constraint fk_crm_tickets_parent
1272 references crm_tickets on delete set null on update restrict,
1273 constraint chk_crm_tickets_times
1274 check ((closed_at IS NULL) OR (closed_at >= created_at)),
1275 constraint chk_crm_tickets_status_time
1276 check ((status = 'closed'::text) = (closed_at IS NOT NULL)),
1277 constraint chk_crm_tickets_not_own_parent
1278 check ((parent_ticket_id IS NULL) OR (parent_ticket_id <> ticket_id)),
1279 constraint chk_crm_tickets_subscription_requires_account
1280 check ((subscription_id IS NULL) OR (account_id IS NOT NULL)),
1281 constraint fk_crm_tickets_account_customer
1282 foreign key (account_id, customer_id)
1283 references accounts (account_id, customer_id) on delete restrict on update restrict,
1284 constraint fk_crm_tickets_subscription_account
1285 foreign key (subscription_id, account_id)
1286 references subscriptions (subscription_id, account_id) on delete set null on update restrict
1287);
1288
1289create table crm_interactions
1290(
1291 interaction_id bigserial
1292 primary key,
1293 ticket_id bigint not null
1294 constraint fk_crm_interactions_ticket
1295 references crm_tickets on delete restrict on update restrict,
1296 employee_id bigint
1297 constraint fk_crm_interactions_employee
1298 references employees on delete set null on update restrict,
1299 interaction_type text not null
1300 constraint fk_crm_interactions_interaction_type
1301 references interaction_types (code) on delete restrict on update restrict,
1302 channel text not null
1303 constraint fk_crm_interactions_channel
1304 references interaction_channels (code) on delete restrict on update restrict,
1305 interaction_time timestamptz default CURRENT_TIMESTAMP not null,
1306 notes text,
1307 old_status text
1308 constraint fk_crm_interactions_old_status
1309 references crm_ticket_statuses (code) on delete set null on update restrict,
1310 new_status text
1311 constraint fk_crm_interactions_new_status
1312 references crm_ticket_statuses (code) on delete set null on update restrict
1313);
1314
1315create table employee_assignments
1316(
1317 assignment_id bigserial
1318 primary key,
1319 employee_id bigint not null
1320 constraint fk_employee_assignments_employee
1321 references employees on delete restrict on update restrict,
1322 ticket_id bigint
1323 constraint fk_employee_assignments_ticket
1324 references crm_tickets on delete set null on update restrict,
1325 outage_id bigint
1326 constraint fk_employee_assignments_outage
1327 references outages on delete set null on update restrict,
1328 assignment_type text not null
1329 constraint fk_employee_assignments_assignment_type
1330 references employee_assignment_types (code) on delete restrict on update restrict,
1331 start_time timestamptz not null,
1332 end_time timestamptz,
1333 status text default 'assigned'::text not null
1334 constraint fk_employee_assignments_status
1335 references employee_assignment_statuses (code) on delete set default on update restrict,
1336 constraint chk_employee_assignments_times
1337 check ((end_time IS NULL) OR (end_time >= start_time)),
1338 constraint chk_employee_assignments_exactly_one_target
1339 check (((ticket_id IS NOT NULL) AND (outage_id IS NULL)) OR
1340 ((ticket_id IS NULL) AND (outage_id IS NOT NULL)))
1341);
1342
1343create table sim_card_subscription_history
1344(
1345 sim_card_subscription_history_id bigserial
1346 primary key,
1347 sim_id bigint not null
1348 constraint fk_sim_history_sim
1349 references sim_cards on delete restrict on update restrict,
1350 subscription_id bigint not null
1351 constraint fk_sim_history_subscription
1352 references subscriptions on delete restrict on update restrict,
1353 start_date timestamptz not null,
1354 end_date timestamptz,
1355 constraint chk_sim_history_dates
1356 check ((end_date IS NULL) OR (end_date > start_date)),
1357 constraint ex_sim_history_no_overlap
1358 exclude using gist
1359 (sim_id with =,
1360 tstzrange(start_date, coalesce(end_date, 'infinity'::timestamptz), '[)') with &&)
1361);
1362
1363create table plan_services
1364(
1365 plan_service_id bigserial
1366 primary key,
1367 plan_id bigint not null
1368 constraint fk_plan_services_plan
1369 references plans on delete restrict on update restrict,
1370 service_id bigint not null
1371 constraint fk_plan_services_service
1372 references services on delete restrict on update restrict,
1373 allowance_value numeric
1374 constraint chk_plan_services_allowance_value
1375 check ((allowance_value IS NULL) OR (allowance_value >= 0)),
1376 allowance_unit text
1377 constraint fk_plan_services_allowance_unit
1378 references allowance_units (code) on delete set null on update restrict,
1379 is_unlimited boolean default false not null,
1380 is_included boolean default true not null,
1381 status text default 'active'::text not null
1382 constraint fk_plan_services_status
1383 references plan_service_statuses (code) on delete set default on update restrict,
1384 constraint uq_plan_services_plan_service
1385 unique (plan_id, service_id)
1386);