-- =============================================================================
-- AI SEO Manager — 002_seed
-- Reference data. All INSERTs are idempotent (ON DUPLICATE KEY UPDATE), so this
-- file is safe to re-run after you edit a weight or a threshold.
--
-- This seeds the checks the engine implements in phases 1–3 plus the full AI
-- crawler registry. To add the remaining catalogue entries from the spec,
-- append rows here — check_definition is config, so no code change is needed.
-- =============================================================================


-- ------------------------------------------------------- metric definitions --
-- A metric registry, so a fourth Core Web Vital is a row, not a migration.
-- FID is deliberately absent: Search Console swapped it for INP on 2024-03-12
-- and the Chrome tooling dropped it on 2024-09-10.
INSERT INTO metric_definition
  (metric_key, label, unit, threshold_good, threshold_poor, higher_is_better, is_core, effective_from) VALUES
  ('lcp_p75',  'Largest Contentful Paint', 'ms',  2500, 4000, 0, 1, '2020-05-01'),
  ('inp_p75',  'Interaction to Next Paint','ms',   200,  500, 0, 1, '2024-03-12'),
  ('cls_p75',  'Cumulative Layout Shift',  '',     0.1, 0.25, 0, 1, '2020-05-01'),
  ('ttfb_p75', 'Time to First Byte',       'ms',   800, 1800, 0, 0, '2021-01-01'),
  ('fcp_p75',  'First Contentful Paint',   'ms',  1800, 3000, 0, 0, '2020-05-01'),
  ('tbt_lab',  'Total Blocking Time',      'ms',   200,  600, 0, 0, '2020-05-01'),
  ('si_lab',   'Speed Index',              'ms',  3400, 5800, 0, 0, '2020-05-01'),
  ('render_delta_pct','Retrievability gap','%',      10,   30, 0, 0, '2026-01-01')
ON DUPLICATE KEY UPDATE
  label=VALUES(label), threshold_good=VALUES(threshold_good),
  threshold_poor=VALUES(threshold_poor), is_core=VALUES(is_core);

-- ---------------------------------------------------------- check definitions --
-- weight follows severity: critical 10 · high 5 · medium 2 · low 0.5
-- confidence: 1.00 deterministic · 0.50–0.80 heuristic (protects the score from
-- fuzzy checks and is the defence when a finding is disputed)
INSERT INTO check_definition
 (code, category, subcategory, title, severity, weight, confidence, plane, eligible_unit, evidence_tier, remediation) VALUES

