IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'Customers')
BEGIN
  CREATE TABLE Customers (
      CustomersID INT PRIMARY KEY NOT NULL,
      LastName NVARCHAR(30) NOT NULL,
      FirstName NVARCHAR(30) NOT NULL,
      City NVARCHAR(15) NULL,
      Postalcode NVARCHAR(10) NULL,
      Country NVARCHAR(15) NULL,
      Phone NVARCHAR(24) NULL 
  );
END;

IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'Orders')
BEGIN
  CREATE TABLE Orders (
      OrderID INT PRIMARY KEY NOT NULL,
      CustomersID INT NULL,
      Order_date DATETIME NULL,
      Shipped_date DATETIME NULL,
      TransportCost INT NULL CONSTRAINT "order_transport" DEFAULT (0),
      ShipVia INT NULL,
      FOREIGN KEY (CustomersID) REFERENCES Customers(CustomersID),
      FOREIGN KEY (ShipVia) REFERENCES Shippers(ShipperID)
  );
END;

IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'Shippers')
BEGIN
  CREATE TABLE Shippers (
      ShipperID INT PRIMARY KEY  NOT NULL,
      CompanyName NVARCHAR(40) NOT NULL,
      phone NVARCHAR(24) NULL
  );
END;

IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'Suppliers')
BEGIN
  CREATE TABLE Suppliers (
      SupplierID INT PRIMARY KEY NOT NULL,
      CompanyName NVARCHAR(40) NOT NULL,
      ContactName NVARCHAR(30) NULL,
      Address NVARCHAR(60) NULL,
      City NVARCHAR(15) NULL,
      Region NVARCHAR(15) NULL,
      PostalCode NVARCHAR(10) NULL,
      Phone NVARCHAR(24) NULL
  );
END;

IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'Supplier_product')
BEGIN
  CREATE TABLE Supplier_product (
      SupplierID INT,
      ProductID INT,
      PRIMARY KEY (SupplierID, ProductID),
      FOREIGN KEY (SupplierID) REFERENCES Suppliers(SupplierID),
      FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
  );
END;

IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'Products')
BEGIN
  CREATE TABLE Products (
      ProductID INT PRIMARY KEY NOT NULL,
      ProductName NVARCHAR(40) NOT NULL,
      CategoryID INT NULL,
      UnitPrice MONEY NULL CONSTRAINT "Prod_unit_price" DEFAULT (0),
      UnitsInStock INT NULL CONSTRAINT "Prod_units_stock" DEFAULT(0),
      Discontinued BIT NOT NULL CONSTRAINT "Prod_discontin" DEFAULT(0),
      FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID)
  );
END;

IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'Categories')
BEGIN
  CREATE TABLE Categories (
      CategoryID INT PRIMARY KEY  NOT NULL,
      CategoryName NVARCHAR(15) NOT NULL,
      TextDescription TEXT NULL,
      Picture IMAGE NULL
  );
END;

IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'Order_Details')
BEGIN
  CREATE TABLE Order_Details (
      OrderID INT NOT NULL,
      ProductID INT NOT NULL,
      PRIMARY KEY (OrderID, ProductID),
      UnitPrice MONEY NOT NULL CONSTRAINT "Order_unit_price" default (0),
      Quantity INT NOT NULL CONSTRAINT "Order_quantity" default(1),
      Discount REAL NOT NULL CONSTRAINT "Order_discount" default(0),
      FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
      FOREIGN KEY (ProductID) REFERENCES Products(ProductID),
	  CONSTRAINT "CK_Discount" CHECK (Discount >= 0 and (Discount <= 1)),
      CONSTRAINT "CK_Quantity" CHECK (Quantity > 0),
      CONSTRAINT "CK_UnitPrice" CHECK (UnitPrice >= 0)
  );
END;

IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'Review')
BEGIN
  CREATE TABLE Review (
      ReviewID INT PRIMARY KEY NOT NULL,
      CustomersID INT NULL,
      ProductID INT NOT NULL,
      ReviewText TEXT NOT NULL,
      Rating INT NOT NULL,
      CreatedDate DATETIME NULL,
      FOREIGN KEY (CustomersID) REFERENCES Customers(CustomersID),
      FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
  );
END;

IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'TicketStatusHistory')
BEGIN
  CREATE TABLE TicketStatusHistory (
      TicketStatusHistoryID INT PRIMARY KEY NOT NULL,
      TicketID INT,
      TicketStatusID INT,
      LastUpdate NVARCHAR(100) NOT NULL,
      FOREIGN KEY (TicketID) REFERENCES Ticket(TicketID),
      FOREIGN KEY (TicketStatusID) REFERENCES TicketStatus(TicketStatusID)
  );
END;

IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'Ticket')
BEGIN
  CREATE TABLE Ticket (
      TicketID INT PRIMARY KEY  NOT NULL,
      Subject NVARCHAR(75) NOT NULL,
      Description TEXT NOT NULL,
      Priority BIT NULL,
      CreatedDate DATETIME NULL,
      UpdatedDate DATETIME NULL,
      AgentID INT NULL,
      CustomersID INT NOT NULL,
      FOREIGN KEY (AgentID) REFERENCES Agent(AgentID),
      FOREIGN KEY (CustomersID) REFERENCES Customers(CustomersID)
  );
END;

IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'TicketStatus')
BEGIN
  CREATE TABLE TicketStatus (
      TicketStatusID INT PRIMARY KEY NOT NULL,
      Status NVARCHAR(30) NOT NULL
  );
END;

IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'Agent')
BEGIN
  CREATE TABLE Agent (
      AgentID INT PRIMARY KEY NOT NULL,
      FirstName NVARCHAR(30) NOT NULL,
      LastName NVARCHAR(30) NOT NULL,
      Email NVARCHAR(50) NULL
  );
END;


FA -MI NISTE UPDATE-URI PE BAZA DE DATE, MAI COMPLEXE