-- Vytvo�en� datov�ch struktur pro podvojn� ��etnictv�

-- Aby bylo mo�no p��kazy opakovat, sma�eme si p��padn� star� tabulky
drop table ei_osnova;
drop table ei_hlkniha;
drop table ei_denik;
drop table ei_popis;
drop table ei_poradi;

create table ei_osnova (
    ucet int4,
    anal int4,
    popis varchar(128),
    aktivni float8 not null check (aktivni=1.0 or aktivni=-1.0),
    primary key (ucet, anal)
);

create table ei_hlkniha (
    ucet             int4 not null,
    anal             int4 not null,
    poc_zustatek_md  float8 not null default 0,
    poc_zustatek_dal float8 not null default 0,
    zustatek_md      float8 not null default 0,
    zustatek_dal     float8 not null default 0,
    foreign key (ucet, anal) references ei_osnova(ucet, anal)
                       on update no action on delete no action
);

create table ei_popis (
    cislo int4 primary key,
    popis varchar(128) not null
);

create table ei_denik (
    cislo int4 not null references ei_popis(cislo)
                         on  update no action on delete no action,
    datum date not null default now(),
    mdd   char(1) check (mdd='M' or mdd='D'),
    ucet  int4,
    anal  int4,
    doklad varchar(64),
    castka float8 not null default 0,
    foreign key (ucet, anal) references ei_osnova(ucet, anal)
                       on update no action on delete no action
);

create rule ei_popis_nodelete
    as on delete to ei_popis
    do instead nothing;

create rule ei_denik_nodelete
    as on delete to ei_denik
    do instead nothing;

create table ei_poradi (
   cislo int4 not null
);

insert into ei_poradi values (1);

create rule ei_poradi_noinsert
    as on insert to ei_poradi
    do instead nothing;

create rule ei_poradi_nodelete
    as on delete to ei_poradi
    do instead nothing;

copy ei_osnova from stdin;
211	000	Pen�enka	1
221	100	Bankovn� ��et: Komer�n� banka, b�n� ��et	1
221	101	Bankovn� ��et: Komer�n� banka, finan�n� rezervy	1
221	200	Bankovn� ��et: E-banka, b�n� ��et	1
221	201	Bankovn� ��et: E-banka, b�n� ��et	1
500	100	Spot�ebovan� n�kupy	1
500	200	Za n�jem	1
500	300	Za auto - �dr�ba, opravy, provoz	1
500	301	Za auto - benz�n	1
500	401	N�klady na man�elku	1
500	402	N�klady na p��telkyni	1
518	100	Poplatky v Komer�n� bance	1
518	200	Poplatky v E-bance	1
600	100	V�plata	-1
600	200	Honor��e	-1
600	300	Ostatn� p��jmy	-1
\.