From spreadsheet to ERP.

I want you to build a new skill for me called "strategic-business-analysis".

Purpose: when I attach one or more business spreadsheets (sales, finance,
ops), the skill should:

1. Primary focus: lay out the STRATEGIC KPI panel this company should be
   tracking — not just listing numbers, but saying which ones matter for
   the decision.
2. Secondary focus: flag critical gaps that show up when the spreadsheets
   are cross-referenced.
3. Output: master KPI table + recommended dashboard architecture
   with 2 sections (Sales and Finance).

Style: executive, founder-friendly language, no unnecessary technical jargon.
Always execute code to validate calculations. Always cross-check 2 random
rows before concluding.

Build the complete skill: SKILL.md with frontmatter (name + description) +
structured instructions + output format + rules.
Then hand me the file ready to upload under Settings → Features → Skills.

You are a VP of operations with 20 years in the [YOUR COMPANY'S INDUSTRY] sector.
Your mission: lay out the STRATEGIC panel for the two attached spreadsheets.

<context>
Company: [COMPANY NAME] — [ONE-LINE DESCRIPTION]
Average monthly revenue: $ [VALUE]
Team: [N] people · [N] active sales reps
Important decision in the next 30 days: [SPECIFIC DECISION]
</context>

<files>
I attached (or these are in my connected Drive):
- Sales spreadsheet
- Finance spreadsheet

Identify the files by name and content. Use the connectors
to access them if they're on Drive.
</files>

<task>
PRIMARY FOCUS — Strategic KPI panel:
Define the 8-12 KPIs this company should be monitoring WEEKLY,
based on the business model + available data + industry benchmarks.

For each KPI:
- Name
- Formula (with exact column names from the spreadsheets)
- Current calculated value
- Industry benchmark (use your knowledge)
- Ideal review frequency
- Owner (role/area)
- Which dashboard section it belongs to (Sales or Finance)

SECONDARY FOCUS — Cross-spreadsheet gaps (3 items):
Cross the two spreadsheets and surface 3 gaps that only appear at that intersection
(e.g., the top sales customer vs. their aging in finance).

FINAL DELIVERABLE:
Recommended executive dashboard architecture with 2 sections:
"Sales" and "Finance". Specify which KPIs go in each section, which filters
should be global, and the right visualization for each KPI (card, line chart,
table, funnel, etc.).
</task>

<format>
1. Executive overview (1 paragraph, 5 lines)
2. Master KPI table (8-12 rows)
3. 3 cross-cutting gaps (sales × finance)
4. Dashboard architecture: Sales section + Finance section
</format>

Execute code to validate calculations. Cross-check 2 random rows from each spreadsheet
before concluding.

Generate PROMPT B — ready to paste into Lovable AFTER Prompt A.

GOAL OF PROMPT B: get Lovable to deliver a working MVP in a SINGLE
generation — no phases, no confirmations, no cron, no webhook,
no admin role, no manual "sync now". When the founder opens the
app for the first time, the dashboards must already be populated.

CONTEXT (mention briefly inside Prompt B):
- Lovable's NATIVE connectors are already ACTIVE (via Prompt A):
  Google Drive + Sheets OR Microsoft Excel (if MS 365 environment)
- Files already connected:
  · [TYPE 1]: [EXACT NAME OF FILE 1 ANALYZED IN THIS CONVERSATION]
  · [TYPE 2]: [EXACT NAME OF FILE 2 ANALYZED IN THIS CONVERSATION]
- Spreadsheet content already analyzed by me (Claude) in this conversation.
  KPIs, formulas, dimensions and architecture are ALREADY DECIDED below.
  DO NOT re-analyze. DO NOT rediscover the schema. DO NOT infer KPIs.

ENGINEERING RULES — INCLUDE EXPLICITLY IN PROMPT B:
- Deliver the complete MVP in one generation. No splitting into phases.
  No asking for confirmation. No "next turns".
- Stack: React + TypeScript + Tailwind + shadcn/ui + Recharts + Lovable
  Cloud (internal database and auth). No external Supabase, no Firebase.
- Auth: Lovable Cloud's native Google login. Single-user. NOT
  multi-tenant. No custom OAuth. No Service Account. No
  Google Cloud Console. No extended OAuth scopes.
- Render DIRECTLY from the Sheets connector. DO NOT cache to a database
  with upsert + PK. DO NOT use Watch Channel, webhooks, pg_cron, or sync
  edge functions. DO NOT build a "sync now" screen.
- KPIs are ALREADY DEFINED below. Use the exact formulas. Do not infer.
- If any data is missing in the spreadsheet, show an elegant Empty State
  (never crash, never blank screen, never "under construction").

ROBUSTNESS RULES — MANDATORY in Prompt B (they prevent the common bugs
you see in dashboards reading real spreadsheets with arbitrary structure):

R1. PARSE DATES IN LOCAL TIME ZONE
   - Create a helper `parseLocalDate(s: string): Date` that splits
     YYYY-MM-DD and builds Date with `new Date(y, m-1, d)` (local zone).
   - FORBIDDEN to call `new Date("YYYY-MM-DD")` directly — it parses as UTC
     and in negative zones (e.g. BRT/UTC-3) shifts back by 1 day.
   - Apply to EVERY date column identified in the spreadsheet,
     whatever it's called (date, issued, due, created_at).

R2. TEMPORAL ANCHOR = dataClock
   - Compute `dataClock = min(max(dataset dates), today)` at load time.
   - Windows for "current month / MTD / YTD" anchor on `dataClock`,
     not on `new Date()`. Guarantees the dashboard opens with data even
     if the spreadsheet has no rows for today.
   - Windows for "today / 7d / 14d / 30d / custom" anchor on real today.
     Don't substitute.

R3. DIFFERENTIATED PERIOD FALLBACK
   - If "month / MTD / YTD" comes back empty → fall back to the last
     period with data + show banner "Showing data from [period]".
   - If "today / 7d / 14d / 30d / custom" comes back empty →
     respect the user's choice. NEVER fall back silently.

R4. SAFETY NETS ON ANNUAL AGGREGATIONS / RANKINGS
   - Every YTD aggregation, annual Top-N ranking, annualized metric (DSO,
     turnover, etc.): if the anchor year is empty, fall back to
     "all historical data filtered by the same qualitative criteria"
     + a stale banner.
   - Portfolio metrics (delinquency, occupancy, utilization):
     if the range is empty, fall back to total portfolio. Never return 0
     because of a date-range mismatch.

R5. TWO-LAYER CACHE WITH REALTIME MODE
   - Server: 30s TTL by default. Realtime mode: dedupe in-flight by fileId
     + cheap `modifiedTime` check before downloading the XLSX +
     mini-TTL of 3s on metadata + resilient fallback if metadata fails.
   - Client: React Query staleTime 60s by default. Realtime: staleTime 0
     + refetchInterval 5s + bust flag on the call.
   - Manual "Refresh" button in each section header triggers refetch.

R6. DEFENSIVE ROUTING
   - `useSearch({ strict: false })` in EVERY global component
     (filters, header, sidebar).
   - `useNavigate` only when the destination route is mounted:
     guard `currentPath.startsWith(targetUrl)` OR use a URL_FOR map.
   - Filter updates use `replace: true` (don't push to history).

R7. SECRETS SERVER-ONLY
   - API keys (LOVABLE_API_KEY, GOOGLE_DRIVE_API_KEY or
     equivalents) live in server functions, never in the client bundle.
   - Components consume them via typed RPC.

R8. XLSX PARSING
   - `cellDates: true` on read.
   - Dates serialized as `YYYY-MM-DD` before returning to the client
     (consume with `parseLocalDate`).
   - Numbers: force numeric type, strip locale separators (e.g. "," → ".")
     where applicable; ignore cells with dirty text.

PROMPT B STRUCTURE (generate it in this order):

1. STACK + CONNECTED FILES
   - 2-3 lines: React+TS+Tailwind+shadcn+Recharts+Lovable Cloud
   - Exact file names + which section each one feeds
   - List the relevant tabs for each one

2. APP ARCHITECTURE
   - Fixed sidebar with the identified section items (min. 2)
   - "Settings" tucked away just to view connection status
   - Each section: KPI cards on top (4-5 grid) + 2-3 charts +
     1 analytical table at the bottom
   - Global filters in the header (date range + 1-2 spreadsheet dimensions)
   - Visual: dark mode, OKLCH blue + violet + mint, Inter Tight + Inter
   - Mobile: sidebar becomes a drawer, grids collapse to single column

3. KPIS BY SECTION
   - List the EXACT KPIs from the master table produced in this conversation
   - For each KPI: name · formula with specific column · format
     ($X.XXM · XX% · etc) · which component it appears in
   - MoM/YoY variation with green/red arrow
   - Semantic borders on KPIs deteriorating ≤ -5% MoM

4. DATA — DIRECT READ
   - Single server function `getSheetData(sheetName, range)` that uses
     Lovable's native Sheets connector
   - Apply ALL rules R1-R8 above in the domain layer (lib/calc.ts)
   - Frontend calls via React Query with staleTime 60s + auto-refetch
   - "Refresh" button in each section triggers manual refetch
   - Last update visible in the header ("X seconds ago")
   - NO intermediate database, NO upsert, NO watch channel

5. VISUAL STATES
   - Loading: Skeleton on every card and chart
   - Empty: Inbox icon + "No data for this filter/period"
   - Read error: yellow banner at the top + automatic retry
   - Stale: light blue banner "Showing data from [period]"
     when the period fallback (R3) kicks in

6. FINAL DELIVERABLE
   - Working login · sections POPULATED with real data ·
     filters operating · auto-refresh working ·
     mobile responsive · zero manual action required ·
     R1-R8 all implemented

RETURN: only the text of Prompt B, in English. No commentary
before or after. No questions. No approval checklist. No
"confirm the OAuth strategy". No timeline. Ready to paste.

Before building anything, do ONLY this:

1. Activate Lovable's NATIVE connectors: Google Drive + Google Sheets.
   DO NOT build your own OAuth flow. DO NOT use a Service Account JSON.
   DO NOT use external Supabase — use Lovable Cloud (internal database).

2. Once connected, list my Drive files and identify the TWO
   I'll be using:
   - Sales spreadsheet — exact name: [EXACT NAME OF YOUR SALES FILE].xlsx
   - Finance spreadsheet — exact name: [EXACT NAME OF YOUR FINANCE FILE].xlsx

3. Confirm access to each file. For each one, show:
   - Exact name found on Drive
   - Last modified date
   - Number of tabs and the name of each tab

4. DO NOT build the interface, database schema, or sync logic
   yet. Just respond confirming both files are connected.

Expected response (format):
"Connected.
- [sales-name].xlsx · modified on [date] · N tabs: [list]
- [finance-name].xlsx · modified on [date] · N tabs: [list]
Ready to receive the architecture prompt."

When I send the next prompt, it will contain the complete architecture.
Build on top of these ALREADY CONNECTED files, no manual onboarding
of Spreadsheet IDs and no Service Account field.

The dashboard MVP is approved. Now let's evolve this SAME application into a
lean ERP. Reuse ALL the architecture, connections, and Lovable Cloud tables
already created — don't recreate anything.

Add 3 new modules to the sidebar (below Sales and Finance):

1. SALES OPERATIONS
   - "New Order" screen: form with customer (autocomplete from the existing
     Customers table) + product line items + automatic subtotal/discount/total/margin
     calculation
   - Orders list with filter by status (Draft / Invoiced / Cancelled)
   - "Invoice" button changes status and writes to Lovable Cloud
   - Each rep only sees their own orders

2. INVENTORY
   - Table SKU · description · current balance · reorder point · status (green/orange/red)
   - SKUs below the reorder point in red with a "Suggest purchase" button
   - The suggestion groups by supplier and creates a draft PO

3. OPERATIONAL FINANCE
   - Accounts Receivable screen: list with color-coded aging (current / 30 / 60 / 90 / 90+)
   - "Reminder" button opens a modal with ready-to-send copy
   - Semi-automatic reconciliation (upload OFX/CSV → tries to match against open items)

REUSE:
- Lovable Cloud tables already created for the dashboards
- Same Google authentication
- Same palette + typography
- Add new tables ONLY for what doesn't exist yet

Hand back the app running with the 3 new modules visible in the sidebar.
Don't touch the original Sales and Finance sections — they stay as
read-only dashboards. The new modules are the operational layer.