#!/usr/bin/env python3
# -*- coding: utf-8 -*-

import psycopg2
import requests
import time
from datetime import timedelta
from docx import Document
from docx.shared import RGBColor
import urllib3

# ==============================
# DESATIVA AVISO SSL
# ==============================
urllib3.disable_warnings(urllib3.exceptions.InsecureRequestWarning)

# ==============================
# CONFIGURAÇÕES
# ==============================
TOKEN_TEMPORARIO = "eyJ0eXAiOiJKV1QiLCJhbGciOiJSUzI1NiJ9.eyJhdWQiOiI2IiwianRpIjoiNjJjODE5NWE0OTk2ZDkwNGQxZmI1MDU5NWJmOGNlYzFhYzZlYThiYjk5ZDk1YjUyZTY5OTE1OWE1Mzc5MzNiMTQzMDhiNDFlMGZlOGU4MjIiLCJpYXQiOjE3Njg1ODgyNDUuOTk1MTUsIm5iZiI6MTc2ODU4ODI0NS45OTUxNTIsImV4cCI6MTc2OTg4NDI0NS45OTIzOSwic3ViIjoiNCIsInNjb3BlcyI6W119.EhL2_a0bpH5A-cxXY-FnVQVzaX5fYVZhKX8sTuxKJmAEk6WsM1qW3YperrASvM74Tsk1cNjqg_1bRjDOFn6QMyO8yTw_y-9M33Q6q_0_Ijm-k22oSYdEzc83fIhM3QSLNkwOQfj2PYvPBUf6gGFpDXp1r2LG-egIYfAuovF_npY8f_HujwzridHFJZh1CvU_4H9Kd_eHj0dlmSASwcr0zFBepiQRIOsRa3Ol9jhrN7HzQ9aRATsEqs8H_VaXxZSL36foHONELWAP1XflOEgPc1NRSQYtp_yWoe0EuE-vRh1EJI145d7TO739x-Li6HbtqAWVjtbkyEe4_32RwrLHnQMyDenVwJHJv13nDZn-UVOBoTO4TXyLzSACp23yXn8Ce3L7RVdmFAUujyBN3dfbzLQONpWJ5B1RV-5neEoj14R1ptPXuHZtaEkJ1BFqxWp5Ugt5jveVbCNgrX9pmY45qlcfxry1v4M81WiyLtNdeooIKQJKQOV8U0lk8Q7lihD72Ec5aNZNgY-e9qXr9mEa6c78VrlfJzx8piRzNLCuDgkYA0zE0xfcf1naSa75vbqGfutcanOlO8KwwMeybnhzYF6OhnuoJy0lSbFWaRKYn9Z-cAfGatJpGSrsrNHFBO4TNCSp4j6U3wHXOJ3koqPT_vI_QWYrj-2Slw6nifG66yw"

HOST_DB = "191.37.171.239"
DATABASE = "dbsgp"
PORT = 5432
USER_DB = "consulta_sgp"
PASS_DB = "LvUgFpVXyo9ofczw"

ID_CLIENTE_ALVO = 658
ID_CIRCUITO_SAPERX = 8009186

API_URL = "https://38.226.184.125:17122/cdr"

TIME_START = "2023-01-01 00:00:00"
TIME_END   = "2023-01-20 23:59:59"

