#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
Agro Revolution — Excel de seguimiento con enlaces click-to-chat de WhatsApp.

Consume Mensajes_WhatsApp.csv (generado por generate_letters.py) y
Contactos_Expo_Agro-2026.xlsx. Produce:
    Contactos_WhatsApp_Expo_Agro-2026.xlsx

Sin envío automático: solo enlaces wa.me que abren el chat con el mensaje
pre-cargado para envío manual.
"""

from __future__ import annotations

import csv
import re
from pathlib import Path
from urllib.parse import quote

import openpyxl
from openpyxl.formatting.rule import FormulaRule
from openpyxl.styles import Alignment, Border, Font, PatternFill, Side
from openpyxl.utils import get_column_letter
from openpyxl.worksheet.datavalidation import DataValidation

ROOT = Path(__file__).resolve().parent
XLSX = ROOT / "Contactos_Expo_Agro-2026.xlsx"
CSV = ROOT / "Mensajes_WhatsApp.csv"
OUT = ROOT / "Contactos_WhatsApp_Expo_Agro-2026.xlsx"

STATUSES = ["Pendiente", "Enviado", "Respondió", "Agendado", "No interesado", "Número inválido"]

GREEN_DEEP = "2E7D32"
GREEN = "54B435"
GOLD = "E9A825"
PALE = "EDFBE2"
AMBER_FILL = "FBF4E2"
AMBER_INK = "8A5E0B"
ROSE_FILL = "FBE4E4"
ROSE_INK = "9B1C1C"
GREEN_FILL = "E3F4DC"

THIN = Side(style="thin", color="D5EAD8")
BORDER = Border(left=THIN, right=THIN, top=THIN, bottom=THIN)


def clean(value) -> str:
    if value is None:
        return ""
    return re.sub(r"\s+", " ", str(value)).strip()


def load_csv() -> dict:
    with CSV.open(encoding="utf-8-sig", newline="") as fh:
        rows = list(csv.DictReader(fh))
    return {int(row["#"]): row for row in rows}


def main() -> int:
    messages = load_csv()

    source = openpyxl.load_workbook(XLSX, data_only=True)["Contactos"]
    raw_rows = list(source.iter_rows(values_only=True))
    headers = [clean(h) for h in raw_rows[0]]
    records = [dict(zip(headers, r)) for r in raw_rows[1:] if any(v is not None for v in r)]

    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = "Envío"

    columns = [
        ("#", 5),
        ("Nombre", 24),
        ("Empresa", 28),
        ("Cargo", 22),
        ("Cultivo / Zona", 26),
        ("País", 12),
        ("Teléfono original", 19),
        ("Número E.164", 16),
        ("Abrir WhatsApp", 16),
        ("Mensaje", 60),
        ("PDF", 34),
        ("Estado", 16),
        ("Fecha de envío", 14),
        ("Notas", 40),
        ("Verificar", 13),
        ("Fuente", 10),
        ("Asesor", 20),
    ]

    header_fill = PatternFill("solid", fgColor=GREEN_DEEP)
    header_font = Font(name="Calibri", bold=True, color="FFFFFF", size=10)
    for col, (title, width) in enumerate(columns, 1):
        cell = ws.cell(row=1, column=col, value=title)
        cell.fill = header_fill
        cell.font = header_font
        cell.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
        cell.border = BORDER
        ws.column_dimensions[get_column_letter(col)].width = width
    ws.row_dimensions[1].height = 30

    for index, row in enumerate(records, 1):
        csv_row = messages.get(index, {})
        message = csv_row.get("Mensaje", "")
        e164 = csv_row.get("Telefono_E164", "")
        digits = e164.lstrip("+")

        name = csv_row.get("Nombre") or clean(row["Nombre"]) or "REVISAR NOMBRE"
        notas = clean(row["Notas"])
        if "duplicado" in notas.lower():
            notas = "POSIBLE DUPLICADO. " + notas
        if index == 32:
            notas = ("Se usó el segundo número; el primero está incompleto. " + notas).strip()

        values = [
            index,
            name,
            clean(row["Empresa"]),
            clean(row["Cargo"]),
            clean(row["Zona / Cultivo"]),
            clean(row["Pais"]),
            clean(row["Telefono/WhatsApp"]),
            e164,
            None,
            message,
            csv_row.get("PDF", ""),
            "Pendiente" if digits else "Número inválido",
            None,
            notas,
            "⚠ Verificar" if clean(row["Verificar"]).lower().startswith("s") else None,
            clean(row["Fuente"]),
            "Diego Quintero" if clean(row["Fuente"]).upper().startswith("D") else "Luis Carlos Gutiérrez Barragán",
        ]
        excel_row = index + 1
        for col, value in enumerate(values, 1):
            cell = ws.cell(row=excel_row, column=col, value=value)
            cell.border = BORDER
            cell.font = Font(name="Calibri", size=10, color="22301F")
            cell.alignment = Alignment(vertical="top", wrap_text=col in (5, 10, 14))

        # Enlace click-to-chat
        if digits:
            url = f"https://wa.me/{digits}?text={quote(message, safe='')}"
            link_cell = ws.cell(row=excel_row, column=9)
            link_cell.value = f'=HYPERLINK("{url}","Abrir chat")'
            link_cell.hyperlink = url
            link_cell.font = Font(name="Calibri", size=10, color="1155CC", underline="single")
            link_cell.alignment = Alignment(horizontal="center", vertical="top")

        # Resaltado de filas marcadas para verificar
        if clean(row["Verificar"]).lower().startswith("s"):
            for col in range(1, len(columns) + 1):
                ws.cell(row=excel_row, column=col).fill = PatternFill("solid", fgColor=AMBER_FILL)

        ws.cell(row=excel_row, column=15).font = Font(
            name="Calibri", size=10, bold=True, color=AMBER_INK
        )
        ws.row_dimensions[excel_row].height = 62

    last_row = len(records) + 1

    # Filtros y paneles
    ws.auto_filter.ref = f"A1:Q{last_row}"
    ws.freeze_panes = "C2"

    # Validación de estado
    dv = DataValidation(
        type="list",
        formula1='"' + ",".join(STATUSES) + '"',
        allow_blank=False,
        showDropDown=False,
    )
    dv.error = "Selecciona un estado de la lista."
    dv.errorTitle = "Estado no válido"
    ws.add_data_validation(dv)
    dv.add(f"L2:L{last_row}")

    # Formato condicional
    ws.conditional_formatting.add(
        f"L2:L{last_row}",
        FormulaRule(formula=['$L2="Enviado"'], fill=PatternFill("solid", fgColor=GREEN_FILL)),
    )
    ws.conditional_formatting.add(
        f"L2:L{last_row}",
        FormulaRule(
            formula=['$L2="Número inválido"'],
            fill=PatternFill("solid", fgColor=ROSE_FILL),
            font=Font(color=ROSE_INK, bold=True),
        ),
    )
    ws.conditional_formatting.add(
        f"O2:O{last_row}",
        FormulaRule(
            formula=['$O2="⚠ Verificar"'],
            fill=PatternFill("solid", fgColor=AMBER_FILL),
            font=Font(color=AMBER_INK, bold=True),
        ),
    )

    # ------------------------------------------------------------------
    # Resumen
    # ------------------------------------------------------------------
    res = wb.create_sheet("Resumen")
    res.sheet_view.showGridLines = False
    res.column_dimensions["A"].width = 38
    res.column_dimensions["B"].width = 14

    title = res.cell(row=1, column=1, value="Resumen de envío — Expo Agro 2026")
    title.font = Font(name="Calibri", size=15, bold=True, color=GREEN_DEEP)
    res.merge_cells("A1:B1")

    res.cell(row=3, column=1, value="Indicador").font = Font(bold=True, color="FFFFFF")
    res.cell(row=3, column=1).fill = PatternFill("solid", fgColor=GREEN_DEEP)
    res.cell(row=3, column=2, value="Valor").font = Font(bold=True, color="FFFFFF")
    res.cell(row=3, column=2).fill = PatternFill("solid", fgColor=GREEN_DEEP)

    metrics = [
        ("Total de contactos", f'=COUNTA(Envío!B2:B{last_row})'),
        ("Colombia", f'=COUNTIF(Envío!F2:F{last_row},"Colombia")'),
        ("Chile", f'=COUNTIF(Envío!F2:F{last_row},"Chile")'),
        ("Perú", f'=COUNTIF(Envío!F2:F{last_row},"Perú")'),
        ("Venezuela", f'=COUNTIF(Envío!F2:F{last_row},"Venezuela")'),
        ("Argentina", f'=COUNTIF(Envío!F2:F{last_row},"Argentina")'),
        ("Pendiente", f'=COUNTIF(Envío!L2:L{last_row},"Pendiente")'),
        ("Enviado", f'=COUNTIF(Envío!L2:L{last_row},"Enviado")'),
        ("Respondió", f'=COUNTIF(Envío!L2:L{last_row},"Respondió")'),
        ("Agendado", f'=COUNTIF(Envío!L2:L{last_row},"Agendado")'),
        ("No interesado", f'=COUNTIF(Envío!L2:L{last_row},"No interesado")'),
        ("Número inválido", f'=COUNTIF(Envío!L2:L{last_row},"Número inválido")'),
        ("Marcados Verificar", f'=COUNTIF(Envío!O2:O{last_row},"⚠ Verificar")'),
        ("Sin teléfono / sin enlace", f'=COUNTIF(Envío!H2:H{last_row},"")'),
        ("Sin nombre de persona", f'=COUNTIF(Envío!B2:B{last_row},"REVISAR NOMBRE")'),
        ("Con enlace de WhatsApp", f'=COUNTA(Envío!I2:I{last_row})'),
        ("Contactos de Luis Carlos", f'=COUNTIF(Envío!Q2:Q{last_row},"Luis Carlos Gutiérrez Barragán")'),
        ("Contactos de Diego", f'=COUNTIF(Envío!Q2:Q{last_row},"Diego Quintero")'),
    ]
    for offset, (label, formula) in enumerate(metrics, start=4):
        label_cell = res.cell(row=offset, column=1, value=label)
        label_cell.border = BORDER
        label_cell.font = Font(size=10, color="22301F")
        value_cell = res.cell(row=offset, column=2, value=formula)
        value_cell.border = BORDER
        value_cell.font = Font(size=10, bold=True, color=GREEN_DEEP)
        value_cell.alignment = Alignment(horizontal="center")

    # ------------------------------------------------------------------
    # Instrucciones
    # ------------------------------------------------------------------
    ins = wb.create_sheet("Instrucciones")
    ins.sheet_view.showGridLines = False
    ins.column_dimensions["A"].width = 5
    ins.column_dimensions["B"].width = 104

    heading = ins.cell(row=1, column=1, value="Cómo enviar las cartas por WhatsApp")
    heading.font = Font(name="Calibri", size=15, bold=True, color=GREEN_DEEP)
    ins.merge_cells("A1:B1")

    intro = ins.cell(
        row=2,
        column=1,
        value=(
            "Envío manual, uno por uno. No hay automatización ni envío masivo: "
            "tú abres el chat, adjuntas el PDF y envías."
        ),
    )
    intro.font = Font(size=10, italic=True, color="54635A")
    ins.merge_cells("A2:B2")

    steps = [
        "Abre este archivo y filtra la columna Estado = Pendiente.",
        "Haz clic en «Abrir chat» del contacto que vas a contactar.",
        "Se abre WhatsApp Web o la app con el mensaje ya escrito.",
        "Adjunta el PDF que aparece en la columna PDF (carpeta Cartas_PDF/).",
        "Revisa el nombre y el cultivo antes de enviar; si algo no cuadra, no envíes.",
        "Marca Estado = Enviado y escribe la fecha en «Fecha de envío».",
    ]
    for i, step in enumerate(steps, 1):
        num = ins.cell(row=i + 3, column=1, value=i)
        num.font = Font(bold=True, color="FFFFFF")
        num.fill = PatternFill("solid", fgColor=GREEN)
        num.alignment = Alignment(horizontal="center", vertical="center")
        num.border = BORDER
        text = ins.cell(row=i + 3, column=2, value=step)
        text.font = Font(size=10, color="22301F")
        text.alignment = Alignment(vertical="center", wrap_text=True)
        text.border = BORDER
        ins.row_dimensions[i + 3].height = 26

    notes = [
        "",
        "Notas importantes",
        "• Los contactos marcados «⚠ Verificar» requieren confirmar nombre o número antes de enviar.",
        "• Los contactos con Estado «Número inválido» no tienen enlace de WhatsApp; no se pueden contactar por este medio.",
        "• Los posibles duplicados se conservaron: confirma antes de fusionarlos.",
        "• Esta hoja es de uso interno: no compartirla con el cliente.",
    ]
    start = len(steps) + 5
    for i, note in enumerate(notes):
        cell = ins.cell(row=start + i, column=1, value=note)
        if i == 1:
            cell.font = Font(size=11, bold=True, color=GREEN_DEEP)
        else:
            cell.font = Font(size=10, color="54635A")
        ins.merge_cells(start_row=start + i, start_column=1, end_row=start + i, end_column=2)

    wb.save(OUT)
    print(f"OK — {OUT}")
    print(f"Filas: {len(records)} | con enlace: {sum(1 for i in range(1, len(records) + 1) if messages.get(i, {}).get('Telefono_E164'))}")
    return 0


if __name__ == "__main__":
    raise SystemExit(main())