-- Seed: Área do Cliente — companhias + usuários portal (1 por empresa)
-- Senha padrão: Cliente@123
-- Hash bcrypt: $2b$10$fbG8MIRaeQj7UicUyaN2o.97cdRqYJoqyCPZSigez9CM8IKITpgoa
-- Executar: mysql -u USER -p DATABASE < seeds/area_cliente_empresas.sql
--
-- Projetos:
--   maxifarma  → InfoERP Suporte + Convênio
--   demais     → InfoERP Suporte

SET @pwd = '$2b$10$fbG8MIRaeQj7UicUyaN2o.97cdRqYJoqyCPZSigez9CM8IKITpgoa';

SET @infoerp_suporte_id = (
  SELECT id FROM ptk_project
  WHERE LOWER(description) LIKE '%infoerp%suporte%'
  ORDER BY id ASC
  LIMIT 1
);

SET @convenio_id = (
  SELECT id FROM ptk_project
  WHERE LOWER(description) LIKE '%convênio%'
     OR LOWER(description) LIKE '%convenio%'
  ORDER BY id ASC
  LIMIT 1
);

INSERT INTO amb_form (nameform, description, default_rights, created_at)
SELECT 'notes', 'Chat / Notas', 'IAEC', NOW()
WHERE NOT EXISTS (SELECT 1 FROM amb_form WHERE nameform = 'notes' LIMIT 1);

INSERT INTO amb_profile (description, created_at)
SELECT 'Cliente Portal', NOW()
WHERE NOT EXISTS (SELECT 1 FROM amb_profile WHERE description = 'Cliente Portal' LIMIT 1);

SET @profile_id = (SELECT id FROM amb_profile WHERE description = 'Cliente Portal' LIMIT 1);

INSERT INTO amb_profile_autoriza (amb_profile_id, amb_form_id, direitos, created_at)
SELECT @profile_id, f.id, 'IAEC', NOW()
FROM amb_form f
WHERE f.nameform IN ('area-cliente', 'area_cliente', 'ptk_ticket', 'amb_user', 'notes')
  AND NOT EXISTS (
    SELECT 1 FROM amb_profile_autoriza pa
    WHERE pa.amb_profile_id = @profile_id AND pa.amb_form_id = f.id
  );

INSERT INTO amb_profile_autoriza (amb_profile_id, amb_form_id, direitos, created_at)
SELECT @profile_id, f.id, 'C', NOW()
FROM amb_form f
WHERE f.nameform = 'ptk_project'
  AND NOT EXISTS (
    SELECT 1 FROM amb_profile_autoriza pa
    WHERE pa.amb_profile_id = @profile_id AND pa.amb_form_id = f.id
  );

-- ---------------------------------------------------------------------------
-- Helper: cria/atualiza empresa + portal + usuário e vincula projetos
-- ---------------------------------------------------------------------------

-- ========== MAXIFARMA (InfoERP Suporte + Convênio) ==========
INSERT INTO amb_person (name, email, status, created_at)
SELECT 'Maxifarma', 'contato@maxifarma.com.br', 'A', NOW()
WHERE NOT EXISTS (SELECT 1 FROM amb_person WHERE name = 'Maxifarma' LIMIT 1);

SET @biz_id = (SELECT id FROM amb_person WHERE name = 'Maxifarma' ORDER BY id ASC LIMIT 1);

INSERT INTO amb_company_login (name, name_id, login_type, created_at)
SELECT 'Maxifarma', CAST(@biz_id AS CHAR), 1, NOW()
WHERE NOT EXISTS (SELECT 1 FROM amb_company_login WHERE name = 'Maxifarma' AND login_type = 1 LIMIT 1);

UPDATE amb_company_login
SET name_id = CAST(@biz_id AS CHAR), login_type = 1
WHERE name = 'Maxifarma' AND login_type = 1;

SET @cl_id = (SELECT id FROM amb_company_login WHERE name = 'Maxifarma' AND login_type = 1 ORDER BY id DESC LIMIT 1);

INSERT INTO amb_person (name, email, status, created_at)
SELECT 'Maxifarma Portal', 'contato@maxifarma.com.br', 'A', NOW()
WHERE NOT EXISTS (SELECT 1 FROM amb_person WHERE name = 'Maxifarma Portal' LIMIT 1);

