-- 2026-04-16
-- Seed operacional de pacientes e agendamentos para Empresa Alpha / Unidade Centro
-- Objetivo:
-- 1) criar pacientes seed
-- 2) vincular esses pacientes ao contrato da Unidade Centro (#65)
-- 3) criar agendamentos visiveis para o escopo da secretaria/recepcao da unidade
-- 4) alimentar estados diferentes da agenda para validar lista, KPIs e filtros
--
-- Ordem sugerida de execucao:
-- 1) 2026-04-14-seed-gestor-empresa-alpha.sql
-- 2) 2026-04-15-01-seed-secretaria-unidades-empresa-alpha.sql
-- 3) 2026-04-15-02-unidade-menu-e-acl-secretaria-empresa-alpha.sql
-- 4) 2026-04-15-03-taxonomias-e-agenda-seed-profissionais-alpha.sql
-- 5) 2026-04-15-04-seed-login-dos-medicos-alpha.sql
-- 6) ESTE ARQUIVO
--
-- Resultado esperado:
-- - pacientes seed criados em wpjy_users/wpjy_user_dados
-- - agendamentos no dia 2026-04-16 para a Unidade Centro
-- - visiveis ao acessar /unidade/agendamentos com a secretaria Alpha

SET @empresa_contract_id = 64;
SET @unit_contract_id = 65;
SET @broker_contract_id = 63;
SET @root_contract_id = 57;
SET @agenda_date = '2026-04-16';
SET @senha_plana = 'Tiix@2026';

SET @secretaria_user_id = (
    SELECT ID
      FROM wpjy_users
     WHERE user_email = CONVERT('secretaria.unidades.alpha@tiix.com.br' USING utf8mb4) COLLATE utf8mb4_unicode_520_ci
     ORDER BY ID ASC
     LIMIT 1
);

SET @recepcao_user_id = (
    SELECT ID
      FROM wpjy_users
     WHERE user_email = CONVERT('recepcao.centro.alpha@tiix.com.br' USING utf8mb4) COLLATE utf8mb4_unicode_520_ci
     ORDER BY ID ASC
     LIMIT 1
);

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 @capabilities_subscriber = 'a:1:{s:10:"subscriber";b:1;}';

-- ---------------------------------------------------------------------------
-- Paciente 01
-- ---------------------------------------------------------------------------
SET @p01_email = CONVERT('paciente.alpha.01@tiix.com.br' USING utf8mb4) COLLATE utf8mb4_unicode_520_ci;
SET @p01_login = @p01_email;
SET @p01_nome = CONVERT('Carla Mendes' USING utf8mb4) COLLATE utf8mb4_unicode_520_ci;
SET @p01_nicename = CONVERT('carla-mendes-seed-alpha' USING utf8mb4) COLLATE utf8mb4_unicode_520_ci;
SET @p01_user_id = (
    SELECT ID FROM wpjy_users
     WHERE user_login = @p01_login OR user_email = @p01_email
     ORDER BY ID ASC LIMIT 1
);

INSERT INTO wpjy_users (user_login, user_pass, user_nicename, user_email, user_registered, display_name)
SELECT @p01_login, MD5(@senha_plana), @p01_nicename, @p01_email, NOW(), @p01_nome
WHERE COALESCE(@p01_user_id, 0) = 0;

SET @p01_user_id = (
    SELECT ID FROM wpjy_users
     WHERE user_login = @p01_login OR user_email = @p01_email
     ORDER BY ID ASC LIMIT 1
);

UPDATE wpjy_users
   SET user_pass = MD5(@senha_plana),
       user_nicename = @p01_nicename,
       user_email = @p01_email,
       display_name = @p01_nome
 WHERE ID = @p01_user_id;

DELETE FROM wpjy_usermeta
 WHERE user_id = @p01_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
(@p01_user_id, 'nickname', @p01_nome),
(@p01_user_id, 'first_name', 'Carla'),
(@p01_user_id, 'last_name', 'Mendes'),
(@p01_user_id, 'wpjy_capabilities', @capabilities_subscriber),
(@p01_user_id, 'wpjy_user_level', '0'),
(@p01_user_id, 'tiix_home_area', 'cliente'),
(@p01_user_id, 'papel_default', 'cliente');

DELETE FROM wpjy_user_dados WHERE user_id = @p01_user_id;
INSERT INTO wpjy_user_dados (user_id, nome, email, telefone, celular, contrato, ativo, tipo)
VALUES (@p01_user_id, @p01_nome, @p01_email, '21991000001', '21991000001', @unit_contract_id, 1, 'cliente');

DELETE FROM wpjy_usuario_contratos WHERE id_usuario = @p01_user_id AND id_contrato = @unit_contract_id;
INSERT INTO wpjy_usuario_contratos (id_usuario, id_contrato) VALUES (@p01_user_id, @unit_contract_id);

