-- ============================================================================
-- QUERIES ÚTEIS PARA EXPORTAÇÃO EXCEL E RELATÓRIOS
-- ============================================================================

-- ============================================================================
-- 1. RELATÓRIO COMPLETO DE TRANSAÇÕES (FORMATO EXCEL)
-- ============================================================================

SELECT 
    'ID' AS 'ID',
    'Data' AS 'Data',
    'Tipo' AS 'Tipo',
    'Descrição' AS 'Descrição',
    'Valor' AS 'Valor',
    'Categoria' AS 'Categoria',
    'Subcategoria' AS 'Subcategoria',
    'Conta Origem' AS 'Conta Origem',
    'Conta Destino' AS 'Conta Destino',
    'Cartão' AS 'Cartão',
    'Status' AS 'Status',
    'Parcelas' AS 'Parcelas',
    'Recorrente' AS 'Recorrente',
    'Observações' AS 'Observações'
UNION ALL
SELECT 
    CAST(t.id AS CHAR),
    DATE_FORMAT(t.data_transacao, '%d/%m/%Y'),
    t.tipo,
    t.descricao,
    CONCAT('R$ ', FORMAT(t.valor, 2, 'pt_BR')),
    IFNULL(c.nome, '-'),
    IFNULL(sc.nome, '-'),
    IFNULL(co.nome_conta, '-'),
    IFNULL(cd.nome_conta, '-'),
    IFNULL(cc.nome_cartao, '-'),
    t.status,
    CASE 
        WHEN t.numero_parcelas > 1 THEN CONCAT(t.parcela_atual, '/', t.numero_parcelas)
        ELSE '-'
    END,
    CASE WHEN t.recorrente = 1 THEN 'Sim' ELSE 'Não' END,
    IFNULL(t.observacoes, '')
FROM transacoes t
LEFT JOIN categorias c ON t.categoria_id = c.id
LEFT JOIN subcategorias sc ON t.subcategoria_id = sc.id
LEFT JOIN contas_bancarias co ON t.conta_origem_id = co.id
LEFT JOIN contas_bancarias cd ON t.conta_destino_id = cd.id
LEFT JOIN cartoes_credito cc ON t.cartao_credito_id = cc.id
WHERE t.usuario_id = 1 -- ALTERAR ID DO USUÁRIO
ORDER BY t.data_transacao DESC;

-- ============================================================================
-- 2. FLUXO DE CAIXA MENSAL (FORMATO EXCEL)
-- ============================================================================

SELECT 
    'Período' AS 'Período',
    'Receitas' AS 'Receitas',
    'Despesas' AS 'Despesas',
    'Saldo' AS 'Saldo',
    'Qtd Transações' AS 'Qtd Transações'
UNION ALL
SELECT 
    DATE_FORMAT(CONCAT(ano, '-', LPAD(mes, 2, '0'), '-01'), '%m/%Y'),
    CONCAT('R$ ', FORMAT(total_receitas, 2, 'pt_BR')),
    CONCAT('R$ ', FORMAT(total_despesas, 2, 'pt_BR')),
    CONCAT('R$ ', FORMAT(saldo_mensal, 2, 'pt_BR')),
    CAST(total_transacoes AS CHAR)
FROM vw_fluxo_caixa_mensal
WHERE usuario_id = 1 -- ALTERAR ID DO USUÁRIO
ORDER BY ano DESC, mes DESC
LIMIT 12;

-- ============================================================================
-- 3. DESPESAS POR CATEGORIA (FORMATO EXCEL)
-- ============================================================================

SELECT 
    'Categoria' AS 'Categoria',
    'Total Gasto' AS 'Total Gasto',
    'Qtd Transações' AS 'Qtd Transações',
    'Ticket Médio' AS 'Ticket Médio',
    '% do Total' AS '% do Total'