SET @portal_id = (SELECT id FROM amb_person WHERE name = 'Maxifarma Portal' ORDER BY id DESC LIMIT 1);

INSERT INTO amb_user (login_user, login_email, password, active, person_id, company_login_id, created_at)
SELECT 'maxifarma', 'contato@maxifarma.com.br', @pwd, 'S', @portal_id, @cl_id, NOW()
WHERE NOT EXISTS (SELECT 1 FROM amb_user WHERE login_user = 'maxifarma' LIMIT 1);

UPDATE amb_user
SET password = @pwd, active = 'S', person_id = @portal_id, company_login_id = @cl_id,
    login_email = 'contato@maxifarma.com.br'
WHERE login_user = 'maxifarma';

SET @uid = (SELECT id FROM amb_user WHERE login_user = 'maxifarma' LIMIT 1);

INSERT INTO amb_user_profile (amb_user_id, amb_profile_id, created_at)
SELECT @uid, @profile_id, NOW()
WHERE @uid IS NOT NULL
  AND NOT EXISTS (
    SELECT 1 FROM amb_user_profile
    WHERE amb_user_id = @uid AND amb_profile_id = @profile_id
  );

INSERT IGNORE INTO ptk_project_company (project_id, company_id)
SELECT @infoerp_suporte_id, @biz_id FROM DUAL
WHERE @infoerp_suporte_id IS NOT NULL AND @biz_id IS NOT NULL;

INSERT IGNORE INTO ptk_project_company (project_id, company_id)
SELECT @convenio_id, @biz_id FROM DUAL
WHERE @convenio_id IS NOT NULL AND @biz_id IS NOT NULL;


-- ========== HIPERFARMA ==========
INSERT INTO amb_person (name, email, status, created_at)
SELECT 'HiperFarma', 'portal@hiperfarma.com.br', 'A', NOW()
WHERE NOT EXISTS (
  SELECT 1 FROM amb_person
  WHERE name IN ('HiperFarma', 'Hiperfarma', 'hiperfarma')
  LIMIT 1
);

SET @biz_id = (
  SELECT id FROM amb_person
  WHERE name IN ('HiperFarma', 'Hiperfarma', 'hiperfarma')
  ORDER BY id ASC LIMIT 1
);

INSERT INTO amb_company_login (name, name_id, login_type, created_at)
SELECT 'HiperFarma', CAST(@biz_id AS CHAR), 1, NOW()
WHERE NOT EXISTS (SELECT 1 FROM amb_company_login WHERE name = 'HiperFarma' AND login_type = 1 LIMIT 1);

UPDATE amb_company_login
SET name_id = CAST(@biz_id AS CHAR), login_type = 1
WHERE name = 'HiperFarma' AND login_type = 1;

SET @cl_id = (SELECT id FROM amb_company_login WHERE name = 'HiperFarma' AND login_type = 1 ORDER BY id DESC LIMIT 1);

INSERT INTO amb_person (name, email, status, created_at)
SELECT 'HiperFarma Portal', 'portal@hiperfarma.com.br', 'A', NOW()
WHERE NOT EXISTS (SELECT 1 FROM amb_person WHERE name = 'HiperFarma Portal' LIMIT 1);

SET @portal_id = (SELECT id FROM amb_person WHERE name = 'HiperFarma Portal' ORDER BY id DESC LIMIT 1);

INSERT INTO amb_user (login_user, login_email, password, active, person_id, company_login_id, created_at)
SELECT 'hiperfarma', 'portal@hiperfarma.com.br', @pwd, 'S', @portal_id, @cl_id, NOW()
WHERE NOT EXISTS (SELECT 1 FROM amb_user WHERE login_user = 'hiperfarma' LIMIT 1);

UPDATE amb_user
SET password = @pwd, active = 'S', person_id = @portal_id, company_login_id = @cl_id,
    login_email = 'portal@hiperfarma.com.br'
WHERE login_user = 'hiperfarma';

SET @uid = (SELECT id FROM amb_user WHERE login_user = 'hiperfarma' LIMIT 1);

INSERT INTO amb_user_profile (amb_user_id, amb_profile_id, created_at)
SELECT @uid, @profile_id, NOW()
WHERE @uid IS NOT NULL
  AND NOT EXISTS (
    SELECT 1 FROM amb_user_profile
    WHERE amb_user_id = @uid AND amb_profile_id = @profile_id
  );

