DatabaseCreation: TelecomSystemDDL.txt

File TelecomSystemDDL.txt, 33.7 KB (added by 231094, 3 weeks ago)
Line 
1create table public.customers
2(
3 customer_id bigserial
4 primary key,
5 customer_type text not null
6 constraint chk_customers_type_value
7 check (customer_type = ANY (ARRAY ['individual'::text, 'business'::text])),
8 first_name text,
9 last_name text,
10 company_name text,
11 email text
12 unique,
13 phone text,
14 date_of_birth date,
15 status text default 'active'::text not null
16 constraint check_status
17 check (status = ANY (ARRAY ['active'::text, 'suspended'::text, 'closed'::text])),
18 created_at timestamp default CURRENT_TIMESTAMP not null,
19 updated_at timestamp default CURRENT_TIMESTAMP not null,
20 constraint chk_customers_type_fields
21 check (((customer_type = 'individual'::text) AND (first_name IS NOT NULL) AND (last_name IS NOT NULL)) OR
22 ((customer_type = 'business'::text) AND (company_name IS NOT NULL)))
23);
24
25alter table public.customers
26 owner to postgres;
27
28create table public.customer_addresses
29(
30 address_id bigserial
31 primary key,
32 customer_id bigint not null
33 constraint fk_customer_addresses_customer
34 references public.customers,
35 address_type text not null
36 constraint chk_customer_addresses_type
37 check (address_type = ANY
38 (ARRAY ['billing'::text, 'service'::text, 'home'::text, 'office'::text, 'other'::text])),
39 country text not null,
40 city text not null,
41 street text not null,
42 postal_code text,
43 latitude numeric,
44 longitude numeric,
45 is_primary boolean default false not null,
46 created_at timestamp default CURRENT_TIMESTAMP not null
47);
48
49alter table public.customer_addresses
50 owner to postgres;
51
52create table public.billing_cycles
53(
54 billing_cycle_id bigserial
55 primary key,
56 cycle_name text not null
57 unique,
58 day_of_month integer not null
59 constraint chk_billing_cycles_day
60 check ((day_of_month >= 1) AND (day_of_month <= 31)),
61 grace_days integer default 0 not null
62 constraint chk_billing_cycles_grace
63 check (grace_days >= 0),
64 is_active boolean default true not null,
65 created_at timestamp default CURRENT_TIMESTAMP not null
66);
67
68alter table public.billing_cycles
69 owner to postgres;
70
71create table public.accounts
72(
73 account_id bigserial
74 primary key,
75 customer_id bigint not null
76 constraint fk_accounts_customer
77 references public.customers,
78 account_number text not null
79 unique,
80 account_status text default 'active'::text not null
81 constraint check_status
82 check (account_status = ANY (ARRAY ['active'::text, 'suspended'::text, 'closed'::text])),
83 credit_limit numeric default 0.00 not null,
84 current_balance numeric default 0.00 not null
85 constraint balance_above_zero
86 check (current_balance >= (0)::numeric),
87 billing_cycle_id bigint
88 constraint fk_accounts_billing_cycle
89 references public.billing_cycles,
90 created_at timestamp default CURRENT_TIMESTAMP not null,
91 updated_at timestamp default CURRENT_TIMESTAMP not null
92);
93
94alter table public.accounts
95 owner to postgres;
96
97create table public.contracts
98(
99 contract_id bigserial
100 primary key,
101 customer_id bigint not null
102 constraint fk_contracts_customer
103 references public.customers,
104 account_id bigint not null
105 constraint fk_contracts_account
106 references public.accounts,
107 contract_type text not null,
108 contract_number text not null
109 unique,
110 start_date date not null,
111 end_date date,
112 auto_renew boolean default false not null,
113 status text default 'active'::text not null,
114 signed_at timestamp,
115 constraint chk_contracts_dates
116 check ((end_date IS NULL) OR (end_date >= start_date))
117);
118
119alter table public.contracts
120 owner to postgres;
121
122create table public.departments
123(
124 department_id bigserial
125 primary key,
126 department_name text not null
127 unique,
128 location text,
129 status text default 'active'::text not null
130);
131
132alter table public.departments
133 owner to postgres;
134
135create table public.employee_roles
136(
137 role_id bigserial
138 primary key,
139 role_name text not null
140 unique,
141 role_description text not null,
142 access_level integer default 1 not null
143 constraint chk_employee_roles_access
144 check (access_level >= 1),
145 is_active boolean default true not null
146);
147
148alter table public.employee_roles
149 owner to postgres;
150
151create table public.employees
152(
153 employee_id bigserial
154 primary key,
155 department_id bigint not null
156 constraint fk_employees_department
157 references public.departments,
158 role_id bigint not null
159 constraint fk_employees_role
160 references public.employee_roles,
161 first_name text not null,
162 last_name text not null,
163 email text not null
164 unique,
165 phone text,
166 hire_date date not null,
167 employment_status text default 'active'::text not null,
168 manager_id bigint
169 constraint fk_employees_manager
170 references public.employees
171);
172
173alter table public.employees
174 owner to postgres;
175
176create table public.devices
177(
178 device_id bigserial
179 primary key,
180 imei text
181 unique,
182 serial_number text
183 unique,
184 manufacturer text,
185 model text,
186 device_type text
187 constraint check_device_type
188 check (device_type = ANY (ARRAY ['smartphone'::text, 'tablet'::text, 'modem'::text, 'iot_device'::text])),
189 purchase_date date
190);
191
192alter table public.devices
193 owner to postgres;
194
195create table public.sim_cards
196(
197 sim_id bigserial
198 primary key,
199 iccid text not null
200 unique,
201 imsi text not null
202 unique,
203 msisdn text not null
204 unique,
205 sim_type text not null
206 constraint check_type
207 check (sim_type = ANY (ARRAY ['nano'::text, 'micro'::text, 'eSIM'::text])),
208 pin_code text,
209 puk_code text,
210 status text default 'available'::text not null
211 constraint check_name
212 check (status = ANY (ARRAY ['available'::text, 'active'::text, 'suspended'::text, 'deactivated'::text])),
213 issued_at timestamp
214);
215
216alter table public.sim_cards
217 owner to postgres;
218
219create table public.services
220(
221 service_id bigserial
222 primary key,
223 service_name text not null
224 unique,
225 service_category text not null,
226 description text,
227 is_active boolean default true not null,
228 created_at timestamp default CURRENT_TIMESTAMP not null
229);
230
231alter table public.services
232 owner to postgres;
233
234create table public.products
235(
236 product_id bigserial
237 primary key,
238 product_name text not null,
239 product_code text not null
240 unique,
241 product_type text not null,
242 description text,
243 status text default 'active'::text not null,
244 created_at timestamp default CURRENT_TIMESTAMP not null
245);
246
247alter table public.products
248 owner to postgres;
249
250create table public.overage_policies
251(
252 overage_policy_id bigserial
253 primary key,
254 policy_name text not null
255 unique,
256 voice_rate_per_min numeric default 0.0000 not null,
257 sms_rate numeric default 0.0000 not null,
258 data_rate_per_mb numeric default 0.0000 not null,
259 throttle_after_limit boolean default false not null,
260 fair_use_limit_mb numeric,
261 status text default 'active'::text not null
262);
263
264alter table public.overage_policies
265 owner to postgres;
266
267create table public.plans
268(
269 plan_id bigserial
270 primary key,
271 product_id bigint not null
272 constraint fk_plans_product
273 references public.products,
274 plan_name text not null,
275 monthly_fee numeric default 0.00 not null
276 constraint check_monthly_fee
277 check (monthly_fee >= (0)::numeric),
278 overage_policy_id bigint
279 constraint fk_plans_overage_policy
280 references public.overage_policies,
281 contract_term_months integer default 0 not null
282 constraint chk_plans_term
283 check (contract_term_months >= 0),
284 status text default 'active'::text not null
285);
286
287alter table public.plans
288 owner to postgres;
289
290create table public.subscriptions
291(
292 subscription_id bigserial
293 primary key,
294 account_id bigint not null
295 constraint fk_subscriptions_account
296 references public.accounts,
297 plan_id bigint not null
298 constraint fk_subscriptions_plan
299 references public.plans,
300 contract_id bigint
301 constraint fk_subscriptions_contract
302 references public.contracts,
303 subscription_number text not null
304 unique,
305 activation_date date not null,
306 end_date date,
307 status text default 'active'::text not null
308 constraint check_status
309 check (status = ANY
310 (ARRAY ['active'::text, 'suspended'::text, 'cancelled'::text, 'expired'::text, 'pending'::text])),
311 billing_start_date date not null,
312 constraint chk_subscriptions_dates
313 check ((end_date IS NULL) OR (end_date >= activation_date))
314);
315
316alter table public.subscriptions
317 owner to postgres;
318
319create table public.addons
320(
321 addon_id bigserial
322 primary key,
323 addon_name text not null,
324 addon_type text not null,
325 price numeric default 0.00 not null,
326 allowance_value numeric,
327 allowance_unit text,
328 is_recurring boolean default true not null,
329 status text default 'active'::text not null
330);
331
332alter table public.addons
333 owner to postgres;
334
335create table public.subscription_addons
336(
337 subscription_addon_id bigserial
338 primary key,
339 subscription_id bigint not null
340 constraint fk_subscription_addons_subscription
341 references public.subscriptions,
342 addon_id bigint not null
343 constraint fk_subscription_addons_addon
344 references public.addons,
345 activation_date date not null,
346 deactivation_date date,
347 status text default 'active'::text not null
348 constraint check_addon_status
349 check (status = ANY (ARRAY ['active'::text, 'deactivated'::text])),
350 price_at_activation numeric default 0.00 not null,
351 constraint chk_subscription_addons_dates
352 check ((deactivation_date IS NULL) OR (deactivation_date >= activation_date))
353);
354
355alter table public.subscription_addons
356 owner to postgres;
357
358create table public.subscription_status_history
359(
360 status_history_id bigserial
361 primary key,
362 subscription_id bigint not null
363 constraint fk_sub_status_history_subscription
364 references public.subscriptions,
365 old_status text,
366 new_status text not null,
367 changed_at timestamp default CURRENT_TIMESTAMP not null,
368 changed_by_employee_id bigint
369 constraint fk_sub_status_history_employee
370 references public.employees,
371 reason text
372);
373
374alter table public.subscription_status_history
375 owner to postgres;
376
377create index idx_subs_id_subs_stat_hist
378 on public.subscription_status_history (subscription_id);
379
380create table public.device_assignments
381(
382 device_assignment_id bigserial
383 primary key,
384 device_id bigint not null
385 constraint fk_device_assignments_device
386 references public.devices,
387 subscription_id bigint not null
388 constraint fk_device_assignments_subscription
389 references public.subscriptions,
390 assigned_from timestamp not null,
391 assigned_to timestamp,
392 assignment_status text default 'active'::text not null
393 constraint check_assignment_status
394 check (assignment_status = ANY (ARRAY ['active'::text, 'returned'::text, 'lost'::text, 'inactive'::text])),
395 notes text,
396 constraint chk_device_assignments_times
397 check ((assigned_to IS NULL) OR (assigned_to >= assigned_from))
398);
399
400alter table public.device_assignments
401 owner to postgres;
402
403create index idx_device_assignments_subscription_id
404 on public.device_assignments (subscription_id);
405
406create table public.network_technologies
407(
408 technology_id bigserial
409 primary key,
410 technology_name text not null
411 unique,
412 generation text not null,
413 description text,
414 status text default 'active'::text not null
415);
416
417alter table public.network_technologies
418 owner to postgres;
419
420create table public.network_sites
421(
422 site_id bigserial
423 primary key,
424 site_code text not null
425 unique,
426 site_name text not null,
427 address text,
428 region text,
429 latitude numeric,
430 longitude numeric,
431 site_type text,
432 status text default 'active'::text not null,
433 opened_at timestamp
434);
435
436alter table public.network_sites
437 owner to postgres;
438
439create table public.cell_towers
440(
441 tower_id bigserial
442 primary key,
443 site_id bigint not null
444 constraint fk_cell_towers_site
445 references public.network_sites,
446 tower_code text not null
447 unique,
448 height_meters numeric,
449 ownership_type text,
450 installation_date date,
451 status text default 'active'::text not null,
452 vendor_name text
453);
454
455alter table public.cell_towers
456 owner to postgres;
457
458create table public.tower_sectors
459(
460 sector_id bigserial
461 primary key,
462 tower_id bigint not null
463 constraint fk_tower_sectors_tower
464 references public.cell_towers,
465 sector_label text not null,
466 azimuth integer,
467 beamwidth integer
468 constraint chk_tower_sectors_beamwidth
469 check ((beamwidth >= 1) AND (beamwidth <= 360)),
470 frequency_band text,
471 technology_id bigint not null
472 constraint fk_tower_sectors_technology
473 references public.network_technologies,
474 status text default 'active'::text not null,
475 constraint uq_tower_sector_label
476 unique (tower_id, sector_label)
477);
478
479alter table public.tower_sectors
480 owner to postgres;
481
482create table public.coverage_zones
483(
484 coverage_zone_id bigserial
485 primary key,
486 sector_id bigint not null
487 constraint fk_coverage_zones_sector
488 references public.tower_sectors,
489 coverage_type text not null,
490 zone_description text,
491 signal_quality_score numeric
492 constraint chk_signal_quality_score
493 check ((signal_quality_score IS NULL) OR
494 ((signal_quality_score >= (0)::numeric) AND (signal_quality_score <= (100)::numeric))),
495 last_measured_at timestamp
496);
497
498alter table public.coverage_zones
499 owner to postgres;
500
501create table public.roaming_partners
502(
503 roaming_partner_id bigserial
504 primary key,
505 partner_name text not null,
506 country text not null,
507 mcc text,
508 mnc text,
509 agreement_start date,
510 agreement_end date,
511 status text default 'active'::text not null,
512 constraint chk_roaming_partners_dates
513 check ((agreement_end IS NULL) OR (agreement_end >= agreement_start))
514);
515
516alter table public.roaming_partners
517 owner to postgres;
518
519create table public.network_alarms
520(
521 alarm_id bigserial
522 primary key,
523 tower_id bigint
524 constraint fk_network_alarms_tower
525 references public.cell_towers,
526 sector_id bigint
527 constraint fk_network_alarms_sector
528 references public.tower_sectors,
529 alarm_type text not null
530 constraint chk_network_alarms_type
531 check (alarm_type = ANY
532 (ARRAY ['antenna_misalignment'::text, 'signal_degradation'::text, 'battery_low'::text, 'software_error'::text, 'high_temperature'::text, 'hardware_fault'::text, 'link_down'::text, 'backhaul_congestion'::text, 'power_failure'::text, 'high_cpu'::text])),
533 severity text not null
534 constraint chk_network_alarms_severity
535 check (severity = ANY (ARRAY ['warning'::text, 'minor'::text, 'major'::text, 'critical'::text])),
536 raised_at timestamp not null,
537 cleared_at timestamp,
538 status text default 'open'::text not null
539 constraint chk_network_alarms_status
540 check (status = ANY (ARRAY ['open'::text, 'cleared'::text])),
541 description text,
542 constraint chk_network_alarms_times
543 check ((cleared_at IS NULL) OR (cleared_at >= raised_at)),
544 constraint chk_alarm_exactly_one_location
545 check (((tower_id IS NOT NULL) AND (sector_id IS NULL)) OR ((tower_id IS NULL) AND (sector_id IS NOT NULL)))
546);
547
548alter table public.network_alarms
549 owner to postgres;
550
551create table public.outages
552(
553 outage_id bigserial
554 primary key,
555 site_id bigint not null
556 constraint fk_outages_site
557 references public.network_sites,
558 alarm_id bigint
559 constraint fk_outages_alarm
560 references public.network_alarms,
561 outage_type text not null,
562 start_time timestamp not null,
563 end_time timestamp,
564 status text default 'open'::text not null,
565 root_cause text,
566 constraint chk_outages_times
567 check ((end_time IS NULL) OR (end_time >= start_time))
568);
569
570alter table public.outages
571 owner to postgres;
572
573create table public.usage_cdr_calls
574(
575 call_cdr_id bigserial
576 primary key,
577 subscription_id bigint not null
578 constraint fk_cdr_calls_subscription
579 references public.subscriptions,
580 originating_msisdn text not null,
581 destination_msisdn text not null,
582 sector_id bigint
583 constraint fk_cdr_calls_sector
584 references public.tower_sectors,
585 roaming_partner_id bigint
586 constraint fk_cdr_calls_roaming_partner
587 references public.roaming_partners,
588 event_start_time timestamp not null,
589 event_end_time timestamp not null,
590 duration_seconds integer not null
591 constraint chk_cdr_calls_duration
592 check (duration_seconds >= 0),
593 call_type text not null,
594 direction text not null
595 constraint chk_cdr_calls_direction
596 check (direction = ANY (ARRAY ['outbound'::text, 'inbound'::text])),
597 charge_amount numeric default 0.0000 not null,
598 fraud_score numeric default 0.00,
599 constraint chk_cdr_calls_times
600 check (event_end_time >= event_start_time)
601);
602
603alter table public.usage_cdr_calls
604 owner to postgres;
605
606create index idx_subs_id_cdr_calls
607 on public.usage_cdr_calls (subscription_id);
608
609create table public.usage_cdr_sms
610(
611 sms_cdr_id bigserial
612 primary key,
613 subscription_id bigint not null
614 constraint fk_cdr_sms_subscription
615 references public.subscriptions,
616 source_msisdn text not null,
617 destination_msisdn text not null,
618 sector_id bigint
619 constraint fk_cdr_sms_sector
620 references public.tower_sectors,
621 roaming_partner_id bigint
622 constraint fk_cdr_sms_roaming_partner
623 references public.roaming_partners,
624 event_time timestamp not null,
625 sms_type text not null,
626 direction text not null
627 constraint chk_cdr_sms_direction
628 check (direction = ANY (ARRAY ['outbound'::text, 'inbound'::text])),
629 charge_amount numeric default 0.0000 not null
630);
631
632alter table public.usage_cdr_sms
633 owner to postgres;
634
635create index idx_subscriptionid_usage_cdr_sms
636 on public.usage_cdr_sms (subscription_id);
637
638create table public.usage_cdr_data
639(
640 data_cdr_id bigserial
641 primary key,
642 subscription_id bigint not null
643 constraint fk_cdr_data_subscription
644 references public.subscriptions,
645 sector_id bigint
646 constraint fk_cdr_data_sector
647 references public.tower_sectors,
648 roaming_partner_id bigint
649 constraint fk_cdr_data_roaming_partner
650 references public.roaming_partners,
651 session_start timestamp not null,
652 session_end timestamp not null,
653 data_used_mb numeric default 0.0000 not null,
654 apn text,
655 ip_address inet,
656 charge_amount numeric default 0.0000 not null,
657 constraint chk_cdr_data_times
658 check (session_end >= session_start)
659);
660
661alter table public.usage_cdr_data
662 owner to postgres;
663
664create index idx_cdr_data_subscriptionid
665 on public.usage_cdr_data (subscription_id);
666
667create index idx_subscriptionid_usage_cdr_data
668 on public.usage_cdr_data (subscription_id);
669
670create table public.usage_aggregates_daily
671(
672 usage_daily_id bigserial
673 primary key,
674 subscription_id bigint not null
675 constraint fk_usage_daily_subscription
676 references public.subscriptions,
677 usage_date date not null,
678 total_call_seconds bigint default 0 not null,
679 total_sms_count integer default 0 not null,
680 total_data_mb numeric default 0.0000 not null,
681 total_charge_amount numeric default 0.0000 not null,
682 generated_at timestamp default CURRENT_TIMESTAMP not null,
683 constraint uq_usage_daily_subscription_date
684 unique (subscription_id, usage_date)
685);
686
687alter table public.usage_aggregates_daily
688 owner to postgres;
689
690create table public.invoices
691(
692 invoice_id bigserial
693 primary key,
694 account_id bigint not null
695 constraint fk_invoices_account
696 references public.accounts,
697 invoice_number text not null
698 unique,
699 billing_period_start date not null,
700 billing_period_end date not null,
701 issue_date date not null,
702 due_date date not null,
703 total_amount numeric default 0.00 not null,
704 tax_amount numeric default 0.00 not null,
705 discount_amount numeric default 0.00 not null,
706 status text default 'issued'::text not null,
707 constraint chk_invoices_period
708 check (billing_period_end >= billing_period_start),
709 constraint chk_invoices_due_date
710 check (due_date >= issue_date)
711);
712
713alter table public.invoices
714 owner to postgres;
715
716create index idx_invoice_accountid
717 on public.invoices (account_id);
718
719create table public.invoice_items
720(
721 invoice_item_id bigserial
722 primary key,
723 invoice_id bigint not null
724 constraint fk_invoice_items_invoice
725 references public.invoices,
726 subscription_id bigint
727 constraint fk_invoice_items_subscription
728 references public.subscriptions,
729 item_type text not null,
730 description text,
731 quantity numeric default 1 not null,
732 unit_price numeric default 0.00 not null,
733 line_amount numeric default 0.00 not null,
734 tax_rate numeric default 0.00 not null
735);
736
737alter table public.invoice_items
738 owner to postgres;
739
740create index idx_inv_items_invoiceid
741 on public.invoice_items (invoice_id);
742
743create table public.payment_methods
744(
745 payment_method_id bigserial
746 primary key,
747 method_name text not null
748 unique,
749 provider_name text,
750 is_online boolean default false not null,
751 status text default 'active'::text not null
752);
753
754alter table public.payment_methods
755 owner to postgres;
756
757create table public.payments
758(
759 payment_id bigserial
760 primary key,
761 account_id bigint not null
762 constraint fk_payments_account
763 references public.accounts,
764 invoice_id bigint
765 constraint fk_payments_invoice
766 references public.invoices,
767 payment_method_id bigint
768 constraint fk_payments_method
769 references public.payment_methods,
770 payment_date timestamp not null,
771 amount numeric not null
772 constraint chk_payments_amount
773 check (amount >= (0)::numeric),
774 reference_number text,
775 status text default 'completed'::text not null
776);
777
778alter table public.payments
779 owner to postgres;
780
781create index idx_payments_invoiceid
782 on public.payments (invoice_id);
783
784create index idx_accountid_payments
785 on public.payments (account_id);
786
787create table public.billing_runs
788(
789 billing_run_id bigserial
790 primary key,
791 billing_cycle_id bigint not null
792 constraint fk_billing_runs_cycle
793 references public.billing_cycles,
794 period_start date not null,
795 period_end date not null,
796 run_started_at timestamp not null,
797 run_finished_at timestamp,
798 status text default 'running'::text not null,
799 generated_invoices_count integer default 0 not null,
800 constraint chk_billing_runs_period
801 check (period_end >= period_start),
802 constraint chk_billing_runs_times
803 check ((run_finished_at IS NULL) OR (run_finished_at >= run_started_at))
804);
805
806alter table public.billing_runs
807 owner to postgres;
808
809create table public.crm_tickets
810(
811 ticket_id bigserial
812 primary key,
813 customer_id bigint not null
814 constraint fk_crm_tickets_customer
815 references public.customers,
816 account_id bigint
817 constraint fk_crm_tickets_account
818 references public.accounts,
819 subscription_id bigint
820 constraint fk_crm_tickets_subscription
821 references public.subscriptions,
822 assigned_employee_id bigint not null
823 constraint fk_crm_tickets_employee
824 references public.employees,
825 ticket_type text not null,
826 subject text not null,
827 description text,
828 priority text default 'medium'::text not null
829 constraint check_priority
830 check (priority = ANY (ARRAY ['low'::text, 'medium'::text, 'high'::text, 'critical'::text])),
831 status text default 'open'::text not null
832 constraint check_name
833 check (status = ANY
834 (ARRAY ['open'::text, 'in_progress'::text, 'escalated'::text, 'resolved'::text, 'closed'::text])),
835 created_at timestamp default CURRENT_TIMESTAMP not null,
836 closed_at timestamp,
837 parent_ticket_id bigint
838 constraint fk_crm_tickets_parent
839 references public.crm_tickets,
840 constraint chk_crm_tickets_times
841 check ((closed_at IS NULL) OR (closed_at >= created_at))
842);
843
844alter table public.crm_tickets
845 owner to postgres;
846
847create table public.crm_interactions
848(
849 interaction_id bigserial
850 primary key,
851 ticket_id bigint not null
852 constraint fk_crm_interactions_ticket
853 references public.crm_tickets,
854 employee_id bigint
855 constraint fk_crm_interactions_employee
856 references public.employees,
857 interaction_type text not null,
858 channel text not null,
859 interaction_time timestamp default CURRENT_TIMESTAMP not null,
860 notes text,
861 old_status text,
862 new_status text
863);
864
865alter table public.crm_interactions
866 owner to postgres;
867
868create index idx_ticketid_crm_i
869 on public.crm_interactions (ticket_id);
870
871create table public.employee_assignments
872(
873 assignment_id bigserial
874 primary key,
875 employee_id bigint not null
876 constraint fk_employee_assignments_employee
877 references public.employees,
878 ticket_id bigint
879 constraint fk_employee_assignments_ticket
880 references public.crm_tickets,
881 outage_id bigint
882 constraint fk_employee_assignments_outage
883 references public.outages,
884 assignment_type text not null,
885 start_time timestamp not null,
886 end_time timestamp,
887 status text default 'assigned'::text not null,
888 constraint chk_employee_assignments_times
889 check ((end_time IS NULL) OR (end_time >= start_time))
890);
891
892alter table public.employee_assignments
893 owner to postgres;
894
895create table public.sim_card_subscription_history
896(
897 sim_card_subscription_history_id bigint default nextval('sim_card_subscription_history_sim_card_subscription_history_seq'::regclass) not null
898 primary key,
899 sim_id bigint
900 constraint fk_sim_history_sim
901 references public.sim_cards,
902 subscription_id bigint not null
903 constraint fk_sim_history_subscription
904 references public.subscriptions,
905 start_date timestamp not null,
906 end_date timestamp,
907 constraint chk_sim_history_dates
908 check ((end_date IS NULL) OR (end_date >= start_date))
909);
910
911alter table public.sim_card_subscription_history
912 owner to postgres;
913
914create index idx_subsid_sim_c_subs_hist
915 on public.sim_card_subscription_history (subscription_id);
916
917create table public.plan_services
918(
919 plan_service_id bigserial
920 primary key,
921 plan_id bigint not null
922 constraint fk_plan_services_plan
923 references public.plans,
924 service_id bigint not null
925 constraint fk_plan_services_service
926 references public.services,
927 allowance_value numeric,
928 allowance_unit text,
929 is_unlimited boolean default false not null,
930 is_included boolean default true not null,
931 status text default 'active'::text not null,
932 constraint uq_plan_services_plan_service
933 unique (plan_id, service_id)
934);
935
936alter table public.plan_services
937 owner to postgres;
938