# ==============================
# FUNÇÃO PRINCIPAL
# ==============================
def gerar_docx_venner():

    # ------------------------------
    # 1. BUSCA DADOS DO CLIENTE
    # ------------------------------
    try:
        conn = psycopg2.connect(
            host=HOST_DB,
            port=PORT,
            database=DATABASE,
            user=USER_DB,
            password=PASS_DB,
            sslmode="prefer"
        )
        cur = conn.cursor()

        cur.execute(f"""
            SELECT
                upper(p.nome),
                p.cpfcnpj,
                c.id,
                upper(e.logradouro),
                e.numero,
                upper(e.complemento),
                upper(e.bairro),
                upper(e.cidade),
                upper(e.uf),
                (
                    SELECT co.contato
                    FROM admcore_contato co
                    INNER JOIN admcore_clientecontato cc ON cc.contato_id = co.id
                    WHERE cc.cliente_id = c.id AND co.tipo = 'EMAIL'
                    LIMIT 1
                ),
                (
                    SELECT co.contato
                    FROM admcore_contato co
                    INNER JOIN admcore_clientecontato cc ON cc.contato_id = co.id
                    WHERE cc.cliente_id = c.id AND co.tipo LIKE 'CELULAR%'
                    LIMIT 1
                )
            FROM admcore_cliente c
            INNER JOIN admcore_pessoa p ON c.pessoa_id = p.id
            INNER JOIN admcore_endereco e ON c.endereco_id = e.id
            WHERE c.id = {ID_CLIENTE_ALVO}
        """)

        res = cur.fetchone()
        cur.close()
        conn.close()

        if not res:
            print("Cliente não encontrado.")
            return

        nome, cnpj, id_cli = res[0], res[1], res[2]
        end_1 = f"{res[3]} {res[4]} {res[5] or ''}".strip()
        end_2 = f"{res[6]}, {res[7]}-{res[8]}"
        email = res[9] or "Não informado"
        telefone = res[10] or "Não informado"

    except Exception as e:
        print("Erro ao buscar cliente no banco:", e)
        return

    # ------------------------------
    # 2. CONSULTA API (GET)
    # ------------------------------
    headers = {
        "Authorization": f"Bearer {TOKEN_TEMPORARIO}",
        "Accept": "application/json"
    }

    params = {
        "time_start": TIME_START,
        "time_end": TIME_END,
        "id": ID_CIRCUITO_SAPERX,
        "timestamp": int(time.time())
    }

    chamadas = []

    try:
        response = requests.get(
            API_URL,
            headers=headers,
            params=params,
            verify=False,
            timeout=30
        )

        print("=" * 60)
        print("URL FINAL:", response.url)
        print("STATUS:", response.status_code)
        print("RESPONSE:")
        print(response.text)
        print("=" * 60)

        try:
            resposta_json = response.json()
        except Exception:
            print("Resposta não é JSON:")
            resposta_json = {}

        if response.status_code == 200:
            if "data" in resposta_json and isinstance(resposta_json["data"], list):
                chamadas = resposta_json["data"]
                print(f"API OK — {len(chamadas)} registros encontrados")
            else:
                print("API OK, mas sem dados para o período informado.")
                chamadas = []
        else:
            print(f"Erro da API ({response.status_code}): {resposta_json}")

    except Exception as e:
        print(f"Erro ao consultar API: {e}")


    # ------------------------------
    # 3. GERA DOCX
    # ------------------------------
    doc = Document()
    azul = RGBColor(0, 112, 192)

    doc.add_heading("Informações do Cliente e Serviço", level=1).runs[0].font.color.rgb = azul

    p = doc.add_paragraph()
    p.add_run("CLIENTE (FATURAMENTO)\n").bold = True
    p.add_run(f"{nome}\n").bold = True
    p.add_run(f"CNPJ: {cnpj}\n")
    p.add_run(f"ID DO CLIENTE: {id_cli}\n")
    p.add_run(f"ENDEREÇO: {end_1}\n{end_2}\n\n")
    p.add_run(f"Telefone: {telefone}\n")
    p.add_run(f"E-mail: {email}\n\n")
    p.add_run("Descrição do Serviço:\n").bold = True
    p.add_run("VOIP – PABX VIRTUAL\n")

    doc.add_heading("Resumo de Chamadas", level=2).runs[0].font.color.rgb = azul

    total_segundos = sum(int(float(c.get("duration", 0))) for c in chamadas)
    atendidas = sum(1 for c in chamadas if c.get("status") == "ANSWERED")

    r = doc.add_paragraph()
    r.add_run(f"Total de Ligações: {len(chamadas)}\n")
    r.add_run(f"Chamadas Atendidas: {atendidas}\n")
    r.add_run(f"Minutagem Total: {timedelta(seconds=total_segundos)}")

    doc.add_heading("Detalhe do Consumo", level=2).runs[0].font.color.rgb = azul

    tabela = doc.add_table(rows=1, cols=6)
    tabela.style = "Table Grid"

    headers_tab = ["Data", "Origem", "Destino", "Duração", "Status", "ID"]
    for i, h in enumerate(headers_tab):
        tabela.rows[0].cells[i].text = h

    for c in chamadas:
        row = tabela.add_row().cells
        row[0].text = str(c.get("time_start", ""))
        row[1].text = str(c.get("source_number", "-"))
        row[2].text = str(c.get("destination_number", "-"))
        duracao_segundos = int(float(c.get("duration", 0) or 0))
        row[3].text = str(timedelta(seconds=duracao_segundos))
        row[4].text = str(c.get("status", "-"))
        row[5].text = str(c.get("id", "-"))

    nome_arquivo = f"RELATORIO_CLIENTE_{id_cli}_NOV_2025.docx"
    doc.save(nome_arquivo)

    print(f"Arquivo gerado com sucesso: {nome_arquivo}")


# ==============================
# EXECUÇÃO
# ==============================
if __name__ == "__main__":
    gerar_docx_venner()