// AI analyst — OpenAI function calling over the analytics data layer.
// The model receives tools that map to live GA4 queries; it decides which
// to call, we execute them, and it writes the answer from real numbers.

import {
  loadSites, totals, timeseries, breakdown, filteredBreakdown, pageDetail,
  portfolioTotals, portfolioTimeseries, portfolioBreakdown,
  resolveRange, previousRange, searchTerms, pageFlows, outboundClicks,
  channelMix, miscPages, pageTrafficSources, outboundJourneys, mediaYearly, fileDownloadsRanked,
} from './ga4.js';
import { trafficPeaks } from './peaks.js';
import { computeInsights } from './insights.js';
import { emailOverview } from './email.js';
import { monitoringOverview } from './monitoring.js';
import { markdownReport, pptxReport } from './report.js';
import { legacyOverview, legacyRelease, searchReleases, campaignRecipients, searchPeople, personActivity, journalistRanking, emailEngagers, searchCompanies, quietReleases, uploadStats, searchMediaAssets, fullList, mediaDownloadsDated, topSpotComparison, mediaYearPlatform, modelInterest, emailIntelligence } from './legacy.js';
import { reconReport, unifiedReleases } from './releases.js';
import { datedAssetActivity } from './snapshots.js';
import { searchPages } from './ga4.js';

const MODEL = process.env.OPENAI_MODEL || 'gpt-5.6-terra';
// GPT-5.x models require reasoning_effort 'none' when using function tools on
// /v1/chat/completions. Override with OPENAI_REASONING_EFFORT in .env, or set
// it to 'omit' to leave the parameter out entirely (for models that reject it).
const REASONING = (process.env.OPENAI_REASONING_EFFORT || 'none').toLowerCase();

