/*
    Aby to nehazelo error, ze tabulky neexistuji
*/

BEGIN
      EXECUTE IMMEDIATE 'DROP TABLE klient CASCADE CONSTRAINTS';
EXCEPTION
      WHEN OTHERS THEN NULL;
END;
/
BEGIN
      EXECUTE IMMEDIATE 'DROP TABLE ucet CASCADE CONSTRAINTS';
EXCEPTION
      WHEN OTHERS THEN NULL;
END;
/
BEGIN
      EXECUTE IMMEDIATE 'DROP TABLE zmluva CASCADE CONSTRAINTS';
EXCEPTION
      WHEN OTHERS THEN NULL;
END;
/
BEGIN
      EXECUTE IMMEDIATE 'DROP TABLE makler CASCADE CONSTRAINTS';
EXCEPTION
      WHEN OTHERS THEN NULL;
END;
/
BEGIN
      EXECUTE IMMEDIATE 'DROP TABLE maklerovi_reality CASCADE CONSTRAINTS';
EXCEPTION
      WHEN OTHERS THEN NULL;
END;
/
BEGIN
      EXECUTE IMMEDIATE 'DROP TABLE dotaz CASCADE CONSTRAINTS';
EXCEPTION
      WHEN OTHERS THEN NULL;
END;
/
BEGIN
      EXECUTE IMMEDIATE 'DROP TABLE realita CASCADE CONSTRAINTS';
EXCEPTION
      WHEN OTHERS THEN NULL;
END;
/
BEGIN
      EXECUTE IMMEDIATE 'DROP TABLE prehliadka CASCADE CONSTRAINTS';
EXCEPTION
      WHEN OTHERS THEN NULL;
END;
/
BEGIN
      EXECUTE IMMEDIATE 'DROP TABLE predaj CASCADE CONSTRAINTS';
EXCEPTION
      WHEN OTHERS THEN NULL;
END;
/

CREATE TABLE klient(
  id NUMBER NOT NULL,
  email VARCHAR2(30) NOT NULL
);

CREATE TABLE ucet(
  id NUMBER NOT NULL,
  jmeno VARCHAR2(30) NOT NULL,
  prijmeni VARCHAR2(30) NOT NULL,
  datum_nar DATE NOT NULL,
  rodne_cis NUMBER NOT NULL,
  cislo_op VARCHAR2(10) NOT NULL,
  telefon NUMBER NOT NULL,
  adresa VARCHAR2(100) NOT NULL,
  potvrzeny_ucet NUMBER NOT NULL
);

CREATE TABLE zmluva(
  id NUMBER NOT NULL,
  id_klienta NUMBER NOT NULL,
  id_reality NUMBER NOT NULL,
  stav NUMBER NOT NULL,
  nabidka_ceny NUMBER NOT NULL
);

CREATE TABLE makler(
  id NUMBER NOT NULL,
  jmeno VARCHAR2(30) NOT NULL,
  prijmeni VARCHAR2(30) NOT NULL,
  email VARCHAR2(30) NOT NULL,
  telefon NUMBER NOT NULL
);

CREATE TABLE maklerovi_reality(
  id_maklere NUMBER NOT NULL,
  id_reality NUMBER NOT NULL
);

CREATE TABLE dotaz(
  id NUMBER NOT NULL,
  id_reality NUMBER NOT NULL,
  otazka VARCHAR2(100) NOT NULL
);

CREATE TABLE realita(
  id NUMBER NOT NULL,
  nazev VARCHAR2(30) NOT NULL,
  kategorie NUMBER NOT NULL,
  cena NUMBER NOT NULL,
  datum_pridani DATE NOT NULL,
  adresa VARCHAR2(100) NOT NULL
);

CREATE TABLE prehliadka(
  id NUMBER NOT NULL,
  id_klienta NUMBER NOT NULL,
  id_reality NUMBER NOT NULL,
  datum_prohlidky DATE NOT NULL,
  potvrzeno NUMBER NOT NULL
);

CREATE TABLE predaj(
  id NUMBER NOT NULL,
  id_reality NUMBER NOT NULL,
  id_klienta NUMBER NOT NULL,
  datum_prodeje DATE NOT NULL,
  cena NUMBER NOT NULL
);

/* primarní klíče */

ALTER TABLE klient ADD CONSTRAINT PK_klient PRIMARY KEY (id);

ALTER TABLE ucet ADD CONSTRAINT PK_ucet PRIMARY KEY (id);

ALTER TABLE zmluva ADD CONSTRAINT PK_zmluva PRIMARY KEY (id);

ALTER TABLE makler ADD CONSTRAINT PK_makler PRIMARY KEY (id);

