/* VYTVORENI DOMEN */
CREATE DOMAIN id AS
INTEGER
NOT NULL;

CREATE DOMAIN hodnota AS
INTEGER
NOT NULL
CHECK (value > 0);

CREATE DOMAIN text AS
VARCHAR(50) CHARACTER SET UTF8
NOT NULL COLLATE UTF8;

CREATE DOMAIN datum AS
DATE
NOT NULL;



/* VYTVORENI TABULEK */
CREATE TABLE ZAKAZNIK (
       IDZAKAZNIKA    ID,
       JMENO          TEXT,
       PRIJMENI       TEXT,
       PRIMARY KEY (IDZAKAZNIKA)
);

CREATE TABLE SKUPINA(
       IDSKUPINY     ID,
       SPECIALIZACE  TEXT,
       PRIMARY KEY (IDSKUPINY)
);

CREATE TABLE ZAKAZKA (
       IDSKUPINY      ID,
       IDZAKAZNIKA    ID,
       CENA           HODNOTA,
       DATUMZADANI    DATUM,
       DATUMDOKONCENI DATUM,
       PRIMARY KEY (IDSKUPINY, IDZAKAZNIKA),
       FOREIGN KEY (IDZAKAZNIKA) REFERENCES ZAKAZNIK (IDZAKAZNIKA) ON DELETE CASCADE ON UPDATE CASCADE,
       FOREIGN KEY (IDSKUPINY) REFERENCES SKUPINA (IDSKUPINY) ON DELETE NO ACTION ON UPDATE CASCADE
);

CREATE TABLE NOTEBOOK(
       IDNOTEBOOKU    ID,
       CPU            HODNOTA,
       RAM            HODNOTA,
       HDD            HODNOTA,
       PRIMARY KEY (IDNOTEBOOKU)
);

CREATE TABLE TECHNIK(
       IDTECHNIKA    ID,
       IDSKUPINY     ID,
       IDNOTEBOOKU   ID,
       JMENO         TEXT,
       PRIJMENI      TEXT,
       PLAT          HODNOTA,
       PRIMARY KEY (IDTECHNIKA),
       FOREIGN KEY (IDSKUPINY) REFERENCES SKUPINA (IDSKUPINY) ON DELETE CASCADE ON UPDATE CASCADE,
       FOREIGN KEY (IDNOTEBOOKU) REFERENCES NOTEBOOK (IDNOTEBOOKU) ON DELETE NO ACTION ON UPDATE CASCADE
);



/* VLOZENI ZKUSEBNICH DAT */
INSERT INTO ZAKAZNIK VALUES (1,'Franta','Jezek');
INSERT INTO ZAKAZNIK VALUES (2,'Ota','Sejcek');
INSERT INTO ZAKAZNIK VALUES (3,'Ronald','Novy');
INSERT INTO ZAKAZNIK VALUES (4,'Radek','Zelezny');
INSERT INTO ZAKAZNIK VALUES (5,'Pepa','Krejcarek');
COMMIT WORK;

INSERT INTO SKUPINA VALUES (1,'optimalizace');
INSERT INTO SKUPINA VALUES (2,'hardware');
INSERT INTO SKUPINA VALUES (3,'analyza');
COMMIT WORK;

INSERT INTO ZAKAZKA VALUES (3,1,3000,'2009-07-01','2009-07-03');
INSERT INTO ZAKAZKA VALUES (2,1,10000,'2009-07-03','2009-07-04');
INSERT INTO ZAKAZKA VALUES (1,1,5000,'2009-07-04','2009-07-07');
INSERT INTO ZAKAZKA VALUES (3,2,3000,'2009-06-01','2009-06-03');
INSERT INTO ZAKAZKA VALUES (2,2,13000,'2009-06-03','2009-06-04');
INSERT INTO ZAKAZKA VALUES (1,2,4000,'2009-06-04','2009-06-07');
INSERT INTO ZAKAZKA VALUES (3,3,6000,'2009-07-02','2009-07-04');
INSERT INTO ZAKAZKA VALUES (2,3,7000,'2009-07-04','2009-07-07');
INSERT INTO ZAKAZKA VALUES (3,4,2000,'2009-07-06','2009-07-07');
COMMIT WORK;

