import openpyxl
from openpyxl.styles import (
    PatternFill, Font, Alignment, Border, Side
)
from openpyxl.utils import get_column_letter

# ── Colors ────────────────────────────────────────────────────────────────────
BLACK   = "18181B"   # Agency Black
CHARCOAL= "27272A"   # Charcoal
ZINC    = "52525B"   # Zinc 600
WHITE   = "FAFAFA"   # White
LGRAY   = "E4E4E7"   # Light divider
DGRAY   = "3F3F46"   # Dark row alt

# ── Helpers ───────────────────────────────────────────────────────────────────
def hfill(hex_str): return PatternFill("solid", fgColor=hex_str)
def font(color=WHITE, bold=False, sz=10, name="Calibri"):
    return Font(color=color, bold=bold, size=sz, name=name)
def wrap_align(h="left", v="top"):
    return Alignment(horizontal=h, vertical=v, wrap_text=True)
def thin_border():
    s = Side(style="thin", color=LGRAY)
    return Border(left=s, right=s, top=s, bottom=s)

def style_header_row(ws, row, cols, bg=BLACK, fg=WHITE, sz=11, bold=True):
    for col in range(1, cols + 1):
        c = ws.cell(row=row, column=col)
        c.fill = hfill(bg)
        c.font = font(fg, bold, sz)
        c.alignment = wrap_align("center", "center")
        c.border = thin_border()

def style_data_row(ws, row, cols, bg=CHARCOAL, fg=WHITE):
    for col in range(1, cols + 1):
        c = ws.cell(row=row, column=col)
        c.fill = hfill(bg)
        c.font = font(fg, False, 9)
        c.alignment = wrap_align()
        c.border = thin_border()

def set_col_widths(ws, widths):
    for i, w in enumerate(widths, 1):
        ws.column_dimensions[get_column_letter(i)].width = w

def set_row_height(ws, row, h):
    ws.row_dimensions[row].height = h

# ── DATA ──────────────────────────────────────────────────────────────────────

