#!/usr/bin/env python3
"""THE SUCCESSION — when a charity is revoked, does its funder list move, together, to one heir?

Reads only the CRA annual "List of charities" releases (open.canada.ca, 2000–2024) and writes
measure.json, the single source for every figure on /succession/.

    python3 measure.py --cra <dir of YYYY_{donees,ident,fin,s2det,s2res,dir}.csv> \
                       --db /tmp/succession.sqlite --out ../measure.json --extracts ../data

The test (rule fixed before the named cases were looked up, applied to every charity in Canada):

  edge      donor -> donee in a gift-year, >= $5,000, read from the DONOR's T1236 rows
            (gift-year = the CRA release year of the donor's return)
  collapse  donee X, year c: the larger of its two prior years from charities >= $1,000,000,
            and years c and c+1 together <= half of that; first year of a collapse only
  funders   F = donors with an edge to X in c-2 or c-1;  |F| >= 10
  adopter   a member of F with an edge to some Y in c or c+1 and no edge to Y in c-4..c-1
  score     share(Y) = adopters of Y / |F|.  The event's heir is the Y with the most adopters.

Release 2011 has no qualified-donee file. Any event whose window touches 2011 is dropped, not
filled: a missing year reads as a collapse, and a donor's 2011 gift would read as "new" in 2012.
"""
import argparse, csv, glob, json, os, re, sqlite3, statistics
from collections import defaultdict

csv.field_size_limit(10**9)
AP = argparse.ArgumentParser()
AP.add_argument('--cra', required=True)
AP.add_argument('--db', required=True)
AP.add_argument('--out', required=True)
AP.add_argument('--extracts', required=True)
A = AP.parse_args()

GOM, BO, MA = '891283145', '118807080', '118974294'
NAMED = {GOM: 'Gates of Mercy', BO: 'Beth Oloth Charitable Organization', MA: 'Moral Arc Foundation'}
REVOKED = {BO: '2019-01-12', GOM: '2019-04-13'}           # Canada Gazette Part I, effective on publication
MIN, FLOOR, DROP, MINF = 5000, 1_000_000, 0.5, 10
MISSING = {2011}


def bn9(s):
    d = re.sub(r'\D', '', s or '')
    return d[:9] if len(d) >= 8 else ''


def money(v):
    try: return float((v or '').replace(',', '').replace('$', '').strip())
    except ValueError: return None


def col(h, *names):
    low = [x.strip().lower() for x in h]
    for n in names:
        if n.lower() in low: return low.index(n.lower())
    for n in names:
        for i, x in enumerate(low):
            if n.lower() in x: return i


# ------------------------------------------------------------------ load
def load():
    db = sqlite3.connect(A.db)
    db.executescript('''DROP TABLE IF EXISTS gifts; DROP TABLE IF EXISTS ident;
      CREATE TABLE gifts(rel INT, fpe TEXT, donor TEXT, donee TEXT, amount REAL);
      CREATE TABLE ident(rel INT, bn TEXT, designation TEXT, legal TEXT, city TEXT);''')
    for p in sorted(glob.glob(os.path.join(A.cra, '*_donees.csv'))):
        rel = int(os.path.basename(p)[:4])
        with open(p, newline='', encoding='utf-8-sig', errors='replace') as fh:
            r = csv.reader(fh); h = next(r)
            ib, ifp, idb, ia = col(h, 'BN'), col(h, 'FPE'), col(h, 'Donee BN'), col(h, 'Total Gifts')
            db.executemany('INSERT INTO gifts VALUES (?,?,?,?,?)',
                ((rel, x[ifp], bn9(x[ib]), bn9(x[idb]), money(x[ia]) or 0.0) for x in r if len(x) > max(ib, idb, ia)))
    for p in sorted(glob.glob(os.path.join(A.cra, '*_ident.csv'))):
        rel = int(os.path.basename(p)[:4])
        with open(p, newline='', encoding='utf-8-sig', errors='replace') as fh:
            r = csv.reader(fh); h = next(r)
            ix = [col(h, n) for n in ('BN', 'Designation', 'Legal Name', 'City')]
            db.executemany('INSERT INTO ident VALUES (?,?,?,?,?)',
                ((rel, bn9(x[ix[0]]), x[ix[1]], x[ix[2]], x[ix[3]]) for x in r if len(x) > max(ix)))
    db.executescript('CREATE INDEX g1 ON gifts(donee, rel); CREATE INDEX g2 ON gifts(donor, rel); CREATE INDEX i1 ON ident(bn, rel);')
    db.commit()
    return db


