CREATE TABLE `announcement` (
  `id` int(11) NOT NULL auto_increment,
  `company_id` int(11) NOT NULL DEFAULT '0',
  `title` varchar(125) NOT NULL default '',
  `excerpt` text NOT NULL,
  `post` longtext NOT NULL,
  `postdate` datetime NULL default '0000-00-00 00:00:00',
  `publish` tinyint(4) NOT NULL default '0',
  `authorid` int(11) NOT NULL default '0',
  `recipient` tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) DEFAULT CHARACTER SET utf8, ENGINE=MyISAM;

CREATE TABLE `announcement_recipient` (
    `ann_id` INT NOT NULL ,
    `recipient_id` INT NOT NULL ,
    PRIMARY KEY ( `ann_id` , `recipient_id` )
) DEFAULT CHARACTER SET utf8, ENGINE=MYISAM ;

CREATE TABLE `calendar` (
    `id` INT NOT NULL AUTO_INCREMENT ,
    `title` VARCHAR( 256 ) NOT NULL ,
    `allday` TINYINT NOT NULL DEFAULT '0',
    `start` TIMESTAMP NOT NULL ,
    `end` TIMESTAMP NOT NULL ,
    `url` TEXT NOT NULL ,
    `className` VARCHAR( 56 ) NOT NULL ,
    `appliesto` INT NOT NULL DEFAULT '0',
    `description` TEXT NOT NULL ,
    `userid` INT NOT NULL DEFAULT '0',
    `isprivate` TINYINT NOT NULL DEFAULT '0',
    `isrepeating` TINYINT NOT NULL DEFAULT '0',
    PRIMARY KEY ( `id` )
) DEFAULT CHARACTER SET utf8, ENGINE=MYISAM ;


CREATE TABLE IF NOT EXISTS `billingtype` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(25) NOT NULL default '',
  `description` text NOT NULL,
  `detail` text NOT NULL,
  `price` varchar(10) NOT NULL default '0.00',
  `archived` tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) DEFAULT CHARACTER SET utf8, ENGINE=MyISAM AUTO_INCREMENT=1 ;

CREATE TABLE `coupons` (
  `coupons_id` int(11) NOT NULL auto_increment,
  `coupons_name` varchar(50) NOT NULL default '',
  `coupons_description` text NOT NULL,
  `coupons_code` varchar(50) NOT NULL default '',
  `coupons_quantity` int(11) NOT NULL default '0',
  `coupons_type` tinyint(4) NOT NULL default '0',
  `coupons_applicable_to` tinyint(4) NOT NULL default '127',
  `coupons_discount` float(23,2) NOT NULL default '0.00',
  `coupons_start` date NOT NULL default '0000-00-00',
  `coupons_expires` date NOT NULL default '0000-00-00',
  `coupons_recurring` tinyint(4) NOT NULL default '0',
  `coupons_recurringmonths` tinyint(4) NOT NULL default '0',
  `coupons_billingcycles` tinyint(4) NOT NULL default '0',
  `coupons_archive` tinyint(1) NOT NULL default '0',
  `coupons_taxable` tinyint(4) NOT NULL default '1',
  PRIMARY KEY  (`coupons_id`)
) DEFAULT CHARACTER SET utf8, ENGINE=MyISAM;

CREATE TABLE `coupons_usage` (
  `invoiceentryid` INT(11) NOT NULL ,
  `couponid` INT(11) NOT NULL ,
  `isrecurring` tinyint(1) NOT NULL,
  PRIMARY KEY ( `invoiceentryid` , `couponid`, `isrecurring` )
) DEFAULT CHARACTER SET utf8, ENGINE=MYISAM ;

