# -*- coding: utf-8 -*-
"""Every table in paper 09, generated. No number is typed into the prose.

    python tables.py

Reads only the aggregates: `crawlers_verified.csv`, `crawlers.csv`,
`referrals.csv` and `verification.csv`. It cannot reach a raw log line and it is
not allowed to, which is why the paper can ship the whole analysis path while the
logs themselves stay off the site.

Writes `../draft/tables/*.md`. Anything that appears in the paper as a figure
comes from here or it does not appear.
"""
import argparse
import collections
import csv
import io
import os
import sys

HERE = os.path.dirname(os.path.abspath(__file__))
OUT = os.path.normpath(os.path.join(HERE, '..', 'draft', 'tables'))

sys.path.insert(0, HERE)
import sitecfg                                                     # noqa: E402

SITE = 'broikos.gr'         # the alias whose aggregates the tables are built from
PREFIX = ''                 # what the written table files are named

MONTHS = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct',
          'Nov', 'Dec']

# operator -> the crawler agents it uses, and the referrer domains it sends
# visitors from. Kept here and in ratio.py deliberately: the two scripts are
# meant to be independently readable, and a shared import would hide the pairing
# that the whole claim rests on.
PAIRS = [
    ('OpenAI', ['GPTBot', 'OAI-SearchBot', 'ChatGPT-User'],
     ['chatgpt.com', 'openai.com']),
    ('Anthropic', ['ClaudeBot', 'Claude-User', 'Claude-SearchBot'], ['claude.ai']),
    ('Perplexity', ['PerplexityBot', 'Perplexity-User'], ['perplexity.ai']),
    ('Google AI', ['Google-Extended'], ['gemini.google.com', 'bard.google.com']),
    ('Microsoft', ['Bingbot'], ['copilot.microsoft.com', 'bing.com/chat']),
    ('Apple', ['Applebot-Extended', 'Applebot'], []),
    ('Meta', ['meta-externalagent'], []),
    ('ByteDance', ['Bytespider'], []),
    ('Common Crawl', ['CCBot'], []),
    ('Amazon', ['Amazonbot'], []),
    ('Google search', ['Googlebot'], []),
    ('You.com', ['YouBot'], ['you.com']),
]


def read(name, site=None):
    path = os.path.join(HERE, 'sites', site or SITE, name)
    if not os.path.exists(path):
        return []
    with io.open(path, encoding='utf-8') as f:
        return list(csv.DictReader(f))


def daykey(d):
    return (d[7:], MONTHS.index(d[3:6]), d[:2])


def write(name, lines):
    name = PREFIX + name
    os.makedirs(OUT, exist_ok=True)
    io.open(os.path.join(OUT, name), 'w', encoding='utf-8').write(
        '\n'.join(lines) + '\n')
    print('  %s' % name)


def t_window(ver, claimed):
    """What was actually read, month by month, so the gaps are visible."""
    days = collections.defaultdict(set)
    req = collections.Counter()
    for r in claimed:
        days[r['day'][3:]].add(r['day'])
        req[r['day'][3:]] += int(r['requests'])
    order = sorted(days, key=lambda m: (m[4:], MONTHS.index(m[:3])))
    out = ['| month | days with traffic | crawler requests |', '|---|---|---|']
    for m in order:
        out.append('| %s | %d | %d |' % (m.replace('/', ' '), len(days[m]),
                                         req[m]))
    out.append('| **whole window** | **%d** | **%d** |'
               % (sum(len(v) for v in days.values()), sum(req.values())))
    return out


def t_verification(vrows):
    out = ['| operator | requests | addresses | verified addresses | '
           'requests verified | how |', '|---|---|---|---|---|---|']
    tot = ver = 0
    for r in sorted(vrows, key=lambda r: -int(r['requests'])):
        n, v = int(r['requests']), int(r['verified_requests'])
        tot += n
        ver += v
        out.append('| `%s` | %d | %d | %d | %d (%.0f%%) | %s |'
                   % (r['operator'], n, int(r['distinct_ips']),
                      int(r['verified_ips']), v, 100.0 * v / n if n else 0,
                      r['method']))
    out.append('| **total** | **%d** | | | **%d (%.1f%%)** | |'
               % (tot, ver, 100.0 * ver / tot if tot else 0))
    return out