-- ---------------------------------------------------------------------------
-- Paciente 02
-- ---------------------------------------------------------------------------
SET @p02_email = CONVERT('paciente.alpha.02@tiix.com.br' USING utf8mb4) COLLATE utf8mb4_unicode_520_ci;
SET @p02_login = @p02_email;
SET @p02_nome = CONVERT('Felipe Rocha' USING utf8mb4) COLLATE utf8mb4_unicode_520_ci;
SET @p02_nicename = CONVERT('felipe-rocha-seed-alpha' USING utf8mb4) COLLATE utf8mb4_unicode_520_ci;
SET @p02_user_id = (
    SELECT ID FROM wpjy_users
     WHERE user_login = @p02_login OR user_email = @p02_email
     ORDER BY ID ASC LIMIT 1
);

INSERT INTO wpjy_users (user_login, user_pass, user_nicename, user_email, user_registered, display_name)
SELECT @p02_login, MD5(@senha_plana), @p02_nicename, @p02_email, NOW(), @p02_nome
WHERE COALESCE(@p02_user_id, 0) = 0;

SET @p02_user_id = (
    SELECT ID FROM wpjy_users
     WHERE user_login = @p02_login OR user_email = @p02_email
     ORDER BY ID ASC LIMIT 1
);

UPDATE wpjy_users
   SET user_pass = MD5(@senha_plana),
       user_nicename = @p02_nicename,
       user_email = @p02_email,
       display_name = @p02_nome
 WHERE ID = @p02_user_id;

DELETE FROM wpjy_usermeta
 WHERE user_id = @p02_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
(@p02_user_id, 'nickname', @p02_nome),
(@p02_user_id, 'first_name', 'Felipe'),
(@p02_user_id, 'last_name', 'Rocha'),
(@p02_user_id, 'wpjy_capabilities', @capabilities_subscriber),
(@p02_user_id, 'wpjy_user_level', '0'),
(@p02_user_id, 'tiix_home_area', 'cliente'),
(@p02_user_id, 'papel_default', 'cliente');

DELETE FROM wpjy_user_dados WHERE user_id = @p02_user_id;
INSERT INTO wpjy_user_dados (user_id, nome, email, telefone, celular, contrato, ativo, tipo)
VALUES (@p02_user_id, @p02_nome, @p02_email, '21991000002', '21991000002', @unit_contract_id, 1, 'cliente');

DELETE FROM wpjy_usuario_contratos WHERE id_usuario = @p02_user_id AND id_contrato = @unit_contract_id;
INSERT INTO wpjy_usuario_contratos (id_usuario, id_contrato) VALUES (@p02_user_id, @unit_contract_id);

-- ---------------------------------------------------------------------------
-- Paciente 03
-- ---------------------------------------------------------------------------
SET @p03_email = CONVERT('paciente.alpha.03@tiix.com.br' USING utf8mb4) COLLATE utf8mb4_unicode_520_ci;
SET @p03_login = @p03_email;
SET @p03_nome = CONVERT('Juliana Castro' USING utf8mb4) COLLATE utf8mb4_unicode_520_ci;
SET @p03_nicename = CONVERT('juliana-castro-seed-alpha' USING utf8mb4) COLLATE utf8mb4_unicode_520_ci;
SET @p03_user_id = (
    SELECT ID FROM wpjy_users
     WHERE user_login = @p03_login OR user_email = @p03_email
     ORDER BY ID ASC LIMIT 1
);

INSERT INTO wpjy_users (user_login, user_pass, user_nicename, user_email, user_registered, display_name)
SELECT @p03_login, MD5(@senha_plana), @p03_nicename, @p03_email, NOW(), @p03_nome
WHERE COALESCE(@p03_user_id, 0) = 0;

SET @p03_user_id = (
    SELECT ID FROM wpjy_users
     WHERE user_login = @p03_login OR user_email = @p03_email
     ORDER BY ID ASC LIMIT 1
);

UPDATE wpjy_users
   SET user_pass = MD5(@senha_plana),
       user_nicename = @p03_nicename,
       user_email = @p03_email,
       display_name = @p03_nome
 WHERE ID = @p03_user_id;

DELETE FROM wpjy_usermeta
 WHERE user_id = @p03_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
(@p03_user_id, 'nickname', @p03_nome),
(@p03_user_id, 'first_name', 'Juliana'),
(@p03_user_id, 'last_name', 'Castro'),
(@p03_user_id, 'wpjy_capabilities', @capabilities_subscriber),
(@p03_user_id, 'wpjy_user_level', '0'),
(@p03_user_id, 'tiix_home_area', 'cliente'),
(@p03_user_id, 'papel_default', 'cliente');

