create database if not exists valID;

CREATE TABLE `address` (
  `address_id` int(11) NOT NULL auto_increment,
  `address_type_id` char(4) default NULL,
  owner_id int(11),
  `city_id` int(11) default NULL,
  `county_id` int(11) default NULL,
  `state_id` int(11) default NULL,
  `country_id` int(11) default NULL,
  `street_number` varchar(255) default NULL,
  `street_line_01` varchar(255) default NULL,
  `street_line_02` varchar(255) default NULL,
  `street_line_03` varchar(255) default NULL,
  `zipCode` varchar(255) default NULL,
  `lat` float default '0',
  `lon` float default '0',
  PRIMARY KEY  (`address_id`),
  KEY `address_type_id` (`address_type_id`),
  KEY `city_id` (`city_id`),
  KEY `state_id` (`state_id`),
  KEY `county_id` (`county_id`),
  KEY `country_id` (`country_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

-- 
-- Dumping data for table `address`
-- 

--INSERT INTO `address` VALUES (1, 'ooff',1, 5, 7, 0, 223, '1521 1st Ave', 'Seattle', NULL, NULL, '00606', 18.1856, -66.9774);
--INSERT INTO `address` VALUES (2, 'ooff',1, 8, 20, 0, 223, '1301 Alaskan Way', 'Seattle', NULL, NULL, '00610', 18.2859, -67.1442);
--INSERT INTO `address` VALUES (3, 'ooff',1, 6, 30, NULL, 223, '2234 2nd Ave', 'Seattle', NULL, NULL, '00611', 18.2877, -66.7976);
--INSERT INTO `address` VALUES (4, 'ooff',1, 2, 25, NULL, 223, '1416 E Olive Way', 'Seattle', NULL, NULL, '00616', 18.4265, -66.6738);
--INSERT INTO `address` VALUES (5, 'ooff',1, 4, 23, NULL, 223, '1908 Pike pl', 'Seattle', NULL, NULL, '00617', 18.4588, -66.5372);
--INSERT INTO `address` VALUES (8, 'ooff',1, 1, 43, NULL, 223, 'Bidon Street 55', 'Bidon Street', '', '', '00617', 18.4588, -66.5372);

-- --------------------------------------------------------

-- 
-- Table structure for table `address_type`
-- 

CREATE TABLE `address_type` (
  `address_type_id` char(4) NOT NULL,
  `type_name` varchar(255) default NULL,
  PRIMARY KEY  (`address_type_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


-- --------------------------------------------------------

-- 
-- Table structure for table `certificate`
-- 

CREATE TABLE `certificate` (
  `certificate_id` int(11) auto_increment,
  certificate_status_code char(2) default 'gd',
  certificate_type_code char(3),
	serial_number int(11),
  `date_issued` datetime default NULL,
  `valid_until` datetime default NULL,
  `certificate_provider` varchar(255) default NULL,
  `is_valid` char(1) default NULL,
  certificate text,
  PRIMARY KEY  (`certificate_id`),
  KEY `certificate_type_code` (`certificate_type_code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- 
-- Dumping data for table `certificate`
-- 

create table certificate_status_code (
	certificate_status_code char(2),
	certificate_status varchar(255)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


create table certificate_type_code (
	certificate_type_code char(3),
	certificate_type varchar(255),
	PRIMARY KEY(certificate_type_code)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


-- --------------------------------------------------------

-- 
-- Table structure for table `city`
-- 

CREATE TABLE `city` (
  `city_id` int(11) NOT NULL auto_increment,
  `state_id` int(11) default NULL,
  `country_code` int(11) default NULL,
  `city_name` varchar(255) default NULL,
  `city_code` char(1) default NULL,
  `city_dial_code` int(11) default NULL,
  PRIMARY KEY  (`city_id`),
  KEY `state_id` (`state_id`),
  KEY `country_code` (`country_code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

-- 
-- Dumping data for table `city`
-- 

INSERT INTO `city` VALUES (1, 43, 223, 'Holtsville', NULL, NULL);


-- 
-- Table structure for table `country`
-- 

CREATE TABLE `country` (
  `country_id` int(11) NOT NULL auto_increment,
  inet char(2),
  `country_name` varchar(255) NOT NULL default '',
  `country_code` char(3) NOT NULL default '',
  `country_dial_code` int(11) default NULL,
  PRIMARY KEY  (`country_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=240 ;

-- 
-- Dumping data for table `country`
-- 


-- --------------------------------------------------------

-- 
-- Table structure for table `county`
-- 

CREATE TABLE `county` (
  `county_id` int(11) NOT NULL default '0',
  `state_id` int(11) default NULL,
  `county_name` varchar(255) default NULL,
  PRIMARY KEY  (`county_id`),
  KEY `state_id` (`state_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- 
-- Dumping data for table `county`
-- 


-- --------------------------------------------------------

-- 
-- Table structure for table `credential`
-- 

CREATE TABLE `credential` (
  `credential_id` int(11) NOT NULL default '0',
  credential_type_code char (2),
  enrollee_id int(11),
  `address_id` int(11) default NULL,
  `credential_name` varchar(255) default NULL,
  prior_name varchar(255) default NULL,
--  `credntial_description` varchar(255) default NULL,
  `issuing_agency` varchar(255) default NULL,
  `issue_date` date default NULL,
  `valid_until` date default NULL,
  PRIMARY KEY  (`credential_id`),
  KEY `address_id` (`address_id`),
  KEY enrollee_id (enrollee_id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- 
-- Dumping data for table `credential`
-- 

create table credential_type_code (
	credential_type_code char(2),
	credential_type varchar(255),
	primary key (credential_type_code)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------
create table credit_card_type_code (
	credit_card_type_code char(2),
	credit_card_type varchar(255),
	primary key (credit_card_type_code)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
-- 
-- Table structure for table `credit_card`
--

CREATE TABLE `credit_card` (
  `card_id` int(11) NOT NULL auto_increment,
  `card_type_code` char(2) default NULL,
  `card_number` bigint(20) default NULL,
  `expiration_date` date default NULL,
  `name_on_card` varchar(255) default NULL,
--  `issuing_bank` varchar(255) default NULL,
  `security_code` varchar(255) default NULL,
  PRIMARY KEY  (`card_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- 
-- Dumping data for table `credit_card`
-- 

--INSERT INTO `credit_card` VALUES (1, '', 0, '0000-00-00', '', '', '');

-- --------------------------------------------------------

-- 
-- Table structure for table `enrollee`
-- 
create table enrollee_status_code (
	enrollee_status_code char(2),
	enrollee_status varchar(255),
	primary key(enrollee_status_code)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `enrollee` (
	`enrollee_id` int(11) NOT NULL auto_increment,
	certificate_id int(11) default NULL,
	`address_id` int(11) default NULL,
	`city_born` int(11) default NULL,
	`application_date` int(11) default NULL,
	`current_status_code` int(11) NOT NULL default '0',
	enrollee_status_code char(2) default 'nu',
	`first_name` varchar(255) default NULL,
	`middle_name` varchar(255) default NULL,
	`last_name` varchar(255) default NULL,
--	`other_name_01` varchar(255) default NULL,
--	`other_name_02` varchar(255) default NULL,
	`gender` char(1) default NULL,
	`date_of_birth` date default NULL,
	`email` varchar(255) NOT NULL default '',
	`activFlag` int(2) NOT NULL default '0',
	`curDate` timestamp NOT NULL default CURRENT_TIMESTAMP,
	PRIMARY KEY  (`enrollee_id`),
	KEY `address_id` (`address_id`),
	KEY `city_born` (`city_born`),
--	KEY `application_date` (`application_date`),
	KEY `current_status_code` (`current_status_code`),
--	KEY `curDate` (`curDate`),
	KEY certificate_id (certificate_id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

-- 
-- Dumping data for table `enrollee`
-- 
create table alias (
	alias_id int(11) auto_increment,
	enrollee_id int(11),
	alias varchar(255),
	primary key (alias_id),
	key enrollee_id (enrollee_id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--INSERT INTO `enrollee` VALUES (1, 8, 1, NULL, 1, 'Arindam', 'Paul', 'm', NULL, NULL, 'm', '2008-12-26', 'paularindam2@gmail.com', 'arindam007', 0x6172696e64616d303037, 1, '2008-12-14 18:35:47');
-- --------------------------------------------------------

-- 
-- Table structure for table `enrollee_eo`
-- 

CREATE TABLE `enrollee_eo` (
  `enrollee_id` int(11) NOT NULL default '0',
  `eo_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`enrollee_id`,`eo_id`),
  KEY `ENROLLEE_EO_enrollee_id_idxfk` (`enrollee_id`),
  KEY `ENROLLEE_EO_eo_id_idxfk` (`eo_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- 
-- Dumping data for table `enrollee_eo`
-- 

INSERT INTO `enrollee_eo` VALUES (1, 5);
-- --------------------------------------------------------

-- 
-- Table structure for table `enrollment_officer`
-- 

CREATE TABLE `enrollment_officer` (
  `officer_id` int(11) NOT NULL auto_increment,
  enrollee_id int(11) default NULL,
--  `city_born` int(11) default NULL,
--  `application_date` int(11) default NULL,
  `current_status_code` int(11) default NULL,
--  `address_id` int(11) default NULL,
  `first_name` varchar(255) default NULL,
  `last_name` varchar(255) default NULL,
  `middle_name` varchar(255) default NULL,
--  `other_name_01` varchar(255) default NULL,
--  `other_name_02` varchar(255) default NULL,
  `gender` char(1) default NULL,
  `date_of_birth` date default NULL,
  `email` varchar(255) NOT NULL default '',
--  `password` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`officer_id`),
--  KEY `city_born` (`city_born`),
--  KEY `application_date` (`application_date`),
  KEY `current_status_code` (`current_status_code`)
--  KEY `address_id` (`address_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

-- 
-- Dumping data for table `enrollment_officer`
-- 

--INSERT INTO `enrollment_officer` VALUES (1, 0, NULL, NULL, 1, 'Kevin', 'Pietersen', '', NULL, NULL, 'M', '1983-12-01', 'kevin@gmail.com', 'kevin005');
--INSERT INTO `enrollment_officer` VALUES (2, 0, NULL, NULL, 2, 'Tim', 'Ambrose', NULL, NULL, NULL, 'M', '2008-12-01', 'tim@gmail.com', 'ambrose005');
--INSERT INTO `enrollment_officer` VALUES (3, 0, NULL, NULL, 3, 'James', 'Anderson', '', NULL, NULL, 'M', '1983-12-01', 'james@gmail.com', 'james005');
--INSERT INTO `enrollment_officer` VALUES (4, 0, NULL, NULL, 4, 'Steve', 'Harmison', NULL, NULL, NULL, 'M', '2008-12-01', 'steve@gmail.com', 'steve005');
--INSERT INTO `enrollment_officer` VALUES (5, 0, NULL, NULL, 5, 'Stuart', 'Broad', NULL, NULL, NULL, 'M', '2008-12-01', 'stuart@gmail.com', 'stuart005');



-- --------------------------------------------------------

-- 
-- Table structure for table `foundational_info`
-- 

CREATE TABLE `foundational_info` (
  `fi_id` int(11) NOT NULL auto_increment,
  `enrollee_id` int(11) NOT NULL default '0',
  `country_born` int(11) default NULL,
  `credit_card_id` int(11) default NULL,
  `gid_issuing_country` int(11) default NULL,
  `ssn` varchar(255) default NULL,
  `education` varchar(255) default NULL,
  `mothers_m_name` varchar(255) default NULL,
  `fathers_name` varchar(255) default NULL,
  `bank_name` varchar(255) default NULL,
  `driver_lic_number` varchar(255) default NULL,
  `passport_number` varchar(255) default NULL,
  `gov_id_number` varchar(255) default NULL,
  `gid_issue_date` date default NULL,
  `gid_expiry_date` date default NULL,
  `gid_issuing_agency` varchar(255) default NULL,
  PRIMARY KEY  (`fi_id`,`enrollee_id`),
  KEY `FOUNDATIONAL_INFO_enrollee_id_idxfk` (`enrollee_id`),
  KEY `country_born` (`country_born`),
  KEY `credit_card_id` (`credit_card_id`),
  KEY `gid_issuing_country` (`gid_issuing_country`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

-- 
-- Dumping data for table `foundational_info`
-- 

--INSERT INTO `foundational_info` VALUES (1, 1, 223, 1, 223, '12222', 'PG', 'Hra', 'Father', 'SBI', '123456', '12453', 'xcsdsd656', '2008-12-01', '2008-12-03', 'asasasa');


-- --------------------------------------------------------

create table phone_type_code (
	phone_type_code char(4),
	phone_type varchar(255),
	primary key (phone_type_code)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


-- 
-- Table structure for table `phone`
-- 

CREATE TABLE `phone` (
  `phone_id` int(11) NOT NULL auto_increment,
--		enrollee_id, officer_id, or notary_id
  owner_id int(11),
  `phone_type_code` char(4) default NULL,
  `phone_number` bigint(20) default NULL,
  country_code int(11),
  area_code char(4),
  exchange char(4),
  number char(10),
  PRIMARY KEY  (`phone_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

-- 
-- Dumping data for table `phone`
-- 

--INSERT INTO `phone` VALUES (1, 'Land Phone', 25652577);
--INSERT INTO `phone` VALUES (2, 'Mobile Phone', 9433388592);

-- --------------------------------------------------------

-- 
-- Table structure for table `state`
-- 

CREATE TABLE `state` (
  `state_id` int(11) NOT NULL auto_increment,
  `country_id` int(11) default NULL,
  `state_name` varchar(255) default NULL,
  `state_code` char(2) default NULL,
  `state_dial_code` int(11) default NULL,
  PRIMARY KEY  (`state_id`),
  KEY `country_id` (`country_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=66 ;

-- --------------------------------------------------------

-- 
-- Table structure for table `status`
-- 

CREATE TABLE `status` (
  `status_id` int(11) NOT NULL default '0',
  `status` varchar(50) default NULL,
  PRIMARY KEY  (`status_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- 
-- Dumping data for table `status`
-- 

INSERT INTO `status` VALUES (0, 'Pending');
INSERT INTO `status` VALUES (1, 'Approved');
INSERT INTO `status` VALUES (2, 'Needs further documents');
INSERT INTO `status` VALUES (3, 'Not Approved');

-- --------------------------------------------------------

-- 
-- Table structure for table `zip_code`
-- 

CREATE TABLE `zip_code` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `zip_code` varchar(5) character set utf8 collate utf8_bin NOT NULL default '',
  `city` varchar(50) character set utf8 collate utf8_bin default NULL,
  `county` varchar(50) character set utf8 collate utf8_bin default NULL,
  `state_name` varchar(50) character set utf8 collate utf8_bin default NULL,
  `state_prefix` varchar(2) character set utf8 collate utf8_bin default NULL,
  `area_code` varchar(3) character set utf8 collate utf8_bin default NULL,
  `time_zone` varchar(50) character set utf8 collate utf8_bin default NULL,
  `lat` float NOT NULL default '0',
  `lon` float NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `zip_code` (`zip_code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=42625 ;

-- 
-- Dumping data for table `zip_code`
-- 



