-- Execute this file directly against the target database after taking a backup.
-- Do not run `php artisan migrate` for this deployment.
-- This script contains no DROP, TRUNCATE, DELETE, or UPDATE statements.

CREATE TABLE IF NOT EXISTS `bot_manual_audits` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `request_id` varchar(64) NOT NULL,
  `request_hash` char(64) NOT NULL,
  `admin_id` bigint unsigned NULL DEFAULT NULL,
  `balance_log_id` bigint unsigned NULL DEFAULT NULL,
  `member_id` varchar(64) NOT NULL,
  `username` varchar(128) NOT NULL DEFAULT '',
  `first_name` varchar(128) NOT NULL DEFAULT '',
  `transaction_type` varchar(16) NOT NULL,
  `change_type` varchar(50) NOT NULL,
  `amount` decimal(30, 10) NOT NULL,
  `before_balance` decimal(30, 10) NULL DEFAULT NULL,
  `after_balance` decimal(30, 10) NULL DEFAULT NULL,
  `level_before` int NOT NULL DEFAULT 0,
  `level_before_name` varchar(100) NOT NULL DEFAULT '',
  `level_after` int NOT NULL DEFAULT 0,
  `level_after_name` varchar(100) NOT NULL DEFAULT '',
  `status` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '0=failed, 1=success',
  `remark` varchar(255) NOT NULL DEFAULT '',
  `failure_reason` varchar(255) NOT NULL DEFAULT '',
  `created_at` datetime NULL DEFAULT NULL,
  `updated_at` datetime NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_manual_audits_request_id` (`request_id`),
  UNIQUE KEY `uniq_manual_audits_balance_log_id` (`balance_log_id`),
  KEY `idx_manual_audits_member_created` (`member_id`, `created_at`),
  KEY `idx_manual_audits_type_created` (`transaction_type`, `created_at`),
  KEY `idx_manual_audits_status_created` (`status`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Import successful historical admin adjustments. Automatic rewards use a
-- non-NULL related_id and are intentionally excluded.
INSERT IGNORE INTO `bot_manual_audits` (
  `request_id`,
  `request_hash`,
  `admin_id`,
  `balance_log_id`,
  `member_id`,
  `username`,
  `first_name`,
  `transaction_type`,
  `change_type`,
  `amount`,
  `before_balance`,
  `after_balance`,
  `level_before`,
  `level_before_name`,
  `level_after`,
  `level_after_name`,
  `status`,
  `remark`,
  `failure_reason`,
  `created_at`,
  `updated_at`
)
SELECT
  CONCAT('legacy-', bl.`id`),
  SHA2(CONCAT('legacy-', bl.`id`), 256),
  NULL,
  bl.`id`,
  bl.`member_id`,
  COALESCE(u.`username`, ''),
  COALESCE(u.`first_name`, ''),
  CASE WHEN bl.`amount` < 0 THEN 'debit' ELSE 'credit' END,
  bl.`change_type`,
  bl.`amount`,
  bl.`before_balance`,
  bl.`after_balance`,
  COALESCE(u.`level`, 0),
  COALESCE(
    NULLIF(l.`level_name`, ''),
    CASE WHEN COALESCE(u.`level`, 0) > 0 THEN CONCAT('等级', u.`level`) ELSE '普通会员' END
  ),
  COALESCE(u.`level`, 0),
  COALESCE(
    NULLIF(l.`level_name`, ''),
    CASE WHEN COALESCE(u.`level`, 0) > 0 THEN CONCAT('等级', u.`level`) ELSE '普通会员' END
  ),
  1,
  COALESCE(bl.`remark`, ''),
  '',
  COALESCE(bl.`created_at`, NOW()),
  COALESCE(bl.`updated_at`, bl.`created_at`, NOW())
FROM `bot_balance_logs` AS bl
LEFT JOIN `bot_users` AS u ON u.`member_id` = bl.`member_id`
LEFT JOIN `bot_level` AS l ON l.`level` = u.`level`
WHERE bl.`member_id` IS NOT NULL
  AND bl.`member_id` <> ''
  AND bl.`related_id` IS NULL
  AND (
    (bl.`change_type` IN ('人工充值', '注册赠送', '优惠活动') AND bl.`amount` > 0)
    OR (bl.`change_type` = '人工扣款' AND bl.`amount` < 0)
  );

-- Indexes used by the finance summary and third-party deposit queries.
SET @index_exists = (
  SELECT COUNT(*)
  FROM information_schema.statistics
  WHERE table_schema = DATABASE()
    AND table_name = 'bot_balance_logs'
    AND index_name = 'idx_balance_logs_finance_type_created'
);
SET @ddl = IF(
  @index_exists = 0,
  'ALTER TABLE `bot_balance_logs` ADD INDEX `idx_balance_logs_finance_type_created` (`change_type`, `created_at`, `related_id`, `member_id`)',
  'SELECT 1'
);
PREPARE admin_finance_stmt FROM @ddl;
EXECUTE admin_finance_stmt;
DEALLOCATE PREPARE admin_finance_stmt;

SET @index_exists = (
  SELECT COUNT(*)
  FROM information_schema.statistics
  WHERE table_schema = DATABASE()
    AND table_name = 'bot_balance_logs'
    AND index_name = 'idx_balance_logs_finance_related_type'
);
SET @ddl = IF(
  @index_exists = 0,
  'ALTER TABLE `bot_balance_logs` ADD INDEX `idx_balance_logs_finance_related_type` (`related_id`, `change_type`, `id`)',
  'SELECT 1'
);
PREPARE admin_finance_stmt FROM @ddl;
EXECUTE admin_finance_stmt;
DEALLOCATE PREPARE admin_finance_stmt;

SET @index_exists = (
  SELECT COUNT(*)
  FROM information_schema.statistics
  WHERE table_schema = DATABASE()
    AND table_name = 'bot_payment_orders'
    AND index_name = 'idx_payment_orders_finance_status_type_created'
);
SET @ddl = IF(
  @index_exists = 0,
  'ALTER TABLE `bot_payment_orders` ADD INDEX `idx_payment_orders_finance_status_type_created` (`status`, `type`, `created_at`, `member_id`)',
  'SELECT 1'
);
PREPARE admin_finance_stmt FROM @ddl;
EXECUTE admin_finance_stmt;
DEALLOCATE PREPARE admin_finance_stmt;

SET @index_exists = (
  SELECT COUNT(*)
  FROM information_schema.statistics
  WHERE table_schema = DATABASE()
    AND table_name = 'bot_user_login'
    AND index_name = 'idx_user_login_finance_user_status_created'
);
SET @login_table_exists = (
  SELECT COUNT(*)
  FROM information_schema.tables
  WHERE table_schema = DATABASE()
    AND table_name = 'bot_user_login'
);
SET @status_column_exists = (
  SELECT COUNT(*)
  FROM information_schema.columns
  WHERE table_schema = DATABASE()
    AND table_name = 'bot_user_login'
    AND column_name = 'status'
);
SET @ddl = IF(
  @login_table_exists = 0 OR @index_exists > 0,
  'SELECT 1',
  IF(
    @status_column_exists > 0,
    'ALTER TABLE `bot_user_login` ADD INDEX `idx_user_login_finance_user_status_created` (`user_id`, `status`, `created_at`, `id`)',
    'ALTER TABLE `bot_user_login` ADD INDEX `idx_user_login_finance_user_status_created` (`user_id`, `created_at`, `id`)'
  )
);
PREPARE admin_finance_stmt FROM @ddl;
EXECUTE admin_finance_stmt;
DEALLOCATE PREPARE admin_finance_stmt;