db = sqlite3.connect(A.db) if os.path.exists(A.db) else load()
releases = sorted({r for (r,) in db.execute('SELECT DISTINCT rel FROM gifts')})
years = list(range(releases[0], releases[-1] + 1))   # financial files exist for every year, gift files do not
name = {b: n for b, n in db.execute('SELECT bn, legal FROM ident ORDER BY rel')}
desig = {(b, r): d for r, b, d in db.execute('SELECT rel, bn, designation FROM ident')}

E = defaultdict(float)
for d, x, y, a in db.execute("SELECT donor, donee, rel, sum(amount) FROM gifts WHERE donee<>'' AND donor<>donee GROUP BY 1,2,3"):
    E[(d, x, y)] = a
inc = defaultdict(float)
out_by = defaultdict(lambda: defaultdict(set))
into = defaultdict(lambda: defaultdict(set))
for (d, x, y), a in E.items():
    inc[(x, y)] += a
    if a >= MIN:
        out_by[d][y].add(x); into[x][y].add(d)


# ------------------------------------------------------------------ the collapse test
def collapses():
    ev = []
    for x in list(into):
        last = None
        for c in range(2002, 2024):
            if set(range(c - 4, c + 2)) & MISSING: continue      # collapse window and the 'new funder' look-back
            peak = max(inc.get((x, c - 1), 0), inc.get((x, c - 2), 0))
            if peak < FLOOR or inc.get((x, c), 0) + inc.get((x, c + 1), 0) > DROP * peak: continue
            if last == c - 1: continue
            last = c
            F = into[x][c - 1] | into[x][c - 2]
            if len(F) < MINF: continue
            per = defaultdict(set)
            for d in F:
                prior = set().union(*[out_by[d][y] for y in range(c - 4, c)])
                for y in (c, c + 1):
                    for t in out_by[d][y]:
                        if t != x and t not in prior: per[t].add(d)
            ranked = sorted(per.items(), key=lambda kv: (-len(kv[1]), kv[0]))
            top = ranked[0] if ranked else (None, set())
            run = ranked[1] if len(ranked) > 1 else (None, set())
            ev.append(dict(x=x, c=c, peak=round(peak), funders=len(F), heir=top[0], adopters=len(top[1]),
                           share=round(len(top[1]) / len(F), 4), runner=run[0], runner_adopters=len(run[1]),
                           heir_dollars_from_funders=round(sum(E.get((d, top[0], y), 0) for d in F for y in (c, c + 1))) if top[0] else 0))
    ev.sort(key=lambda e: (-e['share'], -e['funders']))
    return ev


EV = collapses()
named_ev = next(e for e in EV if e['x'] == BO and e['c'] == 2019)
tiers = {}
for k in (10, 30, 50, 100):
    sub = [e for e in EV if e['funders'] >= k]
    tiers[k] = dict(events=len(sub), rank=1 + next(i for i, e in enumerate(sub) if e is named_ev),
                    median_share=round(statistics.median(e['share'] for e in sub), 4),
                    p90_share=round(sorted(e['share'] for e in sub)[int(.9 * len(sub))], 4),
                    second=dict(share=next(e for e in sub if e is not named_ev)['share']))
by_adopters = sorted(EV, key=lambda e: -e['adopters'])
next_largest_move = next(e for e in by_adopters if e is not named_ev)
ma_heir_elsewhere = [dict(x=name.get(e['x'], ''), c=e['c'], adopters=e['adopters']) for e in EV if e['heir'] == MA and e is not named_ev]
ratios = sorted(round(e['adopters'] / max(1, e['runner_adopters']), 2) for e in EV if e['funders'] >= 50)

# The common-cause control: one destination absorbing many unrelated collapses in one year.
heir_years = defaultdict(list)
for e in EV:
    if e['heir']: heir_years[(e['heir'], e['c'])].append(e)
common = max(heir_years.items(), key=lambda kv: len(kv[1]))
common_cause = dict(heir=name.get(common[0][0], ''), year=common[0][1], events=len(common[1]),
                    collapsed_charities=sorted({name.get(e['x'], '') for e in common[1]})[:6],
                    largest_single_move=max(e['adopters'] for e in common[1]))

