--- GECAMed 1.5.0

CREATE SCHEMA billing;

CREATE TABLE billing.bailiff
	(
 	id				SERIAL PRIMARY KEY,
    name			VARCHAR(128)
    );

CREATE TABLE billing.bailiff_address
	(
    id 				SERIAL PRIMARY KEY,
    bailiff_id 		INTEGER NOT NULL,
    streetname 		VARCHAR(50),
    streetnumber 	VARCHAR(10),
    zip 			VARCHAR(10),
    locality 		VARCHAR(50),
    country 		VARCHAR(50),
    type 			VARCHAR(20),
	date 			DATE,
	locality_id 	INTEGER NOT NULL
	);

ALTER TABLE billing.bailiff_address ADD CONSTRAINT fk_bailiff_locality_id
    FOREIGN KEY (locality_id) REFERENCES address.locality (id) ON DELETE NO ACTION;

CREATE TABLE billing.rel_municipality_bailiff
	(
	id					SERIAL PRIMARY KEY,
	municipality_id 	INTEGER NOT NULL,
	bailiff_id 			INTEGER NOT NULL
	);

ALTER TABLE billing.rel_municipality_bailiff ADD CONSTRAINT fk_rel_municipality_bailiff_municipality
      FOREIGN KEY (municipality_id) REFERENCES address.municipality(id) ON DELETE CASCADE;

ALTER TABLE billing.rel_municipality_bailiff ADD CONSTRAINT fk_rel_municipality_bailiff_bailiff
      FOREIGN KEY (bailiff_id) REFERENCES billing.bailiff(id) ON DELETE CASCADE;


CREATE TABLE billing.bailiff_rate
	(
	id				SERIAL PRIMARY KEY,
	bailiff_id 		INTEGER DEFAULT NULL,
	lower			FLOAT DEFAULT 0,
	upper			FLOAT DEFAULT 0,
	rate			FLOAT DEFAULT 0
	);

ALTER TABLE billing.bailiff_rate ADD CONSTRAINT fk_bailiff_rate_id
    FOREIGN KEY (bailiff_id) REFERENCES billing.bailiff(id) ON DELETE CASCADE;

CREATE TABLE billing.account
	(
	id				SERIAL PRIMARY KEY,
	bankname		VARCHAR(128),
	bic				VARCHAR(11),
	iban			VARCHAR(32),
	label			VARCHAR DEFAULT NULL,
	holder_id		INTEGER DEFAULT NULL,
	obsolete		BOOLEAN DEFAULT FALSE
	);

ALTER TABLE billing.account ADD CONSTRAINT fk_account_holder
     FOREIGN KEY (holder_id) REFERENCES office.physician(id) ON DELETE CASCADE;

CREATE TABLE billing.payment
	(
	id				SERIAL PRIMARY KEY,
	method 			VARCHAR(128)
	);

CREATE TABLE billing.settlement
	(
	id			SERIAL PRIMARY KEY,
	account_id 	INTEGER DEFAULT NULL,
	payment_id 	INTEGER DEFAULT NULL
	);

ALTER TABLE billing.settlement ADD CONSTRAINT fk_settlement_account
      FOREIGN KEY (account_id) REFERENCES billing.account(id) ON DELETE CASCADE;

ALTER TABLE billing.settlement ADD CONSTRAINT fk_settlement_payment
      FOREIGN KEY (payment_id) REFERENCES billing.payment(id) ON DELETE CASCADE;

CREATE TABLE billing.key_value
	(
	id				SERIAL PRIMARY KEY,
    label			VARCHAR (32),
    key_type		INTEGER,
    description		VARCHAR (128),
    value			FLOAT DEFAULT 0,
    applicability	DATE,
    fraction_digits INTEGER DEFAULT 1,
    editable        BOOLEAN DEFAULT 'false',
    protected		BOOLEAN DEFAULT 'false' 
	);

CREATE TABLE billing.suffix
	(
	id				SERIAL PRIMARY KEY,
	letter			CHAR,
	meaning			VARCHAR(512),
	factor			FLOAT DEFAULT 1,
	minimum			FLOAT DEFAULT 0
	);

CREATE TABLE billing.rate_index
	(
	id				SERIAL PRIMARY KEY,
	parent_id		INTEGER,
	label			VARCHAR(32),
	title			VARCHAR(512),
	applicability   DATE
	);