INSERT INTO NOTEBOOK VALUES (1,1000,512,120);
INSERT INTO NOTEBOOK VALUES (2,1200,512,120);
INSERT INTO NOTEBOOK VALUES (3,1500,512,120);
INSERT INTO NOTEBOOK VALUES (4,1800,1024,120);
INSERT INTO NOTEBOOK VALUES (5,2000,2048,160);
INSERT INTO NOTEBOOK VALUES (6,2400,2048,160);
COMMIT WORK;

INSERT INTO TECHNIK VALUES (1,1,1,'Richard','Burns',10000);
INSERT INTO TECHNIK VALUES (2,1,2,'Disco','Stu',15000);
INSERT INTO TECHNIK VALUES (3,2,3,'Ned','Flanders',20000);
INSERT INTO TECHNIK VALUES (4,3,4,'Christian','Scott',22000);
INSERT INTO TECHNIK VALUES (5,3,5,'Homer','Simpson',17000);
INSERT INTO TECHNIK VALUES (6,3,6,'Ray','Patterson',18000);
COMMIT WORK;



/* VIEWS */
CREATE VIEW VLASTNISTITECHNIKA (JMENO, PRIJMENI, SPECIALIZACE, PLAT, CPU, RAM, HDD)
       AS
       SELECT JMENO, PRIJMENI, SPECIALIZACE, PLAT, CPU, RAM, HDD
       FROM TECHNIK
       JOIN SKUPINA ON (TECHNIK.IDSKUPINY=SKUPINA.IDSKUPINY)
       JOIN NOTEBOOK ON (TECHNIK.IDNOTEBOOKU=NOTEBOOK.IDNOTEBOOKU)
;


CREATE VIEW ZAKAZKYKOMPLET (JMENO, PRIJMENI, CENA, DATUMZADANI, DATUMDOKONCENI, SPECIALIZACE)
       AS
       SELECT JMENO, PRIJMENI, CENA, DATUMZADANI, DATUMDOKONCENI, SPECIALIZACE
       FROM ZAKAZKA
       JOIN ZAKAZNIK ON (ZAKAZKA.IDZAKAZNIKA=ZAKAZNIK.IDZAKAZNIKA)
       JOIN SKUPINA ON (ZAKAZKA.IDSKUPINY=SKUPINA.IDSKUPINY)
;




/* NESELECTOVE PROCEDURY */
create sequence szak;
create sequence sskup;
create sequence sntbk;

set term ^;

create procedure novyzakaznik(jmeno TEXT, prijmeni TEXT)
as
begin
  insert into zakaznik values (gen_id(szak,1),:jmeno,:prijmeni);
  when any do exit;
end^


create procedure novaskupina(specializace TEXT)
as
begin
  insert into skupina values (gen_id(sskup,1),:specializace);
  when any do exit;
end^


create procedure novynotebook(cpu hodnota, ram hodnota, hdd hodnota)
as
begin
  insert into notebook values (gen_id(sntbk,1),:cpu, :ram, :hdd);
  when any do exit;
end^



/* SELECTOVE PROCEDURY */
create procedure plattechnika (idt integer)
returns (plat hodnota)
as
begin
  for select plat
  from technik
  where idtechnika=:idt
  into :plat
  do
  suspend;
end^


create procedure jakynb(idt integer)
returns (idnotebooku id, cpu hodnota, ram hodnota, hdd hodnota)
as
begin
  for select technik.idnotebooku, cpu, ram, hdd
  from technik join notebook on (technik.idnotebooku=notebook.idnotebooku)
  where idtechnika=:idt
  into :idnotebooku, :cpu, :ram, :hdd
  do
  suspend;
end^


create procedure zakaznikinfo(idz integer)
returns (idskupiny hodnota, cena hodnota, datumzadani datum, datumdokonceni datum)
as
begin
  for select idskupiny, cena, datumzadani, datumdokonceni
  from zakazka
  join zakaznik on (zakazka.idzakaznika=zakaznik.idzakaznika)
  where zakazka.idzakaznika=:idz
  into :idskupiny, :cena, :datumzadani, :datumdokonceni
  do
  suspend;
end^


create procedure skupiny(ids integer)
returns (idskupiny hodnota, specializace text, pocettechniku id, celkovyplat hodnota)
as
begin
  for select skupina.idskupiny, min(specializace), count(idtechnika), sum(plat)
  from skupina
  left join technik on (skupina.idskupiny=technik.idskupiny)
  group by skupina.idskupiny
  into :idskupiny, :specializace, :pocettechniku, :celkovyplat
  do
  suspend;
end^