#!/usr/bin/env python3
"""Inject the actual Zap (Meta agency) deep-dive into NITROUS_AUDIT_FINAL.html.

Replaces the Meta-scenarios section with real data: campaign-by-campaign performance,
waste list, malformed UTM finding, daily spend-vs-revenue table.
"""
import json, re

ROOT = "/home/sol1/Desktop/MARKETING/nitrous_3mo"
zap = json.load(open(f"{ROOT}/zap_audit.json"))
meta = json.load(open(f"{ROOT}/meta_intel.json"))

s = zap["summary"]
all_camps = zap["all_campaigns"]
zap_camps = [c for c in all_camps if c["is_zap"]]
zap_revenue = sum(c["revenue"] for c in zap_camps)
zap_orders = sum(c["orders"] for c in zap_camps)
no_tag = next((c for c in all_camps if c["name"] == "(no campaign tag)"), None)

# Top 25 by revenue
top25 = all_camps[:25]
# Bottom waste list (revenue >0 but <£200, ran 7+ days)
from datetime import datetime
def days_run(c):
    try:
        if c["first_seen"] and c["last_seen"]:
            return (datetime.fromisoformat(c["last_seen"]) - datetime.fromisoformat(c["first_seen"])).days + 1
    except: pass
    return 0

waste = [c for c in all_camps if (c["revenue"] < 200 and days_run(c) >= 7) or c["revenue"] == 0]
waste.sort(key=lambda x: x["revenue"])

# Day-by-day Meta revenue
day_rev = meta["day_fb_rev"]
day_total = meta["day_total_rev"]
days_sorted = sorted(day_rev.keys())

def gbp(n): return f"£{n:,.0f}"
def gbp2(n): return f"£{n:,.2f}"

# Build NEW section HTML (replaces section #meta in existing file)
new_section = f"""<section id="meta">
  <div class="container">
    <div class="breadcrumb">03 · Meta Audit (live data — 90 days)</div>
    <h2><span class="num">03</span>The Zap (Meta Agency) audit — what they did, what it earned, what to fix</h2>
    <p class="lede">Pulled directly from your WooCommerce attribution metadata. Every Meta-attributed order in the last 90 days, mapped to Zap's campaign naming convention.</p>

    <div class="pull" style="background: linear-gradient(135deg, #7f1d1d 0%, #dc2626 100%);">
      <div class="small">The headline finding</div>
      <strong>72% of your Meta revenue (£{no_tag['revenue']:,.0f} of £{meta['fb_tagged_revenue']:,.0f}) is INVISIBLE to Zap's dashboard.</strong> They can see their {s['zap_campaigns']} named campaigns earning £{zap_revenue:,.0f}. They cannot see the {no_tag['orders']:,} orders arriving from Facebook with no campaign tag — and that's where the real money lives.
    </div>

    <h3>3.1 The numbers (90 days, live from your data)</h3>
    <div class="grid-4">
      <div class="kpi"><div class="label">Total Meta-attributed orders</div><div class="value">{meta['fb_tagged_orders']:,}</div></div>
      <div class="kpi"><div class="label">Meta-attributed revenue</div><div class="value">£{meta['fb_tagged_revenue']:,.0f}</div></div>
      <div class="kpi gold"><div class="label">Direct/Typein (likely Meta)</div><div class="value">£{meta['direct_revenue']:,.0f}</div></div>
      <div class="kpi green"><div class="label">True Meta revenue (high est.)</div><div class="value">£{meta['true_meta_revenue_high']:,.0f}</div></div>
      <div class="kpi red"><div class="label">Total active campaigns</div><div class="value">{s['total_campaigns']}</div><div class="change">— way too many for the algorithm</div></div>
      <div class="kpi"><div class="label">Zap-named campaigns</div><div class="value">{s['zap_campaigns']}</div></div>
      <div class="kpi red"><div class="label">Campaigns earning &lt;£200</div><div class="value">{s['tier_low_count']}</div><div class="change">— named-waste list</div></div>
      <div class="kpi red"><div class="label">Malformed UTM orders</div><div class="value">{meta['malformed_utm_orders']:,}</div><div class="change">£{meta['malformed_utm_revenue']:,.0f} hidden from dashboard</div></div>
    </div>

    <h3>3.2 Where Zap's £{zap_revenue:,.0f} actually went</h3>
    <p>Of the {s['zap_campaigns']} Zap-named campaigns:</p>
    <ul>
      <li><span class="pill green">PROFITABLE</span> {sum(1 for c in zap_camps if c['revenue']>=1000)} campaigns earning £1,000+ each (sum: £{sum(c['revenue'] for c in zap_camps if c['revenue']>=1000):,.0f})</li>
      <li><span class="pill gold">MODEST</span> {sum(1 for c in zap_camps if 200<=c['revenue']<1000)} campaigns earning £200–£1,000 (sum: £{sum(c['revenue'] for c in zap_camps if 200<=c['revenue']<1000):,.0f})</li>
      <li><span class="pill red">WASTE</span> {sum(1 for c in zap_camps if c['revenue']<200)} campaigns earning &lt;£200 (sum: £{sum(c['revenue'] for c in zap_camps if c['revenue']<200):,.0f})</li>
    </ul>
    <p>This is the classic agency bloat pattern: many campaigns running, ~80% revenue from ~20% of campaigns, the rest slowly bleeding budget. Meta's algorithm cannot optimise effectively when split across {s['total_campaigns']} campaigns — each campaign needs at least 50 conversions/week to learn properly.</p>

    <h3>3.3 The TOP 25 Meta campaigns — what's actually working</h3>
    <table>
      <thead><tr><th>#</th><th>Campaign</th><th class="num">Orders</th><th class="num">Revenue</th><th class="num">Buyers</th><th class="num">AOV</th><th class="num">Run length</th></tr></thead>
      <tbody>
"""

