---

USE user300_db;
GO

/* =========================================================
   1. УДАЛЕНИЕ ТАБЛИЦ
   БАЗА ДАННЫХ user300_db НЕ УДАЛЯЕТСЯ
   ========================================================= */

DROP TABLE IF EXISTS [dbo].[DocumentByService];
DROP TABLE IF EXISTS [dbo].[ProductSale];
DROP TABLE IF EXISTS [dbo].[ProductPhoto];
DROP TABLE IF EXISTS [dbo].[AttachedProduct];
DROP TABLE IF EXISTS [dbo].[ServicePhoto];
DROP TABLE IF EXISTS [dbo].[TagOfClient];
DROP TABLE IF EXISTS [dbo].[ClientService];
DROP TABLE IF EXISTS [dbo].[Product];
DROP TABLE IF EXISTS [dbo].[Client];
DROP TABLE IF EXISTS [dbo].[Service];
DROP TABLE IF EXISTS [dbo].[Manufacturer];
DROP TABLE IF EXISTS [dbo].[Gender];
DROP TABLE IF EXISTS [dbo].[Tag];
GO


/* =========================================================
   2. СОЗДАНИЕ ТАБЛИЦ ЗАНОВО
   ========================================================= */

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO


/* ---------- Gender ---------- */

CREATE TABLE [dbo].[Gender](
    [Code] [nchar](1) NOT NULL,
    [Name] [nvarchar](10) NULL,

    CONSTRAINT [PK_Gender] PRIMARY KEY CLUSTERED
    (
        [Code] ASC
    )
);
GO


/* ---------- Manufacturer ---------- */

CREATE TABLE [dbo].[Manufacturer](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](100) NOT NULL,
    [StartDate] [date] NULL,

    CONSTRAINT [PK_Manufacturer] PRIMARY KEY CLUSTERED
    (
        [ID] ASC
    )
);
GO


/* ---------- Service ---------- */

CREATE TABLE [dbo].[Service](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Title] [nvarchar](100) NOT NULL,
    [Cost] [money] NOT NULL,
    [DurationInSeconds] [int] NOT NULL,
    [Description] [nvarchar](max) NULL,
    [Discount] [float] NULL,
    [MainImagePath] [nvarchar](1000) NULL,

    CONSTRAINT [PK_Service] PRIMARY KEY CLUSTERED
    (
        [ID] ASC
    )
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];
GO


/* ---------- Tag ---------- */

CREATE TABLE [dbo].[Tag](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Title] [nvarchar](30) NOT NULL,
    [Color] [nchar](6) NOT NULL,

    CONSTRAINT [PK_Tag] PRIMARY KEY CLUSTERED
    (
        [ID] ASC
    )
);
GO


/* ---------- Client ---------- */

CREATE TABLE [dbo].[Client](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [FirstName] [nvarchar](50) NOT NULL,
    [LastName] [nvarchar](50) NOT NULL,
    [Patronymic] [nvarchar](50) NULL,
    [Birthday] [date] NULL,
    [RegistrationDate] [datetime] NOT NULL,
    [Email] [nvarchar](255) NULL,
    [Phone] [nvarchar](20) NOT NULL,
    [GenderCode] [nchar](1) NOT NULL,
    [PhotoPath] [nvarchar](1000) NULL,

    CONSTRAINT [PK_Client] PRIMARY KEY CLUSTERED
    (
        [ID] ASC
    )
);
GO


/* ---------- Product ---------- */

CREATE TABLE [dbo].[Product](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Title] [nvarchar](100) NOT NULL,
    [Cost] [money] NOT NULL,
    [Description] [nvarchar](max) NULL,
    [MainImagePath] [nvarchar](1000) NULL,
    [IsActive] [bit] NOT NULL,
    [ManufacturerID] [int] NULL,

    CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
    (
        [ID] ASC
    )
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];
GO


/* ---------- ClientService ---------- */

CREATE TABLE [dbo].[ClientService](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [ClientID] [int] NOT NULL,
    [ServiceID] [int] NOT NULL,
    [StartTime] [datetime] NOT NULL,
    [Comment] [nvarchar](max) NULL,

    CONSTRAINT [PK_ClientService] PRIMARY KEY CLUSTERED
    (
        [ID] ASC
    )
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];
GO


/* ---------- DocumentByService ---------- */

CREATE TABLE [dbo].[DocumentByService](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [ClientServiceID] [int] NOT NULL,
    [DocumentPath] [nvarchar](1000) NOT NULL,

    CONSTRAINT [PK_DocumentByService] PRIMARY KEY CLUSTERED
    (
        [ID] ASC
    )
);
GO


/* ---------- ProductPhoto ---------- */

CREATE TABLE [dbo].[ProductPhoto](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [ProductID] [int] NOT NULL,
    [PhotoPath] [nvarchar](1000) NOT NULL,

    CONSTRAINT [PK_ProductPhoto] PRIMARY KEY CLUSTERED
    (
        [ID] ASC
    )
);
GO


/* ---------- ProductSale ---------- */