DELETE FROM wpjy_user_dados WHERE user_id = @p03_user_id;
INSERT INTO wpjy_user_dados (user_id, nome, email, telefone, celular, contrato, ativo, tipo)
VALUES (@p03_user_id, @p03_nome, @p03_email, '21991000003', '21991000003', @unit_contract_id, 1, 'cliente');

DELETE FROM wpjy_usuario_contratos WHERE id_usuario = @p03_user_id AND id_contrato = @unit_contract_id;
INSERT INTO wpjy_usuario_contratos (id_usuario, id_contrato) VALUES (@p03_user_id, @unit_contract_id);

-- ---------------------------------------------------------------------------
-- Paciente 04
-- ---------------------------------------------------------------------------
SET @p04_email = CONVERT('paciente.alpha.04@tiix.com.br' USING utf8mb4) COLLATE utf8mb4_unicode_520_ci;
SET @p04_login = @p04_email;
SET @p04_nome = CONVERT('Renato Souza' USING utf8mb4) COLLATE utf8mb4_unicode_520_ci;
SET @p04_nicename = CONVERT('renato-souza-seed-alpha' USING utf8mb4) COLLATE utf8mb4_unicode_520_ci;
SET @p04_user_id = (
    SELECT ID FROM wpjy_users
     WHERE user_login = @p04_login OR user_email = @p04_email
     ORDER BY ID ASC LIMIT 1
);

INSERT INTO wpjy_users (user_login, user_pass, user_nicename, user_email, user_registered, display_name)
SELECT @p04_login, MD5(@senha_plana), @p04_nicename, @p04_email, NOW(), @p04_nome
WHERE COALESCE(@p04_user_id, 0) = 0;

SET @p04_user_id = (
    SELECT ID FROM wpjy_users
     WHERE user_login = @p04_login OR user_email = @p04_email
     ORDER BY ID ASC LIMIT 1
);

UPDATE wpjy_users
   SET user_pass = MD5(@senha_plana),
       user_nicename = @p04_nicename,
       user_email = @p04_email,
       display_name = @p04_nome
 WHERE ID = @p04_user_id;

DELETE FROM wpjy_usermeta
 WHERE user_id = @p04_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
(@p04_user_id, 'nickname', @p04_nome),
(@p04_user_id, 'first_name', 'Renato'),
(@p04_user_id, 'last_name', 'Souza'),
(@p04_user_id, 'wpjy_capabilities', @capabilities_subscriber),
(@p04_user_id, 'wpjy_user_level', '0'),
(@p04_user_id, 'tiix_home_area', 'cliente'),
(@p04_user_id, 'papel_default', 'cliente');

DELETE FROM wpjy_user_dados WHERE user_id = @p04_user_id;
INSERT INTO wpjy_user_dados (user_id, nome, email, telefone, celular, contrato, ativo, tipo)
VALUES (@p04_user_id, @p04_nome, @p04_email, '21991000004', '21991000004', @unit_contract_id, 1, 'cliente');

DELETE FROM wpjy_usuario_contratos WHERE id_usuario = @p04_user_id AND id_contrato = @unit_contract_id;
INSERT INTO wpjy_usuario_contratos (id_usuario, id_contrato) VALUES (@p04_user_id, @unit_contract_id);

-- ---------------------------------------------------------------------------
-- Paciente 05
-- ---------------------------------------------------------------------------
SET @p05_email = CONVERT('paciente.alpha.05@tiix.com.br' USING utf8mb4) COLLATE utf8mb4_unicode_520_ci;
SET @p05_login = @p05_email;
SET @p05_nome = CONVERT('Vanessa Lima' USING utf8mb4) COLLATE utf8mb4_unicode_520_ci;
SET @p05_nicename = CONVERT('vanessa-lima-seed-alpha' USING utf8mb4) COLLATE utf8mb4_unicode_520_ci;
SET @p05_user_id = (
    SELECT ID FROM wpjy_users
     WHERE user_login = @p05_login OR user_email = @p05_email
     ORDER BY ID ASC LIMIT 1
);

INSERT INTO wpjy_users (user_login, user_pass, user_nicename, user_email, user_registered, display_name)
SELECT @p05_login, MD5(@senha_plana), @p05_nicename, @p05_email, NOW(), @p05_nome
WHERE COALESCE(@p05_user_id, 0) = 0;

SET @p05_user_id = (
    SELECT ID FROM wpjy_users
     WHERE user_login = @p05_login OR user_email = @p05_email
     ORDER BY ID ASC LIMIT 1
);