ALTER TABLE billing.rate_index ADD CONSTRAINT fk_index_parent
      FOREIGN KEY (parent_id) REFERENCES billing.rate_index(id) ON DELETE CASCADE;

CREATE TABLE billing.rate
	(
	id                 SERIAL PRIMARY KEY,
	key_type           INTEGER NOT NULL,
	index_id           INTEGER,
	code               VARCHAR,
	label              VARCHAR,	
    applicability      DATE,
 	coefficient        FLOAT   DEFAULT 0,
 	default_adjustment INTEGER DEFAULT 100,
	cat                BOOLEAN DEFAULT 'false',
	cac                BOOLEAN DEFAULT 'false',
	apcm               BOOLEAN DEFAULT 'false',
	acm                BOOLEAN DEFAULT 'false'
	);

ALTER TABLE billing.rate ADD CONSTRAINT fk_rate_index
      FOREIGN KEY (index_id) REFERENCES billing.rate_index(id) ON DELETE CASCADE;

CREATE INDEX idx_rate_code ON billing.rate (code);

CREATE TABLE billing.template
	(
	id				SERIAL PRIMARY KEY,
	user_id			INTEGER,
	label 			VARCHAR(512),
	shortcut		VARCHAR(10),
	sequence_number INTEGER DEFAULT 0
	);

ALTER TABLE billing.template ADD CONSTRAINT fk_template_user
      FOREIGN KEY (user_id) REFERENCES usermanagement.gecamed_user(id);

CREATE TABLE billing.template_rates
	(
	id				SERIAL PRIMARY KEY,
	template_id 	INTEGER,
	rate_id 		INTEGER NOT NULL,
	suffixes 		VARCHAR(8) DEFAULT '',
	quantity		INTEGER DEFAULT 1 NOT NULL
	);

ALTER TABLE billing.template_rates ADD CONSTRAINT fk_rel_template_rate_template
      FOREIGN KEY (template_id) REFERENCES billing.template(id) ON DELETE CASCADE;

ALTER TABLE billing.template_rates ADD CONSTRAINT fk_rel_template_rate_rate
      FOREIGN KEY (rate_id) REFERENCES billing.rate(id) ON DELETE CASCADE;

CREATE TABLE billing.statement
	(
	id					SERIAL PRIMARY KEY,
	account_id			INTEGER,
	state 				INTEGER DEFAULT 0,
	reference			VARCHAR(128),
	start_date			DATE,
	end_date			DATE,
	all_physicians		BOOLEAN DEFAULT 'true',
    closer_id			INTEGER,
	closure_date		TIMESTAMP,
	settlement_date		DATE
	);

ALTER TABLE billing.statement ADD CONSTRAINT fk_statement_closer
     FOREIGN KEY (closer_id) REFERENCES usermanagement.gecamed_user(id);

ALTER TABLE billing.statement ADD CONSTRAINT fk_statement_account
      FOREIGN KEY (account_id) REFERENCES billing.account(id);


CREATE TABLE billing.rel_statement_insurance
	(
	id					SERIAL PRIMARY KEY,
	statement_id 		INTEGER NOT NULL,
	insurance_id 		INTEGER NOT NULL
	);

ALTER TABLE billing.rel_statement_insurance ADD CONSTRAINT fk_rel_statement_insurance_statement
      FOREIGN KEY (statement_id) REFERENCES billing.statement(id) ON DELETE CASCADE;

ALTER TABLE billing.rel_statement_insurance ADD CONSTRAINT fk_rel_statement_insurance_insurance
      FOREIGN KEY (insurance_id) REFERENCES patient.insurance(id) ON DELETE CASCADE;

CREATE TABLE billing.rel_statement_physician
	(
	id					SERIAL PRIMARY KEY,
	statement_id 		INTEGER NOT NULL,
	physician_id 		INTEGER NOT NULL
	);

ALTER TABLE billing.rel_statement_physician ADD CONSTRAINT fk_rel_statement_physician_statement
      FOREIGN KEY (statement_id) REFERENCES billing.statement(id) ON DELETE CASCADE;

ALTER TABLE billing.rel_statement_physician ADD CONSTRAINT fk_rel_statement_physician_physician
      FOREIGN KEY (physician_id) REFERENCES office.physician(id) ON DELETE CASCADE;

