"""
Import the Birdie Technical Support number's full WhatsApp history from
Evolution's own database into the system inbox.

WhatsApp now addresses most chats by a privacy id ("<digits>@lid") instead of a
phone number, and Evolution stored only that id. So each chat is keyed by the
best identity we can recover, in this order:
  1. the real phone from key.remoteJidAlt, if it ever appeared in the chat
  2. the phone, when the chat is addressed by phone at all
  3. otherwise the lid / group id, with the WhatsApp display name for the agent

Idempotent: existing wa_message_ids are skipped, so a re-run tops up rather than
duplicating.
"""
import csv, json, os, re, sys, time, urllib.request, urllib.error
from collections import defaultdict

csv.field_size_limit(sys.maxsize)

SRC = "/tmp/birdie_msgs.csv"
INSTANCE = "birdie"
BATCH = 500

url = os.environ["SUPABASE_URL"].rstrip("/")
key = os.environ["SUPABASE_SERVICE_ROLE_KEY"]
HDR = {"apikey": key, "Authorization": "Bearer " + key, "Content-Type": "application/json"}


def req(method, path, body=None, prefer=None, retries=4):
    h = dict(HDR)
    if prefer:
        h["Prefer"] = prefer
    data = json.dumps(body).encode() if body is not None else None
    for attempt in range(retries):
        try:
            r = urllib.request.Request(url + "/rest/v1/" + path, method=method, data=data, headers=h)
            raw = urllib.request.urlopen(r, timeout=180).read().decode()
            return json.loads(raw) if raw.strip() else None
        except urllib.error.HTTPError as e:
            detail = e.read().decode()[:400]
            if attempt == retries - 1:
                raise RuntimeError(f"{method} {path} -> {e.code}: {detail}")
            time.sleep(2 * (attempt + 1))
        except Exception:
            if attempt == retries - 1:
                raise
            time.sleep(2 * (attempt + 1))


def digits(s):
    return re.sub(r"\D", "", str(s or ""))


def usable_name(s):
    """
    WhatsApp reports a pushName for nearly every chat, but for a contact with no
    display name set it just echoes the id back. Storing that would show the agent
    a 15-digit number twice over, so treat a mostly-numeric name as no name.
    """
    s = (s or "").strip()
    if not s:
        return None
    return s if len(digits(s)) < len(s) * 0.6 else None


def text_of(msg, mtype):
    """Mirror of the webhook's extractText, plus the types only history carries."""
    if not isinstance(msg, dict):
        return ""
    if isinstance(msg.get("conversation"), str):
        return msg["conversation"]
    for k, label in (
        ("extendedTextMessage", None),
        ("imageMessage", "[صورة]"),
        ("videoMessage", "[فيديو]"),
        ("documentMessage", "[ملف]"),
        ("documentWithCaptionMessage", "[ملف]"),
    ):
        v = msg.get(k)
        if isinstance(v, dict):
            return v.get("text") or v.get("caption") or label or ""
    if msg.get("audioMessage"):
        return "[رسالة صوتية]"
    if msg.get("stickerMessage"):
        return "[ملصق]"
    if msg.get("locationMessage"):
        return "[موقع]"
    if msg.get("contactMessage") or msg.get("contactsArrayMessage"):
        return "[جهة اتصال]"
    r = msg.get("reactionMessage")
    if isinstance(r, dict):
        return f"[تفاعل] {r.get('text', '')}".strip()
    p = msg.get("protocolMessage")
    if isinstance(p, dict):
        return "[رسالة محذوفة]"
    return f"[{mtype}]" if mtype else ""


# ---------------------------------------------------------------- pass 1: identity
rows = []
alt_of, name_of, chatname_of = {}, {}, {}
seen_wa = set()
dupe_in_source = 0

with open(SRC, encoding="utf-8", newline="") as fh:
    for raw in csv.DictReader(fh):
        wa_id = (raw.get("wa_id") or "").strip()
        # The Chat join can emit a row twice when a jid has more than one Chat
        # record; collapse on the WhatsApp message id.
        if wa_id:
            if wa_id in seen_wa:
                dupe_in_source += 1
                continue
            seen_wa.add(wa_id)

        try:
            msg = json.loads(raw["msg"]) if raw.get("msg") else None
        except json.JSONDecodeError:
            msg = None

        r = {
            "jid": (raw.get("jid") or "").strip(),
            "alt": (raw.get("alt") or "").strip(),
            "wa_id": wa_id,
            "fromMe": str(raw.get("from_me", "")).lower() == "true",
            "push": (raw.get("push") or "").strip(),
            "ts": raw.get("ts") or 0,
            "type": (raw.get("mtype") or "").strip(),
            "msg": msg,
            "chatname": (raw.get("chatname") or "").strip(),
        }
        rows.append(r)

        jid = r["jid"]
        if r["alt"] and "s.whatsapp.net" in r["alt"]:
            alt_of[jid] = digits(r["alt"])
        # Only an INBOUND pushName is the customer's name — outbound ones are ours
        # (Evolution records the sender as "Você").
        if not r["fromMe"] and r["push"]:
            name_of.setdefault(jid, r["push"])
        if r["chatname"]:
            chatname_of.setdefault(jid, r["chatname"])