UNION ALL
SELECT 
    categoria,
    CONCAT('R$ ', FORMAT(total_gasto, 2, 'pt_BR')),
    CAST(quantidade_transacoes AS CHAR),
    CONCAT('R$ ', FORMAT(ticket_medio, 2, 'pt_BR')),
    CONCAT(FORMAT((total_gasto / (SELECT SUM(total_gasto) FROM vw_despesas_por_categoria WHERE usuario_id = 1 AND ano = YEAR(CURDATE()) AND mes = MONTH(CURDATE())) * 100), 2), '%')
FROM vw_despesas_por_categoria
WHERE usuario_id = 1 -- ALTERAR ID DO USUÁRIO
  AND ano = YEAR(CURDATE())
  AND mes = MONTH(CURDATE())
ORDER BY total_gasto DESC;

-- ============================================================================
-- 4. ORÇAMENTO VS REALIZADO (FORMATO EXCEL)
-- ============================================================================

SELECT 
    'Orçamento' AS 'Orçamento',
    'Categoria' AS 'Categoria',
    'Planejado' AS 'Planejado',
    'Gasto' AS 'Gasto',
    'Saldo' AS 'Saldo',
    '% Utilizado' AS '% Utilizado',
    'Status' AS 'Status'
UNION ALL
SELECT 
    nome,
    categoria,
    CONCAT('R$ ', FORMAT(valor_planejado, 2, 'pt_BR')),
    CONCAT('R$ ', FORMAT(valor_gasto, 2, 'pt_BR')),
    CONCAT('R$ ', FORMAT(saldo_restante, 2, 'pt_BR')),
    CONCAT(FORMAT(percentual_utilizado, 2), '%'),
    CASE 
        WHEN status_orcamento = 'excedido' THEN '⚠️ EXCEDIDO'
        WHEN status_orcamento = 'alerta' THEN '⚡ ALERTA'
        ELSE '✅ OK'
    END
FROM vw_orcamento_realizado
WHERE usuario_id = 1 -- ALTERAR ID DO USUÁRIO
  AND mes_referencia = MONTH(CURDATE())
  AND ano_referencia = YEAR(CURDATE())
ORDER BY percentual_utilizado DESC;

-- ============================================================================
-- 5. CONTAS A PAGAR/RECEBER (FORMATO EXCEL)
-- ============================================================================

SELECT 
    'Vencimento' AS 'Vencimento',
    'Tipo' AS 'Tipo',
    'Descrição' AS 'Descrição',
    'Valor' AS 'Valor',
    'Categoria' AS 'Categoria',
    'Conta/Cartão' AS 'Conta/Cartão',
    'Dias Restantes' AS 'Dias Restantes',
    'Status' AS 'Status'
UNION ALL
SELECT 
    DATE_FORMAT(data_vencimento, '%d/%m/%Y'),
    tipo,
    descricao,
    CONCAT('R$ ', FORMAT(valor, 2, 'pt_BR')),
    categoria,
    IFNULL(conta_origem, IFNULL(cartao, '-')),
    CASE 
        WHEN DATEDIFF(data_vencimento, CURDATE()) < 0 THEN CONCAT(ABS(DATEDIFF(data_vencimento, CURDATE())), ' dias ATRASADO')
        ELSE CONCAT(DATEDIFF(data_vencimento, CURDATE()), ' dias')
    END,
    CASE 
        WHEN DATEDIFF(data_vencimento, CURDATE()) < 0 THEN '🔴 VENCIDO'
        WHEN DATEDIFF(data_vencimento, CURDATE()) <= 3 THEN '🟡 URGENTE'
        WHEN DATEDIFF(data_vencimento, CURDATE()) <= 7 THEN '🟢 PRÓXIMO'
        ELSE '⚪ FUTURO'
    END
FROM vw_transacoes_detalhadas
WHERE usuario_id = 1 -- ALTERAR ID DO USUÁRIO
  AND status = 'pendente'
  AND data_vencimento IS NOT NULL
ORDER BY data_vencimento ASC;

