#!/usr/bin/env python3
"""FULL blog run builder — LARAVEL-DRIVEN (every published blog, no sheet-match loss).
Title/slug/content/author/tags from Laravel (authoritative). Dates: published_at, with
epoch(1970)/null fallback to sheet date then created_at. Categories from taggables + parents."""
import openpyxl, json, os, re, base64, subprocess, shutil, sys, html as _html

# Optional: pass Laravel blog ids as arguments to build ONLY those (incremental
# top-up runs). With no arguments the behaviour is unchanged — every blog.
ONLY_IDS = set(a.strip() for a in sys.argv[1:] if a.strip())

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

def lslug(name):
    s = name.lower(); s = s.replace(' ', '-'); s = re.sub(r'[^a-z0-9\-]', '', s); return s
def b64dec(s):
    try: return base64.b64decode(s).decode('utf-8', 'replace')
    except: return ''
def q(sql):
    cmd = 'mysql -u root -p"$MYSQL_ROOT_PASSWORD" brentwood -N -s -e "%s"' % sql.replace('"', '\\"')
    return subprocess.run(['docker','exec','brentwood-mysql','sh','-c',cmd], capture_output=True, text=True).stdout

print("bulk-extracting Laravel data...")
# blogs: id, version, b64(name), published_at, b64(author), created_at
blogs = []
for ln in q("SELECT b.id, b.published_version_id, REPLACE(TO_BASE64(COALESCE(v.name,'')),0x0a,''), "
            "COALESCE(v.published_at,''), REPLACE(TO_BASE64(COALESCE(b.author,'')),0x0a,''), COALESCE(b.created_at,'') "
            "FROM blogs b JOIN versions v ON v.id=b.published_version_id "
            "WHERE b.deleted_at IS NULL AND b.published_version_id IS NOT NULL").splitlines():
    p = ln.split('\t')
    if len(p) >= 6: blogs.append({'id': p[0], 'ver': p[1], 'name': b64dec(p[2]), 'pub': p[3], 'author': b64dec(p[4]), 'created': p[5]})

elements = {}
for ln in q("SELECT ct.version_id, 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 "
            "JOIN blogs b ON b.published_version_id=ct.version_id AND b.deleted_at IS NULL "
            "WHERE ct.deleted_at IS NULL ORDER BY ct.version_id, ct.sort_order").splitlines():
    p = ln.split('\t')
    if len(p) >= 3: elements.setdefault(p[0], []).append((p[1], p[2]))

textbody = {}
for ln in q("SELECT id, REPLACE(TO_BASE64(COALESCE(body,'')),0x0a,'') FROM text_blocks").splitlines():
    p = ln.split('\t');
    if len(p) >= 2: textbody[p[0]] = p[1]

pblocks = {}
for ln in q("SELECT id, columns, REPLACE(TO_BASE64(COALESCE(header,'')),0x0a,''), REPLACE(TO_BASE64(COALESCE(body,'')),0x0a,'') FROM photo_blocks").splitlines():
    p = ln.split('\t')
    if len(p) >= 4: pblocks[p[0]] = (p[1], p[2], p[3])

photos = {}
for ln in q("SELECT content_id, COALESCE(large,''),COALESCE(medium,''),COALESCE(small,''),COALESCE(retina,''),"
            "REPLACE(TO_BASE64(COALESCE(alt,'')),0x0a,''),REPLACE(TO_BASE64(COALESCE(description,'')),0x0a,'') "
            "FROM photos WHERE content_type LIKE '%PhotoBlock' AND deleted_at IS NULL ORDER BY content_id, sort_order").splitlines():
    p = ln.split('\t')
    if len(p) >= 7: photos.setdefault(p[0], []).append(p[1:])

# tags + hierarchy + blog taggables
tag_name = {}; tag_parent = {}
for ln in q("SELECT id, REPLACE(TO_BASE64(COALESCE(name,'')),0x0a,''), COALESCE(parent_tag_id,'') FROM tags").splitlines():
    p = ln.split('\t')
    if len(p) >= 3: tag_name[p[0]] = b64dec(p[1]); tag_parent[p[0]] = p[2]