ALTER TABLE dotaz ADD CONSTRAINT PK_dotaz PRIMARY KEY (id);

ALTER TABLE realita ADD CONSTRAINT PK_realita PRIMARY KEY (id);

ALTER TABLE prehliadka ADD CONSTRAINT PK_prehliadka PRIMARY KEY (id);

ALTER TABLE predaj ADD CONSTRAINT PK_predaj PRIMARY KEY (id);

/* cizí klíče */

ALTER TABLE ucet ADD CONSTRAINT FK_ucet FOREIGN KEY(id) REFERENCES klient;

ALTER TABLE zmluva ADD CONSTRAINT FK_zmluva_klient FOREIGN KEY(id_klienta) REFERENCES ucet;
ALTER TABLE zmluva ADD CONSTRAINT FK_zmluva_realita FOREIGN KEY(id_reality) REFERENCES realita;

ALTER TABLE maklerovi_reality ADD CONSTRAINT FK_maklerovi_reality_makler FOREIGN KEY(id_maklere) REFERENCES makler;
ALTER TABLE maklerovi_reality ADD CONSTRAINT FK_maklerovi_reality_realita FOREIGN KEY(id_reality) REFERENCES realita;

ALTER TABLE dotaz ADD CONSTRAINT FK_dotaz FOREIGN KEY(id_reality) REFERENCES realita;

ALTER TABLE prehliadka ADD CONSTRAINT FK_prehliadka_klient FOREIGN KEY(id_klienta) REFERENCES klient;
ALTER TABLE prehliadka ADD CONSTRAINT FK_prehliadka_realita FOREIGN KEY(id_reality) REFERENCES realita;

ALTER TABLE predaj ADD CONSTRAINT FK_predaj_realita FOREIGN KEY(id_reality) REFERENCES realita;
ALTER TABLE predaj ADD CONSTRAINT FK_predaj_klient FOREIGN KEY(id_klienta) REFERENCES ucet;

/* Overeni rodneho cisla
   nemusime testovat, jestli je to cislo
   musime testovat delitelnost 11
   musime testovat spravnost datumu
   musime testovat delku (min 9, max 10)
   pokud delka = 9 a koncovku 000 tak je spatne */

ALTER SESSION SET nls_date_format='dd.mm.yy';
SET serveroutput ON;

CREATE OR REPLACE TRIGGER TR_rodne_cislo
  BEFORE INSERT OR UPDATE OF rodne_cis ON ucet
  FOR EACH ROW
DECLARE
  rod_c ucet.rodne_cis%TYPE;
  den NUMBER(2);
  mesic NUMBER(2);
  rok NUMBER(2);
  datum DATE;
BEGIN
  rod_c := :NEW.rodne_cis;               -- z rodneho cisla ...
  mesic := MOD( (rod_c / 1000000), 100); -- zjistime mesic
  den   := MOD( (rod_c / 10000), 100);   -- den
  rok   := rod_c / 100000000;            -- rok

  -- pokud rodne cislo neni delitelne 11
  IF ( MOD(rod_c, 11) <> 0 ) THEN
    Raise_Application_Error (-20203, 'Neplatne rodne cislo: Neni delitelne 11');
  END IF;

  -- jestli je cislo vetsi nez 50, pak se jedna o zenu a 50 odecteme
  IF ( mesic > 50 ) THEN
    mesic := mesic - 50;
  END IF;

  -- pokud se nejedna o platne datum, pak vznikne vyjimka
  BEGIN
    datum := den||'.'||mesic||'.'||rok;
  EXCEPTION
    WHEN OTHERS THEN
       Raise_Application_Error (-20204, 'Neplatne datum v rodnem cisle');
  END;

  -- pokud je kratsi nez 9
  IF ( LENGTH(rod_c) < 9 ) THEN
    Raise_Application_Error (-20205, 'Neplatne rodne cislo: Rodne cislo je kratsi, nez 9 znaku');
  END IF;

  -- pokud je delsi nez 10
  IF ( LENGTH(rod_c) > 10 ) THEN
    Raise_Application_Error (-20206, 'Neplatne rodne cislo: Rodne cislo je delsi, nez 10 znaku');
  END IF;

  -- pokud delka = 9 a koncovku 000 tak je spatne
  IF ( LENGTH(rod_c) = 9 ) THEN
    IF ( SUBSTR(TO_CHAR(rod_c), 7) = '000' ) THEN
      Raise_Application_Error (-20207, 'Neplatne rodne cislo: Koncovka rodneho cisla 000 je nepripustna');
    END IF;
  END IF;

