BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE telefon';
EXCEPTION
   WHEN OTHERS THEN
      IF SQLCODE != -942 THEN
         RAISE;
      END IF;
END;
/

BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE kontakt';
EXCEPTION
   WHEN OTHERS THEN
      IF SQLCODE != -942 THEN
         RAISE;
      END IF;
END;
/

BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE tarif';
EXCEPTION
   WHEN OTHERS THEN
      IF SQLCODE != -942 THEN
         RAISE;
      END IF;
END;
/

BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE predvolba';
EXCEPTION
   WHEN OTHERS THEN
      IF SQLCODE != -942 THEN
         RAISE;
      END IF;
END;
/

BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE operator';
EXCEPTION
   WHEN OTHERS THEN
      IF SQLCODE != -942 THEN
         RAISE;
      END IF;
END;
/

BEGIN
   EXECUTE IMMEDIATE 'DROP SEQUENCE kontakt_id_seq';
EXCEPTION
   WHEN OTHERS THEN
      IF SQLCODE != -2289 THEN
         RAISE;
      END IF;
END;
/

BEGIN
   EXECUTE IMMEDIATE 'DROP SEQUENCE operator_id_seq';
EXCEPTION
   WHEN OTHERS THEN
      IF SQLCODE != -2289 THEN
         RAISE;
      END IF;
END;
/

BEGIN
   EXECUTE IMMEDIATE 'DROP SEQUENCE tarif_id_seq';
EXCEPTION
   WHEN OTHERS THEN
      IF SQLCODE != -2289 THEN
         RAISE;
      END IF;
END;
/

CREATE TABLE kontakt (
    id INTEGER PRIMARY KEY NOT NULL,
    jmeno VARCHAR2(20) DEFAULT '' NULL,
    prijmeni VARCHAR2(20) DEFAULT '' NULL,
    priorita INTEGER DEFAULT 0 NOT NULL CHECK (priorita >= -3 AND priorita <= 3),
    pohlavi INTEGER DEFAULT NULL CHECK(pohlavi BETWEEN 0 AND 1),
    adresa VARCHAR2(100) DEFAULT NULL,
    vek INTEGER DEFAULT NULL CHECK (vek > 0),
    poznamka CLOB DEFAULT '' NULL,
    firma_nazev VARCHAR2(50) DEFAULT NULL,
    firma_poznamka CLOB DEFAULT NULL
);
CREATE SEQUENCE kontakt_id_seq;

CREATE TABLE operator (
    id INTEGER PRIMARY KEY NOT NULL,
    jmeno VARCHAR2(20) NOT NULL UNIQUE
);
CREATE SEQUENCE operator_id_seq;

CREATE TABLE predvolba (
    predvolba CHAR(3) NOT NULL PRIMARY KEY CHECK(LENGTH(predvolba) >= 1),
    operator_id INTEGER REFERENCES operator(id) NOT NULL
);

CREATE TABLE tarif (
    id INTEGER PRIMARY KEY NOT NULL,
    operator_id INTEGER REFERENCES operator(id) NULL,
    nazev VARCHAR2(20) NOT NULL,
    cena NUMERIC(3, 1) NULL CHECK(cena >= 0),
    CONSTRAINT operator_nazev_uix UNIQUE (operator_id, nazev)
);
CREATE SEQUENCE tarif_id_seq;

CREATE TABLE telefon (
    predvolba CHAR(3) NOT NULL REFERENCES predvolba(predvolba),
    telefon VARCHAR2(6) NOT NULL,
    operator_id INTEGER REFERENCES operator(id) NOT NULL,
    tarif_id INTEGER DEFAULT NULL REFERENCES tarif(id),
    kontakt_id INTEGER DEFAULT NULL REFERENCES kontakt(id),
    poznamka VARCHAR2(100) DEFAULT '' NULL,
    CONSTRAINT telefon_pk PRIMARY KEY(predvolba, telefon)
);

