create table dbo.Country(
iCountryId int not null identity primary key,
chCountryCode char(2) not null constraint u_Country_CountryCode_must_be_unique unique
constraint ck_country_CountryCode_Must_be_2_characters check (len(chCountryCode) = 2)
)
go

create table dbo.Region(
iRegionId int not null identity primary key,
iCountryId int not null constraint f_country_region foreign key references country(iCountryId),
vchRegionName varchar(30) not null
constraint u_region_RegionName_must_be_unique unique
constraint ck_region_RegionName_cant_be_blank check (vchRegionName <> '')
)
go

create table dbo.ParentCompany(
iParentCompanyId int not null identity Primary key,
vchParentCompanyName varchar(255) not null
constraint u_ParentCompany_ParentCompanyName_must_be_unique unique
constraint ck_ParentCompany_ParentCompanyName_cant_be_blank check (vchParentCompanyName <> '')
)
go

create table dbo.Store(
iStoreId int not null identity primary key,
iRegionId int not null
constraint f_Region_Store foreign key references Region(iRegionId),
iParentCompanyId int null
constraint f_ParentCompany_Store foreign key references ParentCompany(iParentCompanyId),
iStoreNo int
constraint u_store_StoreNo_must_be_unique unique
constraint ck_store_StoreNo_must_be_greater_than_0 check (iStoreNo > 0),
vchStoreName varchar(255) not null
constraint ck_store_storeName_cant_be_blank check(vchStoreName <> '')

)
go

create table dbo.ShelfSet(
iShelfSetId int not null identity primary key,
iStoreId int not null
constraint f_store_ShelfSet foreign key references store(iStoreId),
vchShelfSetName varchar(25) not null
constraint ck_shelfSet_shelfSetName_is_not_blank check (vchShelfSetName <> ''),
iShelfSetSequence int not null
constraint ck_shelfSet_ShelfSetSequence_must_be_greater_than_0 check(ishelfSetSequence > 0),
--RP This constraint may be too restrictive - maybe there are two "front window display cases" - each with a different sequence
--CL--Fixed--removed it.
constraint u_shelfSet_ShelfSetSequence_must_be_unique unique(iStoreId,iShelfSetSequence)

)
go

create table dbo.Shelf(
iShelfId int not null identity primary key,
iShelfSetId int not null
constraint f_ShelfSet_Shelf foreign key references ShelfSet(iShelfSetId),
iShelfSequence int not null
constraint ck_shelf_ShelfSequence_must_be_greater_than_0 check(ishelfSequence > 0),
iSubunitCapacity int not null
constraint ck_shelf_SubUnitCapacity_must_be_greater_than_or_equal_to_0 check(iSubunitCapacity >= 0)
constraint ck_shelf_shelfSequence_must_be_unique unique (iShelfSetid,IshelfSequence)
)
go

create table dbo.BeverageType(
iBeverageTypeId int not null identity primary key,
vchBeverageTypeName varchar(100) not null
constraint u_BeverageType_BeverageTypeName_cant_be_blank unique
constraint ck_BeverageType_BeverageTypeName_Cant_be_blank check(vchBeverageTypeName <> ''),
vchAlcoholContentDescriptor varchar(100) not null
constraint ck_beverageType_AlchocholContentDescriptor_cant_be_blank check(vchAlcoholContentDescriptor <> ''),
dMinAlcoholContent decimal(6,2) not null
constraint ck_bevergetype_minAlcoholContent_must_be_beteween_0_and_100 check(dMinAlcoholContent between 0 and 100) ,
dMaxAlcoholContent decimal(6,2) not null
constraint ck_bevergetype_maxAlcoholContent_must_be_beteween_0_and_100 check(dMaxAlcoholContent between 0 and 100)
)
go

create table dbo.BeverageTypeNotAllowedinRegion(
iBeverageTypeNotAllowedinRegionId int not null identity primary key,
iBeverageTypeId int not null
constraint f_BeverageType_BeverageTypeNotAllowedinRegion foreign key references beverageType(iBeverageTypeId),
iRegionId int not null
constraint f_Region_BeverageTypeNotAllowedinRegion foreign key references Region(iRegionId)
constraint u_BeverageType_BevegrageTypeName_and_Region_must_be_unique unique(iBeverageTypeId,iRegionId)
)