INSERT IGNORE INTO ptk_project_company (project_id, company_id)
SELECT @infoerp_suporte_id, @biz_id FROM DUAL
WHERE @infoerp_suporte_id IS NOT NULL AND @biz_id IS NOT NULL;


-- ========== REQUEMAQ ==========
INSERT INTO amb_person (name, email, status, created_at)
SELECT 'Requemaq', 'portal@requemaq.com.br', 'A', NOW()
WHERE NOT EXISTS (SELECT 1 FROM amb_person WHERE name = 'Requemaq' LIMIT 1);

SET @biz_id = (SELECT id FROM amb_person WHERE name = 'Requemaq' ORDER BY id ASC LIMIT 1);

INSERT INTO amb_company_login (name, name_id, login_type, created_at)
SELECT 'Requemaq', CAST(@biz_id AS CHAR), 1, NOW()
WHERE NOT EXISTS (SELECT 1 FROM amb_company_login WHERE name = 'Requemaq' AND login_type = 1 LIMIT 1);

UPDATE amb_company_login
SET name_id = CAST(@biz_id AS CHAR), login_type = 1
WHERE name = 'Requemaq' AND login_type = 1;

SET @cl_id = (SELECT id FROM amb_company_login WHERE name = 'Requemaq' AND login_type = 1 ORDER BY id DESC LIMIT 1);

INSERT INTO amb_person (name, email, status, created_at)
SELECT 'Requemaq Portal', 'portal@requemaq.com.br', 'A', NOW()
WHERE NOT EXISTS (SELECT 1 FROM amb_person WHERE name = 'Requemaq Portal' LIMIT 1);

SET @portal_id = (SELECT id FROM amb_person WHERE name = 'Requemaq Portal' ORDER BY id DESC LIMIT 1);

INSERT INTO amb_user (login_user, login_email, password, active, person_id, company_login_id, created_at)
SELECT 'requemaq', 'portal@requemaq.com.br', @pwd, 'S', @portal_id, @cl_id, NOW()
WHERE NOT EXISTS (SELECT 1 FROM amb_user WHERE login_user = 'requemaq' LIMIT 1);

UPDATE amb_user
SET password = @pwd, active = 'S', person_id = @portal_id, company_login_id = @cl_id,
    login_email = 'portal@requemaq.com.br'
WHERE login_user = 'requemaq';

SET @uid = (SELECT id FROM amb_user WHERE login_user = 'requemaq' LIMIT 1);

INSERT INTO amb_user_profile (amb_user_id, amb_profile_id, created_at)
SELECT @uid, @profile_id, NOW()
WHERE @uid IS NOT NULL
  AND NOT EXISTS (
    SELECT 1 FROM amb_user_profile
    WHERE amb_user_id = @uid AND amb_profile_id = @profile_id
  );

INSERT IGNORE INTO ptk_project_company (project_id, company_id)
SELECT @infoerp_suporte_id, @biz_id FROM DUAL
WHERE @infoerp_suporte_id IS NOT NULL AND @biz_id IS NOT NULL;


-- ========== BETONEX ==========
INSERT INTO amb_person (name, email, status, created_at)
SELECT 'Betonex', 'portal@betonex.com.br', 'A', NOW()
WHERE NOT EXISTS (SELECT 1 FROM amb_person WHERE name = 'Betonex' LIMIT 1);

SET @biz_id = (SELECT id FROM amb_person WHERE name = 'Betonex' ORDER BY id ASC LIMIT 1);

INSERT INTO amb_company_login (name, name_id, login_type, created_at)
SELECT 'Betonex', CAST(@biz_id AS CHAR), 1, NOW()
WHERE NOT EXISTS (SELECT 1 FROM amb_company_login WHERE name = 'Betonex' AND login_type = 1 LIMIT 1);

UPDATE amb_company_login
SET name_id = CAST(@biz_id AS CHAR), login_type = 1
WHERE name = 'Betonex' AND login_type = 1;

SET @cl_id = (SELECT id FROM amb_company_login WHERE name = 'Betonex' AND login_type = 1 ORDER BY id DESC LIMIT 1);

