-- -------------------------------------------------------------------------------------------------------------------
-- -------------------------------------------------------------------------------------------------------------------
-- 1. TWORZENIE STRUKTURY BAZY DANYCH ORAZ USTAWIENIA KODOWANIA ZNAKÓW
-- -------------------------------------------------------------------------------------------------------------------
-- -------------------------------------------------------------------------------------------------------------------
-- -------------------------------------------------------------------------------------------------------------------

CREATE DATABASE IF NOT EXISTS Sklep DEFAULT CHARACTER SET utf8 COLLATE utf8_polish_ci;

USE Sklep;

-- -------------------------------------------------------------------------------------------------------------------
-- Tworzenie tabeli KategorieProduktow
-- -------------------------------------------------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS KategorieProduktow (
  idKategorieProduktow INT NOT NULL PRIMARY KEY UNIQUE AUTO_INCREMENT,
  nazwaKategorii VARCHAR(20) NOT NULL
);

-- -------------------------------------------------------------------------------------------------------------------
-- Tworzenie tabeli Produkty
-- -------------------------------------------------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS Produkty (
  idProduktu INT NOT NULL PRIMARY KEY UNIQUE AUTO_INCREMENT,
  idKategorii INT NOT NULL,
  cena_jednostkowa FLOAT NOT NULL,
  ilosc_dostepnych INT NULL DEFAULT 0,
  INDEX idKategorii_idx (idKategorii ASC),
  CONSTRAINT idKategorii
    FOREIGN KEY (idKategorii)
    REFERENCES Sklep.KategorieProduktow (idKategorieProduktow)
    ON DELETE CASCADE
    ON UPDATE RESTRICT);

-- -------------------------------------------------------------------------------------------------------------------
-- Tworzenie tabeli Klienci
-- -------------------------------------------------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS Klienci (
  idKlienta INT NOT NULL PRIMARY KEY UNIQUE AUTO_INCREMENT,
  imieKlienta VARCHAR(18) NOT NULL,
  nazwiskoKlienta VARCHAR(30) NOT NULL,
  peselKlienta VARCHAR(11) NOT NULL,
  eMail VARCHAR(40) NOT NULL
);

-- -------------------------------------------------------------------------------------------------------------------
-- Tworzenie tabeli Sprzedaz
-- -------------------------------------------------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS Sprzedaz (
  idSprzedazy INT NOT NULL PRIMARY KEY UNIQUE AUTO_INCREMENT,
  idProduktu INT NOT NULL,
  idKlienta INT NOT NULL,
  iloscSztuk INT NULL DEFAULT 1,
  dataSprzedazy DATE NOT NULL,
  INDEX idProduktu_idx (idProduktu ASC),
  INDEX idKlienta_idx (idKlienta ASC),
  CONSTRAINT idProduktu
    FOREIGN KEY (idProduktu)
    REFERENCES Sklep.Produkty (idProduktu)
    ON DELETE CASCADE
    ON UPDATE RESTRICT,
  CONSTRAINT idKlienta
	FOREIGN KEY (idKlienta)
	REFERENCES Sklep.Klienci (idKlienta)
	ON DELETE CASCADE
	ON UPDATE RESTRICT);

-- -------------------------------------------------------------------------------------------------------------------
-- Tworzenie tabeli FirmyKurierskie
-- -------------------------------------------------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS FirmyKurierskie (
  idFirmy INT NOT NULL PRIMARY KEY UNIQUE AUTO_INCREMENT,
  nazwaFirmy VARCHAR(50) NOT NULL,
  kontaktBiuro VARCHAR(14) NOT NULL
  );

-- -------------------------------------------------------------------------------------------------------------------
-- Tworzenie tabeli Kurierzy
-- -------------------------------------------------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS Kurierzy (
  idKuriera INT NOT NULL PRIMARY KEY UNIQUE AUTO_INCREMENT,
  idFirmy INT NOT NULL,
  imieKuriera VARCHAR(18) NOT NULL,
  nazwiskoKuriera VARCHAR(30) NOT NULL,
  peselKuriera VARCHAR(11) NOT NULL,
  nrTelefonu VARCHAR(14) NOT NULL,
  INDEX idFirmy_idx (idFirmy ASC),
  CONSTRAINT idFirmy
    FOREIGN KEY (idFirmy)
    REFERENCES Sklep.FirmyKurierskie (idFirmy)
    ON DELETE CASCADE
    ON UPDATE RESTRICT);

-- -------------------------------------------------------------------------------------------------------------------
-- Tworzenie tabeli Dostawy idK = idKlienta idP = idProduktu
-- -------------------------------------------------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS Dostawy (
  idDostawy INT NOT NULL PRIMARY KEY UNIQUE AUTO_INCREMENT,
  idProduktu INT NOT NULL,
  idKuriera INT NOT NULL,
  dataDostawy DATE NOT NULL,
  iloscDostawy INT NOT NULL,
  INDEX idProduktu_idx (idProduktu ASC),
  INDEX idKuriera_idx (idKuriera ASC),
  CONSTRAINT idP
    FOREIGN KEY (idProduktu)
    REFERENCES Sklep.Produkty (idProduktu)
    ON DELETE CASCADE
    ON UPDATE RESTRICT,
  CONSTRAINT idK
    FOREIGN KEY (idKuriera)
    REFERENCES Sklep.Kurierzy (idKuriera)
    ON DELETE CASCADE
    ON UPDATE RESTRICT);
	