print(f"parsed {len(rows)} messages ({dupe_in_source} duplicate source rows collapsed)")


def identity(jid):
    """(customer_phone, kind) for a chat."""
    if jid in alt_of:
        return alt_of[jid], "phone"
    if jid.endswith("@s.whatsapp.net"):
        return digits(jid), "phone"
    if jid.endswith("@g.us"):
        return digits(jid), "group"
    return digits(jid), "lid"


chats = defaultdict(list)
for r in rows:
    chats[r.get("jid") or ""].append(r)

kinds = defaultdict(int)
for jid in chats:
    kinds[identity(jid)[1]] += 1
print("chats by identity:", dict(kinds))

# ---------------------------------------------------------------- pass 2: dedupe
print("loading existing wa_message_ids ...")
existing = set()
off = 0
while True:
    b = req("GET", f"whatsapp_messages?select=wa_message_id&limit=1000&offset={off}") or []
    existing.update(x["wa_message_id"] for x in b if x.get("wa_message_id"))
    if len(b) < 1000:
        break
    off += 1000
print(f"  {len(existing)} already stored")

# ---------------------------------------------------------------- pass 3: build
msg_rows, conv_state = [], {}
skipped_dupe = 0

for jid, items in chats.items():
    phone, kind = identity(jid)
    if not phone:
        continue
    items.sort(key=lambda x: int(x.get("ts") or 0))
    display = usable_name(chatname_of.get(jid) if kind == "group" else name_of.get(jid))

    for r in items:
        wa_id = r.get("wa_id")
        if wa_id and wa_id in existing:
            skipped_dupe += 1
            continue
        body = (text_of(r.get("msg"), r.get("type")) or "").strip() or "(no text)"
        ts = int(r.get("ts") or 0)
        created = time.strftime("%Y-%m-%dT%H:%M:%S+00:00", time.gmtime(ts)) if ts else None
        msg_rows.append({
            "customer_phone": phone,
            "body": body[:8000],
            "direction": "outbound" if r.get("fromMe") else "inbound",
            "status": "sent" if r.get("fromMe") else "received",
            "wa_message_id": wa_id,
            "created_at": created,
            "raw_payload": {
                "source": "evolution-import",
                "instance": INSTANCE,
                "brand": "birdie",
                "jid": jid,
                "jid_kind": kind,
                "pushName": r.get("push"),
            },
        })
        prev = conv_state.get(phone)
        if not prev or (created or "") >= (prev["last_message_at"] or ""):
            conv_state[phone] = {
                "customer_phone": phone,
                "customer_name": display,
                "last_message": body[:2000],
                "last_message_at": created,
                "instance": INSTANCE,
                # A lid chat has no phone and (almost never) a real name, so an
                # agent cannot identify, call, or CRM-match the person. Those go
                # to the archive; only chats with a usable identity land in the
                # working inbox.
                "status": "archived" if kind == "lid" else "open",
                "unread_count": 0,
                "updated_at": created,
            }

print(f"to insert: {len(msg_rows)} messages across {len(conv_state)} conversations "
      f"(skipped {skipped_dupe} already present)")

if not msg_rows and not conv_state:
    print("nothing to do")
    sys.exit(0)

# ---------------------------------------------------------------- pass 4: conversations
print("upserting conversations ...")
have = {}
off = 0
while True:
    b = req("GET", f"whatsapp_conversations?select=id,customer_phone,instance&instance=eq.{INSTANCE}"
                   f"&limit=1000&offset={off}") or []
    for c in b:
        have[c["customer_phone"]] = c["id"]
    if len(b) < 1000:
        break
    off += 1000

new_convs = [c for p, c in conv_state.items() if p not in have]
upd_convs = [c for p, c in conv_state.items() if p in have]
for i in range(0, len(new_convs), BATCH):
    req("POST", "whatsapp_conversations", new_convs[i:i + BATCH], prefer="return=minimal")
    print(f"  inserted {min(i + BATCH, len(new_convs))}/{len(new_convs)}")
for c in upd_convs:
    req("PATCH", f"whatsapp_conversations?customer_phone=eq.{c['customer_phone']}&instance=eq.{INSTANCE}",
        {k: v for k, v in c.items() if k not in ("customer_phone", "instance")}, prefer="return=minimal")
print(f"  conversations: {len(new_convs)} new, {len(upd_convs)} updated")

# ---------------------------------------------------------------- pass 5: messages
print("inserting messages ...")
done = 0
for i in range(0, len(msg_rows), BATCH):
    chunk = msg_rows[i:i + BATCH]
    req("POST", "whatsapp_messages", chunk, prefer="return=minimal")
    done += len(chunk)
    if done % 5000 < BATCH:
        print(f"  {done}/{len(msg_rows)}")
print(f"  inserted {done} messages")
print("DONE")