import random
import os
import time
import json
import logging
import re

import pyodbc
import requests
from dotenv import load_dotenv

load_dotenv()

logging.basicConfig(
    level=logging.INFO,
    format="%(asctime)s | %(levelname)s | %(message)s",
)

DRY_RUN = os.getenv("DRY_RUN", "0") == "1"
POLL_SECONDS = int(os.getenv("POLL_SECONDS", "5"))
BATCH_LIMIT = int(os.getenv("BATCH_LIMIT", "20"))
MIN_DELAY_SECONDS = float(os.getenv("MIN_DELAY_SECONDS", "6"))
MAX_DELAY_SECONDS = float(os.getenv("MAX_DELAY_SECONDS", "14"))
MAX_RETRY = int(os.getenv("MAX_RETRY", "3"))


def env(name: str, default: str = "") -> str:
    return os.getenv(name, default).strip()


def sql_connection():
    driver = os.getenv("SQLSERVER_DRIVER", "ODBC Driver 17 for SQL Server")

    host = os.getenv("DB_HOST", "127.0.0.1")
    port = os.getenv("DB_PORT", "1433")
    database = os.getenv("DB_DATABASE", "RoboTransmissao")

    user = os.getenv("DB_USERNAME", "")
    password = os.getenv("DB_PASSWORD", "")

    conn_str = (
        f"DRIVER={{{driver}}};"
        f"SERVER={host},{port};"
        f"DATABASE={database};"
        f"UID={user};"
        f"PWD={password};"
        f"TrustServerCertificate=yes;"
        f"Encrypt=no;"
    )

    return pyodbc.connect(conn_str, autocommit=False)


def normalize_phone(phone: str) -> str:
    digits = re.sub(r"\D", "", str(phone or ""))

    if digits and not digits.startswith("55"):
        digits = "55" + digits

    return digits


def render_template_text(template: str, variables_json: str | None, contact_name: str | None = None) -> str:
    variables = {}

    if variables_json:
        try:
            variables = json.loads(variables_json)
        except Exception:
            variables = {}

    text = template or ""

    if contact_name:
        text = text.replace("{{nome}}", str(contact_name))

    for key, value in variables.items():
        text = text.replace("{{" + str(key) + "}}", str(value or ""))

    return text


def build_text_or_media_payload(phone: str, message: str, media_type=None, media_url=None, media_filename=None):
    base = {
        "messaging_product": "whatsapp",
        "to": phone,
    }

    media_type = str(media_type or "").strip().lower()
    media_url = str(media_url or "").strip()

    if media_url and media_type:
        if media_type == "image":
            base["type"] = "image"
            base["image"] = {
                "link": media_url,
                "caption": message or "",
            }
            return base

        if media_type == "video":
            base["type"] = "video"
            base["video"] = {
                "link": media_url,
                "caption": message or "",
            }
            return base

        if media_type == "audio":
            base["type"] = "audio"
            base["audio"] = {
                "link": media_url,
            }
            return base

        base["type"] = "document"
        base["document"] = {
            "link": media_url,
            "caption": message or "",
            "filename": media_filename or "arquivo.pdf",
        }
        return base

    base["type"] = "text"
    base["text"] = {
        "preview_url": False,
        "body": message or "",
    }

    return base


def parse_template_components(variables_json: str | None):
    if not variables_json:
        return None

    try:
        data = json.loads(variables_json)
    except Exception:
        return None

    if isinstance(data, dict) and isinstance(data.get("components"), list):
        return data["components"]

    if not isinstance(data, dict):
        return None

    body_vars = []

    for key in sorted(data.keys()):
        if str(key).isdigit():
            body_vars.append({
                "type": "text",
                "text": str(data[key] or "")
            })

    if not body_vars:
        return None

    return [
        {
            "type": "body",
            "parameters": body_vars
        }
    ]


def build_template_payload(phone: str, template_name: str, template_language: str, variables_json: str | None = None):
    payload = {
        "messaging_product": "whatsapp",
        "to": phone,
        "type": "template",
        "template": {
            "name": template_name,
            "language": {
                "code": template_language or "en_US"
            }
        }
    }

    components = parse_template_components(variables_json)

    if components:
        payload["template"]["components"] = components

    return payload