def t_load(vrows):
    """What each operator took, counting only addresses that survived the check."""
    by = {r['operator']: r for r in vrows}
    out = ['| operator | agent | pages taken | all requests | data served |',
           '|---|---|---|---|---|']
    bytes_by = collections.Counter()
    for r in read('crawlers_verified.csv'):
        bytes_by[r['operator']] += int(r['bytes'])
    rows = []
    for name, agents, _dom in PAIRS:
        p = sum(int(by[a]['verified_pages']) for a in agents if a in by)
        q = sum(int(by[a]['verified_requests']) for a in agents if a in by)
        b = sum(bytes_by[a] for a in agents)
        if q or p:
            rows.append((p, name, ', '.join('`%s`' % a for a in agents
                                            if a in by), q, b))
    for p, name, agents, q, b in sorted(rows, reverse=True):
        out.append('| %s | %s | **%d** | %d | %.1f MB |'
                   % (name, agents, p, q, b / 1e6))
    out.append('| **total** | | **%d** | **%d** | **%.1f MB** |'
               % (sum(r[0] for r in rows), sum(r[3] for r in rows),
                  sum(r[4] for r in rows) / 1e6))
    return out


def t_ratio(vrows, refer):
    by = {r['operator']: r for r in vrows}
    vis = collections.Counter()
    for r in refer:
        vis[r['engine']] += int(r.get('browser') or 0)
    out = ['| operator | pages taken | visitors sent back | pages per visitor |',
           '|---|---|---|---|']
    tp = tv = 0
    for name, agents, domains in PAIRS:
        if not domains:
            continue
        p = sum(int(by[a]['verified_pages']) for a in agents if a in by)
        v = sum(vis[d] for d in domains)
        if not p and not v:
            continue
        tp += p
        tv += v
        if not v:
            cell = '**not one visitor**'
        elif not p:
            cell = 'a visitor, and no crawl that survived the check'
        else:
            cell = '%d : 1' % round(p / v)
        out.append('| %s | %d | %d | %s |' % (name, p, v, cell))
    out.append('| **every operator with a front door** | **%d** | **%d** | '
               '**%d : 1** |' % (tp, tv, round(tp / tv) if tv else 0))
    return out


def t_windows(vrows):
    """The two months the archive holds substantially complete, side by side."""
    rows = read('crawlers_verified.csv')
    by = collections.defaultdict(lambda: {'req': 0, 'pages': 0, 'days': set()})
    for r in rows:
        m = r['day'][3:]
        b = by[m]
        b['req'] += int(r['requests'])
        b['pages'] += int(r['pages'])
        b['days'].add(r['day'])
    a, z = 'Jul/2025', 'Aug/2026'
    out = ['| | %s | %s | change |' % (a.replace('/', ' '), z.replace('/', ' ')),
           '|---|---|---|---|']
    if a not in by or z not in by:
        return out + ['| the two windows are not both present | | | |']
    for label, key in (('days observed', 'days'), ('crawler requests', 'req'),
                       ('pages taken', 'pages')):
        va = len(by[a][key]) if key == 'days' else by[a][key]
        vz = len(by[z][key]) if key == 'days' else by[z][key]
        chg = '' if key == 'days' else ('%.1f times' % (float(vz) / va) if va
                                        else 'from nothing')
        out.append('| %s | %d | %d | %s |' % (label, va, vz, chg))
    pa = float(by[a]['pages']) / len(by[a]['days'])
    pz = float(by[z]['pages']) / len(by[z]['days'])
    out.append('| **pages taken per day** | **%.0f** | **%.0f** | **%.1f times** |'
               % (pa, pz, pz / pa if pa else 0))
    return out


def t_referrals(refer):
    out = ['| day | engine | arrivals | a reader in a browser |',
           '|---|---|---|---|']
    for r in sorted(refer, key=lambda r: daykey(r['day'])):
        out.append('| %s | %s | %s | %s |'
                   % (r['day'].replace('/', ' '), r['engine'], r['requests'],
                      'yes' if int(r.get('browser') or 0) else
                      'no, and it was not even a served page'))
    if not refer:
        out.append('| none in the whole window | | | |')
    return out