-- crawlability & indexation ---------------------------------------------------
('T001','crawlability','indexation','noindex on an indexable page','critical',10,1.00,'crawl','indexable_url','primary','Remove noindex from the meta robots tag and the X-Robots-Tag header. Remember the most restrictive rule wins and Google honours the tag even in <body>.'),
('T002','crawlability','indexation','noindex injected by JavaScript','critical',10,1.00,'render','rendered_url','primary','Robots directives must match between raw and rendered HTML. Never mutate them client-side.'),
('T003','crawlability','indexation','robots.txt blocks an important URL','critical',10,1.00,'crawl','indexable_url','primary','Narrow the Disallow rule. Remember the * group applies only when no specific group matches.'),
('T004','crawlability','indexation','robots.txt unreachable or 5xx','critical',10,1.00,'crawl','host','primary','A persistent 5xx on robots.txt is treated by Googlebot as disallow-all. Fix the server or serve a static file.'),
('T005','crawlability','indexation','robots.txt larger than 500 KiB','high',5,1.00,'crawl','host','primary','Content past 500 KiB is ignored. Trim the file.'),
('T006','crawlability','indexation','Unsupported robots.txt directive','medium',2,1.00,'crawl','host','primary','Google ignores crawl-delay, noindex and nofollow in robots.txt. Use meta robots or Search Console settings instead.'),
('T007','crawlability','indexation','noindex on a page that is also Disallowed','high',5,1.00,'crawl','indexable_url','primary','Google cannot read the noindex if it cannot fetch the page, so the URL can stay indexed URL-only. Allow the crawl, keep the noindex.'),
('T009','crawlability','status','Non-200 status on an indexable URL','critical',10,1.00,'crawl','indexable_url','primary','Restore the page or 301 it to the correct destination and remove it from the sitemap.'),
('T010','crawlability','status','Soft 404','high',5,0.70,'crawl','indexable_url','primary','Return a real 404 or 410, or restore meaningful content.'),
('T011','crawlability','status','5xx or 429 during crawl','critical',10,1.00,'crawl','crawled_url','primary','Server instability reduces Google crawl capacity. Investigate load and error rates.'),
('T012','crawlability','redirects','Redirect chain or loop','high',5,1.00,'crawl','crawled_url','primary','Point every internal link and redirect at the final destination in one hop.'),
('T014','crawlability','redirects','Meta-refresh or JS redirect','medium',2,0.80,'crawl','crawled_url','primary','Use a 301 or 302 HTTP redirect instead.'),
('T015','crawlability','architecture','Orphan page','high',5,0.80,'crawl','indexable_url','primary','Add contextual internal links from relevant hub pages.'),
('T018','crawlability','indexation','Parameter or facet crawl trap','high',5,0.70,'crawl','crawled_url','primary','Canonicalise faceted URLs, block the parameter space, or serve it behind POST.'),
('T019','crawlability','indexation','Session or tracking parameter in an internal link','medium',2,1.00,'crawl','internal_link','primary','Strip utm_*, gclid, fbclid and session IDs from internal hrefs.'),
('T020','crawlability','architecture','Uncrawlable pagination','high',5,0.80,'render','crawled_url','primary','Render pagination as real <a href> links to distinct URLs.'),
('T022','crawlability','indexation','unavailable_after date is in the past','high',5,1.00,'crawl','indexable_url','primary','Remove the directive if the page should stay indexed.'),
('T024','crawlability','ai','llms.txt present','low',0.5,1.00,'crawl','host','third_party','Informational only. 97% of llms.txt files receive zero requests and Google states no AI text file is required. Keep it only for docs targeting coding agents.'),

-- architecture & internal linking ---------------------------------------------
('T025','architecture','depth','Click depth greater than 4 from the homepage','high',5,0.80,'crawl','indexable_url','primary','Add hub links or flatten the navigation for commercially important pages.'),
('T026','architecture','links','Broken internal link','high',5,1.00,'crawl','internal_link','primary','Fix or remove the link.'),
('T027','architecture','links','Internal link to a redirect','medium',2,1.00,'crawl','internal_link','primary','Update the href to the final URL.'),
('T028','architecture','links','Indexable page with fewer than 3 internal inlinks','medium',2,0.70,'crawl','indexable_url','primary','Add contextual links from related pages.'),
('T030','architecture','links','Non-crawlable anchor','high',5,1.00,'crawl','anchor','primary','Use <a href="..."> rather than click handlers, href="#" or javascript: URLs.'),
('T033','architecture','links','Template has no contextual outbound links','medium',2,0.60,'crawl','template','primary','Add in-body editorial links; navigation and footer links alone do not distribute equity meaningfully.'),

-- performance -----------------------------------------------------------------
('T036','performance','cwv','Field Core Web Vitals failing at p75','critical',10,1.00,'api','url_group','primary','Assess at the 75th percentile, segmented by form factor. Fix the metric that is poor, not the Lighthouse score.'),
('T037','performance','cwv','Lab Core Web Vitals failing','high',5,0.80,'render','template','primary','Use the Lighthouse insight audits to find the specific culprit.'),
('T038','performance','server','TTFB above 0.8 s','high',5,1.00,'api','url_group','primary','Cache at the edge, reduce server work, and check database query time.'),
('T039','performance','images','LCP element is lazy-loaded','high',5,1.00,'render','template','primary','Remove loading="lazy" from the hero and LCP element; add fetchpriority="high".'),
('T041','performance','render','Render-blocking resources','high',5,0.90,'crawl','template','primary','Defer non-critical CSS and JS; inline critical CSS.'),
('T042','performance','transport','Response not compressed','high',5,1.00,'crawl','crawled_url','primary','Enable brotli or gzip for text, HTML, CSS, JS and JSON.'),
('T043','performance','transport','Static asset missing cache headers','medium',2,1.00,'crawl','asset','primary','Set Cache-Control: public, max-age=31536000, immutable on hashed assets.'),
('T046','performance','transport','No HTTP/2 or HTTP/3','medium',2,1.00,'crawl','host','primary','Enable HTTP/2 at minimum; advertise h3 via Alt-Svc.'),
('T049','performance','cls','Image or iframe without dimensions','high',5,1.00,'crawl','image','primary','Set width and height attributes or a CSS aspect-ratio.'),

