db create

/*
 * creation DB for lab
 */

use [master]
drop database if exists [Lab1];
create database [Lab1];
use [Lab1];

create table [Animator] (
    [ID]            integer identity(1,1),

    [Number]        char(60)    not null    check([Number] like('[a-zA-Z0-9-][a-zA-Z0-9-][a-zA-Z0-9-][a-zA-Z0-9-][a-zA-Z0-9-][a-zA-Z0-9-][a-zA-Z0-9-][a-zA-Z0-9-][a-zA-Z0-9-][a-zA-Z0-9-]')),
    [FirstName]     varchar(20),
    [MiddleName]    varchar(20),
    [LastName]      varchar(20),
    [Status]        bit         not null    default(1),
    [Hired]         date        not null    default(getdate()),
    [Fired]         date        null,

    constraint [CHK_Fired] check ([Fired] >= [Hired] or [Fired] is null),
    constraint [AK1_Number] unique ([Number]),

    primary key([ID])
);
create table [Nickname] (
    [ID]            integer identity(1,1),

    [Nickname]      varchar(20)  not null,
    [Description]   varchar(255),
    -- Вычесляемое поле. Занята ли кличка или нет.
    [Status]        bit          not null   default(0),

    constraint [AK1_Nickname] unique([Nickname]),

    primary key([ID])
);
create table [AnimatorsNic] (
    [AnimatorID]    integer not null,
    [NicID]         integer not null,

    [Reason]        varchar(50),

    foreign key([AnimatorID]) references [Animator]([ID]),
    foreign key([NicID]) references [Nickname]([ID]),

    primary key([AnimatorID], [NicID])
);
create table [Health] (
    [AnimatorID]    integer not null,

    [Max]            smallint not null      check([Max] >=0 and [Max] <= 100),
    [Current]        smallint not null,
    [MaxSatiety]     smallint not null      check([MaxSatiety] >=0 and [MaxSatiety] <= 100),
    [CurrentSatiety] smallint not null,
    [LastMeal]       datetime               default(getdate()),

    constraint [CHK_Health]     check ([Current] <= [Max] and [Current] >= 0),
    constraint [CHK_Satiety]    check ([CurrentSatiety] <= [MaxSatiety] and [CurrentSatiety]>= 0),

    foreign key([AnimatorID]) references [Animator]([ID]),

    primary key([AnimatorID])
);
create table [Scenario] (
    [ID]            integer identity(1,1),

    [Name]          varchar(50) not null,
    [Scenario]      text        not null,

    primary key([ID])
);
create table [Service] (
    [ServiceID]     integer identity(1,1),
    [ScenarioID]    integer not null,

    [Name]          varchar(50)  not null,
    [TargetAge]     varchar(5)   not null   default('6+'),
    [Duration]      float        not null,
    [Price]         money        not null,
    [Description]   varchar(255) not null,

    foreign key([ScenarioID]) references [Scenario]([ID]),

    primary key([ServiceID], [ScenarioID])
);
create table [Specialization] (
    [AnimatorID]    integer not null,
    [ServiceID]     integer not null,
    [ScenarioID]    integer not null,

    [Grade]         smallint,
    [Sertificat]    bit     not null    default(0),

    foreign key([ServiceID], [ScenarioID]) references [Service]([ServiceID], [ScenarioID]),
    foreign key([AnimatorID]) references [Animator]([ID]),

    primary key([AnimatorID], [ServiceID], [ScenarioID])
);
create table [Client] (
    [ID]            integer identity(1,1),

    [FirstName]     varchar(20) not null,
    [MiddleName]    varchar(20) not null,
    [LastName]      varchar(20) not null,
    [Phone]         char(14)    not null,
    [Email]         varchar(25) not null,

    constraint [AK1_Phone] unique([Phone]),
    constraint [AK2_Email] unique([Email]),

    primary key([ID])
);
create table [Order] (
    [ID]            integer identity(1,1),

    [ClientID]      integer  not null,
    [Price]         money    not null   default(0),
    [OrderDate]     datetime not null   default(getdate()),
    [ExecutionDate] datetime not null   default(getdate()),

    constraint [CHK_Price] check ([Price] >= 0),
    constraint [CHK_OrderDate] check ([ExecutionDate] >= [OrderDate]),

    foreign key([ClientID]) references [Client]([ID]),

    primary key([ID])
);
create table [ServiceOrder] (
    [ServiceID]     integer not null,
    [ScenarioID]    integer not null,
    [AnimatorID]    integer not null,
    [OrderID]       integer not null,
    [Status]        bit     not null    default(1),

    foreign key([ServiceID], [ScenarioID]) references [Service]([ServiceID], [ScenarioID]),
    foreign key([AnimatorID]) references [Animator]([ID]),
    foreign key([OrderID]) references [Order]([ID]),

    primary key([ServiceID], [ScenarioID], [AnimatorID], [OrderID])
);
create table [Payment] (
    [ID]            integer identity(1,1),

    [OrderID]       integer  not null,
    [FDP]           char(10) not null   check([FDP] like('[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')),
    [FullPayment]   bit      not null   default(1),
    [Approved]      bit,
    [Date]          datetime not null   default(getdate()),

    foreign key([OrderID]) references [Order]([ID]),

    primary key([ID])
);
create table [Food] (
    [ID]            integer identity(1,1),

    [Name]          varchar(20) not null,
    [Price]         money       not null,
    [Calorific]     float       not null,

    primary key([ID])
);
create table [FoodPayment] (
    [FoodID]        integer not null,
    [PaymentID]     integer not null,

    [Date]          datetime not null   default(getdate()),
    [Count]         integer  not null   default(1)  check([Count] > 0),

    foreign key([FoodID]) references [Food]([ID]),
    foreign key([PaymentID]) references [Payment]([ID]),

    primary key([FoodID], [PaymentID])
);
create table [Journal] (
    [ID]            integer identity(1,1),

    [ScenarioID]    integer  not null,
    [ServiceID]     integer  not null,
    [OrderID]       integer  not null,
    [AnimatorID]    integer  not null,
    [Date]          datetime not null       default(getdate()),

    constraint [AK1_Key] unique ([ScenarioID], [ServiceID], [OrderID], [AnimatorID]),

    foreign key([ServiceID], [ScenarioID], [AnimatorID], [OrderID]) references [ServiceOrder]([ServiceID], [ScenarioID], [AnimatorID], [OrderID]),

    primary key([ID])
);
create table [Limetation] (
    [ID]            integer identity(1,1),

    [Description]   varchar(255) not null,
    [CreatedAt]     datetime     not null   default(getdate()),
    [UpdatedAt]     datetime     not null   default(getdate()),

    constraint [CHK_Updated] check ([UpdatedAt] >= [CreatedAt]),

    primary key([ID])
);
create table [ScenarioLimetation] (
    [LimetationID]  integer      not null,
    [ScenarioID]    integer      not null,

    [Date]          datetime     not null   default(getdate()),
    [Status]        bit          not null   default(1),

    foreign key([LimetationID]) references [Limetation]([ID]),
    foreign key([ScenarioID])   references [Scenario]([ID]),

    primary key([LimetationID], [ScenarioID])
);
create table [OrderLimetation] (
    [LimetationID]  integer     not null,
    [ScenarioID]    integer     not null,
    [JournalID]     integer     not null,

    [Date]          datetime    not null    default(getdate()),

    foreign key([LimetationID], [ScenarioID]) references [ScenarioLimetation]([LimetationID], [ScenarioID]),
    foreign key([JournalID])   references [Journal]([ID]),

    primary key([LimetationID], [ScenarioID], [JournalID])
);
create table [Dump] (
    [ID]            integer identity(1,1),

    [Name]          varchar(20),
    [Address]       varchar(50) not null,

    primary key([ID])
);
create table [Registration] (
    [AnimatorID]    integer not null,
    [DumpID]        integer not null,

    [Status]        bit     not null    default(1),

    foreign key([AnimatorID]) references [Animator]([ID]),
    foreign key([DumpID]) references [Dump]([ID]),

    primary key([AnimatorID], [DumpID])
);
create table [FoodList] (
    [FoodID]        integer  not null,
    [DumpID]        integer  not null,

    [OwnerID]       integer  not null,
    [Count]         integer             default(1)  check([Count] >= 0),
    [Fresh]         datetime not null,

    foreign key([FoodID]) references [Food]([ID]),
    foreign key([DumpID]) references [Dump]([ID]),

    primary key([FoodID], [DumpID], [OwnerID])
);
create table [Matherial] (
    [ID]            integer identity(1,1),

    [Name]          varchar(20) not null,
    [Description]   varchar(255),

    primary key([ID])
);
create table [MatherialList] (
    [DumpID]        integer  not null,
    [MatherialID]   integer  not null,

    [Count]         integer             default(1)  check([Count] >= 0),

    foreign key([MatherialID]) references [Matherial]([ID]),
    foreign key([DumpID]) references [Dump]([ID]),

    primary key([DumpID],[MatherialID])
);
create table [Costume] (
    [ID]            integer identity(1,1),

    [Name]          varchar(20) not null,
    [Description]   varchar(255),
    [Durability]    integer     not null,
    [Status]        bit         not null    default(1),

    primary key([ID])
);
create table [Receipt] (
    [CostumeID]     integer  not null,
    [MatherialID]   integer  not null,

    [AsembleCount]      integer             default(1)  check([AsembleCount] >= 0),
    [DisasembleCount]   integer             default(1)  check([DisasembleCount] >= 0),

    constraint [CHK_Count] check ([AsembleCount] >= [DisasembleCount]),

    foreign key([MatherialID]) references [Matherial]([ID]),
    foreign key([CostumeID]) references [Costume]([ID]),

    primary key([CostumeID],[MatherialID])
);
create table [ServiceCostume] (
    [CostumeID]     integer  not null,
    [ServiceID]     integer  not null,
    [ScenarioID]    integer  not null,

    [Count]         integer                 default(1)  check([Count] >= 0),
    [Damage]        integer                 default(1)  check([Damage] >= 0),

    foreign key([ServiceID], [ScenarioID]) references [Service]([ServiceID],[ScenarioID]),
    foreign key([CostumeID]) references [Costume]([ID]),

    primary key([CostumeID],[ServiceID],[ScenarioID])
);