UPDATE wpjy_users
   SET user_pass = MD5(@senha_plana),
       user_nicename = @p05_nicename,
       user_email = @p05_email,
       display_name = @p05_nome
 WHERE ID = @p05_user_id;

DELETE FROM wpjy_usermeta
 WHERE user_id = @p05_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
(@p05_user_id, 'nickname', @p05_nome),
(@p05_user_id, 'first_name', 'Vanessa'),
(@p05_user_id, 'last_name', 'Lima'),
(@p05_user_id, 'wpjy_capabilities', @capabilities_subscriber),
(@p05_user_id, 'wpjy_user_level', '0'),
(@p05_user_id, 'tiix_home_area', 'cliente'),
(@p05_user_id, 'papel_default', 'cliente');

DELETE FROM wpjy_user_dados WHERE user_id = @p05_user_id;
INSERT INTO wpjy_user_dados (user_id, nome, email, telefone, celular, contrato, ativo, tipo)
VALUES (@p05_user_id, @p05_nome, @p05_email, '21991000005', '21991000005', @unit_contract_id, 1, 'cliente');

DELETE FROM wpjy_usuario_contratos WHERE id_usuario = @p05_user_id AND id_contrato = @unit_contract_id;
INSERT INTO wpjy_usuario_contratos (id_usuario, id_contrato) VALUES (@p05_user_id, @unit_contract_id);

-- ---------------------------------------------------------------------------
-- Paciente 06
-- ---------------------------------------------------------------------------
SET @p06_email = CONVERT('paciente.alpha.06@tiix.com.br' USING utf8mb4) COLLATE utf8mb4_unicode_520_ci;
SET @p06_login = @p06_email;
SET @p06_nome = CONVERT('Igor Teixeira' USING utf8mb4) COLLATE utf8mb4_unicode_520_ci;
SET @p06_nicename = CONVERT('igor-teixeira-seed-alpha' USING utf8mb4) COLLATE utf8mb4_unicode_520_ci;
SET @p06_user_id = (
    SELECT ID FROM wpjy_users
     WHERE user_login = @p06_login OR user_email = @p06_email
     ORDER BY ID ASC LIMIT 1
);

INSERT INTO wpjy_users (user_login, user_pass, user_nicename, user_email, user_registered, display_name)
SELECT @p06_login, MD5(@senha_plana), @p06_nicename, @p06_email, NOW(), @p06_nome
WHERE COALESCE(@p06_user_id, 0) = 0;

SET @p06_user_id = (
    SELECT ID FROM wpjy_users
     WHERE user_login = @p06_login OR user_email = @p06_email
     ORDER BY ID ASC LIMIT 1
);

UPDATE wpjy_users
   SET user_pass = MD5(@senha_plana),
       user_nicename = @p06_nicename,
       user_email = @p06_email,
       display_name = @p06_nome
 WHERE ID = @p06_user_id;

DELETE FROM wpjy_usermeta
 WHERE user_id = @p06_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
(@p06_user_id, 'nickname', @p06_nome),
(@p06_user_id, 'first_name', 'Igor'),
(@p06_user_id, 'last_name', 'Teixeira'),
(@p06_user_id, 'wpjy_capabilities', @capabilities_subscriber),
(@p06_user_id, 'wpjy_user_level', '0'),
(@p06_user_id, 'tiix_home_area', 'cliente'),
(@p06_user_id, 'papel_default', 'cliente');

DELETE FROM wpjy_user_dados WHERE user_id = @p06_user_id;
INSERT INTO wpjy_user_dados (user_id, nome, email, telefone, celular, contrato, ativo, tipo)
VALUES (@p06_user_id, @p06_nome, @p06_email, '21991000006', '21991000006', @unit_contract_id, 1, 'cliente');

DELETE FROM wpjy_usuario_contratos WHERE id_usuario = @p06_user_id AND id_contrato = @unit_contract_id;
INSERT INTO wpjy_usuario_contratos (id_usuario, id_contrato) VALUES (@p06_user_id, @unit_contract_id);

-- ---------------------------------------------------------------------------
-- Limpa agendamentos seed anteriores para reexecucao idempotente
-- ---------------------------------------------------------------------------
DELETE FROM wpjy_parceiros_profissionais_agendamentos
 WHERE descricao LIKE 'Seed Alpha Agenda %'
   AND user_id IN (@p01_user_id, @p02_user_id, @p03_user_id, @p04_user_id, @p05_user_id, @p06_user_id);

SET @appt_table = 'wpjy_parceiros_profissionais_agendamentos';