-- rendering -------------------------------------------------------------------
('T052','rendering','js','Main content only present after JS execution','high',5,1.00,'render','rendered_url','primary','Server-render or pre-render the main content. Most AI crawlers never execute JavaScript.'),
('T053','rendering','js','Links only present after JS execution','high',5,1.00,'render','rendered_url','primary','Emit real anchors in the server response.'),
('T055','rendering','js','Canonical mutated by JavaScript','critical',10,1.00,'render','rendered_url','primary','Set the canonical server-side and never change it client-side.'),
('T057','rendering','js','Fragment-only routing','critical',10,1.00,'crawl','crawled_url','primary','Use History API URLs that return real HTML on direct request.'),
('T058','rendering','js','Client-side-only 404','high',5,0.80,'render','rendered_url','primary','Return HTTP 404 from the server for unknown routes.'),

-- structured data -------------------------------------------------------------
('T064','structured_data','validity','JSON-LD parse error','high',5,1.00,'crawl','schema_block','primary','Fix the JSON syntax; check for HTML-escaped payloads.'),
('T065','structured_data','validity','Missing required schema property','high',5,1.00,'crawl','schema_entity','primary','Add the properties Google lists as required for that rich-result feature.'),
('T067','structured_data','validity','Unrecognised @type','medium',2,1.00,'crawl','schema_entity','primary','Use a type that exists in the schema.org vocabulary.'),
('T068','structured_data','policy','Structured data does not match visible text','high',5,0.70,'crawl','schema_entity','primary','Google requires markup to describe visible content. Align the values or remove the markup.'),
('T069','structured_data','policy','Schema price or availability differs from the page','high',5,0.80,'crawl','schema_entity','primary','Generate offers data from the same source as the rendered price.'),

-- international ---------------------------------------------------------------
('T075','international','hreflang','hreflang cluster missing return links','critical',10,1.00,'crawl','hreflang_edge','primary','Reciprocity is mandatory: if X points to Y, Y must point back to X, or Google ignores the annotations entirely.'),
('T076','international','hreflang','Missing self-referential hreflang','high',5,1.00,'crawl','hreflang_node','primary','Every page in a cluster must list itself.'),
('T077','international','hreflang','Invalid language or region code','high',5,1.00,'crawl','hreflang_edge','primary','ISO 639-1 language plus optional ISO 3166-1 alpha-2 region. Use GB not UK; region-only values are invalid.'),
('T078','international','hreflang','hreflang target is not indexable','high',5,1.00,'crawl','hreflang_edge','primary','Every target must return 200, self-canonicalise and be indexable.'),
('T079','international','hreflang','More than one hreflang method in use','medium',2,1.00,'crawl','host','primary','Pick one of HTML link, HTTP Link header or sitemap. Mixing has no benefit and risks conflicts.'),
('T082','international','hreflang','hreflang conflicts with the canonical','high',5,1.00,'crawl','hreflang_edge','primary','Never cross-canonicalise localised variants; each must self-canonicalise.'),
('T083','international','hreflang','html lang does not match the self-hreflang','medium',2,1.00,'crawl','indexable_url','primary','Set <html lang> to the same language subtag, and dir="rtl" for Arabic.'),

-- security & hygiene ----------------------------------------------------------
('T085','security','https','Indexable page served over HTTP','critical',10,1.00,'crawl','indexable_url','primary','Serve everything over HTTPS and 301 HTTP to HTTPS.'),
('T086','security','https','Mixed content','high',5,1.00,'render','rendered_url','primary','Load every subresource over HTTPS.'),
('T087','security','tls','Certificate expiring, mismatched or weak','critical',10,1.00,'crawl','host','primary','Renew the certificate; ensure the SAN covers every host; require TLS 1.2+.'),
('T088','security','https','HTTP does not redirect to HTTPS','high',5,1.00,'crawl','host','primary','Add a 301 from http:// to https://.'),
('T091','security','duplication','www and non-www both return 200','high',5,1.00,'crawl','host','primary','Pick one canonical host and 301 the other.'),
('T096','security','status','Custom 404 page returns HTTP 200','high',5,1.00,'crawl','host','primary','Return a real 404 or 410 status with the custom page.'),

