CREATE TABLE [dbo].[Czescis] (
    [ID_czesci]        INT            IDENTITY (1, 1) NOT NULL,
    [nazwa]            NVARCHAR (MAX) NOT NULL,
    [Centa_w_hurtowni] INT            NOT NULL,
    CONSTRAINT [PK_dbo.Czescis] PRIMARY KEY CLUSTERED ([ID_czesci] ASC)
);

CREATE TABLE [dbo].[Klients] (
    [ID_klienta]         INT            IDENTITY (1, 1) NOT NULL,
    [Imie]               NVARCHAR (MAX) NOT NULL,
    [Nazwisko]           NVARCHAR (MAX) NOT NULL,
    [Login]              NVARCHAR (MAX) NOT NULL,
    [Haslo]              NVARCHAR (MAX) NOT NULL,
    [Numer_telefonu]     INT            NOT NULL,
    [Ostatnie_logowanie] DATETIME       NOT NULL,
    [Teraz_aktywny]      BIT            NOT NULL,
    CONSTRAINT [PK_dbo.Klients] PRIMARY KEY CLUSTERED ([ID_klienta] ASC)
);

CREATE TABLE [dbo].[Komputers] (
    [ID_komputera]          INT            IDENTITY (1, 1) NOT NULL,
    [Producent]             NVARCHAR (MAX) NOT NULL,
    [Model]                 NVARCHAR (MAX) NOT NULL,
    [Cena]                  INT            NOT NULL,
    [Data_zakupu]           DATETIME       NOT NULL,
    [ID_klienta_ID_klienta] INT            NULL,
    CONSTRAINT [PK_dbo.Komputers] PRIMARY KEY CLUSTERED ([ID_komputera] ASC),
    CONSTRAINT [FK_dbo.Komputers_dbo.Klients_ID_klienta_ID_klienta] FOREIGN KEY ([ID_klienta_ID_klienta]) REFERENCES [dbo].[Klients] ([ID_klienta])
);


GO
CREATE NONCLUSTERED INDEX [IX_ID_klienta_ID_klienta]
    ON [dbo].[Komputers]([ID_klienta_ID_klienta] ASC);

CREATE TABLE [dbo].[Pracowniks] (
    [ID_pracownika]            INT            NOT NULL,
    [Imie]                     NVARCHAR (MAX) NOT NULL,
    [Nazwisko]                 NVARCHAR (MAX) NOT NULL,
    [PESEL]                    INT            NOT NULL,
    [NIP]                      INT            NOT NULL,
    [Login]                    NVARCHAR (MAX) NOT NULL,
    [Haslo]                    NVARCHAR (MAX) NOT NULL,
    [Data_zatrudnienia]        DATETIME       NOT NULL,
    [Wynagrodzenie]            INT            NOT NULL,
    [Data_urodzenia]           DATETIME       NOT NULL,
    [Nr_telefonu]              INT            NOT NULL,
    [Dzial]                    NVARCHAR (MAX) NULL,
    [Superuser]                BIT            NOT NULL,
    [Ostatnie_Logowanie]       DATETIME       NOT NULL,
    [Teraz_aktywny]            BIT            NOT NULL,
    [Stanowisko_ID_stanowiska] INT            NULL,
    CONSTRAINT [PK_dbo.Pracowniks] PRIMARY KEY CLUSTERED ([ID_pracownika] ASC),
    CONSTRAINT [FK_dbo.Pracowniks_dbo.Stanowiskoes_Stanowisko_ID_stanowiska] FOREIGN KEY ([Stanowisko_ID_stanowiska]) REFERENCES [dbo].[Stanowiskoes] ([ID_stanowiska]),
    CONSTRAINT [FK_dbo.Pracowniks_dbo.Uprawnienias_ID_pracownika] FOREIGN KEY ([ID_pracownika]) REFERENCES [dbo].[Uprawnienias] ([Id_pracownika])
);