posts = [
    # ── MONDAY — Brand Awareness ──────────────────────────────────────────────
    {
        "day": "Monday", "post": 1, "format": "Single Image",
        "pillar": "Brand Awareness",
        "hook": "You've been adapting to your software for years.",
        "headline": "You've been adapting to your software for years.\nIt should be the other way around.",
        "subline": "Custom-built. $799/month. Yours to keep.",
        "slides": "N/A — Single Image",
        "caption": (
            "Your business is unique.\n"
            "Your software should be too.\n\n"
            "Custom-built. Starting at $799/month.\n\n"
            "Tell us your problem. We'll build it.\n"
            "→ agency.symph.co"
        ),
        "cta": "Tell us your problem. We'll build it. → agency.symph.co",
        "hashtags": "#CustomSoftware #FounderLife #BusinessTools #SaaS #SmallBusiness #OperationsLeader",
        "logo_element": "Angular bracket as text frame — bracket wraps the headline block, centered composition. Structural, architectural feel.",
        "visual_notes": "Agency Black bg. Bold white Inter headline. Bracket in Zinc #52525b, thin stroke. Blueprint grid at 5% opacity.",
    },
    {
        "day": "Monday", "post": 2, "format": "Carousel (5 slides)",
        "pillar": "Brand Awareness",
        "hook": "Your software doesn't fit your business. That's not your fault.",
        "headline": "Your software doesn't fit your business.\nThat's not your fault — it was never built for you.",
        "subline": "Swipe to see what to do instead →",
        "slides": (
            "S1 (Cover): Your software doesn't fit your business. That's not your fault — it was never built for you. | Swipe →\n"
            "S2: There's a new type of software company. | Custom-built. Subscription pricing. Starting at $799/month.\n"
            "S3: Not a $100K project. | Not a 6-month wait. Software that fits — built in days.\n"
            "S4: You own everything we build. | Your IP. Your codebase. Your competitive edge.\n"
            "S5 (CTA): Tell us what you need. | We'll build it. → agency.symph.co"
        ),
        "caption": (
            "Off-the-shelf software wasn't built for you.\n"
            "Custom dev quotes $100K and takes 6 months.\n\n"
            "There's a third option.\n\n"
            "Swipe →\n"
            "→ agency.symph.co"
        ),
        "cta": "Tell us your problem. We'll build it. → agency.symph.co",
        "hashtags": "#TheAgency #CustomSoftware #Startups #FounderTools #BusinessAutomation #SubscriptionSaaS",
        "logo_element": "Angular bracket repeated as tessellating background pattern — fades at edges. Blueprint grid texture layered underneath.",
        "visual_notes": "Consistent Agency Black across all slides. Slide number in Zinc top-left. Arrow hand-drawn on cover slide.",
    },
    {
        "day": "Monday", "post": 3, "format": "Quote Card",
        "pillar": "Brand Awareness",
        "hook": "You've been making your business fit the software.",
        "headline": '"You\'ve been making your business fit the software.\nWhat if the software fit your business?"',
        "subline": "— The Agency by Symph",
        "slides": "N/A — Quote Card",
        "caption": (
            "That's the only question that matters.\n\n"
            "Custom-built software. $799/month.\n\n"
            "Tell us your problem. We'll build it.\n"
            "→ agency.symph.co"
        ),
        "cta": "Tell us your problem. We'll build it. → agency.symph.co",
        "hashtags": "#CustomSoftware #BuildInPublic #FounderLife #TheAgency #SaaS",
        "logo_element": "Angular bracket as corner accent — small, bottom-right, Zinc color. Understated brand mark placement.",
        "visual_notes": "Agency Black bg. No texture — let type breathe. Oversized Zinc opening quotation mark, decorative. Attribution in small Zinc.",
    },

    # ── TUESDAY — The Traditional Agency Trap ────────────────────────────────
    {
        "day": "Tuesday", "post": 4, "format": "Single Image",
        "pillar": "Problem / Pain",
        "hook": "The custom software quote was $150,000.",
        "headline": "The custom software quote was $150,000.\nThe timeline was 8 months.\nThe scope kept changing.",
        "subline": "Founders deserve better than that.",
        "slides": "N/A — Single Image",
        "caption": (
            "That's not a project. That's a risk.\n\n"
            "Founders deserve better than that.\n\n"
            "The Agency. Custom software. $799/month.\n\n"
            "Tell us your problem. We'll build it.\n"
            "→ agency.symph.co"
        ),
        "cta": "Tell us your problem. We'll build it. → agency.symph.co",
        "hashtags": "#CustomSoftware #FounderProblems #SaaS #AgencyLife #SmallBusiness #TechStartup",
        "logo_element": "Angular bracket cropped at top edge — oversized, partially visible, creates visual tension. Like looking through an architectural opening.",
        "visual_notes": "Agency Black bg. Headline stacked for drama. Each line slightly smaller. Bracket in Zinc, heavy stroke, bleeds off top edge.",
    },
    {
        "day": "Tuesday", "post": 5, "format": "Carousel (5 slides)",
        "pillar": "Problem / Pain",
        "hook": "The traditional software agency trap.",
        "headline": "The traditional software agency trap.\n(And how founders keep falling into it.)",
        "subline": "Swipe to see all 3 traps →",
        "slides": (
            "S1 (Cover): The traditional software agency trap. (And how founders keep falling into it.) | Swipe →\n"
            "S2 — Trap #1: The $100K proposal | You ask for a quote. You get a 40-page proposal and a $100K number. Most founders never even reply.\n"
            "S3 — Trap #2: The 6-month timeline | You agree. Work begins. Months pass. The software still isn't done — and now they want more for 'scope changes.'\n"
            "S4 — Trap #3: You don't own the code | The project ends. The code is theirs. If you want changes? Start over. Pay again.\n"
            "S5 (CTA): There's a different way. | Custom software. Subscription. You own everything. $799/month. Tell us your problem. We'll build it. → agency.symph.co"
        ),
        "caption": (
            "Most founders have been burned by at least one of these.\n\n"
            "The Agency was built for people who refuse to fall for it again.\n\n"
            "Swipe →\n"
            "→ agency.symph.co"
        ),
        "cta": "Tell us your problem. We'll build it. → agency.symph.co",
        "hashtags": "#FounderProblems #CustomSoftware #SoftwareAgency #SmallBusiness #TechFounder #SaaS",
        "logo_element": "Angular bracket repeated 3x in diminishing sizes — creates perspective/depth. Each trap gets one bracket scale. Structural rhythm.",
        "visual_notes": "Trap numbers in Zinc. Bracket sizes: S2=large, S3=medium, S4=small. Bold white label per trap. Red strikethrough implied by stacking.",
    },
    {
        "day": "Tuesday", "post": 6, "format": "Quote Card",
        "pillar": "Problem / Pain",
        "hook": "A $100K software quote isn't a price. It's a stop sign.",
        "headline": '"A $100K software quote isn\'t a price.\nIt\'s a stop sign."',
        "subline": "— The Agency by Symph",
        "slides": "N/A — Quote Card",
        "caption": (
            "You shouldn't need a board approval to fix a broken workflow.\n\n"
            "Custom software. $799/month.\n\n"
            "Tell us your problem. We'll build it.\n"
            "→ agency.symph.co"
        ),
        "cta": "Tell us your problem. We'll build it. → agency.symph.co",
        "hashtags": "#FounderLife #CustomSoftware #SoftwareAgency #TechFounder #SmallBusiness",
        "logo_element": "Angular bracket as oversized quotation mark surrogate — replaces opening quote marks. Premium, architectural, ownable.",
        "visual_notes": "Agency Black bg. Bracket at 300% scale functions as decorative quote opener. No texture. Bold type only.",
    },

    # ── WEDNESDAY — Solution ──────────────────────────────────────────────────
    {
        "day": "Wednesday", "post": 7, "format": "Single Image",
        "pillar": "Solution",
        "hook": "We build the software your business actually needs.",
        "headline": "We build the software your business actually needs.",
        "subline": "In days. Not months. For less than your marketing budget.",
        "slides": "N/A — Single Image",
        "caption": (
            "Not a template.\n"
            "Not a workaround.\n"
            "Not someone else's workflow forced onto yours.\n\n"
            "Custom. Fast. $799/month.\n\n"
            "Tell us your problem. We'll build it.\n"
            "→ agency.symph.co"
        ),
        "cta": "Tell us your problem. We'll build it. → agency.symph.co",
        "hashtags": "#CustomSoftware #SoftwareDevelopment #FounderTools #SaaS #Startups #TheAgency",
        "logo_element": "Angular bracket as centered frame — bracket surrounds the headline like an architectural drawing callout. Clean, structural.",
        "visual_notes": "Agency Black bg. Headline inside bracket frame. Subline below bracket in Zinc. Blueprint grid at 5% opacity.",
    },
    {
        "day": "Wednesday", "post": 8, "format": "Carousel (5 slides)",
        "pillar": "Solution",
        "hook": "How The Agency builds your software.",
        "headline": "How The Agency builds your software.\n(And why it's nothing like what you've tried before.)",
        "subline": "3 steps. Days, not months. →",
        "slides": (
            "S1 (Cover): How The Agency builds your software. (And why it's nothing like what you've tried before.) | Swipe →\n"
            "S2 — Step 01: Tell us your problem | No 40-page RFPs. No discovery fees. Tell us what's broken — and what you need.\n"
            "S3 — Step 02: We build it | AI velocity + human engineering. Your software ships in days. Not months.\n"
            "S4 — Step 03: You approve | Test it. Use it. Request changes. Updates and improvements are included.\n"
            "S5 (CTA): You own everything. | Your IP. Your codebase. Starting at $799/month. Tell us your problem. We'll build it. → agency.symph.co"
        ),
        "caption": (
            "01 → AI builds.\n"
            "02 → Humans review.\n"
            "03 → You approve.\n\n"
            "That's the whole model.\n\n"
            "Tell us your problem. We'll build it.\n"
            "→ agency.symph.co"
        ),
        "cta": "Tell us your problem. We'll build it. → agency.symph.co",
        "hashtags": "#CustomSoftware #HowItWorks #SaaS #FounderTools #AIEngineering #TheAgency",
        "logo_element": "Angular bracket as step number frame — each step (01, 02, 03) is enclosed in a bracket. Consistent rhythm across slides.",
        "visual_notes": "Step numbers large in Zinc. Bracket frames each number. Body copy below. Final slide: bracket at full scale as hero element.",
    },
    {
        "day": "Wednesday", "post": 9, "format": "Quote Card",
        "pillar": "Solution",
        "hook": "When it doesn't exist, we build it.",
        "headline": '"When it doesn\'t exist,\nwe build it."',
        "subline": "— The Agency by Symph",
        "slides": "N/A — Quote Card",
        "caption": (
            "That's the whole business model.\n\n"
            "No $100K quotes. No 6-month timelines.\n"
            "Just your software, built fast.\n\n"
            "Tell us your problem. We'll build it.\n"
            "→ agency.symph.co"
        ),
        "cta": "Tell us your problem. We'll build it. → agency.symph.co",
        "hashtags": "#WhenItDoesntExist #CustomSoftware #TheAgency #BuildInPublic #FounderLife",
        "logo_element": "Angular bracket as full-bleed low-opacity background pattern — bracket tessellated at 8% opacity behind quote text. Subtle texture.",
        "visual_notes": "Agency Black bg. Bracket pattern as background. Quote text floats above. Clean, minimal foreground.",
    },

    # ── THURSDAY — Comparison ─────────────────────────────────────────────────
    {
        "day": "Thursday", "post": 10, "format": "Single Image",
        "pillar": "Comparison",
        "hook": "Traditional agency: $100K. 6 months. You don't own the code.",
        "headline": "Traditional agency:\n$100K. 6 months. You don't own the code.\n\nThe Agency:\n$799/month. Days. You own everything.",
        "subline": "Same result. Completely different model.",
        "slides": "N/A — Single Image",
        "caption": (
            "One is a project. One is a subscription.\n\n"
            "Same custom software. Half the risk.\n"
            "A fraction of the cost.\n\n"
            "Tell us your problem. We'll build it.\n"
            "→ agency.symph.co"
        ),
        "cta": "Tell us your problem. We'll build it. → agency.symph.co",
        "hashtags": "#CustomSoftware #SoftwareAgency #FounderLife #SaaS #Comparison #TheAgency",
        "logo_element": "Angular bracket as split composition divider — bracket sits center-vertical, dividing left (traditional) from right (The Agency). Two worlds.",
        "visual_notes": "Left half: Charcoal bg, Zinc text (muted = old). Right half: Agency Black bg, white text (sharp = new). Bracket as divider in Zinc.",
    },
    {
        "day": "Thursday", "post": 11, "format": "Carousel (5 slides)",
        "pillar": "Comparison",
        "hook": "Traditional agency vs. The Agency.",
        "headline": "Traditional agency vs. The Agency.\n(A comparison that changes how you think about software.)",
        "subline": "Swipe to see every difference →",
        "slides": (
            "S1 (Cover): Traditional agency vs. The Agency. A comparison that changes how you think about software. | Swipe →\n"
            "S2 — PRICE: Traditional: $50K–$200K project fee | The Agency: $799–$1,499/month\n"
            "S3 — SPEED: Traditional: 3–8 months | The Agency: Days to weeks\n"
            "S4 — OWNERSHIP: Traditional: Yours in theory. Theirs in practice. | The Agency: Your IP. Your code. Full stop.\n"
            "S5 — RISK + CTA: Traditional: 100% upfront. No guarantee. | The Agency: Cancel anytime. No lock-in. | Which one would you choose? → agency.symph.co"
        ),
        "caption": (
            "We didn't reinvent custom software.\n\n"
            "We just removed everything that made it impossible for small businesses to afford.\n\n"
            "Swipe to see the difference →\n"
            "→ agency.symph.co"
        ),
        "cta": "Tell us your problem. We'll build it. → agency.symph.co",
        "hashtags": "#CustomSoftware #SoftwareAgency #Comparison #FounderTools #SmallBusiness #SaaS",
        "logo_element": "Angular bracket as column divider — bracket runs vertically center of comparison slides, separating old vs new. Clean, architectural split.",
        "visual_notes": "Left column (traditional): muted Zinc text. Right column (The Agency): bold white text. Category label in small caps above. Bracket in Zinc center.",
    },
    {
        "day": "Thursday", "post": 12, "format": "Quote Card",
        "pillar": "Comparison",
        "hook": "You shouldn't have to choose between software that fits and software you can afford.",
        "headline": '"You shouldn\'t have to choose\nbetween software that fits\nand software you can afford."',
        "subline": "— The Agency by Symph",
        "slides": "N/A — Quote Card",
        "caption": (
            "The traditional agency model made that a choice.\n\n"
            "We removed the tradeoff.\n\n"
            "Custom software. $799/month.\n\n"
            "Tell us your problem. We'll build it.\n"
            "→ agency.symph.co"
        ),
        "cta": "Tell us your problem. We'll build it. → agency.symph.co",
        "hashtags": "#CustomSoftware #FounderLife #SaaS #AffordableSoftware #TheAgency",
        "logo_element": "Two angular brackets facing each other — tension/resolution visual. Left bracket opens toward quote. Right bracket closes after it. Dialectic framing.",
        "visual_notes": "Agency Black bg. Brackets in Zinc, facing inward. Quote centered between them. Attribution below. No other decoration.",
    },

    # ── FRIDAY — Credibility + CTA ────────────────────────────────────────────
    {
        "day": "Friday", "post": 13, "format": "Single Image",
        "pillar": "Credibility + CTA",
        "hook": "The software you've been waiting for someone to build.",
        "headline": "The software you've been waiting for someone to build.",
        "subline": "It already exists. We just need to build it for you.",
        "slides": "N/A — Single Image",
        "caption": (
            "Most founders assume custom software is out of reach.\n\n"
            "They've been quoted $100K. Burned by long timelines.\n\n"
            "The Agency was built for exactly them.\n\n"
            "Custom-built. $799/month. Yours to keep.\n\n"
            "Tell us your problem. We'll build it.\n"
            "→ agency.symph.co"
        ),
        "cta": "Tell us your problem. We'll build it. → agency.symph.co",
        "hashtags": "#CustomSoftware #FounderLife #SaaS #BuildForYou #TheAgency #Startups",
        "logo_element": "Angular bracket as large architectural hero element — bracket at 400% scale, centered, agency mark as monument. Confident, ownable.",
        "visual_notes": "Agency Black bg. Bracket as primary visual, Zinc, centered large. Headline below bracket. Subline smaller below that. Maximum presence.",
    },
    {
        "day": "Friday", "post": 14, "format": "Carousel (5 slides)",
        "pillar": "Credibility + CTA",
        "hook": "What happens when you stop adapting to your software.",
        "headline": "What happens when you stop adapting to your software.\n(Real answers from founders who made the switch.)",
        "subline": "Swipe to read their stories →",
        "slides": (
            "S1 (Cover): What happens when you stop adapting to your software. Real answers from founders who made the switch. | Swipe →\n"
            "S2: 'We had three tools doing what one custom system should do. The data was always wrong somewhere.' — Operations Director, 50-person company\n"
            "S3: 'We got a $120,000 quote from an agency. We walked away. Now we use The Agency.' — CEO, SaaS company\n"
            "S4: 'I didn't think we were big enough for custom software. Turns out $100K was the problem, not our size.' — Founder, e-commerce brand\n"
            "S5 (CTA): If your business has a workflow no software handles — we built this for you. | Starting at $799/month. Tell us your problem. We'll build it. → agency.symph.co"
        ),
        "caption": (
            "Founders who were told custom software was too expensive.\n\n"
            "It wasn't. It was just the wrong agency.\n\n"
            "Tell us your problem. We'll build it.\n"
            "→ agency.symph.co"
        ),
        "cta": "Tell us your problem. We'll build it. → agency.symph.co",
        "hashtags": "#CustomSoftware #FounderStories #SaaS #CustomerSuccess #TheAgency #SmallBusiness",
        "logo_element": "Angular bracket as speech bubble frame — bracket wraps each testimonial quote. Architectural callout. Each slide: one bracket, one voice.",
        "visual_notes": "Testimonial text in large white Inter. Role/company in Zinc below. Bracket in Zinc frames the quote block. Blueprint grid at 5% on cover slide.",
    },
    {
        "day": "Friday", "post": 15, "format": "Quote Card",
        "pillar": "Credibility + CTA",
        "hook": "Your business is too unique to run on software built for everyone.",
        "headline": '"Your business is too unique\nto run on software\nbuilt for everyone."',
        "subline": "— The Agency by Symph",
        "slides": "N/A — Quote Card",
        "caption": (
            "That's the problem we solve.\n\n"
            "Custom-built. Fast. $799/month.\n\n"
            "Tell us your problem. We'll build it.\n"
            "→ agency.symph.co"
        ),
        "cta": "Tell us your problem. We'll build it. → agency.symph.co",
        "hashtags": "#CustomSoftware #BuildForYou #FounderLife #TheAgency #SaaS #WhenItDoesntExist",
        "logo_element": "Angular bracket bold, oversized, partially cropped — maximum impact. Bracket bleeds off right edge. Bold statement. Week-closing confidence.",
        "visual_notes": "Agency Black bg. Bracket large, partially off-canvas right side. Quote left-aligned, white. Attribution below in Zinc. No other elements.",
    },
]

