import sys
import os
import warnings
from urllib.parse import quote_plus
import pandas as pd
import openpyxl
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter
from sqlalchemy import create_engine, text
from datetime import datetime

warnings.filterwarnings("ignore", category=DeprecationWarning)

from langchain_community.utilities import SQLDatabase
from langchain_core.prompts import PromptTemplate
from langchain_core.output_parsers import StrOutputParser
from langchain_ollama import ChatOllama

if sys.platform == "win32":
    sys.stdout.reconfigure(encoding='utf-8', errors='replace')
    sys.stderr.reconfigure(encoding='utf-8', errors='replace')

# 1. CAPTURA DOS PARÂMETROS VINDOS DO FASTHTML
pergunta_usuario = sys.argv[1] if len(sys.argv) > 1 else "Mostrar movimentações"
tipo_operacao = sys.argv[2].lower() if len(sys.argv) > 2 else "venda"

# Configuração do LLM via Ollama
llm = ChatOllama(
    base_url="http://172.16.16.159:11434",
    model="qwen2.5-coder:7b",
    temperature=0,
    timeout=240.0
)

# Configurações do Banco
host = '127.0.0.1'
port = 5432
user = 'postgres'
password = 'ps@web'
dbname = 'bdps01'

try:
    password_encoded = quote_plus(password)
    url = f"postgresql+psycopg2://{user}:{password_encoded}@{host}:{port}/{dbname}"
    
    engine = create_engine(
        url, 
        connect_args={
            "client_encoding": "WIN1252"
        }
    )
    