const tools = [
  {
    type: 'function',
    function: {
      name: 'list_sites',
      description: 'List all websites (id, name, url) tracked in this dashboard.',
      parameters: { type: 'object', properties: {} },
    },
  },
  {
    type: 'function',
    function: {
      name: 'get_totals',
      description:
        'Get total metrics (sessions, users, new users, page views, engaged sessions, conversions, engagement rate, avg session duration) for one site or the whole portfolio over a date range. Also returns the previous equal-length period for comparison.',
      parameters: {
        type: 'object',
        properties: {
          site_id: { type: 'string', description: 'Site id from list_sites, or "all" for every site combined.' },
          range: { type: 'string', description: 'One of 7d, 28d, 90d, 12m, mtd, or a custom "YYYY-MM-DD..YYYY-MM-DD".' },
        },
        required: ['site_id', 'range'],
      },
    },
  },
  {
    type: 'function',
    function: {
      name: 'get_timeseries',
      description: 'Daily metrics (sessions, users, conversions, page views) for one site or "all", to analyse trends, spikes and dips.',
      parameters: {
        type: 'object',
        properties: {
          site_id: { type: 'string' },
          range: { type: 'string', description: '7d, 28d, 90d, 12m, mtd, or "YYYY-MM-DD..YYYY-MM-DD".' },
        },
        required: ['site_id', 'range'],
      },
    },
  },
  {
    type: 'function',
    function: {
      name: 'get_breakdown',
      description: 'Break sessions down by a dimension for one site or "all": traffic channels, top pages, devices, countries, or sources.',
      parameters: {
        type: 'object',
        properties: {
          site_id: { type: 'string' },
          dimension: {
            type: 'string',
            enum: ['sessionDefaultChannelGroup', 'pagePath', 'deviceCategory', 'country', 'sessionSource'],
          },
          range: { type: 'string' },
          limit: { type: 'integer', description: 'Max rows, default 12.' },
          path_prefix: {
            type: 'string',
            description: "pagePath only: return pages UNDER this path, e.g. '/releases' lists /releases/1652, /releases/1634… Use to drill into a section of a site.",
          },
        },
        required: ['site_id', 'dimension', 'range'],
      },
    },
  },
  {
    type: 'function',
    function: {
      name: 'get_traffic_peaks',
      description: 'Explain WHAT DROVE each traffic spike for one site: finds the peak days in the period and attributes each to the top release (with real title), page, or email send that day. Use for "why did traffic spike", "what drove the peaks", annotated-timeline questions.',
      parameters: {
        type: 'object',
        properties: {
          site_id: { type: 'string' },
          range: { type: 'string' },
        },
        required: ['site_id', 'range'],
      },
    },
  },
  {
    type: 'function',
    function: {
      name: 'get_search_terms',
      description: "What visitors typed into the site's on-site search box: top terms with search counts, merged from BOTH GA4 view_search_results events AND search-page URL parameters (?q=…), so the list is complete even when the property under-reports the searchTerm dimension. Use for 'what are people searching for'.",
      parameters: {
        type: 'object',
        properties: {
          site_id: { type: 'string' },
          range: { type: 'string' },
        },
        required: ['site_id', 'range'],
      },
    },
  },
  {
    type: 'function',
    function: {
      name: 'get_channel_mix',
      description: "Sessions per GA4 channel group (Organic Search, Organic Video, Direct, Referral, Email…) for the period AND a comparison period. compare='previous' (default) uses the immediately-preceding period; compare='year' uses the SAME dates last year — use 'year' whenever the question says yearly / year-on-year / vs last year. Organic Video is always included even at zero — use this for 'organic video traffic', 'has YouTube traffic grown', 'channel trends'.",
      parameters: {
        type: 'object',
        properties: { site_id: { type: 'string' }, range: { type: 'string' }, compare: { type: 'string', enum: ['previous', 'year'] } },
        required: ['site_id', 'range'],
      },
    },
  },
  {
    type: 'function',
    function: {
      name: 'get_release_traffic_sources',
      description: "The REAL traffic sources for one release page: GA4 source/medium rows (google/organic, bing/organic, (direct), news.google.com/referral…) for that page's path. Use whenever someone asks where the traffic to a release came from — do NOT answer that question with the platform-vs-GA4 measurement table.",
      parameters: {
        type: 'object',
        properties: {
          site_id: { type: 'string' },
          path: { type: 'string', description: "The release page path, e.g. /releases/4897. If the user gave a release title, first find its path/id via get_platform_data or get_release_360." },
          range: { type: 'string' },
        },
        required: ['site_id', 'path', 'range'],
      },
    },
  },
  {
    type: 'function',
    function: {
      name: 'find_asset_stats',
      description: "Look up a SPECIFIC media asset (image, video or document) by name or code — e.g. '67178-2026KonaSpecsFeatures20250613' or 'EX0112' — and return its platform views, downloads, type and the release it belongs to. Use whenever someone asks about clicks/downloads/views of a named file that isn't in the top rankings.",
      parameters: {
        type: 'object',
        properties: {
          site_id: { type: 'string' },
          query: { type: 'string', description: 'Part of the asset name/code to search for' },
        },
        required: ['site_id', 'query'],
      },
    },
  },
  {
    type: 'function',
    function: {
      name: 'get_misc_pages',
      description: "Pageviews of the site's NON-release pages grouped by page (Photo gallery, Video gallery, Downloads cart, Contacts…) plus a Top-executives ranking of executive-bio pages. Replicates the old manual report's 'Misc. pages' and 'Top 15 executives' charts.",
      parameters: {
        type: 'object',
        properties: { site_id: { type: 'string' }, range: { type: 'string' } },
        required: ['site_id', 'range'],
      },
    },
  },
  {
    type: 'function',
    function: {
      name: 'get_media_yearly',
      description: "YEAR-ON-YEAR media engagement COMBINING BOTH TRACKERS (this year vs last year, cut at today's date so they compare fairly). GA4 side: video-page views, video player events, and file downloads split by type (images/videos/documents) per year — dated, consented visitors. PLATFORM side: uploads per year by type (dated) and the top video assets with lifetime views (every visitor). Use for 'video view differences 2025 vs 2026', 'downloads by type per year', 'has video engagement grown yearly'. Present both sides together, each labelled with its source.",
      parameters: {
        type: 'object',
        properties: { site_id: { type: 'string' } },
        required: ['site_id'],
      },
    },
  },
  {
    type: 'function',
    function: {
      name: 'get_top_assets',
      description: "Top media assets from the platform database by type: type='video' (top videos — ranked by views since videos are watched, not downloaded), type='image' (top image downloads), type='document' (top document downloads), type='all'. Every row includes the release the asset belongs to, so code-names like EX0112 get human context. Use for 'top ten videos', 'top image downloads', 'most downloaded documents'.",
      parameters: {
        type: 'object',
        properties: {
          site_id: { type: 'string' },
          type: { type: 'string', enum: ['video', 'image', 'document', 'all'] },
          limit: { type: 'number' },
        },
        required: ['site_id', 'type'],
      },
    },
  },
  {
    type: 'function',
    function: {
      name: 'get_release_performance',
      description: "THE default tool for ANY release popularity/performance question ('most popular release', 'how did releases do', 'top releases', 'best performing content'). Returns the UNIFIED view of both trackers per release: platformViews (server-side, every visitor — the complete count) AND gaViews/gaSessions (GA4, consented visitors, includes consumer traffic), matched by release id, ranked so a release strong in either tracker surfaces. Also returns what each side has that the other doesn't: platform releases with zero GA traffic, and gaOnlyReleases (older releases still being read, with titles). ALWAYS quote both numbers per release.",
      parameters: {
        type: 'object',
        properties: {
          site_id: { type: 'string' },
          range: { type: 'string' },
          limit: { type: 'number', description: 'Max releases, default 25.' },
        },
        required: ['site_id', 'range'],
      },
    },
  },
  {
    type: 'function',
    function: {
      name: 'get_top_spot_comparison',
      description: "Compare TOP SPOT (featured) releases vs regular releases — average and total views, top titles, uplift factor. Uses the reports DB's top-spot flag automatically when one exists; when it does not, pass the manually-supplied list via top_spot_release_ids and/or top_spot_titles and the comparison is computed for the period. If the result says available:false, tell the user exactly what it suggests (supply the list, or have a top_spot flag added).",
      parameters: { type: 'object', properties: {
        site_id: { type: 'string' }, range: { type: 'string' },
        top_spot_release_ids: { type: 'array', items: { type: 'number' }, description: 'Release ids the client supplied as Top Spot (optional)' },
        top_spot_titles: { type: 'array', items: { type: 'string' }, description: 'Release titles (or fragments) supplied as Top Spot (optional)' },
      }, required: ['site_id'] },
    },
  },
  {
    type: 'function',
    function: {
      name: 'get_asset_downloads_dated',
      description: "DATE-BOUNDED per-asset download ranking ('top image downloads THIS YEAR / this month / last 3 months'). Source chain: (1) the platform's dated media EVENT LOG when the DB has one — full history, every visitor, human titles, per-country split: the authoritative answer; (2) the dashboard's own daily counter snapshots — deltas of the platform's lifetime counters, every visitor, per asset, dated (available from the day tracking started; the result says trackedSince/effectiveFrom); (2) GA4 file_download events per file name (only what the tag captures). If neither covers the window it says so precisely, and you should then give the LIFETIME platform ranking clearly labelled as all-time, plus the fix recommendation. Use this for ANY 'top downloads' question with a time bound; use get_top_assets only for all-time rankings.",
      parameters: {
        type: 'object',
        properties: {
          site_id: { type: 'string' },
          range: { type: 'string' },
          type: { type: 'string', enum: ['image', 'video', 'document', 'all'] },
          limit: { type: 'number' },
        },
        required: ['site_id', 'range', 'type'],
      },
    },
  },
  {
    type: 'function',
    function: {
      name: 'get_model_interest',
      description: "Which VEHICLE MODELS the press audience is engaging with, from the platform event log (every visitor, dated): model-page views (current vs previous period), asset downloads per model (which model's images/videos journalists take), and release views per model. Use for 'which model is getting the most interest', 'Palisade vs Tucson engagement', 'what are journalists interested in'.",
      parameters: {
        type: 'object',
        properties: { site_id: { type: 'string' }, range: { type: 'string' } },
        required: ['site_id', 'range'],
      },
    },
  },
  {
    type: 'function',
    function: {
      name: 'get_email_intelligence',
      description: "Deep email engagement from the platform's per-recipient event log: TOP CLICKED LINKS across campaigns (which content actually pulls journalists in), opens/clicks by HOUR of day and DAY of week (best send times), and average open delay. Use for 'what do people click in our emails', 'when should we send', 'best day/time to email journalists'.",
      parameters: {
        type: 'object',
        properties: { site_id: { type: 'string' }, range: { type: 'string' } },
        required: ['site_id', 'range'],
      },
    },
  },
  {
    type: 'function',
    function: {
      name: 'get_navigation_flows',
      description: "How visitors MOVE through one site: top internal page-to-page journeys (from → to with counts) and top entry points (external source → first page). Use for 'user journeys', 'navigation flow', 'how do people move around the site'.",
      parameters: {
        type: 'object',
        properties: { site_id: { type: 'string' }, range: { type: 'string' } },
        required: ['site_id', 'range'],
      },
    },
  },
  {
    type: 'function',
    function: {
      name: 'get_outbound_clicks',
      description: "Where visitors GO when they leave a site AND at what stage: destinations (domain/URL) PLUS exitJourneys — what page/kind (press release, gallery, model page…) they were on when they clicked away, the consumer-site handover pages with release titles, and how visitors arrived at those pages first. Use for 'where does traffic go', 'when/why do they leave', 'media site to consumer site handover'.",
      parameters: {
        type: 'object',
        properties: { site_id: { type: 'string' }, range: { type: 'string' } },
        required: ['site_id', 'range'],
      },
    },
  },
  {
    type: 'function',
    function: {
      name: 'get_upload_stats',
      description: "Publishing volume year-over-year: monthly counts of releases published and media files uploaded for the range AND the same months last year, INCLUDING a per-type breakdown (images / videos / documents) in totals.byType and per month — so video-only upload counts are answerable. Use for 'uploads this year vs last year', 'how many videos were uploaded'.",
      parameters: {
        type: 'object',
        properties: { site_id: { type: 'string' }, range: { type: 'string' } },
        required: ['site_id', 'range'],
      },
    },
  },
  {
    type: 'function',
    function: {
      name: 'get_data_reconciliation',
      description: "Platform-vs-GA4 comparison for the same releases: server-side platform views next to GA4 consented pageviews, plus the measured GA4 visibility share. Use when someone asks why GA numbers don't match the platform / reporting tool, or how big the cookie-consent gap is.",
      parameters: {
        type: 'object',
        properties: { site_id: { type: 'string' }, range: { type: 'string' } },
        required: ['site_id', 'range'],
      },
    },
  },
  {
    type: 'function',
    function: {
      name: 'get_quiet_releases',
      description: 'Bottom/quietest releases by views for one site (only releases live ≥30 days qualify). Use for "worst performing releases", "bottom 10".',
      parameters: {
        type: 'object',
        properties: {
          site_id: { type: 'string' },
          range: { type: 'string' },
        },
        required: ['site_id', 'range'],
      },
    },
  },
  {
    type: 'function',
    function: {
      name: 'get_insights',
      description: 'Pre-computed facts + suggestions for one site or the whole portfolio: traffic movement, spike days, biggest rising/falling pages, channel shifts, engagement, audience mix. Great starting point for "how are we doing" or report-style questions.',
      parameters: {
        type: 'object',
        properties: {
          scope: { type: 'string', description: 'Site id, or "all" for the portfolio.' },
          range: { type: 'string' },
        },
        required: ['scope', 'range'],
      },
    },
  },
  {
    type: 'function',
    function: {
      name: 'get_page_detail',
      description: 'Everything about ONE page (exact path): daily views over the period, traffic sources, and countries. Use for questions about a specific release or article.',
      parameters: {
        type: 'object',
        properties: {
          site_id: { type: 'string' },
          path: { type: 'string', description: "Exact page path, e.g. '/releases/1652'." },
          range: { type: 'string' },
        },
        required: ['site_id', 'path', 'range'],
      },
    },
  },
  {
    type: 'function',
    function: {
      name: 'get_channel_detail',
      description: 'Drill into one traffic channel for a site: which sources make it up and which landing pages that channel drives.',
      parameters: {
        type: 'object',
        properties: {
          site_id: { type: 'string' },
          channel: { type: 'string', description: "Channel group name, e.g. 'Organic Search', 'Organic Social', 'Referral'." },
          range: { type: 'string' },
        },
        required: ['site_id', 'channel', 'range'],
      },
    },
  },
  {
    type: 'function',
    function: {
      name: 'get_email_campaigns',
      description: 'Email campaign performance for a site (SparkPost/Brevo): campaigns with sent/delivered/open/click numbers and rates, plus send days. Not every site sends email — the result says so when not. Combine with get_timeseries to relate sends to site traffic.',
      parameters: {
        type: 'object',
        properties: {
          site_id: { type: 'string' },
          range: { type: 'string' },
        },
        required: ['site_id', 'range'],
      },
    },
  },
  {
    type: 'function',
    function: {
      name: 'get_platform_data',
      description: 'Native publishing-platform data from the site\'s reports database (when connected): release titles with views/downloads/basket adds, media asset performance, DB-sourced email campaigns, vehicle models (newsrooms), banners & audience make-up (Newspress markets), and the client/company list. On Newspress markets pass company_id to scope to one client.',
      parameters: {
        type: 'object',
        properties: {
          site_id: { type: 'string' },
          range: { type: 'string' },
          company_id: { type: 'string', description: 'Newspress markets only: numeric company id from a previous call.' },
        },
        required: ['site_id', 'range'],
      },
    },
  },
  {
    type: 'function',
    function: {
      name: 'find_company',
      description: 'Find a CLIENT/COMPANY on a Newspress market by name (partial and misspelled names OK). Returns id + name. ALWAYS use this to get the company_id for client-scoped questions and reports — do NOT try to read the full company list from get_platform_data (it is thousands of rows and gets truncated).',
      parameters: { type: 'object', properties: { site_id: { type: 'string' }, name: { type: 'string' } }, required: ['site_id', 'name'] },
    },
  },
  {
    type: 'function',
    function: {
      name: 'search_releases',
      description: 'Search the reports DATABASE for releases by words in their TITLE (e.g. a person, car model, event). Returns id, title, published date, platform views. Use whenever the user names a story, person or topic.',
      parameters: { type: 'object', properties: { site_id: { type: 'string' }, query: { type: 'string' } }, required: ['site_id', 'query'] },
    },
  },
  {
    type: 'function',
    function: {
      name: 'get_release_360',
      description: 'EVERYTHING about one release by its numeric id: platform views/downloads/basket, categories, company, NAMED READERS (every registered person who viewed/downloaded it, with counts), its media assets with per-asset downloads, daily interest curve, reader countries, its email campaigns (recipients/opens/clicks), AND its GA4 web traffic. THE tool for \'everything about release X\' — combine with get_campaign_recipients for the full email recipient list of its campaigns, and create_pdf_report for a downloadable release dossier.',
      parameters: { type: 'object', properties: { site_id: { type: 'string' }, release_id: { type: 'string' }, range: { type: 'string' } }, required: ['site_id', 'release_id', 'range'] },
    },
  },
  {
    type: 'function',
    function: {
      name: 'get_campaign_recipients',
      description: 'Per-recipient events for one email campaign from the reports DB: each email address with delivered/opened/clicked/unsubscribed flags.',
      parameters: { type: 'object', properties: { site_id: { type: 'string' }, campaign_id: { type: 'string' } }, required: ['site_id', 'campaign_id'] },
    },
  },
  {
    type: 'function',
    function: {
      name: 'find_person',
      description: 'Find a registered person (journalist, consumer) in the reports DB by name or email — tolerant of misspellings. Returns id, name, email, type (3=journalist). ALWAYS use this first for questions about a named person.',
      parameters: { type: 'object', properties: { site_id: { type: 'string' }, name: { type: 'string' } }, required: ['site_id', 'name'] },
    },
  },
  {
    type: 'function',
    function: {
      name: 'get_person_activity',
      description: "One person's full engagement history: every release they viewed/downloaded (with titles and dates), their publications, and their email delivery/open/click events. Use after find_person.",
      parameters: { type: 'object', properties: { site_id: { type: 'string' }, user_id: { type: 'string' }, email: { type: 'string' } }, required: ['site_id', 'user_id'] },
    },
  },
  {
    type: 'function',
    function: {
      name: 'get_top_journalists',
      description: 'BULK RANKING from the reports DB: the top N registered journalists (up to 500) by release views + downloads over a period, each with name, email, views, downloads, publications and last-seen date. Use for "top journalists", "who is using the platform", "most active readers", "top 100" style questions. On Newspress markets pass company_id to rank within one client\'s releases only.',
      parameters: {
        type: 'object',
        properties: {
          site_id: { type: 'string' },
          range: { type: 'string' },
          limit: { type: 'integer', description: 'How many journalists to return, 1–500. Default 100.' },
          company_id: { type: 'string', description: 'Newspress markets only: scope the ranking to one client company.' },
        },
        required: ['site_id', 'range'],
      },
    },
  },
  {
    type: 'function',
    function: {
      name: 'get_email_engagers',
      description: 'BULK EXTRACTION from the reports DB: everyone who opened or clicked ANY email campaign in a period, deduplicated across all campaigns, with name, type (3=journalist), publications, total opens, total clicks and how many campaigns they engaged with. Use for "who opened/clicked our emails", "journalists who engaged by email", consolidated recipient lists across campaigns.',
      parameters: {
        type: 'object',
        properties: {
          site_id: { type: 'string' },
          range: { type: 'string' },
          limit: { type: 'integer', description: '1–500, default 100.' },
          clicked_only: { type: 'boolean', description: 'true = only people who clicked (strongest interest signal).' },
          journalists_only: { type: 'boolean', description: 'true = only registered journalists (users.type=3).' },
        },
        required: ['site_id', 'range'],
      },
    },
  },
  {
    type: 'function',
    function: {
      name: 'get_media_monitoring',
      description: 'MEDIA MONITORING data for a site (when its monitoring database is connected — e.g. Ferrari, KGM): article volume & reach totals vs previous period, daily volume/reach series, splits by region, topic, model, sentiment (incl. by region), top publications by reach, share of voice vs competitor brands, and top articles. Use for any question about press coverage, articles, reach, sentiment, share of voice or "which publications picked up the story".',
      parameters: {
        type: 'object',
        properties: { site_id: { type: 'string' }, range: { type: 'string' } },
        required: ['site_id', 'range'],
      },
    },
  },
  {
    type: 'function',
    function: {
      name: 'create_pdf_report',
      description: 'Create a DOWNLOADABLE report file from markdown you have written (tables, headings, lists supported). Returns a download link. Use whenever the user asks for a report/PDF they can download or send to a client — first gather the data with the other tools, write the full report in markdown, then call this. ALWAYS pass site_id (or "all") and range: real charts for that scope (KPI tiles, daily traffic, channels, top pages, publishing cadence, top releases, email campaigns) are embedded into the document automatically — you write the narrative and tables, the charts are added for you. Pass company_id when the report is about one Newspress client. Rendered in the SparkSoftware house style; a PDF is produced when possible, otherwise a print-ready HTML.',
      parameters: {
        type: 'object',
        properties: {
          title: { type: 'string', description: 'Report title, e.g. "Newspress UK — Journalist Engagement, July 2026".' },
          markdown: { type: 'string', description: 'The complete report content in markdown.' },
          site_id: { type: 'string', description: 'The site this report covers, or "all" for the portfolio — enables the embedded charts.' },
          range: { type: 'string', description: 'The period the report covers (7d, 28d, 90d, mtd, 12m or YYYY-MM-DD..YYYY-MM-DD).' },
          company_id: { type: 'string', description: 'Newspress markets only: scope the charts to one client company.' },
          release_id: { type: 'string', description: 'For a report about ONE RELEASE: its numeric id (from search_releases). The chart pack then contains ONLY that release — interest curve, named readers, assets, email, countries — and NO site-wide charts.' },
          charts: { type: 'string', enum: ['all', 'none', 'monitoring'], description: "Default 'all': the complete graph pack + the full journalist-interaction table. 'monitoring': ONLY the myNewspress media-monitoring block (key metrics, volume & reach, sentiment, regions, categories, models, release→coverage, publications, SOV, highlight articles) — use for a myNewspress/coverage report. 'none' only if the user explicitly asked for a plain/table-only report." },
        },
        required: ['title', 'markdown'],
      },
    },
  },
  {
    type: 'function',
    function: {
      name: 'create_pptx_report',
      description: "Create a DOWNLOADABLE POWERPOINT deck (.pptx) of a report, branded in the client's corporate identity. Slides are generated from LIVE data for the scope: title slide, your key-insight bullets, KPI tiles, daily chart, releases, journalists, media assets, email campaigns. Pass site_id (or 'all'), range, and company_id for one Newspress client. Branding: brand_primary/brand_accent hex colours + brand_name label the deck in the client's CI (site defaults from Sites admin are used when omitted). Use whenever the user asks for a PowerPoint/deck/presentation.",
      parameters: {
        type: 'object',
        properties: {
          title: { type: 'string' },
          subtitle: { type: 'string', description: 'e.g. "Monthly report · July 2026" or the client name.' },
          site_id: { type: 'string' },
          range: { type: 'string' },
          company_id: { type: 'string', description: 'Newspress markets: scope every slide to one client (use find_company first).' },
          release_id: { type: 'string', description: 'For a deck about ONE RELEASE: its numeric id — every slide scopes to that release only.' },
          key_insights: { type: 'array', items: { type: 'string' }, description: '3-8 short narrative bullets you have written from the data — becomes the Key insights slide.' },
          monitoring: { type: 'boolean', description: 'true = a MEDIA MONITORING deck: coverage KPIs, volume by day, region split, sentiment, media types, share of voice, top publications and highlight articles — NO GA/platform slides. Use whenever the user asks for a myNewspress / media monitoring / coverage PowerPoint.' },
          market: { type: 'string', description: 'Monitoring decks only: comma-separated market id(s) to scope the coverage to.' },
          brand_primary: { type: 'string', description: "Client CI primary colour hex, e.g. '#101010' — title slide background." },
          brand_accent: { type: 'string', description: "Client CI accent colour hex — bars and highlights." },
          brand_name: { type: 'string', description: 'Brand label shown on every slide, e.g. "Mazda UK Press Office".' },
        },
        required: ['title', 'site_id', 'range'],
      },
    },
  },
  {
    type: 'function',
    function: {
      name: 'compare_sites',
      description: 'Totals for EVERY site individually over a range (plus previous period), for ranking and comparing sites.',
      parameters: {
        type: 'object',
        properties: { range: { type: 'string' } },
        required: ['range'],
      },
    },
  },
];

