#!/usr/bin/env python3
"""
Sync coordinates from the SQLite DB into analysis_output.csv and enriched_data.json.
Use this to backfill lat/lon when CSV/JSON are missing coords but DB already has them.
"""
import json
from pathlib import Path
from typing import Dict, Tuple

import pandas as pd
from db_manager import DatabaseManager

ANALYSIS_CSV = Path("analysis_output.csv")
ENRICHED_JSON = Path("enriched_data.json")


def load_db_coords() -> Dict[str, Tuple[float, float]]:
    db = DatabaseManager()
    props = db.get_all_properties(status=None)
    mapping = {}
    for p in props:
        lat = p.get("lat")
        lon = p.get("lon")
        url = p.get("url")
        if url and lat is not None and lon is not None:
            mapping[url] = (lat, lon)
    return mapping


def sync_csv(mapping: Dict[str, Tuple[float, float]]) -> int:
    if not ANALYSIS_CSV.exists():
        print(f"❌ {ANALYSIS_CSV} not found.")
        return 0
    df = pd.read_csv(ANALYSIS_CSV)
    # Ensure columns exist
    for col in ["Latitude", "Longitude"]:
        if col not in df.columns:
            df[col] = None
    updated = 0
    for idx, row in df.iterrows():
        url = row.get("URL")
        if not url:
            continue
        if pd.notna(row.get("Latitude")) and pd.notna(row.get("Longitude")):
            continue
        if url in mapping:
            lat, lon = mapping[url]
            df.at[idx, "Latitude"] = lat
            df.at[idx, "Longitude"] = lon
            updated += 1
    df.to_csv(ANALYSIS_CSV, index=False, encoding="utf-8")
    return updated


def sync_json(mapping: Dict[str, Tuple[float, float]]) -> int:
    if not ENRICHED_JSON.exists():
        print(f"❌ {ENRICHED_JSON} not found.")
        return 0
    data = json.loads(ENRICHED_JSON.read_text(encoding="utf-8"))
    updated = 0
    for prop in data:
        url = prop.get("url")
        if not url:
            continue
        if prop.get("lat") is not None and prop.get("lon") is not None:
            continue
        if url in mapping:
            lat, lon = mapping[url]
            prop["lat"] = lat
            prop["lon"] = lon
            updated += 1
    ENRICHED_JSON.write_text(json.dumps(data, ensure_ascii=False, indent=2), encoding="utf-8")
    return updated


def main():
    mapping = load_db_coords()
    print(f"📥 Loaded {len(mapping)} URL→coords from DB")
    csv_updated = sync_csv(mapping)
    json_updated = sync_json(mapping)
    print(f"✅ Backfilled {csv_updated} rows in analysis_output.csv")
    print(f"✅ Backfilled {json_updated} entries in enriched_data.json")
    print("ℹ️  Re-run risk_features.py and quality_gate.py after this.")


if __name__ == "__main__":
    main()