-- 
-- Table structure for table `rm_cards`
-- 

CREATE TABLE `rm_cards` (
  `id` bigint(20) NOT NULL,
  `cardnum` varchar(16) NOT NULL,
  `value` decimal(22,2) NOT NULL,
  `expiration` date NOT NULL,
  `series` varchar(16) NOT NULL,
  `date` date NOT NULL,
  `owner` varchar(20) NOT NULL,
  `used` datetime NOT NULL,
  `cardtype` tinyint(1) NOT NULL,
  `revoked` tinyint(1) NOT NULL,
  `downlimit` int(11) NOT NULL,
  `uplimit` int(11) NOT NULL,
  `timelimit` int(11) NOT NULL,
  `srvid` int(11) NOT NULL,
  `transid` int(11) NOT NULL,
  PRIMARY KEY  (`id`)
) ;

-- 
-- Table structure for table `rm_changesrv`
-- 

CREATE TABLE `rm_changesrv` (
  `id` int(11) NOT NULL auto_increment,
  `username` varchar(32) NOT NULL,
  `newsrvid` int(11) NOT NULL,
  `newsrvname` varchar(40) NOT NULL,
  `scheduledate` date NOT NULL,
  `requestdate` date NOT NULL,
  `status` tinyint(1) NOT NULL,
  `transid` int(11) NOT NULL,
  `requested` varchar(32) NOT NULL,
  PRIMARY KEY  (`id`)
) ;

-- 
-- Table structure for table `rm_settings`
-- 

CREATE TABLE `rm_settings` (
  `currency` varchar(15) NOT NULL,
  `unixacc` tinyint(1) NOT NULL,
  `diskquota` tinyint(1) NOT NULL,
  `quotatpl` varchar(30) NOT NULL,
  `taxtypecash` tinyint(1) NOT NULL,
  `taxtypefirm` tinyint(1) NOT NULL,
  `paymentopt` int(11) NOT NULL,
  `enchangesrv` tinyint(1) NOT NULL,
  `taxpercentglobal` decimal(4,2) NOT NULL,
  `disablenotpaid` tinyint(1) NOT NULL,
  `resetctr` tinyint(1) NOT NULL
) ;

-- 
-- Dumping data for table `rm_settings`
-- 

INSERT INTO `rm_settings` (`currency`, `unixacc`, `diskquota`, `quotatpl`, `taxtypecash`, `taxtypefirm`, `paymentopt`, `enchangesrv`, `taxpercentglobal`, `disablenotpaid`, `resetctr`) VALUES ('USD', 0, 1, 'template', 0, 0, 7, 1, 18.00, 1, 1);

# rename tables

ALTER TABLE `mt_managers` RENAME `rm_managers` ;
ALTER TABLE `mt_payments` RENAME `rm_invoices` ;
ALTER TABLE `mt_payouts` RENAME `rm_payouts` ;
ALTER TABLE `mt_profiles` RENAME `rm_services` ;
ALTER TABLE `mt_users` RENAME `rm_users` ;

# upgrade tables

ALTER TABLE `rm_users` CHANGE `username` `username` VARCHAR( 32 ) NOT NULL ;
ALTER TABLE `rm_users` CHANGE `password` `password` VARCHAR( 32 ) NOT NULL ;
ALTER TABLE `rm_users` CHANGE `enableuser` `enableuser` TINYINT( 1 ) NOT NULL ;
ALTER TABLE `rm_users` CHANGE `uplimit` `uplimit` BIGINT( 20 ) NOT NULL ;
ALTER TABLE `rm_users` CHANGE `downlimit` `downlimit` BIGINT( 20 ) NOT NULL ;
ALTER TABLE `rm_users` CHANGE `realname` `fullname` VARCHAR( 30 ) NOT NULL ;
ALTER TABLE `rm_users` CHANGE `macid` `macid` INT( 11 ) NOT NULL ;
ALTER TABLE `rm_users` CHANGE `usemacauth` `usemacauth` TINYINT( 1 ) NOT NULL ;
ALTER TABLE `rm_users` CHANGE `expiration` `expiration` DATE NOT NULL ;
ALTER TABLE `rm_users` CHANGE `uptimelimit` `uptimelimit` BIGINT( 20 ) NOT NULL ;
ALTER TABLE `rm_users` CHANGE `profid` `srvid` INT NOT NULL ;
ALTER TABLE `rm_users` CHANGE `usestaticip` `usestaticip` TINYINT( 1 ) NOT NULL ;
ALTER TABLE `rm_users` CHANGE `createdon` `createdon` DATE NOT NULL ;
ALTER TABLE `rm_users` CHANGE `maconlyauth` `acctype` TINYINT( 1 ) NOT NULL ;
ALTER TABLE `rm_users` ADD `credits` DECIMAL( 20, 2 ) NOT NULL ,
ADD `cardfails` TINYINT NOT NULL ,
ADD `createdby` VARCHAR( 16 ) NOT NULL ,
ADD `taxid` VARCHAR( 40 ) NOT NULL ;
UPDATE rm_users SET PASSWORD = MD5( PASSWORD ) ;

