/*
Utwórz projekt bazy danych dla firmy hostingowej. Firma ta zatrudniać będzie pracowników oraz będzie posiadać serwerownie w różnych miejscach w polsce.
Klienci firmy mogą posiadać wiele kont hostinowych. W bazie danych powinny znajdować się informacje o serwerach oraz ofercie kont hostinowych dla klientów.



*/


SET DATEFORMAT ymd;
GO

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE table_name='konta_hostingowe')
DROP TABLE konta_hostingowe;
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE table_name='pracownik')
DROP TABLE pracownik;
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE table_name='klient')
DROP TABLE klient;
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE table_name='pakiet_hostingowy')
DROP TABLE pakiet_hostingowy;
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE table_name='serwer')
DROP TABLE serwer;
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE table_name='miejsce')
DROP TABLE miejsce;
GO

-- Miejsca
CREATE TABLE miejsce (
  id_miejsce INT PRIMARY KEY IDENTITY (1, 1),
  ulica      VARCHAR(24) NOT NULL,
  numer      VARCHAR(8)  NOT NULL,
  miasto     VARCHAR(24) NOT NULL,
  kod        CHAR(6)     NOT NULL,
  telefon    VARCHAR(16),
  uwagi      VARCHAR(40)
);
GO
SET IDENTITY_INSERT miejsce ON
GO
INSERT INTO miejsce (id_miejsce, ulica, numer, miasto, kod, telefon, uwagi)
VALUES
(1, 'Dyrekcyjna', '6', 'Gdańsk', '82-400', '54 444 44', 'Serwerownia 1'),
(2, 'Poznańska', '1', 'Gdynia', '74-222', '55555532', 'Serwerownia 2'),

(3, 'Zielona', '1', 'Gdynia', '81-001', '500500500', 'Pracownicy'),

(4, 'Warszawska', '32', 'Gdynia', '81-002', '600600600', NULL),
(5, 'Poznańska', '1B/30', 'Gdynia', '81-003', '600500400', NULL),
(6, 'Zielona', '7', 'Gdynia', '81-001', '500111000', NULL),
(7, 'Bordowa', '40', 'Gdynia', '81-004', NULL, NULL),
(8, 'Aleja Zwycięstwa', '40/3', 'Gdynia', '81-560', NULL, NULL)
GO
SET IDENTITY_INSERT miejsce OFF
GO

-- Pracownicy
CREATE TABLE pracownik
(
    id_pracownik    int NOT NULL PRIMARY KEY IDENTITY (1, 1),
    imie            VARCHAR(20) NOT NULL,
    nazwisko        VARCHAR(20) NOT NULL,
    login           VARCHAR(50) UNIQUE NOT NULL,
    passwd          VARCHAR(64) NOT NULL,
    email           VARCHAR(50) UNIQUE NOT NULL,
    data_zatr       DATETIME NOT NULL,
    id_miejsce      INT NOT NULL,
)
GO
INSERT INTO pracownik (imie, nazwisko, login, email, passwd, data_zatr, id_miejsce)
VALUES
('Patryk', 'Chęciński', 'pchec', 'patryk.checinski@firma.pl', CONVERT(VARCHAR(65), HashBytes('SHA1', 'superhaslo1'), 2), '2015-01-01', 3),
('Paulina', 'Niewiarowska', 'pniew', 'paulina.niewiarowska@firma.pl', CONVERT(VARCHAR(65), HashBytes('SHA1', 'ciastko123'), 2), '2017-01-01', 3),
('Jan', 'Nowak', 'jnowa', 'jak.nowak@firma.pl', CONVERT(VARCHAR(65), HashBytes('SHA1', 'qwerty'), 2), '2014-12-03', 3),
('Juliam', 'Tuwim', 'jtuwi', 'juliam.tuwim@firma.pl', CONVERT(VARCHAR(65), HashBytes('SHA1', 'lokomotywa'), 2), '2014-03-30', 3),
('Stanisław', 'Trąbka', 'strab', 'stanislaw.trabka@firma.pl',  CONVERT(VARCHAR(65), HashBytes('SHA1', 'trabka'), 2), '2017-05-10', 3)
GO

-- Klienci
CREATE TABLE klient
(
    id_klient   INT NOT NULL PRIMARY KEY IDENTITY (1, 1),
    imie        VARCHAR(20) NOT NULL,
    nazwisko    VARCHAR(20) NOT NULL,
    email       VARCHAR(50) UNIQUE NOT NULL,
    email2      VARCHAR(50) NULL,
    passwd      VARCHAR(64) NOT NULL,
    NIP         char(10) NULL CHECK (ISNUMERIC(NIP) = 1 AND LEN(NIP) = 10),
    pesel       char(11) NULL CHECK (ISNUMERIC(pesel) = 1 AND LEN(PESEL) = 11),
    id_miejsce  INT NOT NULL,
)
GO
INSERT INTO klient (imie, nazwisko, email, email2, passwd, NIP, pesel, id_miejsce)
VALUES
('Jan', 'Nowak', 'nowaczek@wp.pl', NULL, CONVERT(VARCHAR(65), HashBytes('SHA1', 'haslo'), 2), '1234567890', '12345678901', 4),
('Tomasz', 'Adamczak', 'dominator323@gmail.com', 'admin@adamczyk.pl', CONVERT(VARCHAR(65), HashBytes('SHA1', 'haslo'), 2), '1234567890', '12345678901', 5),
('Piotr', 'Malczyk', 'piotrek.122@wp.pl', 'bok@superstrona.com', CONVERT(VARCHAR(65), HashBytes('SHA1', 'haslo'), 2), '1234567890', '12345678901', 6),
('Paweł', 'Fiodorowicz', 'pfiodrowicz@gmail.com', NULL,  CONVERT(VARCHAR(65), HashBytes('SHA1', 'haslo'), 2), '1234567890', '12345678901', 7),
('Aniela', 'Dalgiewicz', 'anielka12@o2.pl', NULL, CONVERT(VARCHAR(65), HashBytes('SHA1', 'haslo'), 2), '1234567890', '12345678901', 8)
GO

