CREATE TABLE Mechanics(
MechanicId INT PRIMARY KEY IDENTITY,
Firstname VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
Address VARCHAR(255) NOT NULL
)

CREATE TABLE Clients(
ClientId INT PRIMARY KEY IDENTITY,
Firstname VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
Phone VARCHAR(12) CHECK (LEN(Phone) = 12) NOT NULL

)

CREATE TABLE Models(
ModelId INT PRIMARY KEY IDENTITY,
Name VARCHAR(50) UNIQUE NOT NULL
)

CREATE TABLE Vendors(
VendorId INT PRIMARY KEY IDENTITY,
Name VARCHAR(50) UNIQUE NOT NULL
)

CREATE TABLE Jobs(
JobId INT PRIMARY KEY IDENTITY,
ModelId INT NOT NULL,
Status VARCHAR(11) DEFAULT 'Pending' CHECK(Status IN ('Pedning', 'In Progress', 'Finished') ),
ClientId INT NOT NULL,
MechanicId INT,
IssueDate DATE NOT NULL,
FinishDate DATE

CONSTRAINT FK_Jobs_Models FOREIGN KEY (ModelId) REFERENCES Models(ModelId),
CONSTRAINT FK_Jobs_Clients FOREIGN KEY(ClientId) REFERENCES Clients(ClientId),
CONSTRAINT KF_Jobs_Mechanics FOREIGN KEY (MechanicId) REFERENCES Mechanics(MechanicId)
)

CREATE TABLE Orders(
OrderId INT PRIMARY KEY IDENTITY,
JobId INT NOT NULL,
IssueDate DATE,
Delivered BIT DEFAULT 0

CONSTRAINT FK_Orders_Jobs FOREIGN KEY (JobId) REFERENCES Jobs(JobId)

)

CREATE TABLE Parts(
PartId INT PRIMARY KEY IDENTITY,
SerialNumber VARCHAR(50) NOT NULL UNIQUE,
Description VARCHAR(255),
Price MONEY NOT NULL CHECK (Price > 0 AND Price < 10000),
VendorId INT NOT NULL,
StockQty INT DEFAULT 0  CHECK (StockQty >=0)

CONSTRAINT FK_Parst_Vendors FOREIGN KEY(VendorId) REFERENCES Vendors(VendorId)
)

CREATE TABLE PartsNeeded (
JobId INT NOT NULL,
PartId INT NOT NULL,
Quantity INT DEFAULT 1  CHECK (Quantity >=1)


CONSTRAINT PK_PartsNeeded PRIMARY KEY (JobId,PartId),
CONSTRAINT FK_PartsNeeded_Jobs FOREIGN KEY (JobId) REFERENCES Jobs(JobId),
CONSTRAINT FK_PartsNeeded_Parts FOREIGN KEY (PartId) REFERENCES Parts(PartId)
)

CREATE TABLE OrderParts(
OrderId INT NOT NULL,
PartId INT NOT NULL,
Quantity INT DEFAULT 1  CHECK (Quantity >=1)


CONSTRAINT PK_OrderParts PRIMARY KEY (OrderId,PartId),
CONSTRAINT FK_OrderParts_Orders FOREIGN KEY (OrderId) REFERENCES Orders(OrderId),
CONSTRAINT FK_POrderParts_Parts FOREIGN KEY (PartId) REFERENCES Parts(PartId)


)