-- MADE BY GRUPPE 2 -- MATHIAS, RANI


drop table BordReservation
drop table Reservation
drop table Bord
drop table deliveryOrder
drop table PersonOrder
drop table Customer
drop table Person
drop table Distance
drop table MapAddress
drop table VodCity
drop table CommdityLineItem
drop table Commdity
drop table Product
go

create table VodCity(
city varchar(233) not null,
postalCode int primary key not null,
)

create table MapAddress(
mapAddressId int primary key identity(1,1) not null,
address varchar(233) not null,
postalCode int not null,
foreign key(postalCode) references VodCity(postalCode) on delete cascade,
)


create table Distance(
sourceAddressIdFk int null,
destinationAddressIdFk int null,
distance int not null,
foreign key (destinationAddressIdFk) references MapAddress(mapAddressId),
foreign key(sourceAddressIdFk) references MapAddress(mapAddressId)
)


create table Person(
	personId int primary key identity(1,1) not null,
	personName varchar(30) not null,
	mapAddressIdFk int not null,
	phone varchar(8) not null,
	mail varchar(30) not null,
	foreign key(mapAddressIdFk) references MapAddress(mapAddressId)
)

create table Customer(
	customerId int primary key identity(1,1) not null,
	personIdFK int not null,
	foodStamp int,
	foreign key (personIdFK) references Person(personId)
)

create table PersonOrder(
orderId int primary key identity(1,1) not null,
orderDate date not null,
totalPrice int null,
paymentDate date null,
customerIdFk int not null,
foreign key(customerIdFk) references Customer(customerId),
)

create table DeliveryOrder(
deliveryStatus int not null, -- 1 = true , 0 = false
readyStatus int not null, -- 1 = true , 0 = false
deliveryDate date null,
orderIdFk int not null,
mapAddressIdFk int null,
foreign key(orderIdFk) references PersonOrder(orderId),
foreign key(mapAddressIdFk) references MapAddress(mapAddressId) on delete cascade
)

create table Bord(
	bordNo int primary key identity(1,1) not null,
	personQuantity int not null,
)

create table Reservation(
	reservationId int primary key identity(1,1) not null,
	startDate date not null,
	startTime varchar(8) not null,
	endTime varchar(8) not null,
	partysize int not null,
	customerId int not null,
	foreign key(customerId) references Customer(customerId)
)

create table BordReservation(
	bordNoFK int not null,
	reservationIdFK int not null,
	foreign key (bordNoFK) references Bord(bordNo),
	foreign key (reservationIdFK) references Reservation(reservationId) on delete cascade
)

create table Product(
	productNo int primary key identity(1,1) not null,
	name varchar(35) not null,
	description varchar(50) null,
	purhasePrice float not null
)
create table Commdity(
	commdityId int primary key identity(1,1) not null,
	comName varchar(35) not null,
	price float not null,
	comDescription varchar(50) null
)
create table CommdityLineItem(
	commdityLineItemId int primary key identity(1,1) not null,
	amount int not null,
	productNoFK int not null,
	commdityIdFK int not null,
	foreign key (productNoFK) references Product(productNo),
	foreign key (commdityIdFK) references Commdity(commdityId),
)
-- Hardcoded inserts

--

--City
insert into VodCity values('Aalborg', 9200);
insert into VodCity values('Vodskov', 9310);

--MapAdress
insert into MapAddress values('Follingsvej 101', 9200); --1
insert into MapAddress values('Smedevejs 3', 9310); --2

insert into MapAddress values('Skippermindevej 1', 9310); --3
insert into MapAddress values('Skippermindevej 4A', 9310); --4
insert into MapAddress values('Rugvangen 8', 9310); --5
insert into MapAddress values('Anesmindevej 12', 9310); -- 6

insert into MapAddress values('Skippermindevej 5', 9310); --7
insert into MapAddress values('Anesmindevej 5', 9310); --8

insert into MapAddress values('Skippermindevej 4', 9310); --9

insert into MapAddress values('Skippermindevej 7', 9310); --10

insert into MapAddress values('Vodskovvej 62', 9310); --11
insert into MapAddress values('Vodskovvej 64', 9310); --12
insert into MapAddress values('Vodskovvej 63', 9310); --13
insert into MapAddress values('Vodskovvej 61', 9310); --14
insert into MapAddress values('Vodskov 51', 9310); --15

insert into MapAddress values('Skippermindevej 14', 9310); --16

insert into MapAddress values('Skippermindevej 11', 9310); --17

insert into MapAddress values('Skippermindevej 12', 9310); --18
--Distance