# ── BUILD WORKBOOK ─────────────────────────────────────────────────────────────

wb = openpyxl.Workbook()

# ─────────────────────────────────────────────────────────────────────────────
# SHEET 1: Overview Calendar
# ─────────────────────────────────────────────────────────────────────────────
ws1 = wb.active
ws1.title = "📅 Overview"

# Title
ws1.merge_cells("A1:H1")
c = ws1["A1"]
c.value = "THE AGENCY — FB/IG CONTENT CALENDAR (Week 1)"
c.fill = hfill(BLACK)
c.font = Font(color=WHITE, bold=True, size=14, name="Calibri")
c.alignment = wrap_align("center", "center")
ws1.row_dimensions[1].height = 36

# Headers
headers = ["#", "Day", "Pillar", "Format", "Hook (First Line)", "CTA", "Logo Element Use", "Key Visual Note"]
for i, h in enumerate(headers, 1):
    ws1.cell(row=2, column=i, value=h)
style_header_row(ws1, 2, len(headers), ZINC, WHITE)

# Day colors
day_colors = {
    "Monday":    "1C1C20",
    "Tuesday":   "1F1F25",
    "Wednesday": "1C2220",
    "Thursday":  "1C1E22",
    "Friday":    "211C22",
}

for idx, p in enumerate(posts, 1):
    row = idx + 2
    bg = day_colors.get(p["day"], CHARCOAL)
    vals = [
        idx,
        p["day"],
        p["pillar"],
        p["format"],
        p["hook"],
        p["cta"],
        p["logo_element"][:80] + "…" if len(p["logo_element"]) > 80 else p["logo_element"],
        p["visual_notes"][:80] + "…" if len(p["visual_notes"]) > 80 else p["visual_notes"],
    ]
    for col, val in enumerate(vals, 1):
        c = ws1.cell(row=row, column=col, value=val)
        c.fill = hfill(bg)
        c.font = font(WHITE, bold=(col == 2), sz=9)
        c.alignment = wrap_align()
        c.border = thin_border()
    ws1.row_dimensions[row].height = 40

