const express = require('express');
const router = express.Router();
const multer = require('multer');
const path = require('path');
const fs = require('fs');
const db = require('../db');
const auth = require('../middleware/auth');

// Multer setup for image uploads
const storage = multer.diskStorage({
  destination: (req, file, cb) => {
    const uploadDir = path.join(__dirname, '../uploads');
    if (!fs.existsSync(uploadDir)) fs.mkdirSync(uploadDir);
    cb(null, uploadDir);
  },
  filename: (req, file, cb) => {
    cb(null, Date.now() + '-' + file.originalname);
  }
});
const allowedExt = new Set(['.jpg', '.jpeg', '.png', '.webp', '.gif']);
const upload = multer({
  storage,
  fileFilter: (req, file, cb) => {
    const ext = path.extname(file.originalname || '').toLowerCase();
    const mime = (file.mimetype || '').toLowerCase();
    const blocked = ext === '.heic' || ext === '.heif' || mime.includes('heic') || mime.includes('heif');
    if (blocked) {
      return cb(new Error('HEIC/HEIF ފައިލް އިން ބްރައުޒަރުގައި ނުދައްކާނެ. JPG ނުވަތަ PNG އަށް ބަދަލު ކޮށް އަޕްލޯޑް ކުރޭ.'));
    }
    if (!allowedExt.has(ext)) {
      return cb(new Error('އެކަމަކު JPG/PNG/WEBP/GIF ފޯމެޓް ބައްލަވާ.'));
    }
    cb(null, true);
  }
});

// All admin routes are protected
router.use(auth);

function normalizeCategoryOrders() {
  const categories = db.prepare('SELECT id, sort_order FROM categories ORDER BY sort_order ASC, id ASC').all();
  const updateSort = db.prepare('UPDATE categories SET sort_order = ? WHERE id = ?');
  const tx = db.transaction((rows) => {
    rows.forEach((row, index) => updateSort.run(index + 1, row.id));
  });
  tx(categories);

  const homeRows = db.prepare('SELECT id, home_sort_order FROM categories WHERE is_home_visible = 1 ORDER BY home_sort_order ASC, sort_order ASC, id ASC').all();
  const updateHomeSort = db.prepare('UPDATE categories SET home_sort_order = ? WHERE id = ?');
  const txHome = db.transaction((rows) => {
    rows.forEach((row, index) => updateHomeSort.run(index + 1, row.id));
  });
  txHome(homeRows);
}

function getCategoryIdSet() {
  return new Set(db.prepare('SELECT id FROM categories').all().map(r => r.id));
}

function isAdminOrEditor(user) {
  return user && (user.role === 'admin' || user.role === 'editor');
}

function slugify(input) {
  return String(input || '')
    .toLowerCase()
    .replace(/&/g, ' and ')
    .replace(/[^a-z0-9]+/g, '-')
    .replace(/^-+|-+$/g, '')
    .slice(0, 120);
}

