create schema main;

Create table main.Carriers
(Id BIGINT NOT NULL IDENTITY(1,1) PRIMARY KEY,
Name NVARCHAR(255) NOT NULL
Constraint AK_Carriers UNIQUE(Name) 
);

go;

Create table main.Deliveries
(Id BIGINT NOT NULL IDENTITY(1,1) PRIMARY KEY,
Delivery NVARCHAR(255) NOT NULL
Constraint AK_Deliveries UNIQUE(Delivery) 
);

go;

Create table main.Transportations
(Id BIGINT NOT NULL IDENTITY(1,1) PRIMARY KEY,
ShipDate DATE NOT NULL,
DeliveryDate DATE NOT NULL,
DeliveryId  BIGINT NOT NULL,
CarrierId BIGINT NOT NULL,
CarrierDeliveryId BIGINT NULL,
Constraint FK_Transportations_1 foreign key(DeliveryId) References main.Deliveries(Id),
Constraint FK_Transportations_2 foreign key(CarrierId) References main.Carriers(Id),
Constraint FK_Transportations_3 foreign key(CarrierDeliveryId) References main.CarrierDeliveries(Id),
Constraint AK_Transportations UNIQUE(ShipDate,DeliveryDate,DeliveryId,CarrierId ) 
);

go;

Create table main.TransportationCosts
(Id BIGINT NOT NULL IDENTITY(1,1) PRIMARY KEY,
TransportationId BIGINT NOT NULL,
CarrierInvoiceId BIGINT NULL,
Cost FLOAT NULL
Constraint FK_TransportationCosts_1 foreign key(TransportationId) References main.Transportations(Id),
Constraint AK_TransportationCosts UNIQUE(TransportationId ,CarrierInvoiceId ) 
);

go;

Create table main.CarrierInvoices
(Id BIGINT NOT NULL IDENTITY(1,1) PRIMARY KEY,
Invoice NVARCHAR(255) NOT NULL,
CarrierId BIGINT NOT NULL,
InvoiceDate DATE NULL,
InvoiceDate2 DATE NULL,
IsChecked TINYINT NULL
Constraint FK_CarrierInvoices foreign key(CarrierId) References main.Carriers(Id),
Constraint AK_CarrierInvoices UNIQUE(Invoice,CarrierId,InvoiceDate) 
)

go

Create table main.CarrierDeliveries
(Id BIGINT NOT NULL IDENTITY(1,1) PRIMARY KEY,
Delivery NVARCHAR(255) NOT NULL,
CarrierId BIGINT NOT NULL
Constraint FK_CarrierDeliveries foreign key(CarrierId) References main.Carriers(Id),
Constraint AK_CarrierDeliveries UNIQUE(Delivery,CarrierId) 
)


Create table main.CarrierInvoiceDeliveryCosts
(
CarrierInvoiceId  BIGINT NOT NULL,
CarrierDeliveryId BIGINT NOT NULL,
Cost FLOAT NOT NULL
Constraint FK_CarrierInvoiceDeliveryCosts_1 foreign key(CarrierInvoiceId) References main.CarrierInvoices(Id),
Constraint FK_CarrierInvoiceDeliveryCosts_2 foreign key(CarrierDeliveryId) References main.CarrierDeliveries(Id),
Constraint AK_CarrierInvoiceDeliveryCosts UNIQUE(CarrierInvoiceId,CarrierDeliveryId  ) 

)