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

// GET /api/categories - All categories
router.get('/', (req, res) => {
  try {
    const isHomeContext = req.query.context === 'home';
    const query = isHomeContext
      ? 'SELECT * FROM categories WHERE is_home_visible = 1 ORDER BY home_sort_order ASC, sort_order ASC, id ASC'
      : 'SELECT * FROM categories ORDER BY sort_order ASC, id ASC';
    const categories = db.prepare(query).all();
    res.json(categories);
  } catch (err) {
    res.status(500).json({ error: 'ކެޓަގަރީތައް ހޯދުމުގައި މައްސަލައެއް ދިމާވެއްޖެ' });
  }
});

// GET /api/categories/:slug/articles - Articles by category, paginated
router.get('/:slug/articles', (req, res) => {
  const { slug } = req.params;
  const page = parseInt(req.query.page) || 1;
  const limit = parseInt(req.query.limit) || 20;
  const offset = (page - 1) * limit;

  try {
    const category = db.prepare('SELECT id, slug FROM categories WHERE slug = ?').get(slug);
    if (!category) {
      return res.status(404).json({ error: 'ކެޓަގަރީއެއް ނުފެނުނު' });
    }

    const articles = db.prepare(`
      SELECT a.*, c.name as category_name, c.slug as category_slug 
      FROM articles a
      JOIN categories c ON a.category_id = c.id
      WHERE c.slug = ?
        AND a.status = 'published'
        AND (a.scheduled_at IS NULL OR a.scheduled_at <= CURRENT_TIMESTAMP)
      ORDER BY a.published_at DESC
      LIMIT ? OFFSET ?
    `).all(category.slug, limit, offset);

    const total = db.prepare(`
      SELECT COUNT(*) as total
      FROM articles a
      JOIN categories c ON a.category_id = c.id
      WHERE c.slug = ?
        AND a.status = 'published'
        AND (a.scheduled_at IS NULL OR a.scheduled_at <= CURRENT_TIMESTAMP)
    `).get(category.slug).total;

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

module.exports = router;