# 1. Definição condicional das tabelas permitidas conforme a operação
    if tipo_operacao == "venda":
        tabelas_permitidas = [
            'vw_clientes',
            'vw_vendas_langchain',
            'vw_vendas_produtos_langchain',
            'vw_produtos',
            'vw_vendedores',
            'vw_titulos_ar',
            'vw_posic',
            'vw_grupo',
            'vw_estoque',
            'vw_custo',
        ]
    elif tipo_operacao == "compra": 
        tabelas_permitidas = [
            'vw_fornecedores',
            'vw_compras_langchain',          # <-- View de compras (cabeçalho)
            'vw_compras_produtos_langchain', # <-- View de compras (itens)
            'vw_produtos',
            'vw_titulos_ap',
            'vw_posic',
            'vw_grupo',
            'vw_estoque',
            'vw_custo',
        ]
    else:
        tabelas_permitidas = [
            'vw_produtos',
            'vw_posic',
            'vw_grupo',
            'vw_estoque',
            'vw_custo',
            'vw_venda',
        ]

    # 2. Inicialização do SQLDatabase limitando o contexto apenas às tabelas da operação
    db = SQLDatabase(
        engine,
        include_tables=tabelas_permitidas,
        sample_rows_in_table_info=2,
        view_support=True
    )
    template = """Você é um assistente especialista em gerar consultas SQL para PostgreSQL.
Sua única tarefa é retornar uma consulta SQL válida baseada na pergunta do usuário e no tipo de operação.

TIPO DE OPERAÇÃO DA CONSULTA: {tipo_operacao}

REGRAS OBRIGATÓRIAS:

1. Retorne APENAS o código SQL puro. Não inclua explicações, markdown extra ou formatação de texto além da instrução SQL.
2. Use SOMENTE as views fornecidas no schema. Nunca invente colunas ou tabelas.
3. REGRA DE TABELAS SEGUNDO O TIPO DE OPERAÇÃO:
   - Se TIPO DE OPERAÇÃO = 'venda': Utilize 'vw_vendas_langchain' ou 'vw_vendas_produtos_langchain' ou 'vw_titulos_ar'.
   - Se TIPO DE OPERAÇÃO = 'compra': Utilize 'vw_compras_langchain' ou 'vw_compras_produtos_langchain' ou 'vw_titulos_ap'.
4. REGRA DE SOMA: Sempre inclua no SELECT a soma da quantidade e do valor total 
(ex: SUM(quantidade_produto), SUM(valor_produto), sum(e.quantidade_estoque))
4a. Sempre que o usuario quiser saber quem mais vendeu ou mais comprou informe quantidade e valor  
5. Para filtros de texto, use ILIKE com '%'.
6. Utilize aliases claros para as views quando fizer JOINs.
7. A tabela vw_clientes contém os clientes e os fornecedores
8. Sempre faca as pesquisas em ordem de valor desc ou ordem de data
9. Toda a vez que mencionar titulos mostra o nome do cliente/fonedor, numero_titulo, ordem, filial, 
data_nota, data_vencto, portador, valor_titulovalor do título
10. Se a pergunta for Cliente que comprou esse ano mas não compra ha 3 meses use o sql
SELECT 
    c.codigo_cliente,
    c.nome_cliente,
    c.municipio,
    c.uf,
    MAX(v.data_nota) AS data_ultima_compra,
    SUM(v.valor_nota) AS total_comprado_ano
FROM vw_clientes c
JOIN vw_vendas_langchain v 
    ON c.codigo_cliente = v.codigo_do_cliente
GROUP BY 
    c.codigo_cliente,
    c.nome_cliente,
    c.municipio,
    c.uf
HAVING 
    -- Comprou no ano atual (2026)
    MAX(v.data_nota) >= DATE_TRUNC('year', CURRENT_DATE)
    -- Não compra há pelo menos 3 meses
    AND MAX(v.data_nota) <= CURRENT_DATE - INTERVAL '3 months';

11. Quero uma listagem dos produtos com o estoque só do codigo  posicionamento  10.12.60.10 
(este codigo é um exemplo) tem que tirar os pontos
SELECT 
    p.codigo_produto,
    p.descricao_produto,
    pos.descricao_posicionamento,
    sum(e.quantidade_estoque)
FROM 
    vw_produtos p
JOIN 
    vw_estoque e ON p.codigo_produto = e.codigo_produto
JOIN 
    vw_posic pos ON p.codigo_posicionamento = pos.posicionamento
WHERE 
    pos.posicionamento = '10126010' -- Ajustado para comparar com a coluna de código
GROUP BY  p.codigo_produto,
    p.descricao_produto,
    pos.descricao_posicionamento
ORDER BY 
    sum(e.quantidade_estoque) DESC;
12. Quero uma listagem dos produtos com o estoque só do  grupo 1 (um exemplo)
SELECT 
    p.codigo_produto,
    p.descricao_produto,
    sum(e.quantidade_estoque)
FROM 
    vw_produtos p
JOIN 
    vw_estoque e ON p.codigo_produto = e.codigo_produto
JOIN 
    vw_grupo g ON p.codigo_grupo = g.codigo_grupo
WHERE 
    g.codigo_grupo = 1
GROUP BY  p.codigo_produto,
    p.descricao_produto
ORDER BY 
    sum(e.quantidade_estoque) DESC;

13. Quanto eu faturei nesse grupo  1 nesse mes
SELECT 
    SUM(v.valor_produto) AS total_faturado
FROM 
    vw_vendas_produtos_langchain  v
JOIN 
    vw_produtos p ON v.codigo_produto = p.codigo_produto
JOIN 
    vw_grupo g ON p.codigo_grupo = g.codigo_grupo
WHERE 
    g.codigo_grupo = 1
    AND EXTRACT(MONTH FROM v.data_nota) = EXTRACT(MONTH FROM CURRENT_DATE)
    AND EXTRACT(YEAR FROM v.data_nota) = EXTRACT(YEAR FROM CURRENT_DATE)

14.Qual é o valor de custo do meu estoque?
SELECT SUM(e.quantidade_estoque * c.custo) AS valor_estoque
FROM vw_estoque e
JOIN vw_custo c ON e.codigo_produto = c.codigo_produto
WHERE e.quantidade_estoque > 0

15.Qual o valor de venda do meu estoque
SELECT SUM(e.quantidade_estoque * v.preco_venda) AS valor_estoque
FROM vw_estoque e
JOIN vw_venda v ON e.codigo_produto = v.codigo_produto
WHERE e.quantidade_estoque > 0

16.Quais são as contas a pagar  com vencimento programado para os próximos 7 dias?
SELECT 
    ap.codigo_fornecedor,
    fn.nome_fornecedor,
    ap.numero_titulo,
    ap.ordem,
    ap.filial,
    ap.data_nota,
    ap.data_vencto,
    ap.portador,
    ap.valor_titulo
FROM 
    vw_titulos_ap ap
JOIN vw_fornecedores fn
    ON ap.codigo_fornecedor = fn.codigo_fornecedor
WHERE 
    ap.data_vencto BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL '7 days'
ORDER BY 
    ap.data_vencto DESC;

17. Relatório  Posicionamento
SELECT posicionamento, descricao_posicionamento
  FROM public.vw_posic
ORDER BY descricao_posicionamento;


18.Quem foi o 5 cliente que mais comprou nesse mes
SELECT 
    c.codigo_cliente,
    c.nome_cliente,
    c.municipio,
    c.uf,
    SUM(v.valor_nota) AS valor_total_comprado
FROM vw_vendas_langchain v
INNER JOIN vw_clientes c 
    ON v.codigo_do_cliente = c.codigo_cliente
WHERE 
    -- Filtra as notas emitidas no mês e ano atuais
    DATE_TRUNC('month', v.data_nota) = DATE_TRUNC('month', CURRENT_DATE)
GROUP BY 
    c.codigo_cliente,
    c.nome_cliente,
    c.municipio,
    c.uf
ORDER BY 
    valor_total_comprado DESC
LIMIT 5;

SCHEMA DO BANCO DE DADOS (PostgreSQL):

-- View de Clientes
vw_clientes (codigo_cliente, nome_cliente, municipio, uf)

-- View de Fornecedores
vw_fornecedores (codigo_fornecedor, nome_fornecedor, municipio, uf)

-- Views de Vendas
vw_vendas_langchain (numero_nota, filial, codigo_do_cliente, data_nota, valor_nota)
vw_vendas_produtos_langchain (numero_nota, filial, codigo_cliente, nome_cliente, codigo_vendedor, nome_vendedor, data_nota, codigo_produto, descricao_produto, quantidade_produto, valor_produto)

-- Views de Compras
vw_compras_langchain (numero_nota, filial, codigo_fornecedor, data_nota, valor_nota)
vw_compras_produtos_langchain (numero_nota, filial, codigo_fornecedor, nome_fornecedor, data_nota, codigo_produto, descricao_produto, quantidade_produto, valor_produto)

-- View de Cadastro de Produtos
vw_produtos (codigo_produto, referencia, descricao_produto)

-- View de Cadastro de Vendedores
vw_vendedores (codigo_vendedor, nome_vendedor, bairro_vendedor, municipio_vendedor, uf_vendedor)

-- View de Contas a Receber
vw_titulos_ar (codigo_cliente, numero_titulo, ordem, filial, data_nota, data_vencto, portador, valor_titulo)

-- View de Contas a Pagar
vw_titulos_ap (codigo_fornecedor, numero_titulo, ordem, filial, data_nota, data_vencto, portador, valor_titulo)

-- View public.vw_posic
vw_posic (posicionamento, descricao_posicionamento)

-- View de vw_grupo
vw_grupo (codigo_grupo, descricao_grupo)

-- View de estoque
vw_estoque (codigo_produto, filial, estoque)

-- View de custo
vw_custo (codigo_produto, custo)

Pergunta: {question}
Query SQL:
"""

    prompt = PromptTemplate.from_template(template)

    schema_info = db.get_table_info(tabelas_permitidas)
    
    chain = (
        {
            "question": lambda x: x["question"],
            "tipo_operacao": lambda x: x["tipo_operacao"],
            "table_info": lambda x: schema_info,
            "dialect": lambda x: db.dialect
        }
        | prompt
        | llm
        | StrOutputParser()
    )

    # Executa a chain passando a pergunta e o tipo de operação
    query_gerada = chain.invoke({
        "question": pergunta_usuario,
        "tipo_operacao": tipo_operacao
    })
    
    # Limpa marcadores do markdown
    query_limpa = query_gerada.replace("```sql", "").replace("```", "").strip()
    
    print(f"Operação: {tipo_operacao.upper()}")
    print(f"SQL Gerado:\n{query_limpa}\n")

