CREATE TABLE [Czesci] (
    [ID_czesci]        INT            IDENTITY (1, 1) NOT NULL,
    [nazwa]            NVARCHAR (40) NOT NULL,
    [Centa_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_ID_klienta] INT            NULL,
    CONSTRAINT [PK_Komputer] PRIMARY KEY CLUSTERED ([ID_komputera] ASC),
    CONSTRAINT [FK_Komputer_Klient_ID_klienta_ID_klienta] FOREIGN KEY ([ID_klienta_ID_klienta]) REFERENCES [Klient] ([ID_klienta])
);

 CREATE TABLE [Uprawnienia] (
    [Id_pracownika] 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_pracownika] ASC)
);

CREATE TABLE [Pracownik] (
    [ID_pracownika]            INT            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]        DATETIME       NOT NULL,
    [Wynagrodzenie]            INT            NOT NULL,
    [Data_urodzenia]           DATETIME       NOT NULL,
    [Nr_telefonu]              INT            NOT NULL,
    [Dzial]                    NVARCHAR (20) NULL,
    [Superuser]                BIT            NOT NULL,
    [Ostatnie_Logowanie]       DATETIME       NOT NULL,
    [Teraz_aktywny]            BIT            NOT NULL,
    [Stanowisko_ID_stanowiska] INT            NULL,
    CONSTRAINT [PK_Pracownik] PRIMARY KEY CLUSTERED ([ID_pracownika] ASC),
    CONSTRAINT [FK_Pracownik_Uprawnienia_ID_pracownika] FOREIGN KEY ([ID_pracownika]) REFERENCES [Uprawnienia] ([Id_pracownika])
);




CREATE TABLE [Zamowienie] (
    [ID_zamowienia]               INT            IDENTITY (1, 1) NOT NULL,
    [Data]                        DATETIME       NOT NULL,
    [Etap]                        NVARCHAR (20) NOT NULL,
    [Cena]                        INT            NOT NULL,
    [Opis]                        NVARCHAR (300) NOT NULL,
    [Czas_pracownikow]            DATETIME       NOT NULL,
    [ID_czesci_ID_czesci]         INT            NOT NULL,
    [ID_klienta_ID_klienta]       INT            NOT NULL,
    [ID_komputera_ID_komputera]   INT            NOT NULL,
    [ID_pracownika_ID_pracownika] INT            NOT NULL,
    CONSTRAINT [PK_Zamowienie] PRIMARY KEY CLUSTERED ([ID_zamowienia] ASC),
    CONSTRAINT [FK_Zamowienie_Czesci_ID_czesci_ID_czesci] FOREIGN KEY ([ID_czesci_ID_czesci]) REFERENCES [Czesci] ([ID_czesci]) ON DELETE CASCADE,
    CONSTRAINT [FK_Zamowienie_Klient_ID_klienta_ID_klienta] FOREIGN KEY ([ID_klienta_ID_klienta]) REFERENCES [Klient] ([ID_klienta]) ON DELETE CASCADE,
    CONSTRAINT [FK_Zamowienie_Komputer_ID_komputera_ID_komputera] FOREIGN KEY ([ID_komputera_ID_komputera]) REFERENCES [Komputer] ([ID_komputera]) ON DELETE CASCADE,
    CONSTRAINT [FK_Zamowienie_Pracownik_ID_pracownika_ID_pracownika] FOREIGN KEY ([ID_pracownika_ID_pracownika]) REFERENCES [Pracownik] ([ID_pracownika]) ON DELETE CASCADE
);



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

GO
CREATE NONCLUSTERED INDEX [IX_ID_klienta_ID_klienta]
    ON [Komputer]([ID_klienta_ID_klienta] ASC);


GO
CREATE NONCLUSTERED INDEX [IX_Zamowienie_ID_zamowienia]
    ON [Stanowisko]([Zamowienie_ID_zamowienia] ASC);



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


GO
CREATE NONCLUSTERED INDEX [IX_Stanowisko_ID_stanowiska]
    ON [Pracownik]([Stanowisko_ID_stanowiska] ASC);






GO
CREATE NONCLUSTERED INDEX [IX_ID_czesci_ID_czesci]
    ON [Zamowienie]([ID_czesci_ID_czesci] ASC);


GO
CREATE NONCLUSTERED INDEX [IX_ID_klienta_ID_klienta]
    ON [Zamowienie]([ID_klienta_ID_klienta] ASC);


GO
CREATE NONCLUSTERED INDEX [IX_ID_komputera_ID_komputera]
    ON [Zamowienie]([ID_komputera_ID_komputera] ASC);


GO
CREATE NONCLUSTERED INDEX [IX_ID_pracownika_ID_pracownika]
    ON [Zamowienie]([ID_pracownika_ID_pracownika] ASC);