#!/usr/bin/env python3
"""Build recent (or specified) blogs with hero/body separation + ALL content pockets:
- First content element = HERO (image -> Featured Image; gallery -> Featured Gallery), not re-embedded.
- Photo-block TEXT (header/body) is captured as body text blocks (not dropped).
- Photo DESCRIPTIONS captured as image captions.
- Body text -> proper Gutenberg blocks (done in the PHP importer).
Usage: build_blog_recent.py [blogId ...]   (force-include specific Laravel blog ids for testing)"""
import openpyxl, json, os, re, base64, subprocess, shutil, sys

PUB = "/srv/apps/brentwood/laravel/storage/app/public"
IMP = "/srv/apps/brentwooddev/wp-content/_imp_blog"
os.makedirs(IMP + "/img", exist_ok=True)
FORCE_IDS = set(sys.argv[1:])

def laravel_slug(name):
    s = name.lower(); s = s.replace(' ', '-'); s = re.sub(r'[^a-z0-9\-]', '', s); return s

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

slugmap = {}; id2name = {}
for ln in open('/tmp/all_blogs.tsv', encoding='utf-8', errors='replace'):
    p = ln.rstrip('\n').split('\t')
    if len(p) < 4: continue
    try: nm = base64.b64decode(p[3]).decode('utf-8', 'replace')
    except: continue
    slugmap.setdefault(laravel_slug(nm).strip('-'), []).append((p[0], p[1], p[2], nm))
    id2name[p[0]] = (p[1], nm)

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

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

def b64dec(s):
    try: return base64.b64decode(s).decode('utf-8', 'replace')
    except: return ''

def photoblock(cid, default_alt):
    """Returns (columns, images[], ptext_html). images carry an optional caption (photo.description)."""
    pb = sql("SELECT columns, REPLACE(TO_BASE64(COALESCE(header,'')),0x0a,''), "
             "REPLACE(TO_BASE64(COALESCE(body,'')),0x0a,'') FROM photo_blocks WHERE id=%s" % cid)
    cols = int(pb[0][0]) if pb and pb[0][0].isdigit() else 1
    header = b64dec(pb[0][1]) if pb else ''
    body   = b64dec(pb[0][2]) if pb else ''
    ptext = ''
    if header.strip(): ptext += '<h3>' + header.strip() + '</h3>'
    if body.strip() and body.strip() != '<p></p>': ptext += body
    imgs = []
    for ph in sql("SELECT COALESCE(large,''),COALESCE(medium,''),COALESCE(small,''),COALESCE(retina,''),"
                  "COALESCE(alt,''),REPLACE(TO_BASE64(COALESCE(description,'')),0x0a,'') 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:
            cap = b64dec(ph[5]) if len(ph) > 5 else ''
            imgs.append({'file': stage(disk), 'alt': ph[4] or default_alt, 'caption': cap.strip()})
    return cols, imgs, ptext

def build_one(bid, ver, name, sheet):
    els = 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)
    hero = None; body = []
    for i, (so, ctype, cid) in enumerate(els):
        if ctype.endswith('PhotoBlock'):
            cols, imgs, ptext = photoblock(cid, name)
            if i == 0 and imgs and hero is None:
                hero = {'type': 'image' if len(imgs) == 1 else 'gallery',
                        'columns': max(1, min(cols, len(imgs))), 'images': imgs}
                if ptext: body.append({'type': 'text', 'html': ptext})   # hero's text -> top of body
                continue
            if imgs:
                body.append({'type': 'gallery', 'columns': max(1, min(cols, len(imgs))), 'images': imgs})
            if ptext:
                body.append({'type': 'text', 'html': ptext})
        elif ctype.endswith('TextBlock'):
            rws = sql("SELECT REPLACE(TO_BASE64(COALESCE(body,'')),0x0a,'') FROM text_blocks WHERE id=%s" % cid)
            if rws and rws[0][0]:
                html = b64dec(rws[0][0])
                if html.strip(): body.append({'type': 'text', 'html': html})
    rec = {'name': name, 'slug': laravel_slug(name).strip('-') or str(bid), 'laravel_blog_id': bid,
           'live_url': '/blogs/' + laravel_slug(name), 'hero': hero, 'blocks': body,
           'date': None, 'author': '', 'yoast_title': '', 'yoast_desc': '', 'categories': []}
    if sheet:
        rec.update({
            'slug': re.sub(r'^blogs/', '', str(sheet['Post Slug'])).strip('/') or rec['slug'],
            'date': str(sheet['Publish Date'])[:19] if sheet['Publish Date'] else None,
            'author': (sheet['Author'] or '').strip(),
            'yoast_title': (sheet['Page Title'] or '').strip() if sheet['Page Title'] else '',
            'yoast_desc': (sheet['Meta Description'] or '').strip() if sheet['Meta Description'] else '',
            'categories': [c.strip() for c in str(sheet['Categories']).split(',') if c and c.strip()] if sheet['Categories'] else [],
        })
    return rec

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))
rows = [dict(zip(h, r)) for r in it]
sheet_by_slug = {}
for d in rows:
    leaf = re.sub(r'^blogs/', '', str(d['Post Slug'] or '')).strip('/')
    if leaf: sheet_by_slug[leaf] = d

pilot = []
# forced ids first (for targeted testing)
for bid in FORCE_IDS:
    if bid in id2name:
        ver, name = id2name[bid]
        sheet = sheet_by_slug.get(laravel_slug(name).strip('-'))
        pilot.append(build_one(bid, ver, name, sheet))

def real(d):
    n = (d['Post Name'] or '').strip(); s = re.sub(r'^blogs/', '', str(d['Post Slug'] or '')).strip('/')
    return n and s and not re.match(r'^\d+$', s) and d['Publish Date']
cand = [d for d in rows if real(d)]
cand.sort(key=lambda d: str(d['Publish Date']), reverse=True)
for d in cand:
    if len(pilot) >= 10 + len(FORCE_IDS): break
    name = (d['Post Name'] or '').strip()
    leaf = re.sub(r'^blogs/', '', str(d['Post Slug'])).strip('/')
    matches = slugmap.get(leaf) or slugmap.get(laravel_slug(name).strip('-'))
    if not matches: continue
    bid, ver, _, _ = matches[0]
    if bid in FORCE_IDS: continue
    pilot.append(build_one(bid, ver, name, d))

json.dump(pilot, open(IMP + '/blog-recent.json', 'w'))
os.system("chmod -R 775 %s && find %s -type f -exec chmod 664 {} +" % (IMP, IMP))
for p in pilot:
    hero = p['hero']
    hs = 'none' if not hero else ('IMAGE' if hero['type'] == 'image' else f"GALLERY(c{hero['columns']}x{len(hero['images'])})")
    caps = sum(1 for b in p['blocks'] if b['type']=='gallery' for im in b['images'] if im['caption']) + (sum(1 for im in (hero['images'] if hero else []) if im.get('caption')))
    bs = [f"gal{len(b['images'])}" if b['type']=='gallery' else 'text' for b in p['blocks']]
    print(f"  [{p['laravel_blog_id']}] hero={hs:16} body=[{','.join(bs)}] caps={caps}  {p['name'][:34]}")