-- Seed de usuario gestor da Corretora Geral
-- Objetivo:
-- 1) criar/atualizar um usuario WordPress
-- 2) vincular o usuario somente ao contrato da CORRETORA (63)
-- 3) aplicar grupo/area "corretora"
-- 4) permitir login direto na area /corretora
--
-- Login: gestor.corretora.geral@tiix.com.br
-- Senha inicial: Tiix@2026
--
-- Observacao:
-- este seed deixa o usuario apenas no contrato 63 para evitar
-- cair em /adm/tenant-select no primeiro login.
-- Se voce estiver executando no mesmo cliente SQL apos uma falha anterior,
-- rode antes:
-- ROLLBACK;

SET @contract_corretora_id = 63;
SET @email = CONVERT('gestor.corretora.geral@tiix.com.br' USING utf8mb4) COLLATE utf8mb4_unicode_520_ci;
SET @login = CONVERT('gestor.corretora.geral@tiix.com.br' USING utf8mb4) COLLATE utf8mb4_unicode_520_ci;
SET @senha_plana = 'Tiix@2026';
SET @display_name = CONVERT('Gestor Corretora Geral' USING utf8mb4) COLLATE utf8mb4_unicode_520_ci;
SET @first_name = CONVERT('Gestor' USING utf8mb4) COLLATE utf8mb4_unicode_520_ci;
SET @last_name = CONVERT('Corretora Geral' USING utf8mb4) COLLATE utf8mb4_unicode_520_ci;
SET @user_nicename = CONVERT('gestor-corretora-geral' USING utf8mb4) COLLATE utf8mb4_unicode_520_ci;
SET @group_slug_a = CONVERT('corretora' USING utf8mb4) COLLATE utf8mb4_0900_ai_ci;
SET @group_slug_b = CONVERT('corretor' USING utf8mb4) COLLATE utf8mb4_0900_ai_ci;

SET @user_id = (
    SELECT ID
      FROM wpjy_users
     WHERE user_login = @login
        OR user_email = @email
     ORDER BY ID ASC
     LIMIT 1
);

INSERT INTO wpjy_users (
    user_login,
    user_pass,
    user_nicename,
    user_email,
    user_registered,
    display_name
)
SELECT
    @login,
    MD5(@senha_plana),
    @user_nicename,
    @email,
    NOW(),
    @display_name
WHERE COALESCE(@user_id, 0) = 0;

SET @user_id = (
    SELECT ID
      FROM wpjy_users
     WHERE user_login = @login
        OR user_email = @email
     ORDER BY ID ASC
     LIMIT 1
);

UPDATE wpjy_users
   SET user_pass = MD5(@senha_plana),
       user_nicename = @user_nicename,
       user_email = @email,
       display_name = @display_name
 WHERE ID = @user_id;

DELETE FROM wpjy_usermeta
 WHERE user_id = @user_id
   AND meta_key IN (
       'nickname',
       'first_name',
       'last_name',
       'wpjy_capabilities',
       'wpjy_user_level',
       'tiix_home_area',
       'papel_default'
   );

INSERT INTO wpjy_usermeta (user_id, meta_key, meta_value) VALUES
(@user_id, 'nickname', @display_name),
(@user_id, 'first_name', @first_name),
(@user_id, 'last_name', @last_name),
(@user_id, 'wpjy_capabilities', 'a:1:{s:10:"subscriber";b:1;}'),
(@user_id, 'wpjy_user_level', '0'),
(@user_id, 'tiix_home_area', 'corretora'),
(@user_id, 'papel_default', 'corretora');

DELETE FROM wpjy_user_dados
 WHERE user_id = @user_id;

INSERT INTO wpjy_user_dados (
    user_id,
    nome,
    email,
    contrato
) VALUES (
    @user_id,
    @display_name,
    @email,
    @contract_corretora_id
);

DELETE FROM wpjy_usuario_contratos
 WHERE id_usuario = @user_id;

INSERT INTO wpjy_usuario_contratos (
    id_usuario,
    id_contrato
) VALUES (
    @user_id,
    @contract_corretora_id
);

SET @group_corretora_id = (
    SELECT id
      FROM wpjy_grupos_permissoes
     WHERE (slug = @group_slug_a OR slug = @group_slug_b)
       AND (id_contrato = @contract_corretora_id OR id_contrato = 0 OR id_contrato IS NULL)
     ORDER BY CASE WHEN id_contrato = @contract_corretora_id THEN 0 ELSE 1 END, id ASC
     LIMIT 1
);

DELETE FROM wpjy_usuario_grupo_permissao
 WHERE id_usuario = @user_id;

INSERT INTO wpjy_usuario_grupo_permissao (
    id_usuario,
    id_grupo
)
SELECT @user_id, @group_corretora_id
 WHERE COALESCE(@group_corretora_id, 0) > 0;

SELECT
    u.ID,
    u.user_login,
    u.user_email,
    home.meta_value AS tiix_home_area,
    papel.meta_value AS papel_default
FROM wpjy_users u
LEFT JOIN wpjy_usermeta home
       ON home.user_id = u.ID
      AND home.meta_key = 'tiix_home_area'
LEFT JOIN wpjy_usermeta papel
       ON papel.user_id = u.ID
      AND papel.meta_key = 'papel_default'
WHERE u.ID = @user_id;

SELECT
    uc.id_usuario,
    uc.id_contrato,
    c.tipo,
    c.nome_fantasia
FROM wpjy_usuario_contratos uc
JOIN wpjy_contratos c
  ON c.id = uc.id_contrato
WHERE uc.id_usuario = @user_id;
