/**
 * db/blog.js — Blog post query functions.
 *
 * Owns: blog_posts table queries (list, getBySlug, create).
 * Does NOT own: route handling, HTML rendering, auth.
 */

'use strict';

const pool = require('./index');

/**
 * List all published blog posts ordered by published_at descending.
 * Returns: id, title, slug, excerpt, author, published_at, read_time_minutes
 */
async function listPosts() {
  const result = await pool.query(
    `SELECT id, title, slug, excerpt, author, published_at, read_time_minutes,
            COALESCE(coming_soon, FALSE) AS coming_soon
     FROM blog_posts
     WHERE COALESCE(seo_noindex, FALSE) = FALSE
     ORDER BY COALESCE(coming_soon, FALSE) ASC, published_at DESC`
  );
  return result.rows;
}

/**
 * Fetch a single post by slug.
 * Returns full row including content, or null if not found.
 */
async function getPostBySlug(slug) {
  const result = await pool.query(
    `SELECT id, title, slug, excerpt, content, author, published_at, read_time_minutes
     FROM blog_posts
     WHERE slug = :p1`,
    { p1: slug }
  );
  return result.rows[0] || null;
}

/**
 * Insert a new blog post. Used by seed script.
 */
async function createPost({ title, slug, excerpt, content, author, published_at, read_time_minutes }) {
  // content is a CLOB and post bodies routinely exceed the 32767-byte VARCHAR2
  // bind ceiling; inside MERGE ... USING there is no target column for the driver
  // to infer from, so it is bound as a CLOB explicitly (ORA-01461).
  await pool.query(
    `MERGE INTO blog_posts t
     USING (SELECT :p2 AS slug FROM DUAL) s
        ON (t.slug = s.slug)
     WHEN MATCHED THEN UPDATE SET
       t.title             = :p1,
       t.excerpt           = :p3,
       t.content           = :p4,
       t.author            = :p5,
       t.published_at      = :p6,
       t.read_time_minutes = :p7,
       t.updated_at        = CURRENT_TIMESTAMP
     WHEN NOT MATCHED THEN INSERT
       (title, slug, excerpt, content, author, published_at, read_time_minutes)
       VALUES (:p1, s.slug, :p3, :p4, :p5, :p6, :p7)`,
    {
      p1: title, p2: slug, p3: excerpt, p4: pool.clob(content),
      p5: author || 'TuneVault Team', p6: pool.tstz(published_at), p7: read_time_minutes || 10,
    }
  );
  const result = await pool.query(
    `SELECT id FROM blog_posts WHERE slug = :p1`, { p1: slug });
  return result.rows[0];
}

/**
 * List all slugs for sitemap generation.
 */
async function listSlugs() {
  const result = await pool.query(
    `SELECT slug, updated_at FROM blog_posts WHERE COALESCE(seo_noindex, FALSE) = FALSE ORDER BY published_at DESC`
  );
  return result.rows;
}

module.exports = { listPosts, getPostBySlug, createPost, listSlugs };
