Run the following SQL on existing installations:
-- Add loans_today column
ALTER TABLE `bank_player_accounts`
ADD COLUMN IF NOT EXISTS `loans_today` INT NOT NULL DEFAULT 0 AFTER `daily_transferred`;
-- Update loan status ENUM
ALTER TABLE `bank_loans`
MODIFY COLUMN `status` ENUM('pending', 'pending_approval', 'processing', 'approved', 'active', 'paid', 'defaulted', 'collections', 'denied', 'cancelled') NOT NULL DEFAULT 'pending';
-- Add processing_until column
ALTER TABLE `bank_loans`
ADD COLUMN IF NOT EXISTS `processing_until` DATETIME DEFAULT NULL AFTER `status`;
-- Update payment_type ENUM
ALTER TABLE `bank_loan_payments`
MODIFY COLUMN `payment_type` ENUM('scheduled', 'extra', 'garnishment', 'external') NOT NULL DEFAULT 'scheduled';
-- Add dividend columns
ALTER TABLE `bank_investments`
ADD COLUMN IF NOT EXISTS `last_dividend_day` INT NOT NULL DEFAULT 0 AFTER `current_value`,
ADD COLUMN IF NOT EXISTS `total_dividends` BIGINT NOT NULL DEFAULT 0 AFTER `last_dividend_day`;
-- Fix card daily limit column
ALTER TABLE `bank_cards` DROP COLUMN IF EXISTS `last_reset`;
ALTER TABLE `bank_cards` ADD COLUMN IF NOT EXISTS `last_reset_day` INT NOT NULL DEFAULT 0 AFTER `daily_used`;
UPDATE `bank_cards` SET `last_reset_day` = 0, `daily_used` = 0;
-- Create pending transfers table
CREATE TABLE IF NOT EXISTS `bank_pending_transfers` (
`id` INT NOT NULL AUTO_INCREMENT,
`from_citizenid` VARCHAR(50) NOT NULL,
`to_citizenid` VARCHAR(50) NOT NULL,
`amount` BIGINT NOT NULL,
`fee` BIGINT NOT NULL DEFAULT 0,
`transfer_type` ENUM('internal', 'external', 'wire') NOT NULL,
`expedited` TINYINT(1) NOT NULL DEFAULT 0,
`status` ENUM('pending', 'completed', 'cancelled', 'failed') NOT NULL DEFAULT 'pending',
`scheduled_completion` DATETIME NOT NULL,
`reason` VARCHAR(255) DEFAULT NULL,
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
`completed_at` TIMESTAMP NULL,
PRIMARY KEY (`id`),
KEY `idx_status` (`status`),
KEY `idx_scheduled` (`scheduled_completion`),
KEY `idx_from` (`from_citizenid`),
KEY `idx_to` (`to_citizenid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- Create audit log table
CREATE TABLE IF NOT EXISTS `bank_audit_log` (
`id` BIGINT NOT NULL AUTO_INCREMENT,
`timestamp` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
`citizenid` VARCHAR(50) DEFAULT NULL,
`account_name` VARCHAR(50) DEFAULT NULL,
`action` VARCHAR(50) NOT NULL,
`category` ENUM('account', 'transaction', 'loan', 'card', 'security', 'admin') NOT NULL,
`performed_by` VARCHAR(50) NOT NULL,
`details` JSON DEFAULT NULL,
`success` TINYINT(1) NOT NULL DEFAULT 1,
PRIMARY KEY (`id`),
KEY `idx_timestamp` (`timestamp`),
KEY `idx_citizenid` (`citizenid`),
KEY `idx_account` (`account_name`),
KEY `idx_action` (`action`),
KEY `idx_performed_by` (`performed_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