set_col_widths(ws1, [4, 12, 20, 18, 42, 40, 45, 45])
ws1.freeze_panes = "A3"

# ─────────────────────────────────────────────────────────────────────────────
# SHEET 2: Full Copy (all posts)
# ─────────────────────────────────────────────────────────────────────────────
ws2 = wb.create_sheet("✍️ Full Copy")

ws2.merge_cells("A1:J1")
c = ws2["A1"]
c.value = "THE AGENCY — FULL COPY (All 15 Posts)"
c.fill = hfill(BLACK)
c.font = Font(color=WHITE, bold=True, size=14, name="Calibri")
c.alignment = wrap_align("center", "center")
ws2.row_dimensions[1].height = 36

copy_headers = ["#", "Day", "Format", "Pillar", "Headline", "Subline", "Slide-by-Slide Copy", "Caption", "CTA", "Hashtags"]
for i, h in enumerate(copy_headers, 1):
    ws2.cell(row=2, column=i, value=h)
style_header_row(ws2, 2, len(copy_headers), ZINC, WHITE)

for idx, p in enumerate(posts, 1):
    row = idx + 2
    bg = day_colors.get(p["day"], CHARCOAL)
    vals = [
        idx,
        p["day"],
        p["format"],
        p["pillar"],
        p["headline"],
        p["subline"],
        p["slides"],
        p["caption"],
        p["cta"],
        p["hashtags"],
    ]
    for col, val in enumerate(vals, 1):
        c = ws2.cell(row=row, column=col, value=val)
        c.fill = hfill(bg)
        c.font = font(WHITE, bold=(col == 2), sz=9)
        c.alignment = wrap_align()
        c.border = thin_border()
    ws2.row_dimensions[row].height = 120

