DROP SEQUENCE zchu_seq; 
DROP SEQUENCE doc_seq;

CREATE SEQUENCE zchu_seq; -- Vytvo�� posloupnost pro sd�len� prim. key
CREATE SEQUENCE doc_seq;  -- Vytvo�� posloupnost pro dokumenty.

DROP TABLE MCHU;

CREATE TABLE MCHU(
  id INTEGER PRIMARY KEY DEFAULT nextval('zchu_seq'),
  cis INTEGER NULL UNIQUE,
  status CHAR(1)
DEFAULT 'n' CHECK (status IN ('n','v','z') AND (
  (status = 'n' AND zalozeno IS NOT NULL AND vyhlaseno IS NULL AND zruseno IS NULL) OR
  (status = 'v' AND vyhlaseno IS NOT NULL AND zruseno IS NULL) OR
  (status = 'z' AND zruseno IS NOT NULL AND vyhlaseno IS NOT NULL))),
  zalozeno DATE DEFAULT current_date NULL,
  vyhlaseno DATE NULL CHECK (vyhlaseno >= zalozeno),
  zruseno DATE NULL CHECK (zruseno >= zalozeno AND zruseno >= vyhlaseno),
  rozloha NUMERIC(11,2) NULL CHECK (
(status = 'v' >>> (CAST(rozloha AS DOUBLE PRECISION) > 0.0 AND rozloha IS NOT NULL))),
  nazev VARCHAR(100) NOT NULL UNIQUE CHECK (nazev <> ''),
  vyska_min INTEGER NULL CHECK ((vyska_min > 150 AND vyska_min < 1602) 
AND (status = 'v' >>> vyska_min IS NOT NULL)),
  vyska_max INTEGER NULL CHECK ((vyska_max > 150 AND vyska_max < 1602) 
AND (status = 'v' >>> vyska_max IS NOT NULL)),
  stav_ochr CHAR(1) NULL REFERENCES StavOchr(kod) 
CHECK (status = 'v' >>> stav_ochr IS NOT NULL),
  charakter_uzemi VARCHAR(250) NULL,
  lesni_rezervace BOOLEAN NULL CHECK (status = 'v' >>> lesni_rezervace IS NOT NULL)
);

DROP TABLE VCHU;

CREATE TABLE VCHU(
  id INTEGER PRIMARY KEY DEFAULT nextval('zchu_seq'),
  cis INTEGER NULL UNIQUE,
  status CHAR(1)
DEFAULT 'n' CHECK (status IN ('n','v','z') AND (
  (status = 'n' AND zalozeno IS NOT NULL AND vyhlaseno IS NULL AND zruseno IS NULL) OR
  (status = 'v' AND vyhlaseno IS NOT NULL AND zruseno IS NULL) OR
  (status = 'z' AND zruseno IS NOT NULL AND vyhlaseno IS NOT NULL))),
  zalozeno DATE DEFAULT current_date NULL,
  vyhlaseno DATE NULL CHECK (vyhlaseno >= zalozeno),
  zruseno DATE NULL CHECK (zruseno >= zalozeno AND zruseno >= vyhlaseno),
  rozloha NUMERIC(11,2) NULL CHECK (
(status = 'v' >>> (CAST(rozloha AS DOUBLE PRECISION) > 0.0 AND rozloha IS NOT NULL))),
  nazev VARCHAR(100) NOT NULL UNIQUE CHECK (nazev <> ''),
  vyska_min INTEGER NULL CHECK ((vyska_min > 150 AND vyska_min < 1602) 
AND (status = 'v' >>> vyska_min IS NOT NULL)),
  vyska_max INTEGER NULL CHECK ((vyska_max > 150 AND vyska_max < 1602) 
AND (status = 'v' >>> vyska_max IS NOT NULL)),
  stav_ochr CHAR(1) NULL REFERENCES StavOchr(kod) 
CHECK (status = 'v' >>> stav_ochr IS NOT NULL),
  charakter_uzemi VARCHAR(250) NULL,
  lesni_rezervace BOOLEAN NULL CHECK (status = 'v' >>> lesni_rezervace IS NOT NULL)
);

DROP TABLE PS;

CREATE TABLE PS (
  id INTEGER PRIMARY KEY DEFAULT nextval('zchu_seq'),
  cis INTEGER NULL UNIQUE,
  puv_data BOOLEAN NOT NULL DEFAULT false CHECK (puv_data OR zalozeno IS NOT NULL),
  status CHAR(1)
DEFAULT 'n' CHECK (status IN ('n','v','z') AND (
  (status = 'n' AND zalozeno IS NOT NULL AND vyhlaseno IS NULL AND zruseno IS NULL) OR
  (status = 'v' AND vyhlaseno IS NOT NULL AND zruseno IS NULL) OR
  (status = 'z' AND zruseno IS NOT NULL AND vyhlaseno IS NOT NULL))),
  zalozeno DATE DEFAULT current_date NULL,
  vyhlaseno DATE NULL CHECK (vyhlaseno >= zalozeno),
  zruseno DATE NULL CHECK (zruseno >= zalozeno AND zruseno >= vyhlaseno)
);

DROP TABLE ZCHU_RezervacniKnihy;

CREATE TABLE ZCHU_RezervacniKnihy (
  id INTEGER PRIMARY KEY DEFAULT nextval('doc_seq'), -- teoreticky by bylo mo�n� wf i na dokumentech v RK
  idoo INTEGER NOT NULL,  -- id objektu ochrany
  taboo CHAR(1) NOT NULL CHECK(taboo IN ('m','v','s')), -- bez park�
  isign varchar(50) NOT NULL UNIQUE CHECK (isign <> ''),
  typ CHAR(2) NOT NULL REFERENCES TypDocRK(kod),
  autor_prijmeni VARCHAR(30) NOT NULL CHECK (autor_prijmeni <> ''), 
  autor_jmeno VARCHAR(30) NULL CHECK (autor_jmeno <> ''),
  nazev VARCHAR(100) NOT NULL CHECK (nazev <> ''),
  poznamka VARCHAR(300) NULL,
  rok_vydani INTEGER NULL CHECK (rok_vydani > 1918 AND rok_vydani < EXTRACT(year FROM current_date)),
  zalozeno DATE DEFAULT current_date NOT NULL,
  vyrazeno DATE NULL CHECK (vyrazeno > zalozeno),
  isbn CHAR(12) NULL CHECK (check_ISBN(isbn)),
  zalozil INTEGER NOT NULL REFERENCES AOPKUzivatele(id),
  vyradil INTEGER NULL REFERENCES AOPKUzivatele(id)
CHECK(vyradil IS NOT NULL AND vyrazeno IS NOT NULL),
  url VARCHAR(100) NULL CHECK (url ~* '^(file|http|ftp)://.+')
);