/* vytvoreni domen */

CREATE DOMAIN retezec AS VARCHAR(45) CHARACTER SET WIN1250 NOT NULL CHECK (VALUE NOT LIKE ' %' AND VALUE NOT LIKE '% ') COLLATE WIN1250;
CREATE DOMAIN ano_ne AS VARCHAR(3) DEFAULT 'ne' CHECK (VALUE IN ('ano','ne')) NOT NULL;
CREATE DOMAIN klic AS INTEGER CHECK (VALUE>0);
CREATE DOMAIN ip AS VARCHAR(40) NOT NULL CHECK (VALUE LIKE '_%._%._%._%' OR VALUE LIKE '_%:_%:_%:_%:_%:_%:_%:_%');

COMMIT WORK;

/* vytvoreni tabulek */

CREATE TABLE pobocka
(
  id_pobocky klic NOT NULL,
  ulice retezec,
  cp retezec,
  mesto retezec,
  psc INTEGER CHECK (psc BETWEEN 10000 AND 99999),
  cena_za_min klic DEFAULT 1 NOT NULL,
  CONSTRAINT pobocka_pk PRIMARY KEY(id_pobocky)
);
COMMIT WORK;

CREATE TABLE pocitac
(
  id_stanice klic NOT NULL,
  cislo retezec,
  ip_adresa ip,
  funkcni ano_ne,
  posledni_pouziti DATE,
  pobocka klic,
  CONSTRAINT pocitac_pk PRIMARY KEY(id_stanice),
  CONSTRAINT pocitac_uniq UNIQUE(ip_adresa),
  CONSTRAINT pocitac_fk FOREIGN KEY (pobocka) REFERENCES pobocka (id_pobocky) ON DELETE SET NULL ON UPDATE CASCADE
);
COMMIT WORK;

CREATE TABLE zamestnanec
(
  id_zamestnance klic NOT NULL,
  jmeno retezec,
  prijmeni retezec,
  rodne_cislo VARCHAR(11) CHECK (rodne_cislo LIKE '______/___%'),
  pobocka klic NOT NULL,
  CONSTRAINT zamest_pk PRIMARY KEY (id_zamestnance),
  CONSTRAINT zamest_fk FOREIGN KEY (pobocka) REFERENCES pobocka (id_pobocky) ON DELETE CASCADE ON UPDATE CASCADE
);
COMMIT WORK;

CREATE TABLE pouziti
(
  pocitac klic NOT NULL,
  den DATE NOT NULL,
  cas_od TIME NOT NULL,
  cas_do TIME,
  zamestnanec klic,
  zaplaceno ano_ne,
  CONSTRAINT cas_od_do CHECK (cas_od<cas_do),
  CONSTRAINT pouziti_pk PRIMARY KEY (pocitac, den, cas_od),
  CONSTRAINT pouziti_fk_poc FOREIGN KEY (pocitac) REFERENCES pocitac (id_stanice) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT pouziti_fk_zam FOREIGN KEY (zamestnanec) REFERENCES zamestnanec (id_zamestnance) ON DELETE SET NULL ON UPDATE CASCADE
);
COMMIT WORK;


/* vkladani dat do tabulek: nepouzijeme INSERT..., ale udelame to pomoci procedur (viz nize) */

/* vytvoreni generatoru (sekvenci) */

CREATE SEQUENCE s_pobocka;
CREATE SEQUENCE s_pocitac;
CREATE SEQUENCE s_zamestnanec;

/* vytvoreni vyjimky */

CREATE EXCEPTION chyba 'Nastala nejaka chyba.';

COMMIT WORK;


/* neselektove procedury pro ukladani dat */

SET TERM ^;
CREATE PROCEDURE pobocka_pridej (ulice VARCHAR(45), cp VARCHAR(45), mesto VARCHAR(45), psc INTEGER, cena_za_min INTEGER)
RETURNS (nove_id INTEGER)
AS
BEGIN
  nove_id = GEN_ID(s_pobocka,1);
  INSERT INTO pobocka VALUES(:nove_id, :ulice, :cp, :mesto, :psc, :cena_za_min);
  WHEN ANY DO EXCEPTION chyba 'Nelze ulozit novou pobocku.';
