

--
-- Name: ci_category; Type: TABLE; Schema: bill; Owner: gm-dbo; Tablespace: 
--

/*
CREATE TABLE ci_category (
    pk serial primary key,
    description character varying DEFAULT 'Sonstige'::character varying NOT NULL
); */

CREATE OR REPLACE VIEW bill.ci_category AS 
 SELECT data_source.pk, data_source.name_short AS description
   FROM ref.data_source
  WHERE data_source.pk >= 10 AND data_source.pk <= 17;

/*
INSERT INTO ci_category (pk, description) VALUES (1, 'Sonstige');
INSERT INTO ci_category (pk, description) VALUES (2, 'Kassengebuehren');
INSERT INTO ci_category (pk, description) VALUES (3, 'Konsultation');
INSERT INTO ci_category (pk, description) VALUES (4, 'Behandlung');
INSERT INTO ci_category (pk, description) VALUES (11, 'GöPz');
INSERT INTO ci_category (pk, description) VALUES (12, 'EBMz');
*/

GRANT SELECT ON TABLE ci_category TO "gm-public";
GRANT SELECT ON TABLE ci_category TO "gm-doctors";




--
-- Name: charge_item; Type: TABLE; Schema: bill; Owner: gm-dbo; Tablespace: 
--

CREATE TABLE charge_item (
    pk serial primary key not null,
    fk_category integer DEFAULT 1 NOT NULL 
              -- references ci_category(pk),
		references ref.data_source (pk),
    description text NOT NULL,
    amount double precision DEFAULT 1 NOT NULL,
    vat smallint DEFAULT 0 NOT NULL,
    is_discontinued boolean DEFAULT false NOT NULL, -- you shouldnt use this anymore for whatever reason
    discountable boolean DEFAULT false NOT NULL,
    code character varying DEFAULT '1001'::character varying NOT NULL
    CONSTRAINT charge_item_vat_check CHECK ((vat >= 0))
)

;

INSERT INTO charge_item (pk, code, fk_category, description, amount, vat, is_discontinued, discountable) VALUES (1, '1001', 2, 'Praxisgebuehr pro Quartal', 10, 0, false, false);
INSERT INTO charge_item (pk, code, fk_category, description, amount, vat, is_discontinued, discountable) VALUES (2, '1001', 4, 'Pauschale 30 Min.', 45, 19, false, true);
INSERT INTO charge_item (pk, code, fk_category, description, amount, vat, is_discontinued, discountable) VALUES (26550, 'GS001', 1, 'Gutschrift allgm.', 1, 0, false, false);
INSERT INTO charge_item (pk, code, fk_category, description, amount, vat, is_discontinued, discountable) VALUES (2120, '4118', 11, 'Vitamin AResorptionstest (Zweimalige Bestimmung von Vitamin A)', 41.9699999999999989, 0, false, false);
INSERT INTO charge_item (pk, code, fk_category, description, amount, vat, is_discontinued, discountable) VALUES (14615, '80032D', 12, 'Praxisgebühr befreit (DMP)', 0, 0, false, false);
INSERT INTO charge_item (pk, code, fk_category, description, amount, vat, is_discontinued, discountable) VALUES (14650, '80094', 12, 'Kenn-Nr. für Fälle, der Sonstigen Kostenträger, die von der Praxisgebühr befreit sind', 0, 0, false, false);
INSERT INTO charge_item (pk, code, fk_category, description, amount, vat, is_discontinued, discountable) VALUES (19101, '01829', 12, 'Test bei ungeklärtem Schutz gegen eine Röteln-Infektion', 130, 0, false, false);
INSERT INTO charge_item (pk, code, fk_category, description, amount, vat, is_discontinued, discountable) VALUES (22125, '32575', 12, 'Nachweis von HIV-1- und/oder HIV-1/2- Antikörpern (Immunoassay)', 0, 0, false, false);
INSERT INTO charge_item (pk, code, fk_category, description, amount, vat, is_discontinued, discountable) VALUES (8031, '01435', 12, 'Haus-/Fachärztl. Bereitschaftspauschale', 250, 0, false, false);
INSERT INTO charge_item (pk, code, fk_category, description, amount, vat, is_discontinued, discountable) VALUES (24514, '93036A', 12, 'Netz-Nr.: Hausbesuchszuschlag', 0, 0, false, false);
INSERT INTO charge_item (pk, code, fk_category, description, amount, vat, is_discontinued, discountable) VALUES (26008, '96321', 12, 'INN: Irinotecan (1mg)', 0, 0, false, false);
INSERT INTO charge_item (pk, code, fk_category, description, amount, vat, is_discontinued, discountable) VALUES (18882, '01435', 12, 'Telefonische Beratung im Zusammenhang mit einer Erkrankung', 60, 0, false, false);
INSERT INTO charge_item (pk, code, fk_category, description, amount, vat, is_discontinued, discountable) VALUES (18981, '01734M', 12, 'Untersuchung auf Blut im Stuhl (3 Testbriefchen)', 50, 0, false, false);
INSERT INTO charge_item (pk, code, fk_category, description, amount, vat, is_discontinued, discountable) VALUES (22396, '34230', 12, 'Röntgenaufnahmen von Teilen des Skeletts oder des Kopfes', 210, 0, false, false);





