CREATE TABLE email_row (id BIGINT AUTO_INCREMENT, email VARCHAR(255), name VARCHAR(255), subject VARCHAR(255), html LONGTEXT, text LONGTEXT, date_insert DATETIME, date_first_go DATETIME, date_last_go DATETIME, count_go BIGINT DEFAULT 0, is_valid TINYINT(1) DEFAULT '0', sent TINYINT(1) DEFAULT '0', PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = INNODB;
CREATE TABLE specifikace_cviceni (id BIGINT AUTO_INCREMENT, typ VARCHAR(255), skupina VARCHAR(255), hodnota VARCHAR(255), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE agentura (id BIGINT AUTO_INCREMENT, zakaznik_id BIGINT NOT NULL, nazev VARCHAR(255) NOT NULL, email VARCHAR(255), login VARCHAR(255), heslo VARCHAR(255) NOT NULL, poznamka LONGTEXT, language VARCHAR(255) DEFAULT 'cs', archived TINYINT DEFAULT 0 NOT NULL, INDEX zakaznik_id_idx (zakaznik_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE test (id BIGINT AUTO_INCREMENT, admin_id BIGINT, unikatni_nazev VARCHAR(255) NOT NULL UNIQUE, nazev_testu VARCHAR(255) NOT NULL, cas MEDIUMINT NOT NULL, instrukce LONGTEXT, kriteria LONGTEXT, archive BIGINT DEFAULT 0 NOT NULL, deleted TINYINT(1) DEFAULT '0' NOT NULL, created_at DATETIME, updated_at DATETIME, INDEX admin_id_idx (admin_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE test_cviceni (id BIGINT AUTO_INCREMENT, admin_id BIGINT, cviceni_id BIGINT, test_id BIGINT, poradi BIGINT, pocet_otazek BIGINT, bodu_na_otazku DECIMAL(3,1) DEFAULT 1.0 NOT NULL, created_at DATETIME, updated_at DATETIME, INDEX admin_id_idx (admin_id), INDEX cviceni_id_idx (cviceni_id), INDEX test_id_idx (test_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE test_zakaznik (id BIGINT AUTO_INCREMENT, test_id BIGINT NOT NULL, admin_id BIGINT, zakaznik_id BIGINT NOT NULL, hodnoceni_head_id BIGINT, created_at DATETIME, updated_at DATETIME, deleted TINYINT(1) DEFAULT '0' NOT NULL, INDEX test_id_idx (test_id), INDEX admin_id_idx (admin_id), INDEX zakaznik_id_idx (zakaznik_id), INDEX hodnoceni_head_id_idx (hodnoceni_head_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE specifikace_pracovnik (id BIGINT AUTO_INCREMENT, zakaznik_id BIGINT NOT NULL, pozice BIGINT NOT NULL, hodnota VARCHAR(255), INDEX zakaznik_id_idx (zakaznik_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE zakaznik (id BIGINT AUTO_INCREMENT, hlavicka LONGTEXT, nazev VARCHAR(255) NOT NULL, datum_do DATETIME, email VARCHAR(255), barvy LONGTEXT, logo VARCHAR(255), instrukce VARCHAR(255), email_vysledek LONGTEXT, email_vysledek_predmet VARCHAR(255) DEFAULT 'Výsledek Vašeho testu' NOT NULL, email_kod LONGTEXT, email_kod_predmet VARCHAR(255) DEFAULT 'Testování znalostí jazyků - odkaz na test' NOT NULL, email_personalista LONGTEXT, email_hlavicka_vysledek VARCHAR(255), email_hlavicka_kod VARCHAR(255), email_hlavicka_personalista VARCHAR(255), created_at DATETIME, updated_at DATETIME, PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE admin (id BIGINT AUTO_INCREMENT, prih_jmeno VARCHAR(255), prih_heslo VARCHAR(255), jmeno VARCHAR(255), uroven VARCHAR(255) DEFAULT 'vkladatel', created_at DATETIME, updated_at DATETIME, PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE kurz (id BIGINT AUTO_INCREMENT, zakaznik_id BIGINT NOT NULL, specifikace_kurz VARCHAR(255), typ VARCHAR(255) DEFAULT 'individualni', nazev VARCHAR(255) NOT NULL, poznamka LONGTEXT, oddeleni VARCHAR(100) NOT NULL, lokalita VARCHAR(255) NOT NULL, nakladove_stredisko VARCHAR(255), typ_komplex VARCHAR(255) DEFAULT 'kka', report_trkniha TINYINT(1) DEFAULT '1', archived TINYINT DEFAULT 0 NOT NULL, INDEX zakaznik_id_idx (zakaznik_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE kurz_dochazka (id BIGINT AUTO_INCREMENT, pracovnik_id BIGINT NOT NULL, kurz_hodina_id BIGINT NOT NULL, cena DECIMAL(18,2), stav VARCHAR(255), archived TINYINT DEFAULT 0 NOT NULL, INDEX kurz_hodina_id_idx (kurz_hodina_id), INDEX pracovnik_id_idx (pracovnik_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE personalista (id BIGINT AUTO_INCREMENT, zakaznik_id BIGINT NOT NULL, prih_heslo VARCHAR(255) NOT NULL, jmeno VARCHAR(255) NOT NULL, email VARCHAR(255) NOT NULL, created_at DATETIME, updated_at DATETIME, INDEX zakaznik_id_idx (zakaznik_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE specifikace_kurzu (id BIGINT AUTO_INCREMENT, zakaznik_id BIGINT NOT NULL, poradi BIGINT DEFAULT '1000', hodnota VARCHAR(255), INDEX zakaznik_id_idx (zakaznik_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE hodnoceni (id BIGINT AUTO_INCREMENT, head_id BIGINT NOT NULL, procenta_do MEDIUMINT NOT NULL, hodnoceni VARCHAR(255) NOT NULL, hodnoceni_text LONGTEXT, INDEX head_id_idx (head_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE pracovnik_kurz (id BIGINT AUTO_INCREMENT, pracovnik_id BIGINT NOT NULL, kurz_id BIGINT NOT NULL, archived TINYINT DEFAULT 0 NOT NULL, INDEX pracovnik_id_idx (pracovnik_id), INDEX kurz_id_idx (kurz_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE kurz_beh (id BIGINT AUTO_INCREMENT, kurz_id BIGINT, agentura_id BIGINT, beh_od DATE, beh_do DATE, poznamka LONGTEXT, pokrocilost VARCHAR(255), tema LONGTEXT, fj_meritko VARCHAR(255), fj_hodnota BIGINT, fj_tydne BIGINT, fj_cena DECIMAL(10,2), dotace VARCHAR(100), lektor_id BIGINT, archived TINYINT DEFAULT 0 NOT NULL, INDEX kurz_id_idx (kurz_id), INDEX agentura_id_idx (agentura_id), INDEX lektor_id_idx (lektor_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE kurz_hodina (id BIGINT AUTO_INCREMENT, kurz_beh_id BIGINT NOT NULL, datum DATE NOT NULL, zruseno VARCHAR(255) NOT NULL, tema LONGTEXT NOT NULL, ucebnice VARCHAR(255), kapitola VARCHAR(255), strana VARCHAR(255), gramatika VARCHAR(255), slovni_zasoba VARCHAR(255), poznamka LONGTEXT, archived TINYINT DEFAULT 0 NOT NULL, INDEX kurz_beh_id_idx (kurz_beh_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE cviceni (id BIGINT AUTO_INCREMENT, admin_id BIGINT, specifikace_spolecnost VARCHAR(255), specifikace_pokrocilost VARCHAR(255), specifikace_specializace VARCHAR(255), specifikace_gramatika VARCHAR(255), specifikace_fraze VARCHAR(255), typ TINYINT, nazev VARCHAR(255) NOT NULL UNIQUE, zadani LONGTEXT, created_at DATETIME, updated_at DATETIME, deleted TINYINT(1) DEFAULT '0' NOT NULL, INDEX admin_id_idx (admin_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE otazky (id BIGINT AUTO_INCREMENT, cviceni_id BIGINT NOT NULL, text LONGTEXT NOT NULL, uvod LONGTEXT, created_at DATETIME, updated_at DATETIME, INDEX cviceni_id_idx (cviceni_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE uchazec (id BIGINT AUTO_INCREMENT, personalista_id BIGINT, pracovnik_id BIGINT, kurz_beh_id BIGINT, test_zakaznik_id BIGINT, vlna_id BIGINT, zakaznik_id BIGINT, jmeno VARCHAR(255) NOT NULL, email VARCHAR(255), kod VARCHAR(20) UNIQUE, datum_prihlaseni DATETIME, datum_startu DATETIME, datum_ukonceni DATETIME, archiv TINYINT(1) DEFAULT '0', uspesnost INT DEFAULT 0, odeslani_emailu TINYINT, test_nazev VARCHAR(255) NOT NULL, test_cas MEDIUMINT NOT NULL, test_instrukce LONGTEXT, test_kriteria LONGTEXT, kod_odeslan DATETIME, user_ip VARCHAR(255), user_browser LONGTEXT, created_at DATETIME, updated_at DATETIME, INDEX personalista_id_idx (personalista_id), INDEX test_zakaznik_id_idx (test_zakaznik_id), INDEX vlna_id_idx (vlna_id), INDEX zakaznik_id_idx (zakaznik_id), INDEX pracovnik_id_idx (pracovnik_id), INDEX kurz_beh_id_idx (kurz_beh_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE pomocnici_personalisty (id BIGINT AUTO_INCREMENT, zakaznik_id BIGINT NOT NULL, min_dochazka BIGINT, min_uspesnost BIGINT, min_spokojenost BIGINT, min_hranice BIGINT, email_prekroceni BIGINT, email_nevyplneni BIGINT, email_zp BIGINT, min_dochazka_a BIGINT, min_uspesnost_a BIGINT, min_spokojenost_a BIGINT, min_hranice_a BIGINT, min_dochazka_date VARCHAR(255), min_uspesnost_date VARCHAR(255), min_spokojenost_date VARCHAR(255), min_hranice_date VARCHAR(255), email_trkniha_mesicne TINYINT(1) DEFAULT '0' NOT NULL, email_trkniha_mesicne_ke_dni BIGINT, email_dokumenty TINYINT(1) DEFAULT '0' NOT NULL, INDEX zakaznik_id_idx (zakaznik_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE moduly_zakaznik (id BIGINT AUTO_INCREMENT, zakaznik_id BIGINT NOT NULL, modul VARCHAR(255) NOT NULL, INDEX zakaznik_id_idx (zakaznik_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE pracovnik_rozpocet (id BIGINT AUTO_INCREMENT, pracovnik_id BIGINT NOT NULL, kurz_beh_id BIGINT NOT NULL, rozpocet DECIMAL(18,2), cerpano DECIMAL(18,2), archived TINYINT DEFAULT 0 NOT NULL, INDEX pracovnik_id_idx (pracovnik_id), INDEX kurz_beh_id_idx (kurz_beh_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE kurz_beh_testy (id BIGINT AUTO_INCREMENT, kurz_beh_id BIGINT NOT NULL, test_id BIGINT NOT NULL, archived TINYINT DEFAULT 0 NOT NULL, INDEX kurz_beh_id_idx (kurz_beh_id), INDEX test_id_idx (test_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE dotaznik_pracovnik (id BIGINT AUTO_INCREMENT, dotaznik_id BIGINT NOT NULL, pracovnik_id BIGINT NOT NULL, kurz_id BIGINT NOT NULL, dokonceno BIGINT, INDEX dotaznik_id_idx (dotaznik_id), INDEX pracovnik_id_idx (pracovnik_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE dotaznik_otazka_odpoved (id BIGINT AUTO_INCREMENT, dotaznik_otazka_id BIGINT NOT NULL, text_odpoved TEXT NOT NULL, body VARCHAR(255) NOT NULL, INDEX dotaznik_otazka_id_idx (dotaznik_otazka_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE dotaznik (id BIGINT AUTO_INCREMENT, zakaznik_id BIGINT NOT NULL, personalista_id BIGINT NOT NULL, nazev VARCHAR(255) NOT NULL, uvod_text LONGTEXT NOT NULL, email_hlavicka LONGTEXT NOT NULL, email_text LONGTEXT NOT NULL, email_personalista TINYINT(1) DEFAULT '0' NOT NULL, zasilat_upominku TINYINT(1) DEFAULT '0' NOT NULL, zasilat_upominku_dni_predem BIGINT, text_upominky LONGTEXT NOT NULL, policajt DOUBLE, zobrazit_vysledky_agenture TINYINT(1) DEFAULT '0' NOT NULL, datum_do DATETIME, typ VARCHAR(255), kod VARCHAR(20) UNIQUE, INDEX zakaznik_id_idx (zakaznik_id), INDEX personalista_id_idx (personalista_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE dotaznik_otazka (id BIGINT AUTO_INCREMENT, dotaznik_id BIGINT NOT NULL, text_otazka TEXT NOT NULL, typ BIGINT, INDEX dotaznik_id_idx (dotaznik_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE dotaznik_pracovnik_odpoved (id BIGINT AUTO_INCREMENT, dotaznik_otazka_id BIGINT NOT NULL, pracovnik_id BIGINT NOT NULL, odpoved TEXT, body VARCHAR(255), INDEX dotaznik_otazka_id_idx (dotaznik_otazka_id), INDEX pracovnik_id_idx (pracovnik_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE uchazec_text (id BIGINT AUTO_INCREMENT, uchazec_id BIGINT, cviceni_id BIGINT, otazka_id BIGINT, zdroj_otazka LONGTEXT, zadani LONGTEXT, odpoved LONGTEXT, spravne BIGINT, datum_vytvoreni DATETIME, datum_zodpovezeni DATETIME, datum_posledni_zmeny DATETIME, pocet_zodpovezeni BIGINT, bodu_na_otazku DECIMAL(3,1), spravne_gramatika VARCHAR(255), spatne_gramatika VARCHAR(255), user_ip VARCHAR(255), user_browser LONGTEXT, INDEX uchazec_id_idx (uchazec_id), INDEX cviceni_id_idx (cviceni_id), INDEX otazka_id_idx (otazka_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE pracovnik (id BIGINT AUTO_INCREMENT, zakaznik_id BIGINT NOT NULL, specifikace_pracovnik VARCHAR(255), jmeno VARCHAR(255) NOT NULL, prijmeni VARCHAR(255) NOT NULL, email VARCHAR(255), pozice VARCHAR(255), osobni_cislo VARCHAR(255), nakladove_stredisko VARCHAR(255), oddeleni VARCHAR(255), nadrizeny VARCHAR(255), poznamka LONGTEXT, heslo VARCHAR(255), pristup_app_pracovnik TINYINT(1) DEFAULT '0' NOT NULL, vidi_strediska VARCHAR(255), vidi_celou_dochazku TINYINT(1) DEFAULT '0' NOT NULL, archived TINYINT DEFAULT 0 NOT NULL, INDEX zakaznik_id_idx (zakaznik_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE agentura_beh_soubor (id BIGINT AUTO_INCREMENT, kurz_beh_id BIGINT NOT NULL, soubor VARCHAR(255), nazev VARCHAR(255) NOT NULL, INDEX kurz_beh_id_idx (kurz_beh_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE pracovnik_kurz_hodnoceni (id BIGINT AUTO_INCREMENT, pracovnik_id BIGINT NOT NULL, kurz_id BIGINT NOT NULL, datum DATE, lektor VARCHAR(255), hodnoceni LONGTEXT, archived TINYINT DEFAULT 0 NOT NULL, INDEX pracovnik_id_idx (pracovnik_id), INDEX kurz_id_idx (kurz_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE vlna (id BIGINT AUTO_INCREMENT, zakaznik_id BIGINT NOT NULL, test_zakaznik_id BIGINT NOT NULL, nazev VARCHAR(255) NOT NULL, INDEX zakaznik_id_idx (zakaznik_id), INDEX test_zakaznik_id_idx (test_zakaznik_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE mistnost (id BIGINT AUTO_INCREMENT, budova VARCHAR(255), patro VARCHAR(255), mistnost VARCHAR(255), poznamka LONGTEXT, zakaznik_id BIGINT, barva VARCHAR(255) DEFAULT 'bcbcbc' NOT NULL, INDEX zakaznik_id_idx (zakaznik_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE document_event (id BIGINT AUTO_INCREMENT, document_id BIGINT NOT NULL, zakaznik_id BIGINT NOT NULL, personalista_id BIGINT, agentura_id BIGINT, user_name VARCHAR(255) NOT NULL, document_name VARCHAR(255) NOT NULL, document_path VARCHAR(255), action VARCHAR(255) NOT NULL, created_at DATETIME, INDEX zakaznik_id_idx (zakaznik_id), INDEX agentura_id_idx (agentura_id), INDEX personalista_id_idx (personalista_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE hodnoceni_head (id BIGINT AUTO_INCREMENT, nazev VARCHAR(255) NOT NULL, PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE plan_kurz (id BIGINT AUTO_INCREMENT, kurz_id BIGINT NOT NULL, plan_id BIGINT NOT NULL, level_id BIGINT, met_attend BIGINT DEFAULT '100' NOT NULL, created_at DATETIME, updated_at DATETIME, INDEX kurz_id_idx (kurz_id), INDEX plan_id_idx (plan_id), INDEX level_id_idx (level_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE kurz_rozvrh (id BIGINT AUTO_INCREMENT, kurz_id BIGINT NOT NULL, den_v_tydnu TINYINT, od TIME, do TIME, mistnost_id BIGINT, poznamka LONGTEXT, INDEX kurz_id_idx (kurz_id), INDEX mistnost_id_idx (mistnost_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE gramatika (id BIGINT AUTO_INCREMENT, kod VARCHAR(255) NOT NULL, popis VARCHAR(255) NOT NULL, popis_full LONGTEXT, hodnoceni_head_id BIGINT NOT NULL, jazyk_id BIGINT, kategorie_id BIGINT, priorita BIGINT DEFAULT '100' NOT NULL, INDEX hodnoceni_head_id_idx (hodnoceni_head_id), INDEX jazyk_id_idx (jazyk_id), INDEX kategorie_id_idx (kategorie_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE lektor (id BIGINT AUTO_INCREMENT, jmeno VARCHAR(255) NOT NULL, prijmeni VARCHAR(255) NOT NULL, email VARCHAR(255), telefon VARCHAR(255), specializace VARCHAR(255), poznamka LONGTEXT, agentura_id BIGINT NOT NULL, INDEX agentura_id_idx (agentura_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE plan_level (id BIGINT AUTO_INCREMENT, plan_id BIGINT NOT NULL, position BIGINT DEFAULT '1' NOT NULL, name VARCHAR(255) NOT NULL, created_at DATETIME, updated_at DATETIME, INDEX plan_id_idx (plan_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE jazyk_gramatiky (id BIGINT AUTO_INCREMENT, nazev VARCHAR(255) NOT NULL UNIQUE, PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE dotaznik_kurz (dotaznik_id BIGINT, kurz_id BIGINT, PRIMARY KEY(dotaznik_id, kurz_id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE kategorie_gramatiky (id BIGINT AUTO_INCREMENT, nazev VARCHAR(255) NOT NULL UNIQUE, PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE zavora (id BIGINT AUTO_INCREMENT, zakaznik_id BIGINT NOT NULL, datum DATE NOT NULL, created_at DATETIME, updated_at DATETIME, INDEX zakaznik_id_idx (zakaznik_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE plan (id BIGINT AUTO_INCREMENT, zakaznik_id BIGINT NOT NULL, name VARCHAR(255) NOT NULL, type VARCHAR(255) DEFAULT 'static', period BIGINT, created_at DATETIME, updated_at DATETIME, INDEX zakaznik_id_idx (zakaznik_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE plan_pracovnik (id BIGINT AUTO_INCREMENT, pracovnik_id BIGINT NOT NULL, plan_id BIGINT NOT NULL, level_id BIGINT, is_met TINYINT(1) DEFAULT '0', met_date DATETIME NOT NULL, validity DATETIME, created_at DATETIME, updated_at DATETIME, INDEX pracovnik_id_idx (pracovnik_id), INDEX plan_id_idx (plan_id), INDEX level_id_idx (level_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE document (id BIGINT AUTO_INCREMENT, zakaznik_id BIGINT NOT NULL, parent_id BIGINT, kurz_id BIGINT, beh_id BIGINT, pracovnik_id BIGINT, agentura_id BIGINT, hodina_id BIGINT, name VARCHAR(255) NOT NULL, is_dir TINYINT(1) DEFAULT '0' NOT NULL, is_global TINYINT(1) DEFAULT '0' NOT NULL, filename VARCHAR(255), agentura_read TINYINT(1) DEFAULT '0' NOT NULL, agentura_write TINYINT(1) DEFAULT '0' NOT NULL, created_at DATETIME, updated_at DATETIME, slug VARCHAR(255), UNIQUE INDEX sluggable_idx (slug), INDEX zakaznik_id_idx (zakaznik_id), INDEX kurz_id_idx (kurz_id), INDEX beh_id_idx (beh_id), INDEX pracovnik_id_idx (pracovnik_id), INDEX agentura_id_idx (agentura_id), INDEX hodina_id_idx (hodina_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE pracovnik_vidi_kurz (id BIGINT AUTO_INCREMENT, pracovnik_id BIGINT NOT NULL, kurz_id BIGINT NOT NULL, INDEX pracovnik_id_idx (pracovnik_id), INDEX kurz_id_idx (kurz_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
ALTER TABLE agentura ADD FOREIGN KEY (zakaznik_id) REFERENCES zakaznik(id);
ALTER TABLE test ADD FOREIGN KEY (admin_id) REFERENCES admin(id);
ALTER TABLE test_cviceni ADD FOREIGN KEY (test_id) REFERENCES test(id);
ALTER TABLE test_cviceni ADD FOREIGN KEY (cviceni_id) REFERENCES cviceni(id);
ALTER TABLE test_cviceni ADD FOREIGN KEY (admin_id) REFERENCES admin(id);
ALTER TABLE test_zakaznik ADD FOREIGN KEY (zakaznik_id) REFERENCES zakaznik(id);
ALTER TABLE test_zakaznik ADD FOREIGN KEY (test_id) REFERENCES test(id);
ALTER TABLE test_zakaznik ADD FOREIGN KEY (hodnoceni_head_id) REFERENCES hodnoceni_head(id);
ALTER TABLE test_zakaznik ADD FOREIGN KEY (admin_id) REFERENCES admin(id);
ALTER TABLE specifikace_pracovnik ADD FOREIGN KEY (zakaznik_id) REFERENCES zakaznik(id);
ALTER TABLE kurz ADD FOREIGN KEY (zakaznik_id) REFERENCES zakaznik(id);
ALTER TABLE kurz_dochazka ADD FOREIGN KEY (pracovnik_id) REFERENCES pracovnik(id);
ALTER TABLE kurz_dochazka ADD FOREIGN KEY (kurz_hodina_id) REFERENCES kurz_hodina(id) ON DELETE CASCADE;
ALTER TABLE personalista ADD FOREIGN KEY (zakaznik_id) REFERENCES zakaznik(id);
ALTER TABLE specifikace_kurzu ADD FOREIGN KEY (zakaznik_id) REFERENCES zakaznik(id);
ALTER TABLE hodnoceni ADD FOREIGN KEY (head_id) REFERENCES hodnoceni_head(id);
ALTER TABLE pracovnik_kurz ADD FOREIGN KEY (pracovnik_id) REFERENCES pracovnik(id);
ALTER TABLE pracovnik_kurz ADD FOREIGN KEY (kurz_id) REFERENCES kurz(id);
ALTER TABLE kurz_beh ADD FOREIGN KEY (lektor_id) REFERENCES lektor(id);
ALTER TABLE kurz_beh ADD FOREIGN KEY (kurz_id) REFERENCES kurz(id);
ALTER TABLE kurz_beh ADD FOREIGN KEY (agentura_id) REFERENCES agentura(id);
ALTER TABLE kurz_hodina ADD FOREIGN KEY (kurz_beh_id) REFERENCES kurz_beh(id);
ALTER TABLE cviceni ADD FOREIGN KEY (admin_id) REFERENCES admin(id);
ALTER TABLE otazky ADD FOREIGN KEY (cviceni_id) REFERENCES cviceni(id);
ALTER TABLE uchazec ADD FOREIGN KEY (zakaznik_id) REFERENCES zakaznik(id);
ALTER TABLE uchazec ADD FOREIGN KEY (vlna_id) REFERENCES vlna(id);
ALTER TABLE uchazec ADD FOREIGN KEY (test_zakaznik_id) REFERENCES test_zakaznik(id);
ALTER TABLE uchazec ADD FOREIGN KEY (pracovnik_id) REFERENCES pracovnik(id);
ALTER TABLE uchazec ADD FOREIGN KEY (personalista_id) REFERENCES personalista(id);
ALTER TABLE uchazec ADD FOREIGN KEY (kurz_beh_id) REFERENCES kurz_beh(id);
ALTER TABLE pomocnici_personalisty ADD FOREIGN KEY (zakaznik_id) REFERENCES zakaznik(id);
ALTER TABLE moduly_zakaznik ADD FOREIGN KEY (zakaznik_id) REFERENCES zakaznik(id);
ALTER TABLE pracovnik_rozpocet ADD FOREIGN KEY (pracovnik_id) REFERENCES pracovnik(id);
ALTER TABLE pracovnik_rozpocet ADD FOREIGN KEY (kurz_beh_id) REFERENCES kurz_beh(id);
ALTER TABLE kurz_beh_testy ADD FOREIGN KEY (test_id) REFERENCES test(id);
ALTER TABLE kurz_beh_testy ADD FOREIGN KEY (kurz_beh_id) REFERENCES kurz_beh(id);
ALTER TABLE dotaznik_pracovnik ADD FOREIGN KEY (pracovnik_id) REFERENCES pracovnik(id);
ALTER TABLE dotaznik_pracovnik ADD FOREIGN KEY (dotaznik_id) REFERENCES dotaznik(id);
ALTER TABLE dotaznik_otazka_odpoved ADD FOREIGN KEY (dotaznik_otazka_id) REFERENCES dotaznik_otazka(id);
ALTER TABLE dotaznik ADD FOREIGN KEY (zakaznik_id) REFERENCES zakaznik(id);
ALTER TABLE dotaznik ADD FOREIGN KEY (personalista_id) REFERENCES personalista(id);
ALTER TABLE dotaznik_otazka ADD FOREIGN KEY (dotaznik_id) REFERENCES dotaznik(id);
ALTER TABLE dotaznik_pracovnik_odpoved ADD FOREIGN KEY (pracovnik_id) REFERENCES pracovnik(id);
ALTER TABLE dotaznik_pracovnik_odpoved ADD FOREIGN KEY (dotaznik_otazka_id) REFERENCES dotaznik_otazka(id);
ALTER TABLE uchazec_text ADD FOREIGN KEY (uchazec_id) REFERENCES uchazec(id) ON DELETE CASCADE;
ALTER TABLE uchazec_text ADD FOREIGN KEY (otazka_id) REFERENCES otazky(id);
ALTER TABLE uchazec_text ADD FOREIGN KEY (cviceni_id) REFERENCES cviceni(id);
ALTER TABLE pracovnik ADD FOREIGN KEY (zakaznik_id) REFERENCES zakaznik(id);
ALTER TABLE agentura_beh_soubor ADD FOREIGN KEY (kurz_beh_id) REFERENCES kurz_beh(id);
ALTER TABLE pracovnik_kurz_hodnoceni ADD FOREIGN KEY (pracovnik_id) REFERENCES pracovnik(id);
ALTER TABLE pracovnik_kurz_hodnoceni ADD FOREIGN KEY (kurz_id) REFERENCES kurz(id);
ALTER TABLE vlna ADD FOREIGN KEY (zakaznik_id) REFERENCES zakaznik(id);
ALTER TABLE vlna ADD FOREIGN KEY (test_zakaznik_id) REFERENCES test_zakaznik(id);
ALTER TABLE mistnost ADD FOREIGN KEY (zakaznik_id) REFERENCES zakaznik(id);
ALTER TABLE document_event ADD FOREIGN KEY (zakaznik_id) REFERENCES zakaznik(id) ON DELETE CASCADE;
ALTER TABLE document_event ADD FOREIGN KEY (personalista_id) REFERENCES personalista(id) ON DELETE NO ACTION;
ALTER TABLE document_event ADD FOREIGN KEY (agentura_id) REFERENCES agentura(id) ON DELETE NO ACTION;
ALTER TABLE plan_kurz ADD FOREIGN KEY (plan_id) REFERENCES plan(id) ON DELETE CASCADE;
ALTER TABLE plan_kurz ADD FOREIGN KEY (level_id) REFERENCES plan_level(id) ON DELETE CASCADE;
ALTER TABLE plan_kurz ADD FOREIGN KEY (kurz_id) REFERENCES kurz(id) ON DELETE CASCADE;
ALTER TABLE kurz_rozvrh ADD FOREIGN KEY (mistnost_id) REFERENCES mistnost(id);
ALTER TABLE kurz_rozvrh ADD FOREIGN KEY (kurz_id) REFERENCES kurz(id);
ALTER TABLE gramatika ADD FOREIGN KEY (kategorie_id) REFERENCES kategorie_gramatiky(id);
ALTER TABLE gramatika ADD FOREIGN KEY (jazyk_id) REFERENCES jazyk_gramatiky(id) ON DELETE CASCADE;
ALTER TABLE gramatika ADD FOREIGN KEY (hodnoceni_head_id) REFERENCES hodnoceni_head(id) ON DELETE CASCADE;
ALTER TABLE lektor ADD FOREIGN KEY (agentura_id) REFERENCES agentura(id);
ALTER TABLE plan_level ADD FOREIGN KEY (plan_id) REFERENCES plan(id) ON DELETE CASCADE;
ALTER TABLE dotaznik_kurz ADD FOREIGN KEY (kurz_id) REFERENCES kurz(id) ON DELETE CASCADE;
ALTER TABLE dotaznik_kurz ADD FOREIGN KEY (dotaznik_id) REFERENCES dotaznik(id) ON DELETE CASCADE;
ALTER TABLE zavora ADD FOREIGN KEY (zakaznik_id) REFERENCES zakaznik(id) ON DELETE CASCADE;
ALTER TABLE plan ADD FOREIGN KEY (zakaznik_id) REFERENCES zakaznik(id) ON DELETE CASCADE;
ALTER TABLE plan_pracovnik ADD FOREIGN KEY (pracovnik_id) REFERENCES pracovnik(id) ON DELETE CASCADE;
ALTER TABLE plan_pracovnik ADD FOREIGN KEY (plan_id) REFERENCES plan(id) ON DELETE CASCADE;
ALTER TABLE plan_pracovnik ADD FOREIGN KEY (level_id) REFERENCES plan_level(id) ON DELETE CASCADE;
ALTER TABLE document ADD FOREIGN KEY (zakaznik_id) REFERENCES zakaznik(id) ON DELETE CASCADE;
ALTER TABLE document ADD FOREIGN KEY (pracovnik_id) REFERENCES pracovnik(id) ON DELETE CASCADE;
ALTER TABLE document ADD FOREIGN KEY (kurz_id) REFERENCES kurz(id) ON DELETE CASCADE;
ALTER TABLE document ADD FOREIGN KEY (hodina_id) REFERENCES kurz_hodina(id) ON DELETE CASCADE;
ALTER TABLE document ADD FOREIGN KEY (beh_id) REFERENCES kurz_beh(id) ON DELETE CASCADE;
ALTER TABLE document ADD FOREIGN KEY (agentura_id) REFERENCES agentura(id) ON DELETE CASCADE;
ALTER TABLE pracovnik_vidi_kurz ADD FOREIGN KEY (pracovnik_id) REFERENCES pracovnik(id) ON DELETE CASCADE;
ALTER TABLE pracovnik_vidi_kurz ADD FOREIGN KEY (kurz_id) REFERENCES kurz(id) ON DELETE CASCADE;