CREATE TABLE `coupons_packages` (
  `id` int(11) NOT NULL auto_increment,
  `coupons_id` int(11) NOT NULL default '0',
  `promotion_id` int(11) NOT NULL default '0',
  `package_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) DEFAULT CHARACTER SET utf8, ENGINE=MyISAM;

CREATE TABLE `currency` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(250) NOT NULL default '',
  `symbol` char(10) NOT NULL default '',
  `decimalssep` char(10) NOT NULL default '.',
  `thousandssep` char(10) NOT NULL default ',',
  `abrv` varchar(4) NOT NULL default '',
  `alignment` varchar(10) NOT NULL default 'left',
  `precision` int(1) NOT NULL default '2',
  `rate` float(23,10) NOT NULL default '1.0000000000',
  `enabled` tinyint(4) NOT NULL default '1',
  KEY `id` (`id`)
) DEFAULT CHARACTER SET utf8, ENGINE=MyISAM AUTO_INCREMENT=18 ;

CREATE TABLE `domain_packageaddon_prices` (
    `domain_id` int(11) NOT NULL default '0',
    `packageaddon_prices_id` int(11) NOT NULL default '0',
    `billing_cycle` tinyint(4) NOT NULL default '0',
    `nextbilldate` date default NULL,
    PRIMARY KEY  (`domain_id`,`packageaddon_prices_id`)
) DEFAULT CHARACTER SET utf8, ENGINE=MyISAM;

CREATE TABLE `email_queue` (
  `id` int(11) NOT NULL auto_increment,
  `subject` varchar(255) NOT NULL default '',
  `from` varchar(50) NOT NULL default '',
  `from_name` varchar(50) NOT NULL default '',
  `bcc` text NOT NULL,
  `priority` tinyint(4) NOT NULL default '0',
  `confirmreceipt` tinyint(4) NOT NULL default '0',
  `emailtype` varchar(50) NOT NULL default '',
  `body` text NOT NULL,
  `contenttype` tinyint(4) NOT NULL default '0',
  `dfilename` varchar(50) NOT NULL default '',
  `attachment` longtext NOT NULL,
  PRIMARY KEY  (`id`)
) DEFAULT CHARACTER SET utf8, ENGINE=MyISAM;

CREATE TABLE `groups` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(50) NOT NULL,
  `company_id` INT(11) NOT NULL default '0',
  `isadmin` tinyint(4) NOT NULL default '1',
  `issuperadmin` tinyint(4) NOT NULL default '0',
  `iscompanysuperadmin` tinyint(4) NOT NULL default '0',
  `iscustomersmaingroup` tinyint(4) NOT NULL DEFAULT '0',
  `description` text NOT NULL,
  `usedefaultcolor` tinyint(4) NOT NULL default '1',
  `groupcolor` varchar(7),
  `livesupportid` varchar( 25 ) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `i_company_id` (`company_id`)
) DEFAULT CHARACTER SET utf8, ENGINE=MyISAM;

CREATE TABLE `email_queue_addressees` (
    `email_queue_id` INT NOT NULL ,
    `userid` INT NOT NULL ,
    PRIMARY KEY ( `email_queue_id` , `userid` )
) DEFAULT CHARACTER SET utf8, ENGINE = MYISAM ;

CREATE TABLE `help` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(100) NOT NULL default '',
  `detail` text NOT NULL,
  `linkwords` varchar(40) NOT NULL default 'Click to view available tags',
  `width` int(3) NOT NULL default '240',
  `height` int(3) NOT NULL default '320',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `id_2` (`id`)
) DEFAULT CHARACTER SET utf8, ENGINE=MyISAM AUTO_INCREMENT=8 ;

CREATE TABLE `invoice` (
  `id` int(11) NOT NULL auto_increment,
  `customerid` int(11) NOT NULL default '0',
  `billdate` date NOT NULL default '0000-00-00',
  `description` varchar(200) NOT NULL default '',
  `amount` float(23,2) NOT NULL default '0.00',
  `subtotal` float(23,2) NOT NULL default '0.00',
  `balance_due` float(23,2) NOT NULL default '0.00',
  `sent` tinyint(4) NOT NULL default '0',
  `status` tinyint(4) NOT NULL default '0',
  `archive` tinyint(4) NOT NULL default '0',
  `datecreated` DATE default NULL,
  `sentdate` date default NULL,
  `datepaid` date default NULL,
  `tax` float(23,2) NOT NULL default '0.00',
  `taxname` varchar(20) NOT NULL default '',
  `tax2` float(23,2) NOT NULL default '0.00',
  `tax2name` varchar(20) NOT NULL default '',
  `tax2compound` tinyint(4) NOT NULL default '0',
  `processorid` varchar(255) NOT NULL default '',
  `pluginused` VARCHAR( 15 ) DEFAULT 'none' NOT NULL,
  `checknum` VARCHAR( 50 ) NOT NULL ,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `id` (`id`),
  INDEX ( `customerid` )
) DEFAULT CHARACTER SET utf8, ENGINE=MyISAM AUTO_INCREMENT=1 ;

CREATE TABLE `invoiceentry` (
  `id` int(11) NOT NULL auto_increment,
  `customerid` int(11) NOT NULL default '0',
  `description` varchar(200) NOT NULL default '',
  `detail` text NOT NULL,
  `invoiceid` int(11) NOT NULL default '0',
  `date` date NOT NULL default '0000-00-00',
  `billingtypeid` int(11) NOT NULL default '0',
  `is_prorating` tinyint(4) NOT NULL default '0',
  `price` float(23,2) NOT NULL default '0.00',
  `price_percent` float(23,2) NOT NULL default '0.00',
  `recurring` int(11) NOT NULL default '0',
  `recurringappliesto` int(11) default '0',
  `appliestoid` int(11) NOT NULL default '0',
  `coupon_applicable_to` tinyint(4) NOT NULL default '0',
  `includenextpayment` tinyint(4) NOT NULL default '0',
  `paymentterm` tinyint(4) NOT NULL default '0',
  `setup` tinyint(4) NOT NULL default '0',
  `addon_setup` tinyint(4) NOT NULL default '0',
  `taxable` tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) DEFAULT CHARACTER SET utf8, ENGINE=MyISAM COMMENT='Maintain information of work completed for customer' AUTO_INCREMENT=1 ;

CREATE TABLE `invoicetransaction` (
  `id` int(11) NOT NULL auto_increment,
  `invoiceid` int(11) NOT NULL default '0',
  `accepted` tinyint(4) NOT NULL default '0',
  `response` text NOT NULL,
  `transactiondate` datetime NOT NULL default '0000-00-00 00:00:00',
  `transactionid` VARCHAR( 100 ) DEFAULT 'NA' NOT NULL,
  `action` VARCHAR( 10 ) DEFAULT 'none' NOT NULL,
  `last4` VARCHAR( 5 ) DEFAULT '0000' NOT NULL,
  `amount` FLOAT(23,2) DEFAULT '0.00' NULL,
  PRIMARY KEY  (`id`)
) DEFAULT CHARACTER SET utf8, ENGINE=MyISAM AUTO_INCREMENT=1 ;


CREATE TABLE `nameserver` (
  `id` int(11) NOT NULL auto_increment,
  `serverid` int(11) NOT NULL default '0',
  `ip` varchar(50) NOT NULL default '',
  `hostname` varchar(50) NOT NULL default '',
  PRIMARY KEY  (`id`)
) DEFAULT CHARACTER SET utf8, ENGINE=MyISAM AUTO_INCREMENT=1 ;


CREATE TABLE `package` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `planname` varchar(45) NOT NULL,
  `description` text NOT NULL,
  `planid` int(11) NOT NULL DEFAULT '0',
  `showpackage` smallint(6) NOT NULL DEFAULT '1',
  `pricing` text NOT NULL,
  `style` tinyint(4) DEFAULT '0',
  `automaticactivation` tinyint(4) DEFAULT '1',
  `allowdirectlink` tinyint(4) NOT NULL DEFAULT '1',
  `sendwelcome` tinyint(4) NOT NULL DEFAULT '1',
  `stockInfo` text NOT NULL,
  `emailTemplate` smallint(5) NOT NULL DEFAULT '0',
  `bundledProducts` TEXT NOT NULL,
  `advanced` text NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `id_2` (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0 ;


CREATE TABLE `package_variable` (
  `packageid` int(11) NOT NULL default '0',
  `varname` varchar(50) NOT NULL default '',
  `value` varchar(200) NOT NULL default '',
  PRIMARY KEY  (`packageid`,`varname`)
) DEFAULT CHARACTER SET utf8, ENGINE=MyISAM;

CREATE TABLE `packageaddon` (
    `id` INT NOT NULL AUTO_INCREMENT ,
    `package_id` INT NOT NULL ,
    `name` varchar(50) NOT NULL,
    `description` text NOT NULL,
    `plugin_var` VARCHAR( 50 ) NOT NULL ,
    `order` int(11) NOT NULL default '0',
    `taxable` tinyint(4) NOT NULL default '0',
    PRIMARY KEY ( `id` )
) DEFAULT CHARACTER SET utf8, ENGINE=MyISAM;

CREATE TABLE `packageaddon_prices` (
    `id` int(11) NOT NULL auto_increment,
    `packageaddon_id` int(11) NOT NULL default '0',
    `sortkey` int(11) NOT NULL default '0',
    `detail` varchar(50) NOT NULL default '',
    `plugin_var_value` varchar(50) NOT NULL default '',
    `price0` float(23,2) NOT NULL default '-1.00',
    `price1` float(23,2) NOT NULL default '-1.00',
    `price1_force` smallint(6) NOT NULL default '0',
    `price3` float(23,2) NOT NULL default '-1.00',
    `price3_force` smallint(6) NOT NULL default '0',
    `price6` float(23,2) NOT NULL default '-1.00',
    `price6_force` tinyint(4) NOT NULL default '0',
    `price12` float(23,2) NOT NULL default '-1.00',
    `price12_force` tinyint(4) NOT NULL default '0',
    `price24` float(23,2) NOT NULL default '-1.00',
    `price24_force` tinyint(4) NOT NULL default '0',
    PRIMARY KEY  (`id`)
) DEFAULT CHARACTER SET utf8, ENGINE=MyISAM;

CREATE TABLE `package_server` (
  `id` int(11) NOT NULL auto_increment,
  `package_id` int(11) NOT NULL default '0',
  `server_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) DEFAULT CHARACTER SET utf8, ENGINE=MyISAM;