INSERT INTO amb_person (name, email, status, created_at)
SELECT 'Betonex Portal', 'portal@betonex.com.br', 'A', NOW()
WHERE NOT EXISTS (SELECT 1 FROM amb_person WHERE name = 'Betonex Portal' LIMIT 1);

SET @portal_id = (SELECT id FROM amb_person WHERE name = 'Betonex Portal' ORDER BY id DESC LIMIT 1);

INSERT INTO amb_user (login_user, login_email, password, active, person_id, company_login_id, created_at)
SELECT 'betonex', 'portal@betonex.com.br', @pwd, 'S', @portal_id, @cl_id, NOW()
WHERE NOT EXISTS (SELECT 1 FROM amb_user WHERE login_user = 'betonex' LIMIT 1);

UPDATE amb_user
SET password = @pwd, active = 'S', person_id = @portal_id, company_login_id = @cl_id,
    login_email = 'portal@betonex.com.br'
WHERE login_user = 'betonex';

SET @uid = (SELECT id FROM amb_user WHERE login_user = 'betonex' LIMIT 1);

INSERT INTO amb_user_profile (amb_user_id, amb_profile_id, created_at)
SELECT @uid, @profile_id, NOW()
WHERE @uid IS NOT NULL
  AND NOT EXISTS (
    SELECT 1 FROM amb_user_profile
    WHERE amb_user_id = @uid AND amb_profile_id = @profile_id
  );

INSERT IGNORE INTO ptk_project_company (project_id, company_id)
SELECT @infoerp_suporte_id, @biz_id FROM DUAL
WHERE @infoerp_suporte_id IS NOT NULL AND @biz_id IS NOT NULL;


-- ========== LA ==========
INSERT INTO amb_person (name, email, status, created_at)
SELECT 'LA', 'portal@la.com.br', 'A', NOW()
WHERE NOT EXISTS (SELECT 1 FROM amb_person WHERE name IN ('LA', 'La') LIMIT 1);

SET @biz_id = (SELECT id FROM amb_person WHERE name IN ('LA', 'La') ORDER BY id ASC LIMIT 1);

INSERT INTO amb_company_login (name, name_id, login_type, created_at)
SELECT 'LA', CAST(@biz_id AS CHAR), 1, NOW()
WHERE NOT EXISTS (SELECT 1 FROM amb_company_login WHERE name = 'LA' AND login_type = 1 LIMIT 1);

UPDATE amb_company_login
SET name_id = CAST(@biz_id AS CHAR), login_type = 1
WHERE name = 'LA' AND login_type = 1;

SET @cl_id = (SELECT id FROM amb_company_login WHERE name = 'LA' AND login_type = 1 ORDER BY id DESC LIMIT 1);

INSERT INTO amb_person (name, email, status, created_at)
SELECT 'LA Portal', 'portal@la.com.br', 'A', NOW()
WHERE NOT EXISTS (SELECT 1 FROM amb_person WHERE name = 'LA Portal' LIMIT 1);

SET @portal_id = (SELECT id FROM amb_person WHERE name = 'LA Portal' ORDER BY id DESC LIMIT 1);

INSERT INTO amb_user (login_user, login_email, password, active, person_id, company_login_id, created_at)
SELECT 'la', 'portal@la.com.br', @pwd, 'S', @portal_id, @cl_id, NOW()
WHERE NOT EXISTS (SELECT 1 FROM amb_user WHERE login_user = 'la' LIMIT 1);

UPDATE amb_user
SET password = @pwd, active = 'S', person_id = @portal_id, company_login_id = @cl_id,
    login_email = 'portal@la.com.br'
WHERE login_user = 'la';

SET @uid = (SELECT id FROM amb_user WHERE login_user = 'la' LIMIT 1);

INSERT INTO amb_user_profile (amb_user_id, amb_profile_id, created_at)
SELECT @uid, @profile_id, NOW()
WHERE @uid IS NOT NULL
  AND NOT EXISTS (
    SELECT 1 FROM amb_user_profile
    WHERE amb_user_id = @uid AND amb_profile_id = @profile_id
  );

INSERT IGNORE INTO ptk_project_company (project_id, company_id)
SELECT @infoerp_suporte_id, @biz_id FROM DUAL
WHERE @infoerp_suporte_id IS NOT NULL AND @biz_id IS NOT NULL;