-- ============================================================================
-- 6. RESUMO DE CONTAS BANCÁRIAS (FORMATO EXCEL)
-- ============================================================================

SELECT 
    'Conta' AS 'Conta',
    'Tipo' AS 'Tipo',
    'Instituição' AS 'Instituição',
    'Saldo Atual' AS 'Saldo Atual',
    'Limite Crédito' AS 'Limite Crédito',
    'Total Disponível' AS 'Total Disponível',
    'Status' AS 'Status'
UNION ALL
SELECT 
    nome_conta,
    tipo_conta,
    IFNULL(instituicao, '-'),
    CONCAT('R$ ', FORMAT(saldo_atual, 2, 'pt_BR')),
    CONCAT('R$ ', FORMAT(limite_credito, 2, 'pt_BR')),
    CONCAT('R$ ', FORMAT(saldo_total_disponivel, 2, 'pt_BR')),
    CASE WHEN ativo = 1 THEN '✅ Ativa' ELSE '❌ Inativa' END
FROM vw_resumo_contas
WHERE usuario_id = 1 -- ALTERAR ID DO USUÁRIO
ORDER BY saldo_atual DESC;

-- ============================================================================
-- 7. RESUMO DE CARTÕES DE CRÉDITO (FORMATO EXCEL)
-- ============================================================================

SELECT 
    'Cartão' AS 'Cartão',
    'Bandeira' AS 'Bandeira',
    'Instituição' AS 'Instituição',
    'Limite Total' AS 'Limite Total',
    'Limite Disponível' AS 'Limite Disponível',
    'Valor Utilizado' AS 'Valor Utilizado',
    '% Utilizado' AS '% Utilizado',
    'Vencimento' AS 'Vencimento',
    'Status' AS 'Status'
UNION ALL
SELECT 
    nome_cartao,
    bandeira,
    IFNULL(instituicao, '-'),
    CONCAT('R$ ', FORMAT(limite_total, 2, 'pt_BR')),
    CONCAT('R$ ', FORMAT(limite_disponivel, 2, 'pt_BR')),
    CONCAT('R$ ', FORMAT(valor_utilizado, 2, 'pt_BR')),
    CONCAT(FORMAT(percentual_utilizado, 2), '%'),
    CONCAT('Dia ', dia_vencimento),
    CASE 
        WHEN percentual_utilizado >= 90 THEN '🔴 ALERTA'
        WHEN percentual_utilizado >= 70 THEN '🟡 ATENÇÃO'
        ELSE '🟢 OK'
    END
FROM vw_resumo_cartoes
WHERE usuario_id = 1 -- ALTERAR ID DO USUÁRIO
  AND ativo = 1
ORDER BY percentual_utilizado DESC;

-- ============================================================================
-- 8. METAS FINANCEIRAS (FORMATO EXCEL)
-- ============================================================================

SELECT 
    'Meta' AS 'Meta',
    'Valor Alvo' AS 'Valor Alvo',
    'Valor Atual' AS 'Valor Atual',
    'Valor Faltante' AS 'Valor Faltante',
    '% Concluído' AS '% Concluído',
    'Data Início' AS 'Data Início',
    'Data Alvo' AS 'Data Alvo',
    'Dias Restantes' AS 'Dias Restantes',
    'Sugestão Mensal' AS 'Sugestão Mensal',
    'Prioridade' AS 'Prioridade',
    'Situação' AS 'Situação'
UNION ALL
SELECT 
    nome,
    CONCAT('R$ ', FORMAT(valor_alvo, 2, 'pt_BR')),
    CONCAT('R$ ', FORMAT(valor_atual, 2, 'pt_BR')),
    CONCAT('R$ ', FORMAT(valor_faltante, 2, 'pt_BR')),
    CONCAT(FORMAT(percentual_concluido, 2), '%'),
    DATE_FORMAT(data_inicio, '%d/%m/%Y'),
    DATE_FORMAT(data_alvo, '%d/%m/%Y'),
    CAST(dias_restantes AS CHAR),
    CONCAT('R$ ', FORMAT(IFNULL(valor_mensal_sugerido, 0), 2, 'pt_BR')),
    UPPER(prioridade),
    CASE 
        WHEN situacao = 'concluida' THEN '🎉 CONCLUÍDA'
        WHEN situacao = 'vencida' THEN '🔴 VENCIDA'
        WHEN situacao = 'urgente' THEN '🟡 URGENTE'
        ELSE '🟢 EM DIA'
    END
