CREATE TABLE "diagnoza" (
  "icd_10" INTEGER PRIMARY KEY,
  "nazev" VARCHAR(128) NOT NULL
);

CREATE TABLE "lekar" (
  "id_lekar" SERIAL PRIMARY KEY,
  "jmeno" VARCHAR(32) NOT NULL,
  "prijmeni" VARCHAR(32) NOT NULL
);

CREATE TABLE "pojistovna" (
  "kod_pojistovny" INTEGER PRIMARY KEY,
  "nazev" VARCHAR(128) UNIQUE NOT NULL
);

CREATE TABLE "karta_pacienta" (
  "id_karta" SERIAL PRIMARY KEY,
  "jmeno" VARCHAR(32) NOT NULL,
  "prijmeni" VARCHAR(32) NOT NULL,
  "datum_narozeni" DATE NOT NULL,
  "pojistovna_kod_pojistovny" INTEGER NOT NULL
);

CREATE INDEX "idx_karta_pacienta__pojistovna" ON "karta_pacienta" ("pojistovna_kod_pojistovny");

ALTER TABLE "karta_pacienta" ADD CONSTRAINT "fk_karta_pacienta__pojistovna" FOREIGN KEY ("pojistovna_kod_pojistovny") REFERENCES "pojistovna" ("kod_pojistovny");

CREATE TABLE "vykon" (
  "id_vykon" SERIAL PRIMARY KEY,
  "popis" TEXT NOT NULL,
  "datum" DATE NOT NULL,
  "cena" INTEGER NOT NULL,
  "karta_pacienta_id_karta" INTEGER NOT NULL,
  "lekar_id_lekar" INTEGER NOT NULL
);

CREATE INDEX "idx_vykon__karta_pacienta" ON "vykon" ("karta_pacienta_id_karta");

CREATE INDEX "idx_vykon__lekar" ON "vykon" ("lekar_id_lekar");

ALTER TABLE "vykon" ADD CONSTRAINT "fk_vykon__karta_pacienta" FOREIGN KEY ("karta_pacienta_id_karta") REFERENCES "karta_pacienta" ("id_karta");

ALTER TABLE "vykon" ADD CONSTRAINT "fk_vykon__lekar" FOREIGN KEY ("lekar_id_lekar") REFERENCES "lekar" ("id_lekar");

CREATE TABLE "vysetreni" (
  "id_vysetreni" SERIAL PRIMARY KEY,
  "popis" TEXT NOT NULL,
  "datum" DATE NOT NULL,
  "karta_pacienta_id_karta" INTEGER NOT NULL,
  "lekar_id_lekar" INTEGER NOT NULL
);

CREATE INDEX "idx_vysetreni__karta_pacienta" ON "vysetreni" ("karta_pacienta_id_karta");

CREATE INDEX "idx_vysetreni__lekar" ON "vysetreni" ("lekar_id_lekar");

ALTER TABLE "vysetreni" ADD CONSTRAINT "fk_vysetreni__karta_pacienta" FOREIGN KEY ("karta_pacienta_id_karta") REFERENCES "karta_pacienta" ("id_karta");

ALTER TABLE "vysetreni" ADD CONSTRAINT "fk_vysetreni__lekar" FOREIGN KEY ("lekar_id_lekar") REFERENCES "lekar" ("id_lekar");

CREATE TABLE "vysetreni_has_diagnoza" (
  "diagnoza_ICD_10" INTEGER NOT NULL,
  "vysetreni_id_vysetreni" INTEGER NOT NULL,
  PRIMARY KEY ("diagnoza_ICD_10", "vysetreni_id_vysetreni")
);

CREATE INDEX "idx_diagnoza_vysetreni" ON "vysetreni_has_diagnoza" ("vysetreni_id_vysetreni");

ALTER TABLE "vysetreni_has_diagnoza" ADD CONSTRAINT "fk_diagnoza_vysetreni__diagnoza" FOREIGN KEY ("diagnoza_ICD_10") REFERENCES "diagnoza" ("icd_10");

ALTER TABLE "vysetreni_has_diagnoza" ADD CONSTRAINT "fk_diagnoza_vysetreni__vysetreni" FOREIGN KEY ("vysetreni_id_vysetreni") REFERENCES "vysetreni" ("id_vysetreni")