set_col_widths(ws2, [4, 12, 18, 20, 36, 30, 60, 40, 40, 40])
ws2.freeze_panes = "A3"

# ─────────────────────────────────────────────────────────────────────────────
# SHEET 3: Visual Briefs
# ─────────────────────────────────────────────────────────────────────────────
ws3 = wb.create_sheet("🎨 Visual Briefs")

ws3.merge_cells("A1:G1")
c = ws3["A1"]
c.value = "THE AGENCY — VISUAL BRIEFS & LOGO ELEMENT SYSTEM"
c.fill = hfill(BLACK)
c.font = Font(color=WHITE, bold=True, size=14, name="Calibri")
c.alignment = wrap_align("center", "center")
ws3.row_dimensions[1].height = 36

vis_headers = ["#", "Day", "Format", "Logo Element — How Used", "Visual Notes", "Colors", "Typography"]
for i, h in enumerate(vis_headers, 1):
    ws3.cell(row=2, column=i, value=h)
style_header_row(ws3, 2, len(vis_headers), ZINC, WHITE)

TYPE_NOTES = {
    "Single Image":      "Display: Inter Bold 700 (36–48pt) | Subline: Inter Regular 400 (16pt) | URL: Inter 400 Zinc",
    "Carousel (5 slides)":"Cover: Inter Bold 700 (32pt) | Slide headline: Inter Bold 600 (24pt) | Body: Inter 400 (14pt)",
    "Quote Card":        "Quote: Inter Bold 700 (28–36pt) | Attribution: Inter 400 (12pt) Zinc | No body copy",
}