-- ========== TORK ==========
INSERT INTO amb_person (name, email, status, created_at)
SELECT 'Tork', 'portal@tork.com.br', 'A', NOW()
WHERE NOT EXISTS (
  SELECT 1 FROM amb_person
  WHERE name = 'Tork' OR name LIKE 'Tork Comercio%'
  LIMIT 1
);

SET @biz_id = (
  SELECT id FROM amb_person
  WHERE name = 'Tork' OR name LIKE 'Tork Comercio%'
  ORDER BY id ASC LIMIT 1
);

INSERT INTO amb_company_login (name, name_id, login_type, created_at)
SELECT 'Tork', CAST(@biz_id AS CHAR), 1, NOW()
WHERE NOT EXISTS (SELECT 1 FROM amb_company_login WHERE name = 'Tork' AND login_type = 1 LIMIT 1);

UPDATE amb_company_login
SET name_id = CAST(@biz_id AS CHAR), login_type = 1
WHERE name = 'Tork' AND login_type = 1;

SET @cl_id = (SELECT id FROM amb_company_login WHERE name = 'Tork' AND login_type = 1 ORDER BY id DESC LIMIT 1);

INSERT INTO amb_person (name, email, status, created_at)
SELECT 'Tork Portal', 'portal@tork.com.br', 'A', NOW()
WHERE NOT EXISTS (SELECT 1 FROM amb_person WHERE name = 'Tork Portal' LIMIT 1);

SET @portal_id = (SELECT id FROM amb_person WHERE name = 'Tork Portal' ORDER BY id DESC LIMIT 1);

INSERT INTO amb_user (login_user, login_email, password, active, person_id, company_login_id, created_at)
SELECT 'tork', 'portal@tork.com.br', @pwd, 'S', @portal_id, @cl_id, NOW()
WHERE NOT EXISTS (SELECT 1 FROM amb_user WHERE login_user = 'tork' LIMIT 1);

UPDATE amb_user
SET password = @pwd, active = 'S', person_id = @portal_id, company_login_id = @cl_id,
    login_email = 'portal@tork.com.br'
WHERE login_user = 'tork';

SET @uid = (SELECT id FROM amb_user WHERE login_user = 'tork' LIMIT 1);

INSERT INTO amb_user_profile (amb_user_id, amb_profile_id, created_at)
SELECT @uid, @profile_id, NOW()
WHERE @uid IS NOT NULL
  AND NOT EXISTS (
    SELECT 1 FROM amb_user_profile
    WHERE amb_user_id = @uid AND amb_profile_id = @profile_id
  );

INSERT IGNORE INTO ptk_project_company (project_id, company_id)
SELECT @infoerp_suporte_id, @biz_id FROM DUAL
WHERE @infoerp_suporte_id IS NOT NULL AND @biz_id IS NOT NULL;


-- ========== TELHAGRES (Telhas Paraná) ==========
INSERT INTO amb_person (name, email, status, created_at)
SELECT 'Telhas Parana', 'telhas@telhas.com', 'A', NOW()
WHERE NOT EXISTS (SELECT 1 FROM amb_person WHERE name = 'Telhas Parana' LIMIT 1);

SET @biz_id = (SELECT id FROM amb_person WHERE name = 'Telhas Parana' ORDER BY id ASC LIMIT 1);

INSERT INTO amb_company_login (name, name_id, login_type, created_at)
SELECT 'Telhas Parana', CAST(@biz_id AS CHAR), 1, NOW()
WHERE NOT EXISTS (SELECT 1 FROM amb_company_login WHERE name = 'Telhas Parana' AND login_type = 1 LIMIT 1);

UPDATE amb_company_login
SET name_id = CAST(@biz_id AS CHAR), login_type = 1
WHERE name = 'Telhas Parana' AND login_type = 1;

SET @cl_id = (SELECT id FROM amb_company_login WHERE name = 'Telhas Parana' AND login_type = 1 ORDER BY id DESC LIMIT 1);

INSERT INTO amb_person (name, email, status, created_at)
SELECT 'Telhas Parana Portal', 'portal@telhasparana.com.br', 'A', NOW()
WHERE NOT EXISTS (SELECT 1 FROM amb_person WHERE name = 'Telhas Parana Portal' LIMIT 1);