END TR_rodne_cislo;
/
show errors
ALTER SESSION SET nls_date_format='dd.mm.yyyy';


/* testovací data */

/* 3 uzivatele */
INSERT INTO klient (ID, EMAIL) VALUES ('1', 'test1@email.cz');
INSERT INTO klient (ID, EMAIL) VALUES ('2', 'test2@email.cz');
INSERT INTO klient (ID, EMAIL) VALUES ('3', 'test3@email.cz');

/* pouze 2 z nich maji uplny ucet */
INSERT INTO ucet (ID, JMENO, PRIJMENI, DATUM_NAR, RODNE_CIS, CISLO_OP, TELEFON, ADRESA, POTVRZENY_UCET) VALUES ('2', 'Test2', 'Protest2', TO_DATE('1950-02-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '9207214093', '123456789', '987654321', 'Nekde 5, Brno', '0');
INSERT INTO ucet (ID, JMENO, PRIJMENI, DATUM_NAR, RODNE_CIS, CISLO_OP, TELEFON, ADRESA, POTVRZENY_UCET) VALUES ('3', 'Test3', 'Protest3', TO_DATE('1900-12-24 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '9203178237', '987654321', '123456789', 'Nekde 12, Brno', '1');

/* 3 testovaci makleri */
/* ID pocitano od hodnoty 10, aby bylo poznat, kde se objevuji */
INSERT INTO makler (ID, JMENO, PRIJMENI, EMAIL, TELEFON) VALUES ('10', 'Makler1', 'Prijmeni1', 'makler1@email.cz', '123456789');
INSERT INTO makler (ID, JMENO, PRIJMENI, EMAIL, TELEFON) VALUES ('11', 'Makler2', 'Prijmeni2', 'makler2@email.cz', '789456123');
INSERT INTO makler (ID, JMENO, PRIJMENI, EMAIL, TELEFON) VALUES ('12', 'Makler3', 'Prijmeni3', 'makler3@email.cz', '456123789');

/* 3 testovaci reality */
/* ID pocitano od 100, aby bylo videt, kde se objevuji */
INSERT INTO realita (ID, NAZEV, KATEGORIE, CENA, DATUM_PRIDANI, ADRESA) VALUES ('100', 'Nemovitost1', '1', '1000000', TO_DATE('2015-03-29 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'Nekde 15, Brno');
INSERT INTO realita (ID, NAZEV, KATEGORIE, CENA, DATUM_PRIDANI, ADRESA) VALUES ('101', 'Nemovitost2', '2', '4500000', TO_DATE('2010-06-05 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'Nekde 23, Brno');
INSERT INTO realita (ID, NAZEV, KATEGORIE, CENA, DATUM_PRIDANI, ADRESA) VALUES ('102', 'Nemovitost3', '3', '15000000', TO_DATE('2000-12-24 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'Nekde 42, Brno');

/* propojeni makleru a realit */
/* jedne makler ma 2 reality, dalsi jednu a posledni zadnou */
INSERT INTO maklerovi_reality (ID_MAKLERE, ID_REALITY) VALUES ('10', '100');
INSERT INTO maklerovi_reality (ID_MAKLERE, ID_REALITY) VALUES ('10', '102');
INSERT INTO maklerovi_reality (ID_MAKLERE, ID_REALITY) VALUES ('12', '101');

/* 2 ruzne smlouvy, jedna je uz dokoncena*/
INSERT INTO zmluva (ID, ID_KLIENTA, ID_REALITY, STAV, NABIDKA_CENY) VALUES ('1', '3', '100', '0', '500000');
INSERT INTO zmluva (ID, ID_KLIENTA, ID_REALITY, STAV, NABIDKA_CENY) VALUES ('2', '2', '101', '1', '5000000');

/* dotaz k realite */
INSERT INTO dotaz (ID, ID_REALITY, OTAZKA) VALUES ('1', '100', 'Bydli v dome neprizpusobivi spoluobcane?');

/* zadost o prohlidku */
INSERT INTO prehliadka (ID, ID_KLIENTA, ID_REALITY, DATUM_PROHLIDKY, POTVRZENO) VALUES ('1', '1', '102', TO_DATE('2015-04-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '0');
INSERT INTO prehliadka (ID, ID_KLIENTA, ID_REALITY, DATUM_PROHLIDKY, POTVRZENO) VALUES ('2', '2', '101', TO_DATE('2015-03-27 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '1');

/* dokonceny prodej */
INSERT INTO predaj (ID, ID_REALITY, ID_KLIENTA, DATUM_PRODEJE, CENA) VALUES ('1', '101', '2', TO_DATE('2015-03-27 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '5000000');