// Serialise a tool result within the payload budget WITHOUT corrupting JSON:
// oversized arrays are trimmed (with a note) rather than sliced mid-string.
function safeToolJson(result, budget = 60000) {
  let s2 = JSON.stringify(result);
  if (s2.length <= budget) return s2;
  const shrink = (v, cap) => {
    if (Array.isArray(v)) {
      const out2 = v.slice(0, cap).map((x) => shrink(x, cap));
      if (v.length > cap) out2.push(`…trimmed ${v.length - cap} more rows`);
      return out2;
    }
    if (v && typeof v === 'object') {
      const o = {};
      for (const [k, val] of Object.entries(v)) o[k] = shrink(val, cap);
      return o;
    }
    return typeof v === 'string' && v.length > 2000 ? v.slice(0, 2000) + '…' : v;
  };
  for (const cap of [50, 25, 10]) {
    s2 = JSON.stringify(shrink(result, cap));
    if (s2.length <= budget) return s2;
  }
  return JSON.stringify({ error: 'Result too large even after trimming — request a smaller limit or a narrower range.' });
}

async function execTool(name, args, allowed = null) {
  const range = resolveRange(args.range);
  const prev = previousRange(range);
  // Permission gate: client users only reach their assigned sites.
  if (allowed && args.site_id && args.site_id !== 'all' && !allowed.some((s) => s.id === args.site_id)) {
    return { error: `No access to site "${args.site_id}". Available sites: ${allowed.map((s) => s.id).join(', ')}` };
  }
  if (allowed && args.scope && args.scope !== 'all' && !allowed.some((s) => s.id === args.scope)) {
    return { error: `No access to site "${args.scope}". Available sites: ${allowed.map((s) => s.id).join(', ')}` };
  }
  switch (name) {
    case 'list_sites':
      return (allowed || loadSites()).map(({ id, name: n, url }) => ({ id, name: n, url }));
    case 'get_totals': {
      if (args.site_id === 'all') {
        const cur = await portfolioTotals(range.startDate, range.endDate, allowed);
        const prv = await portfolioTotals(prev.startDate, prev.endDate, allowed);
        return { range, current: cur.totals, previous_period: prv.totals };
      }
      return {
        range,
        current: await totals(args.site_id, range.startDate, range.endDate),
        previous_period: await totals(args.site_id, prev.startDate, prev.endDate),
      };
    }
    case 'get_timeseries':
      return args.site_id === 'all'
        ? await portfolioTimeseries(range.startDate, range.endDate, allowed)
        : await timeseries(args.site_id, range.startDate, range.endDate);
    case 'get_breakdown': {
      const opts = args.path_prefix ? { pathPrefix: args.path_prefix } : {};
      return args.site_id === 'all'
        ? await portfolioBreakdown(args.dimension, range.startDate, range.endDate, args.limit || 12, opts, allowed)
        : await breakdown(args.site_id, args.dimension, range.startDate, range.endDate, args.limit || 12, opts);
    }
    case 'get_insights':
      return await computeInsights(args.scope, args.range, allowed);
    case 'get_traffic_peaks':
      return { range, peaks: await trafficPeaks(args.site_id, args.range) };
    case 'get_search_terms':
      return { range, terms: await searchTerms(args.site_id, range.startDate, range.endDate, 50),
               note: 'Merged from GA4 search events AND search-page URL parameters — the complete collectable list.' };
    case 'get_channel_mix':
      return { range, ...(await channelMix(args.site_id, args.range, args.compare || 'previous')) };
    case 'get_release_traffic_sources':
      return { range, path: args.path, sources: await pageTrafficSources(args.site_id, args.path, args.range),
               note: 'GA4 source/medium rows for this page (consented sessions). These are the real referrers — google, bing, direct, news aggregators.' };
    case 'find_asset_stats':
      return { query: args.query, assets: await searchMediaAssets(args.site_id, args.query),
               note: 'Platform-side counters (every visitor, no consent gap). viewed = views, downloaded = downloads; documents have no separate click metric — downloads ARE the interaction measure.',
               datedRankingAvailable: 'For time-bounded download numbers (this year / last 3 months), call get_asset_downloads_dated — never say dated numbers are unavailable without calling it.' };
    case 'get_misc_pages':
      return { range, ...(await miscPages(args.site_id, args.range)) };
    case 'get_model_interest':
      return (await modelInterest(args.site_id, args.range)) || { models: [], note: 'No model event data in this database.' };
    case 'get_email_intelligence':
      return (await emailIntelligence(args.site_id, args.range)) || { note: 'No per-recipient email event log in this database.' };
    case 'get_release_performance':
      return await unifiedReleases(args.site_id, args.range, args.limit || 25);
    case 'get_top_spot_comparison':
      return await topSpotComparison(args.site_id, args.range || '90d',
        args.top_spot_release_ids || [], args.top_spot_titles || []);
    case 'get_asset_downloads_dated': {
      const wantType = args.type === 'all' ? null : args.type;
      // PRIMARY: the platform's own dated event log (full history, every
      // visitor, human asset titles, per-country splits) when the DB has one.
      const eventLog = await mediaDownloadsDated(args.site_id, args.range, { type: wantType, limit: args.limit || 25 }).catch(() => null);
      if (eventLog && (eventLog.totals.downloads > 0 || eventLog.totals.views > 0)) {
        return { range: args.range, type: args.type, ...eventLog,
                 guidance: 'This is the platform event log — the AUTHORITATIVE dated ranking: every visitor, full history, real timestamps. Quote downloads (and views where useful); name = human asset title, code = internal file name (mention the code only if the user used it). byCountry gives the market split of the downloads. No caveats about lifetime counters are needed — these numbers ARE date-bounded.' };
      }
      const snap = datedAssetActivity(args.site_id, args.range, wantType, args.limit || 25);
      const gaFiles = await fileDownloadsRanked(args.site_id, args.range, 100).catch(() => []);
      const gaFiltered = wantType ? gaFiles.filter((f) => f.bucket === wantType + 's' || f.bucket === wantType) : gaFiles;
      return {
        range: args.range, type: args.type,
        counterSnapshots: snap,
        ga4FileDownloads: gaFiltered.slice(0, args.limit || 25),
        guidance: snap.available
          ? 'counterSnapshots is the authoritative dated ranking (every visitor, platform counters). Quote it, note effectiveFrom/trackedSince if the window is only partially covered, and mention GA4 file numbers as the consented-web view if they add anything.'
          : 'No snapshot history covers this window yet (tracking starts the day the feature is installed and grows daily — say when it started if trackedSince is set). If ga4FileDownloads is also empty for this type, the GA4 tag is not capturing these downloads: recommend (a) letting the dashboard\'s daily counter tracking accumulate — it answers this question automatically from now on, and (b) fixing the GA4 file_download configuration so image formats (.jpg/.png/.webp/.zip baskets) are captured. Then give the LIFETIME platform ranking clearly labelled as all-time.',
      };
    }
    case 'get_media_yearly': {
      const yNow = new Date().getUTCFullYear();
      const ytd = `${yNow}-01-01..${new Date().toISOString().slice(0, 10)}`;
      const [ga, up, vids] = await Promise.all([
        mediaYearly(args.site_id),
        uploadStats(args.site_id, ytd).catch(() => null), // months Jan→today, each vs the SAME month last year, incl. per-type
        fullList(args.site_id, 'videos', '12m', 10).catch(() => []),
      ]);
      const platformDated = await mediaYearPlatform(args.site_id).catch(() => null);
      return {
        ga4: ga,
        platform: {
          datedEngagementByYear: platformDated, // event-log downloads/views per year by type — every visitor, authoritative when present
          uploadsThisYearVsLast: up ? { months: up.months, totals: up.totals } : null,
          topVideosLifetime: vids,
          note: 'Platform view/download counters are LIFETIME totals with no dates — they cannot be split by year. Platform UPLOADS are dated, so uploads ARE compared per year here: totals.byType has images/videos/documents for THIS year AND prevImages/prevVideos/prevDocuments for the SAME period LAST year — always quote both (e.g. "129 videos uploaded in 2026 vs 98 in 2025"). topVideosLifetime shows which video assets carry the engagement.',
        },
        guidance: 'COMBINED ANSWER REQUIRED: present BOTH sources together. If platform.datedEngagementByYear is present it is the AUTHORITATIVE per-year downloads/views by type (event log, every visitor, dated) — lead with it, then GA4 as the consented-web comparison. Otherwise GA4 = the dated comparison and platform contributes uploads by type. Never answer with GA4 alone.',
      };
    }
    case 'get_top_assets': {
      const what = { video: 'videos', document: 'documents', image: 'media', all: 'media' }[args.type] || 'media';
      let rows = await fullList(args.site_id, what, args.range || '12m', Math.min(100, Number(args.limit) || 20));
      if (args.type === 'image') rows = rows.filter((r) => r.type === 'image');
      return { type: args.type, assets: rows.slice(0, Math.min(100, Number(args.limit) || 20)),
               note: 'Platform lifetime counters (no consent gap, not date-bounded). Videos are ranked by the greater of views/downloads — quote VIEWS for videos. releaseTitle gives the human context for coded asset names like EX0112.',
               datedRankingAvailable: 'IMPORTANT: if the user asks for a TIME-BOUNDED version of this ranking (this year / last 3 months / this week — including as a FOLLOW-UP to this answer), call get_asset_downloads_dated. Do NOT reply that dated rankings are unavailable without calling it first — the dashboard keeps daily counter snapshots precisely for this.' };
    }
    case 'get_quiet_releases':
      return { range, note: 'Only releases live ≥30 days qualify.', releases: await quietReleases(args.site_id, args.range, 10) };
    case 'get_navigation_flows':
      return { range, ...(await pageFlows(args.site_id, range.startDate, range.endDate)) };
    case 'get_outbound_clicks': {
      const [ob2, oj2] = await Promise.all([
        outboundClicks(args.site_id, range.startDate, range.endDate),
        outboundJourneys(args.site_id, range.startDate, range.endDate).catch(() => null),
      ]);
      return { range, ...ob2, exitJourneys: oj2 };
    }
    case 'get_upload_stats':
      return { range, stats: await uploadStats(args.site_id, args.range) };
    case 'get_data_reconciliation': {
      const rc2 = await reconReport(args.site_id, args.range);
      return {
        range, recon: rc2,
        explanation: 'Platform views are counted server-side (every visitor). GA4 only records visitors who accept analytics cookies under GDPR/CCPA, minus ad-blockers — so GA4 undercounts. The platform number is the true count; GA4 remains the right source for dimensions only it has (sources, devices, journeys, new vs returning).',
      };
    }
    case 'get_page_detail':
      return await pageDetail(args.site_id, args.path, range.startDate, range.endDate);
    case 'get_channel_detail': {
      const [sources, landing] = await Promise.all([
        filteredBreakdown(args.site_id, { dimension: 'sessionSource', metric: 'sessions', filterField: 'sessionDefaultChannelGroup', filterValue: args.channel }, range.startDate, range.endDate, 10),
        filteredBreakdown(args.site_id, { dimension: 'landingPage', metric: 'sessions', filterField: 'sessionDefaultChannelGroup', filterValue: args.channel }, range.startDate, range.endDate, 10),
      ]);
      return { channel: args.channel, range, sources, landing };
    }
    case 'find_person':
      return await searchPeople(args.site_id, args.name);
    case 'get_person_activity':
      return await personActivity(args.site_id, args.user_id, args.email || null);
    case 'find_company':
      return await searchCompanies(args.site_id, args.name);
    case 'search_releases':
      return await searchReleases(args.site_id, args.query);
    case 'get_release_360': {
      const lr = await legacyRelease(args.site_id, args.release_id, args.range);
      let ga = null;
      try { ga = (await searchPages(args.site_id, String(args.release_id), range.startDate, range.endDate))[0] || null; } catch { /* optional */ }
      return { platform: lr, ga4_page: ga };
    }
    case 'get_campaign_recipients':
      return { recipients: ((await campaignRecipients(args.site_id, args.campaign_id)) || []).slice(0, 200) };
    case 'get_top_journalists':
      return { range, journalists: await journalistRanking(args.site_id, args.range, { limit: args.limit || 100, companyId: args.company_id || null }) };
    case 'get_media_monitoring':
      return await monitoringOverview(args.site_id, args.range);
    case 'create_pptx_report': {
      const rp = await pptxReport({
        siteId: args.site_id, range: args.range, companyId: args.company_id || null,
        releaseId: args.release_id || null,
        monitoring: !!args.monitoring, market: args.market || null,
        title: args.title, subtitle: args.subtitle,
        keyInsights: Array.isArray(args.key_insights) ? args.key_insights : [],
        ci: { primary: args.brand_primary, accent: args.brand_accent, name: args.brand_name },
        allowed,
      });
      return { download_url: '/reports/' + rp.filename, format: 'pptx',
               note: 'Give the user this link as markdown EXACTLY as written, with the path starting /reports/ — never add a scheme like sandbox: or file: : [Download the PowerPoint](' + '/reports/' + rp.filename + ')' };
    }
    case 'create_pdf_report': {
      const r2 = await markdownReport(args.title, args.markdown,
        { siteId: args.site_id, range: args.range, companyId: args.company_id, releaseId: args.release_id || null, charts: args.charts || 'all', allowed });
      return { download_url: '/reports/' + r2.filename, format: r2.format,
               note: 'Give the user this link as markdown EXACTLY as written, with the path starting /reports/ — never add a scheme like sandbox: or file: : [Download the report](' + '/reports/' + r2.filename + ')' };
    }
    case 'get_email_engagers':
      return { range, engagers: await emailEngagers(args.site_id, args.range, { limit: args.limit || 100, clickedOnly: !!args.clicked_only, journalistsOnly: !!args.journalists_only }) };
    case 'get_platform_data': {
      const ov = await legacyOverview(args.site_id, args.range, args.company_id || null);
      // The full company directory can be thousands of rows — never ship it
      // whole into the model; find_company is the lookup tool.
      if (Array.isArray(ov.companies) && ov.companies.length > 30) {
        ov.companiesTotal = ov.companies.length;
        ov.companies = ov.companies.slice(0, 30);
        ov.companiesNote = `Only the first 30 of ${ov.companiesTotal} clients are listed — use find_company to look one up by name.`;
      }
      return ov;
    }
    case 'get_email_campaigns':
      return await emailOverview(args.site_id, args.range);
    case 'compare_sites': {
      const cur = await portfolioTotals(range.startDate, range.endDate, allowed);
      const prv = await portfolioTotals(prev.startDate, prev.endDate, allowed);
      const prevById = Object.fromEntries(prv.perSite.map((s) => [s.id, s]));
      return {
        range,
        sites: cur.perSite.map((s) => ({
          ...s,
          previous_sessions: prevById[s.id]?.sessions ?? null,
          previous_conversions: prevById[s.id]?.conversions ?? null,
        })),
      };
    }
    default:
      throw new Error(`Unknown tool ${name}`);
  }
}