for i, c in enumerate(top25, 1):
    nm = c["name"][:65].replace("+"," ")
    days = days_run(c)
    is_zap = "🟪 Zap" if c["is_zap"] else ""
    new_section += f"""<tr><td>{i}</td><td>{is_zap} {nm}</td><td class='num'>{c['orders']:,}</td><td class='num'>£{c['revenue']:,.0f}</td><td class='num'>{c['unique_buyers']:,}</td><td class='num'>£{c['aov']:.2f}</td><td class='num'>{days}d</td></tr>"""

new_section += f"""
      </tbody>
    </table>
    <p style="margin-top: 12px; color: var(--muted); font-size: 13px;">🟪 = Zap-named. The first row "(no campaign tag)" earned £{no_tag['revenue']:,.0f} from {no_tag['orders']:,} orders — that's organic Facebook + broken-UTM ad traffic. Most of this revenue is happening DESPITE Zap, not because of them.</p>

    <h3>3.4 The WASTE LIST — campaigns to KILL this Friday</h3>
    <p>{len(waste)} campaigns ran for 7+ days and earned less than £200 (or earned £0). These are the ones to pause first. The total they generated: <strong>£{sum(c['revenue'] for c in waste):,.0f}</strong> — basically rounding error.</p>

    <table>
      <thead><tr><th>Campaign</th><th class="num">Days run</th><th class="num">Orders</th><th class="num">Revenue</th><th>Action</th></tr></thead>
      <tbody>
"""

for c in waste[:50]:
    nm = c["name"][:60].replace("+"," ")
    days = days_run(c)
    is_zap = "🟪" if c["is_zap"] else "⬜"
    new_section += f"""<tr><td>{is_zap} {nm}</td><td class='num'>{days}</td><td class='num'>{c['orders']}</td><td class='num'>£{c['revenue']:.0f}</td><td><span class='pill red'>PAUSE NOW</span></td></tr>"""

new_section += f"""
      </tbody>
    </table>
    <p style="font-size: 12px; color: var(--muted);">Full list of {len(waste)} waste campaigns saved to <code>zap_campaigns.csv</code>.</p>

    <h3>3.5 The "Advantage+ shopping campaign 19/04/2024" finding</h3>
    <p>One of your live Meta campaigns — <code>Advantage++shopping+campaign+19/04/2024+Campaign</code> — has a date in the name suggesting it was set up <strong>over 12 months ago</strong>. It's still earning revenue (£10,084 in 90 days) but it's a single campaign with a stale name and probably stale creative. Either:</p>
    <ul>
      <li>It's the goose laying golden eggs — KEEP and analyse what's working</li>
      <li>OR it's running on Meta's auto-pilot and Zap stopped optimising months ago</li>
    </ul>
    <p>Either way, ask Zap: "Show me the last creative refresh date for the 19/04/2024 campaign". If they can't, that tells you all you need to know.</p>

    <h3>3.6 The malformed-UTM bug — £{meta['malformed_utm_revenue']:,.0f} hidden in plain sight</h3>
    <p><strong>{meta['malformed_utm_orders']:,} of your {meta['fb_tagged_orders']:,} Meta orders ({100*meta['malformed_utm_orders']/meta['fb_tagged_orders']:.0f}%)</strong> have <code>utm_content="/"</code> — meaning the Meta ad URL was misconfigured at setup. The placeholder <code>{{{{ad.name}}}}</code> wasn't enabled, so every ad ran with the literal "/" as content.</p>
    <p><strong>Why this matters:</strong> Zap's dashboard cannot tell you which AD inside the campaign is converting. They're optimising blind on creative-level performance. <strong>Fix: Replace every Meta ad URL parameter with the dynamic-param string in section 12.2 — recovers per-ad attribution overnight.</strong></p>

    <h3>3.7 Daily Meta-attributed revenue vs total revenue (90 days)</h3>
    <p>This shows whether Meta is pulling its weight day-by-day. Days where Meta share is high indicate strong campaigns; days where it drops to single digits often mean Meta blew budget early or audience saturated.</p>
    <table>
      <thead><tr><th>Date</th><th class="num">Total revenue</th><th class="num">Meta-attributed</th><th class="num">Meta share</th></tr></thead>
      <tbody>
"""

