-- phpMyAdmin SQL Dump
-- version 2.11.3deb1ubuntu1.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jan 10, 2009 at 12:53 PM
-- Server version: 5.0.51
-- PHP Version: 5.2.4-2ubuntu5.4

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `tracking`
--

DROP_TABLE IF EXISTS `settings`;
DROP TABLE IF EXISTS `project_session_data`;
DROP TABLE IF EXISTS `project_sessions`;
DROP TABLE IF EXISTS `projects`;
DROP TABLE IF EXISTS `client_contacts`;
DROP TABLE IF EXISTS `clients`;
DROP TABLE IF EXISTS `contact_details`;
DROP TABLE IF EXISTS `copy`;
DROP TABLE IF EXISTS `user_validations`;
DROP TABLE IF EXISTS `users`;
DROP TABLE IF EXISTS `people`;

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

--
-- Table structure for table `clients`
--

CREATE TABLE IF NOT EXISTS `clients` (
  `c_id` mediumint(9) NOT NULL auto_increment,
  `c_name` varchar(128) default NULL,
  PRIMARY KEY  (`c_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `clients`
--

INSERT INTO `clients` (`c_id`, `c_name`) VALUES
(1, 'Evans & Finch Limited'),
(2, 'The Bristol Sash Window Company'),
(3, 'SashWise');

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

--
-- Table structure for table `client_contacts`
--

CREATE TABLE IF NOT EXISTS `client_contacts` (
  `cc_id` mediumint(9) NOT NULL auto_increment,
  `cc_client_id` mediumint(9) NOT NULL,
  `cc_person_id` mediumint(9) NOT NULL,
  `cc_job_title` varchar(128) default NULL,
  PRIMARY KEY  (`cc_id`),
  KEY `cc_client_id` (`cc_client_id`),
  KEY `cc_person_id` (`cc_person_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `client_contacts`
--

INSERT INTO `client_contacts` (`cc_id`, `cc_client_id`, `cc_person_id`, `cc_job_title`) VALUES
(1, 2, 2, 'Managing Director'),
(2, 3, 3, 'Managing Director');

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

--
-- Table structure for table `contact_details`
--

CREATE TABLE IF NOT EXISTS `contact_details` (
  `cd_id` mediumint(9) NOT NULL auto_increment,
  `cd_person_id` mediumint(9) NOT NULL,
  `cd_type` enum('email','skype','mobile','phone','fax') NOT NULL default 'email',
  `cd_detail` varchar(256) NOT NULL,
  PRIMARY KEY  (`cd_id`),
  KEY `cd_person_id` (`cd_person_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

--
-- Dumping data for table `contact_details`
--

INSERT INTO `contact_details` (`cd_id`, `cd_person_id`, `cd_type`, `cd_detail`) VALUES
(1, 1, 'email', 'chris@evansfinch.co.uk'),
(2, 2, 'email', 'graham@bristolsashwindowcompany.com'),
(3, 2, 'mobile', '07980755929'),
(4, 3, 'email', 'dan@sashwise.co.uk'),
(5, 3, 'mobile', '07952203882');

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

--
-- Table structure for table `copy`
--

CREATE TABLE IF NOT EXISTS `copy` (
  `cp_id` mediumint(9) NOT NULL auto_increment,
  `cp_text` varchar(512) default NULL,
  `cp_lang` varchar(12) NOT NULL default '''en-gb''',
  `cp_l18n_key` varchar(64) default NULL,
  PRIMARY KEY  (`cp_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `copy`
--

INSERT INTO `copy` (`cp_id`, `cp_text`, `cp_lang`, `cp_l18n_key`) VALUES
(1, 'Bristol Sash Window Company animated flash website.', 'en-gb', ''),
(2, 'Sashwise animated flash website.', 'en-gb', ''),
(3, 'Prototype game for Dr Frag''s Aim Training', 'en-gb', ''),
(4, 'Evans & Finch placeholder portfolio website.', 'en-gb', '');

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

--
-- Table structure for table `settings`
--

CREATE TABLE IF NOT EXISTS `settings` (
  `s_id` mediumint(9) NOT NULL auto_increment,
  `s_project_id` mediumint(9) NOT NULL,
  `s_section` varchar(64) NOT NULL,
  `s_key` varchar(64) default NULL,
  `s_value` varchar(256) NOT NULL,
  PRIMARY KEY  (`cp_id`),
  KEY (`s_project_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


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

--
-- Table structure for table `people`
--

CREATE TABLE IF NOT EXISTS `people` (
  `p_id` mediumint(9) NOT NULL auto_increment,
  `p_title` varchar(6) NOT NULL,
  `p_forename` varchar(40) NOT NULL,
  `p_surname` varchar(40) NOT NULL,
  PRIMARY KEY  (`p_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

--
-- Dumping data for table `people`
--

INSERT INTO `people` (`p_id`, `p_title`, `p_forename`, `p_surname`) VALUES
(1, 'Mr', 'Chris', 'Keegan'),
(2, 'Mr', 'Graham', 'Davis'),
(3, 'Mr', 'Dan', 'Johnston');

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

--
-- Table structure for table `projects`
--

CREATE TABLE IF NOT EXISTS `projects` (
  `pj_id` mediumint(9) NOT NULL auto_increment,
  `pj_client_id` mediumint(9) NOT NULL,
  `pj_title` varchar(64) default NULL,
  `pj_description_copy_id` mediumint(9) NOT NULL,
  `pj_type` enum('website','game','banner') NOT NULL default 'website',
  PRIMARY KEY  (`pj_id`),
  KEY `pj_description_copy_id` (`pj_description_copy_id`),
  KEY `pj_client_id` (`pj_client_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `projects`
--

INSERT INTO `projects` (`pj_id`, `pj_client_id`, `pj_title`, `pj_description_copy_id`, `pj_type`) VALUES
(1, 2, 'Company Website', 1, 'website'),
(2, 3, 'Company Website', 2, 'website'),
(3, 1, 'Dr Frag Prototype', 3, 'game'),
(4, 1, 'Evans & Finch Portfolio Site', 4, 'website');

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

--
-- Table structure for table `project_sessions`
--

CREATE TABLE IF NOT EXISTS `project_sessions` (
  `pjs_id` mediumint(9) NOT NULL auto_increment,
  `pjs_project_id` mediumint(9) NOT NULL,
  `pjs_referer` varchar(256) default NULL,
  `pjs_ip` varchar(64) default NULL,
  `pjs_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (`pjs_id`),
  KEY `pjs_project_id` (`pjs_project_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Dumping data for table `project_sessions`
--


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

--
-- Table structure for table `project_session_data`
--

CREATE TABLE IF NOT EXISTS `project_session_data` (
  `pjsd_id` mediumint(9) NOT NULL auto_increment,
  `pjsd_session_id` mediumint(9) NOT NULL,
  `pjsd_type` enum('action', 'conversion', 'referer', 'heartbeat') default 'action',
  `pjsd_data` varchar(64) default NULL,
  `pjsd_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (`pjsd_id`),
  KEY `pjsd_session_id` (`pjsd_session_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Dumping data for table `project_session_data`
--


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

--
-- Table structure for table `users`
--

CREATE TABLE IF NOT EXISTS `users` (
  `u_id` mediumint(9) NOT NULL auto_increment,
  `u_username` varchar(16) NOT NULL,
  `u_password` varchar(60) NOT NULL,
  `u_type` enum('guest','client','admin','global_admin') NOT NULL default 'guest',
  `u_person_id` mediumint(9) NOT NULL,
  PRIMARY KEY  (`u_id`),
  KEY `u_person_id` (`u_person_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`u_id`, `u_username`, `u_password`, `u_type`, `u_person_id`) VALUES
(1, 'chris', '1f63a0d0556ae4d64c377b4e715dc72a', 'global_admin', 1);

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

--
-- Table structure for table `user_validations`
--

CREATE TABLE IF NOT EXISTS `user_validations` (
  `uv_id` mediumint(9) NOT NULL auto_increment,
  `uv_user_id` mediumint(9) NOT NULL,
  `uv_validation_hash` varchar(60) NOT NULL,
  `uv_validated` tinyint(1) NOT NULL default '0',
  `uv_validation_date` timestamp NOT NULL default '0000-00-00 00:00:00' on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`uv_id`),
  KEY `uv_user_id` (`uv_user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Dumping data for table `user_validations`
--


--
-- Constraints for dumped tables
--

--
-- Constraints for table `client_contacts`
--
ALTER TABLE `client_contacts`
  ADD CONSTRAINT `client_contacts_ibfk_1` FOREIGN KEY (`cc_client_id`) REFERENCES `clients` (`c_id`),
  ADD CONSTRAINT `client_contacts_ibfk_2` FOREIGN KEY (`cc_person_id`) REFERENCES `people` (`p_id`);

--
-- Constraints for table `contact_details`
--
ALTER TABLE `contact_details`
  ADD CONSTRAINT `contact_details_ibfk_1` FOREIGN KEY (`cd_person_id`) REFERENCES `people` (`p_id`);

--
-- Constraints for table `projects`
--
ALTER TABLE `projects`
  ADD CONSTRAINT `projects_ibfk_1` FOREIGN KEY (`pj_description_copy_id`) REFERENCES `copy` (`cp_id`),
  ADD CONSTRAINT `projects_ibfk_2` FOREIGN KEY (`pj_client_id`) REFERENCES `clients` (`c_id`);

--
-- Constraints for table `project_sessions`
--
ALTER TABLE `project_sessions`
  ADD CONSTRAINT `project_sessions_ibfk_1` FOREIGN KEY (`pjs_project_id`) REFERENCES `projects` (`pj_id`);

--
-- Constraints for table `project_session_data`
--
ALTER TABLE `project_session_data`
  ADD CONSTRAINT `project_session_data_ibfk_1` FOREIGN KEY (`pjsd_session_id`) REFERENCES `project_sessions` (`pjs_id`);

--
-- Constraints for table `users`
--
ALTER TABLE `users`
  ADD CONSTRAINT `users_ibfk_1` FOREIGN KEY (`u_person_id`) REFERENCES `people` (`p_id`);

--
-- Constraints for table `user_validations`
--
ALTER TABLE `user_validations`
  ADD CONSTRAINT `user_validations_ibfk_1` FOREIGN KEY (`uv_user_id`) REFERENCES `users` (`u_id`);