--source-destination-weight

--- Skippermindevej 1 and destination
insert into Distance values(3, 6, 3);
insert into Distance values(3, 5, 2);
insert into Distance values(3, 4, 4);
insert into Distance values(3, 11, 3);
insert into Distance values(3, 12, 4);

-- source anes12 and its destinations
insert into Distance values(6, 8, 9);
insert into Distance values(6, 5, 2);
insert into Distance values(6, 7, 8);
insert into Distance values(6, 3, 3);

-- source anes5 and its destinations
insert into Distance values(8, 7, 3);
insert into Distance values(8, 10, 9);

-- source skip5 and its destinations
insert into Distance values(7, 5, 7);
insert into Distance values(7, 8, 3);
insert into Distance values(7, 4, 6);
insert into Distance values(7, 9, 4);
insert into Distance values(7, 10, 2);
insert into Distance values(7, 6, 8);

-- source skip12 and its destinations
insert into Distance values(18, 16, 6);
insert into Distance values(18, 17, 3);
insert into Distance values(18, 10, 1);

-- source skip14 and its destinations
insert into Distance values(16, 18, 3);


-- source skip 11 and its destinations
insert into Distance values(17, 18, 6);

-- source skip 4 and its destinations
insert into Distance values(9, 7, 4);

-- source rug8 and its destinations
insert into Distance values(5, 7, 7);
insert into Distance values(5, 4, 5 );
insert into Distance values(5, 3, 2);
insert into Distance values(5, 12, 2);


-- source skip4A and its destinations
insert into Distance values(4, 8, 6);
insert into Distance values(4, 5, 5);
insert into Distance values(4, 3, 4);

--source vod63 and its destinations
insert into Distance values(13, 3, 4);
insert into Distance values(13, 14, 1);


--source vod61 and its destinations
insert into Distance values(14, 13, 1);
insert into Distance values(14, 15, 2);


--source vod51 and its destinations
insert into Distance values(15, 14, 2);

--source vod62 and its destinations
insert into Distance values(11, 3, 2);
insert into Distance values(11, 12, 5);

-- source vod64 and its destinations
insert into Distance values(12, 11, 5);


--Person
insert into Person values('Mathias', 1, '22424678', 'huskmigffs@gmail.com');
insert into Person values('Rani', 2, '12345678', 'ilikehotmail@hotmail.com');
insert into Person values('Rimon', 3, '12345671', 'Skippermindevej1@hotmail.com');
insert into Person values('Børge', 4, '12345672', 'Skippermindevej2@hotmail.com');
insert into Person values('Simon', 5, '12345673', 'Skippermindevej3@hotmail.com');
insert into Person values('Dorte', 6, '12345674', 'Vodskovvej62@hotmail.com');
--Customer
insert into Customer values(1, 30);
insert into Customer values(2, 30);
insert into Customer values(3, 50);
insert into Customer values(4, 20);
insert into Customer values(5, 10);
insert into Customer values(6, 900);

--PersonOrder

insert into PersonOrder values('2018-11-29', 400, null, 4); --O.1
insert into PersonOrder values('2018-11-29', 400, null, 5); --O.2
insert into PersonOrder values('2018-11-29', 400, null, 6); --O.3

-- DeliverOrder

insert into DeliveryOrder values(0, 1, null, 3, 3); --SKIPPERMINDEVEJ 3
insert into DeliveryOrder values(0, 1, null, 1, 16); --SKIPPERMINEVEJ 16
insert into DeliveryOrder values(0, 1, null, 2, 10); --IDK
insert into DeliveryOrder values(0, 1, null, 2, 11); --IDK

--Borde
insert into Bord values(5);
insert into Bord values(10);
insert into Bord values(4);
insert into Bord values(7);
--Reservation
insert into Reservation values('2018-11-29', '15:30', '16:30', 5, 1);
insert into Reservation values('2019-11-29', '13:30', '14:30', 5, 1);
insert into Reservation values('2029-11-29', '12:30', '17:30', 5, 1);
insert into Reservation values('2011-11-29', '17:30', '18:30', 5, 1);
insert into Reservation values('2015-11-29', '12:10', '12:25', 5, 1);
insert into Reservation values('2019-11-29', '14:10', '16:25', 5, 1);
insert into Reservation values('2023-11-29', '12:11', '12:12', 5, 1);
--BordReservation
insert into BordReservation values(1, 1)
insert into BordReservation values(1, 2)
insert into BordReservation values(1, 3)
insert into BordReservation values(2, 4)
insert into BordReservation values(3, 5)
insert into BordReservation values(4, 6)