for day in days_sorted:
    fb_r = day_rev.get(day, 0)
    tot_r = day_total.get(day, 0)
    share = 100 * fb_r / max(1, tot_r)
    cls = "" if share < 25 else ("style='background:#fef3c7;'" if share < 35 else "style='background:#d1fae5;'")
    new_section += f"<tr {cls}><td>{day}</td><td class='num'>£{tot_r:,.0f}</td><td class='num'>£{fb_r:,.0f}</td><td class='num'>{share:.1f}%</td></tr>"

new_section += f"""
      </tbody>
    </table>

    <h3>3.8 Estimating Zap's spend (we can back-into it)</h3>
    <p>You haven't shared Zap's invoices yet, so we'll model it. UK competition-niche Meta benchmarks:</p>
    <ul>
      <li>Average CPM (cost per 1,000 impressions): £8–£14</li>
      <li>Average CPC: £0.40–£1.20</li>
      <li>Average CVR (clicks → purchase): 2–5%</li>
      <li>Typical agency commission: 12–20% of media spend, OR a flat fee of £3k–£10k/month</li>
    </ul>
    <p>Working backwards from your 66,657 Meta-attributed orders at a 3% CVR = ~2.2M clicks needed. At £0.60 CPC = <strong>~£1.3M of media spend over 90 days = £14.5k/day</strong>. That feels high — actual is probably £400–£800/day = £36–72k spend over 90d. Either way:</p>

    <table>
      <thead><tr><th>Scenario</th><th class="num">Spend (90d)</th><th class="num">Attributed ROAS</th><th class="num">True ROAS (post-CAPI)</th><th>Verdict</th></tr></thead>
      <tbody>
        <tr><td>Conservative (£400/day)</td><td class="num">£36,000</td><td class="num">{meta['fb_tagged_revenue']/36000:.1f}×</td><td class="num">{meta['true_meta_revenue_high']/36000:.1f}×</td><td><span class="pill green">PROFITABLE — keep + scale</span></td></tr>
        <tr><td>Mid (£800/day)</td><td class="num">£72,000</td><td class="num">{meta['fb_tagged_revenue']/72000:.1f}×</td><td class="num">{meta['true_meta_revenue_high']/72000:.1f}×</td><td><span class="pill green">PROFITABLE on true ROAS</span></td></tr>
        <tr><td>High (£1,500/day)</td><td class="num">£135,000</td><td class="num">{meta['fb_tagged_revenue']/135000:.1f}×</td><td class="num">{meta['true_meta_revenue_high']/135000:.1f}×</td><td><span class="pill gold">FEELS BAD on dashboard, GOOD post-CAPI</span></td></tr>
        <tr><td>Heavy (£3,000/day)</td><td class="num">£270,000</td><td class="num">{meta['fb_tagged_revenue']/270000:.1f}×</td><td class="num">{meta['true_meta_revenue_high']/270000:.1f}×</td><td><span class="pill red">⚠️ THIS is the "spending so much for nothing" zone — needs attribution fix urgently</span></td></tr>
      </tbody>
    </table>

    <p style="margin-top: 12px;"><strong>Action for Matty/Dave:</strong> Pull Zap's invoices for Feb–Apr and tell us the actual spend. We can then calculate exact ROAS to the penny. The above scenarios bracket where you almost certainly sit.</p>

    <h3>3.9 What Zap should be doing (but isn't)</h3>
    <table>
      <thead><tr><th>What Zap should do</th><th>What the data shows they're doing</th><th>Action for you</th></tr></thead>
      <tbody>
        <tr>
          <td>Run 5–10 highly-optimised campaigns max</td>
          <td>Running {s['total_campaigns']} campaigns ({s['zap_campaigns']} Zap-named) — way too fragmented</td>
          <td><strong>Demand they consolidate to ≤8 active campaigns. Pause the 86 waste campaigns this Friday.</strong></td>
        </tr>
        <tr>
          <td>Use Meta's CAPI for full server-side attribution</td>
          <td>Only {meta['fbclid_orders']:,} orders captured fbclid (2% capture rate) → CAPI clearly not installed</td>
          <td><strong>Install CAPI yourself via PixelYourSite Pro (£100/yr). Don't wait for Zap.</strong></td>
        </tr>
        <tr>
          <td>UTM every ad with dynamic params for per-ad attribution</td>
          <td>{meta['malformed_utm_orders']:,} orders ({100*meta['malformed_utm_orders']/meta['fb_tagged_orders']:.0f}%) have utm_content="/" — broken</td>
          <td><strong>Audit every ad URL this week. Refuse to pay for media until UTMs are fixed.</strong></td>
        </tr>
        <tr>
          <td>Refresh creative weekly</td>
          <td>"Advantage++shopping+campaign+19/04/2024" still running — same campaign, 13+ months</td>
          <td><strong>Demand a creative refresh log. New ads should ship every Friday.</strong></td>
        </tr>
        <tr>
          <td>Build Custom Audiences off your D1 customer LTV data</td>
          <td>Cannot tell from data — but if it were happening, you'd see "LAL" or "Lookalike" in campaign names. Not seen.</td>
          <td><strong>Send Zap your top D1 customer email export. If they don't know what to do with it, fire them.</strong></td>
        </tr>
        <tr>
          <td>Send weekly performance reports with kill list</td>
          <td>86 waste campaigns running 7+ days at &lt;£200 — nobody reviewing</td>
          <td><strong>Demand: weekly report, every Friday, listing all campaigns by ROAS with a recommended kill list. If they can't, you don't have an agency, you have a bookkeeper.</strong></td>
        </tr>
      </tbody>
    </table>

    <h3>3.10 What you do this week (the Meta fix sequence)</h3>

    <div class="step">
      <div class="n">1</div>
      <div>
        <div class="what">Pull Zap's last 3 monthly invoices</div>
        <div class="how">Email Zap. Subject: "Need invoices Feb / Mar / Apr 2026 + spend dashboard exports". This gives us the actual spend numbers and we can calculate exact ROAS in 5 minutes.</div>
        <div class="who">WHO: Matty / Dave · WHEN: today</div>
      </div>
    </div>

    <div class="step">
      <div class="n">2</div>
      <div>
        <div class="what">Send Zap THIS list of {len(waste)} waste campaigns</div>
        <div class="how">Open <code>zap_campaigns.csv</code> in this audit pack — it has every campaign with revenue, AOV, days run. Email Zap: "Pause these {len(waste)} campaigns this Friday — they earned £{sum(c['revenue'] for c in waste):,.0f} combined over 90 days. We expect 80% of media spend to consolidate into the top 10 performers."</div>
        <div class="who">WHO: Conrad · WHEN: this week</div>
      </div>
    </div>

    <div class="step">
      <div class="n">3</div>
      <div>
        <div class="what">Install Meta CAPI yourself (don't wait for Zap)</div>
        <div class="how">Detailed steps in section 12.1. Buy PixelYourSite Pro for £100/year. 1-day install. Recovers iOS-blocked attribution overnight.</div>
        <div class="who">WHO: Conrad + WP dev · WHEN: this week</div>
      </div>
    </div>

    <div class="step">
      <div class="n">4</div>
      <div>
        <div class="what">Demand UTM hygiene from Zap</div>
        <div class="how">Email Zap: "Every active ad must have utm_content={{{{ad.name}}}} and utm_term={{{{adset.name}}}} dynamic params by Friday. We have {meta['malformed_utm_orders']:,} orders that came through Meta with utm_content="/" — that's 71% of our Meta volume invisible to your dashboard."</div>
        <div class="who">WHO: Conrad · WHEN: this week</div>
      </div>
    </div>

    <div class="step">
      <div class="n">5</div>
      <div>
        <div class="what">Decide: keep Zap, fix Zap, or fire Zap</div>
        <div class="how">Give Zap 30 days. By June 6: campaigns consolidated to ≤8 active, UTMs fixed, weekly performance reports landing in your inbox each Friday with named kill lists. If those don't happen, the data above proves you can run Meta yourself with PixelYourSite Pro + Advantage+ Shopping for £200/month tools cost vs whatever Zap is charging you. The dataset is the leverage.</div>
        <div class="who">WHO: Matty + Dave · WHEN: 30-day deadline</div>
      </div>
    </div>

    <h3>3.11 Where every marketing pound is best spent (final ranking)</h3>
    <table>
      <thead><tr><th>Channel</th><th class="num">Current ROAS</th><th class="num">Potential ROAS</th><th>Status</th><th>Recommendation</th></tr></thead>
      <tbody>
        <tr><td><strong>Email (Klaviyo)</strong></td><td class="num">∞ (cost ~£100/m)</td><td class="num">∞</td><td><span class="pill green">CASH COW</span></td><td>Send 18 weekly touches. Activate the 28 drafted flows.</td></tr>
        <tr><td><strong>SMS (Klaviyo)</strong></td><td class="num">3.15×</td><td class="num">5–6×</td><td><span class="pill green">SCALE</span></td><td>Triple volume on high-intent. Stop the full-list blast.</td></tr>
        <tr><td><strong>Meta (post-CAPI, post-cleanup)</strong></td><td class="num">{meta['fb_tagged_revenue']/72000:.1f}× (dashboard est.)</td><td class="num">15–20×</td><td><span class="pill gold">FIX FIRST</span></td><td>Kill {len(waste)} waste campaigns. Install CAPI. Then 2× the budget.</td></tr>
        <tr><td><strong>TikTok Ads</strong></td><td class="num">N/A (£12k revenue)</td><td class="num">2–4× (UGC)</td><td><span class="pill blue">TEST</span></td><td>£100/day Spark Ads. UK comp niche under-served on TikTok.</td></tr>
        <tr><td><strong>Google Search (brand)</strong></td><td class="num">N/A — barely running</td><td class="num">15×+</td><td><span class="pill red">URGENT MISS</span></td><td>£20/day brand-search before competitors do.</td></tr>
        <tr><td><strong>Google Performance Max</strong></td><td class="num">—</td><td class="num">3–6×</td><td><span class="pill blue">TEST W3</span></td><td>After Meta is fixed, test PMax £100/day for 2 weeks.</td></tr>
        <tr><td><strong>Postcard mailer (top D1 only)</strong></td><td class="num">—</td><td class="num">5–8×</td><td><span class="pill blue">TEST W4+</span></td><td>5,898 VIPs at £0.40 each = £2,400 in. Likely £15–20k incremental.</td></tr>
      </tbody>
    </table>

    <p style="margin-top: 30px;"><strong>Priority order for new spend each month:</strong></p>
    <ol>
      <li><strong>FREE first</strong> — Email + SMS quality (no £, just discipline)</li>
      <li><strong>Google brand-search protection</strong> (£600/month) — lowest-risk, highest-ROAS new spend</li>
      <li><strong>Meta with CAPI fixed + cleanup</strong> (scale £400/d → £1,500/d after data is reliable)</li>
      <li><strong>TikTok Spark Ads test</strong> (£3k/month)</li>
      <li><strong>Postcard to D1 monthly</strong> (£2.5k/month)</li>
      <li><strong>Google PMax</strong> (£3k/month)</li>
    </ol>
  </div>
</section>"""

