ALTER TABLE `radacct` ADD INDEX ( `_AcctTime` ) ;

ALTER TABLE `radcheck` DROP INDEX `username` ,
ADD INDEX `username` ( `username` ) ;

ALTER TABLE `radgroupcheck` DROP INDEX `groupname` ,
ADD INDEX `groupname` ( `groupname` ) ;

ALTER TABLE `radgroupreply` DROP INDEX `groupname` ,
ADD INDEX `groupname` ( `groupname` ) ;

ALTER TABLE `radreply` DROP INDEX `username` ,
ADD INDEX `username` ( `username` ) ;

ALTER TABLE `radusergroup` DROP INDEX `username` ,
ADD INDEX `username` ( `username` ) ;

ALTER TABLE `rm_actsrv` ADD INDEX ( `datetime` ) ;
ALTER TABLE `rm_actsrv` ADD INDEX ( `username` ) ;

CREATE TABLE `rm_allowednases` (
  `srvid` int(11) NOT NULL,
  `nasid` int(11) NOT NULL,
  KEY `srvid` (`srvid`),
  KEY `nasid` (`nasid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

ALTER TABLE `rm_cards` ADD `password` VARCHAR( 8 ) NOT NULL AFTER `cardnum` ;
ALTER TABLE `rm_cards` CHANGE `transid` `transid` VARCHAR( 32 ) NOT NULL ;
ALTER TABLE `rm_cards` ADD UNIQUE (`cardnum`)  ;

ALTER TABLE `rm_changesrv` CHANGE `scheduledate` `scheduledate` DATE NOT NULL ;
ALTER TABLE `rm_changesrv` CHANGE `transid` `transid` VARCHAR( 32 ) NOT NULL ;

ALTER TABLE `rm_ias` ADD `simuse` INT NOT NULL ;
UPDATE rm_ias SET simuse = 1 ;

ALTER TABLE `rm_invoices` ADD `invgroup` tinyint( 1 ) NOT NULL AFTER `id` ;
ALTER TABLE `rm_invoices` DROP `unitprice` ;
ALTER TABLE `rm_invoices` CHANGE `paymode` `paymode` TINYINT( 4 ) NOT NULL ;
ALTER TABLE `rm_invoices` CHANGE `price` `price` DECIMAL( 25, 6 ) NOT NULL ;
ALTER TABLE `rm_invoices` CHANGE `tax` `tax` DECIMAL( 25, 6 ) NOT NULL ;
ALTER TABLE `rm_invoices` ADD INDEX ( `invnum` ) ;
ALTER TABLE `rm_invoices` ADD INDEX ( `username` ) ;
ALTER TABLE `rm_invoices` ADD INDEX ( `managername` ) ;
ALTER TABLE `rm_invoices` ADD INDEX ( `date` ) ;
ALTER TABLE `rm_invoices` ADD INDEX ( `gwtransid` ) ;
ALTER TABLE `rm_invoices` ADD INDEX ( `comment` ) ;
ALTER TABLE `rm_invoices` ADD INDEX ( `deleted` ) ;
ALTER TABLE `rm_invoices` ADD INDEX ( `paymode` ) ;
ALTER TABLE `rm_invoices` ADD INDEX ( `invgroup` ) ;
ALTER TABLE `rm_invoices` DROP `deleted` ;
ALTER TABLE `rm_invoices` CHANGE `vatpercent` `vatpercent` DECIMAL( 4, 2 ) NOT NULL ;
UPDATE `rm_invoices` SET amount = 1 WHERE amount > 1 ;

ALTER TABLE `rm_managers` ADD `country` VARCHAR( 50 ) NOT NULL AFTER `zip` ;
ALTER TABLE `rm_managers` ADD `state` VARCHAR( 50 ) NOT NULL AFTER `country` ;
ALTER TABLE `rm_managers` ADD `company` VARCHAR( 50 ) NOT NULL AFTER `comment` ;
ALTER TABLE `rm_managers` ADD `vatid` VARCHAR( 40 ) NOT NULL AFTER `company` ;
ALTER TABLE `rm_managers` ADD `email` VARCHAR( 50 ) NOT NULL AFTER `vatid` ;
ALTER TABLE `rm_managers` ADD `balance` DECIMAL( 20, 2 ) NOT NULL AFTER `email` ;
ALTER TABLE `rm_managers` ADD `perm_allowdiscount` TINYINT( 1 ) NOT NULL AFTER `perm_allusers` ;
ALTER TABLE `rm_managers` CHANGE `perm_makepayouts` `perm_negbalance` TINYINT( 1 ) NOT NULL ;
ALTER TABLE `rm_managers` DROP `perm_listpayouts` ;
UPDATE rm_managers SET perm_allowdiscount =1 ;

CREATE TABLE `rm_radacct` (
  `radacctid` bigint(21) NOT NULL,
  `acctuniqueid` varchar(32) NOT NULL,
  `username` varchar(64) NOT NULL,
  `acctstarttime` datetime NOT NULL,
  `acctstoptime` datetime NOT NULL,
  `acctsessiontime` int(12) NOT NULL,
  `acctsessiontimeratio` decimal(3,2) NOT NULL,
  `dlbytesstart` bigint(20) NOT NULL,
  `dlbytesstop` bigint(20) NOT NULL,
  `dlbytes` bigint(20) NOT NULL,
  `dlratio` decimal(3,2) NOT NULL,
  `ulbytesstart` bigint(20) NOT NULL,
  `ulbytesstop` bigint(20) NOT NULL,
  `ulbytes` bigint(20) NOT NULL,
  `ulratio` decimal(3,2) NOT NULL,
  PRIMARY KEY  (`radacctid`),
  KEY `acctuniqueid` (`acctuniqueid`),
  KEY `username` (`username`),
  KEY `acctstarttime` (`acctstarttime`),
  KEY `acctstoptime` (`acctstoptime`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

ALTER TABLE `rm_settings` DROP `exchangeusd` ;
ALTER TABLE `rm_settings` DROP `exchangegbp` ;

ALTER TABLE `rm_settings` CHANGE `pm_paypalcc` `pm_paypalstd` TINYINT( 1 ) NOT NULL ;
ALTER TABLE `rm_settings` ADD `pm_paypalpro` TINYINT( 1 ) NOT NULL AFTER `pm_paypalstd` ;
ALTER TABLE `rm_settings` CHANGE `pm_netcashcc` `pm_netcash` TINYINT( 1 ) NOT NULL ;
ALTER TABLE `rm_settings` CHANGE `pm_authorizenetcc` `pm_authorizenet` TINYINT( 1 ) NOT NULL ;
ALTER TABLE `rm_settings` ADD `pm_dps` TINYINT( 1 ) NOT NULL AFTER `pm_authorizenet` ;

CREATE TABLE `rm_specperiods` (
  `specperid` int(11) NOT NULL auto_increment,
  `srvid` int(11) NOT NULL,
  `starttime` time NOT NULL,
  `endtime` time NOT NULL,
  `timeratio` decimal(3,2) NOT NULL,
  `dlratio` decimal(3,2) NOT NULL,
  `ulratio` decimal(3,2) NOT NULL,
  `connallowed` tinyint(1) NOT NULL,
  `mon` tinyint(1) NOT NULL,
  `tue` tinyint(1) NOT NULL,
  `wed` tinyint(1) NOT NULL,
  `thu` tinyint(1) NOT NULL,
  `fri` tinyint(1) NOT NULL,
  `sat` tinyint(1) NOT NULL,
  `sun` tinyint(1) NOT NULL,
  PRIMARY KEY  (`specperid`),
  KEY `srvid` (`srvid`),
  KEY `fromtime` (`starttime`),
  KEY `totime` (`endtime`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

ALTER TABLE `rm_users` ADD `owner` VARCHAR( 64 ) NOT NULL AFTER `createdby` ;
ALTER TABLE `rm_users` ADD `verifysentnum` TINYINT( 4 ) NOT NULL ;
ALTER TABLE `rm_users` ADD `verifymobile` VARCHAR( 15 ) NOT NULL ;
ALTER TABLE `rm_users` ADD `groupid` INT NOT NULL AFTER `password` ;
ALTER TABLE `rm_users` ADD INDEX ( `groupid` ) ;
ALTER TABLE `rm_users` ADD INDEX ( `enableuser` ) ;
ALTER TABLE `rm_users` ADD INDEX ( `firstname` ) ;
ALTER TABLE `rm_users` ADD INDEX ( `lastname` ) ;
ALTER TABLE `rm_users` ADD INDEX ( `company` ) ;
ALTER TABLE `rm_users` ADD INDEX ( `phone` ) ;
ALTER TABLE `rm_users` ADD INDEX ( `mobile` ) ;
ALTER TABLE `rm_users` ADD INDEX ( `address` ) ;
ALTER TABLE `rm_users` ADD INDEX ( `city` ) ;
ALTER TABLE `rm_users` ADD INDEX ( `zip` ) ;
ALTER TABLE `rm_users` ADD INDEX ( `country` ) ;
ALTER TABLE `rm_users` ADD INDEX ( `state` ) ;
ALTER TABLE `rm_users` ADD INDEX ( `comment` ) ;
ALTER TABLE `rm_users` ADD INDEX ( `mac` ) ;
ALTER TABLE `rm_users` ADD INDEX ( `staticip` ) ;
ALTER TABLE `rm_users` ADD INDEX ( `acctype` ) ;
ALTER TABLE `rm_users` ADD INDEX ( `email` ) ;
ALTER TABLE `rm_users` ADD INDEX ( `maccm` ) ;
UPDATE rm_users SET owner = createdby;

CREATE TABLE `rm_usergroups` (
  `groupid` int(11) NOT NULL auto_increment,
  `groupname` varchar(50) NOT NULL,
  `descr` varchar(200) NOT NULL,
  PRIMARY KEY  (`groupid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

INSERT INTO `radius`.`rm_usergroups` (
`groupid` ,
`groupname` ,
`descr` 
)
VALUES ('0', 'Default group', '');

TRUNCATE TABLE `rm_allowednases` ;
INSERT INTO rm_allowednases( srvid, nasid ) 
SELECT rm_services.srvid, nas.id
FROM rm_services, nas ;

CREATE TABLE tmp(
`managername` varchar( 64 ) NOT NULL default '',
`sum` decimal( 25, 6 ) NOT NULL 
) ;

INSERT INTO tmp (managername, sum)
SELECT managername AS manager, - ( (
SELECT sum( price + tax ) 
FROM rm_invoices
WHERE managername = manager ) - ( 
SELECT COALESCE( sum( payout ) , 0 ) 
FROM rm_payouts
WHERE managername = manager ) 
)
FROM rm_invoices
WHERE managername
IN (
SELECT managername
FROM rm_managers
)
GROUP BY managername ;

UPDATE rm_managers INNER JOIN tmp USING (managername) SET balance = tmp.sum ;

DROP TABLE `tmp` ;
DROP TABLE `rm_payouts` ;
