\c template1
drop database "ibh2";
create database "ibh2" with encoding='unicode';
\c ibh2

create table auftragsarten (
    auftragsart_id serial primary key,
    name text not null,
    tabellenname text not null,
    tabellen_id_name text not null
);

create table auftraege (
    auftrag_id serial primary key,
    auftragsart_id int references auftragsarten on delete restrict on update cascade not null,
	bezeichnung text not null
);

create table semester (
    semester_id serial primary key,
    name text not null
);

create table fachbereiche (
    fachbereich_id serial primary key,
    name text not null
);

create table studiengaenge (
    studiengang_id serial primary key,
    fachbereich_id int references fachbereiche on delete cascade on update cascade not null,
    name text not null,
    kuerzel text not null
);

create table ressourcenarten (
    ressourcenart_id serial primary key,
    name text not null,
    tabellenname text not null,
    tabellen_id_name text not null,
    bezeichnungsausdruck text not null
);

create table ressourcen (
    ressource_id serial primary key,
    ressourcenart_id integer not null references ressourcenarten on delete restrict on update cascade,
    menge integer not null,
    constraint menge_groessergleich_null check (menge >= 0)
);


create table raumtypen (
    raumtyp_id serial primary key,
    bezeichnung text not null
);

create table standorte (
    standort_id serial primary key,
    kurzbezeichnung text not null,
    bezeichnung text not null
);

create table gebaeude (
    gebaeude_id serial primary key,
    standort_id int not null references standorte on delete cascade on update cascade,
    kurzbezeichnung text not null,
    bezeichnung text not null,
    nummer text not null
);

create table raeume (
    raum_id serial primary key,
    ressource_id integer not null unique references ressourcen on delete cascade on update cascade,
    gebaeude_id int not null references gebaeude on delete cascade on update cascade,
	raumtyp_id int not null references raumtypen on delete cascade on update cascade,
	nummer text,
	flaeche numeric(8,2),
	bemerkung text
);

create table auftragsressourcen (
	auftragsressource_id serial primary key,
	ressource_id integer not null references ressourcen on delete restrict on update cascade,
	auftrag_id integer not null references auftraege on update cascade on delete cascade,
	menge integer not null,
	status integer default 0,
    unique(ressource_id, auftrag_id),
    constraint gueltiger_status check (status between 0 and 2)
);

create table belegungen (
	belegung_id serial primary key,
	auftragsressource_id integer not null references auftragsressourcen on update cascade on delete cascade,
	von timestamp not null,
	bis timestamp not null,
    constraint von_kleiner_bis check (von < bis)
);

CREATE FUNCTION iGetNumOfBookedRes(integer, timestamp, timestamp) RETURNS numeric AS '
SELECT 
    CASE WHEN (MAX(kumulierte) IS NULL) THEN 0 ELSE MAX(kumulierte) END
    FROM
    (SELECT 
        (SELECT 
            SUM(dynmenge) 
            FROM
            (SELECT
                -- Ressource wird ausgeliehen
                von, menge AS dynmenge
                FROM auftragsressourcen JOIN belegungen USING (auftragsressource_id)
                WHERE ressource_id = $1 AND von > $2 AND von < $3
            UNION SELECT
                -- Ressource wird zurückgegeben
                bis AS von, -menge AS dynmenge
                FROM auftragsressourcen JOIN belegungen USING (auftragsressource_id)
                WHERE ressource_id = $1 AND bis > $2 AND bis < $3
            UNION SELECT
                -- Anfangsstand
                $2, SUM(auftragsressourcen.menge) AS dynmenge
                FROM auftragsressourcen JOIN belegungen USING (auftragsressource_id)
                WHERE ressource_id = $1 AND von <= $2 and bis > $2
            ) AS bel1 
            WHERE bel1.von <= bel2.von
        ) AS kumulierte
        FROM
        (SELECT
            -- Ressource wird ausgeliehen
            von, menge AS dynmenge
            FROM auftragsressourcen JOIN belegungen USING (auftragsressource_id)
            WHERE ressource_id = $1 AND von > $2 AND von < $3
        UNION SELECT
            -- Ressource wird zurückgegeben
            bis AS von, -menge AS dynmenge
            FROM auftragsressourcen JOIN belegungen USING (auftragsressource_id)
            WHERE ressource_id = $1 AND bis > $2 AND bis < $3
        UNION SELECT
            -- Anfangsstand
            $2, SUM(auftragsressourcen.menge) AS dynmenge
            FROM auftragsressourcen JOIN belegungen USING (auftragsressource_id)
            WHERE ressource_id = $1 AND von <= $2 and bis > $2
        ) AS bel2
    ) AS belegte
' LANGUAGE SQL;

CREATE FUNCTION bGetNumOfBookedSingleRes(integer, timestamp, timestamp) RETURNS boolean AS '
select exists (select * from auftragsressourcen JOIN belegungen USING (auftragsressource_id) WHERE ressource_id = $1 AND bis > $2    and von < $3)
' LANGUAGE SQL;