# ------------------------------------------------------------------ the first handoff, which cannot be ranked
def funders_of(x, ys, m=MIN): return {d for (d, xx, y), a in E.items() if xx == x and y in ys and a >= m}
def ever(x, ys): return {d for (d, xx, y), a in E.items() if xx == x and y in ys and a > 0}
g_f = funders_of(GOM, {2008, 2009, 2010})
b_new = funders_of(BO, {2012, 2013}) - ever(BO, set(range(2000, 2011)))
ov1 = g_f & b_new
first = dict(gom_funders_2008_10=len(g_f), bo_new_funders_2012_13=len(b_new), overlap=len(ov1),
             overlap_dollars=round(sum(E.get((d, BO, y), 0) for d in ov1 for y in (2012, 2013))),
             bo_new_dollars=round(sum(E.get((d, BO, y), 0) for d in b_new for y in (2012, 2013))))
bo_f = funders_of(BO, {2017, 2018})
m_new = funders_of(MA, {2019, 2020}) - ever(MA, set(range(2000, 2019)))
ov2 = bo_f & m_new
second = dict(bo_funders_2017_18=len(bo_f), ma_new_funders_2019_20=len(m_new), overlap=len(ov2),
              overlap_dollars=round(sum(E.get((d, MA, y), 0) for d in ov2 for y in (2019, 2020))),
              ma_new_dollars=round(sum(E.get((d, MA, y), 0) for d in m_new for y in (2019, 2020))))
all_three = len(g_f & funders_of(BO, set(range(2012, 2019))) & funders_of(MA, set(range(2019, 2025))))

# ------------------------------------------------------------------ incoming gifts, year by year, from donors' returns
incoming = {NAMED[b]: {y: dict(donors=len({d for (d, xx, yy), a in E.items() if xx == b and yy == y and a > 0}),
                               dollars=round(inc.get((b, y), 0))) for y in releases if inc.get((b, y), 0) > 0}
            for b in NAMED}

# ------------------------------------------------------------------ the three charities paid each other (their own T1236 rows)
transfers = [dict(giver=NAMED[d], receiver=NAMED[x], year=y, dollars=round(a))
             for (d, x, y), a in sorted(E.items(), key=lambda kv: (kv[0][2], kv[0][0])) if d in NAMED and x in NAMED and a > 0]

# ------------------------------------------------------------------ gifts reported to a revoked charity
after = {}
for b, rev in REVOKED.items():
    y1 = str(int(rev[:4]) + 1) + rev[4:]
    rows = db.execute('SELECT fpe, donor, sum(amount) FROM gifts WHERE donee=? AND rel>=2018 GROUP BY 1,2', (b,)).fetchall()
    whole = [(f, d, a) for f, d, a in rows if f >= y1 and a > 0]
    strad = [(f, d, a) for f, d, a in rows if rev < f < y1 and a > 0]
    after[NAMED[b]] = dict(revoked=rev,
        wholly_after=dict(returns=len(whole), donors=len({d for _, d, _ in whole}), dollars=round(sum(a for *_, a in whole)),
                          last_fpe=max((f for f, *_ in whole), default=None)),
        straddling=dict(returns=len(strad), donors=len({d for _, d, _ in strad}), dollars=round(sum(a for *_, a in strad))))

# ------------------------------------------------------------------ T3010 financials of the three, as filed
LINES = ['4500', '4510', '4700', '5000', '5010', '5100', '4200', '4350']
fin = {b: {} for b in NAMED}
for y in years:
    for stem, cols in (('fin', LINES), ('s2det', ['200'])):
        p = os.path.join(A.cra, f'{y}_{stem}.csv')
        if not os.path.exists(p): continue
        with open(p, newline='', encoding='utf-8-sig', errors='replace') as fh:
            for r in csv.DictReader(fh):
                b = bn9(r['BN'])
                if b in fin:
                    d = fin[b].setdefault(y, {'fpe': r['FPE']})
                    for c in cols: d[c] = money(r.get(c))
    p = os.path.join(A.cra, f'{y}_s2res.csv')
    if os.path.exists(p):
        with open(p, newline='', encoding='utf-8-sig', errors='replace') as fh:
            for r in csv.DictReader(fh):
                b = bn9(r['BN'])
                if b in fin:
                    d = fin[b].setdefault(y, {'fpe': r['FPE']})
                    d['intermediary_rows'] = d.get('intermediary_rows', 0) + 1
                    cc = (r.get('Country') or '').strip() or '—'
                    d.setdefault('intermediary_by_country', {})
                    d['intermediary_by_country'][cc] = d['intermediary_by_country'].get(cc, 0) + (money(r.get('Amount')) or 0)
