#!/usr/bin/env python3
"""
Gold Allocation Backtest — Step 1: source ingestion and normalisation.

Loads four primary series to a common monthly index and writes normalised CSVs.
No portfolio computation here. See ALLOCATION-BACKTEST-BUILD-PLAN-2026-09-02.md.

Sources (downloaded 2026-09-02, see data/FETCH-LOG.txt and data/SHA256SUMS.txt):
  gold  World Bank Commodity Markets "Pink Sheet", Monthly Prices, Gold ($/troy oz)
        MONTHLY AVERAGE of daily quotations.
  eq    Shiller ie_data.xls 'Data': S&P Composite price (P) and dividend (D).
        P is itself a MONTHLY AVERAGE of daily closes -- aligns with WB gold.
  cpi   Shiller CPI (BLS CPI-U).
  gs10  FRED GS10, 10y Treasury constant maturity, monthly (% p.a.).
"""
import csv, json, os, warnings
from collections import OrderedDict
warnings.filterwarnings('ignore')

D = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'data')
OUT = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'normalised')
os.makedirs(OUT, exist_ok=True)

def month_key(y, m): return '%04d-%02d' % (y, m)

# ---------- gold: World Bank Pink Sheet ----------
def load_gold():
    import openpyxl
    wb = openpyxl.load_workbook(os.path.join(D, 'CMO-Historical-Data-Monthly.xlsx'), data_only=True)
    ws = wb['Monthly Prices']
    hdr_row = gi = None
    for r in range(1, 12):
        row = [ws.cell(r, c).value for c in range(1, 100)]
        hits = [i for i, v in enumerate(row, 1)
                if isinstance(v, str) and v.strip().lower() == 'gold']
        if hits:
            hdr_row, gi = r, hits[0]
            break
    if gi is None:
        raise SystemExit('FATAL: Gold column not found by header match')
    units = ws.cell(hdr_row + 1, gi).value
    out = OrderedDict()
    for r in range(hdr_row + 2, ws.max_row + 1):
        d = ws.cell(r, 1).value
        v = ws.cell(r, gi).value
        if not d or not isinstance(v, (int, float)):
            continue
        s = str(d).strip()
        if 'M' not in s:
            continue
        y, m = s.split('M')
        out[month_key(int(y), int(m))] = float(v)
    return out, {'column_index': gi, 'header_row': hdr_row, 'units': units}

# ---------- Shiller: equity price, dividend, CPI, GS10 ----------
def load_shiller():
    import xlrd
    wb = xlrd.open_workbook(os.path.join(D, 'ie_data.xls'))
    ws = wb.sheet_by_name('Data')
    P, DIV, CPI, GS = OrderedDict(), OrderedDict(), OrderedDict(), OrderedDict()
    for r in range(1, ws.nrows):
        v = ws.cell_value(r, 0)
        if not isinstance(v, float):
            continue
        y = int(v)
        m = int(round((v - y) * 100))          # 1871.1 == October, not January
        if m < 1 or m > 12 or y < 1800:
            continue
        k = month_key(y, m)
        def num(c):
            x = ws.cell_value(r, c)
            return float(x) if isinstance(x, (int, float)) and x != '' else None
        p, d, cpi, gs = num(1), num(2), num(4), num(6)
        if p is not None:   P[k] = p
        if d is not None:   DIV[k] = d
        if cpi is not None: CPI[k] = cpi
        if gs is not None:  GS[k] = gs
    return P, DIV, CPI, GS

# ---------- FRED ----------
def load_fred(fn):
    out = OrderedDict()
    with open(os.path.join(D, fn)) as f:
        for row in list(csv.reader(f))[1:]:
            if len(row) < 2 or row[1] in ('.', ''):
                continue
            y, m, _ = row[0].split('-')
            out[month_key(int(y), int(m))] = float(row[1])
    return out

def span(d): return (min(d), max(d), len(d)) if d else (None, None, 0)

if __name__ == '__main__':
    gold, gmeta = load_gold()
    P, DIV, CPI, GS_sh = load_shiller()
    GS10 = load_fred('GS10.csv')
    CPI_FRED = load_fred('CPIAUCSL.csv')

    print('=' * 74)
    print('  STEP 1 — SOURCE INGESTION AND NORMALISATION')
    print('=' * 74)
    print('%-22s %-9s %-9s %7s' % ('series', 'first', 'last', 'obs'))
    for name, d in [('gold (World Bank)', gold), ('equity price (Shiller)', P),
                    ('dividend (Shiller)', DIV), ('CPI (Shiller)', CPI),
                    ('GS10 (Shiller col G)', GS_sh), ('GS10 (FRED)', GS10),
                    ('CPIAUCSL (FRED)', CPI_FRED)]:
        a, b, n = span(d)
        print('%-22s %-9s %-9s %7d' % (name, a, b, n))
    print()
    print('gold column located by header match: col %(column_index)d, header row '
          '%(header_row)d, units %(units)s' % gmeta)

    common = sorted(set(gold) & set(P) & set(DIV) & set(CPI) & set(GS10))
    common = [k for k in common if k >= '1968-04']
    print()
    print('COMMON PERIOD (gold + equity + dividend + CPI + FRED GS10, from 1968-04):')
    print('  %s to %s   %d months' % (common[0], common[-1], len(common)))

    # contiguity
    def nxt(k):
        y, m = int(k[:4]), int(k[5:])
        return month_key(y + (m == 12), 1 if m == 12 else m + 1)
    gaps = [(common[i-1], common[i]) for i in range(1, len(common))
            if nxt(common[i-1]) != common[i]]
    print('  contiguity: %s' % ('CONTIGUOUS, no gaps' if not gaps else 'GAPS %s' % gaps[:5]))

    with open(os.path.join(OUT, 'monthly_series.csv'), 'w', newline='') as f:
        w = csv.writer(f)
        w.writerow(['month', 'gold_usd_per_troy_oz_wb_monthly_avg',
                    'sp_price_shiller_monthly_avg', 'sp_dividend_annual_shiller',
                    'cpi_shiller', 'gs10_pct_fred'])
        for k in common:
            w.writerow([k, gold[k], P[k], DIV[k], CPI[k], GS10[k]])
    print('\nwrote normalised/monthly_series.csv (%d rows)' % len(common))

    json.dump({'common_start': common[0], 'common_end': common[-1],
               'months': len(common), 'contiguous': not gaps,
               'gold_meta': {k: str(v) for k, v in gmeta.items()},
               'series_spans': {n: list(span(d)) for n, d in
                                [('gold', gold), ('sp_price', P), ('dividend', DIV),
                                 ('cpi_shiller', CPI), ('gs10_fred', GS10),
                                 ('gs10_shiller', GS_sh), ('cpi_fred', CPI_FRED)]}},
              open(os.path.join(OUT, 'ingest_summary.json'), 'w'), indent=2)