const SYSTEM_PROMPT = `You are the analytics assistant inside LAV Pulse, a multi-website Google Analytics (GA4) reporting dashboard run by a digital agency. Today's date is {{TODAY}}.

You answer questions about traffic and performance across all tracked websites by calling the provided tools, which query Google Analytics live. Rules:
- ALWAYS fetch real data with tools before answering a data question; never invent numbers.
- When the user names a site loosely ("the dental one"), call list_sites first and match it.
- Default to the last 28 days when no period is given, and say which period you used.
- Give concrete numbers with a short, sharp interpretation: what changed, why it might have, what to look at next. Percentages for changes vs the previous period.
- Format clearly with markdown. Use a compact table when comparing several sites. Keep prose tight — this is a professional reporting tool.
- If asked for "a report", structure it: headline summary, key metrics vs previous period, traffic sources, top content, notable movers, one or two recommendations.
- You sit on FIVE live data sources and must think across ALL of them on every question, not just GA4: (1) GA4 web analytics, (2) the platform reports DATABASE (releases, journalists, publications, companies, media assets), (3) the email DATABASE (campaigns + per-recipient events), (4) ESP APIs, (5) the MEDIA MONITORING database (press-coverage articles with reach, sentiment, regions, publications, share of voice — get_media_monitoring; Ferrari/KGM-style sites). For any substantive question, pull from every source that could contribute and MERGE them into one answer — e.g. GA4 sessions + releases published + email sends for the same period, joined by dates and release ids.
- Sites with a connected reports DATABASE have richer tools: search_releases (stories by title/person/topic), get_release_360 (platform + email + GA4 joined for one release), get_campaign_recipients (one campaign), get_platform_data (overview incl. cadence, companies, categories), and BULK tools: get_top_journalists (ranked top N up to 500 named journalists with publications — use for ANY "top journalists" / "who uses the platform" / "top 100" request) and get_email_engagers (deduplicated named list of everyone who opened/clicked ANY campaign in a period, filterable to clickers or journalists only). Bulk extraction and ranking ARE available — NEVER claim you cannot produce a top-N list, a consolidated recipient list, or a cross-campaign extraction; call the bulk tools instead.
- For a named PERSON (journalist, reader): find_person → get_person_activity gives every release they viewed/downloaded, their publications and email events — never say this is impossible. For a story/topic: search_releases.
- SPARSE RESULTS: if a ranking/extraction returns far fewer rows than the user asked for (e.g. 14 when they wanted 100), do NOT stop there. In the SAME turn, automatically re-run the identical query over the last 28 days, and over 90 days if still sparse, and present the fuller period's table as the main answer — noting briefly that the original window only had N active people. "This month" early in a month means only a few days of data; widen it without being asked.
- TOP JOURNALISTS = platform + email combined: for any "top/most active journalists" request, call BOTH get_top_journalists AND get_email_engagers (journalists_only: true) for the same period, then merge by person — someone who only opened/clicked emails still counts as active. Show views, downloads, email opens/clicks and publications in one table.
- SPEED: when several INDEPENDENT tool calls are needed (e.g. totals + platform data + email + journalists for a report), request them ALL IN ONE turn — they execute in parallel. Do not fetch one at a time across many turns.
- MEMORY: if a release, company or person was already identified earlier in THIS conversation, REUSE its id — do not re-search, and NEVER ask the user for an id you already produced. If search_releases returns nothing, retry with fewer or more distinctive words (e.g. just the surname) before anything else.
- NEVER give up after one failed tool call: if a tool errors or returns trimmed/unexpected data, change approach (find_company for client ids, smaller limit, narrower range, a different tool) and try again — apologising without retrying is wrong.
- NEVER defer work: do not reply "I need to re-run this" or offer to fetch data — call the tools and answer in the same turn. Never ask the user to choose between lists you could simply return together.
- Email numbers: campaign "opens/clicks" are UNIQUE people (deduplicated); a rate can never exceed 100%. If a source figure implies more opens than recipients it is counting repeat events — say "open events" for that and prefer unique numbers.
- Many of these sites are press offices / media sites, so think like a digital PR analyst: releases, coverage pickup, journalist audiences, channel distribution. When asked "how are we doing" or for advice, call get_insights first, then drill deeper with the other tools, and always end with concrete recommended actions grounded in the numbers.
- MEDIA MONITORING REPORTS: when the user wants a media-monitoring / myNewspress / coverage report or deck, produce ONLY monitoring content — never the GA/platform pack. PowerPoint → create_pptx_report with monitoring:true. PDF/document → create_pdf_report with charts:'monitoring'. Always give the returned download link back as a markdown link so it renders as a download button. The link URL must be the download_url EXACTLY as the tool returned it (starting with /reports/) — NEVER prefix it with a scheme such as sandbox:, attachment: or file:. For monitoring PowerPoints, do NOT pass key_insights — the deck writes its own KEY INSIGHT slide and per-chart commentary; passing key_insights would duplicate it.
- DOWNLOADABLE REPORTS: when the user wants a report they can download/send, gather the data, write the complete report in markdown (headline verdict, key metrics vs previous period, tables, insights, recommendations), then call create_pdf_report WITH site_id and range (so the full chart pack embeds) and give the user the returned download link as a markdown link. When they ask for a POWERPOINT / deck / presentation, use create_pptx_report instead — same scoping rules, and pass the client's CI colours (brand_primary/brand_accent hex + brand_name) when the report is for a specific brand whose colours you know or the user provides. Reports include EVERYTHING by default — the full graph pack plus a complete table of every journalist who interacted with the scope's content (platform views/downloads merged with email opens/clicks). Only produce a lighter report (charts: 'none') when the user explicitly asks for a plain or table-only version; if their request is ambiguous about depth, include everything rather than asking. SCOPE = what the user asked for, exactly: a report about ONE RELEASE → search_releases for its id, then pass release_id (charts become release-only: readers, assets, interest, email — zero site-wide data). A report about ONE CLIENT → find_company then company_id (client-only charts). A whole site or portfolio → neither. NEVER mix scopes: a release report must not contain site-wide journalists/publications/assets, and a client report must not contain other clients' data. For a report about ONE CLIENT on a Newspress market (e.g. "a report for Mazda"), FIRST call find_company to get that client's company id, then pass company_id to create_pdf_report — every chart (cadence, releases, journalists, media, documents, banners, email) then covers that client only.
- "Why did traffic spike / what drove the peaks" → get_traffic_peaks (it names the release, page or email send behind each spike day). "What are people searching for" → get_search_terms. "Worst / quietest releases" → get_quiet_releases.
- GRAPH NAMES: users refer to dashboard graphs by their card titles — map them to tools and answer with the same data: "Daily traffic" / "Highlights" / "the annotated chart" → get_timeseries + get_traffic_peaks · "What drove the traffic peaks" → get_traffic_peaks · "Content volume vs audience" → get_platform_data (cadence) + get_totals · "New vs returning" → get_totals (newUsers vs the rest) · "Top days of the week" → get_timeseries averaged by weekday · "Top pages" → get_breakdown pagePath · "Traffic channels" → get_breakdown sessionDefaultChannelGroup (+ get_channel_detail) · "Devices" → get_breakdown deviceCategory · "Countries" → get_breakdown country · "Top on-site searches" → get_search_terms · "Quietest releases" → get_quiet_releases · "Releases — platform stats" → get_platform_data / search_releases · "Media assets" / "Documents" / "Vehicle models" / "Categories" / "Banners" / "Top publications" / "Registered audience" → get_platform_data · "Top journalists" → get_top_journalists · "Email campaigns" → get_email_campaigns · "Media monitoring" → get_media_monitoring. When a user names one of these graphs, fetch that data and answer — never say you can't see the dashboard.
- DATA MISMATCH questions ("why is GA lower than the reporting tool"): call get_data_reconciliation and explain — platform counts are server-side and complete; GA4 only sees cookie-consented visitors (GDPR/CCPA) minus ad-blockers, so GA4 typically shows a FRACTION of true traffic. State the measured visibility % for that site. Platform numbers are authoritative for views/downloads; GA4 is authoritative for sources, devices, journeys, new-vs-returning, searches. This tool reads BOTH, so it is not limited the way GA alone is. "Journeys/how people move" → get_navigation_flows · "where do they go when they leave" → get_outbound_clicks · "uploads vs last year" → get_upload_stats · "Site heat map" card = pageviews by section (get_breakdown pagePath).
- "myNewspress" = the media-monitoring product (press-coverage articles: volume, reach, AVE value, sentiment, regions, categories, models, share of voice, top publications/journalists/articles, and release-to-coverage conversion). "myNewspress report/section/data for X" → get_media_monitoring for that site. For a DOWNLOADABLE myNewspress/coverage report: get_media_monitoring, write the KEY INSIGHT narrative in markdown (press releases → coverage, press fleet, sentiment, what's working), then create_pdf_report with charts:'monitoring' — the full monitoring graph block embeds automatically. Its clients include MG UK, Ferrari and KGM.
- "TRAFFIC SOURCE for a release/page" means real referrers (google, bing, direct, news aggregators) → get_release_traffic_sources with that release's /releases/<id> path — NEVER answer that question with the platform-vs-GA4 measurement table. "Organic video traffic" / "YouTube traffic" / channel movements → get_channel_mix (Organic Video is always included with a previous-period comparison, even at zero).
- NAMED FILE / ASSET questions ("how many downloads for 67178-2026KonaSpecs…", "stats for EX0112") → find_asset_stats — it searches the whole media table by name, so an asset never needs to be in a top ranking to be answerable. Documents have downloads as their interaction metric (no separate click count) — say so plainly and give the download number. Videos: use platform views when downloads are zero.
- SEARCH data: get_search_terms merges GA4 search events WITH search-page URL parameters, so it is the complete collectable list; if a client says the list looks short, check a longer range (12m) and mention that searches made by non-consented visitors are invisible to GA4 while the merged URL method recovers most of them.
- Upload/media counts BY TYPE (video-only, image-only, documents) → get_upload_stats (totals.byType + per-month images/videos/documents). Never say media types can't be separated.
- MODEL questions ("which model gets the most interest", "Palisade engagement", "what vehicles are journalists writing about") → get_model_interest — dated model-page views with previous-period comparison, asset downloads per model, release views per model. Media-monitoring model coverage (articles) is get_media_monitoring; PRESS-SITE model engagement is get_model_interest — combine both for a full picture when asked broadly.
- EMAIL content & timing ("what links do journalists click", "best time/day to send") → get_email_intelligence. Release performance questions should also note platformViewsInPeriod (period-true event-log views) over the lifetime counter whenever get_release_performance returns it.
- Dated download rankings can also split byCountry, byCategory and byModel — use those splits when the question mentions markets, categories or vehicles.
- "TOP SPOT" questions (top spot vs non-top-spot performance) → get_top_spot_comparison. If it reports no flag in the DB, ASK the user for their top-spot release list (ids or titles) and call it again with that list — never just say the data is unavailable.
- "Misc pages" / "Top executives" (the old manual-report charts) → get_misc_pages. ANY question about executives — "top executives", "how popular is an executive's page", "executive bio views" — is answered by its executives ranking; never say you have no executive data without calling it first.
- YEAR-ON-YEAR media questions ("video views 2025 vs 2026", "downloads by type per year", "has video engagement grown yearly") → get_media_yearly. The platform's viewed/downloaded columns are LIFETIME totals with no dates, so per-year media numbers can only come from GA4's dated events — quote get_media_yearly's numbers and say which source they are. For "organic video traffic increase yearly" → get_channel_mix with compare='year'.
- "Top ten videos" / "top image downloads" / "top documents" WITHOUT a time bound → get_top_assets (lifetime). WITH a time bound ("this year", "this month", "last 3 months", "last 28 days") — INCLUDING short follow-ups like "in the last 3 months?" after a lifetime ranking — → get_asset_downloads_dated (ALWAYS call it; answering a dated-downloads question without calling it is an error), which uses the dashboard's own daily counter snapshots (deltas = dated activity, every visitor). If its history doesn't cover the window yet, say when tracking started, give the lifetime ranking clearly labelled all-time, and note the ranking becomes fully dated automatically as history accumulates. NEVER flatly answer "no data" for dated download questions — explain what is available and from when. Also: Videos are RANKED AND QUOTED BY VIEWS (they are watched, not downloaded) — never reply "no data" for videos. When an asset has a coded name (EX0112, INT0312…), always show its releaseTitle alongside so the client knows what it is.
- COMBINED-SOURCE RULE (critical): performance questions must draw on EVERY source that has relevant data, each number labelled with its source — a single-tracker answer is only acceptable when no other source has anything relevant, and then you must say why (e.g. "the platform's view counters are lifetime totals, so the dated yearly comparison can only come from GA4 — the platform contributes per-year uploads and lifetime asset engagement, shown below"). Yearly media/video questions → get_media_yearly (it returns BOTH sides — always present both). Release popularity, performance or rankings MUST be answered from get_release_performance — the unified GA4+platform view — NEVER from a single tracker alone. Quote BOTH numbers per release ("2,411 platform views · 640 GA4 page views") and, when relevant, mention the extras each side sees: platform releases with no GA4 traffic (non-consented audience only) and gaOnlyReleases (older releases still earning traffic). Only use a single source when the user explicitly names it ("platform views only", "GA4 sessions"). The two counts overlap but neither contains the other — never add them together as if they were distinct people, and never present one tracker's number as "the" total without naming the source.
- You may be asked about metric definitions (e.g. what is an engaged session) — answer briefly and accurately for GA4.`;

