/* SVN FILE: $Id:$ */
/**
 * eShopStart.
 *
 * Database Table Schema.
 *
 * MySQL Version 5
 *
 * eShopStart
 * Copyright (c)  2006, eShopStart.
 *                      1 St James Square, Hoyland,
 *                      Barnsley S74 9AA
 *
 * Redistribution of this file is strictly prohibited.
 *
 * @filesource
 * @copyright     Copyright (c) 2006, eShopStart.
 * @link          http://eshopstart.com eShopStart
 * @package       eShopStart
 * @subpackage
 * @since         eShopStart v 2.0.0
 * @version       $Revision:$
 * @modifiedby    $LastChangedBy:$
 * @lastmodified  $LastChangedDate:$
 * @license       http://eshopstart.com/licenses/license.php License
 */


/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

CREATE database IF NOT EXISTS `eshopstart`;
USE `eshopstart`;

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;



/*
  -----------------------------------------------------------------------
    System Tables.
  -----------------------------------------------------------------------
*/
/* Config table
 * holds system configurations and defaults */
DROP TABLE IF EXISTS `configs`;
CREATE TABLE `configs` (
    `id` smallint(5) unsigned NOT NULL auto_increment,
    `name` varchar(255) NOT NULL default '',
    `value` varchar(255) NOT NULL default '',
    PRIMARY KEY  (`id`),
    UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/* Sessions table */
DROP TABLE IF EXISTS `sessions`;
CREATE TABLE `sessions` (
	`id` varchar(255) NOT NULL default '',
	`data` text,
	`expires` int(11) unsigned default NULL,
	PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



/*
  -----------------------------------------------------------------------
    Locale tables.
  -----------------------------------------------------------------------
*/
/* Table structure for table `countries` */
DROP TABLE IF EXISTS `countries`;
CREATE TABLE `countries` (
	`id` int(3) unsigned NOT NULL auto_increment,
	`iso` char(2) NOT NULL,
	`name` varchar(80) NOT NULL,
	`printable_name` varchar(80) NOT NULL,
	`iso3` char(3) default NULL,
	`numcode` smallint(6) default NULL,
	PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/* Table structure for table `currencies` */
DROP TABLE IF EXISTS `currencies`;
CREATE TABLE `currencies` (
	`id` int(11) unsigned NOT NULL auto_increment,
	`country_id` int(3) unsigned NOT NULL,
	`base_rate` float(4,2) default NULL,
	`value` varchar(200) default NULL,
	PRIMARY KEY  (`id`),
	KEY `FK_currency__country` (`country_id`),
	CONSTRAINT `currencies_ibfk_1` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



/*
  -----------------------------------------------------------------------
    Client tables.
  -----------------------------------------------------------------------
*/
/*Table structure for table `clients` */
DROP TABLE IF EXISTS `clients`;
CREATE TABLE `clients` (
	`id` int(10) unsigned NOT NULL auto_increment,
	`name` varchar(255) default NULL,
	`no_of_licence_seats` int(3) unsigned default NULL,
	`contact_name` varchar(255) default NULL,
	`phone_number` varchar(255) default NULL,
	`email_address` varchar(255) default NULL,
	`enabled` tinyint(1) unsigned default NULL,
	PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/* Table structure for table `client_configs` */
DROP TABLE IF EXISTS `client_configs`;
CREATE TABLE `client_configs` (
	`id` int(11) unsigned NOT NULL auto_increment,
	`client_id` int(11) unsigned NOT NULL,
	`variable` varchar(255) default NULL,
	`value` varchar(255) default NULL,
	PRIMARY KEY  (`id`),
	KEY `FK_client_config__client` (`client_id`),
	CONSTRAINT `client_configs_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/* Table structure for table `sites` */
DROP TABLE IF EXISTS `sites`;
CREATE TABLE `sites` (
	`id` int(11) unsigned NOT NULL auto_increment,
	`client_id` int(11) unsigned NOT NULL,
	`name` varchar(255) NOT NULL default '',
	`theme` varchar(255) NOT NULL default '',
	`url` varchar(255) NOT NULL default '',
	PRIMARY KEY  (`id`),
	UNIQUE KEY `url` (`url`),
	KEY `FK_site__client` (`client_id`),
	CONSTRAINT `sites_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



/*
  -----------------------------------------------------------------------
    User tables.
  -----------------------------------------------------------------------
*/
/* Users table */
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
	`id` int(11) unsigned NOT NULL auto_increment,
	`username` varchar(30) NOT NULL,
	`first_name` varchar(255) NOT NULL,
	`last_name` varchar(255) NOT NULL,
	`password` char(32) NOT NULL,
	`home_address_id` int(11) unsigned default NULL,
	`work_address_id` int(11) unsigned default NULL,
	`last_login` datetime  NOT NULL default '0000-00-00 00:00:00',
	`last_log_ip` varchar(15) default NULL,
	`avatar` varchar(150) default '',
	`email` varchar(255) default '' COMMENT 'primary email addr for password retrieval',
	`sig` text default '',
	`occupation` varchar(100) default '',
	`enabled` tinyint(1) unsigned default '1',
	`confirmed` tinyint(1) unsigned default '0',
	`confirm_code` varchar(32) default '0',
	`created` datetime NOT NULL default '0000-00-00 00:00:00',
	PRIMARY KEY  (`id`),
	KEY `FK_user__home_address` (`home_address_id`),
	KEY `FK_user__work_address` (`work_address_id`),
	CONSTRAINT `users_ibfk_1` FOREIGN KEY (`home_address_id`) REFERENCES `addresses` (`id`),
	CONSTRAINT `users_ibfk_2` FOREIGN KEY (`work_address_id`) REFERENCES `addresses` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/* User prefs table */
DROP TABLE IF EXISTS `user_prefs`;
CREATE TABLE user_prefs (
    `id` int(11) unsigned NOT NULL auto_increment,
    `user_id` int(11) unsigned NOT NULL,
    `name` varchar(255) NOT NULL,
    `value` varchar(255) NOT NULL,
    PRIMARY KEY  (`id`),
	KEY `FK_user_pref__user` (`user_id`),
	CONSTRAINT `user_prefs_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/* User Ranks table */
DROP TABLE IF EXISTS `user_ranks`;
CREATE TABLE `user_ranks` (
	`id` int(11) unsigned NOT NULL auto_increment,
    `user_id` int(11) unsigned NOT NULL,
    `rank_id` smallint(3) unsigned NOT NULL,
    PRIMARY KEY (`id`),
	KEY `FK_user_rank__user` (`user_id`),
	KEY `FK_user_rank__rank` (`rank_id`),
	CONSTRAINT `user_ranks_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
	CONSTRAINT `user_ranks_ibfk_2` FOREIGN KEY (`rank_id`) REFERENCES `ranks` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/* Rank details table */
DROP TABLE IF EXISTS `ranks`;
CREATE TABLE `ranks` (
    `id` smallint(3) unsigned NOT NULL auto_increment,
    `posts` smallint(5) unsigned NOT NULL COMMENT 'number of post required to reach rank automatically',
    `name` varchar(40) NOT NULL,
    `graphic` varchar(100) default NULL COMMENT 'filename of graphic associated with rank',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/* Group details table */
DROP TABLE IF EXISTS `groups`;
CREATE TABLE `groups` (
    `id` mediumint(8) unsigned NOT NULL auto_increment,
    `name` varchar(255) NOT NULL default '',
    `description` varchar(255) NOT NULL default '',
    `graphic` varchar(255) default NULL COMMENT 'filename of graphic associated with group',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/* User/Groups membership table */
DROP TABLE IF EXISTS `user_groups`;
CREATE TABLE `user_groups` (
	`id` int(11) unsigned NOT NULL auto_increment,
    `user_id` int(11) unsigned NOT NULL,
    `group_id` mediumint(8) unsigned NOT NULL,
    PRIMARY KEY (`id`),
	KEY `FK_user_group__user` (`user_id`),
	KEY `FK_user_group__group` (`group_id`),
	CONSTRAINT `user_groups_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
	CONSTRAINT `user_groups_ibfk_2` FOREIGN KEY (`group_id`) REFERENCES `groups` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/* Table structure for table `addresses` */
DROP TABLE IF EXISTS `addresses`;
CREATE TABLE `addresses` (
	`id` int(10) unsigned NOT NULL auto_increment,
	`user_id` int(10) unsigned NOT NULL COMMENT 'User ID',
	`addr1` varchar(255) default NULL,
	`addr2` varchar(255) default NULL,
	`addr3` varchar(255) default NULL,
	`addr4` varchar(255) default NULL,
	`addr5` varchar(255) default NULL,
	`city` varchar(255) default NULL,
	`state` varchar(255) default NULL,
	`country_id` int(3) unsigned NOT NULL,
	`postcode` varchar(100) default NULL,
	`tel` varchar(255) default NULL,
	`tel2` varchar(255) default NULL,
	`tel3` varchar(255) default NULL,
	`mobile` varchar(255) default NULL,
	`mobile2` varchar(255) default NULL,
	`fax` varchar(255) default NULL,
	`fax2` varchar(255) default NULL,
	`email` varchar(255) default NULL,
	`email2` varchar(255) default NULL,
	`web` varchar(255) default NULL,
	`web2` varchar(255) default NULL,
	`msn` varchar(255) default NULL,
	`icq` varchar(15) default NULL,
	`skype` varchar(255) default NULL,
	`longitude` varchar(10) default NULL COMMENT 'Geocode longitude coord',
	`latitude` varchar(10) default NULL COMMENT 'Geocode latitude coord',
	`created` timestamp NULL default NULL,
	`modified` timestamp NULL default NULL,
	PRIMARY KEY  (`id`),
	KEY `FK_address__user` (`user_id`),
	KEY `FK_address__country` (`country_id`),
	CONSTRAINT `addresses_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
	CONSTRAINT `addresses_ibfk_2` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/* Add the superadmin group */
INSERT INTO `groups` (`id`,`name`,`description`) VALUES ('1','superadmins','Site Administrators Group');
/* Add the guest user */
INSERT INTO `users` (`id`,`username`,`password`,`first_name`,`last_name`) VALUES ('1','guest',md5('guest'),'Guest','User');



/*
  -----------------------------------------------------------------------
    User Privileges and Access Control tables.
  -----------------------------------------------------------------------
*/
/* Access Controls */
DROP TABLE IF EXISTS `acos`;
CREATE TABLE `acos` (
	`id` int(10) unsigned NOT NULL auto_increment,
	`model` varchar(255) NOT NULL default '',
	`object_id` int(10) unsigned NULL default NULL,
	`alias` varchar(255) NOT NULL default '',
	`lft` int(10) NULL default NULL,
	`rght` int(10) NULL default NULL,
	PRIMARY KEY(id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `aros_acos`;
CREATE TABLE `aros_acos` (
	`id` int(10) unsigned NOT NULL auto_increment,
	`aro_id` int(10) unsigned NOT NULL,
	`aco_id` int(10) unsigned NOT NULL,
	`_create` tinyint(1) NOT NULL DEFAULT 0,
	`_read` tinyint(1) NOT NULL DEFAULT 0,
	`_update` tinyint(1) NOT NULL DEFAULT 0,
	`_delete` tinyint(1) NOT NULL DEFAULT 0,
	PRIMARY KEY(id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `aros`;
CREATE TABLE `aros` (
	`id` int(10) unsigned NOT NULL auto_increment,
	`model` varchar(255) NOT NULL DEFAULT '',
	`foreign_key` int(10) unsigned NULL DEFAULT NULL,
	`alias` varchar(255) NOT NULL DEFAULT '',
	`lft` int(10) NULL DEFAULT NULL,
	`rght` int(10) NULL DEFAULT NULL,
	PRIMARY KEY(id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;




/*
  -----------------------------------------------------------------------
    Images and Image Cache Tables.
  -----------------------------------------------------------------------
*/
/* Images table */
DROP TABLE IF EXISTS `images`;
CREATE TABLE `images` (
	`id` int(11) unsigned NOT NULL auto_increment,
	`client_id` int(11) unsigned NOT NULL,
	`user_id` int(11) unsigned NOT NULL,
	`filename` varchar(100) NOT NULL default '',
	`type` char(3) NOT NULL default '',
	`title` varchar(255) default NULL,
	`description` text default NULL,
	`created` datetime NOT NULL default '0000-00-00 00:00:00',
	PRIMARY KEY  (`id`),
	KEY `FK_image__client` (`client_id`),
	KEY `FK_image__user` (`user_id`),
	CONSTRAINT `images_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`),
	CONSTRAINT `images_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/* Image Cache table */
DROP TABLE IF EXISTS `cached_images`;
CREATE TABLE `cached_images` (
	`id` int(11) unsigned NOT NULL auto_increment,
	`cache_code` varchar(36) NOT NULL,
	`image_id` int(11) unsigned NOT NULL,
	`scale` varchar(10) default NULL,
	`x_dim` smallint(4) unsigned NOT NULL default '0',
	`y_dim` smallint(4) unsigned NOT NULL default '0',
	`created` datetime NOT NULL default '0000-00-00 00:00:00',
	PRIMARY KEY  (`id`),
	KEY `FK_cached_image__image` (`image_id`),
  CONSTRAINT `cached_images_ibfk_1` FOREIGN KEY (`image_id`) REFERENCES `images` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



/*
  -----------------------------------------------------------------------
    File Tables.
  -----------------------------------------------------------------------
*/
/* file categories table */
DROP TABLE IF EXISTS `file_categories`;
CREATE TABLE `file_categories` (
    `id` int(5) unsigned NOT NULL auto_increment,
    `client_id` int(11) unsigned NOT NULL,
    `user_id` int(11) unsigned NOT NULL,
    `name` varchar(255) default NULL,
    `title` varchar(100) default NULL,
    `description` varchar(255) default NULL,
    `created` datetime NOT NULL default '0000-00-00 00:00:00',
    PRIMARY KEY  (`id`),
	KEY `FK_file_category__client` (`client_id`),
	KEY `FK_file_category__user` (`user_id`),
	CONSTRAINT `file_categories_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`),
	CONSTRAINT `file_categories_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/* file categories table */
DROP TABLE IF EXISTS `files`;
CREATE TABLE `files` (
    `id` int(5) unsigned NOT NULL auto_increment,
    `client_id` int(11) unsigned NOT NULL,
    `user_id` int(11) unsigned NOT NULL,
    `file_category_id` int(11) unsigned NOT NULL,
    `name` varchar(255) default NULL COMMENT 'filename of the file',
    `path` varchar(100) default NULL COMMENT 'path to file',
    `title` varchar(255) default NULL,
    `description` varchar(255) default NULL,
    `created` datetime NOT NULL default '0000-00-00 00:00:00',
    PRIMARY KEY  (`id`),
	KEY `FK_file__client` (`client_id`),
	KEY `FK_file__user` (`user_id`),
	KEY `FK_file__file_category` (`user_id`),
	CONSTRAINT `files_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`),
	CONSTRAINT `files_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
	CONSTRAINT `files_ibfk_3` FOREIGN KEY (`file_category_id`) REFERENCES `file_categories` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



/*
  -----------------------------------------------------------------------
    Website Tables - Website Site Content.
  -----------------------------------------------------------------------
*/
/* Page categories table */
DROP TABLE IF EXISTS `page_categories`;
CREATE TABLE `page_categories` (
    `id` smallint(5) unsigned NOT NULL auto_increment,
    `client_id` int(11) unsigned NOT NULL,
    `user_id` int(11) unsigned NOT NULL,
    `name` varchar(100) default NULL,
    `title` varchar(100) default NULL,
    `description` varchar(255) default NULL,
    `created` datetime NOT NULL default '0000-00-00 00:00:00',
    `modified` datetime NOT NULL default '0000-00-00 00:00:00',
    PRIMARY KEY  (`id`),
	KEY `FK_page_category__client` (`client_id`),
	KEY `FK_page_category__user` (`user_id`),
	CONSTRAINT `page_categories_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`),
	CONSTRAINT `page_categories_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/* Pages table */
DROP TABLE IF EXISTS `pages`;
CREATE TABLE `pages` (
    `id` int(11) unsigned NOT NULL auto_increment,
	`page_category_id` smallint(5) unsigned NOT NULL,
    `parentid` int(11) unsigned NOT NULL COMMENT 'id of the parent',
    `user_id` int(11) unsigned NOT NULL,
    `name` varchar(255) default NULL,
    `title` varchar(255) default NULL,
    `url` varchar(100) default NULL,
    `description` varchar(255) default NULL,
    `keywords` varchar(255) default NULL,
    `content` blob default NULL,
    `allow_edit` tinyint(1) unsigned default '0',
    `allow_comment` tinyint(1) unsigned default '0',
    `enabled` tinyint(1) unsigned default '1',
    `created` datetime NOT NULL default '0000-00-00 00:00:00',
    `modified` datetime NOT NULL default '0000-00-00 00:00:00',
    PRIMARY KEY  (`id`),
	KEY `FK_page__page_category` (`page_category_id`),
	KEY `FK_page__parent` (`parentid`),
	KEY `FK_page__user` (`user_id`),
	CONSTRAINT `pages_ibfk_1` FOREIGN KEY (`page_category_id`) REFERENCES `page_categories` (`id`),
	CONSTRAINT `pages_ibfk_2` FOREIGN KEY (`parentid`) REFERENCES `pages` (`id`),
	CONSTRAINT `pages_ibfk_3` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/* Page Cache table */
DROP TABLE IF EXISTS `cached_pages`;
CREATE TABLE `cached_pages` (
    `id` int(11) unsigned NOT NULL auto_increment,
    `page_id` int(11) unsigned NOT NULL,
    `cache_file` char(36) default NULL,
    `created` datetime NOT NULL default '0000-00-00 00:00:00',
    `modified` datetime NOT NULL default '0000-00-00 00:00:00',
    PRIMARY KEY  (`id`),
	KEY `FK_cached_page__page` (`page_id`),
	CONSTRAINT `cached_pages_ibfk_1` FOREIGN KEY (`page_id`) REFERENCES `pages` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/* Email Templates table */
DROP TABLE IF EXISTS `mail_templates`;
CREATE TABLE `mail_templates` (
    `id` int(11) unsigned NOT NULL auto_increment,
    `user_id` int(11) unsigned NOT NULL,
    `name` varchar(100) NOT NULL default '',
    `type` enum('html','text') default NULL COMMENT 'email format type',
    `content` blob default NULL,
    PRIMARY KEY  (`id`),
	KEY `FK_mail_template__user` (`user_id`),
	CONSTRAINT `mail_templates_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/* Web contacts table */
DROP TABLE IF EXISTS `web_contacts`;
CREATE TABLE `web_contacts` (
	`id` int(11) unsigned NOT NULL auto_increment,
	`client_id` int(11) unsigned NOT NULL default '0',
	`full_name` varchar(255) NOT NULL default '',
	`company` varchar(255) default NULL,
	`tel` varchar(40) default NULL,
	`url` varchar(120) default NULL,
	`email` varchar(120) NOT NULL default '',
	`message` text NOT NULL,
	`created` datetime NOT NULL default '0000-00-00 00:00:00',
	`read` tinyint(1) unsigned default '0',
	PRIMARY KEY  (`id`),
	KEY `FK_web_contact__client` (`client_id`),
	CONSTRAINT `web_contacts_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/* Comments table */
DROP TABLE IF EXISTS `comments`;
CREATE TABLE `comments` (
	`id` int(11) unsigned NOT NULL auto_increment,
	`client_id` int(11) unsigned NOT NULL,
    `user_id` int(11) unsigned NOT NULL,
	`parentid` int(11) unsigned default NULL,
	`objectid` int(11) unsigned default NULL,
	`type` enum('page','image','blog','product','paste','bookmark','file') default NULL COMMENT 'object type that this comment is related to',
	`username` varchar(30) default NULL COMMENT 'used for non members',
	`moderated` tinyint(1) default '0',
	`title` varchar(255) default NULL,
	`comment` text NOT NULL,
	`url` varchar(255) default NULL,
	`ip_address` varchar(15) default NULL,
	`country_id` int(3) unsigned NOT NULL,
	`longitude` varchar(10) default NULL COMMENT 'Geocode longitude coord',
	`latitude` varchar(10) default NULL COMMENT 'Geocode latitude coord',
	`created` datetime NOT NULL default '0000-00-00 00:00:00',
	PRIMARY KEY  (`id`),
	KEY `FK_comment__client` (`client_id`),
	KEY `FK_comment__user` (`user_id`),
	KEY `FK_comment__country` (`country_id`),
	CONSTRAINT `comments_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`),
	CONSTRAINT `comments_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
	CONSTRAINT `comments_ibfk_3` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;




/*
  -----------------------------------------------------------------------
    Store Tables.
  -----------------------------------------------------------------------
*/
/* `stores` Table layout */
DROP TABLE IF EXISTS `stores`;
CREATE TABLE `stores` (
	`id` int(11) unsigned NOT NULL auto_increment,
	`client_id` int(11) unsigned NOT NULL default '0',
	`name` varchar(255) NOT NULL default '',
	`enabled` tinyint(1) unsigned default '0',
	`site_name` varchar(255) default NULL,
	PRIMARY KEY  (`id`),
	KEY `FK_store__client` (`client_id`),
	CONSTRAINT `stores_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/* `product_categories` Table layout */
DROP TABLE IF EXISTS `product_categories`;
CREATE TABLE `product_categories` (
	`id` int(11) unsigned NOT NULL auto_increment,
	`store_id` int(11) unsigned NOT NULL default '0',
	`parentid` int(11) unsigned default NULL,
	`title` varchar(255) NOT NULL default '',
	`description` text default '',
	`image_id` int(11) unsigned default NULL,
	PRIMARY KEY  (`id`),
	KEY `FK_product_category__store` (`store_id`),
	KEY `FK_product_category__image` (`image_id`),
	CONSTRAINT `product_categories_ibfk_1` FOREIGN KEY (`store_id`) REFERENCES `stores` (`id`),
	CONSTRAINT `product_categories_ibfk_2` FOREIGN KEY (`image_id`) REFERENCES `images` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/* `products` Table layout */
DROP TABLE IF EXISTS `products`;
CREATE TABLE `products` (
	`id` int(11) unsigned NOT NULL auto_increment,
	`store_id` int(11) unsigned NOT NULL,
	`product_category_id` int(11) unsigned NOT NULL,
	`name` varchar(255) NOT NULL default '',
	`description` blob NOT NULL,
	`image_id` int(11) unsigned default NULL,
	`price` float(4,2) NOT NULL default '0',
	`tax` float(2,4) default '0.175' COMMENT 'Value Added Tax rate',
	`weight` float default '0',
	`ref` varchar(30) default NULL,
	`post` float(4,2) default '0',
	`created` datetime NOT NULL default '0000-00-00 00:00:00',
	`modified` datetime NOT NULL default '0000-00-00 00:00:00',
	PRIMARY KEY  (`id`),
	KEY `FK_product__store` (`store_id`),
	KEY `FK_product__product_category` (`product_category_id`),
	KEY `FK_product__image` (`image_id`),
	CONSTRAINT `products_ibfk_1` FOREIGN KEY (`store_id`) REFERENCES `stores` (`id`),
	CONSTRAINT `products_ibfk_2` FOREIGN KEY (`product_category_id`) REFERENCES `product_categories` (`id`),
	CONSTRAINT `products_ibfk_3` FOREIGN KEY (`image_id`) REFERENCES `images` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/* Table layout for `variations` */
DROP TABLE IF EXISTS `variations`;
CREATE TABLE `variations` (
	`id` int(11) unsigned NOT NULL auto_increment,
	`store_id` int(11) unsigned NOT NULL,
	`product_id` int(11) unsigned NOT NULL,
	`price` float(4,2) default '0',
	`tax` float(2,4) default '0',
	`weight` float default '0',
	`ref` varchar(30) default NULL,
	`post` float(4,2) default '0',
	`name` varchar(255) default NULL,
	`description` text default NULL,
	PRIMARY KEY  (`id`),
	KEY `FK_variation__store` (`store_id`),
	KEY `FK_variation__product` (`product_id`),
	CONSTRAINT `variations_ibfk_1` FOREIGN KEY (`store_id`) REFERENCES `stores` (`id`),
	CONSTRAINT `variations_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/* Table layout for `orders` */
DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders` (
	`id` int(11) unsigned NOT NULL auto_increment,
	`store_id` int(11) unsigned NOT NULL,
	`user_id` int(11) unsigned NOT NULL,
	`parentid` int(11) unsigned default NULL COMMENT 'ID of parent for orders with multiple components',
	`order_code` varchar(255) NOT NULL default '' COMMENT 'unique id usually from the payment processor',
	`product_id` int(11) unsigned NOT NULL,
	`variation_id` int(11) unsigned default NULL,
	`qty` int(11) unsigned NOT NULL default '1',
	`price` float NOT NULL default '0',
	`tax` float default '0',
	`weight` float default '0',
	`post` float default '0',
	`despatched` datetime NOT NULL default '0000-00-00 00:00:00',
	`firstname` varchar(255) NOT NULL default '',
	`lastname` varchar(255) NOT NULL default '',
	`delivery_address_id` int(11) unsigned default NULL,
	`billing_address_id` int(11) unsigned default NULL,
	`created` datetime NOT NULL default '0000-00-00 00:00:00',
	`modified` datetime NOT NULL default '0000-00-00 00:00:00',
	PRIMARY KEY  (`id`),
	KEY `FK_order__store` (`store_id`),
	KEY `FK_order__user` (`user_id`),
	KEY `FK_order__product` (`product_id`),
	KEY `FK_order__variation` (`variation_id`),
	KEY `FK_order__delivery_address` (`delivery_address_id`),
	KEY `FK_order__billing_address` (`billing_address_id`),
	CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`store_id`) REFERENCES `stores` (`id`),
	CONSTRAINT `orders_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
	CONSTRAINT `orders_ibfk_3` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`),
	CONSTRAINT `orders_ibfk_4` FOREIGN KEY (`variation_id`) REFERENCES `variations` (`id`),
	CONSTRAINT `orders_ibfk_5` FOREIGN KEY (`delivery_address_id`) REFERENCES `addresses` (`id`),
	CONSTRAINT `orders_ibfk_6` FOREIGN KEY (`billing_address_id`) REFERENCES `addresses` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/* Table layout for `transactions` */
DROP TABLE IF EXISTS `transactions`;
CREATE TABLE `transactions` (
	`id` int(11) unsigned NOT NULL auto_increment,
	`code` varchar(50) default NULL,
	`store_id` int(11) unsigned NOT NULL,
	`detail` text NOT NULL default '',
	`session_id` varchar(32) NOT NULL default '',
	`order_id` int(11) unsigned NOT NULL,
	`status` varchar(10) default NULL,
	PRIMARY KEY  (`id`),
	KEY `FK_transaction__store` (`store_id`),
	KEY `FK_transaction__order` (`order_id`),
	CONSTRAINT `transactions_ibfk_1` FOREIGN KEY (`store_id`) REFERENCES `stores` (`id`),
	CONSTRAINT `transactions_ibfk_2` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;




/*
  -----------------------------------------------------------------------
    Gallery Tables.
  -----------------------------------------------------------------------
*/
/* Galleries table */
DROP TABLE IF EXISTS `galleries`;
CREATE TABLE `galleries` (
	`id` int(11) unsigned NOT NULL auto_increment,
	`client_id` int(11) unsigned NOT NULL,
	`user_id` int(11) unsigned NOT NULL,
	`title` varchar(255) default NULL,
	`name` varchar(255) NOT NULL,
	`description` text NOT NULL default '',
	`created` datetime NOT NULL default '0000-00-00 00:00:00',
	PRIMARY KEY  (`id`),
	KEY `FK_gallery__client` (`client_id`),
	KEY `FK_gallery__user` (`user_id`),
	CONSTRAINT `galleries_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`),
	CONSTRAINT `galleries_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/* Gallery images table */
DROP TABLE IF EXISTS `gallery_images`;
CREATE TABLE `gallery_images` (
	`id` int(11) unsigned NOT NULL auto_increment,
	`gallery_id` int(11) unsigned NOT NULL,
	`image_id` int(11) unsigned NOT NULL,
	`created` datetime NOT NULL default '0000-00-00 00:00:00',
	PRIMARY KEY  (`id`),
	KEY `FK_gallery_image__gallery` (`gallery_id`),
	KEY `FK_gallery_image__image` (`image_id`),
	CONSTRAINT `gallery_images_ibfk_1` FOREIGN KEY (`gallery_id`) REFERENCES `galleries` (`id`),
	CONSTRAINT `gallery_images_ibfk_2` FOREIGN KEY (`image_id`) REFERENCES `images` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;




/*
  -----------------------------------------------------------------------
    Blog Tables.
  -----------------------------------------------------------------------
*/
/* Blog Archives table */
DROP TABLE IF EXISTS `blogs`;
CREATE TABLE `blogs` (
	`id` int(11) unsigned NOT NULL auto_increment,
	`client_id` int(11) unsigned NOT NULL,
	`user_id` int(11) unsigned NOT NULL,
	`title` varchar(255) default NULL,
	`name` varchar(255) NOT NULL,
	`description` text NOT NULL default '',
	`created` datetime NOT NULL default '0000-00-00 00:00:00',
	PRIMARY KEY  (`id`),
	KEY `FK_blog__client` (`client_id`),
	KEY `FK_blog__user` (`user_id`),
	CONSTRAINT `blogs_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`),
	CONSTRAINT `blogs_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/* Blog Archives table */
DROP TABLE IF EXISTS `archives`;
CREATE TABLE `archives` (
	`id` int(11) unsigned NOT NULL auto_increment,
	`client_id` int(11) unsigned NOT NULL,
	`user_id` int(11) unsigned NOT NULL,
	`blog_id` int(11) unsigned NOT NULL,
	`title` varchar(255) default NULL,
	`name` varchar(255) NOT NULL,
	`content` blob NOT NULL default '',
	`created` datetime NOT NULL default '0000-00-00 00:00:00',
	`modified` datetime NOT NULL default '0000-00-00 00:00:00',
	PRIMARY KEY  (`id`),
	KEY `FK_archive__client` (`client_id`),
	KEY `FK_archive__user` (`user_id`),
	KEY `FK_archive__blog` (`blog_id`),
	CONSTRAINT `archives_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`),
	CONSTRAINT `archives_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
	CONSTRAINT `archives_ibfk_3` FOREIGN KEY (`blog_id`) REFERENCES `blogs` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;




/*
  -----------------------------------------------------------------------
    Bookmarks Tables.
  -----------------------------------------------------------------------
*/
/* Bookmark Categories table */
DROP TABLE IF EXISTS `bookmark_categories`;
CREATE TABLE `bookmark_categories` (
	`id` int(11) unsigned NOT NULL auto_increment,
	`client_id` int(11) unsigned NOT NULL,
	`user_id` int(11) unsigned NOT NULL,
	`parentid` int(11) unsigned NOT NULL,
	`name` varchar(255) NOT NULL,
	`description` varchar(255) NOT NULL default '',
	`image` varchar(255) default NULL,
	`created` datetime NOT NULL default '0000-00-00 00:00:00',
	PRIMARY KEY  (`id`),
	KEY `FK_bookmark_category__client` (`client_id`),
	KEY `FK_bookmark_category__user` (`user_id`),
	CONSTRAINT `bookmark_categories_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`),
	CONSTRAINT `bookmark_categories_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/* Bookmarks table */
DROP TABLE IF EXISTS `bookmarks`;
CREATE TABLE `bookmarks` (
	`id` int(11) unsigned NOT NULL auto_increment,
	`user_id` int(11) unsigned NOT NULL,
	`bookmark_category_id` int(11) unsigned NOT NULL,
	`name` varchar(255) NOT NULL,
	`url` varchar(255) NOT NULL,
	`description` varchar(255) NOT NULL default '',
	`image` varchar(255) default NULL,
	`created` datetime NOT NULL default '0000-00-00 00:00:00',
	PRIMARY KEY  (`id`),
	KEY `FK_bookmark__user` (`user_id`),
	KEY `FK_bookmark__bookmark_category` (`bookmark_category_id`),
	CONSTRAINT `bookmarks_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
	CONSTRAINT `bookmarks_ibfk_2` FOREIGN KEY (`bookmark_category_id`) REFERENCES `bookmark_categories` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;




/*
  -----------------------------------------------------------------------
    Notes Tables.
  -----------------------------------------------------------------------
*/
/* Note Categories table */
DROP TABLE IF EXISTS `note_categories`;
CREATE TABLE `note_categories` (
	`id` int(11) unsigned NOT NULL auto_increment,
	`client_id` int(11) unsigned NOT NULL,
	`user_id` int(11) unsigned NOT NULL,
	`parentid` int(11) unsigned NOT NULL,
	`name` varchar(255) NOT NULL,
	`description` varchar(255) NOT NULL default '',
	`image` varchar(255) default NULL,
	`created` datetime NOT NULL default '0000-00-00 00:00:00',
	`modified` datetime NOT NULL default '0000-00-00 00:00:00',
	PRIMARY KEY  (`id`),
	KEY `FK_note_category__client` (`client_id`),
	KEY `FK_note_category__user` (`user_id`),
	CONSTRAINT `note_categories_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`),
	CONSTRAINT `note_categories_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/* Notes table */
DROP TABLE IF EXISTS `notes`;
CREATE TABLE `notes` (
	`id` int(11) unsigned NOT NULL auto_increment,
	`user_id` int(11) unsigned NOT NULL,
	`note_category_id` int(11) unsigned NOT NULL,
	`name` varchar(255) NOT NULL,
	`content` text NOT NULL default '',
	`created` datetime NOT NULL default '0000-00-00 00:00:00',
	`modified` datetime NOT NULL default '0000-00-00 00:00:00',
	PRIMARY KEY  (`id`),
	KEY `FK_note__user` (`user_id`),
	KEY `FK_note__note_category` (`note_category_id`),
	CONSTRAINT `notes_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
	CONSTRAINT `notes_ibfk_2` FOREIGN KEY (`note_category_id`) REFERENCES `note_categories` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



/*
  -----------------------------------------------------------------------
    Tasks Tables.
  -----------------------------------------------------------------------
*/
/* Table structure for table `task_categories` */
DROP TABLE IF EXISTS `task_categories`;
CREATE TABLE `task_categories` (
	`id` int(10) unsigned NOT NULL auto_increment,
	`client_id` int(10) unsigned NOT NULL,
	`user_id` int(11) unsigned NOT NULL,
	`name` varchar(255) default NULL,
	`description` varchar(255) default NULL,
	PRIMARY KEY  (`id`),
	KEY `FK_task_category__client` (`client_id`),
	CONSTRAINT `task_categories_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/* Table structure for table `tasks` */
DROP TABLE IF EXISTS `tasks`;
CREATE TABLE `tasks` (
	`id` int(11) unsigned NOT NULL auto_increment,
	`client_id` int(11) unsigned NOT NULL,
	`task_category_id` int(11) unsigned default NULL,
	`user_id` int(11) unsigned default NULL,
	`name` varchar(255) default NULL,
	`description` text,
	`scheduled_for` datetime NOT NULL default '0000-00-00 00:00:00',
	`created` datetime NOT NULL default '0000-00-00 00:00:00',
	`modified` datetime NOT NULL default '0000-00-00 00:00:00',
	PRIMARY KEY  (`id`),
	KEY `FK_task__task_category` (`task_category_id`),
	KEY `FK_task__client` (`client_id`),
	KEY `FK_task__user` (`user_id`),
	CONSTRAINT `tasks_ibfk_1` FOREIGN KEY (`task_category_id`) REFERENCES `task_categories` (`id`),
	CONSTRAINT `tasks_ibfk_2` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`),
	CONSTRAINT `tasks_ibfk_3` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



/*
  -----------------------------------------------------------------------
    Adserve Tables.
  -----------------------------------------------------------------------
*/
/* Banner Ads table */
DROP TABLE IF EXISTS `adserve_ads`;
CREATE TABLE `adserve_ads` (
	`id` mediumint(8) unsigned NOT NULL auto_increment,
	`client_id` int(11) unsigned NOT NULL,
	`url` varchar(255) default '',
	`alt` varchar(255) default '',
	`type` varchar(20) NOT NULL default 'swf',
	`width` mediumint(8) NOT NULL default '0',
	`height` mediumint(8) NOT NULL default '0',
	PRIMARY KEY  (`id`),
	KEY `FK_adserve_ad__client` (`client_id`),
	CONSTRAINT `adserve_ads_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;




/*
  -----------------------------------------------------------------------
    Visitor Tracker tables.
  -----------------------------------------------------------------------
*/
/* Visits table */
DROP TABLE IF EXISTS `visits`;
CREATE TABLE `visits` (
    `id` int(11) unsigned NOT NULL auto_increment,
	`client_id` int(11) unsigned NOT NULL,
	`timestamp` datetime NOT NULL default '0000-00-00 00:00:00',
	`ip` char(15) default NULL,
	`country_id` int(3) unsigned default NULL,
	`host` varchar(75) default NULL,
	`referer` varchar(250) default NULL,
	`referer_host` varchar(75) default NULL,
	`client` varchar(250) default NULL,
	`browser` varchar(25) default NULL,
	`os` varchar(25) default NULL,
	`engine` varchar(25) default NULL,
	`terms` varchar(150) default NULL,
	`entry` varchar(150) default NULL,
	`mail` varchar(255) default NULL COMMENT 'an ID used for mail promotions',
	PRIMARY KEY  (`id`),
	KEY `FK_visit__client` (`client_id`),
	KEY `FK_visit__country` (`country_id`),
	CONSTRAINT `visits_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`),
	CONSTRAINT `visits_ibfk_2` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/* Visitor Tracks table */
DROP TABLE IF EXISTS `tracks`;
CREATE TABLE `tracks` (
    `id` int(11) unsigned NOT NULL auto_increment,
	`visit_id` int(11) unsigned NOT NULL,
	`timestamp` datetime NOT NULL default '0000-00-00 00:00:00',
	`page` varchar(150) default NULL,
	`query` varchar(150) default NULL,
	PRIMARY KEY  (`id`),
	KEY `FK_track__visit` (`visit_id`),
	CONSTRAINT `tracks_ibfk_1` FOREIGN KEY (`visit_id`) REFERENCES `visits` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/* Downloads tracking table */
DROP TABLE IF EXISTS `dl_visits`;
CREATE TABLE dl_visits (
    `id` INT unsigned  NOT NULL auto_increment,
	`client_id` int(11) unsigned NOT NULL,
	`timestamp` datetime NOT NULL default '0000-00-00 00:00:00',
	`ip` char(15) default NULL,
	`country_id` int(3) unsigned default NULL,
	`host` varchar(75) default NULL,
	`referer` varchar(250) default NULL,
	`referer_host` varchar(75) default NULL,
	`client` varchar(250) default NULL,
	`browser` varchar(25) default NULL,
	`os` varchar(25) default NULL,
	`engine` varchar(25) default NULL,
	`terms` varchar(150) default NULL,
	`file` varchar(150) NOT NULL,
	`size` int(11) default NULL,
	`mail` varchar(255) default NULL,
	PRIMARY KEY  (`id`),
	KEY `FK_dl_visit__client` (`client_id`),
	KEY `FK_dl_visit__country` (`country_id`),
	CONSTRAINT `dl_visits_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`),
	CONSTRAINT `dl_visits_ibfk_2` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;