CREATE TABLE  `plugin_custom_data` (
 `name` VARCHAR( 50 ) NOT NULL ,
 `value` TEXT NULL ,
 `plugin_name` VARCHAR( 25 ) NOT NULL ,
 `plugin_type` VARCHAR( 25 ) NOT NULL ,
 `user_id` INT NOT NULL ,
PRIMARY KEY (  `name` ,  `plugin_name` ,  `plugin_type` ,  `user_id` )
) DEFAULT CHARACTER SET utf8, ENGINE=MyISAM;

CREATE TABLE `promotion` (
  `id` int(11) NOT NULL auto_increment,
  `description` text NOT NULL,
  `insignup` smallint(1) NOT NULL default '0',
  `name` varchar(50) NOT NULL default '',
  `type` smallint(1) NOT NULL default '0',
  `canDelete` tinyint(1) NOT NULL default '1',
  `groupOrder` tinyint(3) NOT NULL default '1',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0 ;

CREATE TABLE `promotion_customdomainfields` (
`promotionid` INT NOT NULL ,
`customid` INT NOT NULL
) DEFAULT CHARACTER SET utf8, ENGINE = MYISAM ;

CREATE TABLE `recurringfee` (
  `id` int(11) NOT NULL auto_increment,
  `customerid` int(11) NOT NULL default '0',
  `billingtypeid` int(11) NOT NULL default '0',
  `packageaddon_prices_id` int(11) NOT NULL default '0',
  `description` text default NULL,
  `detail` text default NULL,
  `amount` float(23,2) NOT NULL default '0.00',
  `amount_percent` float(23,2) NOT NULL default '0.00',
  `appliestoid` int(11) NOT NULL default '0',
  `coupon_applicable_to` tinyint(4) NOT NULL default '0',
  `nextbilldate` date default NULL,
  `paymentterm` tinyint(4) NOT NULL default '1',
  `disablegenerate` tinyint(4) NOT NULL default '0',
  `taxable` tinyint(4) NOT NULL default '0',
  `monthlyusage` text default NULL,
  `recurring` tinyint(4) NOT NULL default '0',
  `subscription_id` text default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `id_2` (`id`)
) DEFAULT CHARACTER SET utf8, ENGINE=MyISAM AUTO_INCREMENT=1 ;

CREATE TABLE `report` (
    `name` VARCHAR( 80 ) NOT NULL ,
    `public` TINYINT DEFAULT '0' NOT NULL ,
    `quickgraph` TINYINT DEFAULT '0' NOT NULL,
    PRIMARY KEY ( `name` )
) DEFAULT CHARACTER SET utf8, ENGINE=MyISAM;

CREATE TABLE `server` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(25) NOT NULL default '',
  `hostname` varchar(50) NOT NULL default '',
  `sharedip` varchar(50) NOT NULL default '',
  `isdefault` tinyint(1) NOT NULL default '0',
  `plugin` varchar(25) NOT NULL default '',
  `last_utilization` datetime NOT NULL default '0000-00-00 00:00:00',
  `domains_quota` int(11) NOT NULL default '0',
  `statsurl` VARCHAR( 225 ) NOT NULL ,
  `statsviewable` TINYINT DEFAULT '0' NOT NULL,
  PRIMARY KEY  (`id`)
) DEFAULT CHARACTER SET utf8, ENGINE=MyISAM;

