#!/usr/bin/env python3
"""Build the 10-blog pilot: join sheet blogs to Laravel by slugified name,
assemble body (TextBlocks + PhotoBlocks) in order, stage images, emit JSON."""
import openpyxl, json, os, re, base64, subprocess, unicodedata, shutil

PUB = "/srv/apps/brentwood/laravel/storage/app/public"
IMP = "/srv/apps/brentwooddev/wp-content/_imp_blog"
os.makedirs(IMP + "/img", exist_ok=True)

def slugify(s):
    s = unicodedata.normalize('NFKD', s).encode('ascii', 'ignore').decode()
    s = s.lower(); s = re.sub(r"['\"’]", '', s); s = re.sub(r'[^a-z0-9]+', '-', s).strip('-')
    return s

def sql(q):
    cmd = 'mysql -u root -p"$MYSQL_ROOT_PASSWORD" brentwood -N -s -e "%s"' % q.replace('"', '\\"')
    out = subprocess.run(['docker', 'exec', 'brentwood-mysql', 'sh', '-c', cmd],
                         capture_output=True, text=True)
    return [ln.split('\t') for ln in out.stdout.splitlines()]

# ---- build slug -> [(blog_id, version_id, published_at)] map ----
slugmap = {}
for ln in open('/tmp/all_blogs.tsv', encoding='utf-8', errors='replace'):
    p = ln.rstrip('\n').split('\t')
    if len(p) < 4: continue
    bid, ver, pub, b64name = p[0], p[1], p[2], p[3]
    try: name = base64.b64decode(b64name).decode('utf-8', 'replace')
    except: name = ''
    slugmap.setdefault(slugify(name), []).append((bid, ver, pub))

def resolve_disk(*cands):
    for c in cands:
        if c and c.strip():
            d = os.path.join(PUB, c.lstrip('/'))
            if os.path.isfile(d): return d
    return None

def stage(disk, tag):
    ext = os.path.splitext(disk)[1] or '.jpg'
    dest = os.path.join(IMP, 'img', f"{tag}{ext}")
    shutil.copyfile(disk, dest)
    return '/var/www/html/wp-content/_imp_blog/img/' + os.path.basename(dest)

def feat_disk(url):
    if not url: return None
    m = re.match(r'https?://[^/]+/storage/(.+)$', url.strip())
    return resolve_disk(m.group(1)) if m else None

# ---- pick 10 real pilot blogs from the sheet ----
wb = openpyxl.load_workbook('/srv/apps/brentwood/brentwood-pages.xlsx', read_only=True, data_only=True)
ws = wb['Blog Migration']; it = ws.iter_rows(values_only=True); h = list(next(it))
pilot = []; scanned = 0
for r in it:
    if len(pilot) >= 10: break
    scanned += 1
    d = dict(zip(h, r))
    name = (d['Post Name'] or '').strip()
    slug = str(d['Post Slug'] or '').strip()
    leaf = re.sub(r'^blogs/', '', slug).strip('/')
    if not name or not leaf or re.match(r'^\d+$', leaf):   # skip degenerate
        continue
    cands = slugmap.get(leaf) or slugmap.get(slugify(name))
    if not cands: continue
    # pick by closest publish date if duplicates
    pubdate = str(d['Publish Date'] or '')[:10]
    pick = cands[0]
    if len(cands) > 1 and pubdate:
        for c in cands:
            if c[2][:10] == pubdate: pick = c; break
    bid, ver, _ = pick

    # assemble blocks in order
    blocks = []; img_n = 0
    for so, ctype, cid in sql(
        "SELECT ct.sort_order, ce.content_type, ce.content_id FROM contentables ct "
        "JOIN content_elements ce ON ce.id=ct.content_element_id AND ce.deleted_at IS NULL "
        "WHERE ct.version_id=%s AND ct.deleted_at IS NULL ORDER BY ct.sort_order" % ver):
        if ctype.endswith('TextBlock'):
            rows = sql("SELECT REPLACE(TO_BASE64(COALESCE(body,'')),0x0a,'') FROM text_blocks WHERE id=%s" % cid)
            if rows and rows[0][0]:
                html = base64.b64decode(rows[0][0]).decode('utf-8', 'replace')
                if html.strip(): blocks.append({'type': 'text', 'html': html})
        elif ctype.endswith('PhotoBlock'):
            for ph in sql("SELECT COALESCE(large,''),COALESCE(medium,''),COALESCE(small,''),"
                          "COALESCE(retina,''),COALESCE(alt,'') FROM photos "
                          "WHERE content_type LIKE '%%PhotoBlock' AND content_id=%s AND deleted_at IS NULL "
                          "ORDER BY sort_order" % cid):
                disk = resolve_disk(ph[0], ph[3], ph[1], ph[2])
                if disk:
                    img_n += 1
                    blocks.append({'type': 'image', 'file': stage(disk, f"{leaf}-{img_n}"), 'alt': ph[4] or name})

    feat = feat_disk(d['Featured Image url'])
    feat_path = stage(feat, f"{leaf}-feat") if feat else None
    cats = [c.strip() for c in str(d['Categories']).split(',')] if d['Categories'] else []
    pilot.append({
        'name': name, 'slug': leaf, 'laravel_blog_id': bid,
        'date': str(d['Publish Date'])[:19] if d['Publish Date'] else None,
        'author': (d['Author'] or '').strip(),
        'yoast_title': (d['Page Title'] or '').strip() if d['Page Title'] else '',
        'yoast_desc': (d['Meta Description'] or '').strip() if d['Meta Description'] else '',
        'categories': [c for c in cats if c],
        'featured': feat_path, 'blocks': blocks,
        'counts': {'text': sum(1 for b in blocks if b['type'] == 'text'),
                   'img': sum(1 for b in blocks if b['type'] == 'image')},
    })

json.dump(pilot, open(IMP + '/blog-pilot.json', 'w'))

# ---- category hierarchy from the Blog Categories tab ----
wsc = wb['Blog Categories']; it = wsc.iter_rows(values_only=True); hc = list(next(it))
cats = []
for r in it:
    d = dict(zip(hc, r))
    name = (d['Category'] or '').strip() if d['Category'] else ''
    parent = (d['Parent Category'] or '').strip() if d['Parent Category'] else ''
    if name: cats.append({'name': name, 'parent': parent})
json.dump(cats, open(IMP + '/blog-categories.json', 'w'))

for f in (IMP + '/blog-pilot.json', IMP + '/blog-categories.json'): os.chmod(f, 0o664)
os.system("chmod -R 775 %s && find %s -type f -exec chmod 664 {} +" % (IMP, IMP))
print(f"scanned {scanned} sheet rows to find 10 real pilot blogs")
print(f"category terms: {len(cats)}")
for p in pilot:
    print(f"  [{p['laravel_blog_id']}] {p['name'][:38]:38} slug={p['slug'][:30]:30} "
          f"text={p['counts']['text']} img={p['counts']['img']} feat={'Y' if p['featured'] else '-'} "
          f"cats={','.join(p['categories']) or '-'}")