USE [master]
GO

DROP DATABASE IF EXISTS [Structures]
GO

CREATE DATABASE [Structures]
GO

USE [Structures]
GO

IF OBJECT_ID('Expertises', 'U') IS NOT NULL
DROP TABLE [Expertises]
GO

IF OBJECT_ID('Employees', 'U') IS NOT NULL
DROP TABLE [Employees]
GO

IF OBJECT_ID('Expertises', 'U') IS NULL
CREATE TABLE [Expertises]
(
    [ID] INT IDENTITY(1,1),
    [Name] VARCHAR(15) NOT NULL,
    CONSTRAINT [ExpertiseID_PK] PRIMARY KEY ([ID]),
    CONSTRAINT [ExpertiseName_Unique] UNIQUE ([Name])
)
GO

IF OBJECT_ID('Employees', 'U') IS NULL
CREATE TABLE [Employees]
(
    [ID] INT IDENTITY(1,1),
    [FIRST] VARCHAR(15) NOT NULL,
    [LAST] VARCHAR(15) NOT NULL,
    [City] VARCHAR(15) NOT NULL,
    [Birthday] DATE NOT NULL,
    [Phone] VARCHAR(15) NOT NULL,
    [ExpertiseID] INT NOT NULL,
    [ManagerID] INT NOT NULL,
    CONSTRAINT [EmployeesID_PK] PRIMARY KEY ([ID]),
    CONSTRAINT [EmployeesPhone_Validation] CHECK ([Phone] LIKE '[0-9]{3}-[0-9]{7}'),
    CONSTRAINT [EmployeesExpertise_FK] FOREIGN KEY ([ExpertiseID]) REFERENCES [Expertises] ([ID]),
    CONSTRAINT [EmployeesManager_FK] FOREIGN KEY ([ManagerID]) REFERENCES [Employees] ([ID])
)
GO