CREATE TABLE SHOPS
( ShopID INT not null
, FloorsNumber INT DEFAULT 10
, EmployeesNumber INT CHECK (EmployeesNumber>0)
, Surface INT DEFAULT 20000
, PhoneNumber INT CHECK (PhoneNumber LIKE ('987[0-9][0-9][0-9][0-9][0-9][0-9]'))
, Zip INT not null
, Street VARCHAR (25) not null
, City VARCHAR (25) not null
, CONSTRAINT PK_SHOPS PRIMARY KEY(ShopID)
);

CREATE TABLE EMPLOYEES
( SSN INT not null
, NameEmp VARCHAR not null
, SurnameEmp VARCHAR not null
, Birthday DATE not null
, EmailAdress NVARCHAR (25)
, PhoneNumber INT CHECK (PhoneNumber LIKE ('[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'))
, BankAccount VARCHAR (24)
, Street VARCHAR (50)
, Zip INT
, Sex CHAR CHECK (Sex IN ('M','F'))
, EmployeeDNI INT not null CHECK (EmployeeDNI LIKE ('[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][A-Z]'))
, HoursWeek INT DEFAULT 40
, YearsInCompany INT DEFAULT 0
, Category VARCHAR (20) not null CHECK (Category IN ('Manager','Customer supporter','Dependent','Security worker','Web designer'))
, CONSTRAINT PK_EMPLOYEES PRIMARY KEY(SSN)
);

CREATE TABLE PRODUCTS
( UPC INT not null
, NmePro VARCHAR (50)
, Quantity INT
, Brand VARCHAR (50)
, Price MONEY CHECK (Price<10000)
, CONSTRAINT PK_PRODUCTS PRIMARY KEY(UPC)
);

CREATE TABLE DEPARTMENTS
( DepartmentCode INT not null
, Employees# INT not null
, DepartmentName VARCHAR (10) not null CHECK (DepartmentName IN ('Food','Technology','Sports','Cosmetics','Clothes'))
, CONSTRAINT PK_DEPARTMENTS PRIMARY KEY(DepartmentCode)
);

CREATE TABLE MANAGER
( ManagerSSN INT not null
, TitleOfUniversity VARCHAR(50) DEFAULT 'Collage'
, CONSTRAINT PK_MANAGER PRIMARY KEY (ManagerSSN)
, CONSTRAINT FK_MANAGER FOREIGN KEY (ManagerSSN) REFERENCES EMPLOYEES(SSN)
);

CREATE TABLE DEPENDENTEMP
( DependentSSN INT not null
, ManagerSSN INT not null
, DependentPasswd VARCHAR (4) not null CHECK (DependentPasswd LIKE ('[0-9][0-9][a-z][0-9]'))
, LanguageLevel VARCHAR (2) DEFAULT 'B1'
, DepartmentName VARCHAR (10) CHECK (DepartmentName IN ('Food','Technology','Sports','Cosmetics','Clothes'))
, CONSTRAINT PK_DEPENDENTS PRIMARY KEY (DependentSSN)
, CONSTRAINT FK_DEPENDENTS FOREIGN KEY (ManagerSSN) REFERENCES MANAGER(ManagerSSN)
, CONSTRAINT FK_DEPENDENTS2 FOREIGN KEY (DependentSSN) REFERENCES EMPLOYEES(SSN)
);

CREATE TABLE SALES
( ShopID INT not null
, DependentSSN INT not null
, Datesale DATE not null DEFAULT getdate()
, Quantity INT DEFAULT 1
, CONSTRAINT PK_SALES PRIMARY KEY (ShopID, DependentSSN, Datesale)
, CONSTRAINT FK_DEPENDENTSSN FOREIGN KEY (DependentSSN) REFERENCES DEPENDENTEMP(DependentSSN)
, CONSTRAINT SK_QUANTITY CHECK (Quantity<1000)
);

