/*Koden kan kjøres herfra til linje 70 uten problemer,
da dropper den alt vi har, så lager vi tre tables:
customer, product og order (vi legger til orderline senere)
så fyller vi inn litt random data for å ha noe i tablesene. */

drop schema test123;

create schema test123;
use test123;

#-------------CUSTOMER START-------------#
create table customer (
c_id numeric,
c_email varchar(80),
c_phone_number numeric(8),
c_first_name varchar (80),
c_last_name varchar (80),
c_address varchar (80),
c_zip_code numeric (4),
c_city varchar (45),
primary key (c_id)
);

insert into customer(c_id, c_email, c_phone_number, c_first_name, c_last_name, c_address, c_zip_code, c_city )
values(1,"brage.fagstad@hotmail.com", 49234591, "Brage","Fagstad","Fjellveien 6b",4656, "Kristiansand"),
(2,"Per123@hotmail.com", 39014839, "Per","Olav","Havreveien 70",4635, "Kristiansand"),
(3,"elias@hotmail.com", 34143431, "Elias","Brynestad","Parkveien 20a",4630, "Kristiansand"),
(4,"klara@hotmail.com", 65817372, "Klara","Brynestad","Ibsensgate 85",5052, "Bergen"),
(5,"anders@hotmail.com", 48371778, "Anders","Brynhildsen","Oslo S 2",2230, "Oslo"),
(6,"johanne@hotmail.com", 64918382, "Johanne","Brygde","Pedersgate 13",3823, "Trondheim");
#-------------CUSTOMER SLUTT-------------#

#-------------PRODUCT START-------------#
create table product(
p_id numeric,
p_name varchar(80),
p_price numeric,
p_quantity numeric,
constraint p_PK primary key (p_id)
);

insert into product(p_id, p_name, p_price, p_quantity)
values(1, "Stol", 100, 4), (2, "Bord", 200, 1), (3, "Kino", 20000, 1), (4, "Hest", 9000, 1);
#-------------PRODUCT SLUTT-------------#

#-------------ORDRE START-------------#
create table ordre(
ord_id numeric(30),
ord_date DATE,
ord_delivery_date DATE,
ord_billing_address varchar(50),
ord_status varchar(20),
c_id numeric,
p_id numeric,
constraint ordre_cFK foreign key (c_id) references customer (c_id),
constraint ordre_pFK foreign key (p_id) references product (p_id),
constraint ordre_pk primary key (ord_id)
);

insert into ordre(ord_id, ord_date, ord_delivery_date, ord_billing_address, ord_status, c_id, p_id)
values (1,20180913,20180917, "fjellveien 5b", "shipping", 1, 1),
(2,20180912,20180918, "fjellveien 5b", "shipping", 1, 2),
(3,20180911,20180918, "pedersvei 20", "shipping", 2, 1),
(4,20180910,20180913, "pedersvei 20", "delivered", 2, 2),
(5,20180912,20180915, "dalen 17a", "shipping", 3, 2),
(6,20180813,20180823, "dalen 17a", "processing", 3, 1),
(7,20180813,20180818, "fjellveien 5b", "processing", 1, 3),
(8,20180813,20180819, "pedersvei 20", "processing", 2, 4),
(9,20180813,20180815, "dalen 17a", "processing", 3, 4);
#-------------ORDRE SLUTT-------------#