-- Script para atualização retroativa de chart_of_account_id nos receivables
-- Este script atualiza receivables antigos com a categoria do plano de contas do serviço vinculado
-- Execute APÓS executar add_chart_of_account_to_services.sql

-- Atualiza receivables vinculados a vendas de alunos (student_sales)
UPDATE receivables r
INNER JOIN student_sales ss ON r.student_sale_id = ss.id
INNER JOIN services s ON ss.service_id = s.id
SET r.chart_of_account_id = s.chart_of_account_id
WHERE r.student_sale_id IS NOT NULL
  AND s.chart_of_account_id IS NOT NULL
  AND (r.chart_of_account_id IS NULL OR r.chart_of_account_id != s.chart_of_account_id);

-- Atualiza receivables vinculados a vendas de empresas (company_sales)
UPDATE receivables r
INNER JOIN company_sales cs ON r.company_sale_id = cs.id
INNER JOIN services s ON cs.service_id = s.id
SET r.chart_of_account_id = s.chart_of_account_id
WHERE r.company_sale_id IS NOT NULL
  AND s.chart_of_account_id IS NOT NULL
  AND (r.chart_of_account_id IS NULL OR r.chart_of_account_id != s.chart_of_account_id);

-- Mostra estatísticas da atualização
SELECT 
    'Receivables atualizados (alunos)' as tipo,
    COUNT(*) as total
FROM receivables r
INNER JOIN student_sales ss ON r.student_sale_id = ss.id
INNER JOIN services s ON ss.service_id = s.id
WHERE r.student_sale_id IS NOT NULL
  AND s.chart_of_account_id IS NOT NULL
  AND r.chart_of_account_id = s.chart_of_account_id

UNION ALL

SELECT 
    'Receivables atualizados (empresas)' as tipo,
    COUNT(*) as total
FROM receivables r
INNER JOIN company_sales cs ON r.company_sale_id = cs.id
INNER JOIN services s ON cs.service_id = s.id
WHERE r.company_sale_id IS NOT NULL
  AND s.chart_of_account_id IS NOT NULL
  AND r.chart_of_account_id = s.chart_of_account_id;