-- ============================================
-- MIGRATION: Groups & Vouchers Enhancement
-- ============================================
-- Date: 2025-10-24
-- Purpose: Add locked feature + voucher tracking
-- Idempotent: Safe to run multiple times
-- ============================================

-- 1. Add 'locked' column to account_groups
-- ============================================
SET @column_exists = (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'account_groups'
    AND COLUMN_NAME = 'locked'
);

SET @add_locked = IF(@column_exists = 0,
    'ALTER TABLE account_groups ADD COLUMN locked TINYINT(1) NOT NULL DEFAULT 0 COMMENT ''قفل المجموعة يمنع rename/delete'' AFTER active',
    'SELECT ''✅ Column locked already exists'' AS msg');

PREPARE stmt FROM @add_locked;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 2. Add generated column for case-insensitive comparison
-- ============================================
SET @column_lower_exists = (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'account_groups'
    AND COLUMN_NAME = 'group_name_lower'
);

SET @add_lower_column = IF(@column_lower_exists = 0,
    'ALTER TABLE account_groups ADD COLUMN group_name_lower VARCHAR(64) AS (LOWER(group_name)) STORED',
    'SELECT ''✅ Column group_name_lower already exists'' AS msg');

PREPARE stmt FROM @add_lower_column;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 3. Add UNIQUE index on generated column
-- ============================================
SET @index_exists = (
    SELECT COUNT(*)
    FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'account_groups'
    AND INDEX_NAME = 'idx_unique_group_name_lower'
);

SET @add_unique_idx = IF(@index_exists = 0,
    'CREATE UNIQUE INDEX idx_unique_group_name_lower ON account_groups (group_name_lower)',
    'SELECT ''✅ Unique index on group_name_lower already exists'' AS msg');

PREPARE stmt FROM @add_unique_idx;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 4. Create voucher_usage table
-- ============================================
CREATE TABLE IF NOT EXISTS voucher_usage (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    voucher_code VARCHAR(64) NOT NULL,
    bot_id INT NULL,
    used_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_voucher_code (voucher_code),
    INDEX idx_bot_id (bot_id),
    INDEX idx_used_at (used_at),
    FOREIGN KEY (voucher_code) REFERENCES vouchers(code) ON DELETE CASCADE,
    FOREIGN KEY (bot_id) REFERENCES sub_bots(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 
COMMENT='تتبع استخدام القسائم من قبل البوتات';

-- 5. Migrate existing voucher_redemptions data (if exists)
-- ============================================
SET @table_redemptions = (
    SELECT COUNT(*)
    FROM information_schema.TABLES
    WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'voucher_redemptions'
);

SET @migrate_data = IF(@table_redemptions > 0,
    'INSERT IGNORE INTO voucher_usage (voucher_code, bot_id, used_at)
     SELECT vr.voucher_code, sb.id, vr.redeemed_at
     FROM voucher_redemptions vr
     LEFT JOIN sub_bots sb ON sb.bot_token = vr.bot_token
     WHERE NOT EXISTS (
         SELECT 1 FROM voucher_usage vu 
         WHERE vu.voucher_code = vr.voucher_code 
         AND vu.bot_id = sb.id
     )',
    'SELECT ''ℹ️ No voucher_redemptions table to migrate'' AS msg');

PREPARE stmt FROM @migrate_data;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ============================================
-- Verification Queries
-- ============================================
SELECT 
    'account_groups' AS table_name,
    COUNT(*) AS total_groups,
    SUM(locked) AS locked_groups,
    SUM(is_default) AS default_groups
FROM account_groups;

SELECT 
    'voucher_usage' AS table_name,
    COUNT(*) AS total_usage_records,
    COUNT(DISTINCT voucher_code) AS unique_vouchers,
    COUNT(DISTINCT bot_id) AS unique_bots
FROM voucher_usage;

SELECT '✅ Migration completed successfully!' AS status;