SET @portal_id = (SELECT id FROM amb_person WHERE name = 'Telhas Parana Portal' ORDER BY id DESC LIMIT 1);

INSERT INTO amb_user (login_user, login_email, password, active, person_id, company_login_id, created_at)
SELECT 'telhagres', 'portal@telhasparana.com.br', @pwd, 'S', @portal_id, @cl_id, NOW()
WHERE NOT EXISTS (SELECT 1 FROM amb_user WHERE login_user = 'telhagres' LIMIT 1);

UPDATE amb_user
SET active = 'S', person_id = @portal_id, company_login_id = @cl_id,
    login_email = 'portal@telhasparana.com.br'
WHERE login_user = 'telhagres';

SET @uid = (SELECT id FROM amb_user WHERE login_user = 'telhagres' LIMIT 1);

INSERT INTO amb_user_profile (amb_user_id, amb_profile_id, created_at)
SELECT @uid, @profile_id, NOW()
WHERE @uid IS NOT NULL
  AND NOT EXISTS (
    SELECT 1 FROM amb_user_profile
    WHERE amb_user_id = @uid AND amb_profile_id = @profile_id
  );

INSERT IGNORE INTO ptk_project_company (project_id, company_id)
SELECT @infoerp_suporte_id, @biz_id FROM DUAL
WHERE @infoerp_suporte_id IS NOT NULL AND @biz_id IS NOT NULL;


-- ========== TRATORRAFFA ==========
INSERT INTO amb_person (name, email, status, created_at)
SELECT 'Tratorraffa', 'portal@tratorraffa.com.br', 'A', NOW()
WHERE NOT EXISTS (SELECT 1 FROM amb_person WHERE name = 'Tratorraffa' LIMIT 1);

SET @biz_id = (SELECT id FROM amb_person WHERE name = 'Tratorraffa' ORDER BY id ASC LIMIT 1);

INSERT INTO amb_company_login (name, name_id, login_type, created_at)
SELECT 'Tratorraffa', CAST(@biz_id AS CHAR), 1, NOW()
WHERE NOT EXISTS (SELECT 1 FROM amb_company_login WHERE name = 'Tratorraffa' AND login_type = 1 LIMIT 1);

UPDATE amb_company_login
SET name_id = CAST(@biz_id AS CHAR), login_type = 1
WHERE name = 'Tratorraffa' AND login_type = 1;

SET @cl_id = (SELECT id FROM amb_company_login WHERE name = 'Tratorraffa' AND login_type = 1 ORDER BY id DESC LIMIT 1);

INSERT INTO amb_person (name, email, status, created_at)
SELECT 'Tratorraffa Portal', 'portal@tratorraffa.com.br', 'A', NOW()
WHERE NOT EXISTS (SELECT 1 FROM amb_person WHERE name = 'Tratorraffa Portal' LIMIT 1);

SET @portal_id = (SELECT id FROM amb_person WHERE name = 'Tratorraffa Portal' ORDER BY id DESC LIMIT 1);

INSERT INTO amb_user (login_user, login_email, password, active, person_id, company_login_id, created_at)
SELECT 'tratorraffa', 'portal@tratorraffa.com.br', @pwd, 'S', @portal_id, @cl_id, NOW()
WHERE NOT EXISTS (SELECT 1 FROM amb_user WHERE login_user = 'tratorraffa' LIMIT 1);

UPDATE amb_user
SET password = @pwd, active = 'S', person_id = @portal_id, company_login_id = @cl_id,
    login_email = 'portal@tratorraffa.com.br'
WHERE login_user = 'tratorraffa';

SET @uid = (SELECT id FROM amb_user WHERE login_user = 'tratorraffa' LIMIT 1);

INSERT INTO amb_user_profile (amb_user_id, amb_profile_id, created_at)
SELECT @uid, @profile_id, NOW()
WHERE @uid IS NOT NULL
  AND NOT EXISTS (
    SELECT 1 FROM amb_user_profile
    WHERE amb_user_id = @uid AND amb_profile_id = @profile_id
  );

INSERT IGNORE INTO ptk_project_company (project_id, company_id)
SELECT @infoerp_suporte_id, @biz_id FROM DUAL
WHERE @infoerp_suporte_id IS NOT NULL AND @biz_id IS NOT NULL;


