"""Reload backtest_results + forecasts from CSVs with per-channel columns."""
import sys
sys.stdout.reconfigure(encoding="utf-8")
import psycopg2
from backend.config import settings
from db.migrate_remaining import _load_backtest, _load_forecast_log, _load_lookup_maps
from sqlalchemy import text
from backend.models.database import SessionLocal

# Loader functions need a psycopg2 conn directly
def parse_url(url):
    # Simple parse for postgresql://user:pass@host:port/db
    from urllib.parse import urlparse
    u = urlparse(url)
    return dict(host=u.hostname, port=u.port or 5432,
                user=u.username, password=u.password, dbname=u.path.lstrip('/'))

conn = psycopg2.connect(**parse_url(settings.DATABASE_URL))
conn.autocommit = False
try:
    maps = _load_lookup_maps(conn)
    print("Reloading backtest_results from data/backtest_fa.csv ...")
    n_bt = _load_backtest(conn, maps)
    print(f"  {n_bt:,} rows")
    print()
    print("Reloading forecasts from data/forecast_log.csv ...")
    n_fc = _load_forecast_log(conn, maps)
    print(f"  {n_fc:,} rows")
    conn.commit()
    print()
    print("OK reload complete")
finally:
    conn.close()

# Verify
db = SessionLocal()
print()
print("=== Verify per-channel populated ===")
r = db.execute(text("""
    SELECT
        COUNT(*)                          AS total,
        COUNT(forecast_retail)            AS n_fc_retail,
        COUNT(forecast_wholesale)         AS n_fc_ws,
        COUNT(actual_retail)              AS n_act_retail,
        COUNT(actual_wholesale)           AS n_act_ws,
        COUNT(ws_share)                   AS n_ws_share
    FROM backtest_results
""")).mappings().first()
print(f"  backtest_results: {dict(r)}")

r = db.execute(text("""
    SELECT COUNT(*) total, COUNT(forecast_retail) n_fc_retail,
           COUNT(forecast_wholesale) n_fc_ws
    FROM forecasts
""")).mappings().first()
print(f"  forecasts:        {dict(r)}")

# Sample
print()
print("=== Sample backtest_results with per-channel ===")
for r in db.execute(text("""
    SELECT p.sku, b.year, b.week, b.forecast, b.actual,
           b.forecast_retail, b.forecast_wholesale,
           b.actual_retail, b.actual_wholesale, b.ws_share
    FROM backtest_results b JOIN dim_products p ON p.id = b.product_id
    WHERE p.sku IN ('POL09734', 'POL12848') AND b.year = 2026 AND b.week BETWEEN 14 AND 19
    ORDER BY p.sku, b.year, b.week LIMIT 10
""")).mappings():
    print(f"  {r['sku']}  W{r['week']}  fc={r['forecast']}/act={r['actual']}  retail={r['forecast_retail']}/{r['actual_retail']}  ws={r['forecast_wholesale']}/{r['actual_wholesale']}  ws_share={r['ws_share']}")
db.close()