CREATE TABLE billing.invoice
	(
	id						SERIAL PRIMARY KEY,
	patient_id				INTEGER,
	physician_id			INTEGER,
	hospitalisation_id		INTEGER,
	class_id				INTEGER,
	insurance_id 			INTEGER,
	third_party_id			INTEGER,
	statement_id			INTEGER,
    site_id                 INTEGER NULL,
    closer_id				INTEGER,
	closure_date			TIMESTAMP,
	modifier_id				INTEGER,
	modification_date		TIMESTAMP,
	invoice_number			VARCHAR(64),
	first_class_required	BOOLEAN DEFAULT 'false',
	created 				DATE,
	printed					TIMESTAMP,
	due 					DATE,
	state 					INTEGER DEFAULT 0,
	old_state 				INTEGER DEFAULT 0,
	accident_number 		VARCHAR(64),
	accident_date			DATE,
	reminders 				INTEGER DEFAULT 0,
	reminder_date			DATE,
	amount 					FLOAT DEFAULT 0,
	payment 				FLOAT DEFAULT 0,
	deduction 				FLOAT DEFAULT 0,
	balance 				FLOAT DEFAULT 0,
	majoration				FLOAT DEFAULT 1,
	settlement_id 			INTEGER,
	settlement_date			DATE,
	med_trans_code 			VARCHAR DEFAULT NULL,
	rule_log 				TEXT
	);

ALTER TABLE billing.invoice ADD CONSTRAINT fk_invoice_patient
      FOREIGN KEY (patient_id) REFERENCES patient.patient(id) ON DELETE CASCADE;

ALTER TABLE billing.invoice ADD CONSTRAINT fk_invoice_physician
      FOREIGN KEY (physician_id) REFERENCES office.physician(id) ON DELETE CASCADE;

ALTER TABLE billing.invoice ADD CONSTRAINT fk_invoice_hospitalisation
      FOREIGN KEY (hospitalisation_id) REFERENCES patient.hospitalisation(id) ON DELETE SET NULL;

ALTER TABLE billing.invoice ADD CONSTRAINT fk_invoice_class
      FOREIGN KEY (class_id) REFERENCES patient.hospitalisation_class(id);

ALTER TABLE billing.invoice ADD CONSTRAINT fk_invoice_insurance
      FOREIGN KEY (insurance_id) REFERENCES patient.insurance(id);

ALTER TABLE billing.invoice ADD CONSTRAINT fk_invoice_thirdparty
      FOREIGN KEY (third_party_id) REFERENCES patient.insurance(id);

ALTER TABLE billing.invoice ADD CONSTRAINT fk_invoice_settlement
      FOREIGN KEY (settlement_id) REFERENCES billing.settlement(id);

ALTER TABLE billing.invoice ADD CONSTRAINT fk_invoice_statement
      FOREIGN KEY (statement_id) REFERENCES billing.statement(id) ON DELETE SET NULL;

ALTER TABLE billing.invoice ADD CONSTRAINT fk_invoice_closer
     FOREIGN KEY (closer_id) REFERENCES usermanagement.gecamed_user(id) ON DELETE SET NULL;
     
ALTER TABLE billing.invoice ADD CONSTRAINT fk_invoice_modifier
     FOREIGN KEY (modifier_id) REFERENCES usermanagement.gecamed_user(id) ON DELETE SET NULL;
     
 ALTER TABLE billing.invoice ADD CONSTRAINT fk_invoice_site_id
    FOREIGN KEY (site_id) REFERENCES office.site (id) ON DELETE NO ACTION;    

CREATE TABLE billing.act
	(
	id						SERIAL PRIMARY KEY,
	invoice_id 				INTEGER,
	physician_id 			INTEGER,
	accession_number		VARCHAR(16),
	performed 				TIMESTAMP,
	show_time				BOOLEAN DEFAULT 'false',
	code 					VARCHAR,
	label 					TEXT,
	quantity				INTEGER,
	coefficient 			FLOAT,
	org_coefficient			FLOAT	DEFAULT NULL,
	fix_amount 				FLOAT 	DEFAULT NULL,
	amount 					FLOAT 	DEFAULT NULL,
	key_value 				FLOAT,
	fraction_digits			INTEGER DEFAULT 1,
	cat 					BOOLEAN DEFAULT 'false',
	cac 					BOOLEAN DEFAULT 'false',
	apcm 					BOOLEAN DEFAULT 'false',
	acm 					BOOLEAN DEFAULT 'false',
	suffixes 				VARCHAR(8),
	hospitalisation_class 	VARCHAR(3) DEFAULT NULL,
	majoration 				FLOAT 	DEFAULT 1,
	med_presc_code			VARCHAR DEFAULT NULL,
	additional_act_info		VARCHAR DEFAULT NULL,
	username				VARCHAR DEFAULT NULL,
	adjustment				INTEGER DEFAULT 100
	);