blog_tags = {}
for ln in q("SELECT taggable_id, tag_id FROM taggables WHERE taggable_type LIKE '%Blog'").splitlines():
    p = ln.split('\t')
    if len(p) >= 2: blog_tags.setdefault(p[0], []).append(p[1])
print(f"  blogs={len(blogs)} versions={len(elements)} tags={len(tag_name)} tagged_blogs={len(blog_tags)}")

# sheet: date + yoast keyed by decoded-name slug
sheet_meta = {}
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))
for r in it:
    d = dict(zip(h, r)); nm = (d['Post Name'] or '').strip()
    if not nm: continue
    k = lslug(_html.unescape(nm)).strip('-')
    if k and k not in sheet_meta:
        sheet_meta[k] = {'date': str(d['Publish Date'])[:19] if d['Publish Date'] else None,
                         'yt': (d['Page Title'] or '').strip() if d['Page Title'] else '',
                         'yd': (d['Meta Description'] or '').strip() if d['Meta Description'] else ''}

_seq = {'n': 0}
def resolve_disk(*c):
    for x in c:
        if x and x.strip():
            dd = os.path.join(PUB, x.lstrip('/'))
            if os.path.isfile(dd): return dd
    return None
def stage(disk):
    _seq['n'] += 1
    ext = os.path.splitext(disk)[1] or '.jpg'
    dest = os.path.join(IMP, 'img', f"i{_seq['n']}{ext}")
    shutil.copyfile(disk, dest)
    return '/var/www/html/wp-content/_imp_blog_full/img/' + os.path.basename(dest)

def photoblock(cid, default_alt):
    pb = pblocks.get(cid)
    cols = int(pb[0]) if pb and pb[0].isdigit() else 1
    header = b64dec(pb[1]) if pb else ''; body = b64dec(pb[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 photos.get(cid, []):
        disk = resolve_disk(ph[0], ph[3], ph[1], ph[2])
        if disk: imgs.append({'file': stage(disk), 'alt': b64dec(ph[4]) or default_alt, 'caption': b64dec(ph[5]).strip()})
    return cols, imgs, ptext

def assemble(ver, name):
    hero = None; body = []
    for i, (ctype, cid) in enumerate(elements.get(ver, [])):
        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})
                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'):
            html = b64dec(textbody.get(cid, ''))
            if html.strip(): body.append({'type': 'text', 'html': html})
    return hero, body

def categories(bid):
    out = []; seen = set()
    for tid in blog_tags.get(bid, []):
        cur = tid; guard = 0
        while cur and cur in tag_name and cur not in seen and guard < 6:
            seen.add(cur); nm = tag_name[cur].strip()
            if nm: out.append(nm)
            cur = tag_parent.get(cur, ''); guard += 1
    return out

def pick_date(b):
    p = b['pub']
    if p and not p.startswith('1970') and p > '2000-01-01':
        return p[:19]
    sm = sheet_meta.get(lslug(b['name']).strip('-'))
    if sm and sm['date']: return sm['date']
    if b['created'] and not b['created'].startswith('1970'): return b['created'][:19]
    return None

recs = []; degenerate = 0; empty = 0
for b in blogs:
    if ONLY_IDS and str(b['id']) not in ONLY_IDS:
        continue
    name = b['name'].strip()
    slug = lslug(name)
    if not name or re.match(r'^[\d\s]*$', name) or not slug.strip('-'):
        degenerate += 1; continue
    hero, body = assemble(b['ver'], name)
    if hero is None and not body: empty += 1
    sm = sheet_meta.get(lslug(name).strip('-'), {})
    recs.append({
        'name': name, 'slug': slug, 'laravel_blog_id': b['id'], 'live_url': '/blogs/' + slug,
        'date': pick_date(b), 'author': b['author'].strip(),
        'yoast_title': sm.get('yt', ''), 'yoast_desc': sm.get('yd', ''),
        'categories': categories(b['id']), 'hero': hero, 'blocks': body,
    })

json.dump(recs, open(IMP + '/blog-full.json', 'w'))
os.system("chmod -R 775 %s 2>/dev/null; find %s -type f -exec chmod 664 {} + 2>/dev/null" % (IMP, IMP))
print(f"RECORDS={len(recs)} images_staged={_seq['n']} degenerate_skipped={degenerate} empty_no_content={empty}")