--
-- Sletter tabellene
--

DROP TABLE IF EXISTS Utleie;
DROP TABLE IF EXISTS Kunde;
DROP TABLE IF EXISTS Sykkel;
DROP TABLE IF EXISTS Modell;


--
-- Tabellstruktur for tabell Kunde
--

CREATE TABLE IF NOT EXISTS Kunde (
  KNr       INTEGER     AUTO_INCREMENT,
  Fornavn   VARCHAR(20),
  Etternavn VARCHAR(50),
  Mobil     VARCHAR(15),
  PRIMARY KEY (KNr)
);



--
-- Tabellstruktur for tabell Modell
--

CREATE TABLE IF NOT EXISTS Modell (
  MNr        INTEGER,
  Fabrikk    VARCHAR(50),
  Betegnelse VARCHAR(50),
  Kategori   VARCHAR(20),
  Dagpris    DECIMAL(8,2),
  PRIMARY KEY (MNr)
);


--
-- Tabellstruktur for tabell Sykkel
--

CREATE TABLE IF NOT EXISTS Sykkel (
  MNr    INTEGER,
  KopiNr SMALLINT,
  Ramme  SMALLINT,
  Farge  VARCHAR(20),
  PRIMARY KEY (MNr, KopiNr),
  FOREIGN KEY (MNr) REFERENCES Modell (MNr)
);


--
-- Tabellstruktur for tabell Utleie
--

CREATE TABLE IF NOT EXISTS Utleie (
  KNr    INTEGER,
  MNr    INTEGER,
  KopiNr SMALLINT,
  TidUt  DATETIME,
  TidInn DATETIME,
  PRIMARY KEY (MNr, KopiNr, TidUt),
  FOREIGN KEY (KNr) REFERENCES Kunde (KNr),
  FOREIGN KEY (MNr, KopiNr) REFERENCES Sykkel (MNr, KopiNr)
);


INSERT INTO Kunde(KNr, Fornavn, Etternavn, Mobil) VALUES
(1, 'Varg', 'Virrum', '99887766'),
(2, 'Trude', 'Stein', '44556677'),
(4, 'Katinka', 'Fosheim', '45671234'),
(5, 'Robert', 'Romman', '98765432'),
(6, 'Jon', 'Vein', '81726354');


INSERT INTO Modell(MNr, Fabrikk, Betegnelse, Kategori, Dagpris) VALUES
(1, 'Avante', 'Birken classic', 'Terreng', '75.00'),
(2, 'Trailo', 'Askeladden', 'Hybrid', '55.00'),
(3, 'Perimeter', 'Downtown', 'Bysykkel', '120.00'),
(4, 'Avante', 'Dovregubben', 'Terreng', '105.00'),
(5, 'Avante', 'Atlantis', 'Landevei', '90.00'),
(6, 'Perimeter', 'Medita', 'Terreng', '95.00'),
(7, 'Avante', 'Aeroflyt', 'Landevei', '115.00'),
(8, 'Trailo', 'MultiX', 'Hybrid', '150.00'),
(9, 'Trailo', 'Trailfinder', 'Terreng', '140.00'),
(10, 'Perimeter', 'Vasaknekk', 'Terreng', '50.00'),
(11, 'Avante', 'Olebrum', 'Hybrid', '125.00'),
(12, 'Trailo', 'Alfavei', 'Terreng', '80.00');

INSERT INTO Sykkel(MNr, KopiNr, Ramme, Farge) VALUES
(1, 1, 25, 'grå'),
(1, 2, 28, 'rød'),
(2, 1, 25, 'blå'),
(2, 2, 27, 'svart'),
(2, 3, 27, 'grå'),
(3, 1, 28, 'rød'),
(3, 2, 28, 'blå'),
(4, 1, 25, 'svart'),
(5, 1, 25, 'blå'),
(5, 2, 28, 'rød'),
(5, 3, 28, 'grå'),
(5, 4, 28, 'svart'),
(6, 1, 28, 'blå'),
(7, 1, 25, 'rød'),
(7, 2, 25, 'grå'),
(7, 3, 27, 'blå'),
(8, 1, 27, 'rød'),
(8, 2, 27, 'blå'),
(8, 3, 27, 'grå'),
(9, 1, 28, 'rød'),
(9, 2, 28, 'rød'),
(10, 1, 28, 'rød'),
(10, 2, 25, 'svart'),
(10, 3, 25, 'blå'),
(11, 1, 27, 'grå'),
(12, 1, 27, 'rød'),
(12, 2, 25, 'svart'),
(12, 3, 28, 'blå');

INSERT INTO Utleie(KNr, MNr, KopiNr, TidUt, TidInn) VALUES
(1, 2,1,'2018-08-13 18:30','2018-08-14 16:05'),
(1, 1,1,'2018-08-13 18:52','2018-08-15 12:33'),
(2, 1,2,'2018-08-13 21:30','2018-08-14 19:55'),
(4, 3,1,'2018-08-13 22:13',NULL),
(4,12,1,'2018-08-14 11:07',NULL),
(5, 1,1,'2018-08-14 14:21',NULL),
(5,12,2,'2018-08-14 16:30','2018-08-14 23:02'),
(6, 8,1,'2018-08-14 20:46',NULL);
