#!/usr/bin/env python3
"""DAS: analisis dua ekspor Keyword Planner (nasional & Surabaya+Sidoarjo) + silang dengan GSC.
Output: peringkat peluang kata kunci + berkas laporan."""
import csv, json, os, re
from google.oauth2 import service_account
from googleapiclient.discovery import build

NAS = "/home/mantapman/.hermes/cache/documents/doc_71c7e60f5dc7_Keyword Stats 2026-09-29 at 08_34_02.csv"
LOK = "/home/mantapman/.hermes/cache/documents/doc_f4edd9ada499_Keyword Stats 2026-09-29 at 08_35_08.csv"
KEY = os.path.expanduser("~/.hermes/gsc_service_account.json")
WS = "/home/mantapman/.hermes/profiles/atun/workspace"


def baca(path):
    with open(path, encoding="utf-16") as f:
        baris = list(csv.reader(f, delimiter="\t"))
    head = baris[2]
    out = {}
    for r in baris[3:]:
        if not r or not r[0].strip():
            continue
        d = dict(zip(head, r))
        kw = d["Keyword"].strip().lower()

        def num(v):
            v = (v or "").strip().strip('"').replace(".", "").replace(",", ".")
            try:
                return float(v)
            except Exception:
                return 0.0
        out[kw] = {"vol": num(d.get("Avg. monthly searches")), "komp": (d.get("Competition") or "").strip(),
                   "idx": num(d.get("Competition (indexed value)")),
                   "bid_lo": num(d.get("Top of page bid (low range)")),
                   "bid_hi": num(d.get("Top of page bid (high range)")),
                   "di_akun": (d.get("In account?") or "").strip()}
    return out, baris[1][0]


nas, rentang = baca(NAS)
lok, rentang2 = baca(LOK)
print(f"nasional : {len(nas)} keyword | rentang {rentang}")
print(f"lokal    : {len(lok)} keyword | rentang {rentang2}")

print("\n=== 15 volume TERBESAR di Surabaya+Sidoarjo ===")
print(f"{'vol lokal':>9} {'vol nas':>8} {'idx':>4} {'komp':<13} {'di akun':<8} keyword")
for kw, v in sorted(lok.items(), key=lambda x: -x[1]["vol"])[:15]:
    n = nas.get(kw, {})
    print(f"{int(v['vol']):>9} {int(n.get('vol', 0)):>8} {int(v['idx']):>4} {v['komp']:<13} {v['di_akun']:<8} {kw[:52]}")

print("\n=== kata kunci yang punya volume LOKAL tapi TIDAK ada di ekspor nasional ===")
baru = [kw for kw in lok if kw not in nas]
print(f"   {len(baru)}: {baru[:12]}")

print("\n=== silang dengan GSC (90 hari) ===")
gsc = build("searchconsole", "v1", credentials=service_account.Credentials.from_service_account_file(
    KEY, scopes=["https://www.googleapis.com/auth/webmasters.readonly"]), cache_discovery=False)
rows = gsc.searchanalytics().query(siteUrl="https://dasindonesia.com/", body={
    "startDate": "2026-07-01", "endDate": "2026-09-28", "dimensions": ["query"], "rowLimit": 5000}).execute().get("rows", [])
gq = {r["keys"][0].lower(): r for r in rows}
pages = gsc.searchanalytics().query(siteUrl="https://dasindonesia.com/", body={
    "startDate": "2026-07-01", "endDate": "2026-09-28", "dimensions": ["query", "page"], "rowLimit": 5000}).execute().get("rows", [])
peta = {}
for r in pages:
    q = r["keys"][0].lower()
    if q not in peta or r["impressions"] > peta[q]["impressions"]:
        peta[q] = {"page": r["keys"][1].replace("https://dasindonesia.com", ""), "impressions": r["impressions"]}

hasil = []
for kw, v in lok.items():
    g = gq.get(kw)
    h = peta.get(kw, {})
    imp = g["impressions"] if g else 0
    klik = g["clicks"] if g else 0
    pos = round(g["position"], 1) if g else None
    # nilai peluang: volume lokal, diringankan kalau sudah halaman 1, dan ditambah kalau impresi besar tapi klik nol
    nilai = 0
    if v["vol"] > 0:
        nilai += min(v["vol"] / 60, 5)          # sampai 5 poin dari volume
    if v["komp"] == "Rendah":
        nilai += 3
    elif v["komp"] == "Menengah":
        nilai += 1.5
    if pos is None:
        nilai += 3                                # belum muncul sama sekali = peluang konten baru
    elif pos > 10:
        nilai += 2.5                              # di luar halaman 1 = bisa didorong
    elif imp >= 5 and klik == 0:
        nilai += 2                                # halaman 1 tapi tidak diklik = masalah cuplikan
    hasil.append({"kw": kw, "vol_lokal": v["vol"], "vol_nas": nas.get(kw, {}).get("vol", 0),
                  "komp": v["komp"], "idx": v["idx"], "bid_lo": v["bid_lo"], "bid_hi": v["bid_hi"],
                  "di_akun": v["di_akun"], "imp": imp, "klik": klik, "pos": pos,
                  "halaman": h.get("page", ""), "nilai": round(nilai, 1)})
hasil.sort(key=lambda x: -x["nilai"])
print(f"\n{'nilai':>5} {'volSby':>7} {'volNas':>7} {'pos':>6} {'imp':>5} {'klik':>4}  keyword / halaman")
for h in hasil[:25]:
    print(f"{h['nilai']:>5} {int(h['vol_lokal']):>7} {int(h['vol_nas']):>7} {str(h['pos'] or '-'):>6} "
          f"{h['imp']:>5} {h['klik']:>4}  {h['kw'][:44]:<46} {h['halaman'][:34]}")

json.dump(hasil, open(f"{WS}/drafts/das_keyword_planner_lokal.json", "w"), indent=1, ensure_ascii=False)
print(f"\n→ drafts/das_keyword_planner_lokal.json ({len(hasil)} keyword)")