ALTER TABLE billing.act ADD CONSTRAINT fk_act_invoice
      FOREIGN KEY (invoice_id) REFERENCES billing.invoice(id) ON DELETE CASCADE;

ALTER TABLE billing.act ADD CONSTRAINT fk_act_physician
      FOREIGN KEY (physician_id) REFERENCES office.physician(id) ON DELETE CASCADE;

CREATE INDEX idx_act_accession_number ON billing.act (accession_number);

--- ALTER TABLE billing.act ADD CONSTRAINT un_act_accession_number UNIQUE (accession_number);

CREATE TABLE billing.memo
	(
	id					SERIAL PRIMARY KEY,
	invoice_id 			INTEGER,
	created 				TIMESTAMP,
	author_id			INTEGER,
	note					TEXT
	);

ALTER TABLE billing.memo ADD CONSTRAINT fk_memo_invoice
      FOREIGN KEY (invoice_id) REFERENCES billing.invoice(id) ON DELETE CASCADE;

ALTER TABLE billing.memo ADD CONSTRAINT fk_memo_author
      FOREIGN KEY (author_id) REFERENCES usermanagement.gecamed_user(id);

CREATE TABLE billing.trashed_invoice
	(
	id						SERIAL PRIMARY KEY,
	patient_id				INTEGER,
	physician_id			INTEGER,
	hospitalisation_id		INTEGER,
	class_id				INTEGER,
	insurance_id 			INTEGER,
	third_party_id			INTEGER,
	statement_id			INTEGER,
    --site_id               INTEGER NULL,
    closer_id				INTEGER,
	closure_date			TIMESTAMP,
	modifier_id				INTEGER,
	modification_date		TIMESTAMP,
	invoice_number			VARCHAR(64),
	first_class_required	BOOLEAN DEFAULT 'false',
	created 				DATE,
	printed					TIMESTAMP,
	due 					DATE,
	state 					INTEGER DEFAULT 0,
	old_state 				INTEGER DEFAULT 0,
	accident_number 		VARCHAR(64),
	accident_date			DATE,
	reminders 				INTEGER DEFAULT 0,
	reminder_date			DATE,
	amount 					FLOAT DEFAULT 0,
	payment 				FLOAT DEFAULT 0,
	deduction 				FLOAT DEFAULT 0,
	balance 				FLOAT DEFAULT 0,
	majoration				FLOAT DEFAULT 1,
	settlement_id 			INTEGER,
	settlement_date			DATE,
	med_trans_code 			VARCHAR DEFAULT NULL,
	trasher_id				INTEGER,
	trashing_date			TIMESTAMP
	);

ALTER TABLE billing.trashed_invoice ADD CONSTRAINT fk_trashed_invoice_patient
      FOREIGN KEY (patient_id) REFERENCES patient.patient(id) ON DELETE SET NULL;

ALTER TABLE billing.trashed_invoice ADD CONSTRAINT fk_trashed_invoice_physician
      FOREIGN KEY (physician_id) REFERENCES office.physician(id) ON DELETE SET NULL;

ALTER TABLE billing.trashed_invoice ADD CONSTRAINT fk_trashed_invoice_hospitalisation
      FOREIGN KEY (hospitalisation_id) REFERENCES patient.hospitalisation(id);

ALTER TABLE billing.trashed_invoice ADD CONSTRAINT fk_trashed_invoice_class
      FOREIGN KEY (class_id) REFERENCES patient.hospitalisation_class(id);

ALTER TABLE billing.trashed_invoice ADD CONSTRAINT fk_trashed_invoice_insurance
      FOREIGN KEY (insurance_id) REFERENCES patient.insurance(id);

ALTER TABLE billing.trashed_invoice ADD CONSTRAINT fk_trashed_invoice_thirdparty
      FOREIGN KEY (third_party_id) REFERENCES patient.insurance(id);