-- mobile ----------------------------------------------------------------------
('T100','mobile','viewport','Missing or restrictive viewport meta','high',5,1.00,'crawl','indexable_url','primary','Use width=device-width, initial-scale=1 and do not set user-scalable=no.'),
('T101','mobile','parity','Mobile and desktop content parity gap','critical',10,0.80,'render','template','primary','Under mobile-first indexing the mobile rendering is the indexed rendering. Serve the same content, links and structured data.'),
('T102','mobile','layout','Horizontal overflow at mobile viewport','high',5,1.00,'render','template','primary','Fix fixed-width elements and overflowing tables.'),

-- duplication & canonicalisation ---------------------------------------------
('T109','duplication','canonical','Missing rel=canonical','medium',2,1.00,'crawl','indexable_url','primary','Add an absolute self-referencing canonical in <head>.'),
('T110','duplication','canonical','Multiple conflicting canonicals','high',5,1.00,'crawl','indexable_url','primary','Google ignores all of them when they disagree. Emit exactly one.'),
('T111','duplication','canonical','Canonical placed in <body>','high',5,1.00,'crawl','indexable_url','primary','Canonicals belong exclusively in <head>.'),
('T112','duplication','canonical','Relative canonical URL','medium',2,1.00,'crawl','indexable_url','primary','Use an absolute URL.'),
('T113','duplication','canonical','Canonical target is not indexable','critical',10,1.00,'crawl','indexable_url','primary','Point the canonical at a 200, indexable, self-canonical URL.'),
('T114','duplication','canonical','Canonical chain or loop','high',5,1.00,'crawl','indexable_url','primary','Canonicals must point directly at the final URL.'),
('T118','duplication','content','Exact duplicate main content','high',5,1.00,'crawl','indexable_url','primary','Consolidate with a 301 or canonical, or differentiate the content substantively.'),
('T119','duplication','content','Near-duplicate main content','medium',2,0.70,'crawl','indexable_url','primary','Merge or differentiate. Swapping only a city name will still cannibalise.'),
('T120','duplication','metadata','Duplicate title, meta description or H1','medium',2,1.00,'crawl','indexable_url','primary','Write unique metadata per page.'),
('T123','duplication','host','Multiple host or scheme variants indexable','high',5,1.00,'crawl','host','primary','Redirect all variants to one canonical host and scheme.'),

-- sitemaps & robots -----------------------------------------------------------
('T126','sitemaps','discovery','Sitemap missing or not referenced in robots.txt','medium',2,1.00,'crawl','host','primary','Publish a sitemap and add a Sitemap: line to robots.txt.'),
('T127','sitemaps','limits','Sitemap exceeds 50,000 URLs or 50 MB','high',5,1.00,'crawl','sitemap','primary','Split into multiple sitemaps behind a sitemap index.'),
('T128','sitemaps','validity','Sitemap XML invalid','high',5,1.00,'crawl','sitemap','primary','Validate against the sitemap 0.9 schema; escape &, < and apostrophes; serve UTF-8.'),
('T129','sitemaps','content','Sitemap contains non-indexable URLs','high',5,1.00,'crawl','sitemap_url','primary','A sitemap should list only canonical, indexable, 200 URLs.'),
('T131','sitemaps','coverage','Indexable page missing from the sitemap','medium',2,1.00,'crawl','indexable_url','primary','Regenerate the sitemap from the canonical URL set.'),
('T132','sitemaps','lastmod','Unreliable lastmod','medium',2,0.70,'crawl','sitemap_url','primary','Google uses lastmod only when it is consistently and verifiably accurate. Emit it from real content change, not from deploy time.'),
('T137','sitemaps','robots','robots.txt syntax error','medium',2,1.00,'crawl','host','primary','Every rule must sit inside a User-agent group; Disallow paths need a leading slash; serve UTF-8 without a BOM.'),
('T138','sitemaps','robots','robots.txt changed since the last run','critical',10,1.00,'crawl','host','primary','Review the diff immediately. This is how a staging robots.txt reaching production is caught.'),
('T139','sitemaps','robots','Blanket Disallow: / present','critical',10,1.00,'crawl','host','primary','Remove it. This single line can de-index an entire site.'),