ALTER TABLE `rm_managers` CHANGE `username` `managername` VARCHAR( 32 ) NOT NULL ;
ALTER TABLE `rm_managers` CHANGE `password` `password` VARCHAR( 32 ) NOT NULL ;
ALTER TABLE `rm_managers` CHANGE `realname` `fullname` VARCHAR( 30 ) NOT NULL ;
ALTER TABLE `rm_managers` CHANGE `perm_listusers` `perm_listusers` TINYINT( 1 ) NOT NULL ;
ALTER TABLE `rm_managers` CHANGE `perm_createusers` `perm_createusers` TINYINT( 1 ) NOT NULL ;
ALTER TABLE `rm_managers` CHANGE `perm_editusers` `perm_editusers` TINYINT( 1 ) NOT NULL ;
ALTER TABLE `rm_managers` CHANGE `perm_deleteusers` `perm_deleteusers` TINYINT( 1 ) NOT NULL ;
ALTER TABLE `rm_managers` CHANGE `perm_listmanagers` `perm_listmanagers` TINYINT( 1 ) NOT NULL ;
ALTER TABLE `rm_managers` CHANGE `perm_createmanagers` `perm_createmanagers` TINYINT( 1 ) NOT NULL ;
ALTER TABLE `rm_managers` CHANGE `perm_editmanagers` `perm_editmanagers` TINYINT( 1 ) NOT NULL ;
ALTER TABLE `rm_managers` CHANGE `perm_deletemanagers` `perm_deletemanagers` TINYINT( 1 ) NOT NULL ;
ALTER TABLE `rm_managers` CHANGE `perm_listprofiles` `perm_listservices` TINYINT( 1 ) NOT NULL ;
ALTER TABLE `rm_managers` CHANGE `perm_createprofiles` `perm_createservices` TINYINT( 1 ) NOT NULL ;
ALTER TABLE `rm_managers` CHANGE `perm_editprofiles` `perm_editservices` TINYINT( 1 ) NOT NULL ;
ALTER TABLE `rm_managers` CHANGE `perm_deleteprofiles` `perm_deleteservices` TINYINT( 1 ) NOT NULL ;
ALTER TABLE `rm_managers` CHANGE `perm_listonlineusers` `perm_listonlineusers` TINYINT( 1 ) NOT NULL ;
ALTER TABLE `rm_managers` CHANGE `perm_listpayments` `perm_listinvoices` TINYINT( 1 ) NOT NULL ;
ALTER TABLE `rm_managers` CHANGE `perm_listpayouts` `perm_listpayouts` TINYINT( 1 ) NOT NULL ;
ALTER TABLE `rm_managers` DROP `perm_searchusers` ;
ALTER TABLE `rm_managers` CHANGE `perm_trafficreport` `perm_trafficreport` TINYINT( 1 ) NOT NULL ;
ALTER TABLE `rm_managers` CHANGE `perm_addcredits` `perm_addcredits` TINYINT( 1 ) NOT NULL ;
ALTER TABLE `rm_managers` CHANGE `perm_makepayouts` `perm_makepayouts` TINYINT( 1 ) NOT NULL ;
ALTER TABLE `rm_managers` CHANGE `perm_listallpayments` `perm_listallinvoices` TINYINT( 1 ) NOT NULL ;
ALTER TABLE `rm_managers` ADD `perm_cardsys` TINYINT( 1 ) NOT NULL ,
ADD `perm_editinvoice` TINYINT( 1 ) NOT NULL ;
ALTER TABLE `rm_managers` CHANGE `perm_logout` `perm_logout` TINYINT( 1 ) NOT NULL ;
UPDATE rm_managers SET PASSWORD = MD5( PASSWORD ) ;

ALTER TABLE `rm_services` CHANGE `profid` `srvid` INT( 11 ) NOT NULL ;
ALTER TABLE `rm_services` CHANGE `profname` `srvname` VARCHAR( 40 ) NOT NULL ;
ALTER TABLE `rm_services` CHANGE `downrate` `downrate` INT( 11 ) NOT NULL ;
ALTER TABLE `rm_services` CHANGE `uprate` `uprate` INT( 11 ) NOT NULL ;
ALTER TABLE `rm_services` CHANGE `limitdl` `limitdl` TINYINT( 1 ) NOT NULL ;
ALTER TABLE `rm_services` CHANGE `limitul` `limitul` TINYINT( 1 ) NOT NULL ;
ALTER TABLE `rm_services` CHANGE `limitexpiration` `limitexpiration` TINYINT( 1 ) NOT NULL ;
ALTER TABLE `rm_services` CHANGE `limituptime` `limituptime` TINYINT( 1 ) NOT NULL ;
ALTER TABLE `rm_services` ADD `unitprice` DECIMAL( 20, 2 ) NOT NULL ,
ADD `unitpriceadd` DECIMAL( 20, 2 ) NOT NULL ,
ADD `timebase` TINYINT( 1 ) NOT NULL ,
ADD `timeunit` INT( 11 ) NOT NULL ,
ADD `trafficunit` INT( 11 ) NOT NULL ,
ADD `srvtype` TINYINT( 1 ) NOT NULL ,
ADD `timeaddmode` TINYINT( 1 ) NOT NULL ,
ADD `trafficaddmode` TINYINT( 1 ) NOT NULL ,
ADD `timeadd` TINYINT( 1 ) NOT NULL ,
ADD `trafficadd` TINYINT( 1 ) NOT NULL ,
ADD `monthly` TINYINT( 1 ) NOT NULL ,
ADD `enaddcredits` TINYINT( 1 ) NOT NULL ,
ADD `taxpercent` DECIMAL( 4, 2 ) NOT NULL ,
ADD `minamount` INT( 20 ) NOT NULL ,
ADD `resetcounters` TINYINT( 1 ) NOT NULL ,
ADD `pricecalcdownload` TINYINT( 1 ) NOT NULL ,
ADD `pricecalcupload` TINYINT( 1 ) NOT NULL ,
ADD `pricecalcuptime` TINYINT( 1 ) NOT NULL ,
ADD `unitpricetax` DECIMAL( 20, 2 ) NOT NULL ,
ADD `unitpriceaddtax` DECIMAL( 20, 2 ) NOT NULL ;