# Now load the existing FINAL HTML and replace the Meta section
print("Loading existing NITROUS_AUDIT_FINAL.html...")
with open(f"{ROOT}/NITROUS_AUDIT_FINAL.html") as f:
    html = f.read()

# Find the existing meta section and replace it
import re
pattern = r'<section id="meta">.*?</section>'
match = re.search(pattern, html, re.DOTALL)
if match:
    print(f"Found existing Meta section ({len(match.group()):,} chars). Replacing...")
    new_html = html[:match.start()] + new_section + html[match.end():]
    # Update the cover sub line to reflect the Zap finding
    new_html = new_html.replace(
        'A complete forensic audit of 90 days of trading + the 4-week roadmap to return to <strong>£100,000/day</strong>.',
        'A complete forensic audit of 90 days of trading, including a campaign-by-campaign teardown of every Zap (Meta agency) ad — and the 4-week roadmap to return to <strong>£100,000/day</strong>.'
    )
    with open(f"{ROOT}/NITROUS_AUDIT_FINAL.html","w") as f:
        f.write(new_html)
    import os
    print(f"Updated NITROUS_AUDIT_FINAL.html: {os.path.getsize(f'{ROOT}/NITROUS_AUDIT_FINAL.html'):,} bytes")
else:
    print("ERROR: Could not find existing Meta section to replace.")