CREATE TABLE CUSTOMERSUPPORTER
( CusSuppSSN INT not null
, ManagerSSN INT not null
, CustomerSuppPasswd VARCHAR (4) not null CHECK (CustomerSuppPasswd LIKE ('[0-9][0-9][a-z][0-9]'))
, LanguageLevel VARCHAR (2) DEFAULT 'B2'
, CONSTRAINT PK_CUSSUPPSSN PRIMARY KEY (CusSuppSSN)
, CONSTRAINT FK_CUSTOMERSUPPORTER FOREIGN KEY (ManagerSSN) REFERENCES MANAGER(ManagerSSN)
, CONSTRAINT FK_CUSTOMERSUPPORTER2 FOREIGN KEY (CusSuppSSN) REFERENCES EMPLOYEES(SSN)
);

CREATE TABLE WEBDESIGNER
( WebDesignerSSN INT not null
, ManagerSSN INT not null
, TitleOfUniversity VARCHAR (50)
, ExtraHours INT CHECK (ExtraHours<4 AND ExtraHours>0)
, CONSTRAINT PK_WEBDESIGNER PRIMARY KEY (WebDesignerSSN)
, CONSTRAINT FK_WEBDESIGNER FOREIGN KEY (ManagerSSN) REFERENCES MANAGER(ManagerSSN)
, CONSTRAINT FK_WEBDESIGNER2 FOREIGN KEY (WebDesignerSSN) REFERENCES EMPLOYEES(SSN)
);

CREATE TABLE SECURITYWORKER
( SecWorkerSSN INT not null
, ManagerSSN INT not null
, CONSTRAINT PK_SECURITYWORKER PRIMARY KEY (SecWorkerSSN)
, CONSTRAINT FK_SECURITYWORKER FOREIGN KEY (ManagerSSN) REFERENCES MANAGER(ManagerSSN)
, CONSTRAINT FK_SECURITYWORKER2 FOREIGN KEY (SecWorkerSSN) REFERENCES EMPLOYEES(SSN)
);

CREATE TABLE PAYROLLS
( SSN INT not null
, DatePayroll DATE not null DEFAULT getdate()
, Amount INT null
, CONSTRAINT PK_PAYROLLS PRIMARY KEY (SSN, DatePayroll)
, CONSTRAINT FK_PAYROLLS FOREIGN KEY (SSN) REFERENCES EMPLOYEES(SSN)
);

CREATE TABLE WAREHOUSE
( WarehouseCode INT not null
, ShopID INT not null
, CONSTRAINT PK_WAREHOUSE PRIMARY KEY (WarehouseCode, ShopID)
, CONSTRAINT FK_WAREHOUSE FOREIGN KEY (ShopID) REFERENCES SHOPS(ShopID)
);

CREATE TABLE SUPPLIERS
( SuppliersCode INT not null
, BankAccount VARCHAR (24)
, PhoneNumber INT CHECK (PhoneNumber LIKE ('[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'))
, EmailAddress NVARCHAR (25)
, Zip INT not null
, Street VARCHAR (50) not null
, City VARCHAR (25) not null DEFAULT 'Madrid'
, Price MONEY
, Category CHAR not null CHECK (Category IN ('I','N'))
, CONSTRAINT PK_SUPPLIERS PRIMARY KEY (SuppliersCode)
);

CREATE TABLE CLIENTS
( ClientID INT not null
, EmailAdress NVARCHAR (25)
, PhoneNumber INT
, BankAccount VARCHAR (24)
, City VARCHAR (25) not null
, Street VARCHAR (50) not null
, Zip INT not null
, ClientDNI VARCHAR (9) not null CHECK (ClientDNI LIKE ('[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][A-Z]'))
, Nationality VARCHAR (30)
, ClientsType VARCHAR (2) not null CHECK (ClientsType IN ('C','P','PP','PN'))
, CONSTRAINT PK_CLIENTS PRIMARY KEY(ClientID)
);