CREATE TABLE `serverip` (
  `serverid` int(11) NOT NULL default '0',
  `ip` varchar(50) NOT NULL default ''
) DEFAULT CHARACTER SET utf8, ENGINE=MyISAM;

CREATE TABLE `serverplugin_options` (
  `serverid` int(11) NOT NULL default '0',
  `optionname` varchar(125) NOT NULL default '',
  `value` longblob NOT NULL,
  PRIMARY KEY  (`serverid`,`optionname`)
) DEFAULT CHARACTER SET utf8, ENGINE=MyISAM;

CREATE TABLE `setting_options` (
  `id` int(11) NOT NULL auto_increment,
  `settingid` int(11) NOT NULL default '0',
  `name` varchar(50) NOT NULL default '',
  `value` text,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `id_2` (`id`)
) DEFAULT CHARACTER SET utf8, ENGINE=MyISAM COMMENT='Contains all the available options for a giving setting' AUTO_INCREMENT=1 ;

CREATE TABLE `taxrule` (
  `countrycode` varchar(5) NOT NULL default '',
  `state` varchar(20) NOT NULL default '',
  `tax` float(23,2) NOT NULL default '0.00',
  `vat` tinyint(4) NOT NULL default '0',
  `name` varchar(20) NOT NULL default '',
  `level` tinyint(4) NOT NULL default '1',
  `compound` tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (`countrycode`,`state`,`level`)
) DEFAULT CHARACTER SET utf8, ENGINE=MyISAM;