END^
SET TERM ;^
/* pridame nekolik pobocek */
EXECUTE PROCEDURE pobocka_pridej('Kamenická','25/1','Děčín',40502,2);
EXECUTE PROCEDURE pobocka_pridej('Čsl. armády','154','Děčín',40502,3);
EXECUTE PROCEDURE pobocka_pridej('nám. Míru','15/31','Ústí nad Labem',40001,3);


SET TERM ^;
CREATE PROCEDURE pocitac_pridej (cislo VARCHAR(45), ip VARCHAR(40), pobocka INTEGER)
RETURNS (nove_id INTEGER)
AS
BEGIN
  nove_id = GEN_ID(s_pocitac,1);
  INSERT INTO pocitac VALUES(:nove_id, :cislo, :ip, 'ano', NULL, :pobocka);
  WHEN SQLCODE -530 DO EXCEPTION chyba 'Pobocka neexistuje!'; /* jine chyby nehlasime */
END^
SET TERM ;^
/* pridame nekolik pocitacu */
EXECUTE PROCEDURE pocitac_pridej('PL1-20','147.32.22.120',1);
EXECUTE PROCEDURE pocitac_pridej('PL1-15','147.32.22.190',1);
EXECUTE PROCEDURE pocitac_pridej('UL1','109.10.10.1',3);
EXECUTE PROCEDURE pocitac_pridej('UL2','109.10.10.27',3);
EXECUTE PROCEDURE pocitac_pridej('UL3','109.10.10.20',3);


SET TERM ^;
CREATE PROCEDURE zamestnanec_pridej (jm VARCHAR(45), prijm VARCHAR(40), rc VARCHAR(11), pobocka INTEGER)
RETURNS (nove_id INTEGER)
AS
BEGIN
  nove_id = GEN_ID(s_zamestnanec,1);
  INSERT INTO zamestnanec VALUES(:nove_id, :jm, :prijm, :rc, :pobocka);
  WHEN SQLCODE -530 DO EXCEPTION chyba 'Pobocka neexistuje!'; /* jine chyby nehlasime */
END^
SET TERM ;^
/* pridame nekolik zamestnancu */
EXECUTE PROCEDURE zamestnanec_pridej('Eva','Malá','825202/1234',1);
EXECUTE PROCEDURE zamestnanec_pridej('Jan','Kos','790228/1234',1);
EXECUTE PROCEDURE zamestnanec_pridej('Ivo','Nový','821231/1234',2);
EXECUTE PROCEDURE zamestnanec_pridej('Jan','Nový','850425/1234',3);
EXECUTE PROCEDURE zamestnanec_pridej('Oto','Srb','800303/1234',3);


SET TERM ^;
CREATE PROCEDURE pocitac_pujcit (idpoc INTEGER, idzam INTEGER)
AS
DECLARE VARIABLE pom INTEGER;
BEGIN
  /* neni nahodou pujceny? */
  SELECT pocitac FROM pouziti WHERE (pocitac=:idpoc AND den=CAST('today' AS DATE) AND cas_do IS NULL) INTO :pom;
  IF (pom IS NOT NULL) THEN
  BEGIN
    EXCEPTION chyba 'CHYBA: Pocitac je momentalne pujceny!';
    EXIT;
  END

  /* pracuje zamestnanec na pobocce, kde je pocitac? */
  SELECT pobocka FROM zamestnanec WHERE id_zamestnance=:idzam INTO :pom;
  IF (pom IS NULL) THEN
  BEGIN
    EXCEPTION chyba 'CHYBA: Zamestnanec neexistuje!';
    EXIT;
  END
  SELECT id_stanice FROM pocitac WHERE id_stanice=:idpoc AND pobocka=:pom INTO :pom;
  IF (pom IS NULL) THEN
  BEGIN
    EXCEPTION chyba 'CHYBA: Pocitac neexistuje anebo neni na stejne pobocce jako zamestnanec!';
    EXIT;
  END

  /* pokus ulozit data */
  INSERT INTO pouziti VALUES(:idpoc, CAST('today' AS DATE), CAST('now' AS TIME), NULL, :idzam, NULL);

  WHEN ANY DO EXCEPTION chyba 'Pocitac nelze pujcit!';
END^
SET TERM ;^
