/**
 * models/blogPost.js
 *
 * CRUD + read helpers for the blog_posts table. Slug-based access is the
 * primary interface (URLs are /blog/:slug). The list endpoint is what the
 * /blog landing page + sitemap-blog.xml render against.
 *
 * Body content is stored as pre-rendered HTML. FAQ content is stored as a
 * JSON array of {q, a} pairs and emitted as a separate FAQPage JSON-LD
 * block on the article page (AI Overview citation lift).
 */

'use strict';

const { db } = require('../config/db');

const PUBLIC_BASE = (process.env.PUBLIC_BASE_URL || 'https://takedowns.vesamuni.com').replace(/\/+$/, '');

function safeJsonParse(s, fallback) {
  if (!s) return fallback;
  try { return JSON.parse(s); }
  catch { return fallback; }
}

function rowToPost(row) {
  if (!row) return null;
  return {
    id:               row.id,
    slug:             row.slug,
    title:            row.title,
    subtitle:         row.subtitle || '',
    excerpt:          row.excerpt,
    body_html:        row.body_html,
    category:         row.category,
    tags:             safeJsonParse(row.tags, []),
    author: {
      name: row.author_name,
      role: row.author_role || '',
      bio:  row.author_bio  || '',
    },
    hero_image_url:   row.hero_image_url,
    og_image_url:     row.og_image_url,
    reading_time_min: row.reading_time_min,
    is_published:     !!row.is_published,
    published_at:     row.published_at,
    updated_at:       row.updated_at,
    canonical_url:    PUBLIC_BASE + '/blog/' + row.slug,
    meta_description: row.meta_description,
    meta_keywords:    row.meta_keywords || '',
    related_slugs:    safeJsonParse(row.related_slugs, []),
    faq:              safeJsonParse(row.faq_json, []),
  };
}

/**
 * Public-facing list (for the blog landing page + JSON API + sitemap).
 * Only published posts, newest first (sort_order ASC then published_at DESC).
 */
function listPublished(limit = 50) {
  const rows = db.prepare(`
    SELECT * FROM blog_posts
     WHERE is_published = 1
     ORDER BY sort_order ASC, published_at DESC
     LIMIT ?
  `).all(limit);
  return rows.map(rowToPost);
}

/**
 * Full row lookup by slug. Returns null if not found OR not published.
 * Used by the /blog/:slug route handler.
 */
function getBySlug(slug) {
  const row = db.prepare(`SELECT * FROM blog_posts WHERE slug = ? AND is_published = 1`).get(slug);
  return rowToPost(row);
}

/**
 * Admin / seed-only: get row by slug regardless of is_published (so seed
 * scripts can re-upsert without false negatives).
 */
function getRawBySlug(slug) {
  const row = db.prepare(`SELECT * FROM blog_posts WHERE slug = ?`).get(slug);
  return rowToPost(row);
}

/**
 * Related posts: pull the slugs listed in `related_slugs` and return them
 * as full post rows (excluding the current post). Falls back to the
 * most-recent published posts if related_slugs is empty.
 */
function getRelated(post, limit = 3) {
  const relatedSlugs = post.related_slugs || [];
  let rows = [];
  if (relatedSlugs.length) {
    const placeholders = relatedSlugs.map(() => '?').join(',');
    rows = db.prepare(`
      SELECT * FROM blog_posts
       WHERE slug IN (${placeholders}) AND is_published = 1 AND slug != ?
       ORDER BY sort_order ASC, published_at DESC
       LIMIT ?
    `).all(...relatedSlugs, post.slug, limit);
  }
  if (rows.length < limit) {
    const exclude = [post.slug, ...rows.map(r => r.slug)];
    const placeholders = exclude.map(() => '?').join(',');
    const more = db.prepare(`
      SELECT * FROM blog_posts
       WHERE is_published = 1 AND slug NOT IN (${placeholders})
       ORDER BY sort_order ASC, published_at DESC
       LIMIT ?
    `).all(...exclude, limit - rows.length);
    rows = rows.concat(more);
  }
  return rows.map(rowToPost);
}

/**
 * Upsert by slug. Used by the seed script + future admin UI. Idempotent
 * on slug collision (ON CONFLICT update everything except id + slug).
 */
const upsert = db.prepare(`
  INSERT INTO blog_posts
    (slug, title, subtitle, excerpt, body_html, category, tags,
     author_name, author_role, author_bio,
     hero_image_url, og_image_url, reading_time_min,
     is_published, published_at, updated_at,
     meta_description, meta_keywords, related_slugs, faq_json, sort_order)
  VALUES
    (@slug, @title, @subtitle, @excerpt, @body_html, @category, @tags,
     @author_name, @author_role, @author_bio,
     @hero_image_url, @og_image_url, @reading_time_min,
     @is_published, @published_at, @updated_at,
     @meta_description, @meta_keywords, @related_slugs, @faq_json, @sort_order)
  ON CONFLICT(slug) DO UPDATE SET
    title            = excluded.title,
    subtitle         = excluded.subtitle,
    excerpt          = excluded.excerpt,
    body_html        = excluded.body_html,
    category         = excluded.category,
    tags             = excluded.tags,
    author_name      = excluded.author_name,
    author_role      = excluded.author_role,
    author_bio       = excluded.author_bio,
    hero_image_url   = excluded.hero_image_url,
    og_image_url     = excluded.og_image_url,
    reading_time_min = excluded.reading_time_min,
    is_published     = excluded.is_published,
    published_at     = excluded.published_at,
    updated_at       = excluded.updated_at,
    meta_description = excluded.meta_description,
    meta_keywords    = excluded.meta_keywords,
    related_slugs    = excluded.related_slugs,
    faq_json         = excluded.faq_json,
    sort_order       = excluded.sort_order
`);

function save(post) {
  return upsert.run({
    slug:             post.slug,
    title:            post.title,
    subtitle:         post.subtitle || null,
    excerpt:          post.excerpt,
    body_html:        post.body_html,
    category:         post.category,
    tags:             JSON.stringify(post.tags || []),
    author_name:      (post.author && post.author.name) || 'Shield Team',
    author_role:      (post.author && post.author.role) || null,
    author_bio:       (post.author && post.author.bio)  || null,
    hero_image_url:   post.hero_image_url,
    og_image_url:     post.og_image_url || post.hero_image_url,
    reading_time_min: post.reading_time_min || 5,
    is_published:     post.is_published === false ? 0 : 1,
    published_at:     post.published_at || Date.now(),
    updated_at:       post.updated_at   || Date.now(),
    meta_description: post.meta_description,
    meta_keywords:    post.meta_keywords || null,
    related_slugs:    JSON.stringify(post.related_slugs || []),
    faq_json:         post.faq ? JSON.stringify(post.faq) : null,
    sort_order:       post.sort_order || 0,
  });
}

/**
 * Count of published posts. Used by the sitemap change-frequency hint
 * and by /blog/api/posts as a sanity check.
 */
function countPublished() {
  return db.prepare(`SELECT COUNT(*) AS n FROM blog_posts WHERE is_published = 1`).get().n;
}

/**
 * Wipe all rows (used by the seed script when --force). Off by default.
 */
function deleteAll() {
  return db.prepare(`DELETE FROM blog_posts`).run();
}

module.exports = {
  listPublished,
  getBySlug,
  getRawBySlug,
  getRelated,
  save,
  countPublished,
  deleteAll,
  rowToPost,        // exported for tests
};