CREATE TABLE [Czesci] (
    [ID_czesci]       INT            IDENTITY (1, 1) NOT NULL,
    [Nazwa]           NVARCHAR (40) NOT NULL,
    [Cena_w_hurtowni] INT            NOT NULL,
    CONSTRAINT [PK_Czesci] PRIMARY KEY CLUSTERED ([ID_czesci] ASC)
);

CREATE TABLE [Klient] (
    [ID_klienta]         INT            IDENTITY (1, 1) NOT NULL,
    [Imie]               NVARCHAR (20) NOT NULL,
    [Nazwisko]           NVARCHAR (20) NOT NULL,
    [Login]              NVARCHAR (20) NOT NULL,
    [Haslo]              NVARCHAR (32) NOT NULL,
    [Numer_telefonu]     INT            NOT NULL,
    [Ostatnie_logowanie] DATETIME       NOT NULL,
    [Teraz_aktywny]      BIT            NOT NULL,
    CONSTRAINT [PK_Klient] PRIMARY KEY CLUSTERED ([ID_klienta] ASC)
);

CREATE TABLE [Komputer] (
    [ID_komputera] INT            IDENTITY (1, 1) NOT NULL,
    [Producent]    NVARCHAR (30) NOT NULL,
    [Model]        NVARCHAR (30) NOT NULL,
    [Cena]         INT            NOT NULL,
    [Data_zakupu]  DATE       NOT NULL,
    [ID_klienta]   INT            NOT NULL,
    CONSTRAINT [PK_Komputer] PRIMARY KEY CLUSTERED ([ID_komputera] ASC),
    CONSTRAINT [FK_Komputer_Klient_ID_klienta] FOREIGN KEY ([ID_klienta]) REFERENCES [Klient] ([ID_klienta]) ON DELETE CASCADE
);


GO
CREATE NONCLUSTERED INDEX [IX_ID_klienta]
    ON [Komputer]([ID_klienta] ASC);

CREATE TABLE [Pracownik] (
    [ID_pracownika]      INT            IDENTITY (1, 1) NOT NULL,
    [Imie]               NVARCHAR (20) NOT NULL,
    [Nazwisko]           NVARCHAR (20) NOT NULL,
    [PESEL]              INT            NOT NULL,
    [NIP]                INT            NOT NULL,
    [Login]              NVARCHAR (20) NOT NULL,
    [Haslo]              NVARCHAR (32) NOT NULL,
    [Data_zatrudnienia]  DATE       NOT NULL,
    [Wynagrodzenie]      INT            NOT NULL,
    [Data_urodzenia]     DATE       NOT NULL,
    [Nr_telefonu]        INT            NOT NULL,
    [ID_stanowiska]      INT            NOT NULL,
    [Dzial]              NVARCHAR (20) NULL,
    [Superuser]          BIT            NOT NULL,
    [Ostatnie_Logowanie] DATETIME       NOT NULL,
    [Teraz_aktywny]      BIT            NOT NULL,
    CONSTRAINT [PK_Pracownik] PRIMARY KEY CLUSTERED ([ID_pracownika] ASC)
);

CREATE TABLE [Uprawnienia] (
    [ID_uprawnienia] INT IDENTITY (1, 1) NOT NULL,
    [Uprawnienie1]   BIT NOT NULL,
    [Uprawnienie2]   BIT NOT NULL,
    [Uprawnienie3]   BIT NOT NULL,
    [Uprawnienie4]   BIT NOT NULL,
    [Uprawnienie5]   BIT NOT NULL,
    [Uprawnienie6]   BIT NOT NULL,
    [Uprawnienie7]   BIT NOT NULL,
    [Uprawnienie8]   BIT NOT NULL,
    [Uprawnienie9]   BIT NOT NULL,
    [Uprawnienie10]  BIT NOT NULL,
    [Uprawnienie11]  BIT NOT NULL,
    [Uprawnienie12]  BIT NOT NULL,
    [Uprawnienie13]  BIT NOT NULL,
    [Uprawnienie14]  BIT NOT NULL,
    CONSTRAINT [PK_Uprawnienia] PRIMARY KEY CLUSTERED ([ID_uprawnienia] ASC)
);

