MySQL Baza CRM

DROP PROCEDURE IF EXISTS `drop_all_tables`;

DELIMITER $$
CREATE PROCEDURE `drop_all_tables`()
BEGIN
    DECLARE _done INT DEFAULT FALSE;
    DECLARE _tableName VARCHAR(255);
    DECLARE _cursor CURSOR FOR
        SELECT table_name
        FROM information_schema.TABLES
        WHERE table_schema = SCHEMA();
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET _done = TRUE;
    SET FOREIGN_KEY_CHECKS = 0;
    OPEN _cursor;
    REPEAT FETCH _cursor INTO _tableName;
    IF NOT _done THEN
        SET @stmt_sql = CONCAT('DROP TABLE ', _tableName);
        PREPARE stmt1 FROM @stmt_sql;
        EXECUTE stmt1;
        DEALLOCATE PREPARE stmt1;
    END IF;
    UNTIL _done END REPEAT;
    CLOSE _cursor;
    SET FOREIGN_KEY_CHECKS = 1;
END$$
DELIMITER ;
call drop_all_tables();
DROP PROCEDURE IF EXISTS `drop_all_tables`;

CREATE TABLE gradovi (
    postbr INT PRIMARY KEY,
    naziv_grada VARCHAR(50) UNIQUE NOT NULL,
    vrijeme_unosa TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO gradovi (postbr, naziv_grada, vrijeme_unosa) VALUES
    (10000, 'Zagreb', '2015-06-25 12:55:01'),
    (51000, 'Rijeka', '2015-06-26 07:12:51'),
    (35000, 'Sl. Brod', CURRENT_TIMESTAMP),
    (21000, 'Split', '2017-01-18 11:11:11'),
    (52100, 'Pula', NULL),
    (31000, 'Osijek', CURRENT_TIMESTAMP),
    (23000, 'Zadar', '2020-04-03'),
    (42000, 'Varaždin', '2020-08-01'),
    (22000, 'Šibenik', '2020-09-04 15:30:21'),
    (20000, 'Dubrovnik', '2020-01-09');


CREATE TABLE uloge (
    id INT PRIMARY KEY AUTO_INCREMENT,
    naziv_uloge VARCHAR(50) UNIQUE NOT NULL,
    nadredeni_id INT,
    FOREIGN KEY (nadredeni_id)
        REFERENCES uloge(id)
        ON DELETE SET NULL
);

INSERT INTO uloge (naziv_uloge, nadredeni_id) VALUES
    ('CEO', NULL),
    ('CTO', 1),
    ('COO', 1),
    ('CFO', 1),
    ('Senior programer/ka', 2),
    ('Programer/ka', 5),
    ('Knjigovodstvo', 4),
    ('Pomoćnik/ca knjigovodstva', 7),
    ('Vanjski suradnik', 3),
    ('Voditelj nadzora', 3),
    ('Pomoćnik/ca nadzora', 10);

CREATE TABLE statusi_zaposlenika (
    id INT PRIMARY KEY AUTO_INCREMENT,
    status VARCHAR(50) UNIQUE NOT NULL,
    broj_zaposlenika INT DEFAULT 0
);

INSERT INTO statusi_zaposlenika (status) VALUES
    ('Aktivno zaposlen/a'),
    ('Vanjski suradnik/ca'),
    ('Suspendiran/a'),
    ('Izvanredni otkaz'),
    ('Bolovanje');

CREATE TABLE zaposlenici (
    id INT PRIMARY KEY AUTO_INCREMENT,
    titula VARCHAR(50),
    ime VARCHAR(50) NOT NULL,
    prezime VARCHAR(50) NOT NULL,
    email VARCHAR(255) UNIQUE,
    uloga_id INT,
    status_id INT,
    postbr_prebivaliste INT,
    postbr_boraviste INT,
    datum_rodenja DATE,

    FOREIGN KEY (uloga_id)
        REFERENCES uloge(id)
        ON DELETE SET NULL,

    FOREIGN KEY (status_id)
        REFERENCES statusi_zaposlenika(id)
        ON DELETE SET NULL,

    FOREIGN KEY (postbr_prebivaliste)
        REFERENCES gradovi(postbr)
        ON DELETE SET NULL,

    FOREIGN KEY (postbr_boraviste)
        REFERENCES gradovi(postbr)
        ON DELETE SET NULL
);

CREATE TABLE statusi_kontakata (
    id INT PRIMARY KEY AUTO_INCREMENT,
    status VARCHAR(50) UNIQUE NOT NULL,
    broj_kontakata INT DEFAULT 0
);

INSERT INTO statusi_kontakata (status) VALUES
    ('Aktivan'),
    ('Neaktivan'),
    ('Čeka na odobrenje'),
    ('Čeka brisanje');

CREATE TABLE tvrtke (
    id INT PRIMARY KEY AUTO_INCREMENT,
    naziv VARCHAR(255) NOT NULL,
    oib_tvrtke VARCHAR(11) UNIQUE,
    adresa VARCHAR(255),
    www VARCHAR(255),
    tip VARCHAR(10) DEFAULT 'd.o.o.' NOT NULL,
    postbr_sjediste INT,
    FOREIGN KEY (postbr_sjediste)
        REFERENCES gradovi(postbr)
        ON DELETE SET NULL
);

CREATE TABLE kontakti (
    id INT PRIMARY KEY AUTO_INCREMENT,
    ime VARCHAR(255) NOT NULL,
    prezime VARCHAR(255) NOT NULL,
    titula VARCHAR(5),
    email VARCHAR(255) UNIQUE,
    status_id INT,
    tvrtka_id INT,
    adresa VARCHAR(255),
    mobitel VARCHAR(30),
    postbr_grad INT,
    privatni TINYINT DEFAULT 0,
    izradio_id INT,

    FOREIGN KEY (status_id)
        REFERENCES statusi_kontakata(id)
        ON DELETE SET NULL,

    FOREIGN KEY (tvrtka_id)
        REFERENCES tvrtke(id)
        ON DELETE SET NULL,

    FOREIGN KEY (postbr_grad)
        REFERENCES gradovi(postbr)
        ON DELETE SET NULL,

    FOREIGN KEY (izradio_id)
        REFERENCES zaposlenici(id)
        ON DELETE SET NULL
);

CREATE TABLE prioriteti_zadataka (
    id INT PRIMARY KEY AUTO_INCREMENT,
    prioritet VARCHAR(50) UNIQUE NOT NULL,
    broj_zadataka INT DEFAULT 0
);


INSERT INTO prioriteti_zadataka (prioritet) VALUES
    ('Niski prioritet'),
    ('Srednji prioritet'),
    ('Visoki prioritet'),
    ('Hitno!');

CREATE TABLE zadaci (
    id INT PRIMARY KEY AUTO_INCREMENT,
    naslov VARCHAR(100) NOT NULL,
    izvrsitelj_id INT,
    zamjenik_id INT,
    izradio_id INT,
    datum_pocetka DATE,
    datum_zavrsetka DATE,
    sati_predvideno INT DEFAULT 0,
    sati_odradeno INT DEFAULT 0,
    sati_razlika INT DEFAULT 0,
    tekst_zadatka varchar(2048),
    kontakt_id INT,
    prioritet_id INT,

    FOREIGN KEY (izvrsitelj_id)
        REFERENCES zaposlenici(id)
        ON DELETE SET NULL,

    FOREIGN KEY (zamjenik_id)
        REFERENCES zaposlenici(id)
        ON DELETE SET NULL,

    FOREIGN KEY (zamjenik_id)
        REFERENCES zaposlenici(id)
        ON DELETE SET NULL,

    FOREIGN KEY (kontakt_id)
        REFERENCES kontakti(id)
        ON DELETE SET NULL,

    FOREIGN KEY (prioritet_id)
        REFERENCES prioriteti_zadataka(id)
        ON DELETE SET NULL
);

CREATE TABLE statusi_racuna (
    id INT PRIMARY KEY AUTO_INCREMENT,
    status VARCHAR(50) UNIQUE NOT NULL,
    broj_racuna INT DEFAULT 0
);

INSERT INTO statusi_racuna (status) VALUES
    ('Neplaćen'),
    ('Plaćen'),
    ('Storniran');

CREATE TABLE racuni (
    id INT PRIMARY KEY AUTO_INCREMENT,
    broj_racuna VARCHAR(5) UNIQUE NOT NULL,
    datum_racuna DATE,
    datum_dospijeca DATE,
    placeno TINYINT DEFAULT 0,
    ukupno_bez_pdv DECIMAL(15, 2) DEFAULT 0,
    pdv_vrijednost DECIMAL(15, 2) DEFAULT 0,
    ukupno  DECIMAL(15, 2) DEFAULT 0,
    tvrtka_id INT,
    status_id INT,

    FOREIGN KEY (tvrtka_id)
        REFERENCES tvrtke(id)
        ON DELETE SET NULL,

    FOREIGN KEY (status_id)
        REFERENCES statusi_racuna(id)
        ON DELETE SET NULL
);

COMMIT;