CREATE TABLE `team_status` (
  `id` int(11) NOT NULL auto_increment,
  `userid` int(11) NOT NULL,
  `userstatus` text NOT NULL,
  `status_datetime` datetime NOT NULL,
  `replyid` int(11),
  PRIMARY KEY  (`id`)
) DEFAULT CHARACTER SET utf8, ENGINE=MyISAM;

CREATE TABLE `team_status_follow` (
  `userid` int(11) NOT NULL,
  `groupid` int(11) NOT NULL,
  UNIQUE(`userid`,`groupid`)
) DEFAULT CHARACTER SET utf8, ENGINE=MyISAM;

CREATE TABLE `users` (
  `id` int(11) NOT NULL auto_increment,
  `company_id` int(11) NOT NULL DEFAULT '0',
  `current` tinyint(1) default '1',
  `paymenttype` varchar(30) NOT NULL default '0',
  `clienttype` tinyint(4) NOT NULL default '0',
  `groupid` int(10) default '1',
  `password` varchar(45) default NULL,
  `dateActivated` date default NULL,
  `signature_text` text default NULL,
  `signature_html` text default NULL,
  `loggedin` SMALLINT( 1 ) NOT NULL DEFAULT '0',
  `lastlogin` datetime default NULL,
  `lastseen` datetime default NULL,
  `lastview` TEXT,
  `isOrganization` tinyint(4) default '0',
  `active` smallint(6) NOT NULL default '1',
  `recurring` tinyint(4) default '1',
  `currency` varchar(5) default '0',
  `data1` text default NULL,
  `autopayment` smallint(1) NOT NULL default '0',
  `ccmonth` varchar(4) NOT NULL default '0',
  `ccyear` varchar(4) NOT NULL default '0',
  `cclastfour` varchar(5) NOT NULL default '',
  `data2` text default NULL,
  `data3` text default NULL,
  `passphrased` smallint(1) NOT NULL default '0',
  `updating` tinyint(4) NOT NULL default '0',
  `taxable` tinyint(4) NOT NULL default '0',
  `status` tinyint(4) NOT NULL default '0',
  `firstname` varchar(25) NOT NULL default '',
  `lastname` varchar(25) NOT NULL default '',
  `email` varchar(60) NOT NULL default '',
  `organization` varchar(60) NOT NULL default '',
  `usernotes` longtext default NULL,
  `usernotespos` varchar(75) NOT NULL default '',
  `warningmask` int(11) NOT NULL default '0',
  `balance` FLOAT(23,2) DEFAULT '0.00' NOT NULL,
  `chatstatus` tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) DEFAULT CHARACTER SET utf8, ENGINE=MyISAM AUTO_INCREMENT=101 ;
