"""Turn data/wiki_rows_raw.csv into data/forecasts.csv: one row per (year, source, issue date)
with numeric lo/hi/point for NS, H, MH. Ranges 'a–b' -> lo=a, hi=b, point=(a+b)/2.
Single values -> lo=hi=point. 'N/A' -> blank."""
import csv, re, datetime
MON = {m: i for i, m in enumerate(['January','February','March','April','May','June','July',
       'August','September','October','November','December'], 1)}
date_re = re.compile(r'^(January|February|March|April|May|June|July|August|September|October|November|December)\s+(\d{1,2}),?\s+(\d{4})$')
def num(c):
    c = c.replace('†', '').replace('*', '').replace('~', '').strip()
    m = re.match(r'^(\d+(?:\.\d+)?)\s*[–\-—]\s*(\d+(?:\.\d+)?)$', c)
    if m: a, b = float(m.group(1)), float(m.group(2)); return a, b, (a + b) / 2, 'range'
    m = re.match(r'^(\d+(?:\.\d+)?)$', c)
    if m: a = float(m.group(1)); return a, a, a, 'point'
    return None
out, skipped = [], []
for r in csv.reader(open('../data/wiki_rows_raw.csv')):
    yr = int(r[0]); cs = r[2:-1]; url = r[-1]
    di = next((i for i, c in enumerate(cs) if date_re.match(c.strip())), None)
    if di is None:
        if any(re.search(r'\d', c) for c in cs) and not any(k in ' '.join(cs) for k in ('Average', 'Record', 'Actual')):
            skipped.append((yr, cs))
        continue
    m = date_re.match(cs[di].strip()); d = datetime.date(int(m.group(3)), MON[m.group(1)], int(m.group(2)))
    src = cs[di - 1] if di > 0 else ''
    vals = [num(c) for c in cs[di + 1: di + 4]]
    row = dict(year=yr, source=src, issued=d.isoformat(), raw='|'.join(cs[di + 1: di + 4]), url=url,
               note='june-nov only' if '*' in src else '')
    for k, v in zip(('ns', 'h', 'mh'), vals + [None] * (3 - len(vals))):
        row[k + '_lo'], row[k + '_hi'], row[k + '_pt'] = (v[0], v[1], v[2]) if v else ('', '', '')
        row[k + '_kind'] = v[3] if v else ''
    out.append(row)
keys = list(out[0].keys())
with open('../data/forecasts.csv', 'w', newline='') as fh:
    w = csv.DictWriter(fh, keys); w.writeheader(); w.writerows(out)
print(len(out), 'forecast rows;', len(skipped), 'skipped rows with digits')
for s in skipped: print('SKIP', s)
import collections
c = collections.Counter(re.sub(r'[*†]', '', r['source']).strip() for r in out)
print(c.most_common())