-- ============================================
-- TEST SUITE: Groups & Vouchers Enhancement
-- ============================================
-- Run these tests to verify all features work correctly
-- ============================================

-- TEST 1: Lock/Unlock prevents rename/delete
-- ============================================
-- Setup
INSERT IGNORE INTO account_groups(group_name, is_default, active, locked) 
VALUES('test_lock', 0, 1, 0);

-- Lock the group
UPDATE account_groups SET locked=1 WHERE group_name='test_lock';

-- Try to rename (should fail)
-- Expected: 🔒 المجموعة test_lock مقفلة ولا يمكن إعادة تسميتها
-- Command: /group_rename test_lock test_renamed

-- Try to delete (should fail)
-- Expected: 🔒 المجموعة test_lock مقفلة ولا يمكن حذفها
-- Command: /group_delete test_lock confirm

-- Unlock and try again (should succeed)
UPDATE account_groups SET locked=0 WHERE group_name='test_lock';
-- Command: /group_rename test_lock test_renamed
-- Expected: ✅ تم إعادة تسمية المجموعة

-- Cleanup
DELETE FROM account_groups WHERE group_name IN('test_lock', 'test_renamed');

-- ============================================
-- TEST 2: Batch add/del accounts
-- ============================================
-- Setup
INSERT IGNORE INTO account_groups(group_name, is_default, active) 
VALUES('test_batch', 0, 1);

INSERT IGNORE INTO steam_accounts(account_name, shared_secret, active) VALUES
('test_acc1', 'secret1==', 1),
('test_acc2', 'secret2==', 1),
('test_acc3', 'secret3==', 1);

-- Add multiple accounts at once
-- Command: /group_account_add test_batch test_acc1 test_acc2 test_acc3 nonexistent
-- Expected: Added: 3, Skipped: 0, NotFound: 1

-- Try adding again (should skip)
-- Command: /group_account_add test_batch test_acc1 test_acc2
-- Expected: Added: 0, Skipped: 2, NotFound: 0

-- Delete multiple accounts
-- Command: /group_account_del test_batch test_acc1 test_acc2 nonexistent
-- Expected: Removed: 2, NotFound: 1

-- Cleanup
DELETE FROM group_accounts WHERE group_id=(SELECT id FROM account_groups WHERE group_name='test_batch');
DELETE FROM account_groups WHERE group_name='test_batch';
DELETE FROM steam_accounts WHERE account_name LIKE 'test_acc%';

-- ============================================
-- TEST 3: group_usage shows correct stats
-- ============================================
-- Setup
INSERT IGNORE INTO account_groups(group_name, is_default, active) 
VALUES('test_usage', 0, 1);

-- Add accounts
INSERT IGNORE INTO group_accounts(group_id, account_id)
SELECT g.id, a.id 
FROM account_groups g, steam_accounts a
WHERE g.group_name='test_usage' 
AND a.account_name IN (SELECT account_name FROM steam_accounts LIMIT 3);

-- Add bot binding
INSERT IGNORE INTO bot_groups(bot_id, group_id)
SELECT b.id, g.id
FROM sub_bots b, account_groups g
WHERE g.group_name='test_usage'
LIMIT 1;

-- Command: /group_usage test_usage
-- Expected: Shows bots_count=1, accounts list

-- Cleanup
DELETE FROM bot_groups WHERE group_id=(SELECT id FROM account_groups WHERE group_name='test_usage');
DELETE FROM group_accounts WHERE group_id=(SELECT id FROM account_groups WHERE group_name='test_usage');
DELETE FROM account_groups WHERE group_name='test_usage';

-- ============================================
-- TEST 4: bot_groups shows union of accounts
-- ============================================
-- Setup: Create 2 groups with different accounts, bind both to same bot
INSERT IGNORE INTO account_groups(group_name, is_default, active) VALUES
('test_group_a', 0, 1),
('test_group_b', 0, 1);

-- Bind both groups to first bot
INSERT IGNORE INTO bot_groups(bot_id, group_id)
SELECT b.id, g.id
FROM sub_bots b
CROSS JOIN account_groups g
WHERE g.group_name IN('test_group_a', 'test_group_b')
LIMIT 2;

-- Command: /bot_groups <first_bot_label>
-- Expected: Shows both groups + UNION of all accounts from both groups

-- Cleanup
DELETE FROM bot_groups WHERE group_id IN(
    SELECT id FROM account_groups WHERE group_name IN('test_group_a', 'test_group_b')
);
DELETE FROM account_groups WHERE group_name IN('test_group_a', 'test_group_b');

-- ============================================
-- TEST 5: voucher_who_used tracks usage
-- ============================================
-- Setup
INSERT IGNORE INTO vouchers(code, voucher_type, amount, max_uses) 
VALUES('TEST123', 'users', 100, 5);

-- Simulate voucher usage by bot
INSERT IGNORE INTO voucher_usage(voucher_code, bot_id, used_at)
SELECT 'TEST123', id, NOW()
FROM sub_bots
LIMIT 1;

-- Command: /voucher_who_used TEST123
-- Expected: Shows voucher info + usage history with bot_label and used_at

-- Cleanup
DELETE FROM voucher_usage WHERE voucher_code='TEST123';
DELETE FROM vouchers WHERE code='TEST123';

-- ============================================
-- TEST 6: Migration is idempotent
-- ============================================
-- Run migration twice - should not error
SOURCE MIGRATION_GROUPS_VOUCHERS.sql;
SOURCE MIGRATION_GROUPS_VOUCHERS.sql;
-- Expected: No errors, columns already exist messages

-- ============================================
-- VERIFICATION QUERIES
-- ============================================
-- Check locked column exists
SELECT COLUMN_NAME, COLUMN_TYPE, COLUMN_DEFAULT, COLUMN_COMMENT
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = 'account_groups'
AND COLUMN_NAME = 'locked';

-- Check unique index on LOWER(group_name)
SELECT INDEX_NAME, COLUMN_NAME
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = 'account_groups'
AND INDEX_NAME = 'idx_unique_group_name_lower';

-- Check voucher_usage table
SHOW CREATE TABLE voucher_usage;

-- Count voucher usage records
SELECT 
    COUNT(*) as total_records,
    COUNT(DISTINCT voucher_code) as unique_codes,
    COUNT(DISTINCT bot_id) as unique_bots
FROM voucher_usage;

-- ============================================
SELECT '✅ All tests defined. Run commands in Telegram bot to verify.' AS status;
