"""
Daily sync: fetches latest prices from SteelStatista.com API and inserts
only NEW (title, date) pairs into the SQLite database.
Run manually or via Windows Task Scheduler once a day.
"""
import os
import requests
import time
from db import get_conn, init_db

# The data-source token is NEVER hardcoded. Set it via the admin panel
# (stored in app_config) or the ASIANMETAL_TOKEN environment variable.
HEADERS = {
    "lang": "fa", "platform": "web", "accept": "application/json",
    "referer": "https://SteelStatista.com/",
    "user-agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36",
}

BASE = "https://api.SteelStatista.com"

CAT_IDS = [9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,
           31,32,33,34,35,36,37,38,39,40,42,43,44,45,46,47,48,49,50,51,52,53,
           54,55,56,57,65,67,68,70,71,72,74,75,76,77,78,79,80,81,82,83,84,85,
           87,88,89,90,92,93,94,95,96,97,98,99,100,101,102,103,104,105]

CAT_NAMES = {1:"Base Metals",2:"Minor Metals",3:"Ferroalloys",4:"Rare Earths",
             5:"Carbon Steel",6:"Stainless & Special",7:"Steel Raw Materials",8:"Refractories",
             9:"Aluminum",10:"Copper",11:"Lead",12:"Nickel",13:"Tin",14:"Zinc",
             15:"Antimony",16:"Arsenic",17:"Bismuth",18:"Cadmium",19:"Calcium",
             20:"Chromium",21:"Cobalt",22:"Gallium",23:"Germanium",24:"Indium",
             25:"Lithium",26:"Magnesium",27:"Manganese",28:"Mercury",29:"Molybdenum",
             30:"Niobium",31:"Rhenium",32:"Selenium",33:"Silicon",34:"Strontium",
             35:"Tantalum",36:"Tellurium",37:"Titanium",38:"Tungsten",39:"Vanadium",
             40:"Zirconium",42:"Ferroboron",43:"Ferrochrome",44:"Ferromanganese",
             45:"Ferromolybdenum",46:"Ferronickel",47:"Ferroniobium",48:"Ferrophosphorus",
             49:"Ferrosilicon",50:"Ferrotitanium",51:"Ferrotungsten",52:"Ferrovanadium",
             53:"Silicomanganese",54:"Chromium Silicon",55:"Calcium Silicon",
             56:"Chrome Ore",57:"Manganese Ore",65:"Lutetium",67:"Neodymium",
             68:"Praseodymium",70:"Samarium",71:"Scandium",72:"Terbium",
             74:"Ytterbium",75:"Yttrium",76:"Wire Rod",77:"Rebar",78:"Sections",
             79:"Pipe",80:"Hot Rolled Coil",81:"Cold Rolled Coil",82:"Plate",
             83:"Coated",84:"Strip",85:"Stainless Bar",87:"Stainless Pipe",
             88:"Stainless Scrap",89:"Bearing Steel",90:"Cold Heading Steel",
             92:"Silicon Steel",93:"Structural Steel",94:"Coal",95:"Coke",
             96:"Iron",97:"Iron Ore",98:"Steel Billet",99:"Steel Scrap",
             100:"Calcined Bauxite",101:"Carbon",102:"Fused Alumina",
             103:"Graphite",104:"Magnesia",105:"Silicon Carbide"}

PARENT_MAP = {9:1,10:1,11:1,12:1,13:1,14:1,15:2,16:2,17:2,18:2,19:2,20:2,
              21:2,22:2,23:2,24:2,25:2,26:2,27:2,28:2,29:2,30:2,31:2,32:2,
              33:2,34:2,35:2,36:2,37:2,38:2,39:2,40:2,42:3,43:3,44:3,45:3,
              46:3,47:3,48:3,49:3,50:3,51:3,52:3,53:3,54:3,55:3,56:3,57:3,
              65:4,67:4,68:4,70:4,71:4,72:4,74:4,75:4,76:5,77:5,78:5,79:5,
              80:5,81:5,82:5,83:5,84:5,85:6,87:6,88:6,89:6,90:6,92:6,93:6,
              94:7,95:7,96:7,97:7,98:7,99:7,100:8,101:8,102:8,103:8,104:8,105:8}

def get_latest_dates(conn) -> dict:
    rows = conn.execute("SELECT title, MAX(date) as d FROM prices GROUP BY title").fetchall()
    return {r["title"]: r["d"] for r in rows}

def get_token() -> str:
    """Read the SteelStatista token from app_config (editable via the admin panel);
    fall back to the bundled default if not set."""
    try:
        with get_conn() as conn:
            row = conn.execute(
                "SELECT value FROM app_config WHERE key='asianmetal_token'"
            ).fetchone()
            if row and row["value"]:
                return row["value"]
    except Exception:
        pass
    return os.environ.get("ASIANMETAL_TOKEN", "")


def sync():
    init_db()
    session = requests.Session()
    session.headers.update(HEADERS)
    session.headers["Authorization"] = f"Bearer {get_token()}"

    with get_conn() as conn:
        latest_dates = get_latest_dates(conn)

    print("Collecting symbols...")
    symbols = []
    for cat_id in CAT_IDS:
        try:
            r = session.get(f"{BASE}/Symbol?categoryId={cat_id}", timeout=10)
            for sym in r.json():
                symbols.append({
                    "symbol_id": sym["id"],
                    "grp":       CAT_NAMES.get(PARENT_MAP.get(cat_id, 0), ""),
                    "category":  CAT_NAMES.get(cat_id, str(cat_id)),
                    "title":     sym["title"],
                })
        except Exception as e:
            print(f"  Error cat {cat_id}: {e}")
        time.sleep(0.15)

    print(f"Found {len(symbols)} symbols. Fetching new prices...")

    inserted = 0
    for i, sym in enumerate(symbols):
        title = sym["title"]
        cutoff = latest_dates.get(title)

        try:
            r = session.get(f"{BASE}/Symbol/{sym['symbol_id']}", timeout=10)
            prices = r.json().get("symbolPrices", [])

            rows_to_insert = [
                (sym["grp"], sym["category"], title, p["date"][:10], p["low"], p["mid"], p["high"])
                for p in prices
                if not (cutoff and p["date"][:10] <= cutoff)
            ]

            if rows_to_insert:
                with get_conn() as conn:
                    conn.executemany(
                        "INSERT OR IGNORE INTO prices(grp,category,title,date,low,mid,high) VALUES(?,?,?,?,?,?,?)",
                        rows_to_insert
                    )
                inserted += len(rows_to_insert)

            if (i + 1) % 100 == 0:
                print(f"  {i+1}/{len(symbols)} done — {inserted} new rows so far")

        except Exception as e:
            print(f"  Error symbol {sym['symbol_id']}: {e}")
        time.sleep(0.2)

    print(f"\nSync complete — {inserted} new rows inserted")

if __name__ == "__main__":
    sync()