-- ========== BIANCO ==========
INSERT INTO amb_person (name, email, status, created_at)
SELECT 'Bianco', 'portal@bianco.com.br', 'A', NOW()
WHERE NOT EXISTS (SELECT 1 FROM amb_person WHERE name = 'Bianco' LIMIT 1);

SET @biz_id = (SELECT id FROM amb_person WHERE name = 'Bianco' ORDER BY id ASC LIMIT 1);

INSERT INTO amb_company_login (name, name_id, login_type, created_at)
SELECT 'Bianco', CAST(@biz_id AS CHAR), 1, NOW()
WHERE NOT EXISTS (SELECT 1 FROM amb_company_login WHERE name = 'Bianco' AND login_type = 1 LIMIT 1);

UPDATE amb_company_login
SET name_id = CAST(@biz_id AS CHAR), login_type = 1
WHERE name = 'Bianco' AND login_type = 1;

SET @cl_id = (SELECT id FROM amb_company_login WHERE name = 'Bianco' AND login_type = 1 ORDER BY id DESC LIMIT 1);

INSERT INTO amb_person (name, email, status, created_at)
SELECT 'Bianco Portal', 'portal@bianco.com.br', 'A', NOW()
WHERE NOT EXISTS (SELECT 1 FROM amb_person WHERE name = 'Bianco Portal' LIMIT 1);

SET @portal_id = (SELECT id FROM amb_person WHERE name = 'Bianco Portal' ORDER BY id DESC LIMIT 1);

INSERT INTO amb_user (login_user, login_email, password, active, person_id, company_login_id, created_at)
SELECT 'bianco', 'portal@bianco.com.br', @pwd, 'S', @portal_id, @cl_id, NOW()
WHERE NOT EXISTS (SELECT 1 FROM amb_user WHERE login_user = 'bianco' LIMIT 1);

UPDATE amb_user
SET password = @pwd, active = 'S', person_id = @portal_id, company_login_id = @cl_id,
    login_email = 'portal@bianco.com.br'
WHERE login_user = 'bianco';

SET @uid = (SELECT id FROM amb_user WHERE login_user = 'bianco' LIMIT 1);

INSERT INTO amb_user_profile (amb_user_id, amb_profile_id, created_at)
SELECT @uid, @profile_id, NOW()
WHERE @uid IS NOT NULL
  AND NOT EXISTS (
    SELECT 1 FROM amb_user_profile
    WHERE amb_user_id = @uid AND amb_profile_id = @profile_id
  );

INSERT IGNORE INTO ptk_project_company (project_id, company_id)
SELECT @infoerp_suporte_id, @biz_id FROM DUAL
WHERE @infoerp_suporte_id IS NOT NULL AND @biz_id IS NOT NULL;


-- ========== IVEMAR ==========
INSERT INTO amb_person (name, email, status, created_at)
SELECT 'Ivemar', 'portal@ivemar.com.br', 'A', NOW()
WHERE NOT EXISTS (SELECT 1 FROM amb_person WHERE name = 'Ivemar' LIMIT 1);

SET @biz_id = (SELECT id FROM amb_person WHERE name = 'Ivemar' ORDER BY id ASC LIMIT 1);

INSERT INTO amb_company_login (name, name_id, login_type, created_at)
SELECT 'Ivemar', CAST(@biz_id AS CHAR), 1, NOW()
WHERE NOT EXISTS (SELECT 1 FROM amb_company_login WHERE name = 'Ivemar' AND login_type = 1 LIMIT 1);

UPDATE amb_company_login
SET name_id = CAST(@biz_id AS CHAR), login_type = 1
WHERE name = 'Ivemar' AND login_type = 1;

SET @cl_id = (SELECT id FROM amb_company_login WHERE name = 'Ivemar' AND login_type = 1 ORDER BY id DESC LIMIT 1);

INSERT INTO amb_person (name, email, status, created_at)
SELECT 'Ivemar Portal', 'portal@ivemar.com.br', 'A', NOW()
WHERE NOT EXISTS (SELECT 1 FROM amb_person WHERE name = 'Ivemar Portal' LIMIT 1);

