GyXLaR6y

1BEGIN TRANSACTION;
2
3DROP TRIGGER check_update ON form25 . card;
4
5CREATE TABLE demographics.patient_hcp_data
6(
7    patient_id bigint,
8    health_care_provider_id bigint,
9    created_by_id bigint NOT NULL,
10    modified_by_id bigint,
11    deleted_by_id bigint,
12    created_at timestamp NOT NULL,
13    modified_at timestamp,
14    deleted_at timestamp,
15    paper_card_no text,
16    notes text,
17    CONSTRAINT patient_hcp_data_pkey PRIMARY KEY ( patient_id, health_care_provider_id ),
18    CONSTRAINT unique_patient_hcp_data_paper_cardno UNIQUE ( health_care_provider_id, paper_card_no ),
19    CONSTRAINT patient_hcp_data_created_by_fkey FOREIGN KEY ( created_by_id ) REFERENCES demographics . party ( id ),
20    CONSTRAINT patient_hcp_data_deleted_by_fkey FOREIGN KEY ( deleted_by_id ) REFERENCES demographics . party ( id ) ON DELETE CASCADE,
21    CONSTRAINT patient_hcp_data_healthcare_provider_fkey FOREIGN KEY ( health_care_provider_id ) REFERENCES demographics . party ( id ),
22    CONSTRAINT patient_hcp_data_modified_by_fkey FOREIGN KEY ( modified_by_id ) REFERENCES demographics . party ( id ),
23    CONSTRAINT patient_hcp_data_patient_fkey FOREIGN KEY ( patient_id ) REFERENCES demographics . party ( id ) ON DELETE CASCADE
24);
25
26CREATE SCHEMA report
27    AUTHORIZATION postgres;
28
29CREATE MATERIALIZED VIEW report.work_progress_report_payment AS
30     SELECT x.employee_id,
31    x.healthcare_provider_id,
32    x.date,
33    x.value,
34    x.service_type_id,
35    x.service_secondary_group_id,
36    sum((x.amount + x.compensation_size)) AS sum,
37    count(*) AS count
38   FROM ( SELECT ps.employee_id,
39            ps.healthcare_provider_id,
40            (ps.date)::date AS date,
41            ps.compensation_size,
42            ls.value,
43            s.service_type_id,
44            s.service_secondary_group_id,
45            LEAST(ps.cost, ( SELECT sum(pps.amount) AS sum
46                   FROM payment.paid_person_service pps
47                  WHERE ((pps.person_service_id = ps.id) AND (pps.is_deleted = false)))) AS amount
48           FROM ((payment.person_service ps
49             JOIN payment.service s ON ((ps.service_id = s.id)))
50             JOIN core.localized_string ls ON (((ls.service_id = ps.service_id) AND ((ls.locale)::text = 'lt'::text))))
51          WHERE ((ps.deleted_by_id IS NULL) AND (ps.date > '2018-01-01 00:00:00'::timestamp without time zone) AND ((ps.cost + ps.compensation_size) > (0)::numeric))) x
52  GROUP BY x.employee_id, x.healthcare_provider_id, x.date, x.value, x.service_type_id, x.service_secondary_group_id WITH DATA;
53
54CREATE MATERIALIZED VIEW payment.debtor_dynamic_1_vw AS
55     SELECT 0 AS "DebtorID",
56    '-infinity'::timestamp without time zone AS "DebtorDate",
57    sum((ps.cost - COALESCE(( SELECT sum(pps.amount) AS sum
58           FROM payment.paid_person_service pps
59          WHERE ((pps.is_deleted = false) AND (pps.person_service_id = ps.id))), (0)::numeric))) AS "DebtorDebt",
60    ps.cost_currency AS "DebtorCurrency",
61    false AS "DebtorIsPaid",
62    true AS "DebtorIsRelevant",
63    NULL::timestamp without time zone AS "DebtorPaidAt",
64    patient.id AS "PatientID",
65    patient.firstname AS "PatientFirstName",
66    patient.lastname AS "PatientLastName",
67    patient.code AS "PatientCode",
68    patient.phone AS "PatientPhone",
69    patient.mobile_phone AS "PatientMobilePhone",
70    hcp.id AS "HcpID",
71    hcp.name AS "HcpName",
72    NULL::timestamp without time zone AS "LastPhoneCallDate",
73    NULL::timestamp without time zone AS "LastNormalMailDate",
74    NULL::timestamp without time zone AS "LastStrictMailDate"
75   FROM (((payment.person_service ps
76     JOIN payment.service serv ON ((ps.service_id = serv.id)))
77     JOIN demographics.party patient ON ((patient.id = ps.receiver_id)))
78     JOIN demographics.party hcp ON (((hcp.id = ps.healthcare_provider_id) AND ((hcp.type_name)::text = 'health-care-provider'::text))))
79  WHERE ((ps.deleted_at IS NULL) AND (((ps.status)::text = 'provided'::text) OR serv.is_payment_required))
80  GROUP BY ps.cost_currency, patient.id, hcp.id WITH DATA;
81
82CREATE MATERIALIZED VIEW report.work_progress_report_subscriptions_previous AS
83     SELECT work_progress_report_subscriptions.doctor_id,
84    work_progress_report_subscriptions.health_care_provider_id,
85    work_progress_report_subscriptions.year_1,
86    work_progress_report_subscriptions.year_1_7,
87    work_progress_report_subscriptions.year_8_17,
88    work_progress_report_subscriptions.year_18_34,
89    work_progress_report_subscriptions.year_35_49,
90    work_progress_report_subscriptions.year_50_65,
91    work_progress_report_subscriptions.year_65,
92    work_progress_report_subscriptions.miestelis,
93    work_progress_report_subscriptions.kaimas,
94    work_progress_report_subscriptions.total
95   FROM report.work_progress_report_subscriptions
96  WHERE (date_part('day'::text, now()) = (1)::double precision) WITH DATA;
97
98CREATE MATERIALIZED VIEW report.work_progress_report_subscriptions AS
99     SELECT s.doctor_id,
100    s.health_care_provider_id,
101    sum(((date_part('year'::text, age(p.birth_date)) < (1)::double precision))::integer) AS year_1,
102    sum((((date_part('year'::text, age(p.birth_date)) >= (1)::double precision) AND (date_part('year'::text, age(p.birth_date)) <= (7)::double precision)))::integer) AS year_1_7,
103    sum((((date_part('year'::text, age(p.birth_date)) >= (8)::double precision) AND (date_part('year'::text, age(p.birth_date)) <= (17)::double precision)))::integer) AS year_8_17,
104    sum((((date_part('year'::text, age(p.birth_date)) >= (18)::double precision) AND (date_part('year'::text, age(p.birth_date)) <= (34)::double precision)))::integer) AS year_18_34,
105    sum((((date_part('year'::text, age(p.birth_date)) >= (35)::double precision) AND (date_part('year'::text, age(p.birth_date)) <= (49)::double precision)))::integer) AS year_35_49,
106    sum((((date_part('year'::text, age(p.birth_date)) >= (50)::double precision) AND (date_part('year'::text, age(p.birth_date)) <= (65)::double precision)))::integer) AS year_50_65,
107    sum(((date_part('year'::text, age(p.birth_date)) > (65)::double precision))::integer) AS year_65,
108    sum((((p.address_settlement_type)::text = 'A'::text))::integer) AS miestelis,
109    sum((((p.address_settlement_type)::text = 'K'::text))::integer) AS kaimas,
110    count(*) AS total
111   FROM (demographics.subscription s
112     JOIN demographics.party p ON ((s.patient_id = p.id)))
113  WHERE ((s.type = ANY (ARRAY[1, 2])) AND (s.doctor_id > 0))
114  GROUP BY s.health_care_provider_id, s.doctor_id WITH DATA;
115
116CREATE INDEX person_service_hcp_idx
117    ON payment.person_service
118    ( healthcare_provider_id );
119
120CREATE TABLE payment.payment_document
121(
122    id bigint,
123    date date NOT NULL,
124    total numeric(10,2) NOT NULL,
125    recipient_id bigint NOT NULL,
126    organisation_id bigint NOT NULL,
127    issued_to_id bigint,
128    service_payment_id bigint,
129    series character varying(255) NOT NULL,
130    number integer NOT NULL,
131    recipient_name character varying(255) NOT NULL,
132    recipient_code character varying(255),
133    recipient_address character varying(255),
134    recipient_contacts character varying(255),
135    recipient_vat_code character varying(255),
136    issued_to_name character varying(255),
137    pay_up_to date,
138    type_name character varying(255) NOT NULL,
139    created_by_id bigint NOT NULL,
140    created_by_name character varying(255) NOT NULL,
141    created_at timestamp NOT NULL,
142    created_by_position character varying(255),
143    deleted_by_id bigint,
144    deleted_at timestamp,
145    notes text,
146    is_promissory_note_created boolean,
147    CONSTRAINT payment_document_pk PRIMARY KEY ( id ),
148    CONSTRAINT pd_created_by_fk FOREIGN KEY ( created_by_id ) REFERENCES demographics . party ( id ),
149    CONSTRAINT pd_deleted_by_fk FOREIGN KEY ( deleted_by_id ) REFERENCES demographics . party ( id ),
150    CONSTRAINT pd_organisation_fk FOREIGN KEY ( organisation_id ) REFERENCES demographics . party ( id ),
151    CONSTRAINT pd_recipient_fk FOREIGN KEY ( recipient_id ) REFERENCES demographics . party ( id )
152);
153
154CREATE INDEX fki_pd_service_payment_fk
155    ON payment.payment_document
156    ( service_payment_id );
157
158CREATE TABLE payment.paid_person_service
159(
160    id bigint,
161    service_payment_id bigint,
162    invoice_id bigint,
163    person_service_id bigint NOT NULL,
164    amount numeric(10,2) NOT NULL,
165    source_id bigint NOT NULL,
166    is_deleted boolean NOT NULL,
167    coupon_id bigint,
168    "type" character varying(255) NOT NULL,
169    CONSTRAINT paid_person_service_pkey PRIMARY KEY ( id ),
170    CONSTRAINT pps_invoice_payment_set CHECK ((service_payment_id > 0) OR (invoice_id > 0)),
171    CONSTRAINT pps_source_coupon_set CHECK (((source_id > 0) AND (coupon_id IS NULL)) OR ((source_id IS NULL) AND (coupon_id > 0))),
172    CONSTRAINT pps_invoice_fk FOREIGN KEY ( invoice_id ) REFERENCES payment . payment_document ( id ),
173    CONSTRAINT pps_person_service_fk FOREIGN KEY ( person_service_id ) REFERENCES payment . person_service ( id ),
174    CONSTRAINT pps_source_fk FOREIGN KEY ( source_id ) REFERENCES demographics . party ( id )
175);
176
177ALTER TABLE payment.paid_person_service CLUSTER ON pps_deleted_idx;
178
179CREATE INDEX pps_person_service_idx
180    ON payment.paid_person_service
181    ( person_service_id );
182
183CREATE INDEX fki_permission_patter_fk
184    ON management.permission
185    ( pattern_id );
186
187CREATE INDEX pd_deleted_by_idx
188    ON payment.payment_document
189    ( deleted_by_id );
190
191CREATE TABLE report.plan
192(
193    id bigint,
194    month smallint,
195    company_id bigint,
196    override_plan_id bigint,
197    subject_id bigint,
198    created_at timestamp,
199    created_by_id bigint,
200    deleted_at timestamp,
201    deleted_by_id bigint,
202    type_name character varying(255) NOT NULL,
203    CONSTRAINT plan_fk PRIMARY KEY ( id ),
204    CONSTRAINT plan_company_fk FOREIGN KEY ( company_id ) REFERENCES management . company ( id ),
205    CONSTRAINT plan_created_by_fk FOREIGN KEY ( created_by_id ) REFERENCES demographics . party ( id ),
206    CONSTRAINT plan_deleted_by_fk FOREIGN KEY ( deleted_by_id ) REFERENCES demographics . party ( id ),
207    CONSTRAINT plan_override_plan_fk FOREIGN KEY ( override_plan_id ) REFERENCES report . plan ( id ),
208    CONSTRAINT plan_subject_fk FOREIGN KEY ( subject_id ) REFERENCES demographics . party ( id )
209);
210
211CREATE INDEX fki_plan_subject_fk
212    ON report.plan
213    ( subject_id );
214
215CREATE INDEX fki_msv_setting_fk
216    ON management.multiple_setting_value
217    ( setting_id );
218
219CREATE UNIQUE INDEX pps_unique_invoice_service_idx
220    ON payment.paid_person_service
221    ( invoice_id , person_service_id )
222    WHERE type::text = 'commision'::text;
223
224CREATE INDEX appointment_doctor_id_idx
225    ON registration.appointment
226    ( doctor_id );
227
228CREATE INDEX fki_permission_environment_fk
229    ON management.permission
230    ( environment_id );
231
232CREATE TABLE demographics.subscription_sync
233(
234    patient_id bigint,
235    synced_at timestamp,
236    "type" smallint,
237    error text,
238    CONSTRAINT subscription_sync_pkey PRIMARY KEY ( patient_id, "type" ),
239    CONSTRAINT subscription_sync_type_chk CHECK (type = ANY (ARRAY[1, 2])),
240    CONSTRAINT subscription_sync_patient_fkey FOREIGN KEY ( patient_id ) REFERENCES demographics . party ( id ) ON DELETE CASCADE
241);
242
243CREATE INDEX subscription_sync_synced_at_idx
244    ON demographics.subscription_sync
245    ( synced_at );
246
247CREATE INDEX pd_type_name_idx
248    ON payment.payment_document
249    ( type_name );
250
251CREATE INDEX appointment_visitor_id_idx
252    ON registration.appointment
253    ( visitor_id );
254
255CREATE INDEX fki_setting_user_fk
256    ON management.setting
257    ( user_id );
258
259CREATE INDEX pps_invoice_idx
260    ON payment.paid_person_service
261    ( invoice_id );
262
263CREATE TABLE demographics."subscription"
264(
265    patient_id bigint,
266    health_care_provider_id bigint,
267    created_by_id bigint,
268    doctor_id bigint,
269    postponed_to_date timestamp,
270    date timestamp,
271    created_at timestamp,
272    subscription_no integer,
273    "type" smallint,
274    status smallint,
275    comments text,
276    CONSTRAINT subscription_pkey PRIMARY KEY ( health_care_provider_id, "type", patient_id ),
277    CONSTRAINT subscription_created_by_fkey FOREIGN KEY ( created_by_id ) REFERENCES demographics . party ( id ),
278    CONSTRAINT subscription_doctor_fkey FOREIGN KEY ( doctor_id ) REFERENCES demographics . party ( id ),
279    CONSTRAINT subscription_healthcare_provider_fkey FOREIGN KEY ( health_care_provider_id ) REFERENCES demographics . party ( id ),
280    CONSTRAINT subscription_patient_fkey FOREIGN KEY ( patient_id ) REFERENCES demographics . party ( id ) ON DELETE CASCADE
281);
282
283CREATE INDEX subscription_status_idx
284    ON demographics."subscription"
285    ( status );
286
287CREATE INDEX person_service_service_id_idx
288    ON payment.person_service
289    ( service_id );
290
291CREATE INDEX party_dik_idx
292    ON demographics.party
293    ( dik )
294    WHERE dik <> 0;
295
296CREATE UNIQUE INDEX pps_unique_type_service_payment_idx
297    ON payment.paid_person_service
298    ( service_payment_id , person_service_id , "type" )
299    WHERE service_payment_id IS NOT NULL;
300
301CREATE INDEX info_screen_patient_visit_idx
302    ON resource_management.info_screen_entry
303    ( patient_visit_id );
304
305CREATE INDEX appointment_identifyer_idx
306    ON registration.appointment
307    ( type_name , health_care_provider_id , doctor_id , "start" , (start + (((duration / 10))::double precision * '00:00:00.000001'::interval)) );
308
309CREATE INDEX pps_service_payment_idx
310    ON payment.paid_person_service
311    ( service_payment_id );
312
313CREATE INDEX fki_pd_organisation_fk
314    ON payment.payment_document
315    ( organisation_id );
316
317CREATE INDEX fki_setting_environment_fk
318    ON management.setting
319    ( environment_id );
320
321CREATE INDEX fki_permission_user_fk
322    ON management.permission
323    ( user_id );
324
325CREATE INDEX person_service_hcp_receiver_idx
326    ON payment.person_service
327    ( healthcare_provider_id , receiver_id );
328
329CREATE INDEX nsp_deleted_by_idx
330    ON payment.service_payment
331    ( deleted_by_id );
332
333CREATE INDEX fki_setting_department_fk
334    ON management.setting
335    ( department_id );
336
337CREATE INDEX fki_message_template_owner_company_id_fkey
338    ON messaging.message_template
339    ( owner_company_id );
340
341CREATE INDEX fki_setting_rule_fk
342    ON management.setting
343    ( rule_id );
344
345CREATE TABLE demographics.subscription_history
346(
347    id bigint,
348    patient_id bigint NOT NULL,
349    synced_at timestamp,
350    error text,
351    CONSTRAINT subscription_history_pkey PRIMARY KEY ( id ),
352    CONSTRAINT subscription_history_patient_fkey FOREIGN KEY ( patient_id ) REFERENCES demographics . party ( id )
353);
354
355CREATE INDEX subscription_history_patient_idx
356    ON demographics.subscription_history
357    ( patient_id );
358
359CREATE INDEX fki_message_template_owner_id_fkey
360    ON messaging.message_template
361    ( owner_id );
362
363CREATE INDEX fki_plan_override_plan_fk
364    ON report.plan
365    ( override_plan_id );
366
367CREATE INDEX subscription_history_sync_at_index
368    ON demographics.subscription_history
369    ( synced_at );
370
371CREATE INDEX fki_nsp_payer_id
372    ON payment.service_payment
373    ( payer_id );
374
375CREATE TABLE report.goal
376(
377    id bigint,
378    plan_id bigint,
379    parameter character varying(255) NOT NULL,
380    code character varying(255) NOT NULL,
381    text character varying(255),
382    "value" character varying(255) NOT NULL,
383    CONSTRAINT goal_pk PRIMARY KEY ( id ),
384    CONSTRAINT goal_plan_fk FOREIGN KEY ( plan_id ) REFERENCES report . plan ( id )
385);
386
387CREATE INDEX fki_goal_plan_fk
388    ON report.goal
389    ( plan_id );
390
391CREATE INDEX fki_pd_recipient_fk
392    ON payment.payment_document
393    ( recipient_id );
394
395CREATE INDEX person_service_test_order_id_idx
396    ON payment.person_service
397    ( test_order_id );
398
399CREATE TABLE demographics.subscription_history_item
400(
401    id bigint,
402    signing_id bigint,
403    subscription_history_id bigint,
404    doctor_id bigint,
405    hcp_id bigint,
406    request_signing_date timestamp,
407    subscription_start_date timestamp,
408    request_signing_end_date timestamp,
409    subscription_end_date timestamp,
410    paid_period_start_date timestamp,
411    paid_period_end_date timestamp,
412    "type" smallint,
413    registration_reason smallint,
414    registration_end_reason smallint,
415    paid_period_change_start_reason smallint,
416    paid_period_change_end_reason smallint,
417    CONSTRAINT subscription_history_item_pkey PRIMARY KEY ( id ),
418    CONSTRAINT subscription_history_item_doctor_fkey FOREIGN KEY ( doctor_id ) REFERENCES demographics . party ( id ),
419    CONSTRAINT subscription_history_item_hcp_fkey FOREIGN KEY ( hcp_id ) REFERENCES demographics . party ( id ),
420    CONSTRAINT subscription_history_item_history_fkey FOREIGN KEY ( subscription_history_id ) REFERENCES demographics . subscription_history ( id )
421);
422
423CREATE INDEX subscription_history_item_history_idx
424    ON demographics.subscription_history_item
425    ( subscription_history_id );
426
427CREATE INDEX fki_plan_company_fk
428    ON report.plan
429    ( company_id );
430
431CREATE INDEX message_template_send_trigger_idx
432    ON messaging.message_template
433    ( send_trigger );
434
435CREATE UNIQUE INDEX unique_pspi_psc_subscription
436    ON demographics."subscription"
437    ( patient_id , "type" )
438    WHERE type > 0;
439
440CREATE INDEX fki_message_template_owner_organisation_id_fkey
441    ON messaging.message_template
442    ( owner_organisation_id );
443
444CREATE TABLE demographics.insurance_history
445(
446    id bigint,
447    patient_id bigint NOT NULL,
448    synced_at timestamp,
449    error text,
450    CONSTRAINT insurance_history_pkey PRIMARY KEY ( id ),
451    CONSTRAINT insurance_history_patient_fkey FOREIGN KEY ( patient_id ) REFERENCES demographics . party ( id )
452);
453
454CREATE INDEX insurance_history_patient_idx
455    ON demographics.insurance_history
456    ( patient_id );
457
458CREATE TABLE demographics.insurance_history_item
459(
460    id bigint,
461    insurance_history_id bigint,
462    date_from timestamp,
463    date_to timestamp,
464    CONSTRAINT insurance_history_item_pkey PRIMARY KEY ( id ),
465    CONSTRAINT insurance_history_item_history_fkey FOREIGN KEY ( insurance_history_id ) REFERENCES demographics . insurance_history ( id )
466);
467
468CREATE INDEX insurance_history_item_history_idx
469    ON demographics.insurance_history_item
470    ( insurance_history_id );
471
472CREATE INDEX pps_deleted_idx
473    ON payment.paid_person_service
474    ( is_deleted );
475
476CREATE INDEX fki_nsp_organisation_fk
477    ON payment.service_payment
478    ( organisation_id );
479
480CREATE INDEX insurance_history_sync_at_index
481    ON demographics.insurance_history
482    ( synced_at );
483
484CREATE UNIQUE INDEX workplace_unique_idx
485    ON demographics.workplace
486    ( employee_id , health_care_provider_id , speciality_id , cabinet_number );
487
488DROP VIEW registration.repdesk_appointments_vw;
489
490DROP VIEW registration.rep_visits_vw;
491
492CREATE VIEW demographics.escroll_view AS
493     SELECT ( SELECT count(us.id) AS count
494           FROM ((management."user" us
495             JOIN management.environment env ON ((us.id = env.user_id)))
496             JOIN demographics.party doctor ON ((us.assigned_person_id = doctor.id)))
497          WHERE (env.is_active AND ((doctor.type_name)::text = 'doctor'::text))) AS "Doctor count",
498    ( SELECT count(DISTINCT patient.id) AS count
499           FROM demographics.party patient
500          WHERE ((patient.type_name)::text = 'patient'::text)) AS "Patients",
501    ( SELECT count(DISTINCT patient.id) AS count
502           FROM (demographics.party patient
503             JOIN demographics.subscription s ON (((patient.id = s.patient_id) AND (s.type = 2))))
504          WHERE (((patient.type_name)::text = 'patient'::text) AND (EXISTS ( SELECT true AS bool
505                   FROM ((management.environment env
506                     JOIN management."user" u ON ((u.id = env.user_id)))
507                     JOIN demographics.party doctor ON (((doctor.id = u.assigned_person_id) AND ((doctor.type_name)::text = 'doctor'::text))))
508                  WHERE ((env.workplace_organisation_id = s.health_care_provider_id) AND env.is_active))))) AS "Foxus patients";
509
510CREATE TABLE payment.debtor
511(
512    id bigint,
513    patient_id bigint NOT NULL,
514    hcp_id bigint NOT NULL,
515    not_relevant_by_id bigint,
516    is_paid boolean NOT NULL,
517    is_relevant boolean NOT NULL,
518    has_debt_changed boolean NOT NULL,
519    date timestamp NOT NULL,
520    paid_at timestamp,
521    not_relevant_from timestamp,
522    debt numeric(10,2) NOT NULL,
523    currency character varying(255) NOT NULL,
524    created_by_id bigint NOT NULL,
525    created_at timestamp NOT NULL,
526    CONSTRAINT debtor_pkey PRIMARY KEY ( id ),
527    CONSTRAINT debtor_created_by_fk FOREIGN KEY ( created_by_id ) REFERENCES demographics . party ( id ),
528    CONSTRAINT debtor_hcp_fk FOREIGN KEY ( hcp_id ) REFERENCES demographics . party ( id ),
529    CONSTRAINT debtor_not_relevenat_by_fk FOREIGN KEY ( not_relevant_by_id ) REFERENCES demographics . party ( id ),
530    CONSTRAINT debtor_patient_fk FOREIGN KEY ( patient_id ) REFERENCES demographics . party ( id )
531);
532
533CREATE TABLE payment.debtor_action
534(
535    id bigint,
536    "type" smallint NOT NULL,
537    debtor_id bigint NOT NULL,
538    is_success boolean NOT NULL,
539    date timestamp NOT NULL,
540    result text,
541    created_by_id bigint NOT NULL,
542    created_from_id bigint NOT NULL,
543    created_at timestamp NOT NULL,
544    CONSTRAINT debtor_action_pkey PRIMARY KEY ( id ),
545    CONSTRAINT debtor_action_created_by_fk FOREIGN KEY ( created_by_id ) REFERENCES demographics . party ( id ),
546    CONSTRAINT debtor_action_created_from_fk FOREIGN KEY ( created_from_id ) REFERENCES demographics . party ( id ),
547    CONSTRAINT debtor_action_debtor_fk FOREIGN KEY ( debtor_id ) REFERENCES payment . debtor ( id )
548);
549
550CREATE VIEW payment.debtor_dynamic_vw AS
551     SELECT debtor.id AS "DebtorID",
552    debtor.date AS "DebtorDate",
553    debtor.debt AS "DebtorDebt",
554    debtor.currency AS "DebtorCurrency",
555    debtor.is_paid AS "DebtorIsPaid",
556    debtor.is_relevant AS "DebtorIsRelevant",
557    debtor.paid_at AS "DebtorPaidAt",
558    patient.id AS "PatientID",
559    patient.firstname AS "PatientFirstName",
560    patient.lastname AS "PatientLastName",
561    patient.code AS "PatientCode",
562    patient.phone AS "PatientPhone",
563    patient.mobile_phone AS "PatientMobilePhone",
564    hcp.id AS "HcpID",
565    hcp.name AS "HcpName",
566    max(phonecall.date) AS "LastPhoneCallDate",
567    max(normalmail.date) AS "LastNormalMailDate",
568    max(strictmail.date) AS "LastStrictMailDate"
569   FROM (((((payment.debtor debtor
570     JOIN demographics.party patient ON ((debtor.patient_id = patient.id)))
571     JOIN demographics.party hcp ON ((debtor.hcp_id = hcp.id)))
572     LEFT JOIN payment.debtor_action phonecall ON (((phonecall.debtor_id = debtor.id) AND phonecall.is_success AND (phonecall.type = 0))))
573     LEFT JOIN payment.debtor_action normalmail ON (((normalmail.debtor_id = debtor.id) AND (normalmail.type = 1))))
574     LEFT JOIN payment.debtor_action strictmail ON (((strictmail.debtor_id = debtor.id) AND (strictmail.type = 2))))
575  WHERE ((debtor.is_paid = false) AND (debtor.is_relevant = true))
576  GROUP BY debtor.id, patient.id, hcp.id
577UNION
578 SELECT debtor_dynamic_1_vw."DebtorID",
579    debtor_dynamic_1_vw."DebtorDate",
580    debtor_dynamic_1_vw."DebtorDebt",
581    debtor_dynamic_1_vw."DebtorCurrency",
582    debtor_dynamic_1_vw."DebtorIsPaid",
583    debtor_dynamic_1_vw."DebtorIsRelevant",
584    debtor_dynamic_1_vw."DebtorPaidAt",
585    debtor_dynamic_1_vw."PatientID",
586    debtor_dynamic_1_vw."PatientFirstName",
587    debtor_dynamic_1_vw."PatientLastName",
588    debtor_dynamic_1_vw."PatientCode",
589    debtor_dynamic_1_vw."PatientPhone",
590    debtor_dynamic_1_vw."PatientMobilePhone",
591    debtor_dynamic_1_vw."HcpID",
592    debtor_dynamic_1_vw."HcpName",
593    debtor_dynamic_1_vw."LastPhoneCallDate",
594    debtor_dynamic_1_vw."LastNormalMailDate",
595    debtor_dynamic_1_vw."LastStrictMailDate"
596   FROM payment.debtor_dynamic_1_vw
597  WHERE (NOT (debtor_dynamic_1_vw."PatientID" IN ( SELECT id.patient_id
598           FROM payment.debtor id
599          WHERE ((id.is_paid = false) AND (id.is_relevant = true)))));
600
601COMMENT ON VIEW payment.debtor_dynamic_vw IS 'Join virtual and created debtors into one list.' ;
602
603CREATE VIEW registration.registrations_internet_vw AS
604     SELECT a.start,
605    a.duration,
606    a.status,
607    a.goal,
608    a.rejection_reason,
609    a.is_emergency,
610    a.created_in,
611    date_trunc('day'::text, a.created_at) AS created_at,
612    date_part('year'::text, a.created_at) AS year_created,
613    ((date_part('year'::text, a.created_at) || ' q-'::text) || date_part('quarter'::text, a.created_at)) AS quarter_created,
614    ((date_part('year'::text, a.created_at) || ' '::text) || to_char(date_part('month'::text, a.created_at), 'FM00'::text)) AS month_created,
615    a.reason,
616    a.reason_type,
617    a.has_doctor_card,
618    (a.start - (date(a.created_at))::timestamp without time zone) AS pre_registration_period,
619    (((d.lastname)::text || ' '::text) || (d.firstname)::text) AS doctor,
620    h.name AS healthcareprovidername,
621    (((p.lastname)::text || ' '::text) || (p.firstname)::text) AS patient
622   FROM (((registration.appointment a
623     JOIN demographics.party d ON ((a.doctor_id = d.id)))
624     JOIN demographics.party h ON ((a.health_care_provider_id = h.id)))
625     JOIN demographics.party p ON ((a.visitor_id = p.id)))
626  WHERE ((a.created_in)::text = 'softdent::patientdesk'::text);
627
628CREATE VIEW registration.foxus_appointments_vw AS
629     SELECT a.created_at AS action_date,
630    (a.start - (date(a.created_at))::timestamp without time zone) AS pre_registration_period,
631    a.id,
632    a.type_name,
633    a.start,
634    a.duration,
635    a.status,
636    a.goal,
637    a.rejection_reason,
638    a.cabinet_number,
639    a.doctor_id,
640    a.visitor_id,
641    a.health_care_provider_id,
642    a.visitor_organisation_id,
643    a.is_emergency,
644    a.created_in,
645    a.created_at,
646    a.reason,
647    a.reason_type,
648    a.has_doctor_card,
649    d.firstname AS doctorfirstname,
650    d.lastname AS doctorlastname,
651    h.name AS healthcareprovidername,
652    p.firstname AS patientfirstname,
653    p.lastname AS patientlastname
654   FROM (((registration.appointment a
655     JOIN demographics.party d ON ((a.doctor_id = d.id)))
656     JOIN demographics.party h ON ((a.health_care_provider_id = h.id)))
657     JOIN demographics.party p ON ((a.visitor_id = p.id)))
658  WHERE (((a.created_in)::text = 'softdent::foxus'::text) OR ((a.created_in)::text = 'softdent::foxus2'::text));
659
660CREATE VIEW registration.epacientas_appointments_vw AS
661     SELECT a.id,
662    a.type_name,
663    a.start,
664    a.duration,
665    a.status,
666    a.goal,
667    a.rejection_reason,
668    a.cabinet_number,
669    a.doctor_id,
670    a.visitor_id,
671    a.health_care_provider_id,
672    a.visitor_organisation_id,
673    a.is_emergency,
674    a.created_in,
675    a.created_at,
676    a.reason,
677    a.reason_type,
678    a.has_doctor_card,
679    (a.start - (date(a.created_at))::timestamp without time zone) AS pre_registration_period,
680    d.firstname AS doctorfirstname,
681    d.lastname AS doctorlastname,
682    h.name AS healthcareprovidername,
683    p.firstname AS patientfirstname,
684    p.lastname AS patientlastname
685   FROM (((registration.appointment a
686     JOIN demographics.party d ON ((a.doctor_id = d.id)))
687     JOIN demographics.party h ON ((a.health_care_provider_id = h.id)))
688     JOIN demographics.party p ON ((a.visitor_id = p.id)))
689  WHERE ((a.created_in)::text = 'softdent::patientdesk'::text);
690
691CREATE SCHEMA documents
692    AUTHORIZATION octopus_lv_rc;
693
694DROP TYPE form25.period_step;
695
696DROP TYPE form25.period_status;
697
698ALTER TABLE payment.person_service
699    ALTER COLUMN service_id SET NOT NULL ,
700    ALTER COLUMN employee_id SET NOT NULL ,
701    ALTER COLUMN receiver_id SET NOT NULL ,
702    ALTER COLUMN created_by_id SET NOT NULL ,
703    ALTER COLUMN modified_at DROP NOT NULL ,
704    ALTER COLUMN deleted_at DROP NOT NULL;
705
706ALTER TABLE prescription.erecipe
707    ALTER COLUMN transcriber_id DROP NOT NULL;
708
709ALTER TABLE files.stored_file
710    ALTER COLUMN target_person_id DROP NOT NULL;
711
712ALTER TABLE management.multiple_setting_value
713    ALTER COLUMN "value" SET NOT NULL;
714
715ALTER TABLE payment.service_payment
716    ALTER COLUMN date SET DATA TYPE date;
717
718ALTER TABLE registration.appointment
719    ALTER COLUMN health_care_provider_id SET NOT NULL ,
720    ALTER COLUMN created_at SET NOT NULL;
721
722ALTER TABLE registration.appointment
723    ALTER COLUMN duration SET DEFAULT 0;
724
725ALTER TABLE registration.appointment
726    ALTER COLUMN goal SET DATA TYPE text ,
727    ALTER COLUMN cabinet_number SET DATA TYPE text ,
728    ALTER COLUMN rejection_reason SET DATA TYPE text ,
729    ALTER COLUMN visitor SET DATA TYPE text ,
730    ALTER COLUMN product SET DATA TYPE text ,
731    ALTER COLUMN producer_ssa_name SET DATA TYPE text ,
732    ALTER COLUMN notes SET DATA TYPE text;
733
734ALTER TABLE messaging.message_template
735    ALTER COLUMN message_type SET STORAGE PLAIN;
736
737ALTER TABLE messaging.message_template
738    ALTER COLUMN message_type SET DATA TYPE smallint;
739
740ALTER TABLE messaging.message
741    ALTER COLUMN "type" SET STORAGE PLAIN;
742
743ALTER TABLE messaging.message
744    ALTER COLUMN status SET DEFAULT 0;
745
746ALTER TABLE messaging.message
747    ALTER COLUMN status SET DATA TYPE smallint ,
748    ALTER COLUMN "type" SET DATA TYPE smallint ,
749    ALTER COLUMN text SET DATA TYPE text ,
750    ALTER COLUMN error SET DATA TYPE text;
751
752DROP TABLE prescription.issue;
753
754DROP TABLE resource_management.patient_room;
755
756DROP TABLE resource_management.patient_operation;
757
758DROP TABLE laboratory.group_tests;
759
760DROP TABLE resource_management.patient_room_visit;
761
762DROP TABLE resource_management.section;
763
764DROP TABLE resource_management.patient_operation_achi_services_used;
765
766DROP TABLE resource_management.patient_operation_achi_services;
767
768DROP TABLE prevention_program.prevention_program_visibility;
769
770DROP TABLE resource_management.medical_supply_used;
771
772DROP TABLE resource_management.bed;
773
774DROP TABLE resource_management.medical_supply;
775
776DROP TABLE resource_management.operation_participant;
777
778DROP TABLE resource_management.operation_room_resource_used;
779
780DROP TABLE resource_management.anaesthetization_type_used;
781
782DROP TABLE resource_management.operation_room_resource;
783
784DROP TABLE resource_management.available_patient_operation;
785
786DROP TABLE resource_management.anaesthetization_types;
787
788DROP TABLE resource_management.operation_room;
789
790DROP TABLE resource_management.anaesthetization_type;
791
792DROP TABLE registration.appointment_product;
793
794DROP TABLE registration.doctor_participation;
795
796DROP TABLE prevention_program.prevention_program_history;
797
798DROP TABLE prevention_program.prevention_program_result;
799
800DROP TABLE prevention_program.cached_programs;
801
802DROP TABLE prevention_program.pending_program_patients;
803
804DROP TABLE prevention_program.invalidation_codes;
805
806DROP TABLE prevention_program.prevention_program;
807
808DROP TABLE prevention_program.cached_patient_programs;
809
810DROP TABLE prescription.recipe;
811
812DROP TABLE payment.lab_test_payment;
813
814DROP TABLE payment.invoice_person_service;
815
816DROP TABLE payment.lab_test_payment_services;
817
818DROP TABLE payment.invoice;
819
820DROP TABLE prescription.medication;
821
822DROP TABLE management.pattern_permissions;
823
824DROP TABLE payment.cash_receipt_services;
825
826DROP TABLE management.patient_group;
827
828DROP TABLE payment.cash_receipt;
829
830DROP TABLE management.patient_group_patients;
831
832DROP TABLE management.environment_settings;
833
834DROP TABLE management.multiple_setting_values;
835
836DROP TABLE management.user_permissions;
837
838DROP TABLE marketing.promotion;
839
840DROP TABLE management.rule_settings;
841
842DROP TABLE management.user_settings;
843
844DROP TABLE management.department_settings;
845
846DROP TABLE form25.visit;
847
848DROP TABLE laboratory.test_analyte;
849
850DROP TABLE laboratory.analyte;
851
852DROP TABLE management.environment_permissions;
853
854DROP TABLE laboratory.test_order;
855
856DROP TABLE laboratory.lab_result;
857
858DROP TABLE laboratory.lab_order;
859
860DROP TABLE laboratory.test;
861
862DROP TABLE form25.result_solution;
863
864DROP TABLE form25.visit_service;
865
866DROP TABLE laboratory.test_group;
867
868DROP TABLE form25.rule_violation;
869
870DROP TABLE form25.card;
871
872DROP TABLE form25.diagnose;
873
874DROP TABLE form25.intervention;
875
876DROP TABLE form25.hi_value;
877
878DROP TABLE form25.medicine;
879
880DROP TABLE form25.period;
881
882DROP TABLE form25.premium;
883
884CREATE TABLE payment.material_group
885(
886    id bigint,
887    company_id bigint,
888    is_imported boolean NOT NULL,
889    code text NOT NULL,
890    name text,
891    CONSTRAINT material_group_pkey PRIMARY KEY ( id ),
892    CONSTRAINT material_group_code_company_id_key UNIQUE ( code, company_id ),
893    CONSTRAINT material_company_fkey FOREIGN KEY ( company_id ) REFERENCES management . company ( id )
894);
895
896CREATE TABLE payment.material
897(
898    id bigint,
899    company_id bigint NOT NULL,
900    group_id bigint,
901    quantity numeric(10,2) NOT NULL,
902    is_imported boolean NOT NULL,
903    code text NOT NULL,
904    name text,
905    unit text,
906    barcode text,
907    prime_cost numeric(10,2) NOT NULL DEFAULT 0,
908    CONSTRAINT material_pkey PRIMARY KEY ( id ),
909    CONSTRAINT material_company_fkey FOREIGN KEY ( company_id ) REFERENCES management . company ( id ),
910    CONSTRAINT material_group_fkey FOREIGN KEY ( group_id ) REFERENCES payment . material_group ( id )
911);
912
913CREATE TABLE payment.used_material
914(
915    id bigint,
916    person_service_id bigint,
917    exported_at timestamp,
918    quantity numeric(10,4) NOT NULL,
919    code text NOT NULL,
920    name text,
921    unit text,
922    package_quantity numeric(10,4) NOT NULL,
923    prime_cost numeric(10,2) NOT NULL DEFAULT 0,
924    CONSTRAINT used_material_pkey PRIMARY KEY ( id ),
925    CONSTRAINT used_material_person_service_fkey FOREIGN KEY ( person_service_id ) REFERENCES payment . person_service ( id )
926);
927
928CREATE TABLE payment.service_material
929(
930    id bigint,
931    service_id bigint NOT NULL,
932    material_id bigint NOT NULL,
933    quantity numeric(10,2) NOT NULL,
934    CONSTRAINT service_material_pkey PRIMARY KEY ( id ),
935    CONSTRAINT service_material_service_id_material_id_key UNIQUE ( service_id, material_id ),
936    CONSTRAINT service_material_fkey FOREIGN KEY ( material_id ) REFERENCES payment . material ( id ) ON DELETE CASCADE,
937    CONSTRAINT service_material_service_fkey FOREIGN KEY ( service_id ) REFERENCES payment . service ( id )
938);
939
940CREATE TABLE payment.service_material_group
941(
942    service_id bigint NOT NULL,
943    material_group_id bigint NOT NULL,
944    CONSTRAINT service_material_group_fkey FOREIGN KEY ( material_group_id ) REFERENCES payment . material_group ( id ),
945    CONSTRAINT service_material_group_service_fkey FOREIGN KEY ( service_id ) REFERENCES payment . service ( id )
946);
947
948CREATE TABLE payment."cost"
949(
950    id bigint,
951    service_id bigint,
952    "cost" numeric(19,5) NOT NULL,
953    cost_subscribed numeric(19,5) NOT NULL,
954    CONSTRAINT cost_pkey PRIMARY KEY ( id ),
955    CONSTRAINT cost_service_fkey FOREIGN KEY ( service_id ) REFERENCES payment . service ( id )
956);
957
958CREATE TABLE payment.healthcareprovider_enabled_services_costs
959(
960    healthcareprovider_enabled_services_id bigint,
961    cost_id bigint,
962    CONSTRAINT healthcareprovider_enabled_services_costs_pkey PRIMARY KEY ( healthcareprovider_enabled_services_id, cost_id ),
963    CONSTRAINT healthcareprovider_enabled_services_costs_fkey FOREIGN KEY ( healthcareprovider_enabled_services_id ) REFERENCES payment . healthcareprovider_enabled_services ( id ),
964    CONSTRAINT healthcareprovider_enabled_services_costs_id_fkey FOREIGN KEY ( cost_id ) REFERENCES payment . "cost" ( id )
965);
966
967CREATE TABLE payment.collection
968(
969    id bigint,
970    valid_from timestamp NOT NULL,
971    valid_to timestamp NOT NULL,
972    discount numeric(19,5) NOT NULL,
973    discount_subscribed numeric(19,5) NOT NULL,
974    is_editable boolean NOT NULL,
975    discount_type text,
976    name text NOT NULL,
977    company_id bigint NOT NULL,
978    CONSTRAINT collection_pkey PRIMARY KEY ( id ),
979    CONSTRAINT collection_company_fkey FOREIGN KEY ( company_id ) REFERENCES management . company ( id )
980);
981
982CREATE TABLE payment.healthcareprovider_enabled_services_collection
983(
984    healthcareprovider_enabled_services_id bigint,
985    collection_id bigint,
986    CONSTRAINT healthcareprovider_enabled_services_collection_pkey PRIMARY KEY ( healthcareprovider_enabled_services_id, collection_id ),
987    CONSTRAINT healthcareprovider_enabled_services_collection_fkey FOREIGN KEY ( healthcareprovider_enabled_services_id ) REFERENCES payment . healthcareprovider_enabled_services ( id ),
988    CONSTRAINT healthcareprovider_enabled_services_collection_id_fkey FOREIGN KEY ( collection_id ) REFERENCES payment . collection ( id )
989);
990
991CREATE TABLE payment.collection_cost
992(
993    collection_id bigint,
994    cost_id bigint,
995    CONSTRAINT collection_cost_pkey PRIMARY KEY ( collection_id, cost_id ),
996    CONSTRAINT collection_cost_collection_fkey FOREIGN KEY ( collection_id ) REFERENCES payment . collection ( id ),
997    CONSTRAINT collection_cost_cost_fkey FOREIGN KEY ( cost_id ) REFERENCES payment . "cost" ( id )
998);
999
1000CREATE TABLE documents.sick_leave
1001(
1002    id bigint,
1003    hcp_id bigint NOT NULL,
1004    certificate_type character varying(255) NOT NULL,
1005    patient_id bigint NOT NULL,
1006    doctor_id bigint NOT NULL,
1007    "type" character varying(255) NOT NULL,
1008    open_date timestamp NOT NULL,
1009    paper_registration_number character varying(255),
1010    notes text,
1011    backdated_justification character varying(255),
1012    CONSTRAINT sick_leave_pkey PRIMARY KEY ( id ),
1013    CONSTRAINT sick_leave_doctor FOREIGN KEY ( doctor_id ) REFERENCES demographics . party ( id ),
1014    CONSTRAINT sick_leave_hcp FOREIGN KEY ( hcp_id ) REFERENCES demographics . party ( id ),
1015    CONSTRAINT sick_leave_patient FOREIGN KEY ( patient_id ) REFERENCES demographics . party ( id )
1016);
1017
1018CREATE TABLE documents.sick_leave_cause
1019(
1020    id bigint,
1021    sick_leave_id bigint NOT NULL,
1022    causecode character varying(255) NOT NULL,
1023    CONSTRAINT sick_leave_cause_pkey PRIMARY KEY ( id ),
1024    CONSTRAINT sick_leave_fkey FOREIGN KEY ( sick_leave_id ) REFERENCES documents . sick_leave ( id )
1025);
1026
1027CREATE TABLE documents.sick_leave_date_range
1028(
1029    id bigint,
1030    sick_leave_id bigint NOT NULL,
1031    date_from timestamp NOT NULL,
1032    date_to timestamp NOT NULL,
1033    CONSTRAINT sick_leave_date_range_pkey PRIMARY KEY ( id ),
1034    CONSTRAINT sick_leave_fkey FOREIGN KEY ( sick_leave_id ) REFERENCES documents . sick_leave ( id )
1035);
1036
1037CREATE TABLE demographics.workplace_section
1038(
1039    workplace_id bigint NOT NULL,
1040    CONSTRAINT workplace_section_workplace_fkey FOREIGN KEY ( workplace_id ) REFERENCES demographics . workplace ( id )
1041);
1042
1043CREATE TABLE management.company_allowed_ip_addresses
1044(
1045    ip_addr character varying(255),
1046    company_id bigint,
1047    CONSTRAINT allowed_ip_addr_pkey PRIMARY KEY ( ip_addr, company_id ),
1048    CONSTRAINT ip_addr_company_fkey FOREIGN KEY ( company_id ) REFERENCES management . company ( id ) ON DELETE CASCADE ON UPDATE CASCADE
1049);
1050
1051CREATE TABLE demographics.patient_consent
1052(
1053    id bigint,
1054    code character varying(255) NOT NULL,
1055    patient_id bigint NOT NULL,
1056    hcp_id bigint,
1057    created_at timestamp,
1058    created_by_id bigint NOT NULL,
1059    deleted_at timestamp,
1060    deleted_by_id bigint,
1061    CONSTRAINT patient_consent_id_pk PRIMARY KEY ( id ),
1062    CONSTRAINT patient_consent_unique UNIQUE ( patient_id, code, hcp_id ),
1063    CONSTRAINT patient_consent_created_by_id_fk FOREIGN KEY ( created_by_id ) REFERENCES demographics . party ( id ),
1064    CONSTRAINT patient_consent_deleted_by_id_fk FOREIGN KEY ( deleted_by_id ) REFERENCES demographics . party ( id ),
1065    CONSTRAINT patient_consent_hcp_id_fk FOREIGN KEY ( hcp_id ) REFERENCES demographics . party ( id ),
1066    CONSTRAINT patient_consent_patient_id_fk FOREIGN KEY ( patient_id ) REFERENCES demographics . party ( id )
1067);
1068
1069CREATE TABLE files.tablet_document
1070(
1071    id bigint,
1072    name text NOT NULL,
1073    CONSTRAINT pk_tablet_document PRIMARY KEY ( id )
1074);
1075
1076CREATE TABLE files.tablet_document_template
1077(
1078    id bigint,
1079    note text,
1080    created_at timestamp NOT NULL,
1081    modified_at timestamp NOT NULL,
1082    stored_file_id bigint NOT NULL,
1083    tablet_document_id bigint NOT NULL,
1084    company_id bigint,
1085    hcp_id bigint,
1086    CONSTRAINT pk_tablet_document_template PRIMARY KEY ( id ),
1087    CONSTRAINT fk_document_template_company FOREIGN KEY ( company_id ) REFERENCES management . company ( id ),
1088    CONSTRAINT fk_document_template_hcp FOREIGN KEY ( hcp_id ) REFERENCES demographics . party ( id ),
1089    CONSTRAINT fk_stored_file FOREIGN KEY ( stored_file_id ) REFERENCES files . stored_file ( id ),
1090    CONSTRAINT fk_tablet_document FOREIGN KEY ( tablet_document_id ) REFERENCES files . tablet_document ( id )
1091);
1092
1093CREATE TABLE files.template_data
1094(
1095    id bigint,
1096    form_key text NOT NULL,
1097    title text,
1098    type_name text NOT NULL,
1099    value_type integer NOT NULL,
1100    "value" text,
1101    code integer,
1102    template_id bigint NOT NULL,
1103    CONSTRAINT pk_template_date PRIMARY KEY ( id ),
1104    CONSTRAINT fk_template_data_template FOREIGN KEY ( template_id ) REFERENCES files . tablet_document_template ( id )
1105);
1106
1107CREATE TABLE payment.workplace_enabled_services
1108(
1109    id bigint,
1110    workplace_id bigint,
1111    CONSTRAINT workplace_enabled_services_pkey PRIMARY KEY ( id ),
1112    CONSTRAINT workplace_enabled_services_workplace_fkey FOREIGN KEY ( workplace_id ) REFERENCES demographics . workplace ( id )
1113);
1114
1115CREATE TABLE payment.workplace_enabled_services_service
1116(
1117    workplace_enabled_services_id bigint,
1118    service_id bigint,
1119    CONSTRAINT workplace_enabled_services_service_pkey PRIMARY KEY ( workplace_enabled_services_id, service_id ),
1120    CONSTRAINT wp_es_s_service_fkey FOREIGN KEY ( service_id ) REFERENCES payment . service ( id ),
1121    CONSTRAINT wp_es_s_wse_fkey FOREIGN KEY ( workplace_enabled_services_id ) REFERENCES payment . workplace_enabled_services ( id )
1122);
1123
1124CREATE TABLE payment.hi_value
1125(
1126    next_value bigint
1127);
1128
1129CREATE TABLE registration.product
1130(
1131    id bigint,
1132    external_id bigint,
1133    name character varying(255),
1134    task_id bigint,
1135    event_id bigint,
1136    CONSTRAINT appointment_product_pkey PRIMARY KEY ( id ),
1137    CONSTRAINT appointment_product_task_fkey FOREIGN KEY ( task_id ) REFERENCES registration . appointment ( id )
1138);
1139
1140CREATE TABLE payment.service_centre
1141(
1142    id bigint,
1143    name character varying(255) NOT NULL,
1144    CONSTRAINT service_centre_pkey PRIMARY KEY ( id )
1145);
1146
1147CREATE TABLE payment.healthcareprovider_enabled_services_service_centre
1148(
1149    healthcareprovider_enabled_services_id bigint,
1150    service_centre_id bigint,
1151    CONSTRAINT healthcareprovider_enabled_services_service_centre_pke PRIMARY KEY ( healthcareprovider_enabled_services_id, service_centre_id ),
1152    CONSTRAINT hcpes_sc_hcpes_fkey FOREIGN KEY ( healthcareprovider_enabled_services_id ) REFERENCES payment . healthcareprovider_enabled_services ( id ),
1153    CONSTRAINT hcpes_sc_sc_fkey FOREIGN KEY ( service_centre_id ) REFERENCES payment . service_centre ( id )
1154);
1155
1156CREATE TABLE payment.service_subdivision
1157(
1158    id bigint,
1159    name character varying(255) NOT NULL,
1160    CONSTRAINT service_subdivision_pkey PRIMARY KEY ( id )
1161);
1162
1163CREATE TABLE payment.healthcareprovider_enabled_services_service_subdivision
1164(
1165    healthcareprovider_enabled_services_id bigint,
1166    service_subdivision_id bigint,
1167    CONSTRAINT healthcareprovider_enabled_services_service_sub_division_pke PRIMARY KEY ( healthcareprovider_enabled_services_id, service_subdivision_id ),
1168    CONSTRAINT hcpes_ssd_hcpes_fkey FOREIGN KEY ( healthcareprovider_enabled_services_id ) REFERENCES payment . healthcareprovider_enabled_services ( id ),
1169    CONSTRAINT hcpes_ssd_ssd_fkey FOREIGN KEY ( service_subdivision_id ) REFERENCES payment . service_subdivision ( id )
1170);
1171
1172CREATE TABLE marketing.patient_group
1173(
1174    id bigint,
1175    name character varying(255) NOT NULL,
1176    "value" character varying(255) NOT NULL,
1177    company_id bigint NOT NULL,
1178    CONSTRAINT patient_group_pkey PRIMARY KEY ( id ),
1179    CONSTRAINT company_fkey FOREIGN KEY ( company_id ) REFERENCES management . company ( id )
1180);
1181
1182CREATE TABLE payment.healthcareprovider_patientgroup_enabled_services
1183(
1184    id bigint,
1185    group_id bigint,
1186    health_care_provider_id bigint,
1187    CONSTRAINT healthcareprovider_patientgroup_enabled_services_pkey PRIMARY KEY ( id ),
1188    CONSTRAINT healthcareprovider_patientgroup_enabled_services_group_fkey FOREIGN KEY ( group_id ) REFERENCES marketing . patient_group ( id ),
1189    CONSTRAINT healthcareprovider_patientgroup_enabled_services_hcp_fkey FOREIGN KEY ( health_care_provider_id ) REFERENCES demographics . party ( id )
1190);
1191
1192CREATE TABLE payment.healthcareprovider_patientgroup_enabled_services_costs
1193(
1194    enabled_id bigint,
1195    cost_id bigint,
1196    CONSTRAINT healthcareprovider_patientgroup_enabled_services_costs_pkey PRIMARY KEY ( enabled_id, cost_id ),
1197    CONSTRAINT healthcareprovider_patientgroup_enabled_services_costs_cost_fke FOREIGN KEY ( cost_id ) REFERENCES payment . "cost" ( id ),
1198    CONSTRAINT healthcareprovider_patientgroup_enabled_services_costs_enabled_ FOREIGN KEY ( enabled_id ) REFERENCES payment . healthcareprovider_patientgroup_enabled_services ( id )
1199);
1200
1201CREATE TABLE payment.patient_group_cost
1202(
1203    group_id bigint,
1204    cost_id bigint,
1205    CONSTRAINT patient_group_cost_pkey PRIMARY KEY ( group_id, cost_id ),
1206    CONSTRAINT patient_group_cost_cost_fkey FOREIGN KEY ( cost_id ) REFERENCES payment . "cost" ( id ),
1207    CONSTRAINT patient_group_cost_group_fkey FOREIGN KEY ( group_id ) REFERENCES marketing . patient_group ( id )
1208);
1209
1210CREATE TABLE marketing.patient_group_patients
1211(
1212    group_id bigint,
1213    patient_id bigint,
1214    CONSTRAINT patient_group_patients_pkey PRIMARY KEY ( group_id, patient_id ),
1215    CONSTRAINT patient_fkey FOREIGN KEY ( patient_id ) REFERENCES demographics . party ( id ),
1216    CONSTRAINT patient_group_fkey FOREIGN KEY ( group_id ) REFERENCES marketing . patient_group ( id )
1217);
1218
1219CREATE TABLE demographics.vacation
1220(
1221    id bigint,
1222    workplace_id bigint NOT NULL,
1223    "type" smallint NOT NULL,
1224    created_by_id bigint NOT NULL,
1225    created_at timestamp NOT NULL,
1226    modified_by_id bigint,
1227    modified_at timestamp,
1228    timing text,
1229    CONSTRAINT vacation_pkey PRIMARY KEY ( id ),
1230    CONSTRAINT vacation_created_by_fkey FOREIGN KEY ( created_by_id ) REFERENCES demographics . party ( id ),
1231    CONSTRAINT vacation_modified_by_fkey FOREIGN KEY ( created_by_id ) REFERENCES demographics . party ( id ),
1232    CONSTRAINT vacation_workplace_fkey FOREIGN KEY ( workplace_id ) REFERENCES demographics . workplace ( id )
1233);
1234
1235CREATE TABLE payment.service_secondary_group
1236(
1237    id bigint,
1238    name text NOT NULL,
1239    CONSTRAINT service_secondary_group_pkey PRIMARY KEY ( id )
1240);
1241
1242CREATE TABLE payment.healthcareprovider_enabled_services_service_secondary_group
1243(
1244    healthcareprovider_enabled_services_id bigint,
1245    service_secondary_group_id bigint,
1246    CONSTRAINT healthcareprovider_enabled_services_service_secondary_group_pke PRIMARY KEY ( healthcareprovider_enabled_services_id, service_secondary_group_id ),
1247    CONSTRAINT hcpes_sg_hcpes_fkey FOREIGN KEY ( healthcareprovider_enabled_services_id ) REFERENCES payment . healthcareprovider_enabled_services ( id ),
1248    CONSTRAINT hcpes_sg_sg_fkey FOREIGN KEY ( service_secondary_group_id ) REFERENCES payment . service_secondary_group ( id )
1249);
1250
1251CREATE TABLE messaging.global_message
1252(
1253    application_name text,
1254    message text NOT NULL,
1255    CONSTRAINT global_message_pkey PRIMARY KEY ( application_name )
1256);
1257
1258CREATE TABLE marketing.hi_value
1259(
1260    next_value bigint
1261);
1262
1263CREATE TABLE demographics.patient_hcp_data_log
1264(
1265    timestamp timestamp,
1266    db_username text,
1267    client text,
1268    ip inet,
1269    sql_action character(1) NOT NULL,
1270    patient_id bigint NOT NULL,
1271    health_care_provider_id bigint NOT NULL,
1272    created_by_id bigint,
1273    modified_by_id bigint,
1274    deleted_by_id bigint,
1275    created_at timestamp,
1276    modified_at timestamp,
1277    deleted_at timestamp,
1278    paper_card_no text,
1279    notes text
1280);
1281
1282CREATE TABLE demographics.patient_consent_log
1283(
1284    timestamp timestamp,
1285    db_username text,
1286    client text,
1287    ip inet,
1288    sql_action character(1) NOT NULL,
1289    id bigint NOT NULL,
1290    code character varying(255) NOT NULL,
1291    patient_id bigint NOT NULL,
1292    hcp_id bigint,
1293    created_at timestamp,
1294    created_by_id bigint NOT NULL,
1295    deleted_at timestamp,
1296    deleted_by_id bigint
1297);
1298
1299CREATE TABLE demographics.party_log
1300(
1301    timestamp timestamp,
1302    db_username text,
1303    client text,
1304    ip inet,
1305    sql_action character(1) NOT NULL,
1306    id bigint NOT NULL,
1307    type_name character varying(255) NOT NULL,
1308    email character varying(255),
1309    phone character varying(255),
1310    mobile_phone character varying(255),
1311    address_post_code character varying(255),
1312    address_region character varying(255),
1313    address_town character varying(255),
1314    address_country character varying(255),
1315    code character varying(255),
1316    name character varying(255),
1317    short_name character varying(255),
1318    firstname character varying(255),
1319    lastname character varying(255),
1320    enterprise_id bigint,
1321    visible_for_representative boolean,
1322    visible_for_patient boolean,
1323    birth_date timestamp,
1324    address_settlement_type character varying(255),
1325    address_municipality character varying(255),
1326    address_street character varying(255),
1327    address_house character varying(255),
1328    address_flat character varying(255),
1329    address_village character varying(255),
1330    patient_schedule_duration bigint,
1331    gender character varying(255),
1332    is_smoker boolean,
1333    work_certificate_no character varying(255),
1334    unemployment_registration_no character varying(255),
1335    officer_certificate_no character varying(255),
1336    fax character varying(255),
1337    vat_code character varying(255),
1338    phone_note character varying(255),
1339    mobile_phone_note character varying(255),
1340    is_dead boolean,
1341    middlename character varying(255),
1342    notes text,
1343    address_block character varying(255),
1344    blood text,
1345    is_blood_rh boolean,
1346    degree text,
1347    license_code text,
1348    dik bigint,
1349    integration_type text,
1350    email_note character varying(255),
1351    created_by_id bigint,
1352    modified_by_id bigint,
1353    deleted_by_id bigint,
1354    created_at timestamp,
1355    modified_at timestamp,
1356    deleted_at timestamp,
1357    created_in text,
1358    working_ability_level_special_needs text
1359);
1360
1361CREATE TABLE registration.appointment_log
1362(
1363    timestamp timestamp,
1364    db_username text,
1365    client text,
1366    ip inet,
1367    sql_action character(1) NOT NULL DEFAULT 'U'::bpchar,
1368    cached_doctor text,
1369    cached_patient text,
1370    id bigint NOT NULL,
1371    health_care_provider_id bigint NOT NULL,
1372    doctor_id bigint,
1373    speciality_id bigint,
1374    visitor_id bigint,
1375    visitor_organisation_id bigint,
1376    created_by_id bigint NOT NULL,
1377    modified_by_id bigint,
1378    deleted_by_id bigint,
1379    producer_ssa_id bigint,
1380    duration bigint NOT NULL DEFAULT 0,
1381    "start" timestamp NOT NULL,
1382    created_at timestamp NOT NULL,
1383    modified_at timestamp,
1384    deleted_at timestamp,
1385    "cost" numeric(19,5),
1386    type_name character varying(255) NOT NULL,
1387    created_in character varying(255),
1388    status character varying(255),
1389    goal text,
1390    cabinet_number text,
1391    reason character varying(255),
1392    reason_type character varying(255),
1393    rejection_reason text,
1394    visitor text,
1395    product text,
1396    producer_ssa_name text,
1397    notes text,
1398    is_confirmed boolean,
1399    is_sms_sent boolean,
1400    reimbursed_medicine_passport boolean,
1401    is_emergency boolean,
1402    has_doctor_card boolean,
1403    pregnant_woman_card boolean,
1404    work_incapacity_certificate boolean,
1405    is_from_call_centre boolean,
1406    is_remote_service boolean,
1407    external_queue_number character varying,
1408    created_in_organisation_id bigint,
1409    negotiator_id bigint
1410);
1411
1412CREATE TABLE vaccination.vaccine_diagnoses
1413(
1414    vaccine_id bigint,
1415    "value" text,
1416    main_diagnose boolean NOT NULL DEFAULT true,
1417    CONSTRAINT vaccine_diagnoses_vaccine_id_fkey FOREIGN KEY ( vaccine_id ) REFERENCES vaccination . vaccine ( id ) ON DELETE CASCADE
1418);
1419
1420ALTER TABLE registration.appointment
1421    ADD COLUMN speciality_id bigint,
1422    ADD COLUMN visitor_id bigint,
1423    ADD COLUMN visitor_organisation_id bigint,
1424    ADD COLUMN created_by_id bigint NOT NULL,
1425    ADD COLUMN modified_by_id bigint,
1426    ADD COLUMN deleted_by_id bigint,
1427    ADD COLUMN modified_at timestamp,
1428    ADD COLUMN deleted_at timestamp,
1429    ADD COLUMN is_confirmed boolean,
1430    ADD COLUMN is_sms_sent boolean,
1431    ADD COLUMN reimbursed_medicine_passport boolean,
1432    ADD COLUMN has_doctor_card boolean,
1433    ADD COLUMN is_from_call_centre boolean,
1434    ADD COLUMN is_remote_service boolean,
1435    ADD COLUMN external_queue_number character varying,
1436    ADD COLUMN created_in_organisation_id bigint,
1437    ADD COLUMN negotiator_id bigint;
1438
1439ALTER TABLE demographics.party
1440    ADD COLUMN dik bigint,
1441    ADD COLUMN email_note character varying ( 255 ),
1442    ADD COLUMN created_by_id bigint NOT NULL,
1443    ADD COLUMN modified_by_id bigint,
1444    ADD COLUMN deleted_by_id bigint,
1445    ADD COLUMN created_at timestamp NOT NULL,
1446    ADD COLUMN modified_at timestamp,
1447    ADD COLUMN deleted_at timestamp,
1448    ADD COLUMN created_in text,
1449    ADD COLUMN working_ability_level_special_needs text;
1450
1451ALTER TABLE payment.person_service
1452    ADD COLUMN healthcare_provider_id bigint NOT NULL,
1453    ADD COLUMN collection_id bigint,
1454    ADD COLUMN service_cost numeric ( 19, 5 ) NOT NULL,
1455    ADD COLUMN third_party_code character varying ( 255 ),
1456    ADD COLUMN status character varying ( 255 ) NOT NULL DEFAULT 'provided'::character varying,
1457    ADD COLUMN test_order_id bigint,
1458    ADD COLUMN patient_group_id bigint;
1459
1460ALTER TABLE management.permission
1461    ADD COLUMN user_id bigint,
1462    ADD COLUMN environment_id bigint,
1463    ADD COLUMN pattern_id bigint;
1464
1465ALTER TABLE management.company
1466    ADD COLUMN "type" character varying ( 255 );
1467
1468ALTER TABLE management.multiple_setting_value
1469    ADD COLUMN setting_id bigint,
1470    ADD COLUMN position integer;
1471
1472ALTER TABLE management.setting
1473    ADD COLUMN user_id bigint,
1474    ADD COLUMN environment_id bigint,
1475    ADD COLUMN department_id bigint,
1476    ADD COLUMN rule_id bigint;
1477
1478ALTER TABLE payment.service_payment
1479    ADD COLUMN payer_id bigint NOT NULL,
1480    ADD COLUMN organisation_id bigint NOT NULL,
1481    ADD COLUMN amount numeric ( 10, 2 ) NOT NULL,
1482    ADD COLUMN amount_in_cash numeric ( 10, 2 ) NOT NULL,
1483    ADD COLUMN amount_in_card numeric ( 10, 2 ) NOT NULL,
1484    ADD COLUMN currency character varying ( 255 ) NOT NULL,
1485    ADD COLUMN cash_receipt_no integer,
1486    ADD COLUMN return_cash_receipt_no integer,
1487    ADD COLUMN "type" character varying ( 255 ) NOT NULL,
1488    ADD COLUMN notes text,
1489    ADD COLUMN created_by_id bigint NOT NULL,
1490    ADD COLUMN created_at timestamp NOT NULL,
1491    ADD COLUMN deleted_by_id bigint,
1492    ADD COLUMN deleted_at timestamp,
1493    ADD COLUMN cash_receipt_source smallint,
1494    ADD COLUMN is_advance_payment boolean;
1495
1496ALTER TABLE messaging.message_template
1497    ADD COLUMN "type" smallint NOT NULL,
1498    ADD COLUMN owner_company_id bigint,
1499    ADD COLUMN editable boolean NOT NULL DEFAULT true;
1500
1501ALTER TABLE demographics.workplace
1502    ADD COLUMN third_party_code text,
1503    ADD COLUMN valid_from timestamp NOT NULL DEFAULT '-infinity'::timestamp without time zone,
1504    ADD COLUMN valid_to timestamp NOT NULL DEFAULT 'infinity'::timestamp without time zone,
1505    ADD COLUMN default_service_code integer,
1506    ADD COLUMN "version" smallint,
1507    ADD COLUMN autocreate_purpose integer;
1508
1509ALTER TABLE management.environment
1510    ADD COLUMN ip_addr character varying ( 255 );
1511
1512ALTER TABLE management.user
1513    ADD COLUMN is_two_factor_enabled boolean,
1514    ADD COLUMN two_factor_disabled_by_id bigint,
1515    ADD COLUMN two_factor_disabled_at timestamp,
1516    ADD COLUMN password_reset_at timestamp;
1517
1518ALTER TABLE payment.person_service
1519    DROP COLUMN uuid,
1520    DROP COLUMN payment_period,
1521    DROP COLUMN is_vat;
1522
1523ALTER TABLE prescription.erecipe
1524    DROP COLUMN healthcareprovider_id,
1525    DROP COLUMN disease_code,
1526    DROP COLUMN medication_code,
1527    DROP COLUMN administration_recommendations,
1528    DROP COLUMN recipe_date_from,
1529    DROP COLUMN recipe_date_to,
1530    DROP COLUMN treatment_date_from,
1531    DROP COLUMN treatment_date_to,
1532    DROP COLUMN notes,
1533    DROP COLUMN quantity,
1534    DROP COLUMN unit;
1535
1536ALTER TABLE prescription.erecipe
1537    ADD COLUMN hcp_id bigint NOT NULL,
1538    ADD COLUMN is_compensated boolean NOT NULL,
1539    ADD COLUMN drug_prescription_type character varying ( 255 ) NOT NULL,
1540    ADD COLUMN serial_number character varying ( 255 ),
1541    ADD COLUMN validity_date_from timestamp NOT NULL,
1542    ADD COLUMN validity_date_to timestamp NOT NULL,
1543    ADD COLUMN is_treatment_course boolean NOT NULL,
1544    ADD COLUMN treatment_course_amount integer,
1545    ADD COLUMN treatment_course_unit character varying ( 255 ),
1546    ADD COLUMN main_diagnosis_code character varying ( 255 ),
1547    ADD COLUMN additional_diagnosis_code character varying ( 255 ),
1548    ADD COLUMN drug_code character varying ( 255 ),
1549    ADD COLUMN drug_name character varying ( 255 ),
1550    ADD COLUMN drug_form_code character varying ( 255 ),
1551    ADD COLUMN drug_form_name character varying ( 255 ),
1552    ADD COLUMN substitution_condition character varying ( 255 ),
1553    ADD COLUMN substitution_prohibition_reason character varying ( 255 ),
1554    ADD COLUMN special_dispense_requirements character varying ( 255 ),
1555    ADD COLUMN quantity_amount double precision,
1556    ADD COLUMN quantity_unit character varying ( 255 ),
1557    ADD COLUMN substance_amount integer,
1558    ADD COLUMN substance_unit character varying ( 255 ),
1559    ADD COLUMN compensation_received boolean NOT NULL,
1560    ADD COLUMN compensation_code character varying ( 255 ),
1561    ADD COLUMN compensation_percentage integer,
1562    ADD COLUMN compensation_supplier character varying ( 255 ),
1563    ADD COLUMN compensation_cause character varying ( 255 ),
1564    ADD COLUMN usage_instructions character varying ( 255 ) NOT NULL,
1565    ADD COLUMN pharmacist_notes character varying ( 255 ),
1566    ADD COLUMN receiver_is_patient_only boolean,
1567    ADD COLUMN first_receiver_id character varying ( 255 ),
1568    ADD COLUMN first_receiver_name character varying ( 255 ),
1569    ADD COLUMN first_receiver_surname character varying ( 255 ),
1570    ADD COLUMN second_receiver_id character varying ( 255 ),
1571    ADD COLUMN second_receiver_name character varying ( 255 ),
1572    ADD COLUMN second_receiver_surname character varying ( 255 );
1573
1574ALTER TABLE vaccination.schedule
1575    DROP COLUMN uuid;
1576
1577ALTER TABLE vaccination.schedule_execution
1578    DROP COLUMN uuid;
1579
1580ALTER TABLE vaccination.vaccine_execution
1581    DROP COLUMN uuid;
1582
1583ALTER TABLE vaccination.vaccine
1584    DROP COLUMN uuid;
1585
1586ALTER TABLE registration.visit_schema_item
1587    DROP COLUMN uuid;
1588
1589ALTER TABLE registration.visit_schema
1590    DROP COLUMN uuid;
1591
1592ALTER TABLE payment.negotiation
1593    DROP COLUMN uuid;
1594
1595ALTER TABLE payment.service_type
1596    DROP COLUMN uuid;
1597
1598ALTER TABLE payment.service_group
1599    DROP COLUMN uuid;
1600
1601ALTER TABLE payment.discount
1602    DROP COLUMN uuid;
1603
1604ALTER TABLE payment.contract
1605    DROP COLUMN uuid;
1606
1607ALTER TABLE management.setting
1608    DROP COLUMN uuid;
1609
1610ALTER TABLE payment.healthcareprovider_enabled_services
1611    DROP COLUMN uuid;
1612
1613ALTER TABLE management.permission
1614    DROP COLUMN uuid;
1615
1616ALTER TABLE management.pattern
1617    DROP COLUMN uuid;
1618
1619ALTER TABLE management.rule
1620    DROP COLUMN uuid;
1621
1622ALTER TABLE management.department
1623    DROP COLUMN uuid;
1624
1625ALTER TABLE management.company
1626    DROP COLUMN uuid;
1627
1628ALTER TABLE management.environment
1629    DROP COLUMN uuid;
1630
1631ALTER TABLE files.stored_file
1632    DROP COLUMN uuid;
1633
1634ALTER TABLE extensions.audit
1635    DROP COLUMN id;
1636
1637ALTER TABLE core.localized_string
1638    DROP COLUMN uuid;
1639
1640ALTER TABLE ehr.medical_document_field
1641    DROP COLUMN uuid;
1642
1643ALTER TABLE ehr.custom_field_type
1644    DROP COLUMN uuid;
1645
1646ALTER TABLE ehr.medical_document
1647    DROP COLUMN uuid;
1648
1649ALTER TABLE ehr.outpatient_template
1650    DROP COLUMN uuid;
1651
1652ALTER TABLE ehr.healthcareprovider_enabled_documents
1653    DROP COLUMN uuid;
1654
1655ALTER TABLE demographics.party_relationship
1656    DROP COLUMN uuid;
1657
1658ALTER TABLE demographics.speciality
1659    DROP COLUMN uuid;
1660
1661ALTER TABLE demographics.speciality
1662    ADD COLUMN short_name text;
1663
1664ALTER TABLE ehr.custom_field
1665    DROP COLUMN uuid;
1666
1667ALTER TABLE demographics.schedule
1668    DROP COLUMN uuid,
1669    DROP COLUMN "type",
1670    DROP COLUMN type_description,
1671    DROP COLUMN participation_duration,
1672    DROP COLUMN max_participant_count,
1673    DROP COLUMN room_id;
1674
1675ALTER TABLE demographics.schedule
1676    ADD COLUMN is_patient_visible boolean DEFAULT true,
1677    ADD COLUMN color text,
1678    ADD COLUMN schedule_title text,
1679    ADD COLUMN created_by_id bigint,
1680    ADD COLUMN created_at timestamp;
1681
1682ALTER TABLE demographics.party
1683    DROP COLUMN uuid,
1684    DROP COLUMN address_uuid,
1685    DROP COLUMN visible_for_doctor,
1686    DROP COLUMN description,
1687    DROP COLUMN health_care_provider_id,
1688    DROP COLUMN photo,
1689    DROP COLUMN representative_visit_rules_description,
1690    DROP COLUMN tlk_name,
1691    DROP COLUMN "type",
1692    DROP COLUMN sveidra_id,
1693    DROP COLUMN gp_uuid,
1694    DROP COLUMN gp_doctor_id,
1695    DROP COLUMN gp_healthcare_provider_id,
1696    DROP COLUMN gp_subscription_status,
1697    DROP COLUMN gp_comments,
1698    DROP COLUMN gp_sveidra_workplace_id,
1699    DROP COLUMN gp_sveidra_workplace_no,
1700    DROP COLUMN gp_date,
1701    DROP COLUMN p_uuid,
1702    DROP COLUMN p_doctor_id,
1703    DROP COLUMN p_healthcare_provider_id,
1704    DROP COLUMN p_subscription_status,
1705    DROP COLUMN p_comments,
1706    DROP COLUMN p_date,
1707    DROP COLUMN next_recipe_passport_no,
1708    DROP COLUMN p_subscription_no,
1709    DROP COLUMN gp_subscription_no,
1710    DROP COLUMN espbi_id;
1711
1712ALTER TABLE files.stored_file_type
1713    DROP COLUMN uuid;
1714
1715ALTER TABLE files.stored_file_type
1716    ADD COLUMN code text;
1717
1718ALTER TABLE management.multiple_setting_value
1719    DROP COLUMN id,
1720    DROP COLUMN uuid;
1721
1722ALTER TABLE payment.service_payment
1723    DROP COLUMN uuid,
1724    DROP COLUMN charged_person_id,
1725    DROP COLUMN cashier_id,
1726    DROP COLUMN payment_type,
1727    DROP COLUMN accepted_payment_amount,
1728    DROP COLUMN accepted_currency,
1729    DROP COLUMN paid_amount,
1730    DROP COLUMN paid_currency,
1731    DROP COLUMN exchange_rate,
1732    DROP COLUMN receiver_organisation,
1733    DROP COLUMN note,
1734    DROP COLUMN invoice_code;
1735
1736ALTER TABLE payment.service
1737    DROP COLUMN uuid,
1738    DROP COLUMN health_care_provider_id,
1739    DROP COLUMN "cost",
1740    DROP COLUMN is_vat;
1741
1742ALTER TABLE payment.service
1743    ADD COLUMN company_id bigint,
1744    ADD COLUMN base_cost_id bigint,
1745    ADD COLUMN third_party_code_subscribed character varying ( 255 ),
1746    ADD COLUMN is_payment_required boolean NOT NULL DEFAULT false,
1747    ADD COLUMN tlk_service_code character varying ( 255 ),
1748    ADD COLUMN tlk_intervention_code character varying ( 255 ),
1749    ADD COLUMN service_secondary_group_id bigint,
1750    ADD COLUMN profitability integer NOT NULL DEFAULT 0,
1751    ADD COLUMN created_at timestamp NOT NULL,
1752    ADD COLUMN created_by_id bigint NOT NULL,
1753    ADD COLUMN modified_at timestamp,
1754    ADD COLUMN modified_by_id bigint,
1755    ADD COLUMN vat_rate numeric,
1756    ADD COLUMN service_centre_id bigint,
1757    ADD COLUMN service_subdivision_id bigint,
1758    ADD COLUMN advance_payment_cost numeric ( 19, 5 );
1759
1760ALTER TABLE registration.appointment
1761    DROP COLUMN uuid,
1762    DROP COLUMN doctor_rejection_reason,
1763    DROP COLUMN representative_rejection_reason,
1764    DROP COLUMN created_by,
1765    DROP COLUMN representative_id,
1766    DROP COLUMN enterprise_id,
1767    DROP COLUMN name,
1768    DROP COLUMN health_care_provider_rejection_reason,
1769    DROP COLUMN enterprise_rejection_reason,
1770    DROP COLUMN schedule_id,
1771    DROP COLUMN doctor_owns_card,
1772    DROP COLUMN patient_id,
1773    DROP COLUMN institution_id;
1774
1775ALTER TABLE messaging.message_template
1776    DROP COLUMN uuid;
1777
1778ALTER TABLE messaging.message
1779    DROP COLUMN uuid;
1780
1781ALTER TABLE demographics.workplace
1782    DROP COLUMN uuid,
1783    DROP COLUMN sveidra_workplace_id,
1784    DROP COLUMN sveidra_workplace_no,
1785    DROP COLUMN espbi_id;
1786
1787ALTER TABLE ehr.phrase
1788    DROP COLUMN uuid;
1789
1790ALTER TABLE management.user
1791    DROP COLUMN uuid;
1792
1793ALTER TABLE payment.person_service
1794    ADD CONSTRAINT person_service_employee_fkey FOREIGN KEY ( employee_id ) REFERENCES demographics . party ( id ),
1795    ADD CONSTRAINT person_service_receiver_fkey FOREIGN KEY ( receiver_id ) REFERENCES demographics . party ( id ),
1796    ADD CONSTRAINT person_service_collection_fkey FOREIGN KEY ( collection_id ) REFERENCES payment . collection ( id ),
1797    ADD CONSTRAINT person_service_hcp_fkey FOREIGN KEY ( healthcare_provider_id ) REFERENCES demographics . party ( id ),
1798    ADD CONSTRAINT person_service_service_fkey FOREIGN KEY ( service_id ) REFERENCES payment . service ( id ),
1799    ADD CONSTRAINT person_service_patient_group_fkey FOREIGN KEY ( patient_group_id ) REFERENCES marketing . patient_group ( id );
1800
1801ALTER TABLE prescription.erecipe
1802    ADD CONSTRAINT erecipe_patient_fkey FOREIGN KEY ( patient_id ) REFERENCES demographics . party ( id ),
1803    ADD CONSTRAINT erecipe_transcriber_fkey FOREIGN KEY ( transcriber_id ) REFERENCES demographics . party ( id ),
1804    ADD CONSTRAINT erecipe_doctor_fkey FOREIGN KEY ( doctor_id ) REFERENCES demographics . party ( id ),
1805    ADD CONSTRAINT erecipe_hcp_fkey FOREIGN KEY ( hcp_id ) REFERENCES demographics . party ( id );
1806
1807ALTER TABLE resource_management.info_screen_entry
1808    ADD CONSTRAINT info_screen_appointment_fkey FOREIGN KEY ( patient_visit_id ) REFERENCES registration . appointment ( id );
1809
1810ALTER TABLE payment.person_service_discounts
1811    ADD CONSTRAINT person_service_discounts_pservice_fkey FOREIGN KEY ( person_service_id ) REFERENCES payment . person_service ( id );
1812
1813ALTER TABLE management.setting
1814    ADD CONSTRAINT setting_user_fk FOREIGN KEY ( user_id ) REFERENCES management . user ( id ),
1815    ADD CONSTRAINT setting_environment_fk FOREIGN KEY ( environment_id ) REFERENCES management . environment ( id ),
1816    ADD CONSTRAINT setting_department_fk FOREIGN KEY ( department_id ) REFERENCES management . department ( id ),
1817    ADD CONSTRAINT setting_rule_fk FOREIGN KEY ( rule_id ) REFERENCES management . rule ( id );
1818
1819ALTER TABLE management.permission
1820    ADD CONSTRAINT permission_user_fk FOREIGN KEY ( user_id ) REFERENCES management . user ( id ),
1821    ADD CONSTRAINT permission_environment_fk FOREIGN KEY ( environment_id ) REFERENCES management . environment ( id ),
1822    ADD CONSTRAINT permission_patter_fk FOREIGN KEY ( pattern_id ) REFERENCES management . pattern ( id );
1823
1824ALTER TABLE core.localized_string
1825    ADD CONSTRAINT localized_string_service_fkey FOREIGN KEY ( service_id ) REFERENCES payment . service ( id );
1826
1827ALTER TABLE demographics.schedule
1828    ADD CONSTRAINT schedule_created_by_fkey FOREIGN KEY ( created_by_id ) REFERENCES demographics . party ( id );
1829
1830ALTER TABLE files.stored_file_type
1831    ADD CONSTRAINT stored_file_type_code_key UNIQUE ( code );
1832
1833ALTER TABLE management.multiple_setting_value
1834    ADD CONSTRAINT msv_setting_fk FOREIGN KEY ( setting_id ) REFERENCES management . setting ( id );
1835
1836ALTER TABLE payment.service_payment
1837    ADD CONSTRAINT sp_payer_id FOREIGN KEY ( payer_id ) REFERENCES demographics . party ( id ),
1838    ADD CONSTRAINT sp_organisation_fk FOREIGN KEY ( organisation_id ) REFERENCES demographics . party ( id ),
1839    ADD CONSTRAINT sp_created_by_fk FOREIGN KEY ( created_by_id ) REFERENCES demographics . party ( id ),
1840    ADD CONSTRAINT sp_deleted_by_fk FOREIGN KEY ( deleted_by_id ) REFERENCES demographics . party ( id );
1841
1842ALTER TABLE payment.service
1843    ADD CONSTRAINT collection_company_fkey FOREIGN KEY ( company_id ) REFERENCES management . company ( id ),
1844    ADD CONSTRAINT service_service_group_fkey FOREIGN KEY ( service_group_id ) REFERENCES payment . service_group ( id ),
1845    ADD CONSTRAINT service_service_type_fkey FOREIGN KEY ( service_type_id ) REFERENCES payment . service_type ( id ),
1846    ADD CONSTRAINT service_base_cost_fkey FOREIGN KEY ( base_cost_id ) REFERENCES payment . "cost" ( id ),
1847    ADD CONSTRAINT modified_by_fkey FOREIGN KEY ( modified_by_id ) REFERENCES demographics . party ( id ),
1848    ADD CONSTRAINT created_by_fkey FOREIGN KEY ( created_by_id ) REFERENCES demographics . party ( id ),
1849    ADD CONSTRAINT service_secondary_group_fkey FOREIGN KEY ( service_secondary_group_id ) REFERENCES payment . service_secondary_group ( id ),
1850    ADD CONSTRAINT service_service_centre_fkey FOREIGN KEY ( service_centre_id ) REFERENCES payment . service_centre ( id ),
1851    ADD CONSTRAINT service_service_subdivision_fkey FOREIGN KEY ( service_subdivision_id ) REFERENCES payment . service_subdivision ( id );
1852
1853ALTER TABLE registration.appointment
1854    ADD CONSTRAINT appointment_created_in_organisation_fkey FOREIGN KEY ( created_in_organisation_id ) REFERENCES demographics . party ( id ),
1855    ADD CONSTRAINT appointment_negotiator_fkey FOREIGN KEY ( negotiator_id ) REFERENCES demographics . party ( id ),
1856    ADD CONSTRAINT appointment_healthcareprovider_fkey FOREIGN KEY ( health_care_provider_id ) REFERENCES demographics . party ( id ),
1857    ADD CONSTRAINT appointment_doctor_fkey FOREIGN KEY ( doctor_id ) REFERENCES demographics . party ( id ),
1858    ADD CONSTRAINT appointment_speciality_fkey FOREIGN KEY ( speciality_id ) REFERENCES demographics . speciality ( id ),
1859    ADD CONSTRAINT appointment_visitor_fkey FOREIGN KEY ( visitor_id ) REFERENCES demographics . party ( id ),
1860    ADD CONSTRAINT appointment_visitor_organisation_fkey FOREIGN KEY ( visitor_organisation_id ) REFERENCES demographics . party ( id ),
1861    ADD CONSTRAINT appointment_created_by_fkey FOREIGN KEY ( created_by_id ) REFERENCES demographics . party ( id ),
1862    ADD CONSTRAINT appointment_modified_by_fkey FOREIGN KEY ( modified_by_id ) REFERENCES demographics . party ( id ),
1863    ADD CONSTRAINT appointment_deleted_by_fkey FOREIGN KEY ( deleted_by_id ) REFERENCES demographics . party ( id );
1864
1865ALTER TABLE messaging.message_template
1866    ADD CONSTRAINT message_template_owner_company_id_fkey FOREIGN KEY ( owner_company_id ) REFERENCES management . company ( id ),
1867    ADD CONSTRAINT message_template_owner_id_fkey FOREIGN KEY ( owner_id ) REFERENCES demographics . party ( id ),
1868    ADD CONSTRAINT message_template_owner_organisation_id_fkey FOREIGN KEY ( owner_organisation_id ) REFERENCES demographics . party ( id );
1869
1870ALTER TABLE management.user
1871    ADD CONSTRAINT user_two_factor_disabled_by_fk FOREIGN KEY ( two_factor_disabled_by_id ) REFERENCES demographics . party ( id );
1872
1873CREATE FUNCTION registration.log_appointment ( ) RETURNS trigger AS $func$ 
1874DECLARE
1875	patient text  := null;
1876	doctor text   := null;
1877BEGIN
1878	IF TG_OP = 'UPDATE' THEN
1879		IF OLD != NEW THEN
1880			if OLD.doctor_id != NEW.doctor_id THEN
1881				SELECT firstname || ' ' || lastname || ' (' || coalesce(code, to_char(birth_date, 'YYYY-MM-DD')) || ')' INTO doctor FROM demographics.party where id = OLD.doctor_id;
1882			END IF;
1883			
1884			if OLD.visitor_id != NEW.visitor_id THEN
1885				SELECT firstname || ' ' || lastname || ' (' || coalesce(code, to_char(birth_date, 'YYYY-MM-DD')) || ')' INTO patient FROM demographics.party where id = OLD.visitor_id;
1886			END IF;
1887		
1888			INSERT INTO registration.appointment_log VALUES (now(), session_user::text, current_setting('application_name'), inet_client_addr(), 'U', doctor, patient, OLD.*);
1889		END IF;
1890	ELSIF TG_OP = 'DELETE'  THEN
1891		SELECT firstname || ' ' || lastname || ' (' || coalesce(code, to_char(birth_date, 'YYYY-MM-DD')) || ')' INTO doctor FROM demographics.party where id = OLD.doctor_id;
1892		SELECT firstname || ' ' || lastname || ' (' || coalesce(code, to_char(birth_date, 'YYYY-MM-DD')) || ')' INTO patient FROM demographics.party where id = OLD.visitor_id;
1893		
1894		INSERT INTO registration.appointment_log VALUES (now(), session_user::text, current_setting('application_name'), inet_client_addr(), 'D', doctor, patient, OLD.*);
1895	END IF;
1896		
1897	RETURN NEW;
1898END;
1899 $func$
1900LANGUAGE plpgsql;
1901
1902CREATE FUNCTION demographics.log_patient_hcp_data ( ) RETURNS trigger AS $func$ 
1903BEGIN
1904	IF TG_OP = 'UPDATE' THEN
1905		IF OLD != NEW THEN
1906			insert into demographics.patient_hcp_data_log VALUES (now(), session_user::text, current_setting('application_name'), inet_client_addr(), 'U', OLD.*);
1907		END IF;
1908	ELSIF TG_OP = 'DELETE'  THEN
1909		INSERT INTO demographics.patient_hcp_data_log VALUES (now(), session_user::text, current_setting('application_name'), inet_client_addr(), 'D', OLD.*);
1910	END IF;
1911	RETURN NEW;
1912END;
1913 $func$
1914LANGUAGE plpgsql;
1915
1916CREATE FUNCTION payment.get_patient_debt ( bigint, bigint ) RETURNS numeric AS $func$ 
1917	SELECT 
1918		sum(GREATEST(ps.cost - COALESCE(( 
1919			SELECT sum(pps.amount) AS sum
1920			FROM payment.paid_person_service pps
1921			WHERE pps.is_deleted = false AND pps.person_service_id = ps.id), 0), 0.0)
1922		)
1923	FROM payment.person_service ps
1924	JOIN payment.service serv ON ps.service_id = serv.id
1925	WHERE ps.cost > 0 AND (ps.status = 'provided' OR (serv.is_payment_required AND ps.test_order_id = null))
1926		AND ps.deleted_at IS NULL AND ps.receiver_id = $1 AND ps.healthcare_provider_id = $2
1927	GROUP BY ps.receiver_id, ps.healthcare_provider_id; $func$
1928LANGUAGE sql
1929STABLE
1930STRICT;
1931
1932COMMENT ON FUNCTION payment."get_patient_debt(bigint, bigint)" ( bigint, bigint ) IS 'Used DebtorManager to get patient debt for HCP.' ;
1933
1934CREATE FUNCTION demographics.log_patient_consent ( ) RETURNS trigger AS $func$ 
1935BEGIN
1936	IF (TG_OP = 'DELETE')  THEN
1937		INSERT INTO demographics.patient_consent_log VALUES (now(), session_user::text, current_setting('application_name'), inet_client_addr(), 'D', OLD.*);
1938	RETURN NEW;
1939	END IF;
1940END;
1941 $func$
1942LANGUAGE plpgsql;
1943
1944CREATE FUNCTION demographics.log_party ( ) RETURNS trigger AS $func$ 
1945BEGIN
1946	IF TG_OP = 'UPDATE' THEN
1947		IF OLD != NEW THEN
1948			insert into demographics.party_log VALUES (now(), session_user::text, current_setting('application_name'), inet_client_addr(), 'U', OLD.*);
1949		END IF;
1950	ELSIF TG_OP = 'DELETE'  THEN
1951		INSERT INTO demographics.party_log VALUES (now(), session_user::text, current_setting('application_name'), inet_client_addr(), 'D', OLD.*);
1952	END IF;
1953		
1954	RETURN NEW;
1955END;
1956 $func$
1957LANGUAGE plpgsql;
1958
1959CREATE FUNCTION payment.has_patient_debt ( bigint, bigint ) RETURNS boolean AS $func$ SELECT payment.get_patient_debt($1, $2) > 0 $func$
1960LANGUAGE sql
1961STABLE
1962STRICT;
1963
1964COMMENT ON FUNCTION payment."has_patient_debt(bigint, bigint)" ( bigint, bigint ) IS 'Used in PatientVisitMap and TaskManager to check if patient has debt in HCP.' ;
1965
1966CREATE TRIGGER appointment_log
1967    AFTER DELETE OR UPDATE ON registration.appointment
1968    FOR EACH ROW
1969    EXECUTE PROCEDURE registration.log_appointment ( );
1970
1971CREATE TRIGGER patient_hcp_data_log
1972    AFTER DELETE OR UPDATE ON demographics.patient_hcp_data
1973    FOR EACH ROW
1974    EXECUTE PROCEDURE demographics.log_patient_hcp_data ( );
1975
1976DROP FUNCTION public.is_date_in_code ( character varying );
1977
1978DROP FUNCTION public.get_birth_date_from_code ( character varying );
1979
1980COMMIT TRANSACTION;