INSERT INTO operator(id, jmeno) VALUES (operator_id_seq.NEXTVAL, '?');
INSERT INTO operator(id, jmeno) VALUES (operator_id_seq.NEXTVAL, 'Pevná linka');
INSERT INTO operator(id, jmeno) VALUES (operator_id_seq.NEXTVAL, 'O2');
INSERT INTO operator(id, jmeno) VALUES (operator_id_seq.NEXTVAL, 'T-Mobil');
INSERT INTO operator(id, jmeno) VALUES (operator_id_seq.NEXTVAL, 'Vodafone'); 
INSERT INTO operator(id, jmeno) VALUES (operator_id_seq.NEXTVAL, 'U:fon'); 

INSERT INTO predvolba VALUES (312,2);
INSERT INTO predvolba VALUES (318,2);
INSERT INTO predvolba VALUES (601,3);
INSERT INTO predvolba VALUES (602,3);
INSERT INTO predvolba VALUES (603,4);
INSERT INTO predvolba VALUES (604,4);
INSERT INTO predvolba VALUES (605,4);
INSERT INTO predvolba VALUES (606,3);
INSERT INTO predvolba VALUES (607,3);
INSERT INTO predvolba VALUES (608,5);
INSERT INTO predvolba VALUES (702,3);
INSERT INTO predvolba VALUES (720,3);
INSERT INTO predvolba VALUES (721,3);
INSERT INTO predvolba VALUES (722,3);
INSERT INTO predvolba VALUES (723,3);
INSERT INTO predvolba VALUES (724,3);
INSERT INTO predvolba VALUES (725,3);
INSERT INTO predvolba VALUES (726,3);
INSERT INTO predvolba VALUES (727,3);
INSERT INTO predvolba VALUES (728,3);
INSERT INTO predvolba VALUES (729,3);
INSERT INTO predvolba VALUES (730,4);
INSERT INTO predvolba VALUES (731,4);
INSERT INTO predvolba VALUES (732,4);
INSERT INTO predvolba VALUES (733,4);
INSERT INTO predvolba VALUES (734,4);
INSERT INTO predvolba VALUES (735,4);
INSERT INTO predvolba VALUES (736,4);
INSERT INTO predvolba VALUES (737,4);
INSERT INTO predvolba VALUES (738,4);
INSERT INTO predvolba VALUES (739,4);
INSERT INTO predvolba VALUES (770,5);
INSERT INTO predvolba VALUES (773,5);
INSERT INTO predvolba VALUES (774,5);
INSERT INTO predvolba VALUES (775,5);
INSERT INTO predvolba VALUES (776,5);
INSERT INTO predvolba VALUES (777,5);
INSERT INTO predvolba VALUES (790,6);
INSERT INTO predvolba VALUES (791,1);
INSERT INTO predvolba VALUES (797,1);
INSERT INTO predvolba VALUES (799,1);
INSERT INTO predvolba VALUES (908,1);

