-- buscar los empleados que están asignados a un proyecto de otro proceso
select * 
from taxhack.employees e
left join taxhack.employee_project ep on ep.employee_id = e.id
left join taxhack.projects p on ep.project_id = p.id
where e.process_id != p.process_id;

-- employees 980 y 981
-- process 140
-- son dos procesos nuevos del mismo cliente, ambos en paso 1

SELECT * FROM taxhack.employee_project where employee_id in (980, 981);
SELECT * FROM taxhack.projects where id in (823, 824, 825, 826, 827, 828);



-- eliminar los eployee_project del process 141
-- 4
SELECT * FROM `employee_project` where project_id in (827, 828);
DELETE FROM `employee_project` where project_id in (827, 828);


prod test client
processes 137 y 142


-- clientes borrados, que hayan sido creados antes de la migracion y que el step del process sea mayor a 5
-- 153 total
select 
c.id as client_id,
c.company_name, 
e.process_id, 
p.tax_year, p.current_step,
e.id as employee_id, 
e.first_name, e.last_name, e.type, 
e.created_at, e.deleted_at
from employees e
left join process p on e.process_id = p.id
left join clients c on p.client_id = c.id
where e.deleted_at between '2025-02-20 20:00:00' and '2025-02-26 20:20:00' and e.created_at < '2025-02-20 20:00:00'
and p.version = 1 and p.tax_year < 2024;

-- diferencia sin data
(490, 417, 416, 351, 349, 350, 308, 297, 150, 296, 295, 294, 293, 206, 205, 204, 203, 202, 201, 200, 199, 198, 197, 196, 195, 194, 193, 192, 191, 190, 189, 188, 187, 186, 185, 184, 183, 182, 180, 179, 178, 177, 176, 175, 174, 173, 172, 171, 170, 169, 158, 168, 157, 156, 155, 153, 152, 149, 148, 147, 146, 145, 144, 143, 142, 141, 140, 139, 138, 137, 136, 135, 134, 133, 132, 131, 130, 127, 151)

-- ver las diferencias para esos ids
-- solo se detectan modificadas las columnas updated_at y deleted_at
-- 79
SELECT 
    e_sana.id,
    e_sana.process_id AS sana_process_id, e_errores.process_id AS errores_process_id,
    e_sana.first_name AS sana_first_name, e_errores.first_name AS errores_first_name,
    e_sana.last_name AS sana_last_name, e_errores.last_name AS errores_last_name,
    e_sana.type AS sana_type, e_errores.type AS errores_type,
    e_sana.contractor_company AS sana_contractor_company, e_errores.contractor_company AS errores_contractor_company,
    e_sana.state AS sana_state, e_errores.state AS errores_state,
    e_sana.federal_salary AS sana_federal_salary, e_errores.federal_salary AS errores_federal_salary,
    (e_sana.federal_salary - e_errores.federal_salary) AS diff_federal_salary,
    e_sana.state_salary AS sana_state_salary, e_errores.state_salary AS errores_state_salary,
    (e_sana.state_salary - e_errores.state_salary) AS diff_state_salary,
    e_sana.title AS sana_title, e_errores.title AS errores_title,
    e_sana.created_at AS sana_created_at, e_errores.created_at AS errores_created_at,
    e_sana.updated_at AS sana_updated_at, e_errores.updated_at AS errores_updated_at,
    e_sana.deleted_at AS sana_deleted_at, e_errores.deleted_at AS errores_deleted_at,
    e_sana.draft_client_id AS sana_draft_client_id, e_errores.draft_client_id AS errores_draft_client_id,
    e_sana.email AS sana_email, e_errores.email AS errores_email,
    e_sana.department AS sana_department, e_errores.department AS errores_department
FROM `bkp_prod_2025-02-19_20_36`.employees e_sana
JOIN taxhack.employees e_errores ON e_sana.id = e_errores.id
WHERE e_sana.id IN (490, 417, 416, 351, 349, 350, 308, 297, 150, 296, 295, 294, 293, 206, 205, 204, 203, 202, 201, 200, 199, 198, 197, 196, 195, 194, 193, 192, 191, 190, 189, 188, 187, 186, 185, 184, 183, 182, 180, 179, 178, 177, 176, 175, 174, 173, 172, 171, 170, 169, 158, 168, 157, 156, 155, 153, 152, 149, 148, 147, 146, 145, 144, 143, 142, 141, 140, 139, 138, 137, 136, 135, 134, 133, 132, 131, 130, 127, 151)
AND (
    e_sana.process_id <> e_errores.process_id OR
    e_sana.first_name <> e_errores.first_name OR
    e_sana.last_name <> e_errores.last_name OR
    e_sana.type <> e_errores.type OR
    e_sana.contractor_company <> e_errores.contractor_company OR
    e_sana.state <> e_errores.state OR
    e_sana.federal_salary <> e_errores.federal_salary OR
    e_sana.state_salary <> e_errores.state_salary OR
    e_sana.title <> e_errores.title OR
    e_sana.created_at <> e_errores.created_at OR
    e_sana.updated_at <> e_errores.updated_at OR
    e_sana.deleted_at <> e_errores.deleted_at OR
    e_sana.draft_client_id <> e_errores.draft_client_id OR
    e_sana.email <> e_errores.email OR
    e_sana.department <> e_errores.department
);