# 2. GRAVAÇÃO DO LOG NO BANCO (sc_log)
    try:
        sql_log = text("""
            INSERT INTO public.sc_log (
                inserted_date, username, application, creator, ip_user, action, description
            ) VALUES (
                :inserted_date, :username, :application, :creator, :ip_user, :action, :description
            )
        """)
        descricao_completa = f"Pergunta: {pergunta_usuario}\nSQL: {query_limpa}"

        with engine.begin() as conn:
            conn.execute(sql_log, {
                "inserted_date": datetime.now(),
                "username": "usuario",
                "application": "grok",
                "creator": "usuario",
                "ip_user": "01.01.01",
                "action": "insert",
                "description": descricao_completa
            })
    except Exception as e_log:
        sys.stderr.write(f"Aviso: Falha ao gravar log no banco: {str(e_log)}\n")

    df = pd.read_sql_query(query_limpa, con=engine)
    total_linhas = len(df)
    
    if total_linhas > 0:
        nome_arquivo = "resultado.xlsx"
        nome_aba = "Resultado_Compras" if tipo_operacao == "compra" else "Resultado_Vendas"
        
        with pd.ExcelWriter(nome_arquivo, engine='openpyxl') as writer:
            df.to_excel(writer, index=False, sheet_name=nome_aba)
            
            workbook = writer.book
            worksheet = writer.sheets[nome_aba]
            worksheet.views.sheetView[0].showGridLines = True
            
            header_font = Font(name="Arial", size=11, bold=True, color="FFFFFF")
            header_fill = PatternFill(start_color="1F497D", end_color="1F497D", fill_type="solid")
            thin_border = Border(
                left=Side(style='thin', color='D9D9D9'), right=Side(style='thin', color='D9D9D9'),
                top=Side(style='thin', color='D9D9D9'), bottom=Side(style='thin', color='D9D9D9')
            )
            zebra_fill = PatternFill(start_color="F2F5F8", end_color="F2F5F8", fill_type="solid")
            
            col_valor_idx = None
            for idx, col_name in enumerate(df.columns, start=1):
                col_lc = str(col_name).strip().lower()
                if col_lc.startswith(("valor", "total", "faturamento", "sum", "custo", "preco_venda")):
                    col_valor_idx = idx
            
            if col_valor_idx is None:
                col_valor_idx = len(df.columns)
            
            col_valor_letter = get_column_letter(col_valor_idx)
            
            pct_col_idx = len(df.columns) + 1
            pct_col_letter = get_column_letter(pct_col_idx)
            row_total = total_linhas + 2

            # Cabeçalho
            for col_num in range(1, pct_col_idx + 1):
                cell = worksheet.cell(row=1, column=col_num)
                if col_num == pct_col_idx:
                    cell.value = "% Relativo"
                cell.font = header_font
                cell.fill = header_fill
                cell.alignment = Alignment(horizontal="center", vertical="center")
                cell.border = thin_border
