DROP TABLE IF EXISTS telefon;
DROP TABLE IF EXISTS kontakt;
DROP TABLE IF EXISTS tarif;
DROP TABLE IF EXISTS predvolba;
DROP TABLE IF EXISTS operator;

CREATE TABLE kontakt (
    id INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL,
    jmeno VARCHAR(20) DEFAULT '' NOT NULL,
    prijmeni VARCHAR(20) DEFAULT '' NOT NULL,
    priorita INTEGER NOT NULL DEFAULT 0 CHECK (priorita >= -3 AND priorita <= 3),
    pohlavi BOOLEAN DEFAULT NULL,
    adresa VARCHAR(100) DEFAULT NULL,
    vek INTEGER DEFAULT NULL CHECK (vek > 0),
    poznamka TEXT NOT NULL,
    firma_nazev VARCHAR(50) DEFAULT NULL,
    firma_poznamka TEXT DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;

CREATE TABLE operator (
    id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
    jmeno VARCHAR(20) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;

CREATE TABLE predvolba (
    predvolba CHAR(3) NOT NULL PRIMARY KEY CHECK(LENGTH(predvolba) >= 1),
    operator_id INTEGER NOT NULL,
    FOREIGN KEY (operator_id) REFERENCES operator(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;

CREATE TABLE tarif (
    id INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL,
    operator_id INTEGER NULL,
    nazev VARCHAR(20) NOT NULL,
    cena NUMERIC(3, 1) NULL CHECK(cena >= 0),
    FOREIGN KEY (operator_id) REFERENCES operator(id),
    CONSTRAINT operator_nazev_uix UNIQUE (operator_id, nazev)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;

CREATE TABLE telefon (
    predvolba CHAR(3) NOT NULL,
    telefon VARCHAR(6) NOT NULL,
    operator_id INTEGER NOT NULL,
    tarif_id INTEGER DEFAULT NULL,
    kontakt_id INTEGER  DEFAULT NULL,
    poznamka VARCHAR(100) NOT NULL DEFAULT '',
    FOREIGN KEY (predvolba) REFERENCES predvolba(predvolba),
    FOREIGN KEY (operator_id) REFERENCES operator(id),
    FOREIGN KEY (tarif_id) REFERENCES tarif(id),
    FOREIGN KEY (kontakt_id) REFERENCES kontakt(id),
    CONSTRAINT telefon_pk PRIMARY KEY(predvolba, telefon)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;

INSERT INTO operator(jmeno) VALUES ('?'); -- id=1
INSERT INTO operator(jmeno) VALUES ('Pevná linka'); -- id=2
INSERT INTO operator(jmeno) VALUES ('O2'); -- id=3
INSERT INTO operator(jmeno) VALUES ('T-Mobil'); -- id=4
INSERT INTO operator(jmeno) VALUES ('Vodafone'); -- id=5
INSERT INTO operator(jmeno) VALUES ('U:fon'); -- id=6

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


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

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');