SET @has_col_empresa_id = (
    SELECT COUNT(1) FROM INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = @appt_table AND COLUMN_NAME = 'empresa_id'
);
SET @has_col_unidade_id = (
    SELECT COUNT(1) FROM INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = @appt_table AND COLUMN_NAME = 'unidade_id'
);
SET @has_col_empresa_contrato_id = (
    SELECT COUNT(1) FROM INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = @appt_table AND COLUMN_NAME = 'empresa_contrato_id'
);
SET @has_col_unidade_contrato_id = (
    SELECT COUNT(1) FROM INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = @appt_table AND COLUMN_NAME = 'unidade_contrato_id'
);
SET @has_col_corretora_contrato_id = (
    SELECT COUNT(1) FROM INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = @appt_table AND COLUMN_NAME = 'corretora_contrato_id'
);
SET @has_col_id_contrato_raiz = (
    SELECT COUNT(1) FROM INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = @appt_table AND COLUMN_NAME = 'id_contrato_raiz'
);
SET @has_col_modalidade = (
    SELECT COUNT(1) FROM INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = @appt_table AND COLUMN_NAME = 'modalidade'
);
SET @has_col_assigned_to = (
    SELECT COUNT(1) FROM INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = @appt_table AND COLUMN_NAME = 'assigned_to'
);
SET @has_col_consultor = (
    SELECT COUNT(1) FROM INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = @appt_table AND COLUMN_NAME = 'consultor_user_id'
);
SET @has_col_created_by = (
    SELECT COUNT(1) FROM INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = @appt_table AND COLUMN_NAME = 'created_by'
);
SET @has_col_updated_by = (
    SELECT COUNT(1) FROM INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = @appt_table AND COLUMN_NAME = 'updated_by'
);
SET @has_col_pre = (
    SELECT COUNT(1) FROM INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = @appt_table AND COLUMN_NAME = 'pre_agendamento'
);
SET @has_col_confirmado_em = (
    SELECT COUNT(1) FROM INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = @appt_table AND COLUMN_NAME = 'confirmado_em'
);
SET @has_col_concluido_em = (
    SELECT COUNT(1) FROM INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = @appt_table AND COLUMN_NAME = 'concluido_em'
);
SET @has_col_cancelado_em = (
    SELECT COUNT(1) FROM INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = @appt_table AND COLUMN_NAME = 'cancelado_em'
);
SET @has_col_motivo_cancelamento = (
    SELECT COUNT(1) FROM INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = @appt_table AND COLUMN_NAME = 'motivo_cancelamento'
);

SET @appt_cols_base = 'id_profissional,user_id,data,hora_inicio,hora_fim,status,descricao,created_at';
SET @appt_cols = @appt_cols_base;
SET @appt_cols = IF(@has_col_empresa_id > 0, CONCAT(@appt_cols, ',empresa_id'), @appt_cols);
SET @appt_cols = IF(@has_col_unidade_id > 0, CONCAT(@appt_cols, ',unidade_id'), @appt_cols);
SET @appt_cols = IF(@has_col_empresa_contrato_id > 0, CONCAT(@appt_cols, ',empresa_contrato_id'), @appt_cols);
SET @appt_cols = IF(@has_col_unidade_contrato_id > 0, CONCAT(@appt_cols, ',unidade_contrato_id'), @appt_cols);
SET @appt_cols = IF(@has_col_corretora_contrato_id > 0, CONCAT(@appt_cols, ',corretora_contrato_id'), @appt_cols);
SET @appt_cols = IF(@has_col_id_contrato_raiz > 0, CONCAT(@appt_cols, ',id_contrato_raiz'), @appt_cols);
SET @appt_cols = IF(@has_col_modalidade > 0, CONCAT(@appt_cols, ',modalidade'), @appt_cols);
SET @appt_cols = IF(@has_col_assigned_to > 0, CONCAT(@appt_cols, ',assigned_to'), @appt_cols);
SET @appt_cols = IF(@has_col_consultor > 0, CONCAT(@appt_cols, ',consultor_user_id'), @appt_cols);
SET @appt_cols = IF(@has_col_created_by > 0, CONCAT(@appt_cols, ',created_by'), @appt_cols);
SET @appt_cols = IF(@has_col_updated_by > 0, CONCAT(@appt_cols, ',updated_by'), @appt_cols);
SET @appt_cols = IF(@has_col_pre > 0, CONCAT(@appt_cols, ',pre_agendamento'), @appt_cols);
SET @appt_cols = IF(@has_col_confirmado_em > 0, CONCAT(@appt_cols, ',confirmado_em'), @appt_cols);
SET @appt_cols = IF(@has_col_concluido_em > 0, CONCAT(@appt_cols, ',concluido_em'), @appt_cols);
SET @appt_cols = IF(@has_col_cancelado_em > 0, CONCAT(@appt_cols, ',cancelado_em'), @appt_cols);
SET @appt_cols = IF(@has_col_motivo_cancelamento > 0, CONCAT(@appt_cols, ',motivo_cancelamento'), @appt_cols);

