-- 2026-04-15
-- Seed de login dos medicos Alpha
-- Ordem de execucao:
-- 1) 2026-04-15-01-seed-secretaria-unidades-empresa-alpha.sql
-- 2) 2026-04-15-02-unidade-menu-e-acl-secretaria-empresa-alpha.sql
-- 3) 2026-04-15-03-taxonomias-e-agenda-seed-profissionais-alpha.sql
-- 4) ESTE ARQUIVO
--
-- Credenciais:
--   marina.ribeiro@tiix.com.br / Tiix@2026
--   bruno.prado@tiix.com.br   / Tiix@2026

SET @contract_profissional_id = 65;
SET @senha_plana = 'Tiix@2026';
SET @kit_slug_profissional = CONVERT('kit.profissional' USING utf8mb4) COLLATE utf8mb4_0900_ai_ci;
SET @group_slug_profissionais = CONVERT('profissionais' USING utf8mb4) COLLATE utf8mb4_0900_ai_ci;

SET @kit_profissional_id = (
    SELECT id
      FROM wpjy_perm_kits
     WHERE slug = @kit_slug_profissional
     LIMIT 1
);

SET @group_profissionais_id = (
    SELECT id
      FROM wpjy_grupos_permissoes
     WHERE slug = @group_slug_profissionais
     ORDER BY CASE WHEN id_contrato = 57 THEN 0 ELSE 1 END, id ASC
     LIMIT 1
);

INSERT INTO wpjy_perm_contract_kit (id_contrato, kit_id, ativo)
SELECT @contract_profissional_id, @kit_profissional_id, 1
WHERE @kit_profissional_id IS NOT NULL
  AND NOT EXISTS (
    SELECT 1
      FROM wpjy_perm_contract_kit
     WHERE id_contrato = @contract_profissional_id
       AND kit_id = @kit_profissional_id
  );

UPDATE wpjy_perm_contract_kit
   SET ativo = 1
 WHERE id_contrato = @contract_profissional_id
   AND kit_id = @kit_profissional_id;

INSERT INTO wpjy_perm_group_kit (group_id, kit_id)
SELECT @group_profissionais_id, @kit_profissional_id
WHERE @group_profissionais_id IS NOT NULL
  AND @kit_profissional_id IS NOT NULL
  AND NOT EXISTS (
    SELECT 1
      FROM wpjy_perm_group_kit
     WHERE group_id = @group_profissionais_id
       AND kit_id = @kit_profissional_id
  );

SET @marina_prof_id = (
    SELECT id
      FROM wpjy_parceiros_profissionais
     WHERE email = CONVERT('marina.ribeiro@tiix.com.br' USING utf8mb4) COLLATE utf8mb4_unicode_520_ci
     ORDER BY id ASC
     LIMIT 1
);
SET @bruno_prof_id = (
    SELECT id
      FROM wpjy_parceiros_profissionais
     WHERE email = CONVERT('bruno.prado@tiix.com.br' USING utf8mb4) COLLATE utf8mb4_unicode_520_ci
     ORDER BY id ASC
     LIMIT 1
);

SET @marina_user_id = (
    SELECT ID
      FROM wpjy_users
     WHERE user_login = CONVERT('marina.ribeiro@tiix.com.br' USING utf8mb4) COLLATE utf8mb4_unicode_520_ci
        OR user_email = CONVERT('marina.ribeiro@tiix.com.br' USING utf8mb4) COLLATE utf8mb4_unicode_520_ci
     ORDER BY ID ASC
     LIMIT 1
);
SET @bruno_user_id = (
    SELECT ID
      FROM wpjy_users
     WHERE user_login = CONVERT('bruno.prado@tiix.com.br' USING utf8mb4) COLLATE utf8mb4_unicode_520_ci
        OR user_email = CONVERT('bruno.prado@tiix.com.br' USING utf8mb4) COLLATE utf8mb4_unicode_520_ci
     ORDER BY ID ASC
     LIMIT 1
);

INSERT INTO wpjy_users (
    user_login,
    user_pass,
    user_nicename,
    user_email,
    user_registered,
    display_name
)
SELECT
    'marina.ribeiro@tiix.com.br',
    MD5(@senha_plana),
    'dra-marina-ribeiro',
    'marina.ribeiro@tiix.com.br',
    NOW(),
    'Dra. Marina Ribeiro'
WHERE COALESCE(@marina_user_id, 0) = 0;

SET @marina_user_id = (
    SELECT ID
      FROM wpjy_users
     WHERE user_login = CONVERT('marina.ribeiro@tiix.com.br' USING utf8mb4) COLLATE utf8mb4_unicode_520_ci
        OR user_email = CONVERT('marina.ribeiro@tiix.com.br' USING utf8mb4) COLLATE utf8mb4_unicode_520_ci
     ORDER BY ID ASC
     LIMIT 1
);

UPDATE wpjy_users
   SET user_pass = MD5(@senha_plana),
       user_nicename = 'dra-marina-ribeiro',
       user_email = 'marina.ribeiro@tiix.com.br',
       display_name = 'Dra. Marina Ribeiro'
 WHERE ID = @marina_user_id;

INSERT INTO wpjy_users (
    user_login,
    user_pass,
    user_nicename,
    user_email,
    user_registered,
    display_name
)
SELECT
    'bruno.prado@tiix.com.br',
    MD5(@senha_plana),
    'dr-bruno-prado',
    'bruno.prado@tiix.com.br',
    NOW(),
    'Dr. Bruno Prado'
