const express = require('express');
const router = express.Router();
const db = require('../db');

function withTags(article) {
  if (!article) return article;
  const tags = db.prepare(`
    SELECT t.id, t.name, t.slug
    FROM tags t
    JOIN article_tags at ON at.tag_id = t.id
    WHERE at.article_id = ?
    ORDER BY t.name ASC
  `).all(article.id);
  return { ...article, tags, tag_ids: tags.map((t) => t.id) };
}

// GET /api/articles - List published articles
router.get('/', (req, res) => {
  const page = parseInt(req.query.page) || 1;
  const limit = parseInt(req.query.limit) || 20;
  const offset = (page - 1) * limit;
  const category = req.query.category; // category slug
  const search = req.query.search;
  const tag = req.query.tag;

  let query = `
    SELECT a.*, c.name as category_name, c.slug as category_slug,
           u.full_name as writer_full_name, u.username as writer_username,
           u.avatar_url as writer_avatar_url, u.twitter_url as writer_twitter_url,
           COALESCE(NULLIF(u.full_name, ''), NULLIF(u.username, ''), NULLIF(a.author, ''), 'ބިލެއްދޫ') as display_author
    FROM articles a
    JOIN categories c ON a.category_id = c.id
    LEFT JOIN users u ON a.user_id = u.id
    WHERE a.status = 'published' AND (a.scheduled_at IS NULL OR a.scheduled_at <= CURRENT_TIMESTAMP)
  `;
  const params = [];

  if (category) {
    query += ` AND c.slug = ?`;
    params.push(category);
  }

  if (tag) {
    query += ` AND EXISTS (
      SELECT 1
      FROM article_tags at
      JOIN tags t ON t.id = at.tag_id
      WHERE at.article_id = a.id AND t.slug = ?
    )`;
    params.push(tag);
  }

  if (search) {
    query += ` AND (
      a.title LIKE ? OR
      a.body LIKE ? OR
      EXISTS (
        SELECT 1
        FROM article_tags at
        JOIN tags t ON t.id = at.tag_id
        WHERE at.article_id = a.id AND (t.name LIKE ? OR t.slug LIKE ?)
      )
    )`;
    params.push(`%${search}%`, `%${search}%`, `%${search}%`, `%${search}%`);
  }

  query += ` ORDER BY a.published_at DESC LIMIT ? OFFSET ?`;
  params.push(limit, offset);

  try {
    const articles = db.prepare(query).all(...params).map(withTags);
    
    // Get total count for pagination
    let countQuery = `SELECT COUNT(*) as total FROM articles a JOIN categories c ON a.category_id = c.id WHERE a.status = 'published' AND (a.scheduled_at IS NULL OR a.scheduled_at <= CURRENT_TIMESTAMP)`;
    const countParams = [];
    if (category) {
      countQuery += ` AND c.slug = ?`;
      countParams.push(category);
    }
    if (tag) {
      countQuery += ` AND EXISTS (
        SELECT 1
        FROM article_tags at
        JOIN tags t ON t.id = at.tag_id
        WHERE at.article_id = a.id AND t.slug = ?
      )`;
      countParams.push(tag);
    }
    if (search) {
      countQuery += ` AND (
        a.title LIKE ? OR
        a.body LIKE ? OR
        EXISTS (
          SELECT 1
          FROM article_tags at
          JOIN tags t ON t.id = at.tag_id
          WHERE at.article_id = a.id AND (t.name LIKE ? OR t.slug LIKE ?)
        )
      )`;
      countParams.push(`%${search}%`, `%${search}%`, `%${search}%`, `%${search}%`);
    }
    const total = db.prepare(countQuery).get(...countParams).total;

    res.json({
      articles,
      pagination: {
        page,
        limit,
        total,
        totalPages: Math.ceil(total / limit)
      }
    });
  } catch (err) {
    res.status(500).json({ error: 'ޚަބަރުތައް ހޯދުމުގައި މައްސަލައެއް ދިމާވެއްޖެ' });
  }
});

// GET /api/articles/featured - Top 3 featured
router.get('/featured', (req, res) => {
  try {
    const featured = db.prepare(`
      SELECT a.*, c.name as category_name, c.slug as category_slug,
             COALESCE(NULLIF(u.full_name, ''), NULLIF(u.username, ''), NULLIF(a.author, ''), 'ބިލެއްދޫ') as display_author
      FROM articles a
      JOIN categories c ON a.category_id = c.id
      LEFT JOIN users u ON a.user_id = u.id
      WHERE a.status = 'published' AND (a.scheduled_at IS NULL OR a.scheduled_at <= CURRENT_TIMESTAMP) AND a.is_featured = 1
      ORDER BY a.published_at DESC
      LIMIT 3
    `).all().map(withTags);
    res.json(featured);
  } catch (err) {
    res.status(500).json({ error: 'ފީޗާޑް ޚަބަރުތައް ހޯދުމުގައި މައްސަލައެއް ދިމާވެއްޖެ' });
  }
});