dormant = sorted({round(d['4200']) for y, d in fin[MA].items() if y < 2019 and d.get('4200')})
fin = {b: {y: d for y, d in v.items() if d.get('4700') is not None} for b, v in fin.items()}
abroad = {NAMED[b]: round(sum(d.get('200') or 0 for d in v.values())) for b, v in fin.items()}
ma = fin[MA]
heir = dict(abroad_2019_24=round(sum(ma[y]['200'] or 0 for y in range(2019, 2025))),
            revenue_2019_24=round(sum(ma[y]['4700'] or 0 for y in range(2019, 2025))),
            revenue_by_year={y: round(ma[y]['4700']) for y in range(2019, 2025)},
            abroad_by_year={y: round(ma[y]['200']) for y in range(2019, 2025)},
            admin_share_by_year={y: round(ma[y]['5010'] / ma[y]['5100'] * 100, 2) for y in range(2019, 2025) if ma[y].get('5010')},
            intermediaries_2024=ma[2024].get('intermediary_rows'),
            country_share_2024={k: round(v / sum(ma[2024]['intermediary_by_country'].values()) * 100, 1)
                                for k, v in sorted(ma[2024]['intermediary_by_country'].items(), key=lambda kv: -kv[1])[:3]},
            names_on_file={r: n for r, n in db.execute('SELECT rel, legal FROM ident WHERE bn=? ORDER BY rel', (MA,))},
            dormant_assets=dormant)
last_return = {NAMED[b]: dict(year=max(v), fpe=v[max(v)]['fpe'], assets=v[max(v)].get('4200'), liabilities=v[max(v)].get('4350'))
               for b, v in fin.items() if b in REVOKED}

# ------------------------------------------------------------------ boards
board = defaultdict(lambda: defaultdict(set))
arm = defaultdict(list)
for p in sorted(glob.glob(os.path.join(A.cra, '*_dir.csv'))):
    y = int(os.path.basename(p)[:4])
    with open(p, newline='', encoding='utf-8-sig', errors='replace') as fh:
        for r in csv.DictReader(fh):
            b = bn9(r.get('BN', ''))
            if b not in NAMED: continue
            nm = r.get('Full Name') or f"{r.get('First Name', '')} {r.get('Last Name', '')}"
            board[' '.join(sorted(re.sub(r'[^A-Z ]', '', nm.upper()).split()))][b].add(y)
            if "At Arm's Length" in r: arm[(b, y)].append((r["At Arm's Length"] or '').strip())
shared = [{NAMED[b]: [min(v), max(v)] for b, v in d.items()} for d in board.values() if len(d) >= 2]
on_all_three = [s for s in shared if len(s) == 3]
ma_board_overlap_from_2018 = sum(1 for d in board.values() if MA in d and any(y >= 2018 for y in d[MA]) and (BO in d or GOM in d))
arm_flags = dict(first_year_asked=min(y for (_, y) in arm),
                 director_years=sum(len(v) for v in arm.values()),
                 flagged_Y=sum(v.count('Y') for v in arm.values()),
                 flagged_N=[dict(charity=NAMED[b], year=y, count=v.count('N')) for (b, y), v in sorted(arm.items()) if 'N' in v],
                 blank=sum(v.count('') for v in arm.values()))

# ------------------------------------------------------------------ the paper's tables, cell by cell
PAPER = json.load(open(os.path.join(os.path.dirname(os.path.abspath(__file__)), 'paper_tables.json')))
recon = []
for b, rows in PAPER.items():
    for y, p in rows.items():
        y = int(y); f = fin[b].get(y, {})
        for k, line in (('revenue', '4700'), ('expenditures', '5100'), ('abroad', '200')):
            if p.get(k) is None or f.get(line) is None: continue
            recon.append(dict(charity=NAMED[b], year=y, cell=k, paper=p[k], cra=round(f[line]), line=line, delta=round(f[line] - p[k])))