-- Actualizar los datos de updated y deleted at
-- 79
UPDATE taxhack.employees e
JOIN `bkp_prod_2025-02-19_20_36`.employees s ON e.id = s.id
SET 
    e.updated_at = s.updated_at,
    e.deleted_at = s.deleted_at
WHERE e.id IN (490, 417, 416, 351, 349, 350, 308, 297, 150, 296, 295, 294, 293, 206, 205, 204, 203, 202, 201, 200, 199, 198, 197, 196, 195, 194, 193, 192, 191, 190, 189, 188, 187, 186, 185, 184, 183, 182, 180, 179, 178, 177, 176, 175, 174, 173, 172, 171, 170, 169, 158, 168, 157, 156, 155, 153, 152, 149, 148, 147, 146, 145, 144, 143, 142, 141, 140, 139, 138, 137, 136, 135, 134, 133, 132, 131, 130, 127, 151);




-- buscar las diferencias en la tabla pivot de proyectos
-- 589
SELECT 
    ep_sana.employee_id,
    ep_sana.project_id AS sana_project_id,
    ep_errores.project_id AS errores_project_id
FROM `bkp_prod_2025-02-19_20_36`.employee_project ep_sana
LEFT JOIN taxhack.employee_project ep_errores 
    ON ep_sana.employee_id = ep_errores.employee_id 
    AND ep_sana.project_id = ep_errores.project_id
WHERE ep_sana.employee_id IN (490, 417, 416, 351, 349, 350, 308, 297, 150, 296, 295, 294, 293, 206, 205, 204, 203, 202, 201, 200, 199, 198, 197, 196, 195, 194, 193, 192, 191, 190, 189, 188, 187, 186, 185, 184, 183, 182, 180, 179, 178, 177, 176, 175, 174, 173, 172, 171, 170, 169, 158, 168, 157, 156, 155, 153, 152, 149, 148, 147, 146, 145, 144, 143, 142, 141, 140, 139, 138, 137, 136, 135, 134, 133, 132, 131, 130, 127, 151)
AND ep_errores.project_id IS NULL
UNION
SELECT 
    ep_errores.employee_id,
    ep_sana.project_id AS sana_project_id,
    ep_errores.project_id AS errores_project_id
FROM taxhack.employee_project ep_errores
LEFT JOIN `bkp_prod_2025-02-19_20_36`.employee_project ep_sana 
    ON ep_errores.employee_id = ep_sana.employee_id 
    AND ep_errores.project_id = ep_sana.project_id
WHERE ep_errores.employee_id IN (490, 417, 416, 351, 349, 350, 308, 297, 150, 296, 295, 294, 293, 206, 205, 204, 203, 202, 201, 200, 199, 198, 197, 196, 195, 194, 193, 192, 191, 190, 189, 188, 187, 186, 185, 184, 183, 182, 180, 179, 178, 177, 176, 175, 174, 173, 172, 171, 170, 169, 158, 168, 157, 156, 155, 153, 152, 149, 148, 147, 146, 145, 144, 143, 142, 141, 140, 139, 138, 137, 136, 135, 134, 133, 132, 131, 130, 127, 151)
AND ep_sana.project_id IS NULL;


-- insertar las diferencias filtrando duplicados con el left join
-- 589
INSERT INTO taxhack.employee_project (employee_id, project_id)
SELECT ep_sana.employee_id, ep_sana.project_id
FROM `bkp_prod_2025-02-19_20_36`.employee_project ep_sana
LEFT JOIN taxhack.employee_project ep_errores 
    ON ep_sana.employee_id = ep_errores.employee_id 
    AND ep_sana.project_id = ep_errores.project_id
WHERE ep_sana.employee_id IN (490, 417, 416, 351, 349, 350, 308, 297, 150, 296, 295, 294, 293, 206, 205, 204, 203, 202, 201, 200, 199, 198, 197, 196, 195, 194, 193, 192, 191, 190, 189, 188, 187, 186, 185, 184, 183, 182, 180, 179, 178, 177, 176, 175, 174, 173, 172, 171, 170, 169, 158, 168, 157, 156, 155, 153, 152, 149, 148, 147, 146, 145, 144, 143, 142, 141, 140, 139, 138, 137, 136, 135, 134, 133, 132, 131, 130, 127, 151)
AND ep_errores.project_id IS NULL;



-------------------------------------------------------------------
-- surveys answers se encuentra correcta, no se han perdido datos
-------------------------------------------------------------------


