CREATE DATABASE PARC_TGI;
Grant all on PARC_TGI.* to "parctgi"@"localhost" identified by "parctgi";

USE PARC_TGI;

Create Table Client
(Client_Code int(10) not null auto_increment primary key,
Client_Name varchar(30),
Address Text,
City varchar(30),
Country varchar(30),
Contact_Preson varchar(30),
Telephone varchar(30),
Mobile varchar(30),
Email varchar(30),
Website varchar(30),
index (Client_Name),
index (City),
index (Country))
ENGINE=MyISAM;

Create Table Market
(Market_Code int(2) not null primary key,
Market_Name varchar(20),
index(Market_Name))
ENGINE=MyISAM;


Create Table Service
(Service_ID int(2) not null auto_increment primary key,
Service_Name Varchar(30),
index(Service_Name))
ENGINE=MyISAM;

Create Table Delivery_Mode
(Delv_Code INT(2)  not null auto_increment primary key,
Delv_Desc varchar(30),
index(Delv_Desc))
ENGINE=MyISAM;

Create Table PO_Header
(PID INT(10)  not null auto_increment primary key,
PO_Num VARCHAR(10),
PO_Date Date,
Client_Code INT(10),
Sales_By INT(10),
Market INT(2),
Service_ID INT(2),
Start_Date Date,
End_Date Date,
PO_Reference VARCHAR(255),
Delivery_Mode INT(2),
UID INT(10),
Created_on DateTime,
Updated_on DateTime,
unique(PO_Num),
index(PO_Date),
index(Client_Code),
index(Sales_By),
index(Market),
index(Service_ID),
index(Start_Date),
index(End_Date),
index(Delivery_Mode),
index(UID))
ENGINE=MyISAM;

Create Table User
(UID int(10)  not null auto_increment primary key,
UserName varchar(30),
Password varchar(255),
index(UserName))
ENGINE=MyISAM;

Create Table UGroup
(GID int(10) not null auto_increment primary key,
GroupName varchar(30),
index(GroupName))
ENGINE=MyISAM;


Create Table User_Group
(UID int(10) not null,
GID int(10) not null,
TeamLeader int(1) default 0,
primary key(UID,GID),
index(UID),
index(GID))
ENGINE=MyISAM;


Create Table PO_Products
(PID int(10)  not null,
Product_Code int(10) not null,
primary key(PID,Product_code),
index(PID),
index(Product_code))
ENGINE=MyISAM;

Create Table PO_License
(PID int(10) not null,
Product_Code int(10),
Client_Location int(3),
User_Name varchar(20),
PC_ID varchar(50),
Activation_Code varchar(50),
Activation_Type int(1),
UID int(10),
primary key(PID,Product_Code),
index(PID),
index(Product_Code),
index(UID))
ENGINE=MyISAM;

Create Table PO_Status
(PID int(10) not null,
Status_Code int(2),
Update_Time datetime,
UID int(10),
primary key(PID,Status_Code))
ENGINE=MyISAM;

Create Table PO_Install_Team
(PID int(10),
Install_User int(10),
Team_Lead int(1),
UID int(10),
primary key(PID,Install_User))
ENGINE=MyISAM;

Create Table Product
(Product_Code int(10)  not null auto_increment primary key,
Description varchar(255),
index(Description))
ENGINE=MyISAM;


Create Table Location
(Location_ID int(3) not null auto_increment primary key,
Location_Name varchar(255),
City varchar(30),
Country varchar(30),
index(Location_Name),
index(City),
index(Country))
ENGINE=MyISAM;


Create Table Status
(Status_Code int(2) not null auto_increment primary key,
Status_Text varchar(25),
index(Status_Text))
ENGINE=MyISAM;