recon_summary = dict(cells=len(recon), exact=sum(1 for r in recon if r['delta'] == 0), mismatches=[r for r in recon if r['delta'] != 0])

# ------------------------------------------------------------------ the dropped mirror test (see build/README.md)
rises = json.load(open(os.path.join(os.path.dirname(os.path.abspath(__file__)), 'rise_test.json')))

M = dict(
    generated_from='CRA List of charities annual releases ' + f'{releases[0]}–{releases[-1]}',
    missing_releases=sorted(set(range(releases[0], releases[-1] + 1)) - set(releases)),
    rule=__doc__.split('The test')[1].strip(),
    dormant_note='Moral Arc (then Jewish Heritage Foundation of Canada) filed total assets of the values in heir.dormant_assets for the years before 2019',
    params=dict(min_gift=MIN, floor=FLOOR, drop=DROP, min_funders=MINF),
    named={b: n for b, n in NAMED.items()}, revoked=REVOKED,
    test=dict(events=len(EV), tiers=tiers, named=dict(funders=named_ev['funders'], adopters=named_ev['adopters'],
              share=named_ev['share'], runner_adopters=named_ev['runner_adopters'], heir=name.get(named_ev['heir']),
              runner=name.get(named_ev['runner']), heir_dollars_from_funders=named_ev['heir_dollars_from_funders']),
              next_largest_move=dict(adopters=next_largest_move['adopters'], funders=next_largest_move['funders'], year=next_largest_move['c']),
              heir_elsewhere=ma_heir_elsewhere,
              heir_to_runner_ratio=round(named_ev['adopters'] / named_ev['runner_adopters'], 1),
              median_ratio_50plus=statistics.median(ratios), common_cause=common_cause),
    first_handoff=first, second_handoff=second, funded_all_three=all_three,
    incoming=incoming, transfers=transfers, after_revocation=after,
    abroad=abroad, abroad_total=sum(abroad.values()), abroad_revoked=abroad[NAMED[GOM]] + abroad[NAMED[BO]],
    heir=heir, last_return=last_return,
    boards=dict(shared_people=len(shared), on_all_three=on_all_three, ma_overlap_from_2018=ma_board_overlap_from_2018, arm_flags=arm_flags),
    reconciliation=recon_summary, mirror_test=rises,
)
json.dump(M, open(A.out, 'w'), indent=1, default=str)

os.makedirs(A.extracts, exist_ok=True)
with open(os.path.join(A.extracts, 'collapse-events.csv'), 'w', newline='') as fh:
    w = csv.writer(fh)
    w.writerow(['rank_by_share', 'collapse_year', 'funders', 'adopters_of_heir', 'share', 'adopters_of_runner_up', 'named_case'])
    for i, e in enumerate(EV, 1):
        w.writerow([i, e['c'], e['funders'], e['adopters'], e['share'], e['runner_adopters'],
                    f"{NAMED[e['x']]} -> {NAMED.get(e['heir'], name.get(e['heir'], ''))}" if e['x'] in NAMED else ''])
with open(os.path.join(A.extracts, 'three-charities.csv'), 'w', newline='') as fh:
    w = csv.writer(fh)
    w.writerow(['charity', 'bn', 'release', 'fpe', 'gifts_from_charities_donor_reported', 'donor_count', *[f'line_{l}' for l in LINES], 'line_200'])
    for b in NAMED:
        for y in years:
            f = fin[b].get(y, {}); i = incoming[NAMED[b]].get(y, {})
            if not f and not i: continue
            w.writerow([NAMED[b], b + 'RR0001', y, f.get('fpe', ''), i.get('dollars', ''), i.get('donors', ''),
                        *[('' if f.get(l) is None else round(f[l])) for l in LINES], '' if f.get('200') is None else round(f['200'])])
print(json.dumps(dict(events=len(EV), tiers=tiers, named=M['test']['named'], next=M['test']['next_largest_move'],
                      common=common_cause, first=first, second=second, all_three=all_three, recon=dict(cells=recon_summary['cells'], exact=recon_summary['exact'], bad=recon_summary['mismatches']),
                      abroad=abroad, heir=dict((k, heir[k]) for k in ('abroad_2019_24', 'revenue_2019_24', 'intermediaries_2024', 'country_share_2024', 'dormant_assets')),
                      boards=M['boards'], after=after, last=last_return), indent=1, default=str))