-- buscar en la matrix segun employee_id y process_id
-- id, client_id, process_id, employee_id, functions, non_rd_discount, prototyping, backend, design, non_innovative, general, rd_only, other, total, rd_percentage, federal_expenses, state_expenses, created_at, updated_at, deleted_at
-- 62
SELECT 
    m_sana.id,
    m_sana.employee_id,
    m_sana.client_id AS sana_client_id, m_errores.client_id AS errores_client_id,
    m_sana.process_id AS sana_process_id, m_errores.process_id AS errores_process_id,
    m_sana.functions AS sana_functions, m_errores.functions AS errores_functions,
    m_sana.non_rd_discount AS sana_non_rd_discount, m_errores.non_rd_discount AS errores_non_rd_discount,
    m_sana.prototyping AS sana_prototyping, m_errores.prototyping AS errores_prototyping,
    m_sana.backend AS sana_backend, m_errores.backend AS errores_backend,
    m_sana.design AS sana_design, m_errores.design AS errores_design,
    m_sana.non_innovative AS sana_non_innovative, m_errores.non_innovative AS errores_non_innovative,
    m_sana.general AS sana_general, m_errores.general AS errores_general,
    m_sana.rd_only AS sana_rd_only, m_errores.rd_only AS errores_rd_only,
    m_sana.other AS sana_other, m_errores.other AS errores_other,
    m_sana.total AS sana_total, m_errores.total AS errores_total,
    m_sana.rd_percentage AS sana_rd_percentage, m_errores.rd_percentage AS errores_rd_percentage,
    m_sana.federal_expenses AS sana_federal_expenses, m_errores.federal_expenses AS errores_federal_expenses,
    m_sana.state_expenses AS sana_state_expenses, m_errores.state_expenses AS errores_state_expenses,
    m_sana.created_at AS sana_created_at, m_errores.created_at AS errores_created_at,
    m_sana.updated_at AS sana_updated_at, m_errores.updated_at AS errores_updated_at,
    m_sana.deleted_at AS sana_deleted_at, m_errores.deleted_at AS errores_deleted_at
FROM `bkp_prod_2025-02-19_20_36`.matrix m_sana
JOIN taxhack.matrix m_errores 
    ON m_sana.id = m_errores.id 
    AND m_sana.employee_id = m_errores.employee_id
WHERE m_sana.employee_id IN (490, 417, 416, 351, 349, 350, 308, 297, 150, 296, 295, 294, 293, 206, 205, 204, 203, 202, 201, 200, 199, 198, 197, 196, 195, 194, 193, 192, 191, 190, 189, 188, 187, 186, 185, 184, 183, 182, 180, 179, 178, 177, 176, 175, 174, 173, 172, 171, 170, 169, 158, 168, 157, 156, 155, 153, 152, 149, 148, 147, 146, 145, 144, 143, 142, 141, 140, 139, 138, 137, 136, 135, 134, 133, 132, 131, 130, 127, 151)
AND (
    m_sana.client_id <> m_errores.client_id OR
    m_sana.process_id <> m_errores.process_id OR
    m_sana.functions <> m_errores.functions OR
    m_sana.non_rd_discount <> m_errores.non_rd_discount OR
    m_sana.prototyping <> m_errores.prototyping OR
    m_sana.backend <> m_errores.backend OR
    m_sana.design <> m_errores.design OR
    m_sana.non_innovative <> m_errores.non_innovative OR
    m_sana.general <> m_errores.general OR
    m_sana.rd_only <> m_errores.rd_only OR
    m_sana.other <> m_errores.other OR
    m_sana.total <> m_errores.total OR
    m_sana.rd_percentage <> m_errores.rd_percentage OR
    m_sana.federal_expenses <> m_errores.federal_expenses OR
    m_sana.state_expenses <> m_errores.state_expenses OR
    m_sana.created_at <> m_errores.created_at OR
    m_sana.updated_at <> m_errores.updated_at OR
    m_sana.deleted_at <> m_errores.deleted_at
);


-- actualizar los valores de deleted y updated at de la matrix v1
-- 62
UPDATE taxhack.matrix m
JOIN `bkp_prod_2025-02-19_20_36`.matrix s 
    ON m.id = s.id 
    AND m.employee_id = s.employee_id
SET 
    m.updated_at = s.updated_at,
    m.deleted_at = s.deleted_at
WHERE m.employee_id IN (490, 417, 416, 351, 349, 350, 308, 297, 150, 296, 295, 294, 293, 206, 205, 204, 203, 202, 201, 200, 199, 198, 197, 196, 195, 194, 193, 192, 191, 190, 189, 188, 187, 186, 185, 184, 183, 182, 180, 179, 178, 177, 176, 175, 174, 173, 172, 171, 170, 169, 158, 168, 157, 156, 155, 153, 152, 149, 148, 147, 146, 145, 144, 143, 142, 141, 140, 139, 138, 137, 136, 135, 134, 133, 132, 131, 130, 127, 151);