CREATE TABLE [Zamowienie] (
    [ID_zamowienia]    INT            IDENTITY (1, 1) NOT NULL,
    [Data]             DATETIME       NOT NULL,
    [Etap]             NVARCHAR (20) NOT NULL,
    [ID_klienta]       INT            NOT NULL,
    [ID_komputera]     INT            NOT NULL,
    [Cena]             INT            NOT NULL,
    [Opis]             NVARCHAR (300) NOT NULL,
    [ID_czesci]        INT            NOT NULL,
    [Czas_pracownikow] DATETIME       NOT NULL,
    [ID_pracownika]    INT            NOT NULL,
    CONSTRAINT [PK_Zamowienie] PRIMARY KEY CLUSTERED ([ID_zamowienia] ASC),
    CONSTRAINT [FK_Zamowienie_Czesci_ID_czesci] FOREIGN KEY ([ID_czesci]) REFERENCES [Czesci] ([ID_czesci]) ON DELETE CASCADE,
    CONSTRAINT [FK_Zamowienie_Klient_ID_klienta] FOREIGN KEY ([ID_klienta]) REFERENCES [Klient] ([ID_klienta]) ON DELETE CASCADE,
    CONSTRAINT [FK_Zamowienie_Pracownik_ID_pracownika] FOREIGN KEY ([ID_pracownika]) REFERENCES [Pracownik] ([ID_pracownika]) ON DELETE CASCADE
);


GO
CREATE NONCLUSTERED INDEX [IX_ID_klienta]
    ON [Zamowienie]([ID_klienta] ASC);


GO
CREATE NONCLUSTERED INDEX [IX_ID_czesci]
    ON [Zamowienie]([ID_czesci] ASC);


GO
CREATE NONCLUSTERED INDEX [IX_ID_pracownika]
    ON [Zamowienie]([ID_pracownika] ASC);

CREATE TABLE [Stanowisko] (
    [ID_stanowiska] INT            IDENTITY (1, 1) NOT NULL,
    [Status]        BIT            NOT NULL,
    [Opis]          NVARCHAR (300) NULL,
    [ID_zamowienia] INT            NOT NULL,
    CONSTRAINT [PK_Stanowisko] PRIMARY KEY CLUSTERED ([ID_stanowiska] ASC),
    CONSTRAINT [FK_Stanowisko_Zamowienie_ID_zamowienia] FOREIGN KEY ([ID_zamowienia]) REFERENCES [Zamowienie] ([ID_zamowienia]) ON DELETE CASCADE
);


GO
CREATE NONCLUSTERED INDEX [IX_ID_zamowienia]
    ON [Stanowisko]([ID_zamowienia] ASC);

CREATE TABLE [PomocniczaUP] (
    [ID_pomocnicza]  INT IDENTITY (1, 1) NOT NULL,
    [ID_pracownika]  INT NOT NULL,
    [ID_uprawnienia] INT NOT NULL,
    CONSTRAINT [PK_PomocniczaUP] PRIMARY KEY CLUSTERED ([ID_pomocnicza] ASC),
    CONSTRAINT [FK_PomocniczaUP_Pracownik_ID_pracownika] FOREIGN KEY ([ID_pracownika]) REFERENCES [Pracownik] ([ID_pracownika]) ON DELETE CASCADE,
    CONSTRAINT [FK_PomocniczaUP_Uprawnienia_ID_uprawnienia] FOREIGN KEY ([ID_uprawnienia]) REFERENCES [Uprawnienia] ([ID_uprawnienia]) ON DELETE CASCADE
);


GO
CREATE NONCLUSTERED INDEX [IX_ID_pracownika]
    ON [PomocniczaUP]([ID_pracownika] ASC);


GO
CREATE NONCLUSTERED INDEX [IX_ID_uprawnienia]
    ON [PomocniczaUP]([ID_uprawnienia] ASC);