ALTER TABLE `users` ADD INDEX `loggedin` ( `loggedin` );
ALTER TABLE  `users` ADD INDEX  `i_lastx` (  `lastseen` ,  `loggedin` ,  `groupid` );
ALTER TABLE `users` ADD INDEX `i_company_id` (`company_id`);

CREATE TABLE `companies` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `hashed_id` varchar(32) NOT NULL,
    `subdomain` varchar(20) NOT NULL,
    `status` tinyint(4) NOT NULL,
    `type` varchar(50),
    `limit_staff` tinyint(4) NOT NULL DEFAULT '-1',
    PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `productgroup_addon` (
  `productgroup_id` int(11) NOT NULL,
  `addon_id` int(11) NOT NULL
) DEFAULT CHARACTER SET utf8, ENGINE=MyISAM;

CREATE TABLE `product_addon` (
  `product_id` int(11) NOT NULL,
  `addon_id` int(11) NOT NULL,
  `order` int(11) NOT NULL
) DEFAULT CHARACTER SET utf8, ENGINE=MyISAM;

CREATE TABLE IF NOT EXISTS active_orders (
    `id` VARCHAR(13) NOT NULL DEFAULT '',
    `product_id` int(11) NOT NULL DEFAULT 0,
    `expires` datetime NOT NULL,
    PRIMARY KEY(`id`),
    INDEX  `prod` (  `product_id` )  
) DEFAULT CHARACTER SET utf8, ENGINE=MyISAM;

CREATE TABLE `companies_plugins` (
  `company_id` int NOT NULL,
  `plugin` varchar(25) NOT NULL,
  `permission` tinyint NOT NULL DEFAULT '0'
) ENGINE=MyISAM;