-- on-page ---------------------------------------------------------------------
('O001','onpage','title','Missing or empty <title>','critical',10,1.00,'crawl','indexable_url','primary','Write a unique, descriptive title.'),
('O002','onpage','title','Duplicate titles','high',5,1.00,'crawl','indexable_url','primary','Make each title unique and specific.'),
('O003','onpage','title','Title at risk of SERP truncation','medium',2,0.80,'crawl','indexable_url','primary','Measure rendered pixel width rather than character count; front-load the distinctive words.'),
('O009','onpage','meta','Missing meta description','medium',2,1.00,'crawl','indexable_url','primary','Write a description that earns the click; Google may still rewrite it.'),
('O014','onpage','headings','Missing H1','high',5,1.00,'crawl','indexable_url','primary','Add one descriptive H1 that matches the page intent.'),
('O016','onpage','headings','Empty or image-only H1','high',5,1.00,'crawl','indexable_url','primary','Use real text; if the H1 wraps an image, give the image alt text.'),
('O017','onpage','headings','Broken heading hierarchy','medium',2,1.00,'crawl','indexable_url','primary','Do not skip levels; headings are structure, not styling.'),
('O024','onpage','intent','Page type does not match query intent','high',5,0.60,'api','indexable_url','third_party','Match the page type to what the country SERP actually serves for the target query.'),
('O031','onpage','content','Thin content','high',5,0.80,'crawl','indexable_url','primary','Expand with genuinely useful, non-commodity detail, or consolidate the page away.'),
('O042','onpage','eeat','Missing author byline','high',5,0.60,'crawl','article_url','third_party','Add a named author with a Person entity and a real bio. Treat as a best-practice proxy, not a ranking signal.'),
('O046','onpage','eeat','dateModified advancing without content change','high',5,0.80,'crawl','article_url','primary','Only update the modified date when the content substantively changes.'),
('O047','onpage','eeat','Missing About or Contact page','high',5,1.00,'crawl','host','primary','Publish reachable About, Contact and, for YMYL, editorial-policy pages.'),
('O053','onpage','images','Image missing alt attribute','high',5,1.00,'crawl','image','primary','Write short descriptive alt text; use alt="" only for decorative images.'),
('O061','onpage','images','Image missing width and height','high',5,1.00,'crawl','image','primary','Set intrinsic dimensions to prevent layout shift.'),
('O067','onpage','links','Non-descriptive anchor text','medium',2,1.00,'crawl','internal_link','primary','Replace "click here" and "read more" with text that describes the destination.'),
('O079','onpage','i18n','Detected content language differs from html lang','medium',2,0.80,'crawl','indexable_url','primary','Fix the lang attribute or the content. Critical for the Arabic pages.'),
('O080','onpage','content','Placeholder or untranslated content published','high',5,1.00,'crawl','indexable_url','primary','Remove lorem ipsum, TODO, template tokens, undefined and NaN from live pages.'),
('O084','onpage','cannibalisation','Query cannibalisation','high',5,0.70,'api','query','primary','Consolidate to one page per (keyword, country, language), or differentiate intent substantively.'),
('O087','onpage','duplication','Duplicate content across own sibling domains','high',5,1.00,'crawl','indexable_url','primary','With four Dolphin properties sharing a catalogue this is near-certain. Use cross-domain canonicals or differentiate.'),
('O088','onpage','decay','Content decay','high',5,0.70,'api','indexable_url','primary','Refresh substantively; classify whether impressions, CTR or position drove the loss before rewriting.'),
('O095','onpage','conversion','Commercial page has no call to action','high',5,0.70,'crawl','commercial_url','primary','Add a quote request, contact or enquiry action above the fold on mobile.'),