def cloud_api_send(
    phone: str,
    message: str,
    media_type=None,
    media_url=None,
    media_filename=None,
    message_type="text",
    template_name=None,
    template_language=None,
    variables_json=None,
):
    phone_id = env("META_PHONE_NUMBER_ID") or env("WHATSAPP_PHONE_NUMBER_ID")
    token = env("META_ACCESS_TOKEN") or env("WHATSAPP_ACCESS_TOKEN")
    version = env("WHATSAPP_API_VERSION", "v20.0")

    if DRY_RUN:
        logging.info(
            "DRY_RUN ativo: tipo=%s phone=%s message=%s media=%s template=%s",
            message_type,
            phone,
            (message or "")[:120],
            media_url,
            template_name,
        )
        return {"dry_run": True, "to": phone, "type": message_type}

    if not phone_id or not token:
        raise RuntimeError(
            "Configure META_PHONE_NUMBER_ID/META_ACCESS_TOKEN ou WHATSAPP_PHONE_NUMBER_ID/WHATSAPP_ACCESS_TOKEN no .env"
        )

    url = f"https://graph.facebook.com/{version}/{phone_id}/messages"
    headers = {
        "Authorization": f"Bearer {token}",
        "Content-Type": "application/json",
    }

    if str(message_type or "text").lower() == "template":
        if not template_name:
            raise RuntimeError("Template inválido: template_name vazio.")

        payload = build_template_payload(
            phone=phone,
            template_name=template_name,
            template_language=template_language or "en_US",
            variables_json=variables_json,
        )
    else:
        payload = build_text_or_media_payload(
            phone=phone,
            message=message,
            media_type=media_type,
            media_url=media_url,
            media_filename=media_filename,
        )

    response = requests.post(url, headers=headers, json=payload, timeout=30)

    logging.info("META STATUS: %s", response.status_code)
    logging.info("META RESPONSE: %s", response.text)

    if response.status_code >= 300:
        raise RuntimeError(f"WhatsApp API {response.status_code}: {response.text}")

    return response.json()


def reset_stuck_sending(conn):
    cur = conn.cursor()

    cur.execute(
        """
        UPDATE rt_messages
        SET status = 'retry',
            updated_at = SYSUTCDATETIME()
        WHERE status = 'sending'
          AND DATEDIFF(MINUTE, updated_at, SYSUTCDATETIME()) >= 2
          AND ISNULL(attempts, 0) < ?
        """,
        MAX_RETRY,
    )

    cur.execute(
        """
        UPDATE rt_messages
        SET status = 'failed',
            response_body = 'Falhou: mensagem travada em sending após muitas tentativas.',
            error_message = 'Mensagem travada em sending após muitas tentativas.',
            updated_at = SYSUTCDATETIME()
        WHERE status = 'sending'
          AND DATEDIFF(MINUTE, updated_at, SYSUTCDATETIME()) >= 2
          AND ISNULL(attempts, 0) >= ?
        """,
        MAX_RETRY,
    )


def fetch_due_messages(conn):
    sql = """
    SELECT TOP (?)
        m.id,
        m.campaign_id,
        m.contact_id,
        m.phone,
        m.body,
        m.media_type,
        m.media_url,
        m.media_filename,
        m.message_type,
        m.template_name,
        m.template_language,
        m.variables_json,
        m.attempts,
        c.name AS contact_name
    FROM rt_messages m
    LEFT JOIN rt_contacts c ON c.id = m.contact_id
    WHERE m.status IN ('queued','retry')
      AND ISNULL(m.attempts, 0) < ?
      AND (m.scheduled_at IS NULL OR m.scheduled_at <= SYSUTCDATETIME())
      AND NOT EXISTS (
        SELECT 1 FROM rt_blacklist b
        WHERE b.phone = m.phone
           OR b.phone = REPLACE(REPLACE(REPLACE(m.phone, '+', ''), ' ', ''), '-', '')
      )
    ORDER BY ISNULL(m.scheduled_at, SYSUTCDATETIME()), m.id
    """

    cur = conn.cursor()
    cur.execute(sql, BATCH_LIMIT, MAX_RETRY)
    return cur.fetchall()


def set_status(conn, message_id, status, response=None, error=None):
    cur = conn.cursor()

    response_txt = json.dumps(response, ensure_ascii=False) if response is not None else None

    cur.execute(
        """
        UPDATE rt_messages
        SET status = ?,
            provider_response = ?,
            response_body = ?,
            error_message = ?,
            sent_at = CASE WHEN ? = 'sent' THEN SYSUTCDATETIME() ELSE sent_at END,
            updated_at = SYSUTCDATETIME()
        WHERE id = ?
        """,
        status,
        response_txt,
        response_txt,
        error,
        status,
        message_id,
    )