CREATE TABLE ORDERS
( UPC INT not null
, ManagerSSN INT not null
, SuppliersCode INT  not null
, OrderWeight INT CHECK (OrderWeight<1000 AND OrderWeight>0)
, Price MONEY DEFAULT 10000.00
, DateUpc DATE not null DEFAULT getdate()
, Quantity INT DEFAULT 50
, CONSTRAINT PK_ORDERS PRIMARY KEY (UPC, ManagerSSN, SuppliersCode, DateUpc)
, CONSTRAINT FK_MANAGERSSN FOREIGN KEY (ManagerSSN) REFERENCES MANAGER(ManagerSSN)
, CONSTRAINT FK_SUPPLIERSCODE FOREIGN KEY (SuppliersCode) REFERENCES SUPPLIERS(SuppliersCode)
, CONSTRAINT FK_UPC FOREIGN KEY (UPC) REFERENCES PRODUCTS(UPC)
);

CREATE TABLE COMPANY
( CompanyClientID INT not null
, Name_company VARCHAR (30)
, CONSTRAINT PK_COMPANY PRIMARY KEY(CompanyClientID)
, CONSTRAINT FK_COMPANYCLIENTID FOREIGN KEY (CompanyClientID) REFERENCES CLIENTS(ClientID)
);

CREATE TABLE PRIVATE_CLI
( PrivateClientID INT not null
, DiscountCard INT
, CONSTRAINT PK_PRIVATE PRIMARY KEY(PrivateClientID)
, CONSTRAINT FK_PRIVATECLIENTID FOREIGN KEY (PrivateClientID) REFERENCES CLIENTS(ClientID)
);

CREATE TABLE PREMIUM_PRI_CLI
( PrivatePremiunID INT not null
, DiscountCard INT
, CONSTRAINT PK_PREMIUM_PRI_CLI PRIMARY KEY (PrivatePremiunID)
, CONSTRAINT FK_PREMIUM_PRI_CLI FOREIGN KEY (PrivatePremiunID) REFERENCES PRIVATE_CLI(PrivateClientID)
);

CREATE TABLE NOPREMIUM_PRI_CLI
( PrivateNoPremiunID INT not null
, CONSTRAINT PK_NOPREMIUM_PRI_CLI PRIMARY KEY (PrivateNoPremiunID)
, CONSTRAINT FK_NOPREMIUM_PRI_CLI FOREIGN KEY (PrivateNoPremiunID) REFERENCES PRIVATE_CLI(PrivateClientID)
);

CREATE TABLE OFFERS
( OfferCode INT not null
, IniciationDate Date DEFAULT getdate()
, ExpirationDate Date
, Discount INT CHECK (Discount<70)
, CONSTRAINT PK_OFFERS PRIMARY KEY(OfferCode)
);

CREATE TABLE COSMETIC
( CosmeticUPC INT not null
, ComseticType VARCHAR
, CONSTRAINT PK_COSMETICS PRIMARY KEY(CosmeticUPC)
, CONSTRAINT FK_COSMETICS FOREIGN KEY (CosmeticUPC) REFERENCES Products(UPC)
);

CREATE TABLE FOOD
( FoodUPC INT not null
, FoodType VARCHAR
, CONSTRAINT PK_FOOD PRIMARY KEY(FoodUPC)
, CONSTRAINT FK_FOOD FOREIGN KEY (FoodUPC) REFERENCES Products(UPC)
);

CREATE TABLE TECHNOLOGY
( TechnoUPC  INT not null
, TechnoType VARCHAR
, CONSTRAINT PK_TECHNOLOGY PRIMARY KEY(TechnoUPC)
, CONSTRAINT FK_TECHNOLOGY FOREIGN KEY (TechnoUPC) REFERENCES Products(UPC)
);

CREATE TABLE CLOTHES
( ClothesUPC INT not null
, ClothesType VARCHAR
, CONSTRAINT PK_CLOTHES PRIMARY KEY(ClothesUPC)
, CONSTRAINT FK_CLOTHES FOREIGN KEY (ClothesUPC) REFERENCES Products(UPC)
);

CREATE TABLE SPORTS
( SportUPC INT not null
, SportType VARCHAR
, CONSTRAINT PK_SPORTS PRIMARY KEY(SportUPC)
, CONSTRAINT FK_SPORTS FOREIGN KEY (SportUPC) REFERENCES Products(UPC)
);