// One-shot executive summary for a site or the portfolio — no tool loop,
// we hand the model a pre-computed data pack and ask for a briefing.
export async function aiSummary(dataPack, label, kind = 'ga') {
  if (!process.env.OPENAI_API_KEY) throw new Error('OPENAI_API_KEY not configured');
  const system = kind === 'mynews-sections'
    ? `You are a senior media-monitoring analyst writing the section commentary for a myNewspress "Media Monitoring & Insight" report on ${label}. You are given the full coverage data as JSON. Return STRICT JSON ONLY — no markdown, no code fences — with these keys, each an ARRAY of 2-4 bullet strings (empty array when that section has no data):
"keyInsight" (the opening KEY INSIGHT: press releases → articles & Reach with the best-performing release NAMED; press-fleet if visible in the data; month-on-month movement and what drove it; sentiment in one line),
"volumeReach", "overtime", "regions", "marketSplit", "topics", "models", "mediaTypes", "publications", "sentiment", "sentimentByRegion", "shareOfVoice", "releases", "journalists", "articles".
Write to the depth of the agency's MANUAL monthly report: each key is an ARRAY of 3-5 substantial bullets, each bullet 1-3 full sentences. EVERY SECTION MUST DO ALL THREE OF: (1) DESCRIBE the movement with numbers AND the month-on-month comparison whenever prev* data exists (prevSentiment, prevTopics, prevModels, prevMediaTypes, publicationStats.prevUniquePublications) — "Print declined 38% m-o-m, from 24 articles to 15"; (2) EXPLAIN why, using the "evidence" object — the actual stories behind each number: evidence.sentimentDrivers, evidence.whatTopJournalistsWrote (each top journalist's real headlines), evidence.topPublicationStories, evidence.categoryDrivers, evidence.modelDrivers, evidence.mediaTypeStories, evidence.peakDayStories — always naming the driving story, its outlet and Reach; (3) go one level DEEPER where the data allows, in the manual report's style:
- "overtime": narrate the TOP THREE peaks with dates, article counts, Reach and the named stories that drove each (evidence.peakDayStories); state the daily average from dailyStats and, when dailyStats.daysWithNoCoverage is non-empty, which days had no coverage at all.
- "topics": break the leading category into its visible themes using evidence.categoryDrivers headlines (launch coverage, reviews, crash stories, counterfeits, events…) and quantify the dominant theme's share where the model/category numbers allow ("Product made up 56% of coverage, with the bulk focused on <model>").
- "publications": open with the landscape from publicationStats — how many unique titles, how many were top-tier and what share of Reach the top tier delivered vs the rest, with the m-o-m change in unique titles — then the leading outlets and their actual stories.
- "mediaTypes": give each channel's share of articles AND Reach with m-o-m movement, and name real stories per channel from evidence.mediaTypeStories.
- "sentiment": quantify each bucket vs the previous period and attribute each to its driving themes and named stories, including what drove NEUTRAL coverage, not just positive/negative.
- "models": beyond the leader, cover the second and third models with their numbers and the stories behind them (evidence.modelDrivers).
A number without its cause is an unfinished bullet when the evidence contains one.
Write in the house analyst voice — specific, numeric, causal, British English. Examples of the voice to imitate:
- "In June, ${label} was featured in 1,684 articles, reaching a potential audience of 446M. Both volume and Reach declined month-on-month as the media frenzy surrounding the launch subsided."
- "Sentiment remained strongly favourable at 75% positive, driven above all by the wave of 296 GTS first-drive reviews — led by Top Gear's 'We've Driven The Ugly Ferrari That Broke The Internet!' (9.5M Reach)."
- "Jason Barlow was the period's leading journalist with four articles, all covering the 296 GTS launch for Top Gear — including the single highest-Reach story of the month."
- "The 12 August Reach spike traces to thesun.co.uk's Erling Haaland Ferrari SUV story (7.3M Reach), which carried an otherwise quiet news day."
- "Online remained the dominant media type, with bild.de the most prominent outlet (4 articles, 26.9M Reach). Print was led by The Sun (6.7M)."
HARD RULES: use ONLY numbers, articles, outlets and names present in the supplied data — never invent figures, outlets, events, headlines or model names; when the evidence does not explain a movement, state what the data shows and do NOT speculate about causes; point out the biggest peak days from the daily series with their dates and what ran on them (evidence.peakDayStories); percentages you compute must come from the supplied numbers.`
    : kind === 'card'
    ? `You are a senior digital PR analyst writing the insight note that sits under ONE dashboard card — "${label}". From the JSON data provided, write crisp markdown: a 2-sentence headline verdict, then 3-5 bullet facts using the real numbers, then "**Recommended actions**" with 2-3 concrete moves grounded in this exact data. If the data includes a 'guidance' field, follow it. British English, no invented numbers, no fluff.`
    : kind === 'monitoring'
    ? `You are a senior media-monitoring analyst writing the KEY INSIGHT section of a myNewspress coverage report for ${label}. You are given real press-coverage data: volume, reach, AVE, sentiment, regions, categories, models, share of voice, top publications/journalists/articles, and how each press release converted into earned coverage. Write in markdown: a 2-sentence headline verdict on the period's coverage; then "**What the coverage says**" (4-5 bullet facts with real numbers — best stories, outlets, sentiment, regions); then "**What's working / what's not**" (contrast the wins against the gaps — releases that earned nothing, weak regions, negative themes); then "**Recommended actions**" (3-4 concrete PR moves grounded in this data: who to pitch, which market to push, what angle to repeat or fix). No fluff, no invented numbers, British English.`
    : `You are a senior digital PR analyst writing for an agency's client report. You are given real Google Analytics data for ${label}. Write a crisp executive summary in markdown: a 2-sentence headline verdict, then "What happened" (3-4 bullet facts with real numbers), then "Why it matters", then "Recommended actions" (3 concrete, PR-specific actions grounded in the data — releases, distribution, channels, content). No fluff, no invented numbers, British English.`;
  const res = await fetch('https://api.openai.com/v1/chat/completions', {
    method: 'POST',
    headers: { Authorization: `Bearer ${process.env.OPENAI_API_KEY}`, 'Content-Type': 'application/json' },
    body: JSON.stringify({
      model: MODEL,
      ...(REASONING !== 'omit' ? { reasoning_effort: REASONING } : {}),
      messages: [
        { role: 'system', content: system },
        { role: 'user', content: JSON.stringify(dataPack).slice(0, 50000) },
      ],
    }),
  });
  if (!res.ok) throw new Error(`OpenAI API error (${res.status}): ${(await res.text()).slice(0, 300)}`);
  const data = await res.json();
  return data.choices[0].message.content || '';
}