-- AI SEO: access --------------------------------------------------------------
('A01','ai_access','robots','robots.txt not reachable or invalid','critical',10,1.00,'crawl','host','primary','Serve a valid text/plain robots.txt under 500 KiB.'),
('A03','ai_access','retrieval','OAI-SearchBot disallowed','critical',10,1.00,'crawl','path','primary','OpenAI documents that sites opted out of OAI-SearchBot will not be shown in ChatGPT search answers. ChatGPT is around 75% of measurable AI referral traffic.'),
('A04','ai_access','retrieval','PerplexityBot disallowed','critical',10,1.00,'crawl','path','primary','Blocking PerplexityBot removes the site from Perplexity results. It is not used for model training.'),
('A05','ai_access','retrieval','Claude-SearchBot disallowed','high',5,1.00,'crawl','path','primary','Allow it if you want to appear in Claude search results.'),
('A06','ai_access','retrieval','Googlebot disallowed','critical',10,1.00,'crawl','path','primary','Googlebot gates AI Overviews and AI Mode as well as ordinary Search. There is no separate AI crawler.'),
('A07','ai_access','retrieval','Bingbot disallowed','critical',10,1.00,'crawl','path','primary','Bingbot feeds both Bing and Copilot.'),
('A09','ai_access','training','Training crawler blocked','info',0,1.00,'crawl','path','primary','Reported for transparency only. Blocking training crawlers is a legitimate business choice with no effect on current AI search visibility.'),
('A10','ai_access','policy','Google-Extended blocked while AI Overview suppression is expected','high',5,1.00,'crawl','host','primary','Google-Extended is a robots.txt token with no user agent. It governs Gemini training and grounding only, and has no effect on AI Overviews, AI Mode, indexing or ranking.'),
('A12','ai_access','meta','noarchive present — excludes the page from Copilot answers','critical',10,1.00,'crawl','indexable_url','primary','Remove noarchive unless Copilot exclusion is intended. It is often legacy boilerplate from pre-AI archive policies.'),
('A13','ai_access','meta','nocache present — limits Copilot to URL, title and snippet','medium',2,1.00,'crawl','indexable_url','primary','Remove it if full Copilot answer eligibility is wanted.'),
('A14','ai_access','meta','nosnippet or max-snippet:0 removes AI Overview eligibility','critical',10,1.00,'crawl','indexable_url','primary','These are the real levers for Google AI surfaces. Remove them unless suppression is intended.'),
('A15','ai_access','meta','data-nosnippet wraps the primary answer','high',5,0.80,'render','template','primary','Move data-nosnippet off the answer text.'),
('A17','ai_access','waf','AI user agent blocked at the edge','critical',10,1.00,'crawl','path','primary','A 403, 429 or challenge page for an AI user agent while Chrome gets 200 is a hard block invisible in robots.txt. Fix the WAF, CDN or bot-management rule.'),
('A18','ai_access','monetisation','HTTP 402 returned to AI crawlers','info',0,1.00,'crawl','path','primary','Cloudflare pay-per-crawl is active. Intentional monetisation, not an error.'),
('A22','ai_access','consistency','robots.txt contradicts meta or header directives','high',5,1.00,'crawl','host','primary','Reconcile the layers; the most restrictive wins and the intent is usually ambiguous.'),
('A24','ai_access','limits','Response exceeds the Googlebot fetch cap','medium',2,1.00,'crawl','indexable_url','primary','Googlebot crawls the first 2 MB of a supported file type and the first 64 MB of a PDF. Reduce the payload.'),