CREATE TABLE CLISHOP
( ClientID INT not null
, ShopID INT not null
, CONSTRAINT PK_CLISHO PRIMARY KEY(ClientID, ShopID)
, CONSTRAINT FK_CLISHO FOREIGN KEY (ClientID) REFERENCES Clients(ClientID)
, CONSTRAINT FK_CLISHO2 FOREIGN KEY (ShopID) REFERENCES Shops(ShopID)
);

CREATE TABLE PREOFFPRO
( OfferCode INT not null
, ClientID INT not null
, UPC INT not null
, DatePreoffpro DATE not null DEFAULT getdate()
, CONSTRAINT PK_PREOFFPRO PRIMARY KEY (OfferCode, ClientID, UPC, DatePreoffpro)
, CONSTRAINT FK_PREOFFPRO FOREIGN KEY (OfferCode) REFERENCES OFFERS(OfferCode)
, CONSTRAINT FK_PREOFFPRO2 FOREIGN KEY (ClientID) REFERENCES CLIENTS(ClientID)
, CONSTRAINT FK_PREOFFPRO3 FOREIGN KEY (UPC) REFERENCES PRODUCTS(UPC)
);

CREATE TABLE INCOMES
( DateIncomes DATE not null DEFAULT getdate()
, ShopID INT not null
, IncomeName VARCHAR
, Quantity INT
, CONSTRAINT PK_INCOMES PRIMARY KEY (DateIncomes, ShopID)
, CONSTRAINT FK_INCOMES FOREIGN KEY (ShopID) REFERENCES SHOPS(ShopID)
);

CREATE TABLE TYPE
( DepartmentCode INT not null
, UPC INT not null
, DependentSSN INT not null
, CONSTRAINT PK_TYPE PRIMARY KEY(DepartmentCode, UPC, DependentSSN)
, CONSTRAINT FK_TYPE FOREIGN KEY (DepartmentCode) REFERENCES DEPARTMENTS(DepartmentCode)
, CONSTRAINT FK_TYPE2 FOREIGN KEY (UPC) REFERENCES PRODUCTS(UPC)
, CONSTRAINT FK_TYPE3 FOREIGN KEY (DependentSSN) REFERENCES DEPENDENTEMP(DependentSSN)
);

CREATE TABLE CLIPRO
( ClientID INT not null
, UPC INT not null
, DateCliPro DATE not null DEFAULT getdate()
, CONSTRAINT PK_CLIPRO PRIMARY KEY (ClientID, UPC, DateCliPro)
, CONSTRAINT FK_CLIPRO FOREIGN KEY (ClientID) REFERENCES CLIENTS(ClientID)
, CONSTRAINT FK_CLIPRO2 FOREIGN KEY (UPC) REFERENCES PRODUCTS(UPC)
);

CREATE TABLE COMPLAINTS
( CustomerSupporterSSN INT not null
, ClientID INT not null
, ComplaintID INT not null
, Date_comp DATE not null DEFAULT getdate()
, Desc_complaints VARCHAR (250)
, Resolved CHAR CHECK (Resolved IN ('Y','N'))
, CONSTRAINT PK_COMPLAINTS PRIMARY KEY(CustomerSupporterSSN,ClientID,Date_comp)
, CONSTRAINT FK_COMPLAINTS FOREIGN KEY (CustomerSupporterSSN) REFERENCES CUSTOMERSUPPORTER(CusSuppSSN)
, CONSTRAINT FK_COMPLAINTS2 FOREIGN KEY (ClientID) REFERENCES CLIENTS(ClientID)
);


CREATE TABLE INVENTORY
( Quantity INT not null
, ShopID INT not null
, UPC INT not null
, DateIn DATE DEFAULT getdate()
, CONSTRAINT PK_INVENTORY PRIMARY KEY(Quantity,ShopID)
, CONSTRAINT FK_INVENTORY FOREIGN KEY (ShopID) REFERENCES SHOPS(ShopID)
, CONSTRAINT FK_INVENTORY2 FOREIGN KEY (UPC) REFERENCES PRODUCTS(UPC)
)