ALTER TABLE `rm_payouts` ADD `id` INT( 11 ) NOT NULL FIRST ;
ALTER IGNORE TABLE `rm_payouts` ADD PRIMARY KEY ( `id` ) ;
ALTER TABLE `rm_payouts` CHANGE `id` `id` INT( 11 ) NOT NULL AUTO_INCREMENT ;
ALTER TABLE `rm_payouts` CHANGE `manager` `managername` VARCHAR( 32 ) NOT NULL ;
ALTER TABLE `rm_payouts` CHANGE `payout` `payout` DECIMAL( 20, 2 ) NOT NULL ;
ALTER TABLE `rm_payouts` CHANGE `datetime` `datetime` DATETIME NOT NULL ;
ALTER TABLE `rm_payouts` ADD `transid` INT( 11 ) NOT NULL ;

ALTER TABLE `rm_invoices` CHANGE `id` `id` INT NOT NULL AUTO_INCREMENT ;
ALTER TABLE `rm_invoices` ADD `invnum` VARCHAR( 16 ) NOT NULL AFTER `id` ;
ALTER TABLE `rm_invoices` CHANGE `manager` `managername` VARCHAR( 32 ) NOT NULL ;
ALTER TABLE `rm_invoices` CHANGE `username` `username` VARCHAR( 32 ) NOT NULL ;
ALTER TABLE `rm_invoices` CHANGE `datetime` `date` DATE NOT NULL ;
ALTER TABLE `rm_invoices` CHANGE `price` `price` DECIMAL( 20, 2 ) NOT NULL ;
ALTER TABLE `rm_invoices` CHANGE `bytesdown` `bytesdown` BIGINT( 20 ) NOT NULL ;
ALTER TABLE `rm_invoices` CHANGE `bytesup` `bytesup` BIGINT( 20 ) NOT NULL ;
ALTER TABLE `rm_invoices` CHANGE `downlimit` `downlimit` BIGINT( 20 ) NOT NULL ;
ALTER TABLE `rm_invoices` CHANGE `uplimit` `uplimit` BIGINT( 20 ) NOT NULL ;
ALTER TABLE `rm_invoices` CHANGE `hours` `hours` INT( 11 ) NOT NULL ;
ALTER TABLE `rm_invoices` CHANGE `uptimelimit` `uptimelimit` BIGINT( 20 ) NOT NULL ;
ALTER TABLE `rm_invoices` CHANGE `days` `days` INT( 6 ) NOT NULL ;
ALTER TABLE `rm_invoices` CHANGE `expiration` `expiration` DATE NOT NULL ;
ALTER TABLE `rm_invoices` CHANGE `profname` `service` VARCHAR( 40 ) NOT NULL ;
ALTER TABLE `rm_invoices` ADD `transid` INT( 11 ) NOT NULL ,
ADD `unitprice` DECIMAL( 20, 2 ) NOT NULL ,
ADD `amount` DECIMAL( 13, 2 ) NOT NULL ,
ADD `taxpercent` DECIMAL( 4, 2 ) NOT NULL ,
ADD `taxtype` TINYINT( 1 ) NOT NULL ,
ADD `address` VARCHAR( 30 ) NOT NULL ,
ADD `city` VARCHAR( 15 ) NOT NULL ,
ADD `zip` VARCHAR( 8 ) NOT NULL ,
ADD `fullname` VARCHAR( 30 ) NOT NULL ,
ADD `taxid` VARCHAR( 40 ) NOT NULL ,
ADD `paymentopt` DATE NOT NULL ,
ADD `invtype` TINYINT( 1 ) NOT NULL ,
ADD `paymode` TINYINT( 1 ) NOT NULL ,
ADD `paid` DATE NOT NULL ,
ADD `tax` DECIMAL( 20, 2 ) NOT NULL ,
ADD `remark` VARCHAR( 400 ) NOT NULL ;
