CREATE DATABASE `eqdb`
    CHARACTER SET 'utf8'
    COLLATE 'utf8_general_ci';

#
# Structure for the `eqdb_action` table : 
#

CREATE TABLE `eqdb_action` (
  `Action_ID` int(11) NOT NULL AUTO_INCREMENT,
  `Action_Name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`Action_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;

#
# Structure for the `eqdb_country` table : 
#

CREATE TABLE `eqdb_country` (
  `Country_ID` int(11) NOT NULL AUTO_INCREMENT,
  `Country_Name` varchar(255) NOT NULL,
  PRIMARY KEY (`Country_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

#
# Structure for the `eqdb_city` table : 
#

CREATE TABLE `eqdb_city` (
  `City_ID` int(11) NOT NULL AUTO_INCREMENT,
  `Country_ID` int(11) NOT NULL,
  `City_Name` varchar(255) NOT NULL,
  `Postal_Code` int(11) NOT NULL,
  PRIMARY KEY (`City_ID`),
  KEY `Country_ID` (`Country_ID`),
  CONSTRAINT `eqdb_city_fk` FOREIGN KEY (`Country_ID`) REFERENCES `eqdb_country` (`Country_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

#
# Structure for the `eqdb_customer` table : 
#

CREATE TABLE `eqdb_customer` (
  `Customer_ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(255) NOT NULL,
  `Username` varchar(20) DEFAULT NULL,
  `Password` varchar(20) DEFAULT NULL,
  `Email` varchar(100) DEFAULT NULL,
  `Contact` varchar(255) DEFAULT NULL,
  `Active` tinyint(4) NOT NULL DEFAULT '1',
  PRIMARY KEY (`Customer_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

#
# Structure for the `eqdb_department` table : 
#

CREATE TABLE `eqdb_department` (
  `Department_ID` int(11) NOT NULL AUTO_INCREMENT,
  `Department_Name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`Department_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

#
# Structure for the `eqdb_status` table : 
#

CREATE TABLE `eqdb_status` (
  `Status_ID` int(11) NOT NULL AUTO_INCREMENT,
  `Status_Name` varchar(100) NOT NULL,
  `Status_Color` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`Status_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

#
# Structure for the `eqdb_owner` table : 
#

CREATE TABLE `eqdb_owner` (
  `Owner_ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(255) NOT NULL,
  `Email` varchar(100) DEFAULT NULL,
  `Contact_Phone` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`Owner_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

#
# Structure for the `eqdb_role` table : 
#

CREATE TABLE `eqdb_role` (
  `Role_ID` int(11) NOT NULL AUTO_INCREMENT,
  `Role_Name` varchar(50) NOT NULL,
  PRIMARY KEY (`Role_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

#
# Structure for the `eqdb_user` table : 
#

CREATE TABLE `eqdb_user` (
  `User_ID` int(11) NOT NULL AUTO_INCREMENT,
  `First_Name` varchar(255) NOT NULL,
  `Last_Name` varchar(255) NOT NULL,
  `Short_Name` varchar(20) NOT NULL,
  `Email` varchar(70) NOT NULL,
  `Active` tinyint(4) DEFAULT '0',
  `Sharepoint_ID` int(11) DEFAULT NULL,
  `Role_ID` int(11) NOT NULL,
  PRIMARY KEY (`User_ID`),
  KEY `eqdb_user_fk` (`Role_ID`),
  CONSTRAINT `eqdb_user_fk` FOREIGN KEY (`Role_ID`) REFERENCES `eqdb_role` (`Role_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

#
# Structure for the `eqdb_device` table : 
#

CREATE TABLE `eqdb_device` (
  `Device_ID` int(11) NOT NULL AUTO_INCREMENT,
  `Status_ID` int(11) NOT NULL,
  `Owner_ID` int(11) NOT NULL DEFAULT '1',
  `Customer_ID` int(11) NOT NULL DEFAULT '1',
  `Department_ID` int(11) NOT NULL DEFAULT '1',
  `Product` varchar(255) DEFAULT NULL,
  `Model` varchar(255) NOT NULL,
  `EQ_ID` varchar(50) NOT NULL,
  `Project` varchar(255) DEFAULT NULL,
  `Platform` varchar(255) DEFAULT NULL,
  `SW_Branch` varchar(255) DEFAULT NULL,
  `Vendor` varchar(255) DEFAULT NULL,
  `RM_Number` varchar(255) DEFAULT NULL,
  `HW_ID_MAC` varchar(255) DEFAULT NULL,
  `Serial_Number` varchar(255) DEFAULT NULL,
  `Battery_Type` varchar(255) DEFAULT NULL,
  `Device_References` varchar(255) DEFAULT NULL,
  `HW_Type` varchar(255) DEFAULT NULL,
  `HW_Version` varchar(255) DEFAULT NULL,
  `MEID` varchar(255) DEFAULT NULL,
  `IMEI` varchar(255) DEFAULT NULL,
  `SIM_ID` varchar(255) DEFAULT NULL,
  `PIN` varchar(255) DEFAULT NULL,
  `MDN` varchar(255) DEFAULT NULL,
  `MIN` varchar(255) DEFAULT NULL,
  `ESN` varchar(255) DEFAULT NULL,
  `Approval_To_Send_By_DHL` varchar(255) DEFAULT NULL,
  `Defect` tinyint(4) NOT NULL DEFAULT '0',
  `Note` varchar(255) DEFAULT NULL,
  `CDMA_Provisioning` varchar(255) DEFAULT NULL,
  `Deactivation_Date` bigint(20) DEFAULT NULL,
  `Last_Inventory` timestamp NULL DEFAULT NULL,
  `Last_Inventory_Location` varchar(255) DEFAULT NULL,
  `Inventory` tinyint(4) NOT NULL DEFAULT '0',
  `Assignment` tinyint(4) NOT NULL DEFAULT '0',
  `Who_Added` int(11) NOT NULL DEFAULT '1',
  `Lastchange` timestamp NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  `When_Added` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`Device_ID`),
  UNIQUE KEY `EQ_ID` (`EQ_ID`),
  KEY `eqdb_device_fk1` (`Owner_ID`),
  KEY `Customer_ID` (`Customer_ID`),
  KEY `Who_Added` (`Who_Added`),
  KEY `Department_ID` (`Department_ID`),
  KEY `Status_ID` (`Status_ID`),
  CONSTRAINT `eqdb_device_fk` FOREIGN KEY (`Status_ID`) REFERENCES `eqdb_status` (`Status_ID`),
  CONSTRAINT `eqdb_device_fk1` FOREIGN KEY (`Owner_ID`) REFERENCES `eqdb_owner` (`Owner_ID`),
  CONSTRAINT `eqdb_device_fk2` FOREIGN KEY (`Customer_ID`) REFERENCES `eqdb_customer` (`Customer_ID`),
  CONSTRAINT `eqdb_device_fk3` FOREIGN KEY (`Who_Added`) REFERENCES `eqdb_user` (`User_ID`),
  CONSTRAINT `eqdb_device_fk4` FOREIGN KEY (`Department_ID`) REFERENCES `eqdb_department` (`Department_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=5001 DEFAULT CHARSET=utf8;

#
# Structure for the `eqdb_history` table : 
#

CREATE TABLE `eqdb_history` (
  `History_ID` int(11) NOT NULL AUTO_INCREMENT,
  `Device_ID` int(11) NOT NULL,
  `Action_ID` int(11) NOT NULL,
  `When_Added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `Who_Added` int(11) NOT NULL,
  `Detalis` text,
  PRIMARY KEY (`History_ID`),
  KEY `eqdb_history_fk` (`Action_ID`),
  KEY `Who_Added` (`Who_Added`),
  CONSTRAINT `eqdb_history_fk` FOREIGN KEY (`Action_ID`) REFERENCES `eqdb_action` (`Action_ID`),
  CONSTRAINT `eqdb_history_fk1` FOREIGN KEY (`Who_Added`) REFERENCES `eqdb_user` (`User_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#
# Structure for the `eqdb_login` table : 
#

CREATE TABLE `eqdb_login` (
  `Login_ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(30) NOT NULL,
  PRIMARY KEY (`Login_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

#
# Structure for the `eqdb_reservation` table : 
#

CREATE TABLE `eqdb_reservation` (
  `Reservation_ID` int(11) NOT NULL AUTO_INCREMENT,
  `Device_ID` int(11) NOT NULL,
  `Reserved_For` varchar(10) NOT NULL,
  `Reservation_Start` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `Reservation_End` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `When_Added` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `Who_Added` varchar(10) NOT NULL,
  PRIMARY KEY (`Reservation_ID`),
  KEY `eqdb_reservation_fk` (`Device_ID`),
  CONSTRAINT `eqdb_reservation_fk` FOREIGN KEY (`Device_ID`) REFERENCES `eqdb_device` (`Device_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#
# Structure for the `eqdb_tracking` table : 
#

CREATE TABLE `eqdb_tracking` (
  `Tracking_ID` int(11) NOT NULL AUTO_INCREMENT,
  `Device_ID` int(11) NOT NULL DEFAULT '1',
  `Previous_Location` varchar(255) DEFAULT NULL,
  `Current_Location` varchar(255) NOT NULL,
  `Current_Responsible` varchar(20) NOT NULL,
  `Current_Assignment_Comment` varchar(255) DEFAULT NULL,
  `Next_Expected_Location` varchar(255) DEFAULT NULL,
  `Next_Assigned_Responsible` varchar(20) DEFAULT NULL,
  `Next_Assignment_Comment` varchar(255) DEFAULT NULL,
  `Date_Of_Expected_Arrival` timestamp NULL DEFAULT NULL,
  `Transfer` varchar(255) DEFAULT NULL,
  `Status` tinyint(4) NOT NULL DEFAULT '1' COMMENT '0 - not active, 1 - active',
  `Date_Of_Lastchange` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`Tracking_ID`),
  KEY `Current_Responsible` (`Current_Responsible`),
  KEY `Next_Assigned_Responsible` (`Next_Assigned_Responsible`),
  KEY `Device_ID` (`Device_ID`),
  CONSTRAINT `eqdb_tracking_fk1` FOREIGN KEY (`Device_ID`) REFERENCES `eqdb_device` (`Device_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=5001 DEFAULT CHARSET=utf8;

#
# Structure for the `eqdb_user_department` table : 
#

CREATE TABLE `eqdb_user_department` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `User_ID` int(11) NOT NULL,
  `Department_ID` int(11) NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `User_ID` (`User_ID`),
  KEY `Department_ID` (`Department_ID`),
  CONSTRAINT `eqdb_user_department_fk` FOREIGN KEY (`User_ID`) REFERENCES `eqdb_user` (`User_ID`),
  CONSTRAINT `eqdb_user_department_fk1` FOREIGN KEY (`Department_ID`) REFERENCES `eqdb_department` (`Department_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

#
# Data for the `eqdb_action` table  (LIMIT 0,500)
#

INSERT INTO `eqdb_action` (`Action_ID`, `Action_Name`) VALUES 
  (1,'device added'),
  (2,'device deleted'),
  (3,'device data edited'),
  (4,'device returned'),
  (5,'device imported'),
  (6,'device reserved'),
  (7,'device reservation cleared'),
  (8,'device assigned'),
  (9,'device assignment cleared'),
  (10,'device picked up'),
  (11,'device set as Arived'),
  (12,'device set as Missing'),
  (13,'device defect set'),
  (14,'defect defect cleared'),
  (15,'alarm activated');

COMMIT;

#
# Data for the `eqdb_country` table  (LIMIT 0,500)
#

INSERT INTO `eqdb_country` (`Country_ID`, `Country_Name`) VALUES 
  (1,'Germany'),
  (2,'Serbia'),
  (3,'Italy'),
  (4,'France');

COMMIT;

#
# Data for the `eqdb_city` table  (LIMIT 0,500)
#

INSERT INTO `eqdb_city` (`City_ID`, `Country_ID`, `City_Name`, `Postal_Code`) VALUES 
  (6,1,'Aachen',23456),
  (7,1,'Ulm',34567),
  (8,2,'Belgrade',23456),
  (9,3,'Rome',23456),
  (10,4,'Paris',23456);

COMMIT;

#
# Data for the `eqdb_customer` table  (LIMIT 0,500)
#

INSERT INTO `eqdb_customer` (`Customer_ID`, `Name`, `Username`, `Password`, `Email`, `Contact`, `Active`) VALUES 
  (1,'Version Wireles','version','version','version@wireles.com','0034565678899',1);

COMMIT;

#
# Data for the `eqdb_department` table  (LIMIT 0,500)
#

INSERT INTO `eqdb_department` (`Department_ID`, `Department_Name`) VALUES 
  (1,'depTT'),
  (2,'depDT');

COMMIT;

#
# Data for the `eqdb_role` table  (LIMIT 0,500)
#

INSERT INTO `eqdb_role` (`Role_ID`, `Role_Name`) VALUES 
  (1,'client'),
  (2,'admin'),
  (3,'superadmin');

COMMIT;

#
# Data for the `eqdb_user` table  (LIMIT 0,500)
#

INSERT INTO `eqdb_user` (`User_ID`, `First_Name`, `Last_Name`, `Short_Name`, `Email`, `Active`, `Sharepoint_ID`, `Role_ID`) VALUES 
  (1,'Miodrag','Lapcevic','MIO','miodraglapcevic@gmail.com',1,230,3);

COMMIT;

#
# Data for the `eqdb_login` table  (LIMIT 0,500)
#

INSERT INTO `eqdb_login` (`Login_ID`, `Name`) VALUES 
  (1,'Show Full View'),
  (2,'Pickup'),
  (3,'Arrival'),
  (4,'Back From FT');

COMMIT;

#
# Data for the `eqdb_owner` table  (LIMIT 0,500)
#

INSERT INTO `eqdb_owner` (`Owner_ID`, `Name`, `Email`, `Contact_Phone`) VALUES 
  (1,'RIM','RIM','RIM');

COMMIT;

#
# Data for the `eqdb_status` table  (LIMIT 0,500)
#

INSERT INTO `eqdb_status` (`Status_ID`, `Status_Name`, `Status_Color`) VALUES 
  (1,'available','green'),
  (2,'in-transit','gray'),
  (3,'missing','red'),
  (4,'returned','light-gray'),
  (5,'not in db','orange');

COMMIT;

#
# Data for the `eqdb_user_department` table  (LIMIT 0,500)
#

INSERT INTO `eqdb_user_department` (`ID`, `User_ID`, `Department_ID`) VALUES 
  (1,1,1),
  (2,1,2);

COMMIT;