GRANT SELECT ON TABLE charge_item TO "gm-public";
GRANT SELECT ON TABLE charge_item TO "gm-doctors";

alter table clin.encounter_type add
 fk_bill_ci integer references bill.charge_item (pk);



update clin.encounter_type set fk_bill_ci = 2 where pk IN (1,3);





--
-- Name: bill_rec; Type: TABLE; Schema: bill; Owner: gm-dbo; Tablespace: 
--

CREATE TABLE bill_rec (
    pk serial primary key NOT NULL,
--     fk_patient integer not null references dem.identity(pk), -- planned
    fk_encounter integer NOT NULL references clin.encounter(pk),
    charge_date date DEFAULT (now())::date NOT NULL, -- the date this line was added
    fk_charge_item integer NOT NULL references charge_item (pk), -- reference to clin.coding_root_system
    qty double precision DEFAULT 1 NOT NULL, -- misnamed, is used as discount currently
    amount double precision, -- the predefined amount per unit without vat might be overriden here
    details text DEFAULT ''::text NOT NULL, -- your staff might enter some explaining words here, reference numbers etc.
    fk_provider integer references dem.staff(pk),-- who
    status character varying DEFAULT 'pending'::character varying NOT NULL , --@@enum
    fk_invoice integer references bill.invoice (pk) -- the invoice were on -if
)
;



GRANT SELECT,INSERT,UPDATE ON TABLE bill_rec TO "gm-staff";
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE bill_rec TO "gm-doctors";


--
-- Name: v_bill_rec; Type: VIEW; Schema: bill; Owner: gm-dbo; Tablespace: 
--

CREATE OR REPLACE VIEW bill.v_bill_rec AS 
 SELECT idl.pk, idl.fk_encounter, idl.charge_date, idl.fk_charge_item, ci.code, cig.description AS ci_category_descrip, ci.description AS ci_descrip, idl.qty, COALESCE(idl.amount, ci.amount) AS amount, idl.details, idl.modified_by, idl.status, idl.fk_invoice
   FROM bill.bill_rec idl
   JOIN bill.charge_item ci ON idl.fk_charge_item = ci.pk
   JOIN bill.ci_category cig ON ci.fk_category = cig.pk;


grant select on v_bill_rec to "gm-staff";
grant select on v_bill_rec to "gm-doctors";


CREATE OR REPLACE VIEW bill.v_enc AS 
-- what should be displayed in the encounter combo box when editing a bill record
 SELECT v_pat_encounters.pk_encounter, (((((v_pat_encounters.pk_encounter || ' - '::text) || v_pat_encounters.l10n_type) || ' reason: '::text) || COALESCE(v_pat_encounters.reason_for_encounter, ''::text)) || ' started: '::text) || to_char(v_pat_encounters.started, 'DD.MM.YYYY HH24:MI'::text) AS display_text, v_pat_encounters.pk_patient, v_pat_encounters.started::date AS started
   FROM clin.v_pat_encounters
  ORDER BY v_pat_encounters.pk_encounter;