-- AI SEO: retrievability & structure -----------------------------------------
('B01','ai_retrieval','rendering','Retrievability gap above 30%','critical',10,1.00,'render','template','third_party','Most AI crawlers do not execute JavaScript. Server-render the main content, headings and primary answer text.'),
('B02','ai_retrieval','rendering','Primary answer text absent from raw HTML','critical',10,1.00,'crawl','indexable_url','third_party','Emit the answer in the server response.'),
('B04','ai_retrieval','interstitial','Consent or geo wall served to non-JS clients','critical',10,0.80,'crawl','path','primary','Serve content to crawlers; gate only what genuinely requires consent.'),
('B07','ai_retrieval','media','Content only available in images or PDFs','high',5,0.70,'crawl','indexable_url','third_party','Publish spec tables as real HTML tables alongside the PDF datasheet.'),
('C01','ai_structure','position','Answer does not appear in the first 30% of content','high',5,0.60,'crawl','article_url','third_party','44% of ChatGPT citations originate in the first third of the content. Lead each section with the answer.'),
('C02','ai_structure','headings','H2 headings are not phrased as questions','high',5,0.60,'crawl','article_url','third_party','78% of question-related citations came from H2s. Use the PAA questions as headings.'),
('C05','ai_structure','phrasing','No definitional phrasing present','high',5,0.50,'crawl','article_url','third_party','Cited passages use "X is" and "X refers to" about twice as often.'),
('C06','ai_structure','entities','Low named-entity density','high',5,0.50,'crawl','article_url','third_party','Cited passages average around 20% proper-noun density versus a typical 5-8%. Name standards, materials, models and places.'),
('C09','ai_structure','evidence','Low statistics density','high',5,0.50,'crawl','article_url','third_party','Adding statistics measured +34% visibility in the GEO study.'),
('C10','ai_structure','evidence','No quotations from credible sources','high',5,0.50,'crawl','article_url','third_party','The strongest single intervention in the GEO study at +43%.'),
('C11','ai_structure','evidence','No outbound citations','high',5,0.50,'crawl','article_url','third_party','+29% in the GEO study.'),
('C12','ai_structure','spam','Keyword stuffing detected','high',5,0.80,'crawl','indexable_url','third_party','Measurably counterproductive: -8% in the GEO study, the only intervention of nine that hurt.'),
('C14','ai_structure','markup','Tabular data rendered as divs or images','high',5,0.70,'crawl','template','third_party','Use real <table> markup with <th> and <caption> so passages extract cleanly.'),
('C16','ai_structure','dates','Published or modified date inconsistent across sources','high',5,1.00,'crawl','article_url','primary','Align the visible date, the JSON-LD date and the sitemap lastmod.'),
('D02','ai_structure','schema','Structured data does not match visible text','high',5,0.70,'crawl','schema_entity','primary','This is Google''s actual stated requirement and a real spam vector.'),
('D06','ai_structure','schema','FAQPage present while rich results are still expected','info',0,1.00,'crawl','schema_entity','primary','FAQ rich results stopped appearing on 7 May 2026 and Search Console API support ended in August 2026. Google says there is no need to remove the markup, but it earns nothing.'),
('D10','ai_structure','schema','Do not score missing schema as an AI visibility defect','info',0,1.00,'crawl','host','third_party','A matched study of 1,885 pages adding JSON-LD found no positive effect on AI citations. Keep schema for rich results and entity resolution.'),
('F01','ai_measure','logging','No server-side bot logging','critical',10,1.00,'log','host','primary','AI crawlers do not execute JavaScript, so client-side analytics cannot see them at all. Ship server-side logging or CDN log export.'),
('E01','ai_entity','mentions','Unlinked brand mentions not tracked','high',5,0.60,'api','host','third_party','Branded web mentions correlate with AI visibility about three times more strongly than backlinks.')

ON DUPLICATE KEY UPDATE
  title=VALUES(title), severity=VALUES(severity), weight=VALUES(weight),
  confidence=VALUES(confidence), plane=VALUES(plane),
  eligible_unit=VALUES(eligible_unit), evidence_tier=VALUES(evidence_tier),
  remediation=VALUES(remediation), category=VALUES(category), subcategory=VALUES(subcategory);

-- ----------------------------------------------------------- country config ---
-- Language weighting is per country, never a global default: UAE is
-- English-dominant for B2B; Saudi Arabia is roughly 71% Arabic.
INSERT INTO country_config
 (country_code, label, default_language, secondary_language, language_weights_json, location_code, device_default, currency) VALUES
('AE','United Arab Emirates','en','ar','{"en":0.72,"ar":0.28}',2784,'mobile','AED'),
('SA','Saudi Arabia',        'ar','en','{"ar":0.71,"en":0.24}',2682,'mobile','SAR'),
('QA','Qatar',               'en','ar','{"en":0.60,"ar":0.40}',2634,'mobile','QAR'),
('KW','Kuwait',              'ar','en','{"ar":0.62,"en":0.38}',2414,'mobile','KWD'),
('OM','Oman',                'en','ar','{"en":0.55,"ar":0.45}',2512,'mobile','OMR'),
('BH','Bahrain',             'en','ar','{"en":0.62,"ar":0.38}',2048,'mobile','BHD'),
('IN','India',               'en',NULL, '{"en":1.0}',           2356,'mobile','INR'),
('GB','United Kingdom',      'en',NULL, '{"en":1.0}',           2826,'desktop','GBP'),
('US','United States',       'en',NULL, '{"en":1.0}',           2840,'desktop','USD')
ON DUPLICATE KEY UPDATE
  label=VALUES(label), default_language=VALUES(default_language),
  secondary_language=VALUES(secondary_language),
  language_weights_json=VALUES(language_weights_json),
  location_code=VALUES(location_code), device_default=VALUES(device_default);

