-- Create a new filtered Users table with only users from specific groups
-- This script creates a new table with users who are in groups 4, 5, 7, 10, and 12
-- PLUS any users with "Admin" in their name

-- First, create the new table structure
CREATE TABLE `Users_Filtered` (
  `id` int NOT NULL,
  `memberId` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'a@a.com',
  `password` varchar(255) NOT NULL,
  `phone` varchar(255) DEFAULT '0',
  `handicap` varchar(255) DEFAULT '0',
  `handicapDec` decimal(10,3) DEFAULT '0.000',
  `federation` varchar(255) DEFAULT '0',
  `privileges` varchar(255) DEFAULT 'User',
  `gender` varchar(255) DEFAULT 'M',
  `usertoken` varchar(255) DEFAULT NULL,
  `balance` varchar(255) DEFAULT '0',
  `balanceDate` datetime DEFAULT NULL,
  `lastLogin` datetime DEFAULT NULL,
  `createdAt` datetime DEFAULT NULL,
  `updatedAt` datetime DEFAULT NULL,
  `allergy` varchar(200) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT ' ',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

-- Insert users who are in groups 4, 5, 7, 10, or 12 OR have "Admin" in their name
INSERT INTO `Users_Filtered` (
  `id`, `memberId`, `name`, `email`, `password`, `phone`, `handicap`, `handicapDec`, 
  `federation`, `privileges`, `gender`, `usertoken`, `balance`, `balanceDate`, 
  `lastLogin`, `createdAt`, `updatedAt`, `allergy`
)
SELECT DISTINCT 
  u.`id`, u.`memberId`, u.`name`, u.`email`, u.`password`, u.`phone`, u.`handicap`, u.`handicapDec`,
  u.`federation`, u.`privileges`, u.`gender`, u.`usertoken`, u.`balance`, u.`balanceDate`,
  u.`lastLogin`, u.`createdAt`, u.`updatedAt`, u.`allergy`
FROM `Users` u
LEFT JOIN `InviteGroupUsers` igu ON u.`id` = igu.`id`
WHERE (igu.`groupNo` IN (4, 5, 7, 10, 12) OR u.`name` LIKE '%Admin%')
ORDER BY u.`name`;

-- Show total count in filtered table
SELECT 'Total users in filtered table:' as description, COUNT(*) as count FROM `Users_Filtered`;

-- Show breakdown: users by group
SELECT 
  CONCAT('Group ', igu.groupNo, ' users:') as description,
  COUNT(DISTINCT u.id) as count
FROM `Users` u
INNER JOIN `InviteGroupUsers` igu ON u.`id` = igu.`id`
WHERE igu.`groupNo` IN (4, 5, 7, 10, 12)
GROUP BY igu.`groupNo`
ORDER BY igu.`groupNo`;

-- Show Admin users count
SELECT 
  'Users with "Admin" in name:' as description,
  COUNT(DISTINCT u.id) as count
FROM `Users` u
WHERE u.`name` LIKE '%Admin%';

-- Show users who are both in specified groups AND have Admin in name
SELECT 
  'Users in groups 4,5,7,10,12 AND have "Admin" in name:' as description,
  COUNT(DISTINCT u.id) as count
FROM `Users` u
INNER JOIN `InviteGroupUsers` igu ON u.`id` = igu.`id`
WHERE igu.`groupNo` IN (4, 5, 7, 10, 12) AND u.`name` LIKE '%Admin%';

-- Optional: Create a backup of the original Users table before replacing it
-- CREATE TABLE `Users_Backup_$(DATE_FORMAT(NOW(), '%Y%m%d_%H%i%s'))` AS SELECT * FROM `Users`;

-- Optional: Replace the original Users table with the filtered one
-- (Uncomment these lines if you want to replace the original table)
-- DROP TABLE `Users`;
-- RENAME TABLE `Users_Filtered` TO `Users`; 