CREATE TABLE IF NOT EXISTS Zamowienia (
  idDostawy INT NOT NULL PRIMARY KEY UNIQUE AUTO_INCREMENT,
  idProduktu INT NOT NULL,
  idKuriera INT NOT NULL,
  iloscZamawiana INT NOT NULL);

-- -------------------------------------------------------------------------------------------------------------------
-- -------------------------------------------------------------------------------------------------------------------
-- -------------------------------------------------------------------------------------------------------------------
-- 2. WSTAWIANIE DANYCH DO ZAMODELOWANEJ BAZY 'SKLEP'
-- -------------------------------------------------------------------------------------------------------------------
-- -------------------------------------------------------------------------------------------------------------------
-- -------------------------------------------------------------------------------------------------------------------
	
	
	
-- -------------------------------------------------------------------------------------------------------------------
-- Wstawianie danych do tabeli FirmyKurierskie
-- -------------------------------------------------------------------------------------------------------------------
INSERT INTO FirmyKurierskie (idFirmy, nazwaFirmy, kontaktBiuro) VALUES
(DEFAULT, "DHL", "+48 801345345"), (DEFAULT, "DPD Polska", "022 5775500"), (DEFAULT, "FedEx", "0 800433339"), (DEFAULT, "GLS Poland", "061 8972900"), (DEFAULT, "InPost", "+48 722444000"), (DEFAULT, "Opek", "022 3187910"), (DEFAULT, "UPS", "022 5340000");

-- -------------------------------------------------------------------------------------------------------------------
-- Wstawianie danych do tabeli KategorieProduktow
-- -------------------------------------------------------------------------------------------------------------------
INSERT INTO KategorieProduktow (idKategorieProduktow, nazwaKategorii) VALUES
(DEFAULT, "Dyski twarde"), (DEFAULT, "Plyty główne"), (DEFAULT, "Pamięci RAM"), (DEFAULT, "Pamięci Flash"), (DEFAULT, "Karty graficzne"), (DEFAULT, "Procesory"), (DEFAULT, "Karty dźwiękowe"), (DEFAULT, "Napędy optyczne"), (DEFAULT, "Listwy zasilające"), (DEFAULT, "Przewody");

-- -------------------------------------------------------------------------------------------------------------------
-- Wstawianie danych do tabeli Klienci
-- -------------------------------------------------------------------------------------------------------------------
INSERT INTO Klienci (idKlienta, imieKlienta, nazwiskoKlienta, peselKlienta, eMail) VALUES
(DEFAULT, "Aleksander", "Bąk", "25052916596", "aleksander.bak@gmail.com"),(DEFAULT, "Jan", "Nowakowski", "25052916596", "jan.nowak@gmail.com"),(DEFAULT, "Janusz", "Nowak", "25052916596", "jan.nowak@gmail.com"),(DEFAULT, "Jan", "Nowak", "25052916596", "jan.nowak@gmail.com"),(DEFAULT, "Jan", "Nowak", "25052916596", "jan.nowak@gmail.com"), (DEFAULT, "Mariusz", "Blank", "64101808275", "mblank@hotmail.com"), (DEFAULT, "Jerzy", "Stefaniuk", "46052507072", "jerzy@stefaniuk-corp.com"), (DEFAULT, "Anna", "Nowak", "81031300484", "ania.nowak@gmail.com"), (DEFAULT, "Jarosław", "Szultz", "29030116475", "jarszultz@yahoo.com"), (DEFAULT, "Max", "Cieślar", "42010214514", "admin@pajacyk.pl"), (DEFAULT, "Maria", "Legierska", "86071009566", "magda_legierska@hotmail.com"), (DEFAULT, "Ian", "Smith", "39071500421", "i.smith@o2.com"), (DEFAULT, "Lara", "Kovalsky", "74032817084", "larakovalsky@gmail.com"), (DEFAULT, "Barbara", "Pilch", "25111405665", "br.pilch@gmail.com"), (DEFAULT, "Justyna", "Małysz", "18080716087", "j.malysz@hotmail.com"), (DEFAULT, "Janusz", "Schwarz", "79123107712", "janusz_schwarz@wp.pl");