for idx, p in enumerate(posts, 1):
    row = idx + 2
    bg = day_colors.get(p["day"], CHARCOAL)
    color_note = f"Bg: #{BLACK} | Headline: #{WHITE} | Accents: #{ZINC} | (Blueprint grid 5% opacity where noted)"
    vals = [
        idx,
        p["day"],
        p["format"],
        p["logo_element"],
        p["visual_notes"],
        color_note,
        TYPE_NOTES.get(p["format"], "Inter family throughout"),
    ]
    for col, val in enumerate(vals, 1):
        c = ws3.cell(row=row, column=col, value=val)
        c.fill = hfill(bg)
        c.font = font(WHITE, bold=(col == 2), sz=9)
        c.alignment = wrap_align()
        c.border = thin_border()
    ws3.row_dimensions[row].height = 80

set_col_widths(ws3, [4, 12, 18, 50, 45, 45, 40])
ws3.freeze_panes = "A3"

# ─────────────────────────────────────────────────────────────────────────────
# SHEET 4: Posting Schedule
# ─────────────────────────────────────────────────────────────────────────────
ws4 = wb.create_sheet("🗓️ Schedule")

ws4.merge_cells("A1:F1")
c = ws4["A1"]
c.value = "THE AGENCY — WEEKLY POSTING SCHEDULE (3x/day)"
c.fill = hfill(BLACK)
c.font = Font(color=WHITE, bold=True, size=14, name="Calibri")
c.alignment = wrap_align("center", "center")
ws4.row_dimensions[1].height = 36

