CREATE TABLE `hb_accounts` (
  `id` int(11) NOT NULL auto_increment,
  `client_id` int(11) NOT NULL,
  `order_id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `parent_id` int(11) NOT NULL default '0',
  `date_created` date NOT NULL,
  `domain` text NOT NULL,
  `server_id` int(11) NOT NULL,
  `payment_module` int(11) NOT NULL,
  `firstpayment` decimal(10,2) NOT NULL default '0.00',
  `total` decimal(10,2) NOT NULL default '0.00',
  `billingcycle` enum('Free','One Time','Monthly','Quarterly','Semi-Annually','Annually','Biennially','Triennially','Quadrennially','Quinquennially','Daily','Weekly','Hourly') default NULL,
  `next_due` date default NULL,
  `next_invoice` date NOT NULL,
  `status` enum('Pending','Active','Suspended','Terminated','Cancelled','Fraud') NOT NULL,
  `username` text NOT NULL,
  `password` text NOT NULL,
  `autosuspend` tinyint(1) NOT NULL default '0',
  `autosuspend_date` date default NULL,
  `rootpassword` text NOT NULL,
  `date_changed` datetime NOT NULL default '0000-00-00 00:00:00',
  `synch_date` datetime NOT NULL,
  `synch_error` tinyint(1) NOT NULL,
  `user_error` tinyint(1) NOT NULL,
  `domain_error` tinyint(1) NOT NULL,
  `notes` text NOT NULL,
  `manual` tinyint(1) NOT NULL,
  `extra_details` text NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `client_id` (`client_id`),
  KEY `order_id` (`order_id`),
  KEY `product_id` (`product_id`),
  KEY `server_id` (`server_id`),
  KEY `domain` (`domain`(64)),
  KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_accounts2servers` (
  `account_id` int(11) NOT NULL,
  `module_id` int(11) NOT NULL,
  `server_id` int(11) NOT NULL,
  `status` enum('Pending','Active','Suspended','Terminated') NOT NULL,
  `username` text NOT NULL,
  `password` text NOT NULL,
  `rootpassword` text NOT NULL,
  `extra_details` text NOT NULL,
  PRIMARY KEY  (`account_id`,`module_id`),
  KEY `server_id` (`server_id`),
  KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_accounts_addons` (
  `id` int(11) NOT NULL auto_increment,
  `account_id` int(11) NOT NULL,
  `order_id` int(11) NOT NULL,
  `addon_id` int(11) NOT NULL,
  `payment_module` int(11) NOT NULL,
  `name` text NOT NULL,
  `setup_fee` decimal(10,2) NOT NULL default '0.00',
  `recurring_amount` decimal(10,2) NOT NULL default '0.00',
  `billingcycle` enum('Free','One Time','Monthly','Quarterly','Semi-Annually','Annually','Biennially','Triennially','Daily','Weekly','Hourly') default NULL,
  `status` enum('Pending','Active','Suspended','Terminated','Cancelled') NOT NULL default 'Pending',
  `regdate` date NOT NULL,
  `next_due` date default NULL,
  `next_invoice` date NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `account_id` (`account_id`),
  KEY `order_id` (`order_id`),
  KEY `status` (`status`),
  KEY `addon_id` (`addon_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_accounts_tags` (
  `account_id` int(11) NOT NULL,
  `target_id` int(11) NOT NULL default '0',
  `tag` varchar(63) NOT NULL default '',
  PRIMARY KEY  (`account_id`,`target_id`,`tag`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_account_logs` (
  `id` int(11) NOT NULL auto_increment,
  `date` datetime NOT NULL,
  `account_id` int(11) NOT NULL,
  `admin_login` text NOT NULL,
  `module` text NOT NULL,
  `manual` tinyint(1) NOT NULL,
  `action` text NOT NULL,
  `change` text NOT NULL,
  `result` tinyint(1) NOT NULL,
  `error` text NOT NULL,
  `event` varchar(64) NOT NULL default '',
  PRIMARY KEY  (`id`),
  KEY `event` (`event`(12)),
  KEY `account_id` (`account_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_addons` (
  `id` int(11) NOT NULL auto_increment,
  `products` text NOT NULL,
  `name` text NOT NULL,
  `description` text NOT NULL,
  `visible` tinyint(1) NOT NULL,
  `unique` tinyint(1) NOT NULL default '0',
  `taxable` tinyint(1) NOT NULL,
  `system` tinyint(1) NOT NULL default '0',
  `hidden` tinyint(1) NOT NULL default '0',
  `autosetup` tinyint(1) NOT NULL default '0',
  `sort_order` int(2) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `name` (`name`(32))
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_addons_modules` (
  `addon_id` int(11) NOT NULL,
  `module` varchar(64) NOT NULL,
  `parentmod` varchar(64) NOT NULL,
  `options` text NOT NULL,
  `features` text NOT NULL,
  PRIMARY KEY  (`addon_id`),
  KEY `module` (`module`(32)),
  KEY `parentmod` (`parentmod`(32))
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_aff` (
  `id` int(11) NOT NULL auto_increment,
  `client_id` int(11) NOT NULL,
  `date_created` datetime NOT NULL,
  `visits` int(11) NOT NULL,
  `total_commissions` decimal(10,2) NOT NULL,
  `total_withdrawn` decimal(10,2) NOT NULL,
  `commision_plans` text NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `client_id` (`client_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_aff_commisions` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(80) NOT NULL,
  `type` enum('Fixed','Percent') NOT NULL,
  `rate` decimal(10,2) NOT NULL,
  `applicable_cycles` text NOT NULL,
  `applicable_products` text NOT NULL,
  `recurring` tinyint(1) NOT NULL,
  `enable_voucher` tinyint(1) NOT NULL,
  `notes` text NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `name` (`name`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
##########
INSERT INTO `hb_aff_commisions` (`id`, `name`, `type`, `rate`, `applicable_cycles`, `applicable_products`, `recurring`, `enable_voucher`, `notes`) VALUES
(1, 'Default', 'Percent', '10.00', 'all', '', 0, 1, 'Default commisions plan');
##########
CREATE TABLE `hb_aff_coupons` (
  `aff_id` int(10) NOT NULL,
  `coupon_id` int(10) NOT NULL,
  `commision_plan` int(10) NOT NULL,
  PRIMARY KEY  (`aff_id`,`coupon_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_aff_logs` (
  `id` int(10) NOT NULL auto_increment,
  `aff_id` int(10) NOT NULL,
  `date` datetime NOT NULL,
  `type` enum('Visit','Singup') NOT NULL,
  `target` int(11) default NULL,
  PRIMARY KEY  (`id`),
  KEY `aff_id` (`aff_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_aff_orders` (
  `id` int(11) NOT NULL auto_increment,
  `aff_id` int(11) NOT NULL,
  `order_id` int(11) NOT NULL,
  `invoice_id` int(11) NOT NULL,
  `commission` decimal(10,2) NOT NULL,
  `paid` tinyint(1) NOT NULL default '0',
  `datepaid` datetime NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `aff_id` (`aff_id`),
  KEY `order_id` (`order_id`),
  KEY `paid` (`paid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_aff_widlog` (
  `id` int(11) NOT NULL auto_increment,
  `aff_id` int(11) NOT NULL,
  `date` datetime NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `note` text,
  PRIMARY KEY  (`id`),
  KEY `aff_id` (`aff_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_annoucements` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `title` tinytext NOT NULL,
  `content` text NOT NULL,
  `enable` tinyint(1) default NULL,
  `date` date default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_automation_settings` (
  `item_id` int(11) NOT NULL,
  `type` enum('Hosting','Addon','Domain','ClientGroup') NOT NULL default 'Hosting',
  `setting` varchar(32) NOT NULL default '',
  `value` text NOT NULL,
  PRIMARY KEY  (`item_id`,`type`,`setting`(29))
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_cancel_requests` (
  `id` int(11) NOT NULL auto_increment,
  `date` datetime NOT NULL,
  `reason` text NOT NULL,
  `type` enum('Immediate','End of billing period','Other') NOT NULL,
  `account_id` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `account_id` (`account_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_categories` (
  `id` int(11) NOT NULL auto_increment,
  `module` int(11) NOT NULL,
  `name` text NOT NULL,
  `description` text NOT NULL,
  `visible` tinyint(1) NOT NULL,
  `sort_order` int(1) NOT NULL default '0',
  `template` varchar(32) NOT NULL default 'default',
  `ctype` enum('wizard','onestep','quote','domain') NOT NULL default 'wizard',
  `ptype` tinyint(2) NOT NULL,
  `slug` varchar(255) NOT NULL default '',
  `opconfig` text NOT NULL,
  `scenario_id` int(11) default '1',
  PRIMARY KEY  (`id`),
  KEY `visible` (`visible`),
  KEY `slug` (`slug`(32)),
  KEY `module` (`module`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_chat_canned_cat` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_chat_canned_fav` (
  `admin_id` int(11) NOT NULL,
  `response_id` int(11) NOT NULL,
  PRIMARY KEY  (`admin_id`,`response_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_chat_canned_resp` (
  `id` int(11) NOT NULL auto_increment,
  `category_id` int(11) NOT NULL,
  `title` varchar(127) NOT NULL,
  `body` text NOT NULL,
  `used` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `category_id` (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_chat_dept` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(127) NOT NULL,
  `description` text NOT NULL,
  `ticket_dept_id` int(11) NOT NULL default '0',
  `language_id` int(11) NOT NULL default '1',
  `options` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `ticket_dept` (`ticket_dept_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_chat_discussions2` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `department_id` int(11) NOT NULL,
  `visitor_id` int(11) NOT NULL,
  `footprint_id` int(11) NOT NULL,
  `staff_id_1` int(11) NOT NULL,
  `staff_id_2` int(11) NOT NULL default '0',
  `staff_rejects` varchar(127) NOT NULL default '',
  `type` enum('CA','AC') NOT NULL default 'CA',
  `status` enum('Invite','Invite Recv','Invite Decline','Active','Pending','Transfer','Timeout','Closed') NOT NULL default 'Pending',
  `date_start` datetime NOT NULL,
  `date_answered` datetime NOT NULL,
  `date_last` datetime NOT NULL,
  `visitor_name` varchar(127) NOT NULL,
  `visitor_email` varchar(127) NOT NULL,
  `subject` text NOT NULL,
  `robin_count` int(11) NOT NULL default '0',
  `staff_type` tinyint(4) NOT NULL default '0',
  `visitor_type` tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `visitor_id` (`visitor_id`),
  KEY `department_id` (`department_id`),
  KEY `staff_id_1` (`staff_id_1`),
  KEY `status` (`status`),
  KEY `footprint_id` (`footprint_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_chat_footprints` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `visitor_id` int(11) NOT NULL,
  `session_id` varchar(32) NOT NULL,
  `count` int(11) NOT NULL default '1',
  `datecreated` datetime NOT NULL,
  `date` datetime NOT NULL,
  `page` text NOT NULL,
  `ref` text NOT NULL,
  `searchterm` text NOT NULL,
  `page_title` text NOT NULL,
  `hash` varchar(32) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `visitor_id` (`visitor_id`),
  KEY `hash` (`hash`),
  KEY `session_id` (`session_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_chat_images` (
  `id` int(11) NOT NULL auto_increment,
  `type` enum('invitation','status') NOT NULL default 'status',
  `name` varchar(127) NOT NULL default '',
  `filename_off` varchar(255) NOT NULL default '',
  `filename_on` varchar(255) NOT NULL default '',
  `location` text NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_chat_messages2` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `chat_id` int(11) NOT NULL,
  `staff_read` tinyint(1) NOT NULL default '0',
  `visitor_read` tinyint(1) NOT NULL default '0',
  `type` enum('Staff','Client','Status','System') NOT NULL default 'Status',
  `date` datetime NOT NULL,
  `message` text NOT NULL,
  `submitter_id` int(11) NOT NULL default '0',
  `submitter_name` varchar(45) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `chat_id` (`chat_id`),
  KEY `submitter_id` (`submitter_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_chat_staff2` (
  `admin_id` int(11) NOT NULL auto_increment,
  `status` enum('Active','Away','Offline') NOT NULL default 'Offline',
  `lastseen` datetime NOT NULL,
  `canned_welcome_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`admin_id`),
  KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_chat_staff2dept` (
  `admin_id` int(11) NOT NULL,
  `dept_id` int(11) NOT NULL,
  PRIMARY KEY  (`admin_id`,`dept_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_chat_visitor` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `client_id` int(11) NOT NULL default '0',
  `session` varchar(32) NOT NULL default '0',
  `last_visit` datetime NOT NULL,
  `first_visit` datetime NOT NULL,
  `ban_util` date NOT NULL default '0000-00-00',
  `ip` varchar(45) NOT NULL,
  `hostname` varchar(127) NOT NULL,
  `name` varchar(127) NOT NULL,
  `email` varchar(127) NOT NULL,
  `os` varchar(45) NOT NULL,
  `browser` varchar(45) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `client_id` (`client_id`),
  KEY `ip` (`ip`),
  KEY `session` (`session`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_chat_visitor_geodata` (
  `visitor_id` int(11) NOT NULL,
  `locId` int(11) NOT NULL,
  `country` varchar(5) NOT NULL,
  `region` varchar(100) NOT NULL,
  `city` varchar(255) NOT NULL,
  `postalCode` varchar(100) NOT NULL,
  `latitude` varchar(100) NOT NULL,
  `longitude` varchar(100) NOT NULL,
  PRIMARY KEY  (`visitor_id`),
  KEY `locId` (`locId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_client_access` (
  `id` int(10) NOT NULL,
  `group_id` int(10) NOT NULL default '0',
  `email` text NOT NULL,
  `password` varchar(255) NOT NULL,
  `lastlogin` datetime default NULL,
  `ip` text NOT NULL,
  `host` text NOT NULL,
  `status` enum('Active','Closed') NOT NULL default 'Active',
  PRIMARY KEY  (`id`),
  KEY `email` (`email`(64)),
  KEY `group_id` (`group_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_client_activity_log` (
  `id` int(11) NOT NULL auto_increment,
  `date` datetime NOT NULL default '0000-00-00 00:00:00',
  `description` text NOT NULL,
  `client_id` int(11) NOT NULL,
  `admin_id` int(11) NOT NULL default '0',
  `admin_name` varchar(50) NOT NULL default '',
  `event` varchar(100) NOT NULL default '',
  PRIMARY KEY  (`id`),
  KEY `client_id` (`client_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_client_billing` (
  `id` int(11) NOT NULL auto_increment,
  `client_id` int(11) NOT NULL,
  `credit` decimal(10,2) NOT NULL,
  `taxexempt` tinyint(1) NOT NULL,
  `latefeeoveride` tinyint(1) NOT NULL,
  `overideduenotices` tinyint(1) NOT NULL,
  `cardtype` enum('Visa','MasterCard','Discover','American Express','Laser','Maestro') NOT NULL,
  `cardnum` blob NOT NULL,
  `expdate` blob NOT NULL,
  `currency_id` int(11) NOT NULL default '0',
  `token` blob NOT NULL,
  `token_gateway_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `client_id` (`client_id`),
  KEY `currency_id` (`currency_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_client_changes_log` (
  `id` int(11) NOT NULL auto_increment,
  `client_id` int(11) NOT NULL,
  `who` varchar(127) NOT NULL default '',
  `change` text NOT NULL,
  `date` datetime NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `client_id` (`client_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_client_credit_log` (
  `id` int(11) NOT NULL auto_increment,
  `date` datetime NOT NULL,
  `client_id` int(11) NOT NULL,
  `in` decimal(10,2) NOT NULL default '0.00',
  `out` decimal(10,2) NOT NULL default '0.00',
  `balance` decimal(10,2) NOT NULL default '0.00',
  `description` varchar(255) NOT NULL,
  `transaction_id` varchar(32) NOT NULL default '',
  `invoice_id` int(11) NOT NULL default '0',
  `admin_id` int(11) NOT NULL default '0',
  `admin_name` varchar(70) NOT NULL default '',
  PRIMARY KEY  (`id`),
  KEY `client_id` (`client_id`),
  KEY `transaction_id` (`transaction_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_client_details` (
  `id` int(10) NOT NULL auto_increment,
  `parent_id` int(11) NOT NULL default '0',
  `firstname` text NOT NULL,
  `lastname` text NOT NULL,
  `companyname` text NOT NULL,
  `address1` text NOT NULL,
  `address2` text NOT NULL,
  `city` text NOT NULL,
  `state` text NOT NULL,
  `postcode` text NOT NULL,
  `country` text NOT NULL,
  `phonenumber` text NOT NULL,
  `datecreated` date NOT NULL,
  `notes` text NOT NULL,
  `language` text NOT NULL,
  `company` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `firstname_lastname` (`firstname`(32),`lastname`(32)),
  KEY `parent_id` (`parent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


##########
CREATE TABLE `hb_client_fields_values` (
  `client_id` int(11) NOT NULL,
  `field_id` int(11) NOT NULL,
  `value` text NOT NULL,
  PRIMARY KEY  (`client_id`,`field_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_client_group` (
  `id` int(10) NOT NULL auto_increment,
  `name` varchar(127) NOT NULL,
  `description` text NOT NULL,
  `color` varchar(7) NOT NULL default '#000000',
  `discount` decimal(10,2) NOT NULL default '0.00',
  `scenario_id` int(10) NOT NULL default '0',
  `default_priority` int(1) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_client_privileges` (
  `client_id` int(11) NOT NULL,
  `privileges` text NOT NULL,
  PRIMARY KEY  (`client_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_common` (
  `id` int(11) NOT NULL,
  `rel` enum('Product','Addon','Config','Bundled','FResource') NOT NULL,
  `paytype` varchar(20) NOT NULL default 'Free',
  `m_setup` decimal(10,2) NOT NULL default '0.00',
  `q_setup` decimal(10,2) NOT NULL default '0.00',
  `s_setup` decimal(10,2) NOT NULL default '0.00',
  `a_setup` decimal(10,2) NOT NULL default '0.00',
  `b_setup` decimal(10,2) NOT NULL default '0.00',
  `t_setup` decimal(10,2) NOT NULL default '0.00',
  `p4_setup` decimal(10,2) NOT NULL default '0.00',
  `p5_setup` decimal(10,2) NOT NULL default '0.00',
  `d_setup` decimal(10,2) NOT NULL default '0.00',
  `w_setup` decimal(10,2) NOT NULL default '0.00',
  `h_setup` decimal(10,2) NOT NULL default '0.00',
  `m` decimal(10,2) NOT NULL default '0.00',
  `q` decimal(10,2) NOT NULL default '0.00',
  `s` decimal(10,2) NOT NULL default '0.00',
  `a` decimal(10,2) NOT NULL default '0.00',
  `b` decimal(10,2) NOT NULL default '0.00',
  `t` decimal(10,2) NOT NULL default '0.00',
  `p4` decimal(10,2) NOT NULL default '0.00',
  `p5` decimal(10,2) NOT NULL default '0.00',
  `d` decimal(10,2) NOT NULL default '0.00',
  `w` decimal(10,2) NOT NULL default '0.00',
  `h` decimal(10,2) NOT NULL default '0.00',
  PRIMARY KEY  (`id`,`rel`),
  KEY `paytype` (`paytype`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_config2accounts` (
  `rel_type` enum('Hosting','Domain') NOT NULL default 'Hosting',
  `account_id` int(11) NOT NULL,
  `config_cat` int(11) NOT NULL,
  `config_id` int(11) NOT NULL,
  `qty` int(11) NOT NULL default '1',
  `data` text NOT NULL,
  PRIMARY KEY  (`rel_type`,`account_id`,`config_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
INSERT INTO `hb_configuration` (`setting`, `value`) VALUES
('1DomainReminder', '50'),
('1OverdueReminder', '1'),
('2DomainReminder', '40'),
('2OverdueReminder', '2'),
('3DomainReminder', '30'),
('3OverdueReminder', '3'),
('4DomainReminder', '10'),
('5DomainReminder', '5'),
('AddLateFeeAfter', '6'),
('AdvancedDueDate', 'off'),
('AffBonus', '5'),
('AffDelay', '2'),
('AffIntegration', ''),
('AffLandingPage', 'default'),
('AffMinWid', '30'),
('AffRecItems', ''),
('AffRecurring', 'off'),
('AffType', 'Percent'),
('AffValue', '10'),
('AfterOrderRedirect', '1'),
('AllowBulkPayment', 'on'),
('AllowedAttachmentExt', '.jpg;.gif;.zip'),
('ApplyTermsURL', ''),
('AttachPDFCopy', 'off'),
('AttachPDFInvoice', 'off'),
('AttachPDFInvoicePaid', 'off'),
('AutoCancelUnpaidInvoices', 'off'),
('AutologoutTime', '30'),
('AutoProcessCancellations', 'off'),
('AutoRegDomains', 'on'),
('AutoSuspensionPeriod', '10'),
('AutoTerminationPeriod', '30'),
('BCCInvoiceEmails', ''),
('BusinessName', 'Your Business Name'),
('CancelInvoicesOnTerminate', 'off'),
('CaptchaUnregTickets', 'off'),
('CCAllowRemove', 'on'),
('CCAttemptOnce', 'off'),
('CCChargeAuto', 'on'),
('CCDaysBeforeCharge', '5'),
('CCForceAttempt', 'off'),
('CCRetryForDays', '4'),
('ChatAllowedHosts', ''),
('ChatGeoIPEnabled', 'off'),
('ChatInvitationTimeout', '120'),
('ChatNonClients', 'off'),
('ChatRoundRobinInterval', '45'),
('ChatRoundRobinMiss', '2'),
('ChatTrackHostBill', 'on'),
('CnoteEnable', 'off'),
('CNoteNumerationFormat', '{$number}/{$y}'),
('CNoteNumerationPaid', '1'),
('CNoteTemplate', '102'),
('CompoundL2', 'off'),
('ContinueInvoices', 'on'),
('CurrencyCode', 'USD'),
('CurrencyFormat', '1,234.56'),
('CurrencyName', 'US Dollars'),
('CurrencySign', '$'),
('DateFormat', 'YYYY-MM-DD'),
('DecimalPlaces', '2'),
('DefaultPassComplex', 'off'),
('DefaultPassLength', '8'),
('DefaultPassOptions', '1;1;1;0;8'),
('DefaultResultsQty', '30'),
('DefaultTimezone', 'America/Los_Angeles'),
('DisplayBanInfo', 'off'),
('DisplayDecimalPlaces', '2'),
('dnsmanagement', 'off'),
('DomainAllowOwn', 'on'),
('DomainAllowRegister', 'off'),
('DomainAllowSubdomain', 'on'),
('DomainAllowTransfer', 'off'),
('DomainDNSCharge', '0.00'),
('DomainEmailCharge', '0.00'),
('DomainIDCharge', '0.00'),
('DontSendSubscrInvNotify', 'on'),
('EmailHTMLWrapper', '<div style="font-family:Verdana;font-size:11px">{$message}</div>'),
('EmailSignature', ''),
('EmailSwitch', 'on'),
('EnableAddonRelatedTermination', 'off'),
('EnableAffiliates', 'off'),
('EnableAfteractions', 'on'),
('EnableAutoSuspension', 'off'),
('EnableAutoTermination', 'off'),
('EnableAutoTransferDomain', 'on'),
('EnableAutoUnSuspension', 'on'),
('EnableChat', 'off'),
('EnableClientScurity', 'on'),
('EnableProfiles', 'on'),
('EnableProRata', 'off'),
('EnableQueue', 'on'),
('EnableTax', 'off'),
('ForceSSL', 'off'),
('InitialDueDays', '0'),
('InstallURL', 'http://four.hostbillapp.com/'),
('InvCompanyLogo', ''),
('InvoiceDelay', 'off'),
('InvoiceDelayDays', '0'),
('InvoiceExpectDays', '0'),
('InvoiceFooter', ''),
('InvoiceGeneration', '7'),
('InvoiceModel', 'default'),
('InvoiceNumerationFormat', '{$number}/{$m}/{$y}'),
('InvoiceNumerationFrom', '1'),
('InvoiceNumerationPaid', '1'),
('InvoicePaidAutoReset', '0'),
('InvoicePrefix', ''),
('InvoiceStoreClient', 'off'),
('InvoiceTemplate', '1'),
('InvoiceUnpaidReminder', '0'),
('ISOCurrency', 'USD'),
('LateFeeType', '0'),
('LateFeeValue', '0'),
('License', ''),
('MailSMTPHost', 'localhost'),
('MailSMTPPassword', ''),
('MailSMTPPort', '25'),
('MailSMTPUsername', ''),
('MailUseSMTP', 'off'),
('MaintenanceMode', 'off'),
('MaxAttachmentSize', '1'),
('MaxDeposit', '500'),
('MinDeposit', '50'),
('MobileNotificationsAdmin', 'off'),
('MobileNotificationsAPriority', ''),
('MobileNotificationsClient', 'off'),
('MobileNotificationsDepts', ''),
('MobileNotificationsModAdmin', 'all'),
('MobileNotificationsModClient', 'all'),
('Netstat', 'off'),
('NetstatFTP', 'on'),
('NetstatHTTP', 'on'),
('NetstatIMAP', 'on'),
('NetstatLOAD', 'on'),
('NetstatMYSQL', 'on'),
('NetstatPOP3', 'on'),
('NetstatSSH', 'on'),
('NetstatUPTIME', 'on'),
('OfferDeposit', 'on'),
('OfferDomains', 'off'),
('OfferDownloads', 'off'),
('OfferEstimates', 'off'),
('OfferKB', 'off'),
('OfferNews', 'off'),
('OfferSupport', 'off'),
('OrderAutoAccept', 'on'),
('PayToText', 'HostBill Demo'),
('PluginUpdates', ''),
('ProRataDay', '1'),
('ProRataMonth', 'disabled'),
('ProRataNextMonth', '0'),
('quickconfig', 'admin_pass|payment'),
('RecordsPerPage', '25'),
('RenewInvoice', '1'),
('SendPaymentReminderEmails', 'off'),
('SEOUrlMode', 'index.php?/'),
('StorePDFInvoice', 'off'),
('StorePDFPath', ''),
('SupportedCC', 'Visa,MasterCard,Discover,American Express'),
('SystemMail', ''),
('TaxClientFunds', 'off'),
('TaxDomains', 'off'),
('TaxLateFee', 'off'),
('TaxType', 'exclusive'),
('TicketAutoClose', '0'),
('TicketHTMLTags', 'off'),
('TicketImportTimeLimit', '5'),
('UserCountry', 'US'),
('UserLanguage', 'english'),
('UserTemplate', 'nextgen_clean'),
('Version', '4.9.8'),
('VersionLastCheck', '0'),
('VersionLatest', '4.9.8');
##########
CREATE TABLE `hb_config_items` (
  `id` int(11) NOT NULL auto_increment,
  `category_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `variable_id` varchar(255) NOT NULL,
  `sort_order` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `category_id` (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_config_items_cat` (
  `id` int(11) NOT NULL auto_increment,
  `type` int(11) NOT NULL default '1',
  `required` tinyint(1) NOT NULL default '0',
  `name` varchar(128) NOT NULL default '',
  `key` varchar(32) NOT NULL,
  `variable` varchar(64) NOT NULL default '',
  `description` text NOT NULL,
  `category` varchar(64) NOT NULL default '',
  `product_id` int(11) NOT NULL,
  `copy_of` int(11) NOT NULL default '0',
  `options` int(11) NOT NULL default '0',
  `config` text,
  `sort_order` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `category` (`category`(16)),
  KEY `type` (`type`),
  KEY `options` (`options`),
  KEY `copy_of` (`copy_of`),
  KEY `product_id` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_config_items_types` (
  `id` int(11) NOT NULL auto_increment,
  `type` varchar(32) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
##########
INSERT INTO `hb_config_items_types` (`id`, `type`) VALUES
(1, 'select'),
(2, 'qty'),
(3, 'checkbox'),
(4, 'input'),
(5, 'radio'),
(6, 'textarea'),
(7, 'slider'),
(8, 'idprotection');
##########
CREATE TABLE `hb_config_resources` (
  `id` int(11) NOT NULL auto_increment,
  `item_id` int(11) NOT NULL,
  `qty` int(11) NOT NULL,
  `qty_max` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `item_id` (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_config_upgrades` (
  `id` int(11) NOT NULL auto_increment,
  `rel_type` enum('Hosting','Domain') NOT NULL default 'Hosting',
  `account_id` int(11) NOT NULL,
  `order_id` int(11) NOT NULL,
  `config_cat` int(11) NOT NULL,
  `old_config_id` int(11) NOT NULL,
  `new_config_id` int(11) NOT NULL,
  `old_qty` int(11) NOT NULL,
  `new_qty` int(11) NOT NULL,
  `status` enum('Pending','Upgraded') NOT NULL default 'Pending',
  PRIMARY KEY  (`id`),
  KEY `order_id` (`order_id`),
  KEY `config_cat` (`config_cat`),
  KEY `rel_type` (`rel_type`,`account_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_coupons` (
  `id` int(11) NOT NULL auto_increment,
  `code` varchar(32) NOT NULL,
  `type` enum('fixed','percent') NOT NULL,
  `applyto` enum('price','setupfee','both') NOT NULL default 'price',
  `cycle` enum('once','recurring') NOT NULL,
  `value` decimal(10,2) NOT NULL,
  `cycles` text NOT NULL,
  `products` text NOT NULL,
  `upgrades` text NOT NULL,
  `addons` text NOT NULL,
  `domains` text NOT NULL,
  `expires` date NOT NULL,
  `max_usage` int(5) NOT NULL,
  `num_usage` int(5) NOT NULL default '0',
  `clients` enum('all','new','existing') NOT NULL,
  `client_id` int(11) NOT NULL default '0',
  `notes` text NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_coupons_log` (
  `id` int(11) NOT NULL auto_increment,
  `coupon_id` int(11) NOT NULL,
  `client_id` int(11) NOT NULL,
  `order_id` int(11) NOT NULL,
  `date` date NOT NULL,
  `discount` decimal(10,2) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `coupon_id` (`coupon_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_creditnotes` (
  `invoice_id` int(11) NOT NULL,
  `credit_note_id` int(11) NOT NULL,
  `invoice_item_id` int(11) NOT NULL default '0',
  `credit_item_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`invoice_id`,`credit_note_id`,`invoice_item_id`),
  KEY `credit_item_id` (`credit_item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
##########
INSERT INTO `hb_cron_tasks` (`task`, `name`, `lastrun`, `status`, `count`, `metadata`, `output`, `run_every`, `run_every_time`) VALUES
('affiliateMonthlyReport', 'Send affiliate monthly referral report', '0000-00-00 00:00:00', -1, 0, '', '', 'Time', '1200'),
('automaticDomainExpire', 'Change status of expired domains', '0000-00-00 00:00:00', -1, 0, '', '', 'Time', '1200'),
('automaticRegisterDomains', 'Auto-Register Domains', '0000-00-00 00:00:00', 1, 0, '', '', 'Run', '1200'),
('automaticRenewDomains', 'Auto-Renew Domains', '0000-00-00 00:00:00', 1, 0, '', '', 'Run', '1200'),
('automaticSynchronizePendingDomains', 'Synchronize Pending Action Domains', '0000-00-00 00:00:00', -1, 0, '', '', 'Hour', '1200'),
('automaticTransferDomains', 'Auto-Transfer Domains', '0000-00-00 00:00:00', -1, 0, '', '', 'Run', '1200'),
('cancellationRequests', 'Automatic Cancellation Requests Processing ', '0000-00-00 00:00:00', 1, 0, '', '', 'Run', '1200'),
('chargeCC', 'Charge credit cards', '0000-00-00 00:00:00', -1, 0, '', '', 'Time', '1200'),
('domainNotification', 'Send Expiring Domain Notifications ', '0000-00-00 00:00:00', 1, 0, '', '', 'Time', '1200'),
('domainSync', 'Synchronize domain expiration dates with registrars ', '0000-00-00 00:00:00', 1, 0, '', '', 'Hour', '1'),
('escalateTickets', 'Escalate overdue tickets', '0000-00-00 00:00:00', 1, 0, '', '', 'Run', '1200'),
('generateInvoices', 'Generate Invoices', '0000-00-00 00:00:00', 1, 0, '', '', 'Time', '1200'),
('importPOP', 'Import Tickets using POP method', '0000-00-00 00:00:00', 1, 0, '', '', 'Run', '1200'),
('InvoiceReminder', 'Send Invoice Reminders', '0000-00-00 00:00:00', 1, 0, '', '', 'Time', '1200'),
('lateFeeInvoices', 'Add Late Fee to overdue invoices', '0000-00-00 00:00:00', 1, 0, '', '', 'Time', '1200'),
('meteredBillingUpdate', 'Update Metered Billing Values', '0000-00-00 00:00:00', 1, 0, '', '', 'Run', '1200'),
('moduleAutomation', 'Perform auto provisioning tasks like suspend/terminate', '0000-00-00 00:00:00', 1, 0, '', '', 'Time', '1200'),
('outdateEstimates', 'Mark outdated estimates Dead', '0000-00-00 00:00:00', -1, 0, '', '', 'Time', '1200'),
('processAccountProvisioning', 'Auto provision paid accounts', '0000-00-00 00:00:00', 1, 0, '', '', 'Run', '1200'),
('sendScheduledTickets', 'Send-Out scheduled support tickets / ticket replies', '0000-00-00 00:00:00', 1, 0, '', '', 'Run', '1200'),
('subscriptionItemsCleanup', 'Remove unused subscription items', '0000-00-00 00:00:00', 1, 0, '', '', 'Time', '1200'),
('TaskScheduler', 'Automation queue scheduler', '0000-00-00 00:00:00', 1, 0, '', '', 'Hour', '1100'),
('TaskScheduler_Execute', 'Execute custom automation tasks', '0000-00-00 00:00:00', 1, 0, '', '', 'Run', '1100'),
('ticketAutoClose', 'Auto-Close tickets non-answered by client', '0000-00-00 00:00:00', 1, 0, '', '', 'Run', '1200'),
('updateConversionRates', 'Update Conversion Rates', '0000-00-00 00:00:00', -1, 0, '', '', 'Time', '1200');
##########
CREATE TABLE `hb_currencies` (
  `id` int(11) NOT NULL auto_increment,
  `code` varchar(4) NOT NULL,
  `sign` varchar(4) NOT NULL,
  `iso` varchar(4) NOT NULL,
  `rate` decimal(10,4) NOT NULL default '1.0000',
  `last_changes` datetime NOT NULL,
  `update` tinyint(1) NOT NULL default '1',
  `enable` tinyint(1) NOT NULL default '1',
  `format` varchar(16) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_dnsconfig` (
  `setting` varchar(128) NOT NULL,
  `value` text NOT NULL,
  PRIMARY KEY  (`setting`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

##########
CREATE TABLE `hb_dnsdomains` (
  `id` int(11) NOT NULL auto_increment,
  `client_id` int(11) NOT NULL,
  `domain` text NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `client_id` (`client_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_dnslogs` (
  `id` int(11) NOT NULL auto_increment,
  `client_id` int(11) NOT NULL,
  `date` datetime NOT NULL default '0000-00-00 00:00:00',
  `action` text NOT NULL,
  `fields` text NOT NULL,
  `errors` text NOT NULL,
  `result` tinyint(1) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `client_id` (`client_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_currency_cache` (
  `id` int(11) NOT NULL auto_increment,
  `currency_id` int(11) NOT NULL,
  `rate` decimal(10,4) NOT NULL default '0.0000',
  `comment` varchar(127) NOT NULL,
  `date` date NOT NULL,
  PRIMARY KEY  (`id`,`currency_id`,`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_dns_domains` (
  `account_id` int(11) NOT NULL,
  `domain_id` int(11) NOT NULL,
  `domain` varchar(255) NOT NULL,
  `created` datetime NOT NULL,
  PRIMARY KEY  (`domain_id`,`account_id`),
  KEY `account` (`account_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_dns_templates` (
  `id` int(11) NOT NULL auto_increment,
  `product_id` int(11) NOT NULL,
  `template` varchar(30) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `product_id` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_dns_templates_records` (
  `id` int(11) NOT NULL auto_increment,
  `template_id` int(11) default NULL,
  `name` varchar(255) default NULL,
  `type` varchar(6) default NULL,
  `content` varchar(255) default NULL,
  `ttl` int(11) default NULL,
  `prio` int(11) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_domains` (
  `id` int(11) NOT NULL auto_increment,
  `client_id` int(11) NOT NULL,
  `order_id` int(11) NOT NULL,
  `tld_id` int(11) NOT NULL,
  `name` text NOT NULL,
  `reg_module` int(11) NOT NULL,
  `payment_module` int(11) NOT NULL,
  `date_created` date NOT NULL,
  `firstpayment` decimal(10,2) NOT NULL default '0.00',
  `recurring_amount` decimal(10,2) NOT NULL,
  `period` int(1) NOT NULL default '1',
  `expires` date default NULL,
  `type` enum('Register','Transfer','Renew') NOT NULL,
  `status` enum('Pending','Pending Registration','Pending Transfer','Active','Expired','Cancelled','Fraud') NOT NULL default 'Pending',
  `next_due` date NOT NULL,
  `next_invoice` date NOT NULL,
  `idprotection` tinyint(1) NOT NULL default '0',
  `nameservers` text NOT NULL,
  `autorenew` tinyint(1) NOT NULL,
  `reglock` tinyint(1) NOT NULL,
  `manual` tinyint(1) NOT NULL default '0',
  `epp_code` text NOT NULL,
  `notes` text NOT NULL,
  `extended` text NOT NULL,
  `synch_date` datetime default '0000-00-00 00:00:00',
  `nsips` text NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `client_id` (`client_id`),
  KEY `order_id` (`order_id`),
  KEY `name` (`name`(64)),
  KEY `status` (`status`),
  KEY `tld_id` (`tld_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_domain_logs` (
  `id` int(11) NOT NULL auto_increment,
  `domain_id` int(11) NOT NULL,
  `admin_login` text NOT NULL,
  `date` datetime NOT NULL,
  `module` varchar(32) NOT NULL,
  `action` text NOT NULL,
  `result` tinyint(1) NOT NULL,
  `change` text NOT NULL,
  `error` text NOT NULL,
  `event` varchar(64) NOT NULL default '',
  PRIMARY KEY  (`id`),
  KEY `domain_id` (`domain_id`),
  KEY `event` (`event`(12))
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_domain_periods` (
  `product_id` int(11) NOT NULL,
  `period` tinyint(3) NOT NULL default '1',
  `register` decimal(10,2) NOT NULL default '0.00',
  `transfer` decimal(10,2) NOT NULL default '0.00',
  `renew` decimal(10,2) NOT NULL default '0.00',
  PRIMARY KEY  (`product_id`,`period`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_domain_prices` (
  `product_id` int(11) NOT NULL,
  `module` int(11) NOT NULL,
  `not_renew` tinyint(1) NOT NULL,
  `epp` tinyint(4) NOT NULL default '1',
  `options` tinyint(4) NOT NULL default '1',
  `ns` text NOT NULL,
  `nsips` text NOT NULL,
  PRIMARY KEY  (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_downloads` (
  `id` int(11) NOT NULL auto_increment,
  `category_id` int(11) NOT NULL,
  `filename` varchar(128) NOT NULL,
  `name` varchar(128) NOT NULL,
  `description` text NOT NULL,
  `size` int(11) NOT NULL,
  `uploaded` date NOT NULL,
  `product_id` int(11) NOT NULL default '0',
  `downloads` int(11) NOT NULL default '0',
  `clients` tinyint(1) NOT NULL default '0',
  `client_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `category_id` (`category_id`),
  KEY `clients` (`clients`),
  KEY `client_id` (`client_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_downloads_cats` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(128) NOT NULL,
  `description` text NOT NULL,
  `visible` tinyint(1) NOT NULL default '1',
  PRIMARY KEY  (`id`),
  KEY `visible` (`visible`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_email_assign` (
  `id` int(11) NOT NULL,
  `rel` enum('Product','Addon','Department') NOT NULL default 'Product',
  `event` varchar(64) NOT NULL,
  `email_id` int(11) NOT NULL,
  `options` text,
  PRIMARY KEY  (`id`,`rel`,`event`),
  KEY `email_id` (`email_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_email_log` (
  `id` int(11) NOT NULL auto_increment,
  `client_id` int(11) NOT NULL,
  `email` varchar(100) NOT NULL,
  `subject` text NOT NULL,
  `message` text NOT NULL,
  `date` datetime default NULL,
  `status` tinyint(4) NOT NULL default '1',
  PRIMARY KEY  (`id`),
  KEY `client_id` (`client_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

##########
CREATE TABLE `hb_estimates` (
  `id` int(11) NOT NULL auto_increment,
  `status` enum('Draft','Sent','Accepted','Invoiced','Dead') NOT NULL,
  `subject` varchar(64) NOT NULL,
  `client_id` int(11) NOT NULL,
  `date_created` date default NULL,
  `date_expires` date default NULL,
  `subtotal` decimal(10,2) NOT NULL,
  `credit` decimal(10,2) NOT NULL,
  `tax` decimal(10,2) NOT NULL,
  `taxrate` decimal(10,2) NOT NULL,
  `tax2` decimal(10,2) NOT NULL,
  `taxrate2` decimal(10,2) NOT NULL,
  `total` decimal(10,2) NOT NULL default '0.00',
  `currency_id` int(3) NOT NULL default '0',
  `rate` decimal(10,4) NOT NULL default '1.0000',
  `notes` text NOT NULL,
  `notes_private` text NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `status` (`status`),
  KEY `client_id` (`client_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_estimate_items` (
  `id` int(11) NOT NULL auto_increment,
  `estimate_id` int(11) NOT NULL,
  `item_id` int(11) NOT NULL default '0',
  `type` enum('Other','Product','Addon','Config') NOT NULL default 'Other',
  `description` text NOT NULL,
  `amount` decimal(10,2) NOT NULL default '0.00',
  `taxed` tinyint(1) NOT NULL,
  `qty` int(4) NOT NULL default '1',
  PRIMARY KEY  (`id`),
  KEY `estid` (`estimate_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_fraud_output` (
  `id` int(11) NOT NULL auto_increment,
  `order_id` int(11) NOT NULL,
  `module` int(11) NOT NULL,
  `output` text NOT NULL,
  `date` datetime NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `order_id` (`order_id`),
  KEY `module` (`module`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_gateway_log` (
  `id` int(11) NOT NULL auto_increment,
  `module` int(11) NOT NULL,
  `date` datetime NOT NULL,
  `output` text NOT NULL,
  `result` enum('Successfull','Failure','Pending') NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `module_id` (`module`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_geoip_block_0` (
  `startIpNum` int(20) unsigned NOT NULL default '0',
  `endIpNum` int(20) unsigned NOT NULL default '0',
  `locId` int(11) NOT NULL,
  KEY `locId` (`locId`),
  KEY `endIpNum` (`endIpNum`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_geoip_block_1` (
  `startIpNum` int(20) unsigned NOT NULL default '0',
  `endIpNum` int(20) unsigned NOT NULL default '0',
  `locId` int(11) NOT NULL,
  KEY `locId` (`locId`),
  KEY `endIpNum` (`endIpNum`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_geoip_block_2` (
  `startIpNum` int(20) unsigned NOT NULL default '0',
  `endIpNum` int(20) unsigned NOT NULL default '0',
  `locId` int(11) NOT NULL,
  KEY `locId` (`locId`),
  KEY `endIpNum` (`endIpNum`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_geoip_block_3` (
  `startIpNum` int(20) unsigned NOT NULL default '0',
  `endIpNum` int(20) unsigned NOT NULL default '0',
  `locId` int(11) NOT NULL,
  KEY `locId` (`locId`),
  KEY `endIpNum` (`endIpNum`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_geoip_block_4` (
  `startIpNum` int(20) unsigned NOT NULL default '0',
  `endIpNum` int(20) unsigned NOT NULL default '0',
  `locId` int(11) NOT NULL,
  KEY `locId` (`locId`),
  KEY `endIpNum` (`endIpNum`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_geoip_block_5` (
  `startIpNum` int(20) unsigned NOT NULL default '0',
  `endIpNum` int(20) unsigned NOT NULL default '0',
  `locId` int(11) NOT NULL,
  KEY `locId` (`locId`),
  KEY `endIpNum` (`endIpNum`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_geoip_block_6` (
  `startIpNum` int(20) unsigned NOT NULL default '0',
  `endIpNum` int(20) unsigned NOT NULL default '0',
  `locId` int(11) NOT NULL,
  KEY `locId` (`locId`),
  KEY `endIpNum` (`endIpNum`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_geoip_block_7` (
  `startIpNum` int(20) unsigned NOT NULL default '0',
  `endIpNum` int(20) unsigned NOT NULL default '0',
  `locId` int(11) NOT NULL,
  KEY `locId` (`locId`),
  KEY `endIpNum` (`endIpNum`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_geoip_cities` (
  `locId` int(11) NOT NULL,
  `country` varchar(5) NOT NULL,
  `region` varchar(100) NOT NULL,
  `city` varchar(255) NOT NULL,
  `postalCode` varchar(100) NOT NULL,
  `latitude` varchar(100) NOT NULL,
  `longitude` varchar(100) NOT NULL,
  PRIMARY KEY  (`locId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_infopages` (
  `id` int(11) NOT NULL auto_increment,
  `url` varchar(128) NOT NULL,
  `title` varchar(128) NOT NULL,
  `content` text NOT NULL,
  `visible` tinyint(1) NOT NULL default '1',
  PRIMARY KEY  (`id`),
  KEY `url` (`url`,`visible`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_invoices` (
  `id` int(11) NOT NULL auto_increment,
  `paid_id` varchar(64) NOT NULL default '',
  `recurring_id` int(11) NOT NULL default '0',
  `status` enum('Paid','Unpaid','Cancelled','Draft','Recurring','Refunded','Creditnote') NOT NULL,
  `client_id` int(11) NOT NULL,
  `date` date default NULL,
  `duedate` date default NULL,
  `paybefore` date NOT NULL,
  `datepaid` datetime NOT NULL,
  `subtotal` decimal(10,2) NOT NULL,
  `credit` decimal(10,2) NOT NULL,
  `tax` decimal(10,2) NOT NULL,
  `taxrate` decimal(10,2) NOT NULL,
  `tax2` decimal(10,2) NOT NULL,
  `taxrate2` decimal(10,2) NOT NULL,
  `total` decimal(10,2) NOT NULL default '0.00',
  `payment_module` int(11) NOT NULL,
  `currency_id` int(3) NOT NULL default '0',
  `rate` decimal(10,4) NOT NULL default '1.0000',
  `rate2` decimal(10,4) NOT NULL default '0.0000',
  `notes` text NOT NULL,
  `locked` tinyint(1) NOT NULL default '0',
  `metadata` text,
  PRIMARY KEY  (`id`),
  KEY `status` (`status`),
  KEY `client_id` (`client_id`),
  KEY `paid_id` (`paid_id`(32)),
  KEY `recurring_id` (`recurring_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_invoice_items` (
  `id` int(11) NOT NULL auto_increment,
  `invoice_id` int(11) NOT NULL,
  `type` enum('Domain Register','Domain Renew','Domain Transfer','Hosting','Addon','Other','Upgrade','Invoice','Config','Credit','FieldUpgrade','Field','Discount') NOT NULL,
  `item_id` int(11) NOT NULL default '0',
  `description` text NOT NULL,
  `amount` decimal(10,2) NOT NULL default '0.00',
  `taxed` tinyint(1) NOT NULL,
  `qty` int(4) NOT NULL default '1',
  PRIMARY KEY  (`id`),
  KEY `invid` (`invoice_id`),
  KEY `item_id` (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_knowledgebase` (
  `id` int(11) NOT NULL auto_increment,
  `language_id` int(11) NOT NULL default '1',
  `cat_id` int(11) NOT NULL,
  `title` varchar(128) NOT NULL,
  `body` text NOT NULL,
  `registered` tinyint(1) NOT NULL,
  `options` tinyint(1) NOT NULL,
  PRIMARY KEY  (`id`,`language_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_knowledgebase_cat` (
  `id` int(11) NOT NULL auto_increment,
  `parent_cat` int(11) NOT NULL,
  `name` varchar(128) NOT NULL,
  `description` text,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_mettered_prices` (
  `id` int(11) NOT NULL auto_increment,
  `variable_id` int(11) NOT NULL,
  `qty` int(11) NOT NULL,
  `qty_max` int(11) NOT NULL,
  `price` decimal(10,5) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `fk_variable` (`variable_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_mettered_reports` (
  `id` int(11) NOT NULL auto_increment,
  `account_id` int(11) NOT NULL,
  `variable_id` int(11) NOT NULL,
  `type` enum('Admin','Client','Log','Report','Invoice') NOT NULL default 'Log',
  `date_created` datetime NOT NULL,
  `output` text NOT NULL,
  `charge` decimal(10,5) NOT NULL,
  `currency_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `account_id` (`account_id`),
  KEY `variable_id` (`variable_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_mettered_values` (
  `id` int(11) NOT NULL auto_increment,
  `variable_id` int(11) NOT NULL,
  `account_id` int(11) NOT NULL,
  `date_created` datetime NOT NULL,
  `date_updated` datetime NOT NULL,
  `currency_id` int(11) NOT NULL default '0',
  `charge` decimal(10,5) NOT NULL,
  `qty` decimal(10,5) NOT NULL,
  `output` text NOT NULL,
  `rawoutput` text,
  PRIMARY KEY  (`id`),
  KEY `fk_var` (`variable_id`),
  KEY `account_id` (`account_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_mettered_variables` (
  `id` int(11) NOT NULL auto_increment,
  `product_id` int(11) NOT NULL,
  `name` varchar(45) NOT NULL,
  `variable_name` varchar(45) NOT NULL,
  `unit_name` varchar(45) NOT NULL,
  `cycle` enum('Account','Hourly','Monthly') NOT NULL default 'Account',
  `scheme` enum('unit','volume','tiered','stairstep','overage') NOT NULL default 'unit',
  `options` int(11) NOT NULL,
  `date_created` datetime default NULL,
  PRIMARY KEY  (`id`),
  KEY `pid` (`product_id`),
  KEY `variable` (`variable_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_mod_rssfeeds` (
  `fetchdate` date NOT NULL,
  `pubDate` date NOT NULL,
  `title` varchar(128) NOT NULL,
  `link` varchar(256) NOT NULL,
  `description` text NOT NULL,
  PRIMARY KEY  (`title`(120))
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_notes` (
  `id` int(10) NOT NULL auto_increment,
  `type` enum('client','account','domain','order','draft') NOT NULL,
  `rel_id` int(10) NOT NULL,
  `admin_id` int(10) NOT NULL,
  `date` datetime NOT NULL,
  `note` text NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `type` (`type`),
  KEY `rel_id` (`rel_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_orders` (
  `id` int(11) NOT NULL auto_increment,
  `number` int(11) NOT NULL,
  `payment_module` int(11) NOT NULL,
  `client_id` int(11) NOT NULL,
  `invoice_id` int(11) NOT NULL default '0',
  `total` decimal(10,2) NOT NULL,
  `date_created` datetime NOT NULL,
  `status` enum('Pending','Active','Cancelled','Fraud') NOT NULL,
  `order_ip` text NOT NULL,
  `notes` text,
  `metadata` text NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `number` (`number`),
  KEY `invoice_id` (`invoice_id`),
  KEY `client_id` (`client_id`),
  KEY `date_created` (`date_created`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_order_drafts` (
  `id` int(10) NOT NULL auto_increment,
  `payment_module` int(10) NOT NULL,
  `client_id` int(10) NOT NULL,
  `total` decimal(10,2) NOT NULL,
  `date_created` datetime NOT NULL,
  `options` tinyint(4) NOT NULL default '7',
  PRIMARY KEY  (`id`),
  KEY `client_id` (`client_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_order_drafts_items` (
  `draft_id` int(10) unsigned NOT NULL,
  `item_id` int(10) NOT NULL,
  `item_type` varchar(50) NOT NULL,
  `settings` text NOT NULL,
  PRIMARY KEY  (`draft_id`,`item_id`,`item_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_order_log` (
  `id` int(11) NOT NULL auto_increment,
  `order_id` int(11) NOT NULL,
  `date` datetime NOT NULL,
  `type` varchar(100) NOT NULL default '',
  `entry` text NOT NULL,
  `who` varchar(127) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `order_id` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_order_scenarios` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(127) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
##########
INSERT INTO `hb_order_scenarios` (`id`, `name`) VALUES
(1, 'Default scenario');
##########
CREATE TABLE `hb_order_scenarios_steps` (
  `id` int(11) NOT NULL auto_increment,
  `scenario_id` int(11) NOT NULL,
  `name` varchar(127) NOT NULL,
  `auto` tinyint(1) NOT NULL default '1',
  `enabled` tinyint(1) NOT NULL default '1',
  PRIMARY KEY  (`id`),
  KEY `scenario_id` (`scenario_id`),
  KEY `name` (`name`(16))
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;
##########
INSERT INTO `hb_order_scenarios_steps` (`id`, `scenario_id`, `name`, `auto`, `enabled`) VALUES
(1, 1, 'FraudCheck', 1, 1),
(2, 1, 'SendInvoice', 1, 1),
(3, 1, 'Authorize', 1, 1),
(4, 1, 'Capture', 1, 1),
(5, 1, 'Provision', 1, 1),
(6, 1, 'FinalReview', 0, 0);
##########
CREATE TABLE `hb_order_steps` (
  `order_id` int(11) NOT NULL,
  `step_id` int(11) NOT NULL,
  `status` enum('Pending','Completed','Failed') NOT NULL default 'Pending',
  `date_changed` datetime NOT NULL,
  `output` text NOT NULL,
  PRIMARY KEY  (`order_id`,`step_id`),
  KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_product_bundles` (
  `product_id` int(10) NOT NULL,
  `options` tinyint(4) NOT NULL,
  `valid_from` date NOT NULL,
  `valid_to` date NOT NULL,
  `draft` int(10) default NULL,
  PRIMARY KEY  (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_products` (
  `id` int(11) NOT NULL auto_increment,
  `category_id` int(11) NOT NULL,
  `type` tinyint(2) NOT NULL,
  `name` text NOT NULL,
  `description` text NOT NULL,
  `visible` tinyint(1) NOT NULL,
  `domain_options` tinyint(1) NOT NULL,
  `stock` tinyint(1) NOT NULL default '0',
  `qty` int(1) NOT NULL default '0',
  `autosetup` tinyint(1) NOT NULL,
  `subdomain` text NOT NULL,
  `owndomain` tinyint(1) NOT NULL default '1',
  `tax` tinyint(1) NOT NULL,
  `upgrades` text NOT NULL,
  `sort_order` int(3) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `category_id` (`category_id`),
  KEY `type` (`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_products_freetlds` (
  `product_id` int(11) NOT NULL,
  `tlds` varchar(128) NOT NULL,
  `periods` varchar(256) NOT NULL,
  PRIMARY KEY  (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_products_modules` (
  `product_id` int(11) NOT NULL,
  `module` int(11) NOT NULL,
  `main` tinyint(1) NOT NULL default '1',
  `server` varchar(128) NOT NULL default '',
  `options` text NOT NULL,
  PRIMARY KEY  (`product_id`,`module`),
  KEY `main` (`main`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_product_types` (
  `id` int(11) NOT NULL auto_increment,
  `type` varchar(32) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `type` (`type`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
##########
INSERT INTO `hb_product_types` (`id`, `type`) VALUES
(10, 'bundles'),
(6, 'Colocation'),
(3, 'Dedicated'),
(9, 'DomainsType'),
(7, 'Marketing'),
(5, 'Other'),
(2, 'Reseller'),
(4, 'Server'),
(1, 'Shared');
##########
CREATE TABLE `hb_recurring_invoices` (
  `id` int(11) NOT NULL auto_increment,
  `frequency` enum('1w','2w','4w','1m','2m','3m','6m','1y','2y') NOT NULL default '1w',
  `start_from` date NOT NULL,
  `next_invoice` date NOT NULL,
  `occurrences` int(11) NOT NULL default '0',
  `invoices_left` int(11) NOT NULL default '0',
  `recstatus` enum('Active','Stopped','Finished','Pending') NOT NULL default 'Pending',
  PRIMARY KEY  (`id`),
  KEY `occurrences` (`occurrences`),
  KEY `recstatus` (`recstatus`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
INSERT INTO `hb_reports` (`id`, `type`, `name`, `query`, `options`) VALUES
(1, 'Orders', 'Orders list in selected period', 'SELECT\r\n	o.id as `Order ID`,\r\n	o.number as `Order Number`,\r\n	COALESCE(m.modname,''None'') AS `Payment Gateway`,\r\n	o.client_id AS `Client ID`,\r\n	ca.email AS `Client Email`,\r\n	CONCAT(cd.firstname,'' '',cd.lastname) AS `Client`,\r\n	hb_currency(o.total) AS `Order Total`,\r\n	o.order_ip AS `Order IP`,\r\n	hb_date(o.date_created) AS `Order Date`,\r\n	o.status AS `Current Status`,\r\n	COALESCE(cp.code,''None'') AS `Promotional Coupon`,\r\n	COALESCE(clog.discount,''0.00'') AS `Discount`\r\n\r\n\r\nFROM\r\n	hb_orders o JOIN\r\n	hb_modules_configuration m ON (m.id=o.payment_module) JOIN\r\n	hb_client_details cd ON (cd.id = o.client_id) JOIN\r\n	hb_client_access ca ON (ca.id=cd.id) LEFT JOIN\r\n	hb_coupons_log clog ON (clog.order_id=o.id) LEFT JOIN\r\n	hb_coupons cp ON (cp.id=clog.coupon_id)\r\nWHERE\r\n	o.date_created > :date_bottom	  AND\r\n	o.date_created < :date_top\r\n\r\n\r\nORDER BY\r\n	o.date_created ASC', 1),
(2, 'Invoices', 'List of all invoices in given period', 'SELECT\r\ni.id AS `Invoice ID`,\r\nCONCAT(d.firstname,'' '',d.lastname) AS `Client`,\r\nhb_currency(i.total,i.currency_id,i.rate) AS `Invoice Total`,\r\ni.status AS `Invoice Status`,\r\nCASE WHEN hbc.value=''eu'' THEN hb_date( i.datepaid) ELSE hb_date( i.date) END AS `Invoice Date`,\r\nhb_currency(i.credit,i.currency_id,i.rate) AS `Invoice Credit`,\r\nm.modname AS `Gateway Name`,\r\ntaxrate as `Tax1 rate`,\r\ntaxrate2 as `Tax2 rate`,\r\ntax as `Tax1 Amount`,\r\ntax2 as `Tax2 Amount`,\r\ni.rate AS `Exchange Rate`,\r\nhb_date(i.duedate) AS `Invoice Due Date`,\r\nhb_date(i.datepaid) AS `Invoice Paid Date`,\r\nt.trans_id AS `Related transaction`,\r\nca.email AS `Client Email`,\r\nd.phonenumber AS `Client Phone`,\r\nd.companyname  AS `Client Company`,\r\nd.city AS `Client City`,\r\nd.country AS `Client Country`,\r\nd.address1 AS `Client Address`,\r\nd.postcode AS `Client ZIP`\r\n\r\nFROM hb_invoices i\r\n    JOIN hb_client_details d ON (i.client_id=d.id)\r\n    JOIN hb_client_access ca ON (i.client_id=ca.id)\r\n    LEFT JOIN hb_modules_configuration m ON (m.id=i.payment_module)\r\n    LEFT JOIN hb_currencies c ON (c.id=i.currency_id)\r\n    LEFT JOIN hb_transactions t ON (t.invoice_id=i.id)\r\n    LEFT JOIN hb_configuration hbc ON hbc.setting=''InvoiceModel''\r\nWHERE\r\n    i.id NOT IN (\r\n        SELECT invoice_id\r\n        FROM hb_invoice_items\r\n        WHERE `type` = ''Invoice''\r\n    ) \r\n    AND i.status NOT IN (''Draft'',''Recurring'')\r\n   AND ( (hbc.value!=''eu'' AND i.`date`>=  :date_bottom AND i.`date`<= :date_top ) OR (hbc.value=''eu'' AND i.`datepaid`>= :date_bottom AND i.`datepaid`<= :date_top) )\r\nORDER BY\r\n	i.id ASC', 1),
(3, 'Clients', 'Top 15 customers by income', 'SELECT\r\ncd.id AS `Client ID`,\r\nCONCAT(cd.firstname,'' '',cd.lastname) AS `Client`,\r\nhb_currency(SUM(t.`in`),cb.currency_id) AS `Total Income`\r\nFROM hb_client_details cd\r\nJOIN hb_client_billing cb ON (cb.client_id=cd.id)\r\nJOIN hb_transactions t ON (t.client_id=cd.id)\r\nGROUP BY cd.id\r\nORDER BY SUM(t.`in`) DESC\r\nLIMIT 15', 1),
(4, 'Support', 'Average number of ticket replies per department', 'SELECT\r\nd.name AS `Department`,\r\n(a.tot/COUNT(t.id)) AS `Average replies`\r\nFROM hb_ticket_departments d JOIN\r\n(\r\n SELECT COUNT(r.id) as tot, t.dept_id FROM hb_ticket_replies r JOIN hb_tickets t ON (t.id=r.ticket_id)\r\n GROUP BY t.dept_id\r\n) a ON (a.dept_id=d.id)\r\nJOIN hb_tickets t ON (t.dept_id=d.id)\r\nGROUP BY t.dept_id\r\n', 1),
(5, 'Support', 'Unresolved tickets count by staff member', 'SELECT aa.id AS `Staff ID` , CONCAT( ad.firstname, '' '', ad.lastname ) AS `Staff Member` , COUNT( t.id ) AS `Unresolved Tickets Count`\r\nFROM hb_admin_access aa\r\nJOIN hb_admin_details ad ON ( aa.id = ad.id )\r\nJOIN hb_tickets t ON ( t.owner_id = aa.id )\r\nWHERE t.status!=''Closed''\r\nGROUP BY t.owner_id', 1),
(6, 'Support', 'Unresolved tickets count by department member', 'SELECT d.id AS `Department ID` , d.name AS `Department Name` , COUNT( t.id ) AS `Unresolved Tickets Count`\r\nFROM hb_tickets t\r\nJOIN hb_ticket_departments d ON ( t.dept_id = d.id )\r\nWHERE t.status != ''Closed''\r\nGROUP BY t.dept_id', 1),
(7, 'Clients', 'List of clients that are Active, but dont have any active service', 'SELECT\r\ncd.id AS `Client ID`,\r\nCONCAT(cd.firstname,'' '',cd.lastname) AS `Client`\r\nFROM hb_client_details cd\r\nWHERE cd.id NOT IN (SELECT client_id FROM hb_accounts WHERE status=''Active'')\r\nAND cd.id NOT IN (SELECT client_id FROM hb_domains WHERE status=''Active'')\r\nAND cd.id NOT IN (SELECT a.client_id FROM hb_accounts a JOIN hb_accounts_addons ad ON (ad.account_id=a.id) WHERE ad.status=''Active'')\r\nORDER BY cd.id ASC', 1),
(8, 'Clients', 'List of clients with active services, with inactive profile (disabled login)', 'SELECT\r\ncd.id AS `Client ID`,\r\nCONCAT(cd.firstname,'' '',cd.lastname) AS `Client`\r\nFROM hb_client_details cd\r\nJOIN hb_client_access ca ON (ca.id=cd.id)\r\nWHERE (cd.id IN (SELECT client_id FROM hb_accounts WHERE status=''Active'')\r\nOR cd.id IN (SELECT client_id FROM hb_domains WHERE status=''Active'')\r\nOR cd.id IN  (SELECT a.client_id FROM hb_accounts a JOIN hb_accounts_addons ad ON (ad.account_id=a.id) WHERE ad.status=''Active''))\r\nAND ca.status!=''Active''\r\nORDER BY cd.id ASC', 1),
(9, 'Clients', 'List of possible duplicates in client profiles', 'SELECT\r\ncd.id AS `Client ID`,\r\nCONCAT(cd.firstname,'' '',cd.lastname) AS `Client`,\r\nca.email AS `Client Email`\r\nFROM hb_client_details cd\r\nJOIN hb_client_access ca ON (ca.id=cd.id)\r\nINNER JOIN (\r\n SELECT firstname, lastname FROM hb_client_details GROUP BY firstname,lastname HAVING COUNT(id)>1\r\n) dup ON (dup.firstname=cd.firstname AND dup.lastname=cd.lastname)\r\nORDER BY cd.firstname,cd.lastname ASC', 1),
(10, 'Orders', 'Total transaction fees by gateway', 'SELECT\r\nm.modname AS `Gateway Name`,\r\nhb_currency(SUM(t.fee),t.currency_id,t.rate) AS `Total Fee`\r\nFROM hb_modules_configuration m\r\nJOIN hb_transactions t ON (t.module=m.id)\r\nGROUP BY t.module', 1),
(11, 'Orders', 'Total transactions count by gateway', 'SELECT\r\nm.modname AS `Gateway Name`,\r\nCOUNT(t.id) AS `Transactions count`\r\nFROM hb_modules_configuration m\r\nJOIN hb_transactions t ON (t.module=m.id)\r\nGROUP BY t.module', 1),
(12, 'Support', 'List of tickets due today (based on SLA)', 'SELECT\r\nt.subject AS `Ticket Subject`,\r\nt.ticket_number AS `Ticket ID`,\r\nd.name AS `Department`,\r\nt.status AS `Ticket Status`,\r\nt.name AS `Submitter Name`,\r\nt.email AS `Submitter Email`,\r\nt.`date` AS `Ticket Date`,\r\nt.resolve_date AS `Ticket Due Date`\r\nFROM hb_tickets t\r\nJOIN hb_ticket_departments d ON ( t.dept_id = d.id )\r\nWHERE t.status != ''Closed''\r\nAND\r\nt.resolve_date!=''0000-00-00 00:00:00''\r\nAND\r\nDATE(t.resolve_date)=DATE(NOW())', 1),
(13, 'Support', 'List of overdue tickets (based on SLA)', 'SELECT\r\nt.subject AS `Ticket Subject`,\r\nt.ticket_number AS `Ticket ID`,\r\nd.name AS `Department`,\r\nt.status AS `Ticket Status`,\r\nt.name AS `Submitter Name`,\r\nt.email AS `Submitter Email`,\r\nt.`date` AS `Ticket Date`,\r\nt.resolve_date AS `Ticket Due Date`\r\nFROM hb_tickets t\r\nJOIN hb_ticket_departments d ON ( t.dept_id = d.id )\r\nWHERE t.status != ''Closed''\r\nAND\r\nt.resolve_date!=''0000-00-00 00:00:00''\r\nAND\r\nt.resolve_date < NOW()', 1),
(14, 'Support', 'List of tickets rated less than 3 points this month', 'SELECT\r\nt.subject AS `Ticket Subject`,\r\nt.ticket_number AS `Ticket ID`,\r\nd.name AS `Department`,\r\nt.status AS `Ticket Status`,\r\nt.name AS `Submitter Name`,\r\nt.email AS `Submitter Email`,\r\nt.`date` AS `Ticket Date`\r\nFROM hb_tickets t\r\nJOIN hb_ticket_departments d ON ( t.dept_id = d.id )\r\nWHERE t.id IN (\r\n    SELECT r.ticket_id FROM hb_ticket_replies r JOIN  `hb_ticket_replies_rating` rate ON (rate.reply_id=r.id)\r\n    WHERE rate.rating<3\r\n)\r\nORDER BY t.date ASC', 1),
(15, 'Invoices', 'List of overdue invoices', '\r\nSELECT\r\ni.id AS `Invoice ID`,\r\nCONCAT(d.firstname,'' '',d.lastname) AS `Client`,\r\nhb_currency(i.total,i.currency_id,i.rate) AS `Invoice Total`,\r\ni.status AS `Invoice Status`,\r\ni.duedate AS `Invoice Due Date`,\r\nm.modname AS `Gateway Name`,\r\ntaxrate as `Tax1 rate`,\r\ntaxrate2 as `Tax2 rate`,\r\ntax as `Tax1 Amount`,\r\ntax2 as `Tax2 Amount`,\r\ni.rate AS `Exchange Rate`,\r\ni.date AS `Invoice Date`,\r\nca.email AS `Client Email`,\r\nd.phonenumber AS `Client Phone`,\r\nd.companyname  AS `Client Company`,\r\nd.city AS `Client City`,\r\nd.country AS `Client Country`,\r\nd.address1 AS `Client Address`,\r\nd.postcode AS `Client ZIP`,\r\nd.id AS `Client ID`\r\n\r\nFROM hb_invoices i\r\n    JOIN hb_client_details d ON (i.client_id=d.id)\r\n    JOIN hb_client_access ca ON (i.client_id=ca.id)\r\n    LEFT JOIN hb_modules_configuration m ON (m.id=i.payment_module)\r\n    LEFT JOIN hb_currencies c ON (c.id=i.currency_id)\r\nWHERE\r\n    i.id NOT IN (\r\n        SELECT invoice_id\r\n        FROM hb_invoice_items\r\n        WHERE `type` = ''Invoice''\r\n    )\r\n    AND i.status IN (''Unpaid'')\r\n    AND i.duedate >= NOW()\r\n\r\nORDER BY\r\n	i.id ASC', 1),
(16, 'Staff', 'Hours spent online by staff this week', '\r\nSELECT\r\nl.admin_id AS `Staff ID`,\r\nCONCAT( ad.firstname, '' '', ad.lastname ) AS `Staff Member`,\r\nSUM(TIME_TO_SEC(TIMEDIFF(l.logout,l.login)))/3600 as `Hours Online`\r\nFROM hb_admin_log l \r\nJOIN hb_admin_details ad ON (ad.id=l.admin_id)\r\nWHERE\r\n YEARWEEK(l.login) = YEARWEEK(NOW())\r\n GROUP BY admin_id', 1),
(17, 'Staff', 'Hours spent online by staff this month', '\r\nSELECT\r\nl.admin_id AS `Staff ID`,\r\nCONCAT( ad.firstname, '' '', ad.lastname ) AS `Staff Member`,\r\nSUM(TIME_TO_SEC(TIMEDIFF(l.logout,l.login)))/3600 as `Hours Online`\r\nFROM hb_admin_log l\r\nJOIN hb_admin_details ad ON (ad.id=l.admin_id)\r\nWHERE\r\n MONTH(l.login) = MONTH(NOW())\r\nAND\r\n YEAR(l.login) = YEAR(NOW())\r\n GROUP BY admin_id', 1),
(18, 'Support', 'Number of support ticket opened per service in given period', 'SELECT\r\nc.name AS `Product Category`,\r\np.name AS `Product Name`,\r\nCOUNT(t.id) AS `Tickets count`\r\nFROM hb_products p\r\nJOIN hb_categories c ON (c.id=p.category_id)\r\nJOIN hb_accounts a ON (a.product_id=p.id)\r\nJOIN hb_tickets t ON (t.client_id=a.client_id)\r\nWHERE\r\n    t.date >= :date_bottom\r\n    AND t.date <= :date_top\r\nGROUP BY p.id\r\n', 1),
(19, 'Invoices', 'Income from manually created invoices in given period', 'SELECT\r\n hb_currency(SUM(t.`in`),t.currency_id,t.rate) AS `Total Income`\r\nFROM\r\n hb_transactions t\r\n JOIN hb_invoices i ON (t.invoice_id=i.id)\r\n WHERE\r\n i.id IN (SELECT invoice_id FROM hb_invoice_items WHERE  `type`=''Other'')\r\n AND\r\n    t.date >= :date_bottom\r\n    AND t.date <= :date_top\r\n', 1),
(20, 'Invoices', 'List of overdue but non-terminated recurring accounts', 'SELECT\r\n a.id AS `Account ID`,\r\n c.name AS `Category Name`,\r\n p.name AS `Product Name`,\r\n a.status AS `Account Status`,\r\n a.next_due AS `Account next due date`\r\n\r\nFROM hb_accounts a\r\n JOIN hb_products p ON (p.id=a.product_id)\r\n JOIN hb_categories c ON (c.id=p.category_id)\r\nWHERE\r\n a.status NOT IN (''Terminated'',''Cancelled'',''Fraud'',''Pending'')\r\nAND\r\n a.billingcycle NOT IN (''Free'',''One Time'')\r\nAND\r\n a.id IN (\r\n SELECT it.item_id FROM hb_invoice_items it JOIN hb_invoices i ON (i.id=it.invoice_id)\r\n WHERE it.type=''Hosting'' AND i.status=''Unpaid''\r\n)', 1),
(21, 'Invoices', 'Total sales tax liability in selected period', 'SELECT\r\n hb_currency(SUM(i.tax)) AS `Total Tax L1`,\r\n hb_currency(SUM(i.tax2)) AS `Total Tax L2`,\r\n hb_currency(SUM(i.total)) AS `Invoice totals`,\r\n hb_currency(SUM(i.subtotal)) AS `Invoice subtotals`,\r\n hb_currency(SUM(i.credit)) AS `Invoice credits`\r\nFROM hb_invoices i\r\n WHERE i.status=''Paid''\r\n AND  i.datepaid>= :date_bottom\r\n    AND i.datepaid<= :date_top', 1),
(22, 'Invoices', 'Sales tax liability in selected period, converted to main currency', '\r\nSELECT\r\ni.id AS `Invoice ID`,\r\nCONCAT(d.firstname,'' '',d.lastname) AS `Client`,\r\nhb_currency(i.total) AS `Invoice Subtotal`,\r\nhb_currency(i.credit) AS `Invoice Credit`,\r\nhb_currency(i.tax) AS `Invoice Tax L1`,\r\nhb_currency(i.tax2) AS `Invoice Tax L2`,\r\nhb_currency(i.total) AS `Invoice Total`,\r\nhb_date(i.datepaid) AS `Invoice Paid Date`,\r\nhb_date(i.date) AS `Invoice Date`,\r\nm.modname AS `Gateway Name`,\r\nt.trans_id AS `Related transaction`\r\n\r\nFROM hb_invoices i\r\n    JOIN hb_client_details d ON (i.client_id=d.id)\r\n    JOIN hb_client_access ca ON (i.client_id=ca.id)\r\n    LEFT JOIN hb_modules_configuration m ON (m.id=i.payment_module)\r\n    LEFT JOIN hb_transactions t ON (t.invoice_id=i.id)\r\nWHERE\r\n    i.id NOT IN (\r\n        SELECT invoice_id\r\n        FROM hb_invoice_items\r\n        WHERE `type` = ''Invoice''\r\n    )\r\n    AND i.status IN (''Paid'')\r\n    AND i.datepaid>= :date_bottom\r\n    AND i.datepaid<= :date_top\r\n\r\nORDER BY\r\n	i.id ASC', 1),
(23, 'Support', '15 clients with highest number of tickets', 'SELECT\r\n t.client_id AS `Client ID`,\r\n COUNT(t.id) AS `Total tickets`,\r\n CONCAT(cd.firstname,'' '',cd.lastname) AS `Client`,\r\n cd.datecreated AS `Client since`\r\nFROM hb_tickets t JOIN\r\n hb_client_details cd ON (cd.id=t.client_id)\r\n\r\nGROUP BY t.client_id\r\nORDER BY `Total tickets` DESC\r\nLIMIT 15', 1),
(24, 'Clients', 'List all suspended accounts with their suspension dates', 'SELECT\r\n a.id AS `Account ID`,\r\n MAX(s.`date`) AS `Suspension Date`,\r\n a.client_id AS `Client ID`,\r\n CONCAT(cd.firstname,'' '',cd.lastname) AS `Client`,\r\n a.`domain` AS `Account Domain`,\r\n a.date_created AS `Creation date`,\r\n a.billingcycle AS `Billing cycle`,\r\n a.username AS `Username`\r\nFROM\r\n     hb_accounts a JOIN\r\n     hb_client_details cd ON (cd.id=a.client_id) JOIN\r\n     `hb_account_logs` s ON (s.account_id=a.id)\r\nWHERE\r\n     a.status=''Suspended''\r\n    AND s.`event`=''AccountSuspend'' \r\n    AND s.`result`=''1''\r\nGROUP BY a.id\r\nORDER BY `Suspension Date` DESC', 1),
(25, 'Orders', 'All transactions informations in given period with purchased items details', 'SELECT t.trans_id AS `Transaction ID`, t.date AS `Transaction Date`, hb_currency(t.`in`,t.`currency_id`,t.rate) AS `Amount In`, it.description AS `Invoice Item`, hb_currency(t.`fee`,t.`currency_id`,t.rate) AS `Transaction Fees`, CONCAT(d.firstname,'' '',d.lastname) AS `Client`, t.description AS `Transaction Description`, hb_currency(t.`out`,t.`currency_id`,t.rate) AS `Amount Out`, t.client_id AS `Client ID`, t.invoice_id AS `Invoice ID`, m.modname AS `Payment Gateway` FROM hb_transactions t JOIN hb_invoices i ON (i.id=t.invoice_id) JOIN hb_invoice_items it ON (it.invoice_id=i.id) JOIN hb_client_details d ON (d.id=t.client_id) JOIN hb_modules_configuration m ON (m.id=t.`module`) WHERE t.date>= :date_bottom AND t.date<= :date_top ORDER BY t.date DESC', 1),
(26, 'Support', 'Weekend support by staff member in given period', '\r\nSELECT COUNT(DISTINCT t.`id`) AS `Tickets answered`,  r.name AS `Staff Name`\r\nFROM hb_tickets t\r\nJOIN hb_ticket_replies r ON (r.ticket_id=t.id)\r\nWHERE\r\n r.type=''Admin''\r\n AND\r\n r.`date` > :date_bottom\r\nAND\r\n r.`date` < :date_top\r\n AND\r\n DAYOFWEEK(r.`date`) IN (1,7)\r\nGROUP BY\r\nr.replier_id\r\n)\r\n', 1),
(27, 'Support', 'Number of tickets closed per staff member in given period', 'SELECT SUBSTRING_INDEX(l.`action`,''member'',''-1'') AS `Staff member`, COUNT(t.`id`) AS `Total closed`, DATE(l.`date`) AS `Date` FROM `hb_tickets` t JOIN `hb_tickets_log` l ON (l.ticket_id=t.id) WHERE l.date>= :date_bottom AND l.date<= :date_top AND t.status=''Closed'' AND l.action LIKE ''%to "Closed" by%'' GROUP BY `Date`, `Staff member`', 1),
(28, 'Support', 'Detailed list of all tickets in given period', 'SELECT t.subject AS `Ticket Subject`, t.ticket_number AS `Ticket ID`, d.`name` AS `Department`, t.status AS `Ticket Status`, t.`name` AS `Submitter Name`, t.email AS `Submitter Email`, t.`date` AS `Ticket Date`, COALESCE(CONCAT(a.firstname,'' '',a.lastname),''-'') AS `Assigned Admin`, COALESCE(r.`rep`,''0'') AS `Number of Replies`, t.`lastreply` AS `Last Reply Date`, t.`priority` AS `Priority Level` FROM `hb_tickets` t JOIN `hb_ticket_departments` d ON ( t.dept_id = d.id ) LEFT JOIN (SELECT r.`ticket_id`, COUNT(r.`id`) AS `rep` FROM `hb_ticket_replies` r WHERE r.`status`!=''Draft'' GROUP BY r.`ticket_id`) r ON (r.ticket_id=t.id) LEFT JOIN `hb_admin_details` a ON (a.id=t.owner_id) WHERE t.`date`>= :date_bottom AND t.`date`<= :date_top ', 1),
(29, 'Orders', 'Income per product in given period', 'SELECT p.name AS `Product Name`, SUM(it.`amount`) AS `Product Income`, p.id AS `Product ID` FROM hb_invoices i JOIN hb_invoice_items it ON (it.invoice_id=i.id) JOIN hb_accounts a ON (a.id=it.item_id AND it.type=''Hosting'') JOIN hb_products p ON (p.id=a.product_id) WHERE i.status=''Paid'' AND i.`datepaid`>= :date_bottom AND i.`datepaid`<= :date_top GROUP BY p.id ORDER BY `Product Income` DESC', 1),
(30, 'Orders', 'Number of sales per product in given period', 'SELECT c.name as `Category Name`,\r\np.name as `Product Name`,\r\nCOUNT(a.id) as `Items Sold`,\r\np.id as `Product ID` \r\nFROM hb_invoices `i` \r\nINNER JOIN hb_invoice_items `it` ON (it.invoice_id = i.id) \r\nINNER JOIN hb_accounts `a` ON (a.id = it.item_id \r\nAND it.type = ''Hosting'') \r\nINNER JOIN hb_products `p` ON (a.product_id = p.id) \r\nINNER JOIN hb_categories `c` ON (c.id = p.category_id) \r\nWHERE\r\n i.status = ''Paid'' \r\nAND i.datepaid > :date_bottom \r\nAND i.datepaid < :date_top \r\nAND i.id IN (SELECT invoice_id \r\nFROM hb_orders) \r\nGROUP BY p.id \r\n\r\nORDER BY `Items Sold` DESC', 1);
##########
CREATE TABLE `hb_servers` (
  `id` int(10) NOT NULL auto_increment,
  `group_id` int(11) NOT NULL default '0',
  `default` tinyint(1) NOT NULL default '0',
  `name` text NOT NULL,
  `username` varchar(128) NOT NULL,
  `password` varchar(128) NOT NULL,
  `ip` text NOT NULL,
  `host` text NOT NULL,
  `status_url` text NOT NULL,
  `ns1` varchar(128) NOT NULL,
  `ns2` varchar(128) NOT NULL,
  `ns3` varchar(128) NOT NULL,
  `ns4` varchar(128) NOT NULL,
  `ip1` varchar(128) NOT NULL,
  `ip2` varchar(128) NOT NULL,
  `ip3` varchar(128) NOT NULL,
  `ip4` varchar(128) NOT NULL,
  `max_accounts` int(11) NOT NULL default '0',
  `default_module` int(11) NOT NULL,
  `hash` text NOT NULL,
  `secure` tinyint(1) NOT NULL default '0',
  `enable` tinyint(1) NOT NULL default '1',
  `field1` text NOT NULL,
  `field2` text NOT NULL,
  `custom` text NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `def` (`default_module`),
  KEY `group_id` (`group_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_server_groups` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(64) NOT NULL default '',
  `module` int(11) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_subproducts` (
  `product_id` int(11) NOT NULL,
  `subproduct_id` int(11) NOT NULL,
  PRIMARY KEY  (`product_id`,`subproduct_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_subscription_items` (
  `id` int(11) NOT NULL auto_increment,
  `subscr_id` int(11) NOT NULL,
  `item_id` int(11) NOT NULL,
  `client_id` int(11) NOT NULL,
  `type` enum('Hosting','Addon') NOT NULL,
  `description` text NOT NULL,
  `next_date` date NOT NULL,
  `period` int(2) NOT NULL,
  `amount` decimal(10,2) NOT NULL default '0.00',
  `invoice_id` int(11) NOT NULL,
  `gateway_subid` text NOT NULL,
  `gateway_name` text NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `subscr_id` (`subscr_id`),
  KEY `invoice_id` (`invoice_id`),
  KEY `client_id` (`client_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_tags` (
  `id` int(11) NOT NULL auto_increment,
  `tag` varchar(128) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `tag` (`tag`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_task_list` (
  `id` int(11) NOT NULL auto_increment,
  `task` varchar(128) NOT NULL,
  `when` enum('before','after') NOT NULL default 'before',
  `event` varchar(128) NOT NULL,
  `interval` int(11) NOT NULL default '0',
  `interval_type` enum('DAY','HOUR','MINUTE') NOT NULL default 'DAY',
  `rel_type` enum('Hosting','Addon','Ticket','Domain') NOT NULL default 'Hosting',
  `rel_id` int(11) NOT NULL default '0',
  `action_config` text NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `when` (`when`,`event`,`interval`),
  KEY `rel_type` (`rel_type`,`rel_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
##########
INSERT INTO `hb_task_list` (`id`, `task`, `when`, `event`, `interval`, `interval_type`, `rel_type`, `rel_id`, `action_config`) VALUES
(1, 'nan', 'before', 'AccountSuspend', 0, 'DAY', 'Hosting', 0, ''),
(2, 'nan', 'before', 'AccountTerminate', 0, 'DAY', 'Hosting', 0, '');
##########
CREATE TABLE `hb_task_log` (
  `id` int(11) NOT NULL auto_increment,
  `task_id` int(11) NOT NULL,
  `rel_type` enum('Hosting','Addon','Domain') NOT NULL default 'Hosting',
  `rel_id` int(11) NOT NULL,
  `date_created` datetime NOT NULL,
  `date_execute` datetime NOT NULL,
  `status` enum('Pending','OK','Failed','Canceled') NOT NULL default 'Pending',
  `log` text NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `task_id` (`task_id`,`status`),
  KEY `rel_type` (`rel_type`,`rel_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_tax` (
  `id` int(11) NOT NULL auto_increment,
  `rate` decimal(10,2) NOT NULL default '0.00',
  `type` enum('L1','L2') NOT NULL,
  `name` varchar(128) NOT NULL,
  `state` text NOT NULL,
  `country` varchar(2) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `country` (`country`),
  KEY `state` (`state`(2))
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_templates` (
  `id` int(11) NOT NULL auto_increment,
  `parent_id` int(11) NOT NULL,
  `name` varchar(45) NOT NULL,
  `target` enum('admin','user','invoice','estimate','creditnote') NOT NULL,
  `created` datetime NOT NULL,
  `updated` datetime NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `KEY` (`parent_id`),
  KEY `KEY2` (`name`),
  KEY `KEY3` (`target`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
##########
INSERT INTO `hb_templates` (`id`, `parent_id`, `name`, `target`, `created`, `updated`) VALUES
(1, 0, 'Default', 'invoice', '2011-12-06 00:00:00', '2011-12-06 00:00:00'),
(100, 0, 'EU Invoicing', 'invoice', '2013-05-16 03:30:01', '2013-05-16 03:30:01'),
(101, 0, 'Default', 'estimate', '2013-05-21 00:00:00', '2013-05-21 00:00:00'),
(102, 0, 'Default', 'creditnote', '2013-10-17 10:25:44', '2013-10-17 10:25:44');
##########
CREATE TABLE `hb_template_content` (
  `id` int(11) NOT NULL auto_increment,
  `template_id` int(11) NOT NULL,
  `name` varchar(45) NOT NULL,
  `content` text NOT NULL,
  `checksum` varchar(32) NOT NULL,
  `updated` datetime NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `KEY` (`template_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
##########
INSERT INTO `hb_template_content` (`id`, `template_id`, `name`, `content`, `checksum`, `updated`) VALUES
(1, 1, 'Default', '<style type="text/css">\r\n    body {\r\n        font-family: Arial;\r\n    }\r\n</style>\r\n<div style="padding:40px;font-family: Arial;" id="invoice-table">\r\n    <table border="0" cellspacing="0" width="100%">\r\n        <tbody>\r\n            <tr id="invoice-header">\r\n                <td width="66%" valign="middle" align="left" colspan="2" style="padding-left:10px">\r\n                    <span style="font-size:18px;font-weight:bold;color:#484740;">{$companylogo}</span>\r\n                    <span style="font-size:18px;font-weight:bold;color:#A4A4A4">{$invoice.number}</span>\r\n                </td>\r\n                \r\n                <td valign="middle" align="right">\r\n                    <table cellpadding="2"  cellspacing="0" border="0">\r\n                        <tr>\r\n                            <td align="right"><span style="font-size:12px;font-weight:bold">{$lang.invoice}</span></td>\r\n                            <td align="left"><span style="font-size:12px">{$invoice.id}</span></td>\r\n                        </tr>\r\n                        <tr>\r\n                            <td align="right"><span style="font-size:12px;font-weight:bold">{$lang.invoice_date}</span></td>\r\n                            <td align="left"><span style="font-size:12px">{$invoice.date}</span></td>\r\n                        </tr>\r\n                        <tr>\r\n                            <td align="right"><span style="font-size:12px;font-weight:bold">{$lang.invoice_due}</span></td>\r\n                            <td align="left"><span style="font-size:12px">{$invoice.duedate}</span></td>\r\n                        </tr>\r\n                    </table>\r\n\r\n                </td>\r\n            </tr>\r\n\r\n\r\n            <tr id="invoice-details">\r\n                <td valign="top" style="padding-top:40px" width="33%">\r\n                    <span style="font-size:12px;font-weight:bold;">{$lang.invoice_to}</span><br/>\r\n                    <span style="font-size:11px">{$client.companyname}<br/>\r\n                        {$client.firstname} {$client.lastname}<br />\r\n                        {$client.address1} <br />\r\n                        {$client.address2}<br />\r\n                        {$client.city}, {$client.state}{$client.postcode}<br />\r\n                        {$client.country}\r\n                    </span>\r\n\r\n                </td>\r\n                <td valign="top"  style="padding-top:40px" width="33%">\r\n                    <span style="font-size:12px;font-weight:bold;">{$lang.invoice_from}</span><br/>\r\n                    <span style="font-size:11px">{$invoicefrom}</span>\r\n                </td>\r\n                <td valign="top"  style="padding-top:40px" align="left"  width="33%">\r\n                    <table cellpadding="2"  cellspacing="0" border="0">\r\n                        <tr>\r\n                            <td align="right"><span style="font-size:12px;font-weight:bold;">{$lang.status}:</span></td>\r\n                            <td align="left"><span style="font-size:12px;font-weight:bold;">{$invoice.status}</span></td>\r\n                        </tr>\r\n                        <tr>\r\n                            <td align="right"><span style="font-size:12px;font-weight:bold;">{$lang.balance}:</span></td>\r\n                            <td align="left"><span style="font-size:12px;font-weight:bold;">{$transbalance}</span></td>\r\n                        </tr>\r\n                    </table>\r\n                </td>\r\n            </tr>\r\n\r\n        </tbody>\r\n    </table>\r\n\r\n\r\n\r\n    <table class="invoice-table" width="100%" border="0" cellpadding="6" cellspacing="0" style="margin-top:50px;width:100%;">\r\n        <tbody>\r\n            <tr>\r\n                <td  align="left" bgcolor="#f0f0f0"  valign="middle" ><span style="font-size:11px;font-weight:bold">{$lang.invoice_desc}</span></td>\r\n                <td style="width:7%"  align="center" bgcolor="#f0f0f0" valign="middle" ><span style="font-size:11px;font-weight:bold">{$lang.invoice_tax}</span></td>\r\n                <td style="width:15%"  align="right" bgcolor="#f0f0f0" valign="middle" ><span style="font-size:11px;font-weight:bold">{$lang.unitcost}</span></td>\r\n                <td style="width:7%"  align="center" bgcolor="#f0f0f0" valign="middle" ><span style="font-size:11px;font-weight:bold">{$lang.qty}</span></td>\r\n                <td style="width:15%"  align="right" bgcolor="#f0f0f0" valign="middle" ><span style="font-size:11px;font-weight:bold">{$lang.price}</span></td>\r\n            </tr>\r\n\r\n\r\n            <tr>\r\n                <td style="border-bottom: 1px solid #F0F0F0;"  valign="middle" ><span style="font-size:11px">{$item.description}</span></td>\r\n                <td style="border-bottom: 1px solid #F0F0F0;" align="center"  valign="middle" ><span style="font-size:11px">{$item.taxed}</span></td>\r\n                <td style="border-bottom: 1px solid #F0F0F0;" align="right"  valign="middle" ><span style="font-size:11px">{$item.amount}</span></td>\r\n                <td style="border-bottom: 1px solid #F0F0F0;" align="center"  valign="middle" ><span style="font-size:11px">{$item.qty}</span></td>\r\n                <td style="border-bottom: 1px solid #F0F0F0;" align="right"  valign="middle" ><span style="font-size:11px">{$item.linetotal}</span></td>\r\n            </tr>\r\n\r\n            <tr>\r\n                <td class=""></td>\r\n                <td align="right" colspan="2"  valign="middle" ><span style="font-size:12px;font-weight:bold;">{$lang.subtotal}</span></td>\r\n                <td align="right" colspan="2"   valign="middle" ><span style="font-size:12px">{$invoice.subtotal}</span></td>\r\n            </tr>\r\n            <tr>\r\n                <td class=""></td>\r\n                <td align="right" colspan="2"  valign="middle" ><span style="font-size:12px;font-weight:bold;">{$lang.credit}</span></td>\r\n                <td align="right" colspan="2"   valign="middle" ><span style="font-size:12px">{$invoice.credit}</span></td>\r\n            </tr>\r\n\r\n            <tr>\r\n                <td class=""></td>\r\n                <td align="right" colspan="2"  valign="middle" ><span style="font-size:12px;font-weight:bold;">{$lang.tax} ({$invoice.taxrate}%)</span></td>\r\n                <td align="right" colspan="2"  valign="middle" ><span style="font-size:12px">{$invoice.tax}</span></td>\r\n            </tr>\r\n\r\n            <tr>\r\n                <td class=""></td>\r\n                <td align="right" colspan="2"   valign="middle" ><span style="font-size:12px;font-weight:bold;">{$lang.tax} ({$invoice.taxrate2}%)</span></td>\r\n                <td align="right" colspan="2"  valign="middle" ><span style="font-size:12px">{$invoice.tax2}</span></td>\r\n            </tr>\r\n\r\n\r\n            <tr>\r\n                <td class=""></td>\r\n                <td align="right" colspan="2"  valign="middle" ><span style="font-size:14px; font-weight:bold;">{$lang.total}</span></td>\r\n                <td align="right" colspan="2"   valign="middle" ><span style="font-size:14px; font-weight:bold;">{$invoice.total}</span></td>\r\n            </tr>\r\n        </tbody>\r\n    </table>\r\n\r\n\r\n    <table  width="100%" border="0" cellpadding="0" cellspacing="0"  style="margin-top:50px;">\r\n        <tr><td align="left"  valign="middle" style="width:100%">\r\n                <span style="font-size:11px;"><em>{$invoice.notes}</em></span>\r\n            </td></tr>\r\n    </table>\r\n\r\n\r\n\r\n    <table class="invoice-table-transaction" width="100%" border="0" cellpadding="6" cellspacing="0" style="margin-top:50px;">\r\n        <tbody>\r\n            <tr>\r\n                <td colspan="4" style="width:100%">\r\n                    <span style="font-size:11px;font-weight:bold;">{$lang.related_trans}</span>\r\n                </td>\r\n            </tr>\r\n            <tr>\r\n                <td   bgcolor="#f0f0f0" align="left"   valign="middle" ><span style="font-size:11px;font-weight:bold">{$lang.trans_date}</span></td>\r\n                <td  align="center" bgcolor="#f0f0f0"   valign="middle" ><span style="font-size:11px;font-weight:bold">{$lang.trans_gtw}</span></td>\r\n                <td  align="center" bgcolor="#f0f0f0"   valign="middle" ><span style="font-size:11px;font-weight:bold">{$lang.trans_id}</span></td>\r\n                <td  align="right" bgcolor="#f0f0f0"   valign="middle" ><span style="font-size:11px;font-weight:bold">{$lang.trans_amount}</span></td>\r\n            </tr>\r\n            <tr>\r\n                <td align="left"  valign="middle" style="border-bottom: 1px solid #F0F0F0;"><span style="font-size:11px">{$transaction.date}</span></td>\r\n                <td align="center"   valign="middle" style="border-bottom: 1px solid #F0F0F0;"><span style="font-size:11px">{$transaction.module}</span></td>\r\n                <td align="center"   valign="middle" style="border-bottom: 1px solid #F0F0F0;"><span style="font-size:11px">{$transaction.trans_id}</span></td>\r\n                <td align="right" valign="middle" style="border-bottom: 1px solid #F0F0F0;"><span style="font-size:11px">{$transaction.amount}</span></td>\r\n            </tr>\r\n            <tr>\r\n                <td class="" ></td>\r\n                <td class=""></td>\r\n                <td align="right"   valign="middle" ><span style="font-size:11px;font-weight:bold;">{$lang.balance}</span></td>\r\n                <td  align="right"   valign="middle" ><span style="font-size:11px;font-weight:bold;">{$transbalance}</span></td>\r\n            </tr>\r\n        </tbody>\r\n    </table>\r\n\r\n\r\n    <table  width="100%" border="0" cellpadding="0" cellspacing="0"  style="margin-top:50px;">\r\n        <tr><td align="center" style="border-top:1px solid #F0F0F0;padding-top:10px;width:100%">\r\n                <span style="font-size:11px;color:#A4A4A4">{$invoiceFooter}</span>\r\n            </td></tr>\r\n    </table>\r\n</div>', 'd41d8cd98f00b204e9800998ecf8427e', '2011-12-06 00:00:00'),
(100, 100, 'EU Invoicing', '<style type="text/css"><!--\r\n    body {\r\n        font-family: Verdana, Arial, Helvetica;\r\n    }\r\n    #invoice-content #invoice-table {\r\n        padding-top:40px !important;\r\n    }\r\n    .fs_11 {\r\n        font-size:11px;\r\n    }\r\n    #invoice-items-table{\r\n        border-collapse: collapse;\r\n    }\r\n    .fullborder {\r\n        border:solid 1px #000;\r\n    }\r\n    .rowspace td {\r\n        height:2px;padding:0px;\r\n    }\r\n    .doubleborder {\r\n        border:solid 2px #000; font-weight:bold;\r\n    }\r\n    .noborder {\r\n        border:none;\r\n    }\r\n--></style>\r\n<div id="invoice-table" style="padding: 20px; font-family: Verdana, Arial, Helvetica;">\r\n<table style="width: 100%; margin-bottom: 20px;" border="0" cellspacing="0">\r\n<tbody>\r\n<tr id="invoicetitle">\r\n<td style="border-bottom: solid 1px #000;" colspan="3" align="right" valign="middle" width="66%"><em style="font-size: 18px; font-weight: bold;">{$invoice.number}</em></td>\r\n</tr>\r\n<tr id="invoice-dateinfo">\r\n<td class="fs_11" colspan="3" align="right">{$lang.original}<br />{$lang.invoice_date}: {$invoice.date}</td>\r\n</tr>\r\n<tr id="invoice-spacer">\r\n<td colspan="3">&nbsp;</td>\r\n</tr>\r\n<tr id="invoice-details">\r\n<td style="border: solid 1px #000;" valign="top" width="45%"><span style="font-size: 12px; font-weight: bold;">{$lang.invoice_from}</span></td>\r\n<td align="left" valign="top" width="10%">&nbsp;</td>\r\n<td style="border: solid 1px #000;" valign="top" width="45%"><span style="font-size: 12px; font-weight: bold;">{$lang.invoice_to}</span> <br /> <span style="font-size: 11px;">{if $client.companyname}{$client.companyname}, {/if}{$client.firstname} {$client.lastname}<br /> {$client.address1} {if $client.address2}, {$client.address2}{/if}{if $client.postcode}, {$client.postcode}{/if} {$client.city} {if $client.country}, {$client.country}{/if}{if $client.vateu}<br />{$lang.vateu}: {$client.vateu}{/if}</span></td>\r\n</tr>\r\n<tr id="invoice-spacer">\r\n<td colspan="3">&nbsp;</td>\r\n</tr>\r\n<tr id="invoice-payinfo">\r\n<td class="fs_11" colspan="3" align="left">{$lang.paymethod}: {$invoice.paymentgw}<br /> {$lang.dateofsale}: {$invoice.date}</td>\r\n</tr>\r\n</tbody>\r\n</table>\r\n<span style="font-size: 11px;">{foreach from=$transactions item=trans}{$lang.trans_id} {$trans.trans_id}{break}{/foreach}</span>{if $invoice.notes}{/if}\r\n<table id="invoice-items-table" class="invoice-table" style="width: 100%;" border="0" cellspacing="0" cellpadding="6">\r\n<tbody>\r\n<tr>\r\n<td class="fullborder" align="left" valign="middle" bgcolor="#f0f0f0"><span style="font-size: 11px; font-weight: bold;">{$lang.invoice_desc}</span></td>\r\n<td class="fullborder" style="width: 8%;" align="right" valign="middle" bgcolor="#f0f0f0"><span style="font-size: 11px; font-weight: bold;">{$lang.qty}</span></td>\r\n<td class="fullborder" style="width: 11%;" align="right" valign="middle" bgcolor="#f0f0f0"><span style="font-size: 11px; font-weight: bold;">{$lang.unitcost}</span></td>\r\n<td class="fullborder" style="width: 11%;" align="right" valign="middle" bgcolor="#f0f0f0"><span style="font-size: 11px; font-weight: bold;">{$lang.netprice}</span></td>\r\n<td class="fullborder" style="width: 8%;" align="right" valign="middle" bgcolor="#f0f0f0"><span style="font-size: 11px; font-weight: bold;">{$lang.tax}</span></td>\r\n<td class="fullborder" style="width: 11%;" align="right" valign="middle" bgcolor="#f0f0f0"><span style="font-size: 11px; font-weight: bold;">{$lang.taxamount}</span></td>\r\n<td class="fullborder" style="width: 11%;" align="right" valign="middle" bgcolor="#f0f0f0"><span style="font-size: 11px; font-weight: bold;">{$lang.grossprice}</span></td>\r\n</tr>\r\n<tr>\r\n<td class="fullborder" valign="middle"><span style="font-size: 11px;">{$item.description}</span></td>\r\n<td class="fullborder" align="right" valign="middle"><span style="font-size: 11px;">{$item.qty}</span></td>\r\n<td class="fullborder" align="right" valign="middle"><span style="font-size: 11px;">{$item.amount}</span></td>\r\n<td class="fullborder" align="right" valign="middle"><span style="font-size: 11px;">{$item.linetotal}</span></td>\r\n<td class="fullborder" align="right" valign="middle"><span style="font-size: 11px;">{if !$item.tax_rate}{$lang.nottaxed}{else}{$item.tax_rate}%{/if}</span></td>\r\n<td class="fullborder" align="right" valign="middle"><span style="font-size: 11px;">{$item.tax_amount}</span></td>\r\n<td class="fullborder" align="right" valign="middle"><span style="font-size: 11px;">{$item.linetaxed}</span></td>\r\n</tr>\r\n<tr>\r\n<td colspan="2" valign="middle"><span style="font-size: 12px; font-weight: bold;">{$lang.invoicedue}: {$invoice.total}</span></td>\r\n<td align="right" valign="middle"><span style="font-size: 12px; font-weight: bold;">{$lang.total}</span></td>\r\n<td class="fullborder" align="right" valign="middle"><span style="font-size: 11px;">{$invoice.subtotal}</span></td>\r\n<td class="fullborder" align="right" valign="middle">&nbsp;</td>\r\n<td class="fullborder" align="right" valign="middle"><span style="font-size: 11px;">{$invoice.tax}</span></td>\r\n<td class="doubleborder" align="right" valign="middle"><span style="font-size: 11px;">{$invoice.total}</span></td>\r\n</tr>\r\n<tr class="rowspace">\r\n<td colspan="7">&nbsp;</td>\r\n</tr>\r\n<tr>\r\n<td colspan="2" valign="middle">&nbsp;</td>\r\n<td align="right" valign="middle">{if $smarty.foreach.taxloop.first}<span style="font-size: 12px; font-weight: bold;">{$lang.including}</span>{/if}</td>\r\n<td class="fullborder" align="right" valign="middle"><span style="font-size: 11px;">{$taxitm.subtotal}</span></td>\r\n<td class="fullborder" align="right" valign="middle"><span style="font-size: 11px;">{if !$taxitm.name}{$lang.nottaxed}{else}{$taxitm.name}%{/if}</span></td>\r\n<td class="fullborder" align="right" valign="middle"><span style="font-size: 11px;">{$taxitm.tax}</span></td>\r\n<td class="fullborder" align="right" valign="middle"><span style="font-size: 11px;">{$taxitm.total}</span></td>\r\n</tr>\r\n<tr>\r\n<td style="font-size: 12px;" colspan="3" valign="middle">{$lang.Paid}: {$totalpaid}<br /> {$lang.leftunpaid}: {$transbalance}</td>\r\n<td colspan="4" valign="middle">&nbsp;</td>\r\n</tr>\r\n<tr>\r\n<td colspan="3" valign="middle">&nbsp;</td>\r\n<td style="font-size: 11px; border: solid 1px #000;" colspan="4" valign="middle">{$lang.notes}<br />{$invoice.notes}</td>\r\n</tr>\r\n</tbody>\r\n</table>\r\n<table style="margin-top: 50px; width: 100%;" border="0" cellspacing="0" cellpadding="0">\r\n<tbody>\r\n<tr>\r\n<td style="width: 100%;" align="left" valign="middle"><span style="font-size: 11px;">{$lang.pdfagreement}</span></td>\r\n</tr>\r\n</tbody>\r\n</table>\r\n<table style="margin-top: 50px; width: 100%;" border="0" cellspacing="0" cellpadding="0">\r\n<tbody>\r\n<tr>\r\n<td style="border-top: 1px solid #F0F0F0; padding-top: 10px; width: 100%;" align="right"><span style="font-size: 11px; color: #a4a4a4;">Invoice footer text</span></td>\r\n</tr>\r\n</tbody>\r\n</table>\r\n</div>', 'b3d83359bb9fd7290243d07b6a9a8176', '2013-05-16 13:04:47'),
(101, 101, 'Default', '<style type="text/css"><!--\r\n        body {\r\n            font-family: Arial;\r\n        }\r\n--></style>\r\n<div id="invoice-table" style="padding: 40px; font-family: Arial;">\r\n<table style="width: 100%;" border="0" cellspacing="0">\r\n<tbody>\r\n<tr id="invoice-header">\r\n<td style="padding-left: 10px;" colspan="2" align="left" valign="middle" width="66%"><span style="font-size: 18px; font-weight: bold; color: #484740;">{$companylogo}</span> <span style="font-size: 18px; font-weight: bold; color: #a4a4a4;">{$lang.customerquote}</span></td>\r\n<td align="right" valign="middle">\r\n<table border="0" cellspacing="0" cellpadding="2">\r\n<tbody>\r\n<tr>\r\n<td align="right"><span style="font-size: 12px; font-weight: bold;">{$lang.estimate_id}</span></td>\r\n<td align="left"><span style="font-size: 12px;">#{$estimate.id}</span></td>\r\n</tr>\r\n<tr>\r\n<td align="right"><span style="font-size: 12px; font-weight: bold;">{$lang.status}</span></td>\r\n<td align="left"><span style="font-size: 12px;">{$estimate.status}</span></td>\r\n</tr>\r\n<tr>\r\n<td align="right"><span style="font-size: 12px; font-weight: bold;">{$lang.estimate_date}</span></td>\r\n<td align="left"><span style="font-size: 12px;">{$estimate.date_created}</span></td>\r\n</tr>\r\n<tr>\r\n<td align="right"><span style="font-size: 12px; font-weight: bold;">{$lang.expirydate}</span></td>\r\n<td align="left"><span style="font-size: 12px;">{$estimate.date_expires}</span></td>\r\n</tr>\r\n</tbody>\r\n</table>\r\n</td>\r\n</tr>\r\n<tr id="invoice-details">\r\n<td style="padding-top: 40px;" valign="top" width="33%"><span style="font-size: 12px; font-weight: bold;">{$lang.customer}</span> <br /> <span style="font-size: 11px;">{$client.companyname} <br /> {$estimate.client.firstname} {$estimate.client.lastname} <br /> {$estimate.client.address1} <br /> {$estimate.client.address2} <br /> {$estimate.client.city}, {$estimate.client.state} {$estimate.client.postcode} <br /> {$estimate.client.country} </span></td>\r\n</tr>\r\n</tbody>\r\n</table>\r\n<table class="invoice-table" style="margin-top: 50px; width: 100%;" border="0" cellspacing="0" cellpadding="6">\r\n<tbody>\r\n<tr>\r\n<td align="left" valign="middle" bgcolor="#f0f0f0"><span style="font-size: 11px; font-weight: bold;">{$lang.invoice_desc}</span></td>\r\n<td style="width: 7%;" align="center" valign="middle" bgcolor="#f0f0f0"><span style="font-size: 11px; font-weight: bold;">{$lang.invoice_tax}</span></td>\r\n<td style="width: 15%;" align="right" valign="middle" bgcolor="#f0f0f0"><span style="font-size: 11px; font-weight: bold;">{$lang.unitcost}</span></td>\r\n<td style="width: 7%;" align="center" valign="middle" bgcolor="#f0f0f0"><span style="font-size: 11px; font-weight: bold;">{$lang.qty}</span></td>\r\n<td style="width: 15%;" align="right" valign="middle" bgcolor="#f0f0f0"><span style="font-size: 11px; font-weight: bold;">{$lang.price}</span></td>\r\n</tr>\r\n<tr>\r\n<td style="border-bottom: 1px solid #F0F0F0;" valign="middle"><span style="font-size: 11px;">{$item.description}</span></td>\r\n<td style="border-bottom: 1px solid #F0F0F0;" align="center" valign="middle"><span style="font-size: 11px;">{$item.taxed}</span></td>\r\n<td style="border-bottom: 1px solid #F0F0F0;" align="right" valign="middle"><span style="font-size: 11px;">{$item.amount}</span></td>\r\n<td style="border-bottom: 1px solid #F0F0F0;" align="center" valign="middle"><span style="font-size: 11px;">{$item.qty}</span></td>\r\n<td style="border-bottom: 1px solid #F0F0F0;" align="right" valign="middle"><span style="font-size: 11px;">{$item.linetotal}</span></td>\r\n</tr>\r\n<tr>\r\n<td>&nbsp;</td>\r\n<td colspan="2" align="right" valign="middle"><span style="font-size: 12px; font-weight: bold;">{$lang.subtotal}</span></td>\r\n<td colspan="2" align="right" valign="middle"><span style="font-size: 12px;">{$estimate.subtotal}</span></td>\r\n</tr>\r\n<tr>\r\n<td>&nbsp;</td>\r\n<td colspan="2" align="right" valign="middle"><span style="font-size: 12px; font-weight: bold;">{$lang.credit}</span></td>\r\n<td colspan="2" align="right" valign="middle"><span style="font-size: 12px;">{$estimate.credit}</span></td>\r\n</tr>\r\n<tr>\r\n<td>&nbsp;</td>\r\n<td colspan="2" align="right" valign="middle"><span style="font-size: 12px; font-weight: bold;">{$lang.tax} ({$estimate.taxrate}%)</span></td>\r\n<td colspan="2" align="right" valign="middle"><span style="font-size: 12px;">{$estimate.tax}</span></td>\r\n</tr>\r\n<tr>\r\n<td>&nbsp;</td>\r\n<td colspan="2" align="right" valign="middle"><span style="font-size: 12px; font-weight: bold;">{$lang.tax} ({$estimate.taxrate2}%)</span></td>\r\n<td colspan="2" align="right" valign="middle"><span style="font-size: 12px;">{$estimate.tax2}</span></td>\r\n</tr>\r\n<tr>\r\n<td>&nbsp;</td>\r\n<td colspan="2" align="right" valign="middle"><span style="font-size: 14px; font-weight: bold;">{$lang.total}</span></td>\r\n<td colspan="2" align="right" valign="middle"><span style="font-size: 14px; font-weight: bold;">{$estimate.total}</span></td>\r\n</tr>\r\n</tbody>\r\n</table>\r\n{if $estimate.notes!=''''}\r\n<table style="margin-top: 50px; width: 100%;" border="0" cellspacing="0" cellpadding="0">\r\n<tbody>\r\n<tr>\r\n<td style="width: 100%;" align="left" valign="middle"><span style="font-size: 11px;" lang="">{$estimate.notes}</span></td>\r\n</tr>\r\n</tbody>\r\n</table>\r\n{/if}\r\n<table style="margin-top: 50px; width: 100%;" border="0" cellspacing="0" cellpadding="0">\r\n<tbody>\r\n<tr>\r\n<td style="border-top: 1px solid #F0F0F0; padding-top: 10px; width: 100%;" align="center">&nbsp;</td>\r\n</tr>\r\n</tbody>\r\n</table>\r\n</div>', '015fee79efd5c71bccdd403649591410', '2013-05-21 00:00:00'),
(102, 102, 'Default', '<style type="text/css"><!--\r\n    body {\r\n        font-family: Arial;\r\n    }\r\n--></style>\r\n<div id="invoice-table" style="padding: 40px; font-family: Arial;">\r\n<table style="width: 100%;" border="0" cellspacing="0">\r\n<tbody>\r\n<tr id="invoice-header">\r\n<td style="padding-left: 10px;" colspan="2" align="left" valign="middle" width="66%"><span style="font-size: 18px; font-weight: bold; color: #484740;">{$companylogo}</span> <span style="font-size: 18px; font-weight: bold; color: #a4a4a4;">{$invoice.number}</span></td>\r\n<td align="right" valign="middle">\r\n<table border="0" cellspacing="0" cellpadding="2">\r\n<tbody>\r\n<tr>\r\n<td align="right"><span style="font-size: 12px; font-weight: bold;">{$lang.creditnote}</span></td>\r\n<td align="left"><span style="font-size: 12px;">{$invoice.id}</span></td>\r\n</tr>\r\n<tr>\r\n<td align="right"><span style="font-size: 12px; font-weight: bold;">{$lang.creditnote_date}</span></td>\r\n<td align="left"><span style="font-size: 12px;">{$invoice.date}</span></td>\r\n</tr>\r\n</tbody>\r\n</table>\r\n</td>\r\n</tr>\r\n<tr id="invoice-details">\r\n<td style="padding-top: 40px;" valign="top" width="33%"><span style="font-size: 12px; font-weight: bold;">{$lang.invoice_to}</span><br /> <span style="font-size: 11px;">{$client.companyname}<br /> {$client.firstname} {$client.lastname}<br /> {$client.address1} <br /> {$client.address2}<br /> {$client.city}, {$client.state}{$client.postcode}<br /> {$client.country} </span></td>\r\n<td style="padding-top: 40px;" valign="top" width="33%"><span style="font-size: 12px; font-weight: bold;">{$lang.invoice_from}</span><br /> <span style="font-size: 11px;">HostBill Demo</span></td>\r\n<td style="padding-top: 40px;" align="left" valign="top" width="33%">\r\n<table border="0" cellspacing="0" cellpadding="2">\r\n<tbody>\r\n<tr>\r\n<td align="right"><span style="font-size: 12px; font-weight: bold;">{$lang.balance}:</span></td>\r\n<td align="left"><span style="font-size: 12px; font-weight: bold;">{$transbalance}</span></td>\r\n</tr>\r\n</tbody>\r\n</table>\r\n</td>\r\n</tr>\r\n</tbody>\r\n</table>\r\n<!--{if $invoice.related_invoice_number}--><span style="font-size: 11px; font-weight: bold;">{$lang.related_invoice} {$invoice.related_invoice_number} {$lang.from} {$invoice.related_invoice_date}</span><!--{/if}-->\r\n<table class="invoice-table" style="margin-top: 50px; width: 100%;" border="0" cellspacing="0" cellpadding="6">\r\n<tbody>\r\n<tr>\r\n<td align="left" valign="middle" bgcolor="#f0f0f0"><span style="font-size: 11px; font-weight: bold;">{$lang.invoice_desc}</span></td>\r\n<td style="width: 7%;" align="center" valign="middle" bgcolor="#f0f0f0"><span style="font-size: 11px; font-weight: bold;">{$lang.invoice_tax}</span></td>\r\n<td style="width: 15%;" align="right" valign="middle" bgcolor="#f0f0f0"><span style="font-size: 11px; font-weight: bold;">{$lang.unitcost}</span></td>\r\n<td style="width: 7%;" align="center" valign="middle" bgcolor="#f0f0f0"><span style="font-size: 11px; font-weight: bold;">{$lang.qty}</span></td>\r\n<td style="width: 15%;" align="right" valign="middle" bgcolor="#f0f0f0"><span style="font-size: 11px; font-weight: bold;">{$lang.price}</span></td>\r\n</tr>\r\n<tr>\r\n<td style="border-bottom: 1px solid #F0F0F0;" valign="middle"><span style="font-size: 11px;">{$item.description}</span></td>\r\n<td style="border-bottom: 1px solid #F0F0F0;" align="center" valign="middle"><span style="font-size: 11px;">{$item.taxed}</span></td>\r\n<td style="border-bottom: 1px solid #F0F0F0;" align="right" valign="middle"><span style="font-size: 11px;">{$item.amount}</span></td>\r\n<td style="border-bottom: 1px solid #F0F0F0;" align="center" valign="middle"><span style="font-size: 11px;">{$item.qty}</span></td>\r\n<td style="border-bottom: 1px solid #F0F0F0;" align="right" valign="middle"><span style="font-size: 11px;">{$item.linetotal}</span></td>\r\n</tr>\r\n<tr>\r\n<td>&nbsp;</td>\r\n<td colspan="2" align="right" valign="middle"><span style="font-size: 12px; font-weight: bold;">{$lang.subtotal}</span></td>\r\n<td colspan="2" align="right" valign="middle"><span style="font-size: 12px;">{$invoice.subtotal}</span></td>\r\n</tr>\r\n<tr>\r\n<td>&nbsp;</td>\r\n<td colspan="2" align="right" valign="middle"><span style="font-size: 12px; font-weight: bold;">{$lang.tax} ({$invoice.taxrate}%)</span></td>\r\n<td colspan="2" align="right" valign="middle"><span style="font-size: 12px;">{$invoice.tax}</span></td>\r\n</tr>\r\n<tr>\r\n<td>&nbsp;</td>\r\n<td colspan="2" align="right" valign="middle"><span style="font-size: 12px; font-weight: bold;">{$lang.tax} ({$invoice.taxrate2}%)</span></td>\r\n<td colspan="2" align="right" valign="middle"><span style="font-size: 12px;">{$invoice.tax2}</span></td>\r\n</tr>\r\n<tr>\r\n<td>&nbsp;</td>\r\n<td colspan="2" align="right" valign="middle"><span style="font-size: 14px; font-weight: bold;">{$lang.total}</span></td>\r\n<td colspan="2" align="right" valign="middle"><span style="font-size: 14px; font-weight: bold;">{$invoice.total}</span></td>\r\n</tr>\r\n</tbody>\r\n</table>\r\n<table style="margin-top: 50px; width: 100%;" border="0" cellspacing="0" cellpadding="0">\r\n<tbody>\r\n<tr>\r\n<td style="width: 100%;" align="left" valign="middle"><span style="font-size: 11px;"><em>{$invoice.notes}</em></span></td>\r\n</tr>\r\n</tbody>\r\n</table>\r\n<table style="margin-top: 50px; width: 100%;" border="0" cellspacing="0" cellpadding="0">\r\n<tbody>\r\n<tr>\r\n<td style="border-top: 1px solid #F0F0F0; padding-top: 10px; width: 100%;" align="center"><span style="font-size: 11px; color: #a4a4a4;">Invoice footer text</span></td>\r\n</tr>\r\n</tbody>\r\n</table>\r\n</div>', '93fc76301bc84500e071b1b69a73f098', '2013-10-17 10:25:44');
##########
CREATE TABLE `hb_tickets` (
  `id` int(11) NOT NULL auto_increment,
  `date` datetime NOT NULL default '0000-00-00 00:00:00',
  `lastreply` datetime NOT NULL,
  `lastupdate` datetime NOT NULL,
  `lastedit` datetime NOT NULL,
  `editby` varchar(50) NOT NULL,
  `ticket_number` int(6) NOT NULL default '0',
  `acc_hash` varchar(6) NOT NULL default '0',
  `dept_id` int(11) NOT NULL,
  `client_id` int(11) NOT NULL,
  `creator_id` int(11) NOT NULL default '0',
  `name` text NOT NULL,
  `email` text NOT NULL,
  `cc` text,
  `subject` text NOT NULL,
  `body` text NOT NULL,
  `status` varchar(31) NOT NULL default 'Open',
  `priority` enum('0','1','2') NOT NULL default '0',
  `notes` int(11) NOT NULL,
  `tags` tinyint(3) unsigned NOT NULL default '0',
  `admin_read` tinyint(1) NOT NULL default '0',
  `client_read` tinyint(1) NOT NULL default '0',
  `type` enum('Client','Admin','Email','Unregistered') default 'Client',
  `senderip` varchar(16) NOT NULL,
  `escalated` tinyint(1) NOT NULL,
  `start_date` datetime NOT NULL,
  `resolve_date` datetime NOT NULL,
  `owner_id` int(11) NOT NULL default '0',
  `metadata` text,
  PRIMARY KEY  (`id`),
  KEY `ticket_number` (`ticket_number`,`acc_hash`),
  KEY `client_id` (`client_id`),
  KEY `date` (`date`),
  KEY `admins` (`admin_read`),
  KEY `dept_id` (`dept_id`),
  KEY `status` (`status`(3)),
  KEY `lastreply` (`lastreply`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_ticket_status` (
  `status` varchar(31) NOT NULL,
  `options` int(3) NOT NULL default '0',
  `color` varchar(7) NOT NULL default '000000',
  `sort_order` int(3) NOT NULL default '0',
  PRIMARY KEY  (`status`),
  KEY `options` (`options`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
INSERT INTO `hb_ticket_status` (`status`, `options`, `color`, `sort_order`) VALUES
('Answered', 3, '000000', 2),
('Client-Reply', 1, '000000', 4),
('Closed', 1, '000000', 1),
('In-Progress', 1, '000000', 3),
('Open', 1, '000000', 0),
('Scheduled', 4, '000000', 0),
('Sent-Out', 4, '000000', 0);
##########
CREATE TABLE `hb_ticket_views` (
  `id` int(10) NOT NULL auto_increment,
  `owner` int(10) NOT NULL,
  `name` varchar(100) NOT NULL,
  `options` int(11) unsigned NOT NULL default '0',
  `columns` int(11) unsigned NOT NULL default '31',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_ticket_views_columns` (
  `id` int(10) NOT NULL,
  `view_id` int(10) NOT NULL,
  `column` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`id`,`view_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_ticket_views_filters` (
  `id` int(10) NOT NULL auto_increment,
  `view_id` int(10) NOT NULL,
  `type` int(10) unsigned NOT NULL,
  `value` text NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_ticketshare_agreements` (
  `id` int(11) NOT NULL auto_increment,
  `uuid` varchar(40) NOT NULL,
  `access_key` varchar(40) NOT NULL,
  `name` varchar(128) NOT NULL,
  `tag` varchar(128) NOT NULL,
  `sharing_url` text NOT NULL,
  `status` enum('pending','accepted','declined','inactive') NOT NULL,
  `deactivated_by` enum('sender','receiver') default NULL,
  `created_by` enum('sender','receiver') NOT NULL,
  `department_id` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `uuid` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_ticketshare_attachments` (
  `attach_id` int(10) NOT NULL,
  `uuid` varchar(40) NOT NULL,
  `local` tinyint(4) NOT NULL,
  PRIMARY KEY  (`attach_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_ticketshare_comments` (
  `uuid` varchar(40) NOT NULL,
  `target` enum('reply','note','ticket') NOT NULL,
  `target_id` int(11) NOT NULL,
  PRIMARY KEY  (`uuid`),
  UNIQUE KEY `target_taget_id` (`target`,`target_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_ticketshare_tickets` (
  `uuid` varchar(40) NOT NULL default '',
  `type` enum('master','slave') NOT NULL,
  `ticket_id` int(11) NOT NULL,
  `agreement_id` int(11) NOT NULL,
  PRIMARY KEY  (`uuid`),
  KEY `ticket_id` (`ticket_id`),
  KEY `agreement_id` (`agreement_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_tickets_attachments` (
  `id` int(11) NOT NULL auto_increment,
  `ticket_id` int(11) NOT NULL,
  `rel_id` int(11) NOT NULL default '0',
  `filename` text NOT NULL,
  `type` varchar(8) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `ticket_id` (`ticket_id`),
  KEY `rel_id` (`rel_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_tickets_bans` (
  `id` int(11) NOT NULL auto_increment,
  `type` enum('Email','Subject','Content') NOT NULL,
  `text` text NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
##########
INSERT INTO `hb_tickets_bans` (`id`, `type`, `text`) VALUES
(1, 'Subject', 'eval(base64_decode');
##########
CREATE TABLE `hb_tickets_emails` (
  `id` int(11) NOT NULL auto_increment,
  `date` datetime NOT NULL,
  `to` text NOT NULL,
  `from` text NOT NULL,
  `subject` text NOT NULL,
  `message` text NOT NULL,
  `status` tinyint(2) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_tickets_log` (
  `id` int(11) NOT NULL auto_increment,
  `ticket_id` int(11) NOT NULL,
  `date` datetime NOT NULL,
  `action` text NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `ticket_id` (`ticket_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_tickets_notes` (
  `id` int(11) NOT NULL auto_increment,
  `ticket_id` int(11) NOT NULL,
  `admin_id` int(11) NOT NULL,
  `name` text NOT NULL,
  `email` text NOT NULL,
  `date` datetime NOT NULL,
  `note` text NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `ticket_id` (`ticket_id`),
  KEY `admin_id` (`admin_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_tickets_predefinied` (
  `id` int(11) NOT NULL auto_increment,
  `category_id` int(11) NOT NULL,
  `name` text NOT NULL,
  `reply` text NOT NULL,
  `usability` int(11) default '0',
  `note` tinyint(4) default NULL,
  `priority` tinyint(4) default NULL,
  `status` varchar(31) default NULL,
  `owner` int(10) default NULL,
  `tags` text,
  `share` int(11) default NULL,
  PRIMARY KEY  (`id`),
  KEY `category` (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_tickets_predefinied_cats` (
  `id` int(11) NOT NULL auto_increment,
  `parent_cat` int(11) NOT NULL default '0',
  `name` text NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `parent` (`parent_cat`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_tickets_predefinied_my` (
  `admin_id` int(11) NOT NULL,
  `reply_id` int(11) NOT NULL,
  `sort_order` int(11) NOT NULL,
  PRIMARY KEY  (`admin_id`,`reply_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_tickets_tags` (
  `tag_id` int(11) NOT NULL,
  `ticket_id` int(11) NOT NULL,
  PRIMARY KEY  (`tag_id`,`ticket_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_ticket_departments` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(64) NOT NULL,
  `description` text NOT NULL,
  `email` varchar(128) NOT NULL,
  `senderemail` varchar(128) NOT NULL,
  `assigned_admins` text NOT NULL,
  `sort` int(10) NOT NULL default '0',
  `method` enum('POP','PIPE') NOT NULL default 'PIPE',
  `host` varchar(128) NOT NULL,
  `port` int(4) NOT NULL default '110',
  `login` varchar(128) NOT NULL,
  `password` varchar(128) NOT NULL,
  `sendmethod` tinyint(1) NOT NULL default '1',
  `smtphost` varchar(128) NOT NULL,
  `smtpport` int(11) NOT NULL,
  `smtplogin` varchar(128) NOT NULL,
  `smtppassword` varchar(128) NOT NULL,
  `options` int(3) unsigned NOT NULL default '13',
  `autoclose` int(10) unsigned NOT NULL default '0',
  `sla_level_one` int(10) unsigned NOT NULL default '0',
  `sla_level_two` int(10) unsigned NOT NULL default '0',
  `macro_sla1_id` int(10) unsigned NOT NULL default '0',
  `macro_sla2_id` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `email` (`email`(64)),
  KEY `admins` (`assigned_admins`(30)),
  KEY `method` (`method`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_ticket_departments_mail` (
  `id` int(10) NOT NULL auto_increment,
  `dept_id` int(10) NOT NULL,
  `email` varchar(50) NOT NULL,
  `method` enum('POP','PIPE') NOT NULL,
  `host` varchar(50) NOT NULL,
  `login` varchar(50) NOT NULL,
  `port` int(10) NOT NULL,
  `password` varchar(50) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `email` (`email`),
  KEY `dept_id` (`dept_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_ticket_departments_staff` (
  `dept_id` int(10) NOT NULL,
  `staff_id` int(10) NOT NULL,
  `options` int(10) NOT NULL,
  `date` datetime NOT NULL,
  PRIMARY KEY  (`dept_id`,`staff_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_ticket_filter` (
  `id` int(10) NOT NULL auto_increment,
  `name` varchar(50) NOT NULL,
  `type` enum('pre','post') NOT NULL,
  `final` int(10) NOT NULL,
  `join_type` enum('all','any') NOT NULL,
  `macro_id` int(10) NOT NULL default '0',
  `sort_order` int(10) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `name` (`name`),
  KEY `macro_id` (`macro_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
##########
INSERT INTO `hb_ticket_filter` (`id`, `name`, `type`, `final`, `join_type`, `macro_id`, `sort_order`) VALUES
(1, 'Example', 'pre', 1, 'any', 0, 0),
(2, 'Old filters', 'pre', 1, 'any', 0, 0);
##########
CREATE TABLE `hb_ticket_filter_items` (
  `id` int(10) NOT NULL,
  `filter_id` int(10) NOT NULL,
  `field` varchar(50) NOT NULL,
  `type` enum('contains','match','regexp') NOT NULL,
  `content` text NOT NULL,
  PRIMARY KEY  (`id`,`filter_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
INSERT INTO `hb_ticket_filter_items` (`id`, `filter_id`, `field`, `type`, `content`) VALUES
(1, 1, 'subject', 'contains', '***SPAM'),
(2, 1, 'body', 'regexp', '/eval\\s*\\(\\s*base64_decode/i');
##########
CREATE TABLE `hb_ticket_replies` (
  `id` int(11) NOT NULL auto_increment,
  `ticket_id` int(11) NOT NULL,
  `replier_id` int(11) NOT NULL default '0',
  `name` text NOT NULL,
  `email` text NOT NULL,
  `date` datetime NOT NULL,
  `lastedit` datetime NOT NULL,
  `editby` varchar(50) NOT NULL,
  `body` text NOT NULL,
  `status` enum('Sent','Draft','Scheduled') NOT NULL default 'Sent',
  `type` enum('Client','Admin','Email','Unregistered') default 'Client',
  PRIMARY KEY  (`id`),
  KEY `ticket_id` (`ticket_id`),
  KEY `rep_type` (`replier_id`,`type`),
  KEY `date` (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_ticket_replies_rating` (
  `reply_id` int(10) NOT NULL,
  `rating` int(10) NOT NULL,
  `date` datetime NOT NULL,
  PRIMARY KEY  (`reply_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_transactions` (
  `id` int(11) NOT NULL auto_increment,
  `client_id` int(11) NOT NULL,
  `invoice_id` int(11) NOT NULL,
  `module` int(11) NOT NULL,
  `date` datetime NOT NULL,
  `description` text NOT NULL,
  `in` decimal(10,2) NOT NULL default '0.00',
  `fee` decimal(10,2) NOT NULL default '0.00',
  `out` decimal(10,2) NOT NULL default '0.00',
  `trans_id` varchar(32) NOT NULL,
  `currency_id` int(11) NOT NULL default '0',
  `rate` decimal(10,4) NOT NULL default '1.0000',
  `refund_of` int(11) default NULL,
  PRIMARY KEY  (`id`),
  KEY `client_id` (`client_id`),
  KEY `invoice_id` (`invoice_id`),
  KEY `trans_id` (`trans_id`),
  KEY `currency_id` (`currency_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_upgrades` (
  `id` int(11) NOT NULL auto_increment,
  `rel_type` enum('Hosting','Domain') NOT NULL default 'Hosting',
  `account_id` int(11) NOT NULL,
  `client_id` int(11) NOT NULL,
  `order_id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `status` enum('Pending','Upgraded') NOT NULL default 'Pending',
  `total` decimal(10,2) NOT NULL,
  `new_value` decimal(10,2) NOT NULL,
  `new_billing` enum('Free','One Time','Monthly','Quarterly','Semi-Annually','Annually','Biennially','Triennially','Daily','Weekly','Hourly') NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `rel_type` (`rel_type`,`account_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_vps_details` (
  `account_id` int(11) NOT NULL,
  `veid` varchar(64) NOT NULL,
  `type` enum('Xen','Xen HVM','OpenVZ','KVM','Virtuozzo','Other') NOT NULL,
  `ip` varchar(40) NOT NULL,
  `additional_ip` text NOT NULL,
  `guaranteed_ram` int(3) NOT NULL,
  `burstable_ram` int(3) NOT NULL,
  `disk_usage` int(11) NOT NULL,
  `disk_limit` int(11) NOT NULL,
  `bw_usage` int(11) NOT NULL,
  `bw_limit` int(11) NOT NULL,
  `os` text NOT NULL,
  `node` varchar(32) NOT NULL,
  `extra` text NOT NULL,
  PRIMARY KEY  (`account_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_widgets` (
  `id` int(11) NOT NULL auto_increment,
  `target_type` enum('Product','Category') NOT NULL default 'Product',
  `target_id` int(11) NOT NULL default '0',
  `widget_id` int(11) NOT NULL,
  `override_defaults` tinyint(1) NOT NULL default '0',
  `name` varchar(128) NOT NULL,
  `config` text NOT NULL,
  `group` varchar(32) NOT NULL,
  `sort_order` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `widget_id` (`widget_id`),
  KEY `tar` (`target_type`,`target_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##########
CREATE TABLE `hb_widgets_config` (
  `id` int(11) NOT NULL auto_increment,
  `widget` varchar(32) NOT NULL,
  `name` text NOT NULL,
  `location` text NOT NULL,
  `ptype` int(11) NOT NULL,
  `config` text NOT NULL,
  `options` int(11) NOT NULL default '1',
  `group` varchar(32) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `widget` (`widget`),
  KEY `ptype` (`ptype`),
  KEY `options` (`options`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
