# Hermes Football Autopilot V1 — Python 3.8 compatible
import os, sqlite3, json, re, logging
from datetime import datetime, timezone
from urllib.parse import urlparse
import httpx, pytz

LOG=logging.getLogger("hermes_football")
TZ=pytz.timezone("America/Tijuana")
DB=os.environ.get("HERMES_FOOTBALL_DB","/var/lib/hermes-nexus/football.db")
LEAGUES={
 "mex.1":("Liga MX",100), "uefa.champions":("Champions League",110),
 "eng.1":("Premier League",90), "esp.1":("LaLiga",90),
 "ita.1":("Serie A",80), "ger.1":("Bundesliga",80),
 "fra.1":("Ligue 1",70), "uefa.europa":("Europa League",75),
 "conmebol.libertadores":("Copa Libertadores",75),
}
URL="https://site.api.espn.com/apis/site/v2/sports/soccer/{}/scoreboard"
def connect():
 os.makedirs(os.path.dirname(DB),exist_ok=True)
 db=sqlite3.connect(DB,timeout=15)
 db.row_factory=sqlite3.Row
 db.execute("""CREATE TABLE IF NOT EXISTS events(
 id TEXT PRIMARY KEY, league TEXT NOT NULL, league_code TEXT NOT NULL,
 home TEXT NOT NULL, away TEXT NOT NULL, logo TEXT NOT NULL,
 starts_utc TEXT NOT NULL, state TEXT NOT NULL, network TEXT NOT NULL,
 source TEXT NOT NULL, local_day TEXT NOT NULL, updated TEXT NOT NULL)""")
 cols={r[1] for r in db.execute("PRAGMA table_info(events)").fetchall()}
 if "away_logo" not in cols:
  db.execute("ALTER TABLE events ADD COLUMN away_logo TEXT NOT NULL DEFAULT ''")
 db.commit()
 return db

def fetch(day, limit=12):
 found=[]; errors=[]
 for code,(league,priority) in LEAGUES.items():
  try:
   r=httpx.get(URL.format(code),params={"dates":day.strftime("%Y%m%d"),"limit":100},timeout=12,
     headers={"User-Agent":"HermesFootball/1.0"})
   r.raise_for_status()
   for ev in r.json().get("events",[]):
    comps=ev.get("competitions") or []
    if not comps: continue
    comp=comps[0]
    sides=comp.get("competitors") or []
    if len(sides)<2: continue
    home=next((x for x in sides if x.get("homeAway")=="home"),sides[0])
    away=next((x for x in sides if x.get("homeAway")=="away"),sides[1])
    def name(x): return x.get("team",{}).get("displayName") or x.get("team",{}).get("name") or ""
    date_raw=ev.get("date","")
    if not date_raw: continue
    dt=datetime.fromisoformat(date_raw.replace("Z","+00:00"))
    if dt.tzinfo is None: dt=dt.replace(tzinfo=timezone.utc)
    dt=dt.astimezone(timezone.utc)
    if dt.astimezone(TZ).date()!=day: continue
    broadcasts=comp.get("broadcasts") or []
    # ESPN broadcaster metadata may not be country-specific: never label it MX/US.
    channels=sorted(set(str(n) for b in broadcasts for n in b.get("names",[]) if n))
    def team_logo(side):
     team=side.get("team",{})
     url=team.get("logo") or ""
     if not url:
      logos=team.get("logos") or []
      url=logos[0].get("href","") if logos else ""
     parsed=urlparse(url)
     return url if parsed.scheme=="https" and parsed.hostname in (
       "a.espncdn.com","a1.espncdn.com","a2.espncdn.com") else ""
    away_logo=team_logo(away)
    logo=team_logo(home)
    if not logo:
     logos=home.get("team",{}).get("logos") or []
     logo=logos[0].get("href","") if logos else ""
    if logo and (urlparse(logo).scheme!="https" or urlparse(logo).hostname not in
      ("a.espncdn.com","a1.espncdn.com","a2.espncdn.com")): logo=""
    eid=str(ev.get("id") or "")
    if not eid.isdigit(): continue
    found.append(dict(id=code+":"+eid,league=league,league_code=code,
      home=name(home),away=name(away),logo=logo,away_logo=away_logo,starts_utc=dt.isoformat(),
      state=ev.get("status",{}).get("type",{}).get("state","pre"),
      network=", ".join(channels) if channels else "Canal por confirmar",
      source="ESPN",local_day=day.isoformat(),priority=priority))
  except Exception as e:
   errors.append(code+": "+type(e).__name__+": "+str(e)[:100])
 found.sort(key=lambda x:(-x["priority"],x["starts_utc"],x["id"]))
 return found[:limit],errors

def run(publish=False,day=None):
 day=day or datetime.now(TZ).date()
 rows,errors=fetch(day)
 if publish and rows:
  db=connect()
  try:
   now=datetime.now(timezone.utc).isoformat()
   db.execute('CREATE TABLE IF NOT EXISTS hermes_football_exclusions (event_id TEXT PRIMARY KEY, deleted_utc TEXT NOT NULL)')
   excluidos={r[0] for r in db.execute('SELECT event_id FROM hermes_football_exclusions')}
   for e in rows:
    if e['id'] in excluidos: continue
    db.execute("""INSERT INTO events
      (id,league,league_code,home,away,logo,away_logo,starts_utc,state,network,source,local_day,updated)
      VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)
      ON CONFLICT(id) DO UPDATE SET
      league=excluded.league, home=excluded.home,away=excluded.away,
      logo=excluded.logo,away_logo=excluded.away_logo,starts_utc=excluded.starts_utc,state=excluded.state,
      network=excluded.network,updated=excluded.updated""",
      tuple(e[k] for k in ("id","league","league_code","home","away","logo","away_logo","starts_utc","state","network","source","local_day"))+(now,))
   db.commit()
  finally: db.close()
 return {"day":day.isoformat(),"published":bool(publish and rows),
         "count":len(rows),"errors":errors,"events":rows}

def list_events(day=None):
 day=day or datetime.now(TZ).date()
 db=connect()
 try:
  rows=db.execute("SELECT * FROM events WHERE local_day=? ORDER BY starts_utc",(day.isoformat(),)).fetchall()
  return [dict(x) for x in rows]
 finally: db.close()

if __name__=="__main__":
 import argparse
 from datetime import date
 p=argparse.ArgumentParser()
 p.add_argument("--publish",action="store_true")
 p.add_argument("--date",help="YYYY-MM-DD, fecha local Mexicali")
 a=p.parse_args()
 result=run(publish=a.publish,day=date.fromisoformat(a.date) if a.date else None)
 print(json.dumps(result,ensure_ascii=False,indent=2))