-- Akash Hisab V5.0 + V5.2 safe migration
-- Import this file ONCE into the existing akashxyz_new_hisab122 database.
-- Existing admins, users, transactions and passwords are preserved.

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS=0;

ALTER TABLE `admins`
  ADD COLUMN IF NOT EXISTS `role` ENUM('super_admin','manager','operator','viewer') NOT NULL DEFAULT 'super_admin' AFTER `photo`,
  ADD COLUMN IF NOT EXISTS `is_active` TINYINT(1) NOT NULL DEFAULT 1 AFTER `role`,
  ADD COLUMN IF NOT EXISTS `last_login_at` DATETIME NULL AFTER `is_active`,
  ADD COLUMN IF NOT EXISTS `created_by` INT NULL AFTER `last_login_at`;

UPDATE `admins` SET `role`='super_admin', `is_active`=1 WHERE `id`=1;

ALTER TABLE `users`
  ADD COLUMN IF NOT EXISTS `custom_commission_rate` DECIMAL(7,4) NOT NULL DEFAULT 0.0000 AFTER `photo`,
  ADD COLUMN IF NOT EXISTS `is_archived` TINYINT(1) NOT NULL DEFAULT 0 AFTER `custom_commission_rate`,
  ADD COLUMN IF NOT EXISTS `archived_at` DATETIME NULL AFTER `is_archived`,
  ADD COLUMN IF NOT EXISTS `archived_by` INT NULL AFTER `archived_at`,
  ADD COLUMN IF NOT EXISTS `updated_at` DATETIME NULL AFTER `created_at`;

ALTER TABLE `transactions`
  ADD COLUMN IF NOT EXISTS `fixed_commission_rate` DECIMAL(7,4) NOT NULL DEFAULT 0.3500 AFTER `amount`,
  ADD COLUMN IF NOT EXISTS `custom_commission_rate` DECIMAL(7,4) NOT NULL DEFAULT 0.0000 AFTER `fixed_commission_rate`,
  ADD COLUMN IF NOT EXISTS `fixed_commission_amount` DECIMAL(14,4) NOT NULL DEFAULT 0.0000 AFTER `custom_commission_rate`,
  ADD COLUMN IF NOT EXISTS `custom_commission_amount` DECIMAL(14,4) NOT NULL DEFAULT 0.0000 AFTER `fixed_commission_amount`,
  ADD COLUMN IF NOT EXISTS `created_by` INT NULL AFTER `custom_commission_amount`,
  ADD COLUMN IF NOT EXISTS `updated_by` INT NULL AFTER `created_by`,
  ADD COLUMN IF NOT EXISTS `deleted_at` DATETIME NULL AFTER `updated_by`,
  ADD COLUMN IF NOT EXISTS `deleted_by` INT NULL AFTER `deleted_at`,
  ADD COLUMN IF NOT EXISTS `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `deleted_by`,
  ADD COLUMN IF NOT EXISTS `updated_at` DATETIME NULL AFTER `created_at`;

CREATE TABLE IF NOT EXISTS `commission_rate_history` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `fixed_rate` DECIMAL(7,4) NOT NULL DEFAULT 0.3500,
  `custom_rate` DECIMAL(7,4) NOT NULL DEFAULT 0.0000,
  `effective_from` DATE NOT NULL,
  `effective_to` DATE NULL,
  `changed_by` INT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_commission_user_date` (`user_id`,`effective_from`,`effective_to`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `notifications` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `admin_id` INT NOT NULL,
  `type` VARCHAR(40) NOT NULL DEFAULT 'system',
  `title` VARCHAR(160) NOT NULL,
  `message` TEXT NOT NULL,
  `reference_date` DATE NULL,
  `is_read` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_notification_daily` (`admin_id`,`type`,`reference_date`),
  KEY `idx_notification_admin_read` (`admin_id`,`is_read`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `backup_files` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `filename` VARCHAR(255) NOT NULL,
  `backup_type` ENUM('automatic','manual') NOT NULL DEFAULT 'manual',
  `file_size` BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `created_by` INT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_backup_filename` (`filename`),
  KEY `idx_backup_type_date` (`backup_type`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `activity_log` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `admin_id` INT NULL,
  `action` VARCHAR(80) NOT NULL,
  `entity_type` VARCHAR(60) NOT NULL,
  `entity_id` BIGINT NULL,
  `details` LONGTEXT NULL,
  `ip_address` VARCHAR(45) NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_activity_admin_date` (`admin_id`,`created_at`),
  KEY `idx_activity_entity` (`entity_type`,`entity_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `commission_rate_history`
  (`user_id`,`fixed_rate`,`custom_rate`,`effective_from`,`effective_to`,`changed_by`)
SELECT u.id, 0.3500, u.custom_commission_rate, '2000-01-01', NULL, 1
FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM commission_rate_history h WHERE h.user_id=u.id
);

UPDATE transactions t
JOIN users u ON u.id=t.user_id
SET
  t.fixed_commission_rate=0.3500,
  t.custom_commission_rate=u.custom_commission_rate,
  t.fixed_commission_amount=CASE WHEN t.type='ব্যয়' THEN ROUND(t.amount*0.0035,4) ELSE 0 END,
  t.custom_commission_amount=CASE WHEN t.type='ব্যয়' THEN ROUND(t.amount*(u.custom_commission_rate/100),4) ELSE 0 END
WHERE t.fixed_commission_amount=0 AND t.custom_commission_amount=0;

ALTER TABLE `transactions`
  ADD INDEX IF NOT EXISTS `idx_tx_active_date` (`deleted_at`,`date`,`id`),
  ADD INDEX IF NOT EXISTS `idx_tx_user_active_date` (`user_id`,`deleted_at`,`date`),
  ADD INDEX IF NOT EXISTS `idx_tx_type_method` (`type`,`payment_method`);

ALTER TABLE `users`
  ADD INDEX IF NOT EXISTS `idx_users_archive_name` (`is_archived`,`name`);

SET FOREIGN_KEY_CHECKS=1;