-- Tabellen erstellen
drop table lieferanten cascade constraints;
drop table artikel cascade constraints;
drop table artikellager cascade constraints;
drop table artikelbewegungen cascade constraints;
drop table kunden cascade constraints;
drop table auftraege cascade constraints;
drop table auftragspositionen cascade constraints;
drop table rechnungen cascade constraints;
drop table sachbearbeiter cascade constraints;


create table lieferanten(
lieferantennummer varchar(10) not null,
name varchar(30), 
strasse varchar(20),
hausnummer varchar(5),
plz int,
ort varchar(20),
primary key(lieferantennummer));


create table artikel(
artikel_nr varchar(27) not null,
bezeichnung varchar(40),
warengruppe int,
mengeneinheit varchar(10),
verkaufspreis number(10,2),
einkaufspreis number(10,2),
lieferzeit int,
bestand_lager int,
bestand_minimum int,
jahresumsatz number(10,2),
vorjahresumsatz number(10,2),
primary key(artikel_nr)); 


--anlegen artikellager
create table artikellager(
artikel_nr varchar(27) not null,
lager_nr int not null constraint lnr check(lager_nr > 0  and lager_nr < 4),
bestand_lager int not null,
primary key(artikel_nr, lager_nr),
foreign key(artikel_nr) references artikel(artikel_nr));


--anlegen Artikelbewegungen
create table artikelbewegungen(
artikel_nr varchar(27),
datum_uhrzeit timestamp not null,
auftrag_nr number(4),
position number(2),
kunde_nr number(5),
menge number (10),
verkaufspreis number(10,2),
verkaufswert number(10,2),
einstandspreis number(10,2),
einstandswert number(10,2),
primary key(artikel_nr, datum_uhrzeit));


--anlegen Kunden
create table kunden(
kunde_nr int not null,
name varchar(30),
strasse varchar(30),
plz_strasse varchar(5),
land varchar(3),
ort varchar(30),
kundengruppe int,
branche varchar(30),
vertreter_nr int,
gesperrt_kz int constraint k1 check(gesperrt_kz between 0 and 1)  not null,
datum_kreditauskunft date,
kreditlimit number(10,2),
saldo_offene_rechnungen number(10,2),
jahresumsatz number(10,2),
vorjahresumsatz number(10,2),
primary key(kunde_nr));


--anlegen Auftraege
create table auftraege(
auftrag_nr int not null,
auftrag_datum date default current_date,
kunde_nr int not null,
vertreter_nr int,
rabatt float,
sachbearbeiter_nr int,
porto number(10,2),
primary key(auftrag_nr));



--anlegen auftragspositionen
create table auftragspositionen(
auftrag_nr int,
position_nr int,
position_aktiv int constraint ap1 check(position_aktiv between 0 and 1),
artikel_nr varchar(27),
verkaufsmenge int,
verkaufspreis number(10,2),
termin date,
offen int,
geliefert int,
berechnet int,
primary key(auftrag_nr, position_nr));


--anlegen Rechnungen
create table rechnungen(
rechnung_nr int,
rechnung_datum date,
kunde_nr int,
auftrag_nr int,
sachbearbeiter_nr int,
warenwert number(10,2),
mwst_prozentsatz float,
rechnung_betrag number(10,2),
datum_faellig date,
datum_mahnung_1 date,
datum_mahnung_2 date,
zahlbetrag_gesamt number(10,2),
zahldatum date,
bezahlt number(1) constraint r1 check(bezahlt between 0 and 1),
primary key(rechnung_nr));


create table sachbearbeiter(
sachbearbeiter_nr int not null,
name varchar(30),
primary key(sachbearbeiter_nr));