SET @common_tail_pre = CONCAT(
    IF(@has_col_empresa_id > 0, CONCAT(',', @empresa_contract_id), ''),
    IF(@has_col_unidade_id > 0, CONCAT(',', @unit_contract_id), ''),
    IF(@has_col_empresa_contrato_id > 0, CONCAT(',', @empresa_contract_id), ''),
    IF(@has_col_unidade_contrato_id > 0, CONCAT(',', @unit_contract_id), ''),
    IF(@has_col_corretora_contrato_id > 0, CONCAT(',', @broker_contract_id), ''),
    IF(@has_col_id_contrato_raiz > 0, CONCAT(',', @root_contract_id), ''),
    IF(@has_col_modalidade > 0, ',''online''', ''),
    IF(@has_col_assigned_to > 0, CONCAT(',', COALESCE(@secretaria_user_id, 0)), ''),
    IF(@has_col_consultor > 0, CONCAT(',', COALESCE(@secretaria_user_id, 0)), ''),
    IF(@has_col_created_by > 0, CONCAT(',', COALESCE(@secretaria_user_id, 0)), ''),
    IF(@has_col_updated_by > 0, CONCAT(',', COALESCE(@secretaria_user_id, 0)), ''),
    IF(@has_col_pre > 0, ',1', ''),
    IF(@has_col_confirmado_em > 0, ',NULL', ''),
    IF(@has_col_concluido_em > 0, ',NULL', ''),
    IF(@has_col_cancelado_em > 0, ',NULL', ''),
    IF(@has_col_motivo_cancelamento > 0, ',NULL', '')
);

SET @common_tail_conf = CONCAT(
    IF(@has_col_empresa_id > 0, CONCAT(',', @empresa_contract_id), ''),
    IF(@has_col_unidade_id > 0, CONCAT(',', @unit_contract_id), ''),
    IF(@has_col_empresa_contrato_id > 0, CONCAT(',', @empresa_contract_id), ''),
    IF(@has_col_unidade_contrato_id > 0, CONCAT(',', @unit_contract_id), ''),
    IF(@has_col_corretora_contrato_id > 0, CONCAT(',', @broker_contract_id), ''),
    IF(@has_col_id_contrato_raiz > 0, CONCAT(',', @root_contract_id), ''),
    IF(@has_col_modalidade > 0, ',''presencial''', ''),
    IF(@has_col_assigned_to > 0, CONCAT(',', COALESCE(@recepcao_user_id, 0)), ''),
    IF(@has_col_consultor > 0, CONCAT(',', COALESCE(@secretaria_user_id, 0)), ''),
    IF(@has_col_created_by > 0, CONCAT(',', COALESCE(@secretaria_user_id, 0)), ''),
    IF(@has_col_updated_by > 0, CONCAT(',', COALESCE(@recepcao_user_id, 0)), ''),
    IF(@has_col_pre > 0, ',0', ''),
    IF(@has_col_confirmado_em > 0, ',NOW()', ''),
    IF(@has_col_concluido_em > 0, ',NULL', ''),
    IF(@has_col_cancelado_em > 0, ',NULL', ''),
    IF(@has_col_motivo_cancelamento > 0, ',NULL', '')
);

SET @common_tail_em_atendimento = CONCAT(
    IF(@has_col_empresa_id > 0, CONCAT(',', @empresa_contract_id), ''),
    IF(@has_col_unidade_id > 0, CONCAT(',', @unit_contract_id), ''),
    IF(@has_col_empresa_contrato_id > 0, CONCAT(',', @empresa_contract_id), ''),
    IF(@has_col_unidade_contrato_id > 0, CONCAT(',', @unit_contract_id), ''),
    IF(@has_col_corretora_contrato_id > 0, CONCAT(',', @broker_contract_id), ''),
    IF(@has_col_id_contrato_raiz > 0, CONCAT(',', @root_contract_id), ''),
    IF(@has_col_modalidade > 0, ',''presencial''', ''),
    IF(@has_col_assigned_to > 0, CONCAT(',', COALESCE(@recepcao_user_id, 0)), ''),
    IF(@has_col_consultor > 0, CONCAT(',', COALESCE(@secretaria_user_id, 0)), ''),
    IF(@has_col_created_by > 0, CONCAT(',', COALESCE(@secretaria_user_id, 0)), ''),
    IF(@has_col_updated_by > 0, CONCAT(',', COALESCE(@recepcao_user_id, 0)), ''),
    IF(@has_col_pre > 0, ',0', ''),
    IF(@has_col_confirmado_em > 0, ',NOW()', ''),
    IF(@has_col_concluido_em > 0, ',NULL', ''),
    IF(@has_col_cancelado_em > 0, ',NULL', ''),
    IF(@has_col_motivo_cancelamento > 0, ',NULL', '')
);