def increment_attempt(conn, message_id):
    cur = conn.cursor()
    cur.execute(
        """
        UPDATE rt_messages
        SET attempts = ISNULL(attempts, 0) + 1,
            updated_at = SYSUTCDATETIME()
        WHERE id = ?
        """,
        message_id,
    )


def mark_sending(conn, message_id, phone):
    cur = conn.cursor()
    cur.execute(
        """
        UPDATE rt_messages
        SET status = 'sending',
            phone = ?,
            updated_at = SYSUTCDATETIME()
        WHERE id = ?
        """,
        phone,
        message_id,
    )


def refresh_campaign_totals(conn, campaign_id):
    if not campaign_id:
        return

    cur = conn.cursor()

    cur.execute(
        """
        UPDATE rt_campaigns SET
            total_messages = (SELECT COUNT(*) FROM rt_messages WHERE campaign_id = ?),
            sent_messages = (SELECT COUNT(*) FROM rt_messages WHERE campaign_id = ? AND status = 'sent'),
            failed_messages = (SELECT COUNT(*) FROM rt_messages WHERE campaign_id = ? AND status = 'failed'),
            queued_messages = (SELECT COUNT(*) FROM rt_messages WHERE campaign_id = ? AND status IN ('queued','retry')),
            status = CASE
                WHEN (SELECT COUNT(*) FROM rt_messages WHERE campaign_id = ? AND status IN ('queued','retry','sending')) = 0 THEN 'finished'
                ELSE status
            END,
            updated_at = SYSUTCDATETIME()
        WHERE id = ?
        """,
        campaign_id,
        campaign_id,
        campaign_id,
        campaign_id,
        campaign_id,
        campaign_id,
    )


def process_once():
    conn = sql_connection()

    try:
        reset_stuck_sending(conn)
        conn.commit()

        rows = fetch_due_messages(conn)

        if not rows:
            return 0

        processed = 0
        campaign_ids = set()

        for row in rows:
            message_id = row.id

            if row.campaign_id:
                campaign_ids.add(row.campaign_id)

            try:
                phone = normalize_phone(row.phone)

                if not phone or len(phone) < 12:
                    raise RuntimeError(f"Telefone inválido: {row.phone}")

                message_type = str(row.message_type or "text").strip().lower()
                body = render_template_text(row.body, row.variables_json, row.contact_name)

                if message_type == "template":
                    if not row.template_name:
                        raise RuntimeError("Template inválido: template_name vazio.")
                else:
                    if not body and not row.media_url:
                        raise RuntimeError("Mensagem vazia e sem mídia.")

                increment_attempt(conn, message_id)
                mark_sending(conn, message_id, phone)
                conn.commit()

                response = cloud_api_send(
                    phone=phone,
                    message=body,
                    media_type=row.media_type,
                    media_url=row.media_url,
                    media_filename=row.media_filename,
                    message_type=message_type,
                    template_name=row.template_name,
                    template_language=row.template_language,
                    variables_json=row.variables_json,
                )

                set_status(conn, message_id, "sent", response=response)
                conn.commit()

                logging.info("Enviado: message_id=%s phone=%s tipo=%s", message_id, phone, message_type)

            except Exception as exc:
                conn.rollback()

                conn = sql_connection()

                attempts = int(row.attempts or 0) + 1
                status = "failed" if attempts >= MAX_RETRY else "retry"

                try:
                    increment_attempt(conn, message_id)
                    set_status(conn, message_id, status, error=str(exc)[:1000])
                    conn.commit()
                except Exception:
                    conn.rollback()
                    raise

                logging.error("Falha message_id=%s: %s", message_id, exc)

            processed += 1

            delay = random.uniform(MIN_DELAY_SECONDS, MAX_DELAY_SECONDS)
            logging.info("Aguardando %.1fs antes da próxima mensagem...", delay)
            time.sleep(delay)

        for campaign_id in campaign_ids:
            refresh_campaign_totals(conn, campaign_id)

        conn.commit()

        return processed

    finally:
        try:
            conn.close()
        except Exception:
            pass


def main():
    logging.info("Robô de transmissão iniciado. DRY_RUN=%s", DRY_RUN)

    while True:
        try:
            count = process_once()

            if count:
                logging.info("Lote processado: %s mensagens", count)
            else:
                logging.info("Nenhuma mensagem na fila.")

        except Exception as exc:
            logging.exception("Erro no loop principal: %s", exc)
            delay = random.randint(3, 12)
            time.sleep(delay)

        time.sleep(POLL_SECONDS)


if __name__ == "__main__":
    main()