FROM vw_progresso_metas
WHERE usuario_id = 1 -- ALTERAR ID DO USUÁRIO
  AND status = 'em_progresso'
ORDER BY dias_restantes ASC;

-- ============================================================================
-- 9. FATURAS DE CARTÃO (FORMATO EXCEL)
-- ============================================================================

SELECT 
    'Cartão' AS 'Cartão',
    'Período' AS 'Período',
    'Fechamento' AS 'Fechamento',
    'Vencimento' AS 'Vencimento',
    'Valor Total' AS 'Valor Total',
    'Valor Pago' AS 'Valor Pago',
    'Saldo Devedor' AS 'Saldo Devedor',
    'Dias p/ Vencimento' AS 'Dias p/ Vencimento',
    'Status' AS 'Status',
    'Situação' AS 'Situação'
UNION ALL
SELECT 
    nome_cartao,
    periodo,
    DATE_FORMAT(data_fechamento, '%d/%m/%Y'),
    DATE_FORMAT(data_vencimento, '%d/%m/%Y'),
    CONCAT('R$ ', FORMAT(valor_total, 2, 'pt_BR')),
    CONCAT('R$ ', FORMAT(valor_pago, 2, 'pt_BR')),
    CONCAT('R$ ', FORMAT(saldo_devedor, 2, 'pt_BR')),
    CAST(dias_para_vencimento AS CHAR),
    status,
    CASE 
        WHEN situacao = 'paga' THEN '✅ PAGA'
        WHEN situacao = 'vencida' THEN '🔴 VENCIDA'
        WHEN situacao = 'vence_em_breve' THEN '🟡 VENCE EM BREVE'
        ELSE '🟢 EM DIA'
    END
FROM vw_faturas_detalhadas
WHERE usuario_id = 1 -- ALTERAR ID DO USUÁRIO
  AND ano_referencia >= YEAR(DATE_SUB(CURDATE(), INTERVAL 6 MONTH))
ORDER BY data_vencimento DESC;

-- ============================================================================
-- 10. ANÁLISE ANUAL - RESUMO EXECUTIVO (FORMATO EXCEL)
-- ============================================================================

SELECT 
    'Indicador' AS 'Indicador',
    'Valor' AS 'Valor'
UNION ALL
SELECT 
    'Total de Contas Ativas',
    CAST((SELECT COUNT(*) FROM contas_bancarias WHERE usuario_id = 1 AND ativo = TRUE) AS CHAR)
UNION ALL
SELECT 
    'Saldo Total em Contas',
    CONCAT('R$ ', FORMAT((SELECT SUM(saldo_atual) FROM contas_bancarias WHERE usuario_id = 1 AND ativo = TRUE), 2, 'pt_BR'))
UNION ALL
SELECT 
    'Total de Cartões Ativos',
    CAST((SELECT COUNT(*) FROM cartoes_credito WHERE usuario_id = 1 AND ativo = TRUE) AS CHAR)
UNION ALL
SELECT 
    'Limite Total Disponível',
    CONCAT('R$ ', FORMAT((SELECT SUM(limite_disponivel) FROM cartoes_credito WHERE usuario_id = 1 AND ativo = TRUE), 2, 'pt_BR'))
UNION ALL
SELECT 
    'Receitas Este Ano',
    CONCAT('R$ ', FORMAT((SELECT SUM(valor) FROM transacoes WHERE usuario_id = 1 AND tipo = 'receita' AND status = 'pago' AND YEAR(data_transacao) = YEAR(CURDATE())), 2, 'pt_BR'))