ALTER TABLE billing.trashed_invoice ADD CONSTRAINT fk_trashed_invoice_settlement
      FOREIGN KEY (settlement_id) REFERENCES billing.settlement(id);

ALTER TABLE billing.trashed_invoice ADD CONSTRAINT fk_trashed_invoice_statement
      FOREIGN KEY (statement_id) REFERENCES billing.statement(id) ON DELETE SET NULL;

ALTER TABLE billing.trashed_invoice ADD CONSTRAINT fk_trashed_invoice_closer
     FOREIGN KEY (closer_id) REFERENCES usermanagement.gecamed_user(id) ON DELETE SET NULL;
     
ALTER TABLE billing.trashed_invoice ADD CONSTRAINT fk_trashed_invoice_modifier
     FOREIGN KEY (modifier_id) REFERENCES usermanagement.gecamed_user(id) ON DELETE SET NULL;

ALTER TABLE billing.trashed_invoice ADD CONSTRAINT fk_trashed_invoice_trasher
     FOREIGN KEY (trasher_id) REFERENCES usermanagement.gecamed_user(id) ON DELETE SET NULL;


CREATE TABLE billing.trashed_act
	(
	id						SERIAL PRIMARY KEY,
	invoice_id 				INTEGER,
	physician_id 			INTEGER,
	accession_number		VARCHAR(16),
	performed 				TIMESTAMP,
	show_time				BOOLEAN DEFAULT 'false',
	code 					VARCHAR,
	label 					TEXT,
	quantity				INTEGER,
	coefficient 			FLOAT,
	org_coefficient			FLOAT	DEFAULT NULL,
	fix_amount 				FLOAT 	DEFAULT NULL,
	amount 					FLOAT 	DEFAULT NULL,
	key_value 				FLOAT,
	fraction_digits			INTEGER DEFAULT 1,
	cat 					BOOLEAN DEFAULT 'false',
	cac 					BOOLEAN DEFAULT 'false',
	apcm 					BOOLEAN DEFAULT 'false',
	acm 					BOOLEAN DEFAULT 'false',
	suffixes 				VARCHAR(8),
	hospitalisation_class 	VARCHAR(3) DEFAULT NULL,
	majoration 				FLOAT 	DEFAULT 1,
	med_presc_code			VARCHAR DEFAULT NULL,
	additional_act_info		VARCHAR DEFAULT NULL,
	username				VARCHAR DEFAULT NULL,
	adjustment				INTEGER DEFAULT 100
	);

ALTER TABLE billing.trashed_act ADD CONSTRAINT fk_trashed_act_invoice
      FOREIGN KEY (invoice_id) REFERENCES billing.trashed_invoice (id) ON DELETE CASCADE;

 ALTER TABLE billing.trashed_act ADD CONSTRAINT fk_trashed_act_physician
      FOREIGN KEY (physician_id) REFERENCES office.physician(id) ON DELETE SET NULL;
      
      
CREATE TABLE billing.trashed_memo
	(
	id					SERIAL PRIMARY KEY,
	invoice_id 			INTEGER,
	created 			TIMESTAMP,
	author_id			INTEGER,
	note				TEXT
	);

ALTER TABLE billing.trashed_memo ADD CONSTRAINT fk_trashed_memo_invoice
      FOREIGN KEY (invoice_id) REFERENCES billing.trashed_invoice(id) ON DELETE CASCADE;

ALTER TABLE billing.trashed_memo ADD CONSTRAINT fk_trashed_memo_author
      FOREIGN KEY (author_id) REFERENCES usermanagement.gecamed_user(id) ON DELETE SET NULL; 

CREATE TABLE billing.ledger
	(
	id					SERIAL PRIMARY KEY,
	invoice_id			INTEGER,
	cashier_id			INTEGER NOT NULL,
	settlement_id		INTEGER NOT NULL,
	settlement_date		DATE,
	transaction_date	TIMESTAMP DEFAULT now (),
	debit				FLOAT,
	credit				FLOAT
	);

ALTER TABLE billing.ledger ADD CONSTRAINT fk_ledger_invoice
      FOREIGN KEY (invoice_id) REFERENCES billing.invoice(id) ON DELETE SET NULL;

ALTER TABLE billing.ledger ADD CONSTRAINT fk_ledger_cashier
      FOREIGN KEY (cashier_id) REFERENCES usermanagement.gecamed_user(id);