function xmlEscape(input) {
  return String(input || '')
    .replace(/&/g, '&amp;')
    .replace(/</g, '&lt;')
    .replace(/>/g, '&gt;')
    .replace(/"/g, '&quot;')
    .replace(/'/g, '&apos;');
}

function normalizeUploadFilename(rawName) {
  const decoded = decodeURIComponent(String(rawName || ''));
  const base = path.basename(decoded);
  if (!base || base === '.' || base === '..') return null;
  return base;
}

function normalizeTwitterUrl(rawValue) {
  const raw = String(rawValue || '').trim();
  if (!raw) return null;
  if (/^https?:\/\/(www\.)?(x\.com|twitter\.com)\//i.test(raw)) return raw;
  const handle = raw.replace(/^@+/, '').trim();
  if (!handle) return null;
  return `https://x.com/${handle}`;
}

function slugifyTag(input) {
  return String(input || '')
    .toLowerCase()
    .trim()
    .replace(/[^a-z0-9\u0780-\u07BF]+/g, '-')
    .replace(/^-+|-+$/g, '')
    .slice(0, 120);
}

function parseTagIds(raw) {
  if (raw == null || raw === '') return [];
  if (Array.isArray(raw)) return raw.map(Number).filter(Number.isFinite);
  if (typeof raw === 'string') {
    try {
      const parsed = JSON.parse(raw);
      if (Array.isArray(parsed)) return parsed.map(Number).filter(Number.isFinite);
    } catch (_) {}
    return raw.split(',').map((v) => Number(v.trim())).filter(Number.isFinite);
  }
  return [];
}

function toFlag(value, defaultValue = 0) {
  if (value === undefined || value === null || value === '') return defaultValue;
  if (typeof value === 'boolean') return value ? 1 : 0;
  const normalized = String(value).trim().toLowerCase();
  if (normalized === '1' || normalized === 'true' || normalized === 'yes' || normalized === 'on') return 1;
  if (normalized === '0' || normalized === 'false' || normalized === 'no' || normalized === 'off') return 0;
  return defaultValue;
}

function syncArticleTags(articleId, tagIds) {
  const uniq = [...new Set((tagIds || []).map(Number).filter(Number.isFinite))];
  const validTagIds = uniq.filter((id) => db.prepare('SELECT 1 FROM tags WHERE id = ?').get(id));
  const del = db.prepare('DELETE FROM article_tags WHERE article_id = ?');
  const ins = db.prepare('INSERT OR IGNORE INTO article_tags (article_id, tag_id) VALUES (?, ?)');
  const tx = db.transaction(() => {
    del.run(articleId);
    validTagIds.forEach((tagId) => ins.run(articleId, tagId));
  });
  tx();
}

const AD_POSITION_SPECS = {
  top: { width: 1180, height: 170 },
  sidebar: { width: 300, height: 580 },
  'mid-grid': { width: 1180, height: 170 },
  'article-below-tags': { width: 776, height: 170 }
};

// --- DASHBOARD STATS ---
router.get('/stats', (req, res) => {
  const { id, role } = req.user;
  try {
    let articleQuery = 'SELECT COUNT(*) as count FROM articles';
    let viewQuery = 'SELECT SUM(views) as count FROM articles';
    let todayQuery = "SELECT COUNT(*) as count FROM articles WHERE date(created_at) = date('now')";
    let mostViewedQuery = 'SELECT title, views FROM articles ORDER BY views DESC LIMIT 5';
    let params = [];

    if (role === 'writer') {
      articleQuery += ' WHERE user_id = ?';
      viewQuery += ' WHERE user_id = ?';
      todayQuery += ' AND user_id = ?';
      mostViewedQuery = 'SELECT title, views FROM articles WHERE user_id = ? ORDER BY views DESC LIMIT 5';
      params = [id];
    }

    const totalArticles = db.prepare(articleQuery).get(...params).count;
    const totalViews = db.prepare(viewQuery).get(...params).count || 0;
    const articlesToday = db.prepare(todayQuery).get(...params).count;
    const mostViewed = db.prepare(mostViewedQuery).all(...params);

    res.json({ totalArticles, totalViews, articlesToday, mostViewed });
  } catch (err) {
    console.error('ތަފާސްހިސާބު މައްސަލަ:', err);
    res.status(500).json({ error: 'ތަފާސްހިސާބު ހޯދުމުގައި މައްސަލައެއް ދިމާވެއްޖެ' });
  }
});

// --- ARTICLES ---
router.get('/articles', (req, res) => {
  const { id, role } = req.user;
  const categoryId = Number(req.query.category_id);
  try {
    let query = `
      SELECT a.*, c.name as category_name 
      FROM articles a
      LEFT JOIN categories c ON a.category_id = c.id
    `;
    const where = [];
    const params = [];

    if (role === 'writer') {
      where.push('a.user_id = ?');
      params.push(id);
    }

    if (Number.isFinite(categoryId) && categoryId > 0) {
      where.push('a.category_id = ?');
      params.push(categoryId);
    }

    if (where.length) {
      query += ` WHERE ${where.join(' AND ')}`;
    }

    query += ` ORDER BY a.created_at DESC`;
    
    const articles = db.prepare(query).all(...params).map((a) => {
      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(a.id);
      return { ...a, tags, tag_ids: tags.map((t) => t.id) };
    });
    res.json(articles);
  } catch (err) {
    res.status(500).json({ error: 'ޚަބަރުތައް ހޯދުމުގައި މައްސަލައެއް ދިމާވެއްޖެ' });
  }
});

router.post('/articles', upload.single('image'), (req, res) => {
  const { title, slug, body, excerpt, category_id, is_published, is_featured, is_breaking, is_recommended, is_slider, status, scheduled_at, tag_ids, seo_title, seo_description, allow_comments } = req.body;
  const existingImage = normalizeUploadFilename(req.body.image_existing || '');
  const existingImagePath = existingImage ? path.join(__dirname, '../uploads', existingImage) : null;
  const image = req.file
    ? req.file.filename
    : (existingImage && existingImagePath && fs.existsSync(existingImagePath) ? existingImage : null);
  const userId = req.user.id;
  const role = req.user.role;
  const author = req.user.full_name || req.user.username || 'ބިލެއްދޫ';
  const canPublish = isAdminOrEditor(req.user);
  const safeIsPublished = canPublish ? toFlag(is_published, 0) : 0;
  const safeIsFeatured = canPublish ? toFlag(is_featured, 0) : 0;
  const safeIsBreaking = canPublish ? toFlag(is_breaking, 0) : 0;
  const safeIsRecommended = canPublish ? toFlag(is_recommended, 0) : 0;
  const safeIsSlider = canPublish ? toFlag(is_slider, 0) : 0;
  const safeStatus = canPublish ? String(status || (safeIsPublished ? 'published' : 'draft')) : 'pending';
  const safeScheduledAt = canPublish ? (scheduled_at || null) : null;
  const safeAllowComments = toFlag(allow_comments, 1);

  try {
    const info = db.prepare(`
      INSERT INTO articles (title, slug, body, excerpt, category_id, user_id, is_published, is_featured, is_breaking, is_recommended, is_slider, status, scheduled_at, author, image, seo_title, seo_description, allow_comments)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `).run(
      title,
      slug ? slugifyTag(slug) : 'temp-slug',
      body,
      excerpt,
      category_id,
      userId,
      safeIsPublished,
      safeIsFeatured,
      safeIsBreaking,
      safeIsRecommended,
      safeIsSlider,
      safeStatus,
      safeScheduledAt,
      author,
      image,
      seo_title || null,
      seo_description || null,
      safeAllowComments
    );

    if (!slug) {
      const autoSlug = `article-${info.lastInsertRowid}`;
      db.prepare('UPDATE articles SET slug = ? WHERE id = ?').run(autoSlug, info.lastInsertRowid);
    }
    syncArticleTags(info.lastInsertRowid, parseTagIds(tag_ids));

    res.status(201).json({ id: info.lastInsertRowid, slug });
  } catch (err) {
    res.status(500).json({ error: 'ޚަބަރު ސޭވްކުރުމުގައި މައްސަލައެއް ދިމާވެއްޖެ' });
  }
});

router.put('/articles/:id', upload.single('image'), (req, res) => {
  const { id } = req.params;
  const { title, slug, body, excerpt, category_id, is_published, is_featured, is_breaking, is_recommended, is_slider, status, scheduled_at, tag_ids, seo_title, seo_description, allow_comments } = req.body;
  const { id: userId, role } = req.user;
  const author = req.user.full_name || req.user.username || 'ބިލެއްދޫ';

  try {
    const article = db.prepare('SELECT user_id FROM articles WHERE id = ?').get(id);
    if (!article) return res.status(404).json({ error: 'ޚަބަރު ނުފެނުނު' });

    // Check ownership or role
    if (role === 'writer' && article.user_id !== userId) {
      return res.status(403).json({ error: 'މި ޚަބަރު އިޞްލާޙްކުރުމުގެ އިޒުނައެއް ނެތް' });
    }

    const canPublish = isAdminOrEditor(req.user);
    const safeIsPublished = canPublish ? toFlag(is_published, 0) : 0;
    const safeIsFeatured = canPublish ? toFlag(is_featured, 0) : 0;
    const safeIsBreaking = canPublish ? toFlag(is_breaking, 0) : 0;
    const safeIsRecommended = canPublish ? toFlag(is_recommended, 0) : 0;
    const safeIsSlider = canPublish ? toFlag(is_slider, 0) : 0;
    const safeStatus = canPublish ? String(status || (safeIsPublished ? 'published' : 'draft')) : 'pending';
    const safeScheduledAt = canPublish ? (scheduled_at || null) : null;
    const safeAllowComments = toFlag(allow_comments, 1);

    let query = `
      UPDATE articles SET 
      title = ?, slug = ?, body = ?, excerpt = ?, category_id = ?, 
      is_published = ?, is_featured = ?, is_breaking = ?, 
      is_recommended = ?, is_slider = ?, status = ?, 
      scheduled_at = ?, author = ?, seo_title = ?, 
      seo_description = ?, allow_comments = ?
    `;
    const params = [title, slugifyTag(slug), body, excerpt, category_id, safeIsPublished, safeIsFeatured, safeIsBreaking, safeIsRecommended, safeIsSlider, safeStatus, safeScheduledAt, author, seo_title || null, seo_description || null, safeAllowComments];

    const existingImage = normalizeUploadFilename(req.body.image_existing || '');
    const existingImagePath = existingImage ? path.join(__dirname, '../uploads', existingImage) : null;

    if (req.file) {
      query += `, image = ?`;
      params.push(req.file.filename);
    } else if (existingImage && existingImagePath && fs.existsSync(existingImagePath)) {
      query += `, image = ?`;
      params.push(existingImage);
    }

    query += ` WHERE id = ?`;
    params.push(id);

    db.prepare(query).run(...params);
    syncArticleTags(id, parseTagIds(tag_ids));
    res.json({ success: true });
  } catch (err) {
    res.status(500).json({ error: 'ޚަބަރު އަޕްޑޭޓްކުރުމުގައި މައްސަލައެއް ދިމާވެއްޖެ' });
  }
});

router.delete('/articles/:id', (req, res) => {
  const { id } = req.params;
  const { id: userId, role } = req.user;

  try {
    const article = db.prepare('SELECT user_id FROM articles WHERE id = ?').get(id);
    if (!article) return res.status(404).json({ error: 'ޚަބަރު ނުފެނުނު' });

    if (role === 'writer' && article.user_id !== userId) {
      return res.status(403).json({ error: 'މި ޚަބަރު ފުހެލުމުގެ އިޒުނައެއް ނެތް' });
    }

    db.prepare('DELETE FROM articles WHERE id = ?').run(id);
    res.json({ success: true });
  } catch (err) {
    res.status(500).json({ error: 'ޚަބަރު ފުހެލުމުގައި މައްސަލައެއް ދިމާވެއްޖެ' });
  }
});

router.patch('/articles/:id/toggle-publish', (req, res) => {
  if (!isAdminOrEditor(req.user)) return res.status(403).json({ error: 'އިޒުން ނެތް' });
  try {
    db.prepare(`
      UPDATE articles
      SET is_published = 1 - is_published,
          status = CASE WHEN is_published = 0 THEN 'published' ELSE 'pending' END,
          published_at = CASE WHEN is_published = 0 THEN CURRENT_TIMESTAMP ELSE published_at END
      WHERE id = ?
    `).run(req.params.id);
    res.json({ success: true });
  } catch (err) {
    res.status(500).json({ error: 'ޚަބަރުގެ ސްޓޭޓަސް ބަދަލުކުރުމުގައި މައްސަލައެއް ދިމާވެއްޖެ' });
  }
});

router.patch('/articles/:id/toggle-feature', (req, res) => {
  if (!isAdminOrEditor(req.user)) return res.status(403).json({ error: 'އިޒުން ނެތް' });
  try {
    db.prepare('UPDATE articles SET is_featured = 1 - is_featured WHERE id = ?').run(req.params.id);
    res.json({ success: true });
  } catch (err) {
    res.status(500).json({ error: 'ޚަބަރުގެ ސްޓޭޓަސް ބަދަލުކުރުމުގައި މައްސަލައެއް ދިމާވެއްޖެ' });
  }
});

// --- TAGS ---
router.get('/tags', (req, res) => {
  try {
    const tags = db.prepare(`
      SELECT t.*,
             (SELECT COUNT(*) FROM article_tags at WHERE at.tag_id = t.id) AS usage_count
      FROM tags t
      ORDER BY t.name ASC
    `).all();
    res.json(tags);
  } catch (err) {
    res.status(500).json({ error: 'ޓެގްތައް ހޯދުމުގައި މައްސަލަ' });
  }
});

router.post('/tags', (req, res) => {
  if (!isAdminOrEditor(req.user)) return res.status(403).json({ error: 'އިޒުން ނެތް' });
  const { name, slug } = req.body || {};
  const cleanName = String(name || '').trim();
  if (!cleanName) return res.status(400).json({ error: 'ނަން ބޭނުން' });
  const cleanSlug = slugifyTag(slug || cleanName);
  if (!cleanSlug) return res.status(400).json({ error: 'slug ސައްޙަ ނޫން' });
  try {
    const info = db.prepare('INSERT INTO tags (name, slug) VALUES (?, ?)').run(cleanName, cleanSlug);
    res.status(201).json({ id: info.lastInsertRowid, name: cleanName, slug: cleanSlug });
  } catch (err) {
    res.status(400).json({ error: 'ޓެގް އޮތީ މިހާރު' });
  }
});

router.delete('/tags/:id', (req, res) => {
  if (!isAdminOrEditor(req.user)) return res.status(403).json({ error: 'އިޒުން ނެތް' });
  try {
    const id = Number(req.params.id);
    const tx = db.transaction(() => {
      db.prepare('DELETE FROM article_tags WHERE tag_id = ?').run(id);
      db.prepare('DELETE FROM tags WHERE id = ?').run(id);
    });
    tx();
    res.json({ success: true });
  } catch (err) {
    res.status(500).json({ error: 'ޓެގް ފުހެލުމުގައި މައްސަލަ' });
  }
});

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

router.post('/categories', (req, res) => {
  if (!isAdminOrEditor(req.user)) return res.status(403).json({ error: 'އިޒުނައެއް ނެތް' });
  const { name, name_en, slug, sort_order, home_grid_style } = req.body;
  try {
    const maxSort = db.prepare('SELECT COALESCE(MAX(sort_order), 0) as max FROM categories').get().max;
    const maxHomeSort = db.prepare('SELECT COALESCE(MAX(home_sort_order), 0) as max FROM categories WHERE is_home_visible = 1').get().max;
    const requestedSort = Number(sort_order);
    const nextSort = Number.isFinite(requestedSort) && requestedSort > 0 ? requestedSort : maxSort + 1;
    const requestedGridStyle = Number(home_grid_style);
    const nextGridStyle = [0, 1, 2, 3, 4].includes(requestedGridStyle) ? requestedGridStyle : 0;
    // Newly created categories should appear on homepage by default.
    const visible = 1;
    const nextHomeSort = visible ? (maxHomeSort + 1) : 0;
    const info = db.prepare('INSERT INTO categories (name, name_en, slug, sort_order, is_home_visible, home_sort_order, home_grid_style) VALUES (?, ?, ?, ?, ?, ?, ?)').run(name, name_en, slug, nextSort, visible, nextHomeSort, nextGridStyle);
    // Keep frontend nav in sync with categories by creating a matching header menu link.
    const menuExists = db.prepare("SELECT id FROM menus WHERE link = ? LIMIT 1").get(`#category/${slug}`);
    if (!menuExists) {
      db.prepare('INSERT INTO menus (title, link, position, sort_order) VALUES (?, ?, ?, ?)').run(name, `#category/${slug}`, 'header', nextSort);
    }
    normalizeCategoryOrders();
    res.status(201).json({ id: info.lastInsertRowid });
  } catch (err) {
    res.status(500).json({ error: 'ކެޓަގަރީ ސޭވްކުރުމުގައި މައްސަލައެއް ދިމާވެއްޖެ' });
  }
});

router.post('/categories/reorder', (req, res) => {
  if (!isAdminOrEditor(req.user)) return res.status(403).json({ error: 'އިޒުނައެއް ނެތް' });
  const { orderedIds } = req.body || {};

  if (!Array.isArray(orderedIds) || orderedIds.length === 0) {
    return res.status(400).json({ error: 'ކެޓަގަރީ އޯޑަރ ސައްހަ ނޫން' });
  }

  try {
    const uniqueIds = [...new Set(orderedIds.map(Number).filter(Number.isFinite))];
    const validIds = getCategoryIdSet();
    if (uniqueIds.length !== validIds.size || uniqueIds.some(id => !validIds.has(id))) {
      return res.status(400).json({ error: 'ކެޓަގަރީ އޯޑަރ އިންޕުޓް ސައްހަ ނޫން' });
    }

    const updateOrder = db.prepare('UPDATE categories SET sort_order = ? WHERE id = ?');
    const tx = db.transaction((ids) => {
      ids.forEach((categoryId, index) => {
        updateOrder.run(index + 1, categoryId);
      });
    });
    tx(uniqueIds);
    normalizeCategoryOrders();
    res.json({ success: true });
  } catch (err) {
    res.status(500).json({ error: 'ކެޓަގަރީ އޯޑަރ އަޕްޑޭޓް ކުރުމުގައި މައްސަލައެއް ދިމާވެއްޖެ' });
  }
});

router.post('/categories/home-arrange', (req, res) => {
  if (!isAdminOrEditor(req.user)) return res.status(403).json({ error: 'އިޒުނައެއް ނެތް' });
  const { orderedIds } = req.body || {};

  if (!Array.isArray(orderedIds)) {
    return res.status(400).json({ error: 'ހޯމް ކެޓަގަރީ އޯޑަރ ސައްހަ ނޫން' });
  }

  try {
    const uniqueIds = [...new Set(orderedIds.map(Number).filter(Number.isFinite))];
    const validIds = getCategoryIdSet();
    if (uniqueIds.some(id => !validIds.has(id))) {
      return res.status(400).json({ error: 'ހޯމް ކެޓަގަރީ އޯޑަރ އިންޕުޓް ސައްހަ ނޫން' });
    }

    const hideAll = db.prepare('UPDATE categories SET is_home_visible = 0');
    const showCategory = db.prepare('UPDATE categories SET is_home_visible = 1, home_sort_order = ? WHERE id = ?');
    const tx = db.transaction((ids) => {
      hideAll.run();
      ids.forEach((categoryId, index) => {
        showCategory.run(index + 1, categoryId);
      });
    });
    tx(uniqueIds);
    normalizeCategoryOrders();
    const updated = db.prepare('SELECT * FROM categories ORDER BY home_sort_order ASC, sort_order ASC, id ASC').all();
    res.json({ success: true, categories: updated });
  } catch (err) {
    res.status(500).json({ error: 'ހޯމް ކެޓަގަރީ އޯޑަރ އަޕްޑޭޓް ކުރުމުގައި މައްސަލަ' });
  }
});

router.put('/categories/:id', (req, res) => {
  if (!isAdminOrEditor(req.user)) return res.status(403).json({ error: 'އިޒުނައެއް ނެތް' });
  const { id } = req.params;
  const { name, name_en, slug, sort_order, is_home_visible, home_grid_style } = req.body;
  try {
    const category = db.prepare('SELECT is_home_visible, home_grid_style FROM categories WHERE id = ?').get(id);
    if (!category) return res.status(404).json({ error: 'ކެޓަގަރީ ނުފެނުނު' });
    const visible = is_home_visible === undefined ? Number(category.is_home_visible) : (Number(is_home_visible) === 1 ? 1 : 0);
    const requestedGridStyle = Number(home_grid_style);
    const nextGridStyle = home_grid_style === undefined
      ? (Number(category.home_grid_style) || 0)
      : ([0, 1, 2, 3, 4].includes(requestedGridStyle) ? requestedGridStyle : 0);
    let homeSort = category.is_home_visible ? db.prepare('SELECT home_sort_order FROM categories WHERE id = ?').get(id).home_sort_order : 0;
    if (visible === 1 && Number(category.is_home_visible) !== 1) {
      const maxHomeSort = db.prepare('SELECT COALESCE(MAX(home_sort_order), 0) as max FROM categories WHERE is_home_visible = 1').get().max;
      homeSort = maxHomeSort + 1;
    }
    if (visible === 0) homeSort = 0;
    db.prepare('UPDATE categories SET name = ?, name_en = ?, slug = ?, sort_order = ?, is_home_visible = ?, home_sort_order = ?, home_grid_style = ? WHERE id = ?').run(name, name_en, slug, sort_order, visible, homeSort, nextGridStyle, id);
    normalizeCategoryOrders();
    res.json({ success: true });
  } catch (err) {
    res.status(500).json({ error: 'ކެޓަގަރީ އަޕްޑޭޓްކުރުމުގައި މައްސަލައެއް ދިމާވެއްޖެ' });
  }
});

router.patch('/categories/:id/grid-style', (req, res) => {
  if (!isAdminOrEditor(req.user)) return res.status(403).json({ error: 'އިޒުނައެއް ނެތް' });
  const { id } = req.params;
  const { home_grid_style } = req.body || {};
  if (home_grid_style === undefined) {
    return res.status(400).json({ error: 'ގްރިޑް ސްޓައިލް ބޭނުންވޭ' });
  }
  try {
    const category = db.prepare('SELECT id FROM categories WHERE id = ?').get(id);
    if (!category) return res.status(404).json({ error: 'ކެޓަގަރީ ނުފެނުނު' });
    const requested = Number(home_grid_style);
    const nextStyle = [0, 1, 2, 3, 4].includes(requested) ? requested : 0;
    db.prepare('UPDATE categories SET home_grid_style = ? WHERE id = ?').run(nextStyle, id);
    const categories = db.prepare('SELECT * FROM categories ORDER BY sort_order ASC, id ASC').all();
    res.json({ success: true, categories });
  } catch (err) {
    res.status(500).json({ error: 'ގްރިޑް ސްޓައިލް އަޕްޑޭޓް މައްސަލަ' });
  }
});

router.patch('/categories/:id/home-visibility', (req, res) => {
  if (!isAdminOrEditor(req.user)) return res.status(403).json({ error: 'އިޒުނައެއް ނެތް' });
  const { id } = req.params;
  const { is_home_visible } = req.body || {};
  if (is_home_visible === undefined) {
    return res.status(400).json({ error: 'ހޯމް ވިޒިބިލިޓީ ބޭނުންވޭ' });
  }
  try {
    const category = db.prepare('SELECT id, is_home_visible FROM categories WHERE id = ?').get(id);
    if (!category) return res.status(404).json({ error: 'ކެޓަގަރީ ނުފެނުނު' });
    const visible = Number(is_home_visible) === 1 ? 1 : 0;
    if (visible === 1) {
      const maxHomeSort = db.prepare('SELECT COALESCE(MAX(home_sort_order), 0) as max FROM categories WHERE is_home_visible = 1').get().max;
      db.prepare('UPDATE categories SET is_home_visible = 1, home_sort_order = ? WHERE id = ?').run(maxHomeSort + 1, id);
    } else {
      db.prepare('UPDATE categories SET is_home_visible = 0, home_sort_order = 0 WHERE id = ?').run(id);
    }
    normalizeCategoryOrders();
    const categories = db.prepare('SELECT * FROM categories ORDER BY sort_order ASC, id ASC').all();
    res.json({ success: true, categories });
  } catch (err) {
    res.status(500).json({ error: 'ހޯމް ވިޒިބިލިޓީ އަޕްޑޭޓް މައްސަލަ' });
  }
});

router.delete('/categories/:id', (req, res) => {
  if (!isAdminOrEditor(req.user)) return res.status(403).json({ error: 'އިޒުނައެއް ނެތް' });
  try {
    const row = db.prepare('SELECT slug FROM categories WHERE id = ?').get(req.params.id);
    db.prepare('DELETE FROM categories WHERE id = ?').run(req.params.id);
    if (row && row.slug) {
      db.prepare('DELETE FROM menus WHERE link = ?').run(`#category/${row.slug}`);
    }
    normalizeCategoryOrders();
    res.json({ success: true });
  } catch (err) {
    res.status(500).json({ error: 'ކެޓަގަރީ ފުހެލުމުގައި މައްސަލައެއް ދިމާވެއްޖެ' });
  }
});

// --- ADS ---
router.get('/ads', (req, res) => {
  try {
    const ads = db.prepare('SELECT * FROM advertisements').all().map((ad) => {
      const spec = AD_POSITION_SPECS[ad.position];
      return {
        ...ad,
        width: ad.width || (spec ? spec.width : null),
        height: ad.height || (spec ? spec.height : null)
      };
    });
    res.json(ads);
  } catch (err) {
    res.status(500).json({ error: 'އިޝްތިހާރުތައް ހޯދުމުގައި މައްސަލައެއް ދިމާވެއްޖެ' });
  }
});

router.post('/ads', upload.single('image'), (req, res) => {
  if (req.user.role !== 'admin') return res.status(403).json({ error: 'އިޒުނައެއް ނެތް' });
  const { name, link, position, is_active } = req.body;
  const spec = AD_POSITION_SPECS[position];
  if (!spec) {
    return res.status(400).json({ error: 'Invalid ad position' });
  }
  const image = req.file ? req.file.filename : null;
  try {
    const tx = db.transaction(() => {
      // Placement activation rules:
      // - top/sidebar: exclusive (single active ad)
      // - mid-grid: allow up to 3 active ads
      // - article-below-tags: allow multiple active ads
      if ((position === 'top' || position === 'sidebar') && Number(is_active) === 1) {
        db.prepare('UPDATE advertisements SET is_active = 0 WHERE position = ?').run(position);
      } else if (position === 'mid-grid' && Number(is_active) === 1) {
        const activeRows = db.prepare(`
          SELECT id
          FROM advertisements
          WHERE position = 'mid-grid' AND is_active = 1
          ORDER BY id DESC
        `).all();
        if (activeRows.length >= 3) {
          const toDisable = activeRows.slice(2).map((r) => r.id);
          const deactivate = db.prepare('UPDATE advertisements SET is_active = 0 WHERE id = ?');
          toDisable.forEach((id) => deactivate.run(id));
        }
      }
      return db.prepare('INSERT INTO advertisements (name, link, position, width, height, is_active, image) VALUES (?, ?, ?, ?, ?, ?, ?)').run(
        name,
        link,
        position,
        spec.width,
        spec.height,
        is_active,
        image
      );
    });
    const info = tx();
    res.status(201).json({ id: info.lastInsertRowid });
  } catch (err) {
    res.status(500).json({ error: 'އިޝްތިހާރު ސޭވްކުރުމުގައި މައްސަލައެއް ދިމާވެއްޖެ' });
  }
});

// Upload error handler (invalid file type, etc.)
router.use((err, req, res, next) => {
  if (err && err.message) {
    return res.status(400).json({ error: err.message });
  }
  next(err);
});

router.delete('/ads/:id', (req, res) => {
  if (req.user.role !== 'admin') return res.status(403).json({ error: 'އިޒުނައެއް ނެތް' });
  try {
    db.prepare('DELETE FROM advertisements WHERE id = ?').run(req.params.id);
    res.json({ success: true });
  } catch (err) {
    res.status(500).json({ error: 'އިޝްތިހާރު ފުހެލުމުގައި މައްސަލައެއް ދިމާވެއްޖެ' });
  }
});

// --- SETTINGS ---
router.get('/settings', (req, res) => {
  try {
    const settings = db.prepare('SELECT * FROM settings').all();
    const settingsObj = {};
    settings.forEach(s => settingsObj[s.key] = s.value);
    res.json(settingsObj);
  } catch (err) {
    res.status(500).json({ error: 'ސެޓިންގްސް ހޯދުމުގައި މައްސަލައެއް ދިމާވެއްޖެ' });
  }
});

router.post('/settings', (req, res) => {
  if (req.user.role !== 'admin') return res.status(403).json({ error: 'އިޒުނައެއް ނެތް' });
  try {
    const payload = req.body;
    const upsert = db.prepare('INSERT OR REPLACE INTO settings (key, value) VALUES (?, ?)');
    if (Array.isArray(payload)) {
      const tx = db.transaction((items) => {
        items.forEach((item) => {
          if (!item || typeof item.key !== 'string') return;
          upsert.run(item.key, item.value == null ? '' : String(item.value));
        });
      });
      tx(payload);
    } else if (payload && typeof payload.key === 'string') {
      upsert.run(payload.key, payload.value == null ? '' : String(payload.value));
    } else {
      return res.status(400).json({ error: 'ސެޓިންގްސް ޑޭޓާ ސައްހަ ނޫން' });
    }
    res.json({ success: true });
  } catch (err) {
    res.status(500).json({ error: 'ސެޓިންގްސް ސޭވްކުރުމުގައި މައްސަލައެއް ދިމާވެއްޖެ' });
  }
});

router.post('/settings/logo', upload.single('image'), (req, res) => {
  if (req.user.role !== 'admin') return res.status(403).json({ error: 'އިޒުނައެއް ނެތް' });
  try {
    const clear = String(req.body?.clear || '').trim() === '1';
    const upsert = db.prepare('INSERT OR REPLACE INTO settings (key, value) VALUES (?, ?)');
    if (clear) {
      upsert.run('site_logo', '');
      return res.json({ success: true, cleared: true });
    }
    if (!req.file) return res.status(400).json({ error: 'ލޯގޯ ފައިލް ނުފެނުނު' });
    upsert.run('site_logo', req.file.filename);
    res.json({ success: true, filename: req.file.filename });
  } catch (err) {
    res.status(500).json({ error: 'ލޯގޯ ސޭވްކުރުމުގައި މައްސަލަ' });
  }
});

// --- TEAM MANAGEMENT (Admin Only) ---
router.get('/users', (req, res) => {
  if (req.user.role !== 'admin') return res.status(403).json({ error: 'އިޒުނައެއް ނެތް' });
  try {
    const users = db.prepare('SELECT id, username, full_name, avatar_url, twitter_url, role, created_at FROM users WHERE username != ?').all('admin');
    res.json(users);
  } catch (err) {
    res.status(500).json({ error: 'ޔޫޒަރުން ހޯދުމުގައި މައްސަލައެއް ދިމާވެއްޖެ' });
  }
});

router.post('/users', upload.single('avatar'), async (req, res) => {
  if (req.user.role !== 'admin') return res.status(403).json({ error: 'އިޒުނައެއް ނެތް' });
  try {
    const body = req.body || {};
    const { username, password, role, full_name, avatar_url, twitter_url } = body;
    if (!username || !password) {
      return res.status(400).json({ error: 'ޔޫޒަރނޭމާއި ޕާސްވޯޑް ބޭނުންވޭ' });
    }
    const bcrypt = require('bcryptjs');
    const hashed = await bcrypt.hash(password, 10);
    const uploadedAvatar = req.file ? req.file.filename : null;
    const finalAvatar = uploadedAvatar || avatar_url || null;
    const finalTwitter = normalizeTwitterUrl(twitter_url);
    const info = db.prepare('INSERT INTO users (username, password, full_name, avatar_url, twitter_url, role) VALUES (?, ?, ?, ?, ?, ?)')
      .run(username, hashed, full_name || username, finalAvatar, finalTwitter, role);
    res.status(201).json({ id: info.lastInsertRowid });
  } catch (err) {
    res.status(500).json({ error: 'ޔޫޒަރު ހެދުމުގައި މައްސަލައެއް ދިމާވެއްޖެ' });
  }
});

router.patch('/users/:id/role', (req, res) => {
  if (req.user.role !== 'admin') return res.status(403).json({ error: 'އިޒުނައެއް ނެތް' });
  const { id } = req.params;
  const { role } = req.body;
  try {
    db.prepare('UPDATE users SET role = ? WHERE id = ?').run(role, id);
    res.json({ success: true });
  } catch (err) {
    res.status(500).json({ error: 'ރޯލް ބަދަލުކުރުމުގައި މައްސަލައެއް ދިމާވެއްޖެ' });
  }
});

router.delete('/users/:id', (req, res) => {
  if (req.user.role !== 'admin') return res.status(403).json({ error: 'އިޒުނައެއް ނެތް' });
  try {
    db.prepare('DELETE FROM users WHERE id = ?').run(req.params.id);
    res.json({ success: true });
  } catch (err) {
    res.status(500).json({ error: 'ޔޫޒަރު ފުހެލުމުގައި މައްސަލައެއް ދިމާވެއްޖެ' });
  }
});

// Change Password
router.post('/change-password', async (req, res) => {
  const { currentPassword, newPassword } = req.body;
  const { id } = req.user;
  try {
    const bcrypt = require('bcryptjs');
    const user = db.prepare('SELECT * FROM users WHERE id = ?').get(id);
    const valid = await bcrypt.compare(currentPassword, user.password);
    if (!valid) return res.status(400).json({ error: 'ކުރީގެ ޕާސްވޯޑް ނުބައި' });

    const hashed = await bcrypt.hash(newPassword, 10);
    db.prepare('UPDATE users SET password = ? WHERE id = ?').run(hashed, id);
    res.json({ success: true });
  } catch (err) {
    res.status(500).json({ error: 'ޕާސްވޯޑް ބަދަލުކުރުމުގައި މައްސަލައެއް ދިމާވެއްޖެ' });
  }
});

// --- COMMENTS MODERATION ---
router.get('/comments', (req, res) => {
  try {
    const comments = db.prepare(`
      SELECT c.*, a.title as article_title 
      FROM comments c
      JOIN articles a ON c.article_id = a.id
      ORDER BY c.created_at DESC
    `).all();
    res.json(comments);
  } catch (err) {
    res.status(500).json({ error: 'ކޮމެންޓްތައް ހޯދުމުގައި މައްސަލައެއް ދިމާވެއްޖެ' });
  }
});

router.delete('/comments/:id', (req, res) => {
  try {
    db.prepare('DELETE FROM comments WHERE id = ?').run(req.params.id);
    res.json({ success: true });
  } catch (err) {
    res.status(500).json({ error: 'ކޮމެންޓް ފުހެލުމުގައި މައްސަލައެއް ދިމާވެއްޖެ' });
  }
});

// --- PAGES MANAGEMENT ---
router.get('/pages', (req, res) => {
  try {
    const pages = db.prepare('SELECT * FROM pages ORDER BY created_at DESC').all();
    res.json(pages);
  } catch (err) {
    res.status(500).json({ error: 'ޕޭޖްތައް ހޯދުމުގައި މައްސަލައެއް ދިމާވެއްޖެ' });
  }
});

router.post('/pages', (req, res) => {
  const { title, slug, content, is_published } = req.body;
  try {
    const info = db.prepare('INSERT INTO pages (title, slug, content, is_published) VALUES (?, ?, ?, ?)').run(title, slug, content, is_published);
    res.status(201).json({ id: info.lastInsertRowid });
  } catch (err) {
    res.status(500).json({ error: 'ޕޭޖް ސޭވްކުރުމުގައި މައްސަލައެއް ދިމާވެއްޖެ' });
  }
});

router.put('/pages/:id', (req, res) => {
  const { id } = req.params;
  const { title, slug, content, is_published } = req.body;
  try {
    db.prepare('UPDATE pages SET title = ?, slug = ?, content = ?, is_published = ? WHERE id = ?').run(title, slug, content, is_published, id);
    res.json({ success: true });
  } catch (err) {
    res.status(500).json({ error: 'ޕޭޖް އަޕްޑޭޓްކުރުމުގައި މައްސަލައެއް ދިމާވެއްޖެ' });
  }
});

router.delete('/pages/:id', (req, res) => {
  try {
    db.prepare('DELETE FROM pages WHERE id = ?').run(req.params.id);
    res.json({ success: true });
  } catch (err) {
    res.status(500).json({ error: 'ޕޭޖް ފުހެލުމުގައި މައްސަލައެއް ދިމާވެއްޖެ' });
  }
});

// --- MENUS MANAGEMENT ---
router.get('/menus', (req, res) => {
  try {
    const menus = db.prepare('SELECT * FROM menus ORDER BY position ASC, sort_order ASC, id ASC').all();
    res.json(menus);
  } catch (err) {
    res.status(500).json({ error: 'މެނޫތައް ހޯދުމުގައި މައްސަލައެއް ދިމާވެއްޖެ' });
  }
});

router.post('/menus', (req, res) => {
  const { title, link, parent_id, sort_order, position } = req.body;
  try {
    const info = db.prepare('INSERT INTO menus (title, link, parent_id, sort_order, position) VALUES (?, ?, ?, ?, ?)').run(title, link, parent_id, sort_order, position);
    res.status(201).json({ id: info.lastInsertRowid });
  } catch (err) {
    res.status(500).json({ error: 'މެނޫ ސޭވްކުރުމުގައި މައްސަލައެއް ދިމާވެއްޖެ' });
  }
});

router.put('/menus/:id', (req, res) => {
  const { id } = req.params;
  const { title, link, parent_id, sort_order, position } = req.body;
  try {
    db.prepare('UPDATE menus SET title = ?, link = ?, parent_id = ?, sort_order = ?, position = ? WHERE id = ?').run(title, link, parent_id, sort_order, position, id);
    res.json({ success: true });
  } catch (err) {
    res.status(500).json({ error: 'މެނޫ އަޕްޑޭޓްކުރުމުގައި މައްސަލައެއް ދިމާވެއްޖެ' });
  }
});

router.delete('/menus/:id', (req, res) => {
  try {
    db.prepare('DELETE FROM menus WHERE id = ?').run(req.params.id);
    res.json({ success: true });
  } catch (err) {
    res.status(500).json({ error: 'މެނޫ ފުހެލުމުގައި މައްސަލައެއް ދިމާވެއްޖެ' });
  }
});

// --- GALLERY MANAGEMENT ---
router.get('/galleries', (req, res) => {
  try {
    const galleries = db.prepare('SELECT * FROM galleries ORDER BY created_at DESC').all();
    for (const g of galleries) {
      g.images = db.prepare('SELECT * FROM gallery_images WHERE gallery_id = ? ORDER BY sort_order ASC').all(g.id);
    }
    res.json(galleries);
  } catch (err) {
    res.status(500).json({ error: 'ގެލެރީތައް ހޯދުމުގައި މައްސަލައެއް ދިމާވެއްޖެ' });
  }
});

router.post('/galleries', (req, res) => {
  const { title, slug, description } = req.body;
  try {
    const info = db.prepare('INSERT INTO galleries (title, slug, description) VALUES (?, ?, ?)').run(title, slug, description);
    res.status(201).json({ id: info.lastInsertRowid });
  } catch (err) {
    res.status(500).json({ error: 'ގެލެރީ ސޭވްކުރުމުގައި މައްސަލައެއް ދިމާވެއްޖެ' });
  }
});

router.post('/galleries/:id/images', upload.single('image'), (req, res) => {
  const { id } = req.params;
  const { caption, sort_order } = req.body;
  const image = req.file ? req.file.filename : null;
  if (!image) return res.status(400).json({ error: 'ފޮޓޯއެއް ނެތް' });

  try {
    const info = db.prepare('INSERT INTO gallery_images (gallery_id, image, caption, sort_order) VALUES (?, ?, ?, ?)').run(id, image, caption, sort_order || 0);
    res.status(201).json({ id: info.lastInsertRowid, filename: image });
  } catch (err) {
    res.status(500).json({ error: 'ފޮޓޯ ސޭވްކުރުމުގައި މައްސަލައެއް ދިމާވެއްޖެ' });
  }
});

router.delete('/gallery-images/:id', (req, res) => {
  try {
    db.prepare('DELETE FROM gallery_images WHERE id = ?').run(req.params.id);
    res.json({ success: true });
  } catch (err) {
    res.status(500).json({ error: 'ފޮޓޯ ފުހެލުމުގައި މައްސަލައެއް ދިމާވެއްޖެ' });
  }
});

router.delete('/galleries/:id', (req, res) => {
  try {
    db.prepare('DELETE FROM galleries WHERE id = ?').run(req.params.id);
    res.json({ success: true });
  } catch (err) {
    res.status(500).json({ error: 'ގެލެރީ ފުހެލުމުގައި މައްސަލައެއް ދިމާވެއްޖެ' });
  }
});

// --- INBOX & NEWSLETTER ---
router.get('/contact-messages', (req, res) => {
  try {
    const msgs = db.prepare('SELECT * FROM contact_messages ORDER BY created_at DESC').all();
    res.json(msgs);
  } catch (err) {
    res.status(500).json({ error: 'މެސެޖްތައް ހޯދުމުގައި މައްސަލައެއް ދިމާވެއްޖެ' });
  }
});

router.delete('/contact-messages/:id', (req, res) => {
  try {
    db.prepare('DELETE FROM contact_messages WHERE id = ?').run(req.params.id);
    res.json({ success: true });
  } catch (err) {
    res.status(500).json({ error: 'މެސެޖް ފުހެލުމުގައި މައްސަލައެއް ދިމާވެއްޖެ' });
  }
});

router.get('/newsletter-subscribers', (req, res) => {
  try {
    const subs = db.prepare('SELECT * FROM newsletter_subscribers ORDER BY created_at DESC').all();
    res.json(subs);
  } catch (err) {
    res.status(500).json({ error: 'ސަބްސްކްރައިބަރުން ހޯދުމުގައި މައްސަލައެއް ދިމާވެއްޖެ' });
  }
});

// Alias routes used by frontend
router.get('/newsletter/subscribers', (req, res) => {
  try {
    const subs = db.prepare('SELECT * FROM newsletter_subscribers ORDER BY created_at DESC').all();
    res.json(subs);
  } catch (err) {
    res.status(500).json({ error: 'ސަބްސްކްރައިބަރުން ހޯދުމުގައި މައްސަލައެއް ދިމާވެއްޖެ' });
  }
});

router.post('/newsletter/send', (req, res) => {
  if (req.user.role !== 'admin') return res.status(403).json({ error: 'އިޒުނައެއް ނެތް' });
  const { subject, body } = req.body || {};
  if (!subject || !body) {
    return res.status(400).json({ error: 'މައުޟޫޢު އަދި މެސެޖް ބޭނުންވޭ' });
  }
  try {
    const total = db.prepare('SELECT COUNT(*) AS count FROM newsletter_subscribers WHERE is_active = 1').get().count || 0;
    // SMTP integration can be plugged in later; this endpoint keeps backend workflow complete.
    res.json({ success: true, queued: total });
  } catch (err) {
    res.status(500).json({ error: 'ނިއުސްލެޓާ ފޮނުވުމުގައި މައްސަލަ' });
  }
});

router.delete('/newsletter-subscribers/:id', (req, res) => {
  try {
    db.prepare('DELETE FROM newsletter_subscribers WHERE id = ?').run(req.params.id);
    res.json({ success: true });
  } catch (err) {
    res.status(500).json({ error: 'ސަބްސްކްރައިބަރު ފުހެލުމުގައި މައްސަލައެއް ދިމާވެއްޖެ' });
  }
});

// --- PHRASES ---
router.get('/phrases', (req, res) => {
  try {
    const phrases = db.prepare('SELECT * FROM phrases').all();
    res.json(phrases);
  } catch (err) {
    res.status(500).json({ error: 'ޕްރޭޒްތައް ހޯދުމުގައި މައްސަލައެއް ދިމާވެއްޖެ' });
  }
});

router.post('/phrases', (req, res) => {
  const { key, value, language } = req.body;
  try {
    db.prepare('INSERT OR REPLACE INTO phrases (key, value, language) VALUES (?, ?, ?)').run(key, value, language || 'dv');
    res.json({ success: true });
  } catch (err) {
    res.status(500).json({ error: 'ޕްރޭޒް ސޭވްކުރުމުގައި މައްސަލައެއް ދިމާވެއްޖެ' });
  }
});

router.delete('/phrases/:id', (req, res) => {
  try {
    db.prepare('DELETE FROM phrases WHERE id = ?').run(req.params.id);
    res.json({ success: true });
  } catch (err) {
    res.status(500).json({ error: 'ޕްރޭޒް ފުހެލުމުގައި މައްސަލައެއް ދިމާވެއްޖެ' });
  }
});

// --- FILE MANAGER ---
router.get('/files', (req, res) => {
  const uploadDir = path.join(__dirname, '../uploads');
  if (!fs.existsSync(uploadDir)) return res.json([]);
  try {
    const files = fs.readdirSync(uploadDir).map(name => {
      const stats = fs.statSync(path.join(uploadDir, name));
      return { name, size: stats.size, created_at: stats.birthtime };
    });
    res.json(files.sort((a, b) => b.created_at - a.created_at));
  } catch (err) {
    res.status(500).json({ error: 'ފައިލްތައް ހޯދުމުގައި މައްސަލައެއް ދިމާވެއްޖެ' });
  }
});

router.delete('/files/:name', (req, res) => {
  const safeName = normalizeUploadFilename(req.params.name);
  if (!safeName) return res.status(400).json({ error: 'ފައިލް ނަން ސައްހަ ނޫން' });
  const filePath = path.join(__dirname, '../uploads', safeName);
  try {
    if (fs.existsSync(filePath)) fs.unlinkSync(filePath);
    res.json({ success: true });
  } catch (err) {
    res.status(500).json({ error: 'ފައިލް ފުހެލުމުގައި މައްސަލައެއް ދިމާވެއްޖެ' });
  }
});

// --- SITEMAP UTILITY ---
router.post('/generate-sitemap', (req, res) => {
  try {
    const articles = db.prepare('SELECT slug, published_at FROM articles WHERE is_published = 1').all();
    const pages = db.prepare('SELECT slug FROM pages WHERE is_published = 1').all();
    const categories = db.prepare('SELECT slug FROM categories').all();
    
    let xml = `<?xml version="1.0" encoding="UTF-8"?>\n<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">\n`;
    const siteUrl = (process.env.SITE_URL || 'http://localhost:3001').replace(/\/+$/, '');
    xml += `  <url><loc>${xmlEscape(siteUrl)}/#home</loc><changefreq>daily</changefreq></url>\n`;
    
    articles.forEach(a => {
      xml += `  <url><loc>${xmlEscape(siteUrl)}/#article/${xmlEscape(a.slug)}</loc><lastmod>${new Date(a.published_at).toISOString().split('T')[0]}</lastmod></url>\n`;
    });
    pages.forEach((p) => {
      xml += `  <url><loc>${xmlEscape(siteUrl)}/#page/${xmlEscape(p.slug)}</loc><changefreq>monthly</changefreq></url>\n`;
    });
    categories.forEach((c) => {
      xml += `  <url><loc>${xmlEscape(siteUrl)}/#category/${xmlEscape(c.slug)}</loc><changefreq>hourly</changefreq></url>\n`;
    });
    
    fs.writeFileSync(path.join(__dirname, '../public/sitemap.xml'), xml + '</urlset>');
    res.json({ success: true, path: '/sitemap.xml' });
  } catch (err) {
    res.status(500).json({ error: 'ސައިޓްމެޕް ހެދުމުގައި މައްސަލައެއް ދިމާވެއްޖެ' });
  }
});

// --- ADMINISTRATORS / ROLES / LANGUAGE / SETTINGS GROUPS ---
router.get('/administrators', (req, res) => {
  if (req.user.role !== 'admin') return res.status(403).json({ error: 'އިޒުނައެއް ނެތް' });
  try {
    const admins = db.prepare("SELECT id, username, full_name, avatar_url, twitter_url, role, created_at FROM users WHERE role IN ('admin','editor') ORDER BY role ASC, created_at DESC").all();
    res.json(admins);
  } catch (err) {
    res.status(500).json({ error: 'އެޑްމިން ލިސްޓް ހޯދުމުގައި މައްސަލަ' });
  }
});

router.get('/roles-permissions', (req, res) => {
  if (req.user.role !== 'admin') return res.status(403).json({ error: 'އިޒުނައެއް ނެތް' });
  try {
    const rows = db.prepare('SELECT role, permissions FROM role_permissions ORDER BY role ASC').all();
    const mapped = rows.map((row) => {
      let parsed = [];
      try { parsed = JSON.parse(row.permissions); } catch (e) {}
      return { role: row.role, permissions: parsed };
    });
    res.json(mapped);
  } catch (err) {
    res.status(500).json({ error: 'ރޯލް ޕާމިޝަންސް ހޯދުމުގައި މައްސަލަ' });
  }
});

router.put('/edit-role/:role', (req, res) => {
  if (req.user.role !== 'admin') return res.status(403).json({ error: 'އިޒުނައެއް ނެތް' });
  const role = String(req.params.role || '').trim();
  const { permissions } = req.body || {};
  if (!role) return res.status(400).json({ error: 'ރޯލް ބޭނުންވޭ' });
  try {
    db.prepare('INSERT OR REPLACE INTO role_permissions (role, permissions) VALUES (?, ?)').run(role, JSON.stringify(Array.isArray(permissions) ? permissions : []));
    res.json({ success: true });
  } catch (err) {
    res.status(500).json({ error: 'ރޯލް އަޕްޑޭޓްކުރުމުގައި މައްސަލަ' });
  }
});

router.get('/language-settings', (req, res) => {
  if (req.user.role !== 'admin') return res.status(403).json({ error: 'އިޒުނައެއް ނެތް' });
  try {
    const rows = db.prepare('SELECT * FROM languages ORDER BY is_default DESC, id ASC').all();
    res.json(rows);
  } catch (err) {
    res.status(500).json({ error: 'ލޭންގުއިޖް ސެޓިންގްސް ހޯދުމުގައި މައްސަލަ' });
  }
});

router.post('/language-settings', (req, res) => {
  if (req.user.role !== 'admin') return res.status(403).json({ error: 'އިޒުނައެއް ނެތް' });
  const { name, short_form, status, is_default } = req.body || {};
  if (!name || !short_form) return res.status(400).json({ error: 'ނަން އަދި ޝޯޓް ފޯމް ބޭނުންވޭ' });
  try {
    if (Number(is_default) === 1) db.prepare('UPDATE languages SET is_default = 0').run();
    const info = db.prepare('INSERT INTO languages (name, short_form, status, is_default) VALUES (?, ?, ?, ?)').run(name, short_form, Number(status) === 0 ? 0 : 1, Number(is_default) === 1 ? 1 : 0);
    res.status(201).json({ id: info.lastInsertRowid });
  } catch (err) {
    res.status(500).json({ error: 'ބަސް ސޭވްކުރުމުގައި މައްސަލަ' });
  }
});

router.get('/search-phrases', (req, res) => {
  try {
    const q = String(req.query.q || '').trim();
    if (!q) return res.json([]);
    const rows = db.prepare('SELECT * FROM phrases WHERE key LIKE ? OR value LIKE ? ORDER BY id DESC LIMIT 100').all(`%${q}%`, `%${q}%`);
    res.json(rows);
  } catch (err) {
    res.status(500).json({ error: 'ޕްރޭޒް ސާޗްގައި މައްސަލަ' });
  }
});

router.get('/seo-tools', (req, res) => {
  try {
    const settings = db.prepare("SELECT key, value FROM settings WHERE key IN ('ga_id','meta_keywords','fb_app_id','google_client_id')").all();
    const map = {};
    settings.forEach((s) => { map[s.key] = s.value; });
    res.json(map);
  } catch (err) {
    res.status(500).json({ error: 'SEO މަޢުލޫމާތު ހޯދުމުގައި މައްސަލަ' });
  }
});

router.get('/social-login-configuration', (req, res) => {
  try {
    const settings = db.prepare("SELECT key, value FROM settings WHERE key IN ('google_client_id','fb_app_id')").all();
    const map = {};
    settings.forEach((s) => { map[s.key] = s.value; });
    res.json(map);
  } catch (err) {
    res.status(500).json({ error: 'ސޯޝަލް ލޮގިން ސެޓިންގްސް ހޯދުމުގައި މައްސަލަ' });
  }
});

router.post('/cache-system/clear', (req, res) => {
  try {
    const { type } = req.body || {};
    res.json({ success: true, cleared: type || 'all' });
  } catch (err) {
    res.status(500).json({ error: 'ކެޝް ކުލިއަރ ކުރުމުގައި މައްސަލަ' });
  }
});

router.get('/font-settings', (req, res) => {
  try {
    const rows = db.prepare("SELECT key, value FROM settings WHERE key IN ('primary_font','headline_font','menu_font')").all();
    const out = {};
    rows.forEach((r) => { out[r.key] = r.value; });
    res.json(out);
  } catch (err) {
    res.status(500).json({ error: 'ފޮންޓް ސެޓިންގްސް ހޯދުމުގައި މައްސަލަ' });
  }
});

router.get('/themes', (req, res) => {
  res.json([
    { id: 'premium-light', name: 'Premium Light' },
    { id: 'premium-dark', name: 'Premium Dark' },
    { id: 'classic', name: 'Classic' }
  ]);
});

router.delete('/newsletter/subscribers/:id', (req, res) => {
  try {
    db.prepare('DELETE FROM newsletter_subscribers WHERE id = ?').run(req.params.id);
    res.json({ success: true });
  } catch (err) {
    res.status(500).json({ error: 'ސަބްސްކްރައިބަރު ފުހެލުމުގައި މައްސަލަ' });
  }
});

// Route aliases to mirror legacy backend naming
router.get('/navigation', (req, res) => {
  try {
    const rows = db.prepare('SELECT * FROM menus ORDER BY position ASC, sort_order ASC, id ASC').all();
    res.json(rows);
  } catch (err) {
    res.status(500).json({ error: 'ނެވިގޭޝަން ހޯދުމުގައި މައްސަލަ' });
  }
});

router.get('/post-queues/:type', (req, res) => {
  const type = String(req.params.type || '').toLowerCase();
  let where = '';
  if (type === 'slider') where = 'is_slider = 1';
  else if (type === 'featured') where = 'is_featured = 1';
  else if (type === 'breaking') where = 'is_breaking = 1';
  else if (type === 'recommended') where = 'is_recommended = 1';
  else if (type === 'pending') where = "status = 'pending'";
  else if (type === 'scheduled') where = "status = 'scheduled' OR (scheduled_at IS NOT NULL AND datetime(scheduled_at) > datetime('now'))";
  else if (type === 'drafts') where = "status = 'draft' OR is_published = 0";
  else return res.status(400).json({ error: 'ޕޯސްޓް ކިއު ޓައިޕް ސައްހަ ނޫން' });

  try {
    const rows = db.prepare(`
      SELECT a.*, c.name AS category_name
      FROM articles a
      LEFT JOIN categories c ON c.id = a.category_id
      WHERE ${where}
      ORDER BY a.created_at DESC
    `).all();
    res.json(rows);
  } catch (err) {
    res.status(500).json({ error: 'ޕޯސްޓް ކިއު ހޯދުމުގައި މައްސަލަ' });
  }
});

module.exports = router;