GO
CREATE NONCLUSTERED INDEX [IX_ID_pracownika]
    ON [dbo].[Pracowniks]([ID_pracownika] ASC);


GO
CREATE NONCLUSTERED INDEX [IX_Stanowisko_ID_stanowiska]
    ON [dbo].[Pracowniks]([Stanowisko_ID_stanowiska] ASC);



CREATE TABLE [dbo].[Stanowiskoes] (
    [ID_stanowiska]            INT            IDENTITY (1, 1) NOT NULL,
    [Status]                   BIT            NOT NULL,
    [Opis]                     NVARCHAR (MAX) NULL,
    [Zamowienie_ID_zamowienia] INT            NULL,
    CONSTRAINT [PK_dbo.Stanowiskoes] PRIMARY KEY CLUSTERED ([ID_stanowiska] ASC),
    CONSTRAINT [FK_dbo.Stanowiskoes_dbo.Zamowienies_Zamowienie_ID_zamowienia] FOREIGN KEY ([Zamowienie_ID_zamowienia]) REFERENCES [dbo].[Zamowienies] ([ID_zamowienia])
);


GO
CREATE NONCLUSTERED INDEX [IX_Zamowienie_ID_zamowienia]
    ON [dbo].[Stanowiskoes]([Zamowienie_ID_zamowienia] ASC);



CREATE TABLE [dbo].[Uprawnienias] (
    [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_dbo.Uprawnienias] PRIMARY KEY CLUSTERED ([Id_pracownika] ASC)
);

CREATE TABLE [dbo].[Zamowienies] (
    [ID_zamowienia]               INT            IDENTITY (1, 1) NOT NULL,
    [Data]                        DATETIME       NOT NULL,
    [Etap]                        NVARCHAR (MAX) NOT NULL,
    [Cena]                        INT            NOT NULL,
    [Opis]                        NVARCHAR (MAX) 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_dbo.Zamowienies] PRIMARY KEY CLUSTERED ([ID_zamowienia] ASC),
    CONSTRAINT [FK_dbo.Zamowienies_dbo.Czescis_ID_czesci_ID_czesci] FOREIGN KEY ([ID_czesci_ID_czesci]) REFERENCES [dbo].[Czescis] ([ID_czesci]) ON DELETE CASCADE,
    CONSTRAINT [FK_dbo.Zamowienies_dbo.Klients_ID_klienta_ID_klienta] FOREIGN KEY ([ID_klienta_ID_klienta]) REFERENCES [dbo].[Klients] ([ID_klienta]) ON DELETE CASCADE,
    CONSTRAINT [FK_dbo.Zamowienies_dbo.Komputers_ID_komputera_ID_komputera] FOREIGN KEY ([ID_komputera_ID_komputera]) REFERENCES [dbo].[Komputers] ([ID_komputera]) ON DELETE CASCADE,
    CONSTRAINT [FK_dbo.Zamowienies_dbo.Pracowniks_ID_pracownika_ID_pracownika] FOREIGN KEY ([ID_pracownika_ID_pracownika]) REFERENCES [dbo].[Pracowniks] ([ID_pracownika]) ON DELETE CASCADE
);


GO
CREATE NONCLUSTERED INDEX [IX_ID_czesci_ID_czesci]
    ON [dbo].[Zamowienies]([ID_czesci_ID_czesci] ASC);


GO
CREATE NONCLUSTERED INDEX [IX_ID_klienta_ID_klienta]
    ON [dbo].[Zamowienies]([ID_klienta_ID_klienta] ASC);


GO
CREATE NONCLUSTERED INDEX [IX_ID_komputera_ID_komputera]
    ON [dbo].[Zamowienies]([ID_komputera_ID_komputera] ASC);


GO
CREATE NONCLUSTERED INDEX [IX_ID_pracownika_ID_pracownika]
    ON [dbo].[Zamowienies]([ID_pracownika_ID_pracownika] ASC);