CREATE TABLE cli (
    id integer PRIMARY KEY,
    fn ,po ,contact, insur,  uid
);
INSERT INTO cli VALUES(1,'Test','','','',NULL);

CREATE TABLE emr ( id integer primary key,
 encdate, sympt, details, lofch ,
 pat integer, inv integer);
CREATE TABLE cb (
    id integer PRIMARY KEY,
    valdate /* default CURRENT_DATE */,
    amount float,
    details ,
    cli integer
);
CREATE TABLE pat (
    id integer PRIMARY KEY,
    n ,specie ,gender ,dob ,cli integer,regid ,deceased 
, tn integer default 0 not null);
CREATE TRIGGER tg_tn_incr after update on pat
begin
 update pat set tn=tn+1 where id = NEW.id;
end;

CREATE VIEW v_fillcli as
select  cli.id,cli.fn,
  cli.fn || group_concat(pat.n||coalesce(pat.regid,'')) as pattern
 from cli
left outer join pat on ( cli.id=pat.cli)
group by 1
/* v_fillcli(id,fn,pattern) */;


CREATE TABLE appt ( id integer primary key, cli integer, appdt, notes , dur integer default 15);



CREATE TABLE ci (
    descr ,price float,vat integer /* default 19 */ ,cat ,qty integer,
    code  NOT NULL
);
INSERT INTO ci VALUES('Standard Encounter',19.0,0,NULL,NULL,'enc');
INSERT INTO ci VALUES('Baytril Injectable 5 %',0.95,7,'mat',10,'baytri5');

CREATE UNIQUE INDEX ci_ukey on ci ( code) 
;




CREATE TRIGGER tg_adj_ciqty after insert on emr 
BEGIN
 update ci set qty=coalesce(qty,0) -1 where code=NEW.lofch and ci.cat in ('mat');
END;
CREATE TRIGGER tg_adj_ciqty3 after delete on emr 
BEGIN
 update ci set qty=coalesce(qty,0) +1 where code=OLD.lofch and ci.cat in ('mat');
END;
CREATE TRIGGER tg_adj_ciqty2 after update on emr 
when ( old.lofch <> new.lofch ) 
BEGIN
 update ci set qty=coalesce(qty,0) +1 where code=OLD.lofch and ci.cat in ('mat');
 update ci set qty=coalesce(qty,0) -1 where code=NEW.lofch and ci.cat in ('mat');

END;

CREATE VIEW v_pending_liab as 
select pat.cli, max(encdate) as stmt_date ,sum(ci.price * (1+ci.vat/100.0) ) as total_incl ,'Forderung Patient '||group_concat(distinct emr.pat) as details from emr
                join pat on ( emr.pat=pat.id)
                join ci on ( emr.lofch=ci.code ) 
                where emr.inv is null and emr.encdate <= CURRENT_DATE 
                group by 1
/* v_pending_liab(cli,stmt_date,total_incl,details) */;
