#!/usr/bin/env python3
"""DAS: silangkan hasil Keyword Planner (CSV) dengan GSC.
Output: volume, persaingan, posisi DAS sekarang, halaman mana yang tampil, dan peluangnya."""
import csv, io, json, os, re
from google.oauth2 import service_account
from googleapiclient.discovery import build

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

# CSV ini UTF-16
with open(CSV, encoding="utf-16") as f:
    baris = list(csv.reader(f, delimiter="\t"))
judul = baris[0][0]
rentang = baris[1][0]
header = baris[2]
data = [dict(zip(header, r)) for r in baris[3:] if r and r[0].strip()]
print(f"{judul} | rentang: {rentang} | {len(data)} kata kunci")

def angka(v):
    v = (v or "").strip().strip('"').replace(".", "").replace(",", ".")
    try:
        return float(v)
    except Exception:
        return None

kp = {}
for d in data:
    kw = d["Keyword"].strip().lower()
    kp[kw] = {"vol": angka(d.get("Avg. monthly searches")),
              "kompetisi": (d.get("Competition") or "").strip(),
              "idx": angka(d.get("Competition (indexed value)")),
              "bid_low": angka(d.get("Top of page bid (low range)")),
              "bid_high": angka(d.get("Top of page bid (high range)")),
              "di_akun": (d.get("In account?") or "").strip()}

print("\n=== 10 volume terbesar ===")
for kw, v in sorted(kp.items(), key=lambda x: -(x[1]["vol"] or 0))[:12]:
    bid = f"bid {int(v['bid_low'])}-{int(v['bid_high'])}" if v["bid_low"] else "bid -"
    print(f"   {int(v['vol'] or 0):>6}/bln | kompetisi {v['kompetisi']:<14} idx {int(v['idx'] or 0):>3} | {bid:<22} | {kw}")

print("\n=== GSC: query nyata DAS (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", [])
gsc_q = {r["keys"][0].lower(): {"imp": r["impressions"], "klik": r["clicks"], "pos": round(r["position"], 1)} for r in rows}
print(f"   {len(gsc_q)} query")

print("\n=== SILANG: keyword Planner vs posisi DAS di Google ===")
hasil = []
for kw, v in kp.items():
    g = gsc_q.get(kw)
    hasil.append({"kw": kw, **v, "gsc_imp": (g or {}).get("imp", 0), "gsc_klik": (g or {}).get("klik", 0),
                  "gsc_pos": (g or {}).get("pos")})
hasil.sort(key=lambda x: -(x["vol"] or 0))
for h in hasil[:28]:
    pos = h["gsc_pos"] if h["gsc_pos"] else "-"
    print(f"   {int(h['vol'] or 0):>6}/bln | idx {int(h['idx'] or 0):>3} | DAS: imp {h['gsc_imp']:>4} klik {h['gsc_klik']:>2} pos {str(pos):>6} | {h['kw'][:50]}")

json.dump(hasil, open(f"{WS}/drafts/das_keyword_planner_silang.json", "w"), indent=1, ensure_ascii=False)
print(f"\n→ drafts/das_keyword_planner_silang.json")
print(f"\nCatatan: file CSV tidak memuat setelan lokasi/bahasa. Volume di atas apa adanya dari ekspor Bos.")