def normalize_goes_goe_tpl_tipoplaza_sql
  "UPDATE goes_goe_tpl_tipoplaza SET tpl_titulo = TRIM(CHAR(9) FROM TRIM(tpl_titulo))"
end

def normalize_goes_goe_pla_plaza_sql
  "UPDATE goes_goe_pla_plaza SET pla_titulo = TRIM(CHAR(9) FROM TRIM(pla_titulo)), pla_salario = IFNULL(pla_salario, 0)"
end

def migrate_remunerations_sql(institution_id)
  "INSERT INTO institution_remunerations
  SELECT
    '',
    t1.pla_titulo,
    SUM(1),
    t1.pla_salario,
    t1.pla_dietas,
    t3.tpl_titulo,
    #{institution_id},
    NOW(),
    NOW()
  FROM goes_goe_pla_plaza t1
  LEFT JOIN goes_goe_fun_funcionario t2 ON (t1.pla_id_pue = t2.fun_id_pla)
  LEFT JOIN goes_goe_tpl_tipoplaza t3 ON (t1.pla_id_tpl = t3.tpl_id)
  WHERE
    t1.pla_salario > 0
    AND t3.tpl_id IS NOT NULL
  GROUP BY
    t1.pla_titulo,
    t1.pla_salario,
    t1.pla_dietas,
    t3.tpl_titulo
  ORDER BY
    t1.pla_titulo"
end

def destroy_remunerations_sql(institution_id)
  "DELETE FROM institution_remunerations WHERE institution_id = #{institution_id}"
end

namespace :remunerations do
  desc 'Migrate institution_remunerations'
  task :migrate, [:institution_id] => :environment do |tsk, args|
    ActiveRecord::Base.connection().execute(normalize_goes_goe_tpl_tipoplaza_sql)
    ActiveRecord::Base.connection().execute(normalize_goes_goe_pla_plaza_sql)
    ActiveRecord::Base.connection().execute(migrate_remunerations_sql(args[:institution_id]))
  end
  
  task :destroy, [:institution_id] => :environment do |tsk, args|
    ActiveRecord::Base.connection().execute(destroy_remunerations_sql(args[:institution_id]))
  end
end