USE parc_tgi;

CREATE TABLE po_header (
  PID int(10) NOT NULL auto_increment,
  PO_Num varchar(10) default NULL,
  PO_Date date default NULL,
  Client_Code int(10) default NULL,
  Sales_By int(10) default NULL,
  Market int(2) default NULL,
  Service_ID int(2) default NULL,
  Start_Date date default NULL,
  End_Date date default NULL,
  PO_Reference varchar(255) default NULL,
  Delivery_Mode int(2) default NULL,
  UID int(10) default NULL,
  Created_on datetime default NULL,
  Updated_on datetime default NULL,
  Active tinyint(1) unsigned default '1',
  PRIMARY KEY  (PID),
  UNIQUE KEY PO_Num (PO_Num),
  KEY PO_Date (PO_Date),
  KEY Client_Code (Client_Code),
  KEY Sales_By (Sales_By),
  KEY Market (Market),
  KEY Service_ID (Service_ID),
  KEY Start_Date (Start_Date),
  KEY End_Date (End_Date),
  KEY Delivery_Mode (Delivery_Mode),
  KEY UID (UID)
) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;


CREATE TABLE po_install_team (
  PID int(10) NOT NULL default '0',
  Install_User int(10) NOT NULL default '0',
  Team_Lead int(1) default NULL,
  UID int(10) default NULL,
  PRIMARY KEY  (PID,Install_User)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


CREATE TABLE po_license (
  PID int(10) NOT NULL,
  Product_Code int(10) NOT NULL default '0',
  Assign_To int(10) unsigned default NULL,
  Installed_By int(10) unsigned default NULL,
  Client_Location int(3) NOT NULL default '0',
  User_Name varchar(20) NOT NULL default '',
  PC_ID varchar(50) default NULL,
  SActivation_Code varchar(50) default NULL,
  DActivation_Code varchar(50) default NULL,
  Installed_On date default NULL,
  PRIMARY KEY  (PID,Product_Code,User_Name,Client_Location),
  KEY PID (PID),
  KEY Product_Code (Product_Code)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE po_products (
  PID int(10) NOT NULL,
  Product_Code int(10) NOT NULL,
  Remarks varchar(255) default NULL,
  PRIMARY KEY  (PID,Product_Code),
  KEY PID (PID),
  KEY Product_Code (Product_Code)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


CREATE TABLE po_status (
  PID int(10) NOT NULL,
  Status_Code int(2) NOT NULL default '0',
  Update_Time datetime default NULL,
  UID int(10) default NULL,
  Remark varchar(255) default NULL,
  PRIMARY KEY  (PID,Status_Code),
  KEY NewIndex (PID,Status_Code),
  KEY pid (PID)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE tbl_client (
  Client_Code int(10) NOT NULL auto_increment,
  Client_Name varchar(30) default NULL,
  Address text,
  City varchar(30) default NULL,
  Country varchar(30) default NULL,
  Contact_Person varchar(30) default NULL,
  Telephone varchar(30) default NULL,
  Mobile varchar(30) default NULL,
  Email varchar(255) default NULL,
  Website varchar(255) default NULL,
  PRIMARY KEY  (Client_Code),
  KEY Client_Name (Client_Name),
  KEY City (City),
  KEY Country (Country)
) ENGINE=MyISAM AUTO_INCREMENT=15 DEFAULT CHARSET=latin1;

CREATE TABLE tbl_delivery_mode (
  Delv_Code int(2) NOT NULL auto_increment,
  Delv_Desc varchar(30) default NULL,
  PRIMARY KEY  (Delv_Code),
  KEY Delv_Desc (Delv_Desc)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

INSERT INTO tbl_delivery_mode (Delv_Code,Delv_Desc) VALUES 
 (1,'By Email');

CREATE TABLE tbl_location (
  Location_ID int(3) NOT NULL auto_increment,
  Location_Name varchar(255) default NULL,
  City varchar(30) default NULL,
  Country varchar(30) default NULL,
  PRIMARY KEY  (Location_ID),
  KEY Location_Name (Location_Name),
  KEY City (City),
  KEY Country (Country)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

CREATE TABLE tbl_market (
  Market_Code int(2) NOT NULL auto_increment,
  Market_Name varchar(20) default NULL,
  PRIMARY KEY  (Market_Code),
  KEY Market_Name (Market_Name)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;

INSERT INTO tbl_market (Market_Code,Market_Name) VALUES 
 (1,'Pan Arab'),
 (2,'Kuwait'),
 (3,'UAE'),
 (4,'Qatar'),
 (5,'Egypt'),
 (6,'Saudi'),
 (7,'Oman');

CREATE TABLE tbl_product (
  Product_Code int(10) NOT NULL auto_increment,
  Product_Desc varchar(255) default NULL,
  Market_Code int(10) unsigned default NULL,
  PRIMARY KEY  (Product_Code),
  KEY Description (Product_Desc),
  KEY NewIndex (Market_Code)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

CREATE TABLE tbl_service (
  Service_ID int(2) NOT NULL auto_increment,
  Service_Name varchar(30) default NULL,
  PRIMARY KEY  (Service_ID),
  KEY Service_Name (Service_Name)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

INSERT INTO tbl_service (Service_ID,Service_Name) VALUES 
 (1,'TGI'),
 (2,'Adhoc - TGI');


CREATE TABLE tbl_status (
  Status_Code int(2) NOT NULL auto_increment,
  Status_Text varchar(25) default NULL,
  PRIMARY KEY  (Status_Code),
  KEY Status_Text (Status_Text)
) ENGINE=MyISAM AUTO_INCREMENT=100 DEFAULT CHARSET=latin1;

INSERT INTO tbl_status (Status_Code,Status_Text) VALUES 
 (1,'New'),
 (2,'Updated by Marketing Dept'),
 (3,'Scheduled for Installtion'),
 (4,'Installation Started'),
 (5,'Installation Finished'),
 (99,'Canceled');

CREATE TABLE tbl_ugroup (
  GID int(10) NOT NULL auto_increment,
  GroupName varchar(30) default NULL,
  PRIMARY KEY  (GID),
  KEY GroupName (GroupName)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

INSERT INTO tbl_ugroup (GID,GroupName) VALUES 
 (1,'Admin'),
 (2,'Accounts'),
 (3,'Marketing'),
 (4,'Installation');

CREATE TABLE tbl_user (
  UID int(10) NOT NULL auto_increment,
  UserName varchar(30) default NULL,
  Password varchar(255) default NULL,
  UserFullName varchar(50) default NULL,
  Activate tinyint(1) unsigned NOT NULL default '1',
  email varchar(100) default NULL,
  PRIMARY KEY  (UID),
  KEY UserName (UserName)
) ENGINE=MyISAM AUTO_INCREMENT=18 DEFAULT CHARSET=latin1;

CREATE TABLE tbl_user_group (
  UID int(10) NOT NULL,
  GID int(10) NOT NULL,
  TeamLeader int(1) default '0',
  PRIMARY KEY  (UID,GID),
  KEY UID (UID),
  KEY GID (GID)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