SET @common_tail_conc = CONCAT(
    IF(@has_col_empresa_id > 0, CONCAT(',', @empresa_contract_id), ''),
    IF(@has_col_unidade_id > 0, CONCAT(',', @unit_contract_id), ''),
    IF(@has_col_empresa_contrato_id > 0, CONCAT(',', @empresa_contract_id), ''),
    IF(@has_col_unidade_contrato_id > 0, CONCAT(',', @unit_contract_id), ''),
    IF(@has_col_corretora_contrato_id > 0, CONCAT(',', @broker_contract_id), ''),
    IF(@has_col_id_contrato_raiz > 0, CONCAT(',', @root_contract_id), ''),
    IF(@has_col_modalidade > 0, ',''online''', ''),
    IF(@has_col_assigned_to > 0, CONCAT(',', COALESCE(@secretaria_user_id, 0)), ''),
    IF(@has_col_consultor > 0, CONCAT(',', COALESCE(@secretaria_user_id, 0)), ''),
    IF(@has_col_created_by > 0, CONCAT(',', COALESCE(@secretaria_user_id, 0)), ''),
    IF(@has_col_updated_by > 0, CONCAT(',', COALESCE(@secretaria_user_id, 0)), ''),
    IF(@has_col_pre > 0, ',0', ''),
    IF(@has_col_confirmado_em > 0, ',NOW()', ''),
    IF(@has_col_concluido_em > 0, ',NOW()', ''),
    IF(@has_col_cancelado_em > 0, ',NULL', ''),
    IF(@has_col_motivo_cancelamento > 0, ',NULL', '')
);

SET @common_tail_cancel = CONCAT(
    IF(@has_col_empresa_id > 0, CONCAT(',', @empresa_contract_id), ''),
    IF(@has_col_unidade_id > 0, CONCAT(',', @unit_contract_id), ''),
    IF(@has_col_empresa_contrato_id > 0, CONCAT(',', @empresa_contract_id), ''),
    IF(@has_col_unidade_contrato_id > 0, CONCAT(',', @unit_contract_id), ''),
    IF(@has_col_corretora_contrato_id > 0, CONCAT(',', @broker_contract_id), ''),
    IF(@has_col_id_contrato_raiz > 0, CONCAT(',', @root_contract_id), ''),
    IF(@has_col_modalidade > 0, ',''online''', ''),
    IF(@has_col_assigned_to > 0, CONCAT(',', COALESCE(@secretaria_user_id, 0)), ''),
    IF(@has_col_consultor > 0, CONCAT(',', COALESCE(@secretaria_user_id, 0)), ''),
    IF(@has_col_created_by > 0, CONCAT(',', COALESCE(@secretaria_user_id, 0)), ''),
    IF(@has_col_updated_by > 0, CONCAT(',', COALESCE(@secretaria_user_id, 0)), ''),
    IF(@has_col_pre > 0, ',0', ''),
    IF(@has_col_confirmado_em > 0, ',NOW()', ''),
    IF(@has_col_concluido_em > 0, ',NULL', ''),
    IF(@has_col_cancelado_em > 0, ',NOW()', ''),
    IF(@has_col_motivo_cancelamento > 0, ',''Paciente pediu remarcacao''', '')
);

SET @common_tail_falta = CONCAT(
    IF(@has_col_empresa_id > 0, CONCAT(',', @empresa_contract_id), ''),
    IF(@has_col_unidade_id > 0, CONCAT(',', @unit_contract_id), ''),
    IF(@has_col_empresa_contrato_id > 0, CONCAT(',', @empresa_contract_id), ''),
    IF(@has_col_unidade_contrato_id > 0, CONCAT(',', @unit_contract_id), ''),
    IF(@has_col_corretora_contrato_id > 0, CONCAT(',', @broker_contract_id), ''),
    IF(@has_col_id_contrato_raiz > 0, CONCAT(',', @root_contract_id), ''),
    IF(@has_col_modalidade > 0, ',''presencial''', ''),
    IF(@has_col_assigned_to > 0, CONCAT(',', COALESCE(@recepcao_user_id, 0)), ''),
    IF(@has_col_consultor > 0, CONCAT(',', COALESCE(@secretaria_user_id, 0)), ''),
    IF(@has_col_created_by > 0, CONCAT(',', COALESCE(@secretaria_user_id, 0)), ''),
    IF(@has_col_updated_by > 0, CONCAT(',', COALESCE(@recepcao_user_id, 0)), ''),
    IF(@has_col_pre > 0, ',0', ''),
    IF(@has_col_confirmado_em > 0, ',NOW()', ''),
    IF(@has_col_concluido_em > 0, ',NULL', ''),
    IF(@has_col_cancelado_em > 0, ',NULL', ''),
    IF(@has_col_motivo_cancelamento > 0, ',NULL', '')
);

