// index.js — خادم منصة الأرشفة الرقمية لمجلة نور الإسلام
import express from 'express';
import cors from 'cors';
import compression from 'compression';
import { dirname, join } from 'node:path';
import { fileURLToPath } from 'node:url';
import db from './db.js';

const __dirname = dirname(fileURLToPath(import.meta.url));
const app = express();
const PORT = process.env.PORT || 3344;

app.use(cors());
app.use(compression());
app.use(express.json());

// خدمة صور الصفحات بشكل مباشر (static)
app.use('/media', express.static(join(__dirname, 'uploads', 'pages')));

// خدمة واجهة المستخدم (Frontend)
app.use(express.static(join(__dirname, '..', 'client')));

// =====================================================================
// أدوات مساعدة
// =====================================================================

function sanitizeFtsQuery(q) {
  // إزالة رموز FTS5 الخاصة التي قد تكسر الاستعلام، وتحويل المسافات لعملية AND ضمنية
  return q
    .replace(/["'*^]/g, ' ')
    .trim()
    .split(/\s+/)
    .filter(Boolean)
    .map(term => `"${term}"*`)
    .join(' ');
}

// =====================================================================
// 1) المجلدات (Volumes)
// =====================================================================

app.get('/api/volumes', (req, res) => {
  const volumes = db.prepare(`
    SELECT v.*,
      (SELECT COUNT(*) FROM pages p WHERE p.volume_id = v.id AND p.ocr_status = 'done') as pages_processed,
      (SELECT COUNT(*) FROM articles a WHERE a.volume_id = v.id) as articles_count
    FROM volumes v
    ORDER BY v.hijri_year, v.volume_number
  `).all();
  res.json({ volumes });
});

app.get('/api/volumes/:id', (req, res) => {
  const volume = db.prepare(`SELECT * FROM volumes WHERE id = ?`).get(req.params.id);
  if (!volume) return res.status(404).json({ error: 'المجلد غير موجود' });

  const stats = db.prepare(`
    SELECT
      COUNT(*) as total_pages_in_db,
      SUM(CASE WHEN ocr_status = 'done' THEN 1 ELSE 0 END) as ocr_done,
      SUM(CASE WHEN ocr_status = 'failed' THEN 1 ELSE 0 END) as ocr_failed
    FROM pages WHERE volume_id = ?
  `).get(req.params.id);

  res.json({ volume, stats });
});

// =====================================================================
// 2) الصفحات (Pages) — تصفح وعرض صورة الصفحة + نصها
// =====================================================================

app.get('/api/volumes/:volumeId/pages', (req, res) => {
  const { volumeId } = req.params;
  const page = parseInt(req.query.page || '1', 10);
  const perPage = Math.min(parseInt(req.query.per_page || '20', 10), 100);
  const offset = (page - 1) * perPage;

  const pages = db.prepare(`
    SELECT id, pdf_page_number, printed_page_number, image_path, thumb_path, ocr_status, article_id
    FROM pages WHERE volume_id = ?
    ORDER BY pdf_page_number
    LIMIT ? OFFSET ?
  `).all(volumeId, perPage, offset);

  const total = db.prepare(`SELECT COUNT(*) as c FROM pages WHERE volume_id = ?`).get(volumeId).c;

  res.json({ pages, total, page, per_page: perPage });
});

app.get('/api/pages/:id', (req, res) => {
  const page = db.prepare(`
    SELECT p.*, v.volume_number, v.hijri_year, v.gregorian_year, v.title as volume_title,
           a.title as article_title, au.display_name as author_name
    FROM pages p
    JOIN volumes v ON v.id = p.volume_id
    LEFT JOIN articles a ON a.id = p.article_id
    LEFT JOIN authors au ON au.id = a.author_id
    WHERE p.id = ?
  `).get(req.params.id);

  if (!page) return res.status(404).json({ error: 'الصفحة غير موجودة' });
  res.json({ page });
});

// =====================================================================
// 3) البحث (Search) — النص الكامل + المؤلف + الموضوع
// =====================================================================

app.get('/api/search', (req, res) => {
  const q = (req.query.q || '').trim();
  const volumeId = req.query.volume_id || null;
  const page = parseInt(req.query.page || '1', 10);
  const perPage = Math.min(parseInt(req.query.per_page || '20', 10), 50);
  const offset = (page - 1) * perPage;

  if (!q) {
    return res.status(400).json({ error: 'الرجاء إدخال كلمة أو عبارة للبحث' });
  }

  const ftsQuery = sanitizeFtsQuery(q);
  if (!ftsQuery) {
    return res.status(400).json({ error: 'استعلام البحث غير صالح' });
  }

  try {
    const baseWhere = volumeId ? `AND p.volume_id = @volumeId` : '';
    const searchParams = volumeId
      ? { ftsQuery, volumeId, perPage, offset }
      : { ftsQuery, perPage, offset };

    const rows = db.prepare(`
      SELECT
        p.id as page_id,
        p.pdf_page_number,
        p.printed_page_number,
        p.volume_id,
        v.volume_number,
        v.hijri_year,
        v.gregorian_year,
        p.thumb_path,
        p.image_path,
        p.ocr_confidence,
        a.id as article_id,
        a.title as article_title,
        au.display_name as author_name,
        snippet(pages_fts, 0, '【', '】', ' … ', 14) as snippet,
        bm25(pages_fts) as rank
      FROM pages_fts
      JOIN pages p ON p.id = pages_fts.rowid
      JOIN volumes v ON v.id = p.volume_id
      LEFT JOIN articles a ON a.id = p.article_id
      LEFT JOIN authors au ON au.id = a.author_id
      WHERE pages_fts MATCH @ftsQuery ${baseWhere}
      ORDER BY rank - (COALESCE(p.ocr_confidence, 0.1) * 3)
      LIMIT @perPage OFFSET @offset
    `).all(searchParams);

    const totalParams = volumeId ? { ftsQuery, volumeId } : { ftsQuery };
    const totalRow = db.prepare(`
      SELECT COUNT(*) as c
      FROM pages_fts
      JOIN pages p ON p.id = pages_fts.rowid
      WHERE pages_fts MATCH @ftsQuery ${baseWhere}
    `).get(totalParams);

    res.json({
      query: q,
      total: totalRow.c,
      page,
      per_page: perPage,
      results: rows
    });
  } catch (err) {
    res.status(400).json({ error: 'تعذّر تنفيذ البحث', details: err.message });
  }
});

// بحث فى عناوين المقالات والمؤلفين (بحث وصفي / Metadata search)
app.get('/api/search/articles', (req, res) => {
  const q = (req.query.q || '').trim();
  if (!q) return res.status(400).json({ error: 'الرجاء إدخال كلمة للبحث' });

  const like = `%${q}%`;
  const rows = db.prepare(`
    SELECT a.id, a.title, a.start_page, a.end_page, a.volume_id,
           v.volume_number, v.hijri_year,
           au.display_name as author_name
    FROM articles a
    JOIN volumes v ON v.id = a.volume_id
    LEFT JOIN authors au ON au.id = a.author_id
    WHERE a.title LIKE @like OR au.display_name LIKE @like
    ORDER BY v.hijri_year, a.toc_order
    LIMIT 50
  `).all({ like });

  res.json({ query: q, results: rows });
});

// =====================================================================
// 4) المؤلفون (Authors)
// =====================================================================

app.get('/api/authors', (req, res) => {
  const authors = db.prepare(`
    SELECT au.*, COUNT(a.id) as articles_count
    FROM authors au
    LEFT JOIN articles a ON a.author_id = au.id
    GROUP BY au.id
    ORDER BY articles_count DESC, au.display_name
  `).all();
  res.json({ authors });
});

app.get('/api/authors/:id/articles', (req, res) => {
  const articles = db.prepare(`
    SELECT a.*, v.volume_number, v.hijri_year
    FROM articles a
    JOIN volumes v ON v.id = a.volume_id
    WHERE a.author_id = ?
    ORDER BY v.hijri_year, a.start_page
  `).all(req.params.id);
  res.json({ articles });
});

// =====================================================================
// 5) المقالات (Articles) والفهرس
// =====================================================================

app.get('/api/volumes/:volumeId/articles', (req, res) => {
  const articles = db.prepare(`
    SELECT a.*, au.display_name as author_name
    FROM articles a
    LEFT JOIN authors au ON au.id = a.author_id
    WHERE a.volume_id = ?
    ORDER BY a.toc_letter, a.toc_order
  `).all(req.params.volumeId);
  res.json({ articles });
});

app.get('/api/articles/:id', (req, res) => {
  const article = db.prepare(`
    SELECT a.*, au.display_name as author_name, v.volume_number, v.hijri_year, v.gregorian_year
    FROM articles a
    LEFT JOIN authors au ON au.id = a.author_id
    JOIN volumes v ON v.id = a.volume_id
    WHERE a.id = ?
  `).get(req.params.id);

  if (!article) return res.status(404).json({ error: 'المقال غير موجود' });

  const pages = db.prepare(`
    SELECT id, pdf_page_number, printed_page_number, image_path, thumb_path
    FROM pages WHERE article_id = ? ORDER BY pdf_page_number
  `).all(req.params.id);

  const subjects = db.prepare(`
    SELECT s.id, s.name FROM subjects s
    JOIN article_subjects asu ON asu.subject_id = s.id
    WHERE asu.article_id = ?
  `).all(req.params.id);

  const keywords = db.prepare(`
    SELECT k.id, k.term FROM keywords k
    JOIN article_keywords ak ON ak.keyword_id = k.id
    WHERE ak.article_id = ?
  `).all(req.params.id);

  const figures = db.prepare(`
    SELECT f.id, f.name FROM figures f
    JOIN article_figures af ON af.figure_id = f.id
    WHERE af.article_id = ?
  `).all(req.params.id);

  const quranCitations = db.prepare(`
    SELECT id, surah_name, ayah_text FROM quran_citations WHERE article_id = ?
  `).all(req.params.id);

  const hadithCitations = db.prepare(`
    SELECT id, hadith_text, narrator, source_book FROM hadith_citations WHERE article_id = ?
  `).all(req.params.id);

  const installments = db.prepare(`
    SELECT * FROM article_installments WHERE article_id = ? ORDER BY installment_order
  `).all(req.params.id);

  res.json({
    article, pages, subjects, keywords, figures,
    quran_citations: quranCitations,
    hadith_citations: hadithCitations,
    installments
  });
});

// =====================================================================
// 6) إحصائيات عامة للوحة الرئيسية
// =====================================================================

app.get('/api/stats', (req, res) => {
  const stats = {
    volumes: db.prepare(`SELECT COUNT(*) as c FROM volumes`).get().c,
    pages_total: db.prepare(`SELECT COUNT(*) as c FROM pages`).get().c,
    pages_ocr_done: db.prepare(`SELECT COUNT(*) as c FROM pages WHERE ocr_status = 'done'`).get().c,
    articles: db.prepare(`SELECT COUNT(*) as c FROM articles`).get().c,
    authors: db.prepare(`SELECT COUNT(*) as c FROM authors`).get().c,
    ocr_quality: {
      good: db.prepare(`SELECT COUNT(*) as c FROM pages WHERE ocr_confidence >= 0.08`).get().c,
      medium: db.prepare(`SELECT COUNT(*) as c FROM pages WHERE ocr_confidence >= 0.05 AND ocr_confidence < 0.08`).get().c,
      low: db.prepare(`SELECT COUNT(*) as c FROM pages WHERE ocr_confidence < 0.05 AND ocr_confidence IS NOT NULL AND length(ocr_text) > 100`).get().c,
    }
  };
  res.json(stats);
});

// =====================================================================
// 7) الموضوعات (Subjects) — خريطة المعرفة
// =====================================================================

app.get('/api/subjects', (req, res) => {
  const subjects = db.prepare(`
    SELECT s.*, COUNT(asu.article_id) as articles_count
    FROM subjects s
    LEFT JOIN article_subjects asu ON asu.subject_id = s.id
    GROUP BY s.id
    ORDER BY articles_count DESC, s.name
  `).all();
  res.json({ subjects });
});

app.get('/api/subjects/:id/articles', (req, res) => {
  const articles = db.prepare(`
    SELECT a.*, au.display_name as author_name, v.volume_number, v.hijri_year
    FROM articles a
    JOIN article_subjects asu ON asu.article_id = a.id
    JOIN volumes v ON v.id = a.volume_id
    LEFT JOIN authors au ON au.id = a.author_id
    WHERE asu.subject_id = ?
    ORDER BY v.hijri_year, a.start_page
  `).all(req.params.id);
  res.json({ articles });
});

// =====================================================================
// 8) البحث المتقدم (Advanced Search) — فلاتر متعددة مجتمعة
// =====================================================================

app.get('/api/search/advanced', (req, res) => {
  const { author_id, subject_id, volume_id, article_type, hijri_year_from, hijri_year_to } = req.query;

  const conditions = [];
  const params = {};

  if (author_id) { conditions.push('a.author_id = @author_id'); params.author_id = author_id; }
  if (volume_id) { conditions.push('a.volume_id = @volume_id'); params.volume_id = volume_id; }
  if (article_type) { conditions.push('a.article_type = @article_type'); params.article_type = article_type; }

  let joinSubject = '';
  if (subject_id) {
    joinSubject = 'JOIN article_subjects asu ON asu.article_id = a.id';
    conditions.push('asu.subject_id = @subject_id');
    params.subject_id = subject_id;
  }

  const where = conditions.length ? 'WHERE ' + conditions.join(' AND ') : '';

  const articles = db.prepare(`
    SELECT DISTINCT a.*, au.display_name as author_name, v.volume_number, v.hijri_year
    FROM articles a
    ${joinSubject}
    JOIN volumes v ON v.id = a.volume_id
    LEFT JOIN authors au ON au.id = a.author_id
    ${where}
    ORDER BY v.hijri_year, a.start_page
    LIMIT 100
  `).all(params);

  res.json({ filters: req.query, total: articles.length, results: articles });
});

app.get('/api/health', (req, res) => res.json({ status: 'ok', time: new Date().toISOString() }));

// =====================================================================
// 9) الاقتباس العلمي (Citation) — صيغة APA لكل مقال
// =====================================================================

app.get('/api/articles/:id/citation', (req, res) => {
  const article = db.prepare(`
    SELECT a.title, a.start_page, au.display_name as author_name,
           v.volume_number, v.hijri_year, v.gregorian_year, v.title as journal_title
    FROM articles a
    LEFT JOIN authors au ON au.id = a.author_id
    JOIN volumes v ON v.id = a.volume_id
    WHERE a.id = ?
  `).get(req.params.id);

  if (!article) return res.status(404).json({ error: 'المقال غير موجود' });

  const author = article.author_name || 'بدون مؤلف مذكور';
  const year = article.gregorian_year || article.hijri_year || 'بدون تاريخ';

  const apa = `${author} (${year}م). ${article.title}. ${article.journal_title}، ${article.volume_number}، ص ${article.start_page}.`;
  const chicago = `${author}. "${article.title}." ${article.journal_title} ${article.volume_number} (${year}م): ${article.start_page}.`;

  res.json({ apa, chicago, raw: article });
});

// =====================================================================
// 7) أداة المراجعة الإدارية (Review Tool) — لربط الصفحات بالمقالات يدويًا
//    وتصحيح نصوص OCR وأرقام الصفحات المطبوعة بثقة كاملة
// =====================================================================

app.get('/api/review/volumes/:volumeId/pages', (req, res) => {
  const { volumeId } = req.params;
  const onlyUnlinked = req.query.unlinked === '1';
  const where = onlyUnlinked ? 'AND p.article_id IS NULL' : '';

  const pages = db.prepare(`
    SELECT p.id, p.pdf_page_number, p.printed_page_number, p.image_path, p.thumb_path,
           p.article_id, p.reviewed_by_human, a.title as article_title,
           substr(p.ocr_text, 1, 100) as ocr_preview
    FROM pages p
    LEFT JOIN articles a ON a.id = p.article_id
    WHERE p.volume_id = ? ${where}
    ORDER BY p.pdf_page_number
  `).all(volumeId);

  res.json({ pages });
});

app.put('/api/review/pages/:id', (req, res) => {
  const { article_id, printed_page_number, ocr_text } = req.body;
  const fields = [];
  const params = {};

  if (article_id !== undefined) { fields.push('article_id = @article_id'); params.article_id = article_id; }
  if (printed_page_number !== undefined) { fields.push('printed_page_number = @printed_page_number'); params.printed_page_number = printed_page_number; }
  if (ocr_text !== undefined) { fields.push('ocr_text = @ocr_text'); params.ocr_text = ocr_text; }
  fields.push(`reviewed_by_human = 1`);

  if (!fields.length) return res.status(400).json({ error: 'لا توجد حقول للتحديث' });

  params.id = req.params.id;
  db.prepare(`UPDATE pages SET ${fields.join(', ')} WHERE id = @id`).run(params);

  res.json({ success: true });
});

app.get('/api/review/stats/:volumeId', (req, res) => {
  const stats = db.prepare(`
    SELECT
      COUNT(*) as total,
      SUM(CASE WHEN article_id IS NOT NULL THEN 1 ELSE 0 END) as linked,
      SUM(CASE WHEN reviewed_by_human = 1 THEN 1 ELSE 0 END) as reviewed,
      SUM(CASE WHEN printed_page_number IS NOT NULL THEN 1 ELSE 0 END) as has_printed_num
    FROM pages WHERE volume_id = ?
  `).get(req.params.volumeId);
  res.json({ stats });
});

app.listen(PORT, () => {
  console.log(`🚀 خادم منصة الأرشفة يعمل على المنفذ ${PORT}`);
});
