-- Migración para soporte de múltiples clanes por usuario
-- Fecha: 2025-11-21

-- 1. Agregar tabla para gestionar el clan activo de cada usuario
CREATE TABLE IF NOT EXISTS User_Active_Clan (
    user_id INT NOT NULL,
    active_clan_id INT NOT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id),
    FOREIGN KEY (user_id) REFERENCES Users(user_id) ON DELETE CASCADE,
    FOREIGN KEY (active_clan_id) REFERENCES Clans(clan_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. Inicializar clan activo para usuarios existentes
-- Asignar como clan activo el primer clan al que pertenecen
INSERT INTO User_Active_Clan (user_id, active_clan_id)
SELECT cm.user_id, MIN(cm.clan_id)
FROM Clan_Members cm
WHERE NOT EXISTS (
    SELECT 1 FROM User_Active_Clan uac WHERE uac.user_id = cm.user_id
)
GROUP BY cm.user_id
ON DUPLICATE KEY UPDATE active_clan_id = active_clan_id;

-- 3. Verificar estructura de Clan_Members (ya debe permitir múltiples clanes)
-- La tabla Clan_Members ya tiene PRIMARY KEY (clan_id, user_id), lo cual permite
-- que un usuario pertenezca a múltiples clanes

-- 4. Agregar índices para mejorar rendimiento
CREATE INDEX IF NOT EXISTS idx_clan_members_user ON Clan_Members(user_id);
CREATE INDEX IF NOT EXISTS idx_clan_members_clan ON Clan_Members(clan_id);

-- 5. Crear vista para obtener usuarios con sus clanes
CREATE OR REPLACE VIEW User_Clans_View AS
SELECT 
    u.user_id,
    u.username,
    u.full_name,
    u.email,
    GROUP_CONCAT(c.clan_id ORDER BY c.clan_name SEPARATOR ',') as clan_ids,
    GROUP_CONCAT(c.clan_name ORDER BY c.clan_name SEPARATOR ', ') as clan_names,
    uac.active_clan_id,
    ac.clan_name as active_clan_name
FROM Users u
LEFT JOIN Clan_Members cm ON u.user_id = cm.user_id
LEFT JOIN Clans c ON cm.clan_id = c.clan_id
LEFT JOIN User_Active_Clan uac ON u.user_id = uac.user_id
LEFT JOIN Clans ac ON uac.active_clan_id = ac.clan_id
GROUP BY u.user_id, u.username, u.full_name, u.email, uac.active_clan_id, ac.clan_name;

-- Consulta de verificación
SELECT 'Migración completada exitosamente' as status;
SELECT COUNT(*) as total_users_with_active_clan FROM User_Active_Clan;
SELECT 
    u.full_name, 
    COUNT(cm.clan_id) as total_clans,
    GROUP_CONCAT(c.clan_name SEPARATOR ', ') as clans
FROM Users u
LEFT JOIN Clan_Members cm ON u.user_id = cm.user_id
LEFT JOIN Clans c ON cm.clan_id = c.clan_id
GROUP BY u.user_id, u.full_name
HAVING COUNT(cm.clan_id) > 1;