SET @sql_appt_1 = CONCAT(
    'INSERT INTO wpjy_parceiros_profissionais_agendamentos (', @appt_cols, ') VALUES (',
    @marina_prof_id, ',', @p01_user_id, ',''', @agenda_date, ''',''09:00:00'',''09:30:00'',''pre_agendado'',''Seed Alpha Agenda 01 - Carla Mendes'',NOW()',
    @common_tail_pre,
    ')'
);
PREPARE stmt_appt_1 FROM @sql_appt_1; EXECUTE stmt_appt_1; DEALLOCATE PREPARE stmt_appt_1;

SET @sql_appt_2 = CONCAT(
    'INSERT INTO wpjy_parceiros_profissionais_agendamentos (', @appt_cols, ') VALUES (',
    @bruno_prof_id, ',', @p02_user_id, ',''', @agenda_date, ''',''10:00:00'',''10:30:00'',''confirmado'',''Seed Alpha Agenda 02 - Felipe Rocha'',NOW()',
    @common_tail_conf,
    ')'
);
PREPARE stmt_appt_2 FROM @sql_appt_2; EXECUTE stmt_appt_2; DEALLOCATE PREPARE stmt_appt_2;

SET @sql_appt_3 = CONCAT(
    'INSERT INTO wpjy_parceiros_profissionais_agendamentos (', @appt_cols, ') VALUES (',
    @marina_prof_id, ',', @p03_user_id, ',''', @agenda_date, ''',''11:00:00'',''11:30:00'',''em_atendimento'',''Seed Alpha Agenda 03 - Juliana Castro'',NOW()',
    @common_tail_em_atendimento,
    ')'
);
PREPARE stmt_appt_3 FROM @sql_appt_3; EXECUTE stmt_appt_3; DEALLOCATE PREPARE stmt_appt_3;

SET @sql_appt_4 = CONCAT(
    'INSERT INTO wpjy_parceiros_profissionais_agendamentos (', @appt_cols, ') VALUES (',
    @bruno_prof_id, ',', @p04_user_id, ',''', @agenda_date, ''',''13:30:00'',''14:00:00'',''concluido'',''Seed Alpha Agenda 04 - Renato Souza'',NOW()',
    @common_tail_conc,
    ')'
);
PREPARE stmt_appt_4 FROM @sql_appt_4; EXECUTE stmt_appt_4; DEALLOCATE PREPARE stmt_appt_4;

SET @sql_appt_5 = CONCAT(
    'INSERT INTO wpjy_parceiros_profissionais_agendamentos (', @appt_cols, ') VALUES (',
    @marina_prof_id, ',', @p05_user_id, ',''', @agenda_date, ''',''15:00:00'',''15:30:00'',''cancelado'',''Seed Alpha Agenda 05 - Vanessa Lima'',NOW()',
    @common_tail_cancel,
    ')'
);
PREPARE stmt_appt_5 FROM @sql_appt_5; EXECUTE stmt_appt_5; DEALLOCATE PREPARE stmt_appt_5;

SET @sql_appt_6 = CONCAT(
    'INSERT INTO wpjy_parceiros_profissionais_agendamentos (', @appt_cols, ') VALUES (',
    @bruno_prof_id, ',', @p06_user_id, ',''', @agenda_date, ''',''16:00:00'',''16:30:00'',''faltou'',''Seed Alpha Agenda 06 - Igor Teixeira'',NOW()',
    @common_tail_falta,
    ')'
);
PREPARE stmt_appt_6 FROM @sql_appt_6; EXECUTE stmt_appt_6; DEALLOCATE PREPARE stmt_appt_6;

SELECT
    u.ID,
    u.user_login,
    u.user_email,
    ud.nome,
    ud.contrato
FROM wpjy_users u
LEFT JOIN wpjy_user_dados ud
       ON ud.user_id = u.ID
WHERE u.ID IN (@p01_user_id, @p02_user_id, @p03_user_id, @p04_user_id, @p05_user_id, @p06_user_id)
ORDER BY u.ID ASC;

SELECT
    a.id,
    a.data,
    a.hora_inicio,
    a.hora_fim,
    a.status,
    a.id_profissional,
    a.user_id,
    a.descricao
FROM wpjy_parceiros_profissionais_agendamentos a
WHERE a.descricao LIKE 'Seed Alpha Agenda %'
ORDER BY a.data ASC, a.hora_inicio ASC, a.id ASC;
