#!/usr/bin/env python3
"""PX3 Lab - Leitura de metricas Google Ads (READ ONLY). Acao 1."""
import json
from google.ads.googleads.client import GoogleAdsClient

TOK = json.load(open('/opt/mia/config/google_ads_tokens.json'))
CUSTOMER_ID = "2186917336"

cfg = {
    "developer_token": TOK["developer_token"],
    "client_id": TOK["client_id"],
    "client_secret": TOK["client_secret"],
    "refresh_token": TOK["refresh_token"],
    "login_customer_id": TOK["mcc_id"],
    "use_proto_plus": True,
}
client = GoogleAdsClient.load_from_dict(cfg)
ga = client.get_service("GoogleAdsService")

def q(query):
    return ga.search(customer_id=CUSTOMER_ID, query=query)

def micros(v): return (v or 0) / 1_000_000

print("="*70)
print("SEARSH CLOUD (23567395804) - por dia, ultimos 7 dias")
print("="*70)
rows = q("""
SELECT segments.date, metrics.impressions, metrics.clicks, metrics.ctr,
       metrics.average_cpc, metrics.cost_micros, metrics.conversions,
       metrics.all_conversions
FROM campaign
WHERE campaign.id = 23567395804
  AND segments.date DURING LAST_7_DAYS
ORDER BY segments.date
""")
print(f"{'data':<12}{'impr':>7}{'cliq':>6}{'ctr%':>7}{'cpc':>8}{'custo':>9}{'conv':>7}{'allconv':>9}")
for r in rows:
    m = r.metrics
    print(f"{r.segments.date:<12}{m.impressions:>7}{m.clicks:>6}{m.ctr*100:>7.2f}{micros(m.average_cpc):>8.2f}{micros(m.cost_micros):>9.2f}{m.conversions:>7.1f}{m.all_conversions:>9.1f}")

print()
print("="*70)
print("SEARSH CLOUD - agregado (7d e 2d)")
print("="*70)
for period in ["LAST_7_DAYS", "TODAY", "YESTERDAY"]:
    rows = q(f"""
    SELECT metrics.impressions, metrics.clicks, metrics.ctr, metrics.average_cpc,
           metrics.cost_micros, metrics.conversions, metrics.all_conversions,
           metrics.cost_per_conversion,
           metrics.search_impression_share,
           metrics.search_budget_lost_impression_share,
           metrics.search_rank_lost_impression_share
    FROM campaign
    WHERE campaign.id = 23567395804 AND segments.date DURING {period}
    """)
    for r in rows:
        m = r.metrics
        print(f"[{period}] impr={m.impressions} cliq={m.clicks} ctr={m.ctr*100:.2f}% "
              f"cpc=R${micros(m.average_cpc):.2f} custo=R${micros(m.cost_micros):.2f} "
              f"conv={m.conversions:.1f} allconv={m.all_conversions:.1f} "
              f"cpa=R${micros(m.cost_per_conversion):.2f}")
        print(f"          IS={m.search_impression_share*100:.1f}% "
              f"lost_budget={m.search_budget_lost_impression_share*100:.1f}% "
              f"lost_rank={m.search_rank_lost_impression_share*100:.1f}%")

print()
print("="*70)
print("BIDDING ATUAL da SEARSH CLOUD")
print("="*70)
rows = q("""
SELECT campaign.id, campaign.name, campaign.status,
       campaign.bidding_strategy_type, campaign.maximize_conversions.target_cpa_micros,
       campaign.target_spend.cpc_bid_ceiling_micros,
       campaign_budget.amount_micros
FROM campaign WHERE campaign.id = 23567395804
""")
for r in rows:
    c = r.campaign
    print(f"nome={c.name} status={c.status.name} bidding={c.bidding_strategy_type.name}")
    print(f"target_spend_ceiling=R${micros(c.target_spend.cpc_bid_ceiling_micros):.2f} "
          f"maxconv_tcpa=R${micros(c.maximize_conversions.target_cpa_micros):.2f} "
          f"budget/dia=R${micros(r.campaign_budget.amount_micros):.2f}")

print()
print("="*70)
print("TODAS AS CAMPANHAS - ultimos 2 dias (read only)")
print("="*70)
rows = q("""
SELECT campaign.id, campaign.name, campaign.status,
       metrics.impressions, metrics.clicks, metrics.cost_micros,
       metrics.conversions, metrics.all_conversions
FROM campaign
WHERE segments.date DURING LAST_7_DAYS AND metrics.impressions > 0
ORDER BY metrics.cost_micros DESC
""")
print(f"{'id':<13}{'status':<9}{'impr':>7}{'cliq':>6}{'custo':>9}{'conv':>6}{'allc':>6}  nome")
for r in rows:
    m = r.metrics
    print(f"{r.campaign.id:<13}{r.campaign.status.name:<9}{m.impressions:>7}{m.clicks:>6}"
          f"{micros(m.cost_micros):>9.2f}{m.conversions:>6.1f}{m.all_conversions:>6.1f}  {r.campaign.name[:40]}")

print()
print("="*70)
print("ACOES DE CONVERSAO - Form + WhatsApp (last_conversion_date + count 2d)")
print("="*70)
rows = q("""
SELECT conversion_action.id, conversion_action.name, conversion_action.status,
       conversion_action.type
FROM conversion_action
WHERE conversion_action.name IN ('[Site] Envio formulário','[Site] Contato - Whatsapp','[Site] Contato - WhatsApp')
""")
for r in rows:
    ca = r.conversion_action
    print(f"id={ca.id} name={ca.name} status={ca.status.name} type={ca.type_.name}")

# contagem por conversion_action nos ultimos dias
print()
print("--- Conversoes por acao (LAST_7_DAYS, via segments.conversion_action) ---")
rows = q("""
SELECT segments.conversion_action_name, segments.date,
       metrics.all_conversions, metrics.conversions
FROM campaign
WHERE segments.date DURING LAST_7_DAYS AND metrics.all_conversions > 0
ORDER BY segments.date
""")
any_conv = False
for r in rows:
    any_conv = True
    print(f"{r.segments.date} | {r.segments.conversion_action_name} | "
          f"conv={r.metrics.conversions:.1f} allconv={r.metrics.all_conversions:.1f}")
if not any_conv:
    print("NENHUMA conversao registrada em LAST_7_DAYS (via campaign segments).")

print()
print("--- conversion_action com stats (LAST_14_DAYS) ---")
rows = q("""
SELECT conversion_action.name, segments.date, metrics.all_conversions
FROM conversion_action
WHERE segments.date DURING LAST_14_DAYS
  AND conversion_action.name LIKE '%Site%'
ORDER BY segments.date
""")
found = False
for r in rows:
    if r.metrics.all_conversions and r.metrics.all_conversions > 0:
        found = True
        print(f"{r.segments.date} | {r.conversion_action.name} | allconv={r.metrics.all_conversions:.1f}")
if not found:
    print("Nenhuma linha >0 em conversion_action LAST_14_DAYS.")