"""
Phase 4 Backfill v3: single batch insert
"""
import json
import urllib.request
import re
import time
import subprocess
import sys

with open('/opt/adsfb/.env', 'r') as f:
    env = f.read()
TOKEN = re.search(r'FACEBOOK_API_KEY99=([^\s]+)', env).group(1).strip()
print(f'token={len(TOKEN)}b', flush=True)

DB_PW = 'skSy49CaA2LqA8AqOvs0m7UaAusbcr30'

# Get all campaigns
result = subprocess.run([
    'mysql', '-h', '127.0.0.1', '-u', 'adsfb', f'-p{DB_PW}', 'adsfb', '-B', '-N', '-e',
    "SELECT DISTINCT campaign_id FROM campaign_daily_metrics"
], capture_output=True, text=True, timeout=30)
campaigns = [l.strip() for l in result.stdout.split('\n') if l.strip()]
print(f'campaigns: {len(campaigns)}', flush=True)

# For each campaign, fetch ads
ads_to_save = []
t0 = time.time()
for ci, cid in enumerate(campaigns):
    try:
        url = f'https://graph.facebook.com/v20.0/{cid}/ads?fields=id,name,status,effective_status,creative{{id,name,title,body,image_url,video_id,thumbnail_url,call_to_action_type,link_url}}&limit=50&access_token={TOKEN}'
        data = json.loads(urllib.request.urlopen(url, timeout=15).read())
        for ad in data.get('data', []):
            cr = ad.get('creative', {}) or {}
            ads_to_save.append({
                'ad_id': (ad.get('id') or '')[:64],
                'ad_name': (ad.get('name') or '')[:255],
                'campaign_id': cid,
                'campaign_name': '',
                'status': (ad.get('status') or '')[:32],
                'effective_status': (ad.get('effective_status') or '')[:32],
                'creative_id': (cr.get('id') or '')[:64],
                'creative_title': (cr.get('title') or '')[:255],
                'creative_body': cr.get('body') or '',
                'creative_image_url': cr.get('image_url') or '',
                'creative_video_id': (cr.get('video_id') or '')[:64],
                'creative_thumbnail_url': cr.get('thumbnail_url') or '',
                'creative_call_to_action': (cr.get('call_to_action_type') or '')[:64],
                'creative_link_url': cr.get('link_url') or '',
            })
    except Exception as e:
        print(f'  err {cid}: {e}', flush=True)
    time.sleep(0.3)

print(f'Fetched {len(ads_to_save)} ads in {int(time.time()-t0)}s', flush=True)

# Build ONE big SQL
def esc(s):
    if s is None: return 'NULL'
    return "'" + str(s).replace("\\", "\\\\").replace("'", "\\'") + "'"

all_values = []
for ad in ads_to_save:
    all_values.append(f"({esc(ad['ad_id'])}, {esc(ad['ad_name'])}, {esc(ad['campaign_id'])}, {esc(ad['campaign_name'])}, {esc(ad['status'])}, {esc(ad['effective_status'])}, {esc(ad['creative_id'])}, {esc(ad['creative_title'])}, {esc(ad['creative_body'])}, {esc(ad['creative_image_url'])}, {esc(ad['creative_video_id'])}, {esc(ad['creative_thumbnail_url'])}, {esc(ad['creative_call_to_action'])}, {esc(ad['creative_link_url'])})")

# Write to SQL file in chunks of 1000 (max_allowed_packet is usually 16M)
CHUNK = 500
total_written = 0
with open('/tmp/bf_meta_big.sql', 'w') as f:
    for i in range(0, len(all_values), CHUNK):
        chunk = all_values[i:i+CHUNK]
        sql = f"INSERT INTO ad_metadata (ad_id, ad_name, campaign_id, campaign_name, status, effective_status, creative_id, creative_title, creative_body, creative_image_url, creative_video_id, creative_thumbnail_url, creative_call_to_action, creative_link_url) VALUES {','.join(chunk)} ON DUPLICATE KEY UPDATE ad_name=VALUES(ad_name), status=VALUES(status), effective_status=VALUES(effective_status), creative_id=VALUES(creative_id), creative_title=VALUES(creative_title), creative_body=VALUES(creative_body), creative_image_url=VALUES(creative_image_url), creative_video_id=VALUES(creative_video_id), creative_thumbnail_url=VALUES(creative_thumbnail_url), creative_call_to_action=VALUES(creative_call_to_action), creative_link_url=VALUES(creative_link_url), last_updated_at=NOW();\n"
        f.write(sql)

print(f'Wrote {len(all_values)} inserts to /tmp/bf_meta_big.sql', flush=True)

# Execute the big file
result = subprocess.run(['mysql', '-h', '127.0.0.1', '-u', 'adsfb', f'-p{DB_PW}', 'adsfb', '<', '/tmp/bf_meta_big.sql'], capture_output=True, text=True, timeout=600)
print(f'MySQL rc={result.returncode}', flush=True)
if 'ERROR' in result.stderr or 'ERROR' in result.stdout:
    print(f'stderr: {result.stderr[:500]}', flush=True)
    print(f'stdout: {result.stdout[:500]}', flush=True)

# Final
result = subprocess.run(['mysql', '-h', '127.0.0.1', '-u', 'adsfb', f'-p{DB_PW}', 'adsfb', '-B', '-e', "SELECT COUNT(*) as total_ads, COUNT(DISTINCT campaign_id) as campaigns_with_meta FROM ad_metadata;"], capture_output=True, text=True, timeout=10)
print(f'\n[final]\n{result.stdout}', flush=True)