-- -------------------------------------------------------------------------------------------------------------------
-- Wstawianie danych do tabeli Kurierzy
-- -------------------------------------------------------------------------------------------------------------------
INSERT INTO Kurierzy (idKuriera, idFirmy, imieKuriera, nazwiskoKuriera, peselKuriera, nrTelefonu) VALUES
(DEFAULT, 3, "Józef", "Buzek", "93072201279", "+48 508729555"), (DEFAULT, 7, "Aaron", "Will", "26071015394", "+48 789229575"), (DEFAULT, 5, "Antoni", "Gradoń", "92111904654", "+48 502741522"), (DEFAULT, 2, "Bartosz", "Kowalski", "82061615452", "+48 694729555"), (DEFAULT, 3, "Jolanta", "Nowak", "12345678910", "+48 998729555"), (DEFAULT, 4, "Witold", "Legierski", "52732201279", "+48 709286732"), (DEFAULT, 1, "Elżbieta", "Weiss", "33055201272", "+48 607873022"), (DEFAULT, 7, "Sam", "Earl", "68031612214", "+202 703729555"), (DEFAULT, 6, "Jakub", "Woźniak", "10262603196", "+48 777729555"), (DEFAULT, 2, "Krystian", "Hasse", "61031504415", "+48 610009555"), (DEFAULT, 1, "Piotr", "Nowakowski", "66041102954", "+48 620813108");

-- -------------------------------------------------------------------------------------------------------------------
-- Wstawianie danych do tabeli Produkty
-- -------------------------------------------------------------------------------------------------------------------
INSERT INTO Produkty (idProduktu, idKategorii, cena_jednostkowa, ilosc_dostepnych) VALUES
(DEFAULT, 1, 877.99, NULL),(DEFAULT, 1, 679.99, NULL),(DEFAULT, 1, 679.99, 27), (DEFAULT, 5, 2399.99, 8), (DEFAULT, 2, 1099.99, 81), (DEFAULT, 1, 969.99, 10), (DEFAULT, 2, 3679.99, DEFAULT), (DEFAULT, 9, 99.99, 72), (DEFAULT, 10, 9.99, 122), (DEFAULT, 4, 159.99, 6), (DEFAULT, 6, 1279.99, 9), (DEFAULT, 8, 299.99, 11), (DEFAULT, 6, 1979.99, 3), (DEFAULT, 1, 1699.99, DEFAULT), (DEFAULT, 5, 3079.99, 2), (DEFAULT, 7, 279.99, 6), (DEFAULT, 10, 2.99, 423), (DEFAULT, 2, 3629.99, 1), (DEFAULT, 5, 4399.99, 1), (DEFAULT, 6, 2109.99, DEFAULT), (DEFAULT, 10, 49.99, 103), (DEFAULT, 3, 999.99, 8);

-- -------------------------------------------------------------------------------------------------------------------
-- Wstawianie danych do tabeli Dostawy
-- -------------------------------------------------------------------------------------------------------------------
INSERT INTO Dostawy (idDostawy, idProduktu, idKuriera, dataDostawy, iloscDostawy) VALUES
(DEFAULT, 6, 10, '2016-12-03', 7), (DEFAULT, 2, 1, '2016-12-08', 3), (DEFAULT, 1, 4, '2016-12-12', 34), (DEFAULT, 17, 11, '2016-12-14', 9), (DEFAULT, 8, 3, '2016-12-17', 11), (DEFAULT, 12, 5, '2016-12-28', 35), (DEFAULT, 18, 7, '2017-01-02', 37), (DEFAULT, 1, 6, '2017-01-06', 12),(DEFAULT, 1, 5, '2018-01-06', 100);

-- -------------------------------------------------------------------------------------------------------------------
-- Wstawianie danych do tabeli Sprzedaz
-- -------------------------------------------------------------------------------------------------------------------
INSERT INTO Sprzedaz (idSprzedazy, idProduktu, idKlienta, iloscSztuk, dataSprzedazy) VALUES
(DEFAULT, 2, 11, 1, '2017-03-02'), (DEFAULT, 6, 2, 1, '2017-03-02'), (DEFAULT, 17, 9, 1, '2017-03-02'),(DEFAULT, 2, 11, 1, '2017-02-02'), (DEFAULT, 6, 2, 1, '2017-02-08'), (DEFAULT, 17, 9, 1, '2017-02-09'),(DEFAULT, 2, 11, 1, '2017-01-02'), (DEFAULT, 6, 2, 1, '2017-01-02'), (DEFAULT, 17, 9, 1, '2017-01-02'), (DEFAULT, 1, 1, 1, '2017-01-03'), (DEFAULT, 8, 7, 2, '2017-01-04'), (DEFAULT, 9, 4, 1, '2017-01-04'), (DEFAULT, 13, 6, 3, '2017-01-05'), (DEFAULT, 19, 3, 1, '2017-01-06'), (DEFAULT, 6, 10, 1, '2017-01-07'), (DEFAULT, 7, 10, 1, '2017-01-07'), (DEFAULT, 16, 5, 2, '2017-01-07'), (DEFAULT, 20, 8, 1, '2017-01-08');
-- -------------------------------------------------------------------------------------------------------------------
-- Wstawianie danych do tabeli Zamowienia
-- -------------------------------------------------------------------------------------------------------------------

INSERT INTO Zamowienia (idDostawy, idProduktu, idKuriera, iloscZamawiana) VALUES
(DEFAULT, 1, 5, 110);