CREATE TABLE [dbo].[ProductSale](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [SaleDate] [datetime] NOT NULL,
    [ProductID] [int] NOT NULL,
    [Quantity] [int] NOT NULL,
    [ClientServiceID] [int] NULL,

    CONSTRAINT [PK_ProductSale] PRIMARY KEY CLUSTERED
    (
        [ID] ASC
    )
);
GO


/* ---------- ServicePhoto ---------- */

CREATE TABLE [dbo].[ServicePhoto](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [ServiceID] [int] NOT NULL,
    [PhotoPath] [nvarchar](1000) NOT NULL,

    CONSTRAINT [PK_ServicePhoto] PRIMARY KEY CLUSTERED
    (
        [ID] ASC
    )
);
GO


/* ---------- AttachedProduct ---------- */

CREATE TABLE [dbo].[AttachedProduct](
    [MainProductID] [int] NOT NULL,
    [AttachedProductID] [int] NOT NULL,

    CONSTRAINT [PK_AttachedProduct] PRIMARY KEY CLUSTERED
    (
        [MainProductID] ASC,
        [AttachedProductID] ASC
    )
);
GO


/* ---------- TagOfClient ---------- */

CREATE TABLE [dbo].[TagOfClient](
    [ClientID] [int] NOT NULL,
    [TagID] [int] NOT NULL,

    CONSTRAINT [PK_TagOfClient] PRIMARY KEY CLUSTERED
    (
        [ClientID] ASC,
        [TagID] ASC
    )
);
GO


/* =========================================================
   3. СОЗДАНИЕ СВЯЗЕЙ
   ========================================================= */


/* AttachedProduct -> Product */

ALTER TABLE [dbo].[AttachedProduct]
ADD CONSTRAINT [FK_AttachedProduct_Product]
FOREIGN KEY ([MainProductID])
REFERENCES [dbo].[Product] ([ID]);
GO

ALTER TABLE [dbo].[AttachedProduct]
ADD CONSTRAINT [FK_AttachedProduct_Product1]
FOREIGN KEY ([AttachedProductID])
REFERENCES [dbo].[Product] ([ID]);
GO


/* Client -> Gender */

ALTER TABLE [dbo].[Client]
ADD CONSTRAINT [FK_Client_Gender]
FOREIGN KEY ([GenderCode])
REFERENCES [dbo].[Gender] ([Code]);
GO


/* ClientService -> Client */

ALTER TABLE [dbo].[ClientService]
ADD CONSTRAINT [FK_ClientService_Client]
FOREIGN KEY ([ClientID])
REFERENCES [dbo].[Client] ([ID]);
GO


/* ClientService -> Service */

ALTER TABLE [dbo].[ClientService]
ADD CONSTRAINT [FK_ClientService_Service]
FOREIGN KEY ([ServiceID])
REFERENCES [dbo].[Service] ([ID]);
GO


/* DocumentByService -> ClientService */

ALTER TABLE [dbo].[DocumentByService]
ADD CONSTRAINT [FK_DocumentByService_ClientService]
FOREIGN KEY ([ClientServiceID])
REFERENCES [dbo].[ClientService] ([ID]);
GO


/* Product -> Manufacturer */

ALTER TABLE [dbo].[Product]
ADD CONSTRAINT [FK_Product_Manufacturer]
FOREIGN KEY ([ManufacturerID])
REFERENCES [dbo].[Manufacturer] ([ID]);
GO


/* ProductPhoto -> Product */

ALTER TABLE [dbo].[ProductPhoto]
ADD CONSTRAINT [FK_ProductPhoto_Product]
FOREIGN KEY ([ProductID])
REFERENCES [dbo].[Product] ([ID]);
GO


/* ProductSale -> ClientService */

ALTER TABLE [dbo].[ProductSale]
ADD CONSTRAINT [FK_ProductSale_ClientService]
FOREIGN KEY ([ClientServiceID])
REFERENCES [dbo].[ClientService] ([ID]);
GO


/* ProductSale -> Product */

ALTER TABLE [dbo].[ProductSale]
ADD CONSTRAINT [FK_ProductSale_Product]
FOREIGN KEY ([ProductID])
REFERENCES [dbo].[Product] ([ID]);
GO


/* ServicePhoto -> Service */

ALTER TABLE [dbo].[ServicePhoto]
ADD CONSTRAINT [FK_ServicePhoto_Service]
FOREIGN KEY ([ServiceID])
REFERENCES [dbo].[Service] ([ID]);
GO


/* TagOfClient -> Client */

ALTER TABLE [dbo].[TagOfClient]
ADD CONSTRAINT [FK_TagOfClient_Client]
FOREIGN KEY ([ClientID])
REFERENCES [dbo].[Client] ([ID]);
GO


/* TagOfClient -> Tag */

ALTER TABLE [dbo].[TagOfClient]
ADD CONSTRAINT [FK_TagOfClient_Tag]
FOREIGN KEY ([TagID])
REFERENCES [dbo].[Tag] ([ID]);
GO