// GET /api/articles/breaking - Breaking news (limit 5)
router.get('/breaking', (req, res) => {
  try {
    const breaking = db.prepare(`
      SELECT title, slug FROM articles 
      WHERE status = 'published' AND (scheduled_at IS NULL OR scheduled_at <= CURRENT_TIMESTAMP) AND is_breaking = 1
      ORDER BY published_at DESC LIMIT 5
    `).all();
    res.json(breaking);
  } catch (err) {
    res.status(500).json({ error: 'ބްރޭކިންގް ނިއުސް ހޯދުމުގައި މައްސަލައެއް ދިމާވެއްޖެ' });
  }
});

// GET /api/articles/slider - Slider articles (limit 5)
router.get('/slider', (req, res) => {
  try {
    const slider = db.prepare(`
      SELECT a.*, c.name as category_name,
             COALESCE(NULLIF(u.full_name, ''), NULLIF(u.username, ''), NULLIF(a.author, ''), 'ބިލެއްދޫ') as display_author
      FROM articles a
      JOIN categories c ON a.category_id = c.id
      LEFT JOIN users u ON a.user_id = u.id
      WHERE a.status = 'published' AND (a.scheduled_at IS NULL OR a.scheduled_at <= CURRENT_TIMESTAMP) AND a.is_slider = 1
      ORDER BY a.published_at DESC LIMIT 5
    `).all().map(withTags);
    res.json(slider);
  } catch (err) {
    res.status(500).json({ error: 'ސްލައިޑަރ ޚަބަރުތައް ހޯދުމުގައި މައްސަލައެއް ދިމާވެއްޖެ' });
  }
});

// GET /api/articles/recommended - Recommended articles (limit 5)
router.get('/recommended', (req, res) => {
  try {
    const recommended = db.prepare(`
      SELECT a.*, c.name as category_name,
             COALESCE(NULLIF(u.full_name, ''), NULLIF(u.username, ''), NULLIF(a.author, ''), 'ބިލެއްދޫ') as display_author
      FROM articles a
      JOIN categories c ON a.category_id = c.id
      LEFT JOIN users u ON a.user_id = u.id
      WHERE a.status = 'published' AND (a.scheduled_at IS NULL OR a.scheduled_at <= CURRENT_TIMESTAMP) AND a.is_recommended = 1
      ORDER BY a.published_at DESC LIMIT 5
    `).all().map(withTags);
    res.json(recommended);
  } catch (err) {
    res.status(500).json({ error: 'ރިކޮމެންޑެޑް ޚަބަރުތައް ހޯދުމުގައި މައްސަލައެއް ދިމާވެއްޖެ' });
  }
});

// GET /api/articles/:slug - Single article
router.get('/:slug', (req, res) => {
  const { slug } = req.params;
  try {
    const article = db.prepare(`
      SELECT a.*, c.name as category_name, c.slug as category_slug,
             u.full_name as writer_full_name, u.username as writer_username,
             u.avatar_url as writer_avatar_url, u.twitter_url as writer_twitter_url,
             COALESCE(NULLIF(u.full_name, ''), NULLIF(u.username, ''), NULLIF(a.author, ''), 'ބިލެއްދޫ') as display_author
      FROM articles a
      JOIN categories c ON a.category_id = c.id
      LEFT JOIN users u ON a.user_id = u.id
      WHERE a.slug = ? AND a.status = 'published' AND (a.scheduled_at IS NULL OR a.scheduled_at <= CURRENT_TIMESTAMP)
    `).get(slug);

    if (!article) {
      return res.status(404).json({ error: 'ޚަބަރެއް ނުފެނުނު' });
    }

    // Increment view count
    db.prepare('UPDATE articles SET views = views + 1 WHERE id = ?').run(article.id);

    res.json(withTags(article));
  } catch (err) {
    res.status(500).json({ error: 'ޚަބަރު ހޯދުމުގައި މައްސަލައެއް ދިމާވެއްޖެ' });
  }
});

// GET /api/articles/:id/comments - Fetch comments for an article
router.get('/:id/comments', (req, res) => {
  const { id } = req.params;
  try {
    const comments = db.prepare('SELECT * FROM comments WHERE article_id = ? ORDER BY created_at DESC').all(id);
    res.json(comments);
  } catch (err) {
    res.status(500).json({ error: 'ކޮމެންޓްތައް ހޯދުމުގައި މައްސަލައެއް ދިމާވެއްޖެ' });
  }
});

// POST /api/articles/:id/comments - Submit a new comment
router.post('/:id/comments', (req, res) => {
  const { id } = req.params;
  const { name, content } = req.body;

  if (!name || !content) {
    return res.status(400).json({ error: 'ނަމާއި ކޮމެންޓް ލިޔަންވާނެ' });
  }

  if (content.length > 450) {
    return res.status(400).json({ error: 'ކޮމެންޓްގެ ދިގުމިން 450 އަކުރަށްވުރެ އިތުރުވެގެން ނުވާނެ' });
  }

  try {
    const createdAt = new Date().toISOString();
    const info = db.prepare('INSERT INTO comments (article_id, name, content, created_at) VALUES (?, ?, ?, ?)').run(id, name, content, createdAt);
    res.status(201).json({ id: info.lastInsertRowid, success: true });
  } catch (err) {
    res.status(500).json({ error: 'ކޮމެންޓް ސޭވްކުރުމުގައި މައްސަލައެއް ދިމާވެއްޖެ' });
  }
});

module.exports = router;
