-- MSORGVTU security hardening migration.
-- Run this on a backup/staging database first.
-- This does not hash existing plaintext/legacy passwords or PINs because the original plaintext is not recoverable.
-- The patched PHP code automatically rehashes user/admin passwords and PINs on next successful use.

ALTER TABLE `user` MODIFY `password` VARCHAR(255) NOT NULL;
ALTER TABLE `user` MODIFY `pin` VARCHAR(255) NULL;
ALTER TABLE `admin` MODIFY `password1` VARCHAR(255) NOT NULL;

-- Money columns should be DECIMAL. Verify values are clean before running on production.
ALTER TABLE `user` MODIFY `bal` DECIMAL(14,2) NOT NULL DEFAULT 0.00;
ALTER TABLE `user` MODIFY `refbal` DECIMAL(14,2) NOT NULL DEFAULT 0.00;
ALTER TABLE `deposit` MODIFY `amount` DECIMAL(14,2) NOT NULL DEFAULT 0.00;
ALTER TABLE `deposit` MODIFY `charge` DECIMAL(14,2) NOT NULL DEFAULT 0.00;
ALTER TABLE `transactions` MODIFY `amount` DECIMAL(14,2) NOT NULL DEFAULT 0.00;

-- Prevent duplicate credits and reduce duplicate identity records.
ALTER TABLE `deposit` ADD UNIQUE KEY `uniq_deposit_trans` (`trans`);
ALTER TABLE `user` ADD UNIQUE KEY `uniq_user_username` (`username`);
ALTER TABLE `user` ADD UNIQUE KEY `uniq_user_email` (`email`);
ALTER TABLE `user` ADD UNIQUE KEY `uniq_user_phone` (`phone`);
ALTER TABLE `user` ADD UNIQUE KEY `uniq_user_apikey` (`apikey`);

-- Recommended after moving secrets to environment variables and verifying the app reads them:
-- UPDATE `pay` SET `fsecret`='', `fpublickey`='', `psecret`='', `plivekey`='', `mapi`='', `msk`='', `contact`='';
-- UPDATE `mail` SET `host`='', `sender`='', `username`='', `password`='';
-- UPDATE `api` SET `apikey`='', `apimtn`='', `apiglo`='', `apiairtel`='', `apimobile`='', `mtnsend`='', `glosend`='', `mobilesend`='', `airtelsend`='', `sendlink`='';