WHERE COALESCE(@bruno_user_id, 0) = 0;

SET @bruno_user_id = (
    SELECT ID
      FROM wpjy_users
     WHERE user_login = CONVERT('bruno.prado@tiix.com.br' USING utf8mb4) COLLATE utf8mb4_unicode_520_ci
        OR user_email = CONVERT('bruno.prado@tiix.com.br' USING utf8mb4) COLLATE utf8mb4_unicode_520_ci
     ORDER BY ID ASC
     LIMIT 1
);

UPDATE wpjy_users
   SET user_pass = MD5(@senha_plana),
       user_nicename = 'dr-bruno-prado',
       user_email = 'bruno.prado@tiix.com.br',
       display_name = 'Dr. Bruno Prado'
 WHERE ID = @bruno_user_id;

DELETE FROM wpjy_usermeta
 WHERE user_id IN (@marina_user_id, @bruno_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
(@marina_user_id, 'nickname', 'Dra. Marina Ribeiro'),
(@marina_user_id, 'first_name', 'Marina'),
(@marina_user_id, 'last_name', 'Ribeiro'),
(@marina_user_id, 'wpjy_capabilities', 'a:1:{s:10:"subscriber";b:1;}'),
(@marina_user_id, 'wpjy_user_level', '0'),
(@marina_user_id, 'tiix_home_area', 'parceiro'),
(@marina_user_id, 'papel_default', 'profissional'),
(@bruno_user_id, 'nickname', 'Dr. Bruno Prado'),
(@bruno_user_id, 'first_name', 'Bruno'),
(@bruno_user_id, 'last_name', 'Prado'),
(@bruno_user_id, 'wpjy_capabilities', 'a:1:{s:10:"subscriber";b:1;}'),
(@bruno_user_id, 'wpjy_user_level', '0'),
(@bruno_user_id, 'tiix_home_area', 'parceiro'),
(@bruno_user_id, 'papel_default', 'profissional');

DELETE FROM wpjy_user_dados
 WHERE user_id IN (@marina_user_id, @bruno_user_id);

INSERT INTO wpjy_user_dados (
    user_id, nome, email, cpf, telefone, celular, contrato, ativo, tipo, profissao
) 
SELECT
    @marina_user_id,
    p.nome_completo,
    p.email,
    p.cpf,
    p.telefone,
    p.telefone,
    p.id_contrato,
    1,
    'profissional',
    'MEDICO'
FROM wpjy_parceiros_profissionais p
WHERE p.id = @marina_prof_id;

INSERT INTO wpjy_user_dados (
    user_id, nome, email, cpf, telefone, celular, contrato, ativo, tipo, profissao
)
SELECT
    @bruno_user_id,
    p.nome_completo,
    p.email,
    p.cpf,
    p.telefone,
    p.telefone,
    p.id_contrato,
    1,
    'profissional',
    'MEDICO'
FROM wpjy_parceiros_profissionais p
WHERE p.id = @bruno_prof_id;

DELETE FROM wpjy_usuario_contratos
 WHERE id_usuario IN (@marina_user_id, @bruno_user_id);

INSERT INTO wpjy_usuario_contratos (id_usuario, id_contrato)
VALUES
(@marina_user_id, @contract_profissional_id),
(@bruno_user_id, @contract_profissional_id);

DELETE FROM wpjy_usuario_grupo_permissao
 WHERE id_usuario IN (@marina_user_id, @bruno_user_id);

INSERT INTO wpjy_usuario_grupo_permissao (id_usuario, id_grupo)
SELECT @marina_user_id, @group_profissionais_id
WHERE @marina_user_id IS NOT NULL
  AND @group_profissionais_id IS NOT NULL;

INSERT INTO wpjy_usuario_grupo_permissao (id_usuario, id_grupo)
SELECT @bruno_user_id, @group_profissionais_id
WHERE @bruno_user_id IS NOT NULL
  AND @group_profissionais_id IS NOT NULL;

UPDATE wpjy_parceiros_profissionais
   SET user_id = CASE
       WHEN id = @marina_prof_id THEN @marina_user_id
       WHEN id = @bruno_prof_id THEN @bruno_user_id
       ELSE user_id
   END
 WHERE id IN (@marina_prof_id, @bruno_prof_id);

SELECT
    u.ID,
    u.user_login,
    u.user_email,
    home.meta_value AS tiix_home_area,
    papel.meta_value AS papel_default,
    uc.id_contrato AS contrato_ativo_seed
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'
LEFT JOIN wpjy_usuario_contratos uc
       ON uc.id_usuario = u.ID
WHERE u.ID IN (@marina_user_id, @bruno_user_id)
ORDER BY u.ID;

SELECT
    p.id AS profissional_id,
    p.nome_completo,
    p.email,
    p.user_id,
    gp.slug AS grupo_slug
FROM wpjy_parceiros_profissionais p
LEFT JOIN wpjy_usuario_grupo_permissao ugp
       ON ugp.id_usuario = p.user_id
LEFT JOIN wpjy_grupos_permissoes gp
       ON gp.id = ugp.id_grupo
WHERE p.id IN (@marina_prof_id, @bruno_prof_id)
ORDER BY p.id, gp.slug;