UNION ALL
SELECT 
    'Despesas Este Ano',
    CONCAT('R$ ', FORMAT((SELECT SUM(valor) FROM transacoes WHERE usuario_id = 1 AND tipo = 'despesa' AND status = 'pago' AND YEAR(data_transacao) = YEAR(CURDATE())), 2, 'pt_BR'))
UNION ALL
SELECT 
    'Saldo do Ano',
    CONCAT('R$ ', FORMAT(
        (SELECT SUM(valor) FROM transacoes WHERE usuario_id = 1 AND tipo = 'receita' AND status = 'pago' AND YEAR(data_transacao) = YEAR(CURDATE())) -
        (SELECT SUM(valor) FROM transacoes WHERE usuario_id = 1 AND tipo = 'despesa' AND status = 'pago' AND YEAR(data_transacao) = YEAR(CURDATE())),
        2, 'pt_BR'
    ))
UNION ALL
SELECT 
    'Média Mensal de Receitas',
    CONCAT('R$ ', FORMAT((SELECT AVG(total_receitas) FROM vw_fluxo_caixa_mensal WHERE usuario_id = 1 AND ano = YEAR(CURDATE())), 2, 'pt_BR'))
UNION ALL
SELECT 
    'Média Mensal de Despesas',
    CONCAT('R$ ', FORMAT((SELECT AVG(total_despesas) FROM vw_fluxo_caixa_mensal WHERE usuario_id = 1 AND ano = YEAR(CURDATE())), 2, 'pt_BR'))
UNION ALL
SELECT 
    'Total de Transações Este Ano',
    CAST((SELECT COUNT(*) FROM transacoes WHERE usuario_id = 1 AND YEAR(data_transacao) = YEAR(CURDATE())) AS CHAR)
UNION ALL
SELECT 
    'Metas em Progresso',
    CAST((SELECT COUNT(*) FROM metas_financeiras WHERE usuario_id = 1 AND status = 'em_progresso') AS CHAR)
UNION ALL
SELECT 
    'Metas Concluídas',
    CAST((SELECT COUNT(*) FROM metas_financeiras WHERE usuario_id = 1 AND status = 'concluida') AS CHAR)
UNION ALL
SELECT 
    'Contas Vencidas',
    CAST((SELECT COUNT(*) FROM transacoes WHERE usuario_id = 1 AND status = 'pendente' AND data_vencimento < CURDATE()) AS CHAR)
UNION ALL
SELECT 
    'Valor em Contas Vencidas',
    CONCAT('R$ ', FORMAT((SELECT SUM(valor) FROM transacoes WHERE usuario_id = 1 AND status = 'pendente' AND data_vencimento < CURDATE()), 2, 'pt_BR'));

-- ============================================================================
-- INSTRUÇÕES DE USO
-- ============================================================================

/*
COMO EXPORTAR PARA EXCEL:

1. Via MySQL Workbench:
   - Execute a query desejada
   - Clique com botão direito no resultado
   - Selecione "Export Recordset to Excel..."

2. Via phpMyAdmin:
   - Execute a query
   - Clique em "Export"
   - Escolha formato "Microsoft Excel"

3. Via Linha de Comando:
   mysql -u usuario -p -D controle_financeiro < queries_excel.sql > resultado.csv

4. Via Python (recomendado para automação):
   - Use bibliotecas: mysql-connector-python + pandas + openpyxl
   - Conecte ao banco, execute a query e exporte com pandas.to_excel()

5. Via PHP:
   - Use PHPExcel ou PhpSpreadsheet
   - Execute a query e exporte diretamente para XLSX

IMPORTANTE:
- Sempre alterar o usuario_id nas queries para o ID correto
- As queries já estão formatadas com cabeçalhos e valores formatados em R$
- Para períodos customizados, ajuste as condições WHERE
*/