DROP TABLE kfoqsdb."QsEintrag";
DROP TABLE kfoqsdb."FileUpload";
DROP TABLE kfoqsdb."Benachrichtigung";
DROP TABLE kfoqsdb."Auswertung";
DROP TABLE kfoqsdb."Traeger";
DROP TABLE kfoqsdb."Partner";
DROP SEQUENCE public."kfoqsdb_seq";

CREATE SEQUENCE public.kfoqsdb_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;
ALTER TABLE public.kfoqsdb_seq
  OWNER TO kfoqsdb;
  
-- Table: kfoqsdb.traeger
CREATE TABLE kfoqsdb."Traeger"
(
  "Code" character varying NOT NULL,
  "QSBerechtigt" boolean,
  "Sanktionsberechtigt" boolean,
  version bigint NOT NULL DEFAULT 0,
  CONSTRAINT "PK_Traeger" PRIMARY KEY ("Code")
)
WITH (
  OIDS=FALSE
);
ALTER TABLE kfoqsdb."Traeger"
  OWNER TO kfoqsdb;

-- Table: kfoqsdb.auswertung
CREATE TABLE kfoqsdb."Auswertung"
(
  "ID" bigint NOT NULL DEFAULT nextval('kfoqsdb_seq'::regclass),
  "DokumentId" character varying,
  "Mandant" character varying,
  "Produkt" character varying,
  "Titel" character varying,
  "TraegerId" character varying,
  version bigint NOT NULL DEFAULT 0,
  "File" bytea,
  "Erstelldatum" timestamp without time zone NOT NULL,
  CONSTRAINT "PK_Auswertung" PRIMARY KEY ("ID"),
  CONSTRAINT "FK_Auswertung_Traeger" FOREIGN KEY ("TraegerId")
      REFERENCES kfoqsdb."Traeger" ("Code") MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
ALTER TABLE kfoqsdb."Auswertung"
  OWNER TO kfoqsdb;


-- Table: kfoqsdb.benachrichtigung
CREATE TABLE kfoqsdb."Benachrichtigung"
(
  "ID" bigint NOT NULL DEFAULT nextval('kfoqsdb_seq'::regclass),
  "Gelesen" boolean,
  "Titel" character varying,
  "Text" character varying,
  "TraegerId" character varying,
  "AuswertungId" bigint,
  version bigint NOT NULL DEFAULT 0,
  "Erstelldatum" timestamp without time zone NOT NULL,
  CONSTRAINT "PK_Benachrichtigung" PRIMARY KEY ("ID"),
  CONSTRAINT "FK_Benachrichtigung_Auswertung" FOREIGN KEY ("AuswertungId")
      REFERENCES kfoqsdb."Auswertung" ("ID") MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT "FK_Benachrichtigung_Traeger" FOREIGN KEY ("TraegerId")
      REFERENCES kfoqsdb."Traeger" ("Code") MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
ALTER TABLE kfoqsdb."Benachrichtigung"
  OWNER TO kfoqsdb;


-- Table: kfoqsdb.partner
CREATE TABLE kfoqsdb."Partner"
(
  "PartnerArt" character varying(2),
  "PartnerBundesland" character varying,
  "PartnerName" character varying,
  "VpNr" character varying NOT NULL,
  version bigint,
  CONSTRAINT "PK_Partner" PRIMARY KEY ("VpNr")
)
WITH (
  OIDS=FALSE
);
ALTER TABLE kfoqsdb."Partner"
  OWNER TO kfoqsdb;
  
-- Table: kfoqsdb."FileUpload"
CREATE TABLE kfoqsdb."FileUpload"
(
  "Id" integer NOT NULL DEFAULT nextval('kfoqsdb_seq'::regclass),
  "Benutzer" character varying NOT NULL,
  "Datum" timestamp without time zone NOT NULL,
  "File" bytea,
  "Zeilen" integer,
  version bigint,
  "Traeger" character varying NOT NULL,
  "FileName" character varying NOT NULL,
  "Fehlerhaft" boolean NOT NULL DEFAULT false,
  CONSTRAINT "PK_FileUpload" PRIMARY KEY ("Id")
)
WITH (
  OIDS=FALSE
);
ALTER TABLE kfoqsdb."FileUpload"
  OWNER TO kfoqsdb;

-- Table: kfoqsdb.qseintrag
CREATE TABLE kfoqsdb."QsEintrag"
(
  "ID" bigint NOT NULL DEFAULT nextval('kfoqsdb_seq'::regclass),
  "Behandlungsabbruch" date,
  "ErfolgsannahmeErreicht" boolean,
  "Erstelldatum" date,
  "Ersteller" character varying(25),
  "Fachgebiet" integer,
  "GeprueftInSanktionsverfahren" boolean,
  "HBDatumBehandlungsbeginnLE" date,
  "HBDatumBehandlungsendeLE" date,
  "IBBehandlungsendeLE" date,
  "IndikationDatumLE" date,
  "IndikationDatumQS" date,
  "IndikationLE" character varying(10),
  "IndikationQS" character varying(10),
  "IOTNDatumLE" date,
  "IOTNDatumQS" date,
  "Leistungsart" character varying,
  "LZTraeger" character varying,
  "PARAnfangswert" integer,
  "PAREndwert" integer,
  "QSTraeger" character varying,
  "Stornogrund" integer,
  "VsNr" character varying,
  "VpNr" character varying,
  "ABRTraeger" character varying,
  version bigint,
  "StornoDatum" date,
  "StornoBenutzer" character varying(25),
  "Status" character varying,
  "IOTNLE" character varying(2),
  "IOTNQS" character varying(2),
  "FileUploadId" bigint,
  CONSTRAINT "PK_QsEintrag" PRIMARY KEY ("ID"),
  CONSTRAINT "FK_QsEintrag_FileUpload" FOREIGN KEY ("FileUploadId")
      REFERENCES kfoqsdb."FileUpload" ("Id") MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT "FK_QsEintrag_Partner" FOREIGN KEY ("VpNr")
      REFERENCES kfoqsdb."Partner" ("VpNr") MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
ALTER TABLE kfoqsdb."QsEintrag"
  OWNER TO kfoqsdb;
  
-- View: kfoqsdb."InvolvierteTraeger"

-- DROP VIEW kfoqsdb."InvolvierteTraeger";

CREATE OR REPLACE VIEW kfoqsdb."InvolvierteTraeger" AS 
	SELECT DISTINCT "QsEintrag"."VsNr",
	unnest(ARRAY["QsEintrag"."LZTraeger", "QsEintrag"."QSTraeger", "QsEintrag"."ABRTraeger"]) AS "TraegerCode"
	FROM kfoqsdb."QsEintrag";

ALTER TABLE kfoqsdb."InvolvierteTraeger"
OWNER TO kfoqsdb;