CREATE TABLE TerritoryDescription(
	TerritoryID INT PRIMARY KEY,
	TerritoryDescription VARCHAR(50) NOT NULL
);

CREATE TABLE Technician(
	EmployeeID INT PRIMARY KEY,
	LastName VARCHAR(20) NOT NULL,
	FirstName VARCHAR(20) NOT NULL,
	LocationID INT NOT NULL,
	Address VARCHAR(100) NOT NULL,
	City VARCHAR(30) NOT NULL,
	PostalCode VARCHAR(15) NOT NULL,
	MobilePhone VARCHAR(10),
	Email VARCHAR(30) NOT NULL,
	CONSTRAINT fk_TechLoc FOREIGN KEY(LocationID)
		REFERENCES TerritoryDescription(TerritoryID)
);

CREATE TABLE TechnicianTerritory(
	EmployeeID INT NOT NULL,
	TerritoryID INT NOT NULL,
	CONSTRAINT pk_TechTtry PRIMARY KEY(EmployeeID, TerritoryID),
	CONSTRAINT fk_TechTryTech FOREIGN KEY(EmployeeID)
		REFERENCES Technician(EmployeeID),
	CONSTRAINT fk_TechTryTry FOREIGN KEY(TerritoryID)
		REFERENCES TerritoryDescription(TerritoryID)
);

CREATE TABLE Employee(
	EmployeeID INT PRIMARY KEY,
	LocationID INT NOT NULL,
	Address VARCHAR(50) NOT NULL,
	City VARCHAR(30) NOT NULL,
	ZipCode VARCHAR(15) NOT NULL,
	Phone VARCHAR(10) NOT NULL,
	Email VARCHAR(30) NOT NULL,
	CONSTRAINT fk_EmpLoc FOREIGN KEY(LocationID)
		REFERENCES TerritoryDescription(TerritoryID)
);

CREATE TABLE WorkTicket(
	TicketID INT PRIMARY KEY,
	EmployeeID INT NOT NULL,
	TechnicianID INT NOT NULL,
	Address VARCHAR(50) NOT NULL,
	City VARCHAR(30) NOT NULL,
	Region VARCHAR(20),
	PostalCode VARCHAR(15) NOT NULL,
	Phone VARCHAR(15) NOT NULL,
	Email VARCHAR(30) NOT NULL,
	CONSTRAINT fk_TktEmp FOREIGN KEY(EmployeeID)
		REFERENCES Employee(EmployeeID),
	CONSTRAINT fk_TktTech FOREIGN KEY(TechnicianID)
		REFERENCES Technician(EmployeeID)
);

CREATE TABLE Materials(
	PartID INT PRIMARY KEY,
	PartName VARCHAR(30) NOT NULL,
	Qty INT NOT NULL,
	Cost NUMERIC(5, 2) NOT NULL
);

CREATE TABLE TicketDetails(
	TicketID INT NOT NULL,
	PartID INT NOT NULL,
	WorkDetail VARCHAR(100),
	CONSTRAINT pk_TktDtl PRIMARY KEY(TicketID, PartID),
	CONSTRAINT fk_TktDtlTkt FOREIGN KEY(TicketID)
		REFERENCES WorkTicket(TicketID),
	CONSTRAINT fk_TktDtlMtrl FOREIGN KEY(PartID)
		REFERENCES Materials(PartID)
);