grant select on bill.v_enc to "gm-public";







CREATE TABLE bill.invoice
(
  pk serial NOT NULL,
  fk_identity integer NOT NULL, -- NOT the receiver of the invoice, the patient were invoicing for
  payment_method character varying NOT NULL DEFAULT 'cash'::character varying, -- the customer is supposed to pay via...
  close_date timestamp with time zone, -- can further add records to until this is not null
  total_amount numeric(8,2), -- filled during close  
  copy_inv_address text, -- this is the address of the receiver of the invoice, retrieved at close time
  fk_doc integer, -- we dont use it
  CONSTRAINT invoice_pkey PRIMARY KEY (pk ),
  CONSTRAINT invoice_fk_doc_fkey FOREIGN KEY (fk_doc)
      REFERENCES blobs.doc_med (pk) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT invoice_fk_identity_fkey FOREIGN KEY (fk_identity)
      REFERENCES dem.identity (pk) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
;
GRANT SELECT, UPDATE, INSERT ON TABLE bill.invoice TO "gm-doctors";
GRANT SELECT, UPDATE, INSERT ON TABLE bill.invoice TO "gm-staff";
-- everyone can create and close a invoice



set search_path = clin, pg_catalog;
--
-- dont remove encounters if a bill_rec refers to it
--
CREATE OR REPLACE FUNCTION clin.remove_old_empty_encounters(integer, interval)
  RETURNS boolean AS
$BODY$
DECLARE
	_pk_identity alias for $1;
	_defined_minimum_encounter_age alias for $2;
	_usable_minimum_encounter_age interval;
BEGIN
	-- does person exist ?
	perform 1 from dem.identity where pk = _pk_identity;
	if not found then
		raise exception 'clin.remove_old_empty_encounters(%, %): person [%] does not exist', _pk_identity, _defined_minimum_encounter_age, _pk_identity;
		return false;
	end if;
	if _defined_minimum_encounter_age < '3 days'::interval then
		_usable_minimum_encounter_age := '3 days'::interval;
	else
		_usable_minimum_encounter_age := _defined_minimum_encounter_age;
	end if;
	DELETE FROM clin.encounter WHERE
		clin.encounter.fk_patient = _pk_identity
			AND
		age(clin.encounter.last_affirmed) > _usable_minimum_encounter_age
			AND
		NOT EXISTS (SELECT 1 FROM clin.clin_root_item WHERE fk_encounter = clin.encounter.pk)
			AND
		NOT EXISTS (SELECT 1 FROM blobs.doc_med WHERE fk_encounter = clin.encounter.pk)
			AND
		NOT EXISTS (SELECT 1 FROM clin.episode WHERE fk_encounter = clin.encounter.pk)
			AND
		NOT EXISTS (SELECT 1 FROM clin.health_issue WHERE fk_encounter = clin.encounter.pk)
			AND
		NOT EXISTS (SELECT 1 FROM clin.allergy_state WHERE fk_encounter = clin.encounter.pk)
	
			AND --new
		NOT EXISTS (SELECT 1 FROM bill.bill_rec WHERE fk_encounter = clin.encounter.pk)


	;
	return true;
END;$BODY$
  LANGUAGE plpgsql 
;





CREATE OR REPLACE FUNCTION clin.add_default_charge_enctype_driven()
  RETURNS trigger AS
$BODY$Begin
 -- insert the default charge item for an encounter type
 --   @@what if the type is modified during the encounter session

insert into bill.bill_rec ( fk_encounter, charge_date, fk_charge_item, details )
 select NEW.pk, now()::date, ci.pk, ci.description
 from bill.charge_item ci where ci.pk = (
        select et.fk_bill_ci from clin.encounter_type et
        where et.pk = NEW.fk_type
)
;
return NEW;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
;



CREATE TRIGGER tg_add_default_charge 
  AFTER INSERT 
  ON clin.encounter
  --DEFERRABLE INITIALLY IMMEDIATE
  FOR EACH ROW
  EXECUTE PROCEDURE clin.add_default_charge_enctype_driven();