ALTER TABLE billing.ledger ADD CONSTRAINT fk_ledger_settlement
      FOREIGN KEY (settlement_id) REFERENCES billing.settlement(id);

CREATE VIEW billing.invoice_stub         (id, state, old_state, invoice_number, class_id, physician_id, patient_id, patient_first_name, patient_maiden_name, patient_name, patient_ssn, insurance_id, third_party_id, created, due, printed, closure_date, reminders, reminder_date, statement_id, settlement_id, settlement_date, amount, payment, deduction, balance) AS
	SELECT DISTINCT invoice.id, invoice.state, invoice.old_state, invoice.invoice_number, invoice.class_id, invoice.physician_id, patient.id, patient.first_name, patient.maiden_name, patient.sur_name, patient.social_security_number, invoice.insurance_id, invoice.third_party_id, invoice.created, invoice.due, invoice.printed, invoice.closure_date, invoice.reminders, invoice.reminder_date, invoice.statement_id, invoice.settlement_id, invoice.settlement_date, invoice.amount, invoice.payment, invoice.deduction, invoice.balance
    FROM billing.invoice, patient.patient
    WHERE patient.id = invoice.patient_id;

CREATE VIEW billing.trashed_invoice_stub (id, state, old_state, invoice_number, class_id, physician_id, patient_id, patient_first_name, patient_maiden_name, patient_name, patient_ssn, insurance_id, third_party_id, created, due, printed, closure_date, reminders, reminder_date, statement_id, settlement_id, settlement_date, amount, payment, deduction, balance, trasher_id, trashing_date) AS
 	SELECT DISTINCT trashed_invoice.id, trashed_invoice.state, trashed_invoice.old_state, trashed_invoice.invoice_number, trashed_invoice.class_id, trashed_invoice.physician_id, patient.id, patient.first_name, patient.maiden_name, patient.sur_name, patient.social_security_number, trashed_invoice.insurance_id, trashed_invoice.third_party_id, trashed_invoice.created, trashed_invoice.due, trashed_invoice.printed, trashed_invoice.closure_date, trashed_invoice.reminders, trashed_invoice.reminder_date, trashed_invoice.statement_id, trashed_invoice.settlement_id, trashed_invoice.settlement_date, trashed_invoice.amount, trashed_invoice.payment, trashed_invoice.deduction, trashed_invoice.balance, trashed_invoice.trasher_id, trashed_invoice.trashing_date
	FROM billing.trashed_invoice, patient.patient
	WHERE patient.id = trashed_invoice.patient_id;	


CREATE VIEW billing.activity (id, invoice_id, physician_id, patient_id, patient_first_name, patient_maiden_name, patient_name, patient_ssn, accession_number, performed, code, quantity, coefficient, key_value, fraction_digits, suffixes, majoration) AS
 	SELECT DISTINCT act.id, act.invoice_id, act.physician_id, patient.id, patient.first_name, patient.maiden_name, patient.sur_name, patient.social_security_number, act.accession_number, act.performed, act.code, act.quantity, act.coefficient, act.key_value, act.fraction_digits, act.suffixes, invoice.majoration
 	FROM billing.act, billing.invoice, patient.patient
 	WHERE invoice.id = act.invoice_id AND patient.id = invoice.patient_id;
 	
CREATE VIEW billing.transaction (id, invoice_id, physician_id, patient_id, patient_first_name, patient_maiden_name, patient_name, patient_ssn, created, transaction_date, cashier_id, settlement_id, settlement_date, debit, credit) AS
	SELECT DISTINCT ledger.id, ledger.invoice_id, invoice.physician_id, patient.id, patient.first_name, patient.maiden_name, patient.sur_name, patient.social_security_number, invoice.created, ledger.transaction_date, ledger.cashier_id, ledger.settlement_id, ledger.settlement_date, ledger.debit, ledger.credit
	FROM billing.ledger, billing.invoice, patient.patient
	WHERE patient.id = invoice.patient_id AND invoice.id = ledger.invoice_id;
	

	
 	


CREATE INDEX idx_billing_act_invoice_id
	ON billing.act USING btree (invoice_id);
CREATE INDEX idx_billing_invoice_patient_physician_site_id
	ON billing.invoice USING btree (patient_id, physician_id, site_id);
CREATE INDEX idx_billing_rate_code
	ON billing.rate USING btree (code);