INSERT INTO kontakt(id,jmeno,prijmeni,priorita,adresa) VALUES (kontakt_id_seq.NEXTVAL, 'Pavel','Drevokocur', 0,'Na nožičkách 3, Praha 4, 13000');
INSERT INTO kontakt(id,jmeno,prijmeni,priorita,adresa) VALUES (kontakt_id_seq.NEXTVAL, 'Petr','Bílek', 0,'Na balkáně 70, Praha 3, 13004');
INSERT INTO kontakt(id,jmeno,prijmeni,priorita) VALUES (kontakt_id_seq.NEXTVAL, 'Tomas','Drevokocur', 1);
INSERT INTO kontakt(id,jmeno,prijmeni,priorita) VALUES (kontakt_id_seq.NEXTVAL, 'Lukas','Drevokocur',-3);
INSERT INTO kontakt(id,jmeno,prijmeni,priorita) VALUES (kontakt_id_seq.NEXTVAL, 'Milos','Drevokocur', 3);
INSERT INTO kontakt(id,jmeno,prijmeni,priorita,pohlavi) VALUES (kontakt_id_seq.NEXTVAL, 'Pavla', 'Drevokocurova', 3, 0);
INSERT INTO kontakt(id,jmeno,prijmeni,priorita,pohlavi) VALUES (kontakt_id_seq.NEXTVAL, 'Jana',  'Drevokocurova', 0, 0);
INSERT INTO kontakt(id,jmeno,prijmeni,priorita,pohlavi,vek,firma_nazev,firma_poznamka) VALUES (kontakt_id_seq.NEXTVAL, 'Tomáš','Baťa',3,1,70,'Baťovy závody','Vyrábí boty');
INSERT INTO kontakt(id,jmeno,prijmeni,priorita,pohlavi,vek,firma_nazev,firma_poznamka) VALUES (kontakt_id_seq.NEXTVAL, 'Jan','Baťa',2,1,40,'Baťovy závody','Vyrábí boty');


INSERT INTO tarif(id,operator_id,nazev,cena) VALUES (tarif_id_seq.NEXTVAL, 1, 'Za 30', NULL);
INSERT INTO tarif(id,operator_id,nazev,cena) VALUES (tarif_id_seq.NEXTVAL, 1, 'Za 60', NULL);
INSERT INTO tarif(id,operator_id,nazev,cena) VALUES (tarif_id_seq.NEXTVAL, 1, 'Za 90', NULL);
INSERT INTO tarif(id,operator_id,nazev,cena) VALUES (tarif_id_seq.NEXTVAL, 2, 'Pevna', 10);
INSERT INTO tarif(id,operator_id,nazev,cena) VALUES (tarif_id_seq.NEXTVAL, 3, 'O2 zaklad',9);
INSERT INTO tarif(id,operator_id,nazev,cena) VALUES (tarif_id_seq.NEXTVAL, 3, 'O2 kamarad',6);
INSERT INTO tarif(id,operator_id,nazev,cena) VALUES (tarif_id_seq.NEXTVAL, 4, 'zaklad', 8);
INSERT INTO tarif(id,operator_id,nazev,cena) VALUES (tarif_id_seq.NEXTVAL, 4, 'kamarad', 5);
INSERT INTO tarif(id,operator_id,nazev,cena) VALUES (tarif_id_seq.NEXTVAL, 5, 'Vodafone zaklad', 3.5);
INSERT INTO tarif(id,operator_id,nazev,cena) VALUES (tarif_id_seq.NEXTVAL, 5, 'Vodafone kamarad', 0);
INSERT INTO tarif(id,operator_id,nazev,cena) VALUES (tarif_id_seq.NEXTVAL, 6, 'zaklad', 7); 
INSERT INTO tarif(id,operator_id,nazev,cena) VALUES (tarif_id_seq.NEXTVAL, 6, 'kamarad', 5);

INSERT INTO telefon (telefon,predvolba,operator_id, tarif_id, kontakt_id) VALUES ('555555', '737',4, 7, 1);
INSERT INTO telefon (telefon,predvolba,operator_id, tarif_id, kontakt_id) VALUES ('555555', '608',5, 10,  2);
INSERT INTO telefon (telefon,predvolba,operator_id, tarif_id, kontakt_id) VALUES ('555555', '776',5, 9,  4);
INSERT INTO telefon (telefon,predvolba,operator_id, tarif_id, kontakt_id, poznamka) VALUES ('555555', '312', 2, 4, 4, 'Telefon domu');
INSERT INTO telefon (telefon,predvolba,operator_id, tarif_id, kontakt_id) VALUES ('555555', '604',4, 7, 6);
INSERT INTO telefon (telefon,predvolba,operator_id, tarif_id, kontakt_id, poznamka) VALUES ('300300','908',1,1, NULL, 'Půjčka za 30/min');