-- NOTE: verify every location_code against
-- /v3/dataforseo_labs/locations_and_languages before going live. They are
-- stable but must not be trusted from a seed file.

-- --------------------------------------------------------- domain blocklist ---
-- Without this the competitor feature returns directories, not competitors.
INSERT INTO domain_blocklist (domain, class, region, vertical, note) VALUES
('alibaba.com','marketplace',NULL,'industrial',NULL),
('made-in-china.com','marketplace',NULL,'industrial',NULL),
('indiamart.com','marketplace',NULL,'industrial',NULL),
('globalsources.com','marketplace',NULL,'industrial',NULL),
('tradeindia.com','marketplace',NULL,'industrial',NULL),
('exportersindia.com','marketplace',NULL,'industrial',NULL),
('ec21.com','marketplace',NULL,'industrial',NULL),
('thomasnet.com','directory','US','industrial',NULL),
('europages.com','directory','EU','industrial',NULL),
('directindustry.com','directory','EU','industrial',NULL),
('kompass.com','directory',NULL,'industrial',NULL),
('yellowpages.ae','directory','AE',NULL,NULL),
('yellowpages-uae.com','directory','AE',NULL,NULL),
('connect.ae','directory','AE',NULL,'Government-supported — high-value LISTING target, not a competitor'),
('dcciinfo.ae','directory','AE',NULL,'Dubai Chamber — high-value listing target'),
('2gis.ae','directory','AE',NULL,NULL),
('sio365.com','directory','GCC','industrial',NULL),
('localsearch.ae','directory','AE',NULL,NULL),
('businessfinder.ae','directory','AE',NULL,NULL),
('accio.com','aggregator_leadgen',NULL,'industrial','AI sourcing aggregator, 2025-26 cohort'),
('ensun.io','aggregator_leadgen',NULL,'industrial','Supplier-discovery platform'),
('tradeford.com','aggregator_leadgen',NULL,'industrial',NULL),
('expertmarketresearch.com','publisher_media',NULL,NULL,'Market-research publisher'),
('mepmiddleeast.com','publisher_media','GCC','hvac','Trade media power lists'),
('constructionweekonline.com','publisher_media','GCC',NULL,NULL),
('alfalaval.com','oem_principal',NULL,'heat_transfer','Likely a Dolphin principal — verify against the Brands page'),
('kelvion.com','oem_principal',NULL,'heat_transfer','Likely a principal'),
('swep.net','oem_principal',NULL,'heat_transfer','Likely a principal'),
('danfoss.com','oem_principal',NULL,'hvac','Likely a principal'),
('daikin.com','oem_principal',NULL,'hvac','Likely a principal'),
('carrier.com','oem_principal',NULL,'hvac','Likely a principal'),
('spxflow.com','oem_principal',NULL,'heat_transfer','Likely a principal'),
('u.ae','gov_assoc','AE',NULL,NULL),
('esma.gov.ae','gov_assoc','AE',NULL,NULL),
('ashrae.org','gov_assoc',NULL,'hvac',NULL)
ON DUPLICATE KEY UPDATE class=VALUES(class), region=VALUES(region),
  vertical=VALUES(vertical), note=VALUES(note);

-- ------------------------------------------------------------------- sites ---
INSERT INTO site (name, primary_domain, division, default_country, default_language) VALUES
('Dolphin Catalogue',      'REPLACE-ME-catalogue.example',   'catalogue',     'AE','en'),
('Super Dolphin Trading',  'REPLACE-ME-trading.example',     'trading',       'AE','en'),
('Dolphin Heat Transfer',  'REPLACE-ME-heattransfer.example','heat_transfer', 'AE','en'),
('Dolphin HVAC',           'REPLACE-ME-hvac.example',        'hvac',          'AE','en')
ON DUPLICATE KEY UPDATE name=VALUES(name), division=VALUES(division);