# Dados e Fórmulas
            for r_idx in range(2, total_linhas + 2):
                for c_idx in range(1, len(df.columns) + 1):
                    cell = worksheet.cell(row=r_idx, column=c_idx)
                    cell.border = thin_border
                    if r_idx % 2 == 0:
                        cell.fill = zebra_fill
                    
                    nome_coluna = str(df.columns[c_idx - 1]).strip().lower()
                    
                    # 1. Colunas de Valores / Quantidades
                    if nome_coluna.startswith(("valor", "quantidade", "total", "faturamento", "sum", "custo", "preco_venda")):
                        cell.alignment = Alignment(horizontal="right", vertical="center")
                        if nome_coluna.startswith(("valor", "total", "faturamento", "sum", "custo", "preco_venda")):
                            cell.number_format = '#,##0.00'
                        else:
                            cell.number_format = '#,##0'
                    
                    # 2. Colunas de Datas (Formato dd-mm-yyyy e Centralizadas)
                    elif nome_coluna.startswith("data"):
                        cell.alignment = Alignment(horizontal="center", vertical="center")
                        cell.number_format = 'dd-mm-yyyy'
                    
                    # 3. Demais colunas (Texto / Códigos)
                    else:
                        cell.alignment = Alignment(horizontal="left", vertical="center")
                
                pct_cell = worksheet.cell(row=r_idx, column=pct_col_idx)
                pct_cell.value = f"=IFERROR({col_valor_letter}{r_idx}/{col_valor_letter}${row_total}, 0)"
                pct_cell.border = thin_border
                pct_cell.number_format = '0.00%'
                pct_cell.alignment = Alignment(horizontal="right", vertical="center")
                if r_idx % 2 == 0:
                    pct_cell.fill = zebra_fill            
 

            # Totais
            total_font = Font(name="Arial", size=11, bold=True, color="000000")
            total_fill = PatternFill(start_color="D9E1F2", end_color="D9E1F2", fill_type="solid")
            total_border = Border(
                left=Side(style='thin', color='D9D9D9'), right=Side(style='thin', color='D9D9D9'),
                top=Side(style='thin', color='000000'), bottom=Side(style='double', color='000000')
            )
            
            worksheet.cell(row=row_total, column=1, value=f"Total: {total_linhas}").font = total_font
            
            for c_idx in range(1, pct_col_idx + 1):
                cell = worksheet.cell(row=row_total, column=c_idx)
                cell.font = total_font
                cell.fill = total_fill
                cell.border = total_border
                
                if c_idx <= len(df.columns):
                    nome_coluna = str(df.columns[c_idx - 1]).strip().lower()
                    col_letter = get_column_letter(c_idx)
                    if nome_coluna.startswith(("valor", "quantidade", "total", "faturamento", "sum", "custo", "preco_venda")):
                        cell.value = f"=SUM({col_letter}2:{col_letter}{row_total - 1})"
                        cell.alignment = Alignment(horizontal="right", vertical="center")
                        if nome_coluna.startswith(("valor", "total", "faturamento", "sum", "custo", "preco_venda")):
                            cell.number_format = '#,##0.00'
                        else:
                            cell.number_format = '#,##0'
                    else:
                        if c_idx > 1:
                            cell.value = ""
                        cell.alignment = Alignment(horizontal="left", vertical="center")
                else:
                    cell.value = f"=SUM({pct_col_letter}2:{pct_col_letter}{row_total - 1})"
                    cell.number_format = '0.00%'
                    cell.alignment = Alignment(horizontal="right", vertical="center")

            # Largura das colunas
            for col in worksheet.columns:
                max_len = max(len(str(cell.value or '')) for cell in col)
                col_letter = get_column_letter(col[0].column)
                worksheet.column_dimensions[col_letter].width = max(max_len + 4, 12)
        
        primeira_linha = df.head(1).to_string(index=False)
        print(f"Primeira linha do resultado:\n{primeira_linha}\n")
        print(f"[OK] {total_linhas} registro(s) encontrado(s) e salvos em 'resultado.xlsx'.")
    else:
        print(f"A consulta não retornou nenhum resultado no banco de dados.")

except Exception as e:
    sys.stderr.write(f"Erro ao processar consulta: {str(e)}\n")
    sys.exit(1)