create table dbo.BeverageSubtype(
iBeverageSubtypeId int not null identity primary key,
iBeverageTypeId int not null
constraint f_BeverageType_BeverageSubtype foreign key references BeverageType(iBeverageTypeId),
vchBeverageSubtypeName varchar(100) not null
constraint u_BeverageSubtype_BeverageSubtypeName_must_be_unique unique
constraint ck_BeverageSubtype_BeverageSubtypeName_cant_be_blank check (vchBeverageSubtypeName <> '')
)
go

create table dbo.Brand(
iBrandId int not null identity primary key,
vchBrandName varchar(100) not null
constraint u_Brand_BrandName_must_be_unique unique
constraint ck_brand_brandName_cant_be_blank check (vchBrandName <> '')
)
go

create table dbo.Beverage(
iBeverageId int not null identity primary key,
iBeverageSubtypeId int not null
constraint f_beverageSubtype_beverage foreign key references beveragesubtype(iBeverageSubtypeId),
iBrandId int not null
constraint f_brand_beverage foreign key references brand(iBrandId),
vchBeverageName varchar(100)
constraint ck_beverage_beverageName_cant_be_blank check(vchBeverageName <> ''),
iYear int not null default 0,
dAlcoholContent decimal(5,2),
constraint u_beverage_name_year_and_brand_must_be_unique unique(ibrandid,vchBeverageName,iYear),
constraint ck_beverage_alcoholContent_must_be_in_allowable_range
check(dbo.fnAlcoholContentWithinAllowedRange(dAlcoholContent,iBeverageSubtypeId)>=1)

)
go

create table dbo.PackageType(
iPackageTypeId int not null identity primary key,
vchPackageTypeName varchar(100)
constraint u_packageType_packageTypeName_must_be_unique unique
constraint ck_packageType_packageTypeName_cant_be_blank check (vchPackageTypeName <> '')
)
go

create table dbo.SaleUnit(
iSaleUnitId int not null identity primary key,
iPackageTypeId int not null
constraint f_packageType_Sale foreign key references PackageType(iPackageTypeId),
iBeverageId int not null
constraint f_beverage_sale foreign key references beverage(iBeverageId),
iSubunitQuantity int not null
constraint ck_sale_subunitQuantity_must_be_greater_than_0 check(iSubunitQuantity > 0)
)
go


create table dbo.Customer(
iCustomerId int not null identity primary key,
vchFirstName varchar(30) not null ,
vchLastName varchar(30) not null ,
chPhoneNumber char(10) not null,
bAnonymous bit not null,
constraint u_customer_each_customer_must_be_unique unique(vchFirstName,vchLastName,chPhoneNumber)
)


create table dbo.Orders(
iOrderId int not null identity primary key,
iCustomerId int not null
constraint f_customer_orders foreign key references customer(iCustomerId),
dDateOrder date not null default getdate(),
gOrderNumber uniqueidentifier default NewID()
)




create table dbo.Inventory(
iInventoryId int not null identity primary key,
iSaleUnitId int not null
constraint f_SaleUnit_Inventory foreign key references saleUnit(isaleUnitId),
iShelfId int null
constraint f_Shelf_Inventory foreign key references Shelf(iShelfId),
iOrderid int null
constraint f_Orders_Inventory foreign key references Orders(iOrderId),
gBarcode uniqueidentifier not null,
iStoreId as dbo.fnreturnStoreId(iInventoryId),
mPrice money not null
constraint ck_inventory_Price_is_a_positive_number check(mPrice >= 0),
constraint ck_Inventory_Is_BeverageType_allowed_to_Be_sold_in_region
check(dbo.fnisBeverageTypeAllowedinRegion(iSaleUnitId,iShelfId) = 1),
constraint ck_inventory_IShelfId_iorderId_cant_both_be_ null_or_have_value
check((iOrderid is not null and ishelfid is null) or (iOrderid is null and ishelfid is not null) )
)

go



;