SET @portal_id = (SELECT id FROM amb_person WHERE name = 'Ivemar Portal' ORDER BY id DESC LIMIT 1);

INSERT INTO amb_user (login_user, login_email, password, active, person_id, company_login_id, created_at)
SELECT 'ivemar', 'portal@ivemar.com.br', @pwd, 'S', @portal_id, @cl_id, NOW()
WHERE NOT EXISTS (SELECT 1 FROM amb_user WHERE login_user = 'ivemar' LIMIT 1);

UPDATE amb_user
SET password = @pwd, active = 'S', person_id = @portal_id, company_login_id = @cl_id,
    login_email = 'portal@ivemar.com.br'
WHERE login_user = 'ivemar';

SET @uid = (SELECT id FROM amb_user WHERE login_user = 'ivemar' LIMIT 1);

INSERT INTO amb_user_profile (amb_user_id, amb_profile_id, created_at)
SELECT @uid, @profile_id, NOW()
WHERE @uid IS NOT NULL
  AND NOT EXISTS (
    SELECT 1 FROM amb_user_profile
    WHERE amb_user_id = @uid AND amb_profile_id = @profile_id
  );

INSERT IGNORE INTO ptk_project_company (project_id, company_id)
SELECT @infoerp_suporte_id, @biz_id FROM DUAL
WHERE @infoerp_suporte_id IS NOT NULL AND @biz_id IS NOT NULL;


-- ========== MODENA ==========
INSERT INTO amb_person (name, email, status, created_at)
SELECT 'Modena', 'portal@modena.com.br', 'A', NOW()
WHERE NOT EXISTS (SELECT 1 FROM amb_person WHERE name = 'Modena' LIMIT 1);

SET @biz_id = (SELECT id FROM amb_person WHERE name = 'Modena' ORDER BY id ASC LIMIT 1);

INSERT INTO amb_company_login (name, name_id, login_type, created_at)
SELECT 'Modena', CAST(@biz_id AS CHAR), 1, NOW()
WHERE NOT EXISTS (SELECT 1 FROM amb_company_login WHERE name = 'Modena' AND login_type = 1 LIMIT 1);

UPDATE amb_company_login
SET name_id = CAST(@biz_id AS CHAR), login_type = 1
WHERE name = 'Modena' AND login_type = 1;

SET @cl_id = (SELECT id FROM amb_company_login WHERE name = 'Modena' AND login_type = 1 ORDER BY id DESC LIMIT 1);

INSERT INTO amb_person (name, email, status, created_at)
SELECT 'Modena Portal', 'portal@modena.com.br', 'A', NOW()
WHERE NOT EXISTS (SELECT 1 FROM amb_person WHERE name = 'Modena Portal' LIMIT 1);

SET @portal_id = (SELECT id FROM amb_person WHERE name = 'Modena Portal' ORDER BY id DESC LIMIT 1);

INSERT INTO amb_user (login_user, login_email, password, active, person_id, company_login_id, created_at)
SELECT 'modena', 'portal@modena.com.br', @pwd, 'S', @portal_id, @cl_id, NOW()
WHERE NOT EXISTS (SELECT 1 FROM amb_user WHERE login_user = 'modena' LIMIT 1);

UPDATE amb_user
SET password = @pwd, active = 'S', person_id = @portal_id, company_login_id = @cl_id,
    login_email = 'portal@modena.com.br'
WHERE login_user = 'modena';

SET @uid = (SELECT id FROM amb_user WHERE login_user = 'modena' LIMIT 1);

INSERT INTO amb_user_profile (amb_user_id, amb_profile_id, created_at)
SELECT @uid, @profile_id, NOW()
WHERE @uid IS NOT NULL
  AND NOT EXISTS (
    SELECT 1 FROM amb_user_profile
    WHERE amb_user_id = @uid AND amb_profile_id = @profile_id
  );

INSERT IGNORE INTO ptk_project_company (project_id, company_id)
SELECT @infoerp_suporte_id, @biz_id FROM DUAL
WHERE @infoerp_suporte_id IS NOT NULL AND @biz_id IS NOT NULL;


SELECT
  'Seed área cliente concluído' AS resultado,
  @infoerp_suporte_id AS infoerp_suporte_id,
  @convenio_id AS convenio_id,
  @profile_id AS profile_id;