def t_format():
    """What the log line holds, lifted from the file `extract.py` wrote.

    Regenerated rather than copied, so that a change in the extraction cannot
    leave a stale description of the extraction sitting in the paper.
    """
    path = os.path.join(HERE, 'sites', SITE, 'format.md')
    if not os.path.exists(path):
        return ['| the format check has not been run | |', '|---|---|']
    out, keep = [], False
    for line in io.open(path, encoding='utf-8').read().splitlines():
        if line.startswith('| property'):
            keep = True
        if keep and not line.startswith('|'):
            break
        if keep:
            out.append(line)
    return out


def t_context():
    """Who did send readers, so that one arrival can be read against something.

    Only page views from an agent that names a browser count here, and only
    those carrying a referrer from another site. Internal navigation and typed
    or bookmarked arrivals are excluded, because neither was sent by anybody.
    """
    ext = collections.Counter()
    kinds = collections.Counter()
    for r in read('traffic.csv'):
        n = int(r['page_views'])
        kinds[r['kind']] += n
        if r['kind'] != 'browser':
            continue
        h = r['referrer']
        # `(this site)` is internal navigation, masked at extraction. Nothing
        # here needs to know which host it was, and nothing here can find out.
        if h.startswith('(none') or h == '(this site)':
            continue
        ext[h] += n
    out = ['| referring site | page views it sent |', '|---|---|']
    for host, n in ext.most_common(10):
        out.append('| %s | %d |' % (host, n))
    out.append('| **every other referring site** | **%d** |'
               % (sum(ext.values()) - sum(n for _h, n in ext.most_common(10))))
    out.append('| **all of them** | **%d** |' % sum(ext.values()))
    out.append('')
    out.append('Page views from an agent naming a browser: %d. Page views from '
               'everything else that is not a listed crawler, which is scanners, '
               'monitors, libraries and feed readers: %d.'
               % (kinds['browser'], kinds['other']))
    return out


def t_sites():
    """The two sites side by side, which is the only comparison in the paper."""
    out = ['| | pages taken | readers returned | pages per reader |',
           '|---|---|---|---|']
    for al in sitecfg.aliases():
        vrows = read('verification.csv', al)
        refer = read('referrals.csv', al)
        if not vrows:
            continue
        by = {r['operator']: r for r in vrows}
        vis = collections.Counter()
        for r in refer:
            vis[r['engine']] += int(r.get('browser') or 0)
        tp = tv = 0
        for _name, agents, domains in PAIRS:
            if not domains:
                continue
            tp += sum(int(by[x]['verified_pages']) for x in agents if x in by)
            tv += sum(vis[d] for d in domains)
        d = sitecfg.describe(al)
        out.append('| **%s**, %s | %d | %d | %s |'
                   % (al if d['named'] else 'a second Greek site',
                      d['what'], tp, tv,
                      ('**%d : 1**' % round(float(tp) / tv)) if tv
                      else '**not one reader**'))
    return out


def main():
    global SITE, PREFIX
    ap = argparse.ArgumentParser()
    ap.add_argument('--site', default='broikos.gr')
    ap.add_argument('--prefix', default='')
    a = ap.parse_args()
    SITE, PREFIX = a.site, a.prefix
    vrows = read('verification.csv')
    claimed = read('crawlers.csv')
    refer = read('referrals.csv')
    if not vrows:
        print('run verify_agents.py first')
        return 1
    write('t1-window.md', t_window(vrows, claimed))
    write('t2-verification.md', t_verification(vrows))
    write('t3-load.md', t_load(vrows))
    write('t4-ratio.md', t_ratio(vrows, refer))
    write('t5-windows.md', t_windows(vrows))
    write('t6-referrals.md', t_referrals(refer))
    write('t7-context.md', t_context())
    write('t1b-format.md', t_format())
    if not PREFIX:
        write('t8-sites.md', t_sites())
    return 0


if __name__ == '__main__':
    raise SystemExit(main())