sched_headers = ["Day", "Time Slot", "Post #", "Format", "Pillar", "Hook (First Line)"]
for i, h in enumerate(sched_headers, 1):
    ws4.cell(row=2, column=i, value=h)
style_header_row(ws4, 2, len(sched_headers), ZINC, WHITE)

time_slots = ["9:00 AM", "12:00 PM", "6:00 PM"]
days = ["Monday", "Tuesday", "Wednesday", "Thursday", "Friday"]
post_idx = 0
sched_row = 3

for day in days:
    day_posts = [p for p in posts if p["day"] == day]
    bg = day_colors.get(day, CHARCOAL)
    for slot_i, (slot, p) in enumerate(zip(time_slots, day_posts)):
        post_idx += 1
        vals = [day if slot_i == 0 else "", slot, post_idx, p["format"], p["pillar"], p["hook"]]
        for col, val in enumerate(vals, 1):
            c = ws4.cell(row=sched_row, column=col, value=val)
            c.fill = hfill(bg)
            c.font = font(WHITE, bold=(col == 1 and slot_i == 0), sz=9)
            c.alignment = wrap_align()
            c.border = thin_border()
        ws4.row_dimensions[sched_row].height = 30
        sched_row += 1

set_col_widths(ws4, [14, 12, 8, 20, 22, 55])
ws4.freeze_panes = "A3"

# ─────────────────────────────────────────────────────────────────────────────
# SAVE
# ─────────────────────────────────────────────────────────────────────────────
output_path = "/home/aria/marketing-assets/uploads/the-agency-fb-ig-content-calendar.xlsx"
import os
os.makedirs(os.path.dirname(output_path), exist_ok=True)
wb.save(output_path)
print(f"Saved to: {output_path}")