// Human-readable progress labels for the live status stream.
function toolLabel(name, args = {}) {
  const site = args.site_id === 'all' ? 'all sites' : (args.site_id || '');
  switch (name) {
    case 'list_sites': return 'Listing your sites';
    case 'get_totals': return `Getting totals — ${site}`;
    case 'get_timeseries': return `Fetching daily traffic — ${site}`;
    case 'get_breakdown': return `Breaking down ${String(args.dimension || '').replace('sessionDefaultChannelGroup', 'channels').replace('pagePath', 'pages').replace('deviceCategory', 'devices').replace('sessionSource', 'sources')} — ${site}`;
    case 'get_insights': return 'Computing key facts';
    case 'get_traffic_peaks': return `Explaining traffic peaks — ${site}`;
    case 'get_search_terms': return `Reading on-site searches — ${site}`;
    case 'get_quiet_releases': return `Finding quietest releases — ${site}`;
    case 'get_navigation_flows': return `Mapping visitor journeys — ${site}`;
    case 'get_outbound_clicks': return `Tracing outbound clicks — ${site}`;
    case 'get_media_yearly': return `Comparing media engagement year-on-year — ${site}`;
    case 'get_release_performance': return `Combining GA4 + platform release data — ${site}`;
    case 'get_asset_downloads_dated': return `Computing dated ${args.type || ''} download ranking — ${site}`;
    case 'get_model_interest': return `Measuring model-by-model press interest — ${site}`;
    case 'get_email_intelligence': return `Analysing email clicks & send times — ${site}`;
    case 'get_top_assets': return `Ranking top ${args.type || 'media'} assets — ${site}`;
    case 'get_upload_stats': return `Counting uploads year-over-year — ${site}`;
    case 'get_data_reconciliation': return `Reconciling platform vs GA4 — ${site}`;
    case 'get_page_detail': return `Inspecting ${args.path || 'page'}`;
    case 'get_channel_detail': return `Drilling into ${args.channel || 'channel'}`;
    case 'get_email_campaigns': return `Fetching email campaigns — ${site}`;
    case 'get_platform_data': return `Reading platform data — ${site}${args.company_id ? ' (client-scoped)' : ''}`;
    case 'find_company': return `Finding client “${args.name || ''}”`;
    case 'search_releases': return `Searching releases for “${args.query || ''}”`;
    case 'get_release_360': return `Assembling release #${args.release_id || ''} 360°`;
    case 'get_campaign_recipients': return 'Pulling campaign recipients';
    case 'get_top_journalists': return `Ranking journalists — ${site}`;
    case 'get_email_engagers': return 'Extracting email engagers';
    case 'get_media_monitoring': return `Reading media monitoring — ${site}`;
    case 'find_person': return `Looking up “${args.name || ''}”`;
    case 'get_person_activity': return 'Fetching their full activity';
    case 'compare_sites': return 'Comparing all sites';
    case 'create_pdf_report': return 'Building your report (charts + tables)…';
    case 'create_pptx_report': return 'Building your PowerPoint deck…';
    default: return name.replace(/_/g, ' ');
  }
}