-- Pakiety
CREATE TABLE pakiet_hostingowy
(
    id_pakiet       INT PRIMARY KEY IDENTITY (1, 1),
    nazwa           VARCHAR(20) UNIQUE NOT NULL,
    opis            TEXT NOT NULL,
    limit_ftp       INT NOT NULL,
    limit_mail      INT NOT NULL,
    transfer_msc    INT NOT NULL
)
GO
INSERT INTO pakiet_hostingowy (nazwa, opis, limit_ftp, limit_mail, transfer_msc)
VALUES
('start2015', 'Pakiet na start', 3, 10, 300),
('biznes2015', 'Pakiet dla klientów biznesowych', 10, 30, 900),
('pro2015', 'Pakiet dla wymagających klientów', 30, 100, 1000),
('start2.0', 'Nowa oferta dla zwykłych ludzi!', 5, 15, 450),
('pro 2.0', 'Najlepsza oferta', 50, 200, 2000)
GO

-- Serwery
CREATE TABLE serwer
(
    id_serwer int NOT NULL PRIMARY KEY IDENTITY (1, 1),
    id_miejsce INT NOT NULL,
    nazwa VARCHAR(50) NOT NULL,
    adres_ip VARCHAR(20) NOT NULL
)
GO
INSERT INTO serwer (id_miejsce, nazwa, adres_ip)
VALUES
(1, 's100', '195.168.11.101'),
(1, 's101', '195.168.11.103'),
(2, 'sd2', '196.77.45.102'),
(2, 'sd3', '196.77.45.103'),
(2, 'sd4', '196.77.45.104')


CREATE TABLE konta_hostingowe
(
    login           VARCHAR(20) PRIMARY KEY NOT NULL,
    id_klient       INT NOT NULL,
    id_pracownik    INT,
    id_pakiet       INT NOT NULL,
    id_serwer       INT NOT NULL,
    passwd          VARCHAR(65) NOT NULL,

)
GO

INSERT INTO konta_hostingowe
VALUES
('sh200000', 1, NULL, 1, 1, CONVERT(VARCHAR(65), HashBytes('SHA1', 'test1'), 2)),
('sh200001', 1, 1, 2, 1, CONVERT(VARCHAR(65), HashBytes('SHA1', 'test2'), 2)),
('sh200002', 2, 1, 2, 2, CONVERT(VARCHAR(65), HashBytes('SHA1', 'test3'), 2)),
('sh200003', 3, 2, 3, 4, CONVERT(VARCHAR(65), HashBytes('SHA1', 'test4'), 2)),
('sh200004', 4, 2, 3, 5, CONVERT(VARCHAR(65), HashBytes('SHA1', 'test5'), 2)),
('sh200005', 4, 3, 4, 5, CONVERT(VARCHAR(65), HashBytes('SHA1', 'test6'), 2))

SELECT * FROM klient;
SELECT * FROM pracownik;
SELECT * FROM pakiet_hostingowy;
SELECT * FROM serwer;
SELECT * FROM konta_hostingowe;


ALTER TABLE konta_hostingowe ADD CONSTRAINT fk_konta_hostingowe_klient FOREIGN KEY(id_klient) REFERENCES klient(id_klient);
ALTER TABLE konta_hostingowe ADD CONSTRAINT fk_konta_hostingowe_pracownik FOREIGN KEY(id_pracownik) REFERENCES pracownik(id_pracownik);
ALTER TABLE konta_hostingowe ADD CONSTRAINT fk_konta_hostingowe_pakiet_hostingowy FOREIGN KEY(id_pakiet) REFERENCES pakiet_hostingowy(id_pakiet);
ALTER TABLE konta_hostingowe ADD CONSTRAINT fk_konta_hostingowe_serwer FOREIGN KEY(id_serwer) REFERENCES serwer(id_serwer);

ALTER TABLE pracownik ADD CONSTRAINT fk_pracownik_miejsce FOREIGN KEY(id_miejsce) REFERENCES miejsce(id_miejsce);
ALTER TABLE serwer ADD CONSTRAINT fk_serwer_miejsce FOREIGN KEY(id_miejsce) REFERENCES miejsce(id_miejsce);
ALTER TABLE klient ADD CONSTRAINT fk_klient_miejsce FOREIGN KEY(id_miejsce) REFERENCES miejsce(id_miejsce);