export async function chat(messages, allowedSites = null, onEvent = null, context = null) {
  const emit = (e) => { try { if (onEvent) onEvent(e); } catch { /* progress is best-effort */ } };
  if (!process.env.OPENAI_API_KEY) {
    return {
      role: 'assistant',
      content:
        "The AI assistant isn't configured yet — add your `OPENAI_API_KEY` to the `.env` file and restart the server. Everything else in the dashboard works without it.",
    };
  }
  const today = new Date().toISOString().slice(0, 10);
  let sys = SYSTEM_PROMPT.replace('{{TODAY}}', today);
  if (allowedSites) {
    sys += `\n\nIMPORTANT — USER SCOPE: this user only has access to these sites: ${allowedSites.map((s) => `${s.id} (${s.name})`).join(', ')}. All tools are hard-limited to this scope; "all"/portfolio questions mean these sites combined. Never mention or fetch other sites.`;
  }
  if (context?.siteId) {
    sys += `\n\nCURRENT VIEW — CRITICAL: the user has the dashboard OPEN on site_id "${context.siteId}"${context.siteName ? ` (${context.siteName})` : ''}${context.range ? `, period "${context.range}"` : ''}. When they do not name a site, they mean THIS site — call the tools with site_id "${context.siteId}" straight away. NEVER ask "which site" while a site is open. Likewise default the range to "${context.range || '28d'}" when they don't specify one. Only use a different site if they explicitly name it.`;
    if (context.gm?.length) sys += ` The analytics are currently FILTERED to market(s): ${context.gm.join(', ')} — GA4 numbers you fetch reflect that filter; say so when quoting them.`;
  } else if (context) {
    sys += `\n\nCURRENT VIEW: the user is on the all-sites overview. Unscoped questions mean the whole portfolio — answer with the portfolio tools (scope 'all') or the site the question clearly names; don't ask which site unless the question is impossible to scope.`;
  }
  const convo = [
    { role: 'system', content: sys },
    ...messages,
  ];

  // Files generated by tools during THIS turn (reports/decks) — used to
  // guarantee the final answer always carries a working download link, even
  // when the model mangles or omits it.
  const generated = [];
  // Tool-calling loop (max 8 rounds as a safety net)
  for (let round = 0; round < 8; round++) {
    emit({ type: 'status', label: round === 0 ? 'Reading your question…' : 'Analysing the data…' });
    const res = await fetch('https://api.openai.com/v1/chat/completions', {
      method: 'POST',
      headers: {
        Authorization: `Bearer ${process.env.OPENAI_API_KEY}`,
        'Content-Type': 'application/json',
      },
      body: JSON.stringify({
        model: MODEL, messages: convo, tools, tool_choice: 'auto',
        ...(REASONING !== 'omit' ? { reasoning_effort: REASONING } : {}),
      }),
    });
    if (!res.ok) {
      const text = await res.text();
      throw new Error(`OpenAI API error (${res.status}): ${text.slice(0, 300)}`);
    }
    const resp = await res.json();
    const msg = resp.choices[0].message;
    convo.push(msg);
    if (!msg.tool_calls?.length) {
      emit({ type: 'status', label: 'Writing the answer…' });
      let content = msg.content ?? '';
      // The model sometimes wraps report paths in a fake scheme
      // (sandbox:/reports/…) which the UI can't turn into a download
      // button — strip any such prefix back to the real /reports/… path.
      content = content.replace(/(?:sandbox|attachment|file):\/*\s*\/?(?=reports\/)/gi, '/')
        .replace(/\]\(reports\//g, '](/reports/');
      // GUARANTEE: every file generated this turn must be downloadable from
      // the answer — if the model dropped the link, append it ourselves.
      for (const g of generated) {
        if (!content.includes(g.url)) {
          content += `\n\n[Download the ${g.format === 'pptx' ? 'PowerPoint' : 'report'}](${g.url})`;
        }
      }
      return { role: 'assistant', content };
    }
    // Execute ALL of this round's tool calls IN PARALLEL — a report that
    // needs six data pulls now takes as long as the slowest one, not the sum.
    const settled = await Promise.all(msg.tool_calls.map(async (call) => {
      let args = {};
      try { args = JSON.parse(call.function.arguments || '{}'); } catch { /* tolerate */ }
      emit({ type: 'tool', label: toolLabel(call.function.name, args) });
      try {
        const result = await execTool(call.function.name, args, allowedSites);
        if (result?.download_url) generated.push({ url: result.download_url, format: result.format || '' });
        return { id: call.id, result };
      } catch (err) {
        return { id: call.id, result: { error: String(err.message || err) } };
      }
    }));
    for (const { id, result } of settled) {
      convo.push({ role: 'tool', tool_call_id: id, content: safeToolJson(result) });
    }
  }
  return { role: 'assistant', content: 'I hit the tool-call limit for one question — try asking something a little narrower.' };
}
