<?php
/*
==================================================================
PLACE THIS FILE AT:
   mfit-admin/reactions-ratings.php
==================================================================
Full statistics dashboard for the Reactions & Ratings system.
*/
require_once('config.php');

// ---- Filters ----------------------------------------------------
$range  = isset($_GET['range']) ? $_GET['range'] : 'all';        // 7 | 30 | all
$search = isset($_GET['q'])     ? trim($_GET['q']) : '';
$sort   = isset($_GET['sort'])  ? $_GET['sort']    : 'reactions'; // reactions | rating | recent

$where_date = '';
if ($range === '7')  $where_date = "AND r.CreatedAt >= (NOW() - INTERVAL 7 DAY)";
if ($range === '30') $where_date = "AND r.CreatedAt >= (NOW() - INTERVAL 30 DAY)";

// ---- Totals -----------------------------------------------------
$totals = [
  'reactions' => (int)$conn->query("SELECT COUNT(*) FROM mahamudfutureit_reactions")->fetchColumn(),
  'ratings'   => (int)$conn->query("SELECT COUNT(*) FROM mahamudfutureit_ratings")->fetchColumn(),
  'avg'       => (float)($conn->query("SELECT ROUND(AVG(Stars),2) FROM mahamudfutureit_ratings")->fetchColumn() ?: 0),
  'posts_reacted' => (int)$conn->query("SELECT COUNT(DISTINCT PostId) FROM mahamudfutureit_reactions")->fetchColumn(),
];

// Per-type breakdown (global)
$type_rows = $conn->query("SELECT ReactionType, COUNT(*) c FROM mahamudfutureit_reactions GROUP BY ReactionType")
                  ->fetchAll(PDO::FETCH_KEY_PAIR);
$type_total = array_sum($type_rows) ?: 1;

$type_meta = [
  'like' => ['👍','লাইক'],
  'love' => ['❤️','ভালোবাসি'],
  'haha' => ['😂','হা হা'],
  'wow'  => ['😮','ওয়াও'],
  'sad'  => ['😢','দুঃখজনক'],
];

// ---- Per-post aggregated stats ----------------------------------
$search_sql = '';
$params = [];
if ($search !== '') {
    $search_sql = "AND a.PostTitle LIKE ?";
    $params[] = '%'.$search.'%';
}

$order_sql = "ORDER BY total_reactions DESC";
if ($sort === 'rating') $order_sql = "ORDER BY avg_rating DESC, rating_count DESC";
if ($sort === 'recent') $order_sql = "ORDER BY last_activity DESC";

$sql = "
  SELECT a.PostId, a.PostTitle, a.PostDatePublic,
    COALESCE(rx.total_reactions, 0) AS total_reactions,
    COALESCE(rx.likes,0) AS likes,
    COALESCE(rx.loves,0) AS loves,
    COALESCE(rx.hahas,0) AS hahas,
    COALESCE(rx.wows,0)  AS wows,
    COALESCE(rx.sads,0)  AS sads,
    COALESCE(rt.rating_count,0) AS rating_count,
    COALESCE(rt.avg_rating,0)   AS avg_rating,
    GREATEST(COALESCE(rx.last_at,'1970-01-01'), COALESCE(rt.last_at,'1970-01-01')) AS last_activity
  FROM mahamudfutureit_article a
  LEFT JOIN (
    SELECT PostId,
      COUNT(*) total_reactions,
      SUM(ReactionType='like') likes,
      SUM(ReactionType='love') loves,
      SUM(ReactionType='haha') hahas,
      SUM(ReactionType='wow')  wows,
      SUM(ReactionType='sad')  sads,
      MAX(CreatedAt) last_at
    FROM mahamudfutureit_reactions r
    WHERE 1=1 $where_date
    GROUP BY PostId
  ) rx ON rx.PostId = a.PostId
  LEFT JOIN (
    SELECT PostId, COUNT(*) rating_count, ROUND(AVG(Stars),1) avg_rating, MAX(CreatedAt) last_at
    FROM mahamudfutureit_ratings r
    WHERE 1=1 $where_date
    GROUP BY PostId
  ) rt ON rt.PostId = a.PostId
  WHERE (COALESCE(rx.total_reactions,0) > 0 OR COALESCE(rt.rating_count,0) > 0)
    $search_sql
  $order_sql
  LIMIT 200
";

$st = $conn->prepare($sql);
$st->execute($params);
$rows = $st->fetchAll(PDO::FETCH_ASSOC);

require_once('inc/header.php');
?>
<main id="main" class="main">
  <div class="pagetitle d-flex justify-content-between align-items-center">
    <h1><?php echo $page_title ?></h1>
    <div class="d-flex gap-2">
      <form method="get" class="d-flex gap-2">
        <input type="search" name="q" value="<?php echo htmlentities($search) ?>"
               class="form-control form-control-sm" placeholder="পোস্ট টাইটেল খুঁজুন...">
        <select name="range" class="form-select form-select-sm" onchange="this.form.submit()">
          <option value="all" <?php echo $range=='all'?'selected':'' ?>>সব সময়</option>
          <option value="30"  <?php echo $range=='30' ?'selected':'' ?>>শেষ ৩০ দিন</option>
          <option value="7"   <?php echo $range=='7'  ?'selected':'' ?>>শেষ ৭ দিন</option>
        </select>
        <select name="sort" class="form-select form-select-sm" onchange="this.form.submit()">
          <option value="reactions" <?php echo $sort=='reactions'?'selected':'' ?>>রিয়াকশন বেশি</option>
          <option value="rating"    <?php echo $sort=='rating'   ?'selected':'' ?>>রেটিং বেশি</option>
          <option value="recent"    <?php echo $sort=='recent'   ?'selected':'' ?>>সাম্প্রতিক</option>
        </select>
        <button class="btn btn-sm btn-primary"><i class="bi bi-search"></i></button>
      </form>
    </div>
  </div>

  <!-- KPI cards -->
  <section class="row g-3 mt-1">
    <div class="col-md-3 col-6">
      <div class="card shadow-sm h-100">
        <div class="card-body">
          <div class="text-muted small">মোট রিয়াকশন</div>
          <div class="fs-3 fw-bold text-primary"><?php echo number_format($totals['reactions']) ?></div>
        </div>
      </div>
    </div>
    <div class="col-md-3 col-6">
      <div class="card shadow-sm h-100">
        <div class="card-body">
          <div class="text-muted small">মোট রেটিং ভোট</div>
          <div class="fs-3 fw-bold text-warning"><?php echo number_format($totals['ratings']) ?></div>
        </div>
      </div>
    </div>
    <div class="col-md-3 col-6">
      <div class="card shadow-sm h-100">
        <div class="card-body">
          <div class="text-muted small">গড় রেটিং</div>
          <div class="fs-3 fw-bold text-success">
            <?php echo $totals['avg'] ?: '0.00' ?> <small class="text-muted">/ 5</small>
          </div>
        </div>
      </div>
    </div>
    <div class="col-md-3 col-6">
      <div class="card shadow-sm h-100">
        <div class="card-body">
          <div class="text-muted small">এনগেজড পোস্ট</div>
          <div class="fs-3 fw-bold text-info"><?php echo number_format($totals['posts_reacted']) ?></div>
        </div>
      </div>
    </div>
  </section>

  <!-- Global reaction breakdown -->
  <section class="card shadow-sm mt-4">
    <div class="card-body">
      <h5 class="card-title mb-3">রিয়াকশন বিভাজন</h5>
      <div class="row g-3">
        <?php foreach ($type_meta as $key=>$meta):
          $c = (int)($type_rows[$key] ?? 0);
          $pct = round(($c / $type_total) * 100, 1);
        ?>
          <div class="col-md col-6">
            <div class="p-3 border rounded text-center h-100">
              <div style="font-size:2rem"><?php echo $meta[0] ?></div>
              <div class="text-muted small"><?php echo $meta[1] ?></div>
              <div class="fs-5 fw-bold mt-1"><?php echo number_format($c) ?></div>
              <div class="progress mt-2" style="height:6px">
                <div class="progress-bar" role="progressbar" style="width: <?php echo $pct ?>%"></div>
              </div>
              <div class="text-muted small mt-1"><?php echo $pct ?>%</div>
            </div>
          </div>
        <?php endforeach; ?>
      </div>
    </div>
  </section>

  <!-- Per-post table -->
  <section class="card shadow-sm mt-4">
    <div class="card-body">
      <h5 class="card-title mb-3">পোস্ট অনুযায়ী পরিসংখ্যান (<?php echo count($rows) ?>)</h5>
      <div class="table-responsive">
        <table class="table table-hover align-middle">
          <thead class="table-light">
            <tr>
              <th>পোস্ট</th>
              <th class="text-center" title="লাইক">👍</th>
              <th class="text-center" title="ভালোবাসি">❤️</th>
              <th class="text-center" title="হা হা">😂</th>
              <th class="text-center" title="ওয়াও">😮</th>
              <th class="text-center" title="দুঃখজনক">😢</th>
              <th class="text-center">মোট</th>
              <th class="text-center">গড় ⭐</th>
              <th class="text-center">ভোট</th>
              <th class="text-end">সর্বশেষ</th>
            </tr>
          </thead>
          <tbody>
            <?php if (!$rows): ?>
              <tr><td colspan="10" class="text-center text-muted py-4">এখনো কোনো ডেটা নেই।</td></tr>
            <?php else: foreach ($rows as $r):
              $title = htmlentities($r['PostTitle']);
              $link  = function_exists('articleLink') ? articleLink($r['PostId']) : '#';
            ?>
              <tr>
                <td>
                  <a href="<?php echo $link ?>" target="_blank" class="text-decoration-none">
                    <?php echo $title ?>
                  </a>
                  <div class="text-muted small">ID: <?php echo $r['PostId'] ?> · <?php echo $r['PostDatePublic'] ?></div>
                </td>
                <td class="text-center"><?php echo (int)$r['likes'] ?></td>
                <td class="text-center"><?php echo (int)$r['loves'] ?></td>
                <td class="text-center"><?php echo (int)$r['hahas'] ?></td>
                <td class="text-center"><?php echo (int)$r['wows']  ?></td>
                <td class="text-center"><?php echo (int)$r['sads']  ?></td>
                <td class="text-center fw-bold text-primary"><?php echo (int)$r['total_reactions'] ?></td>
                <td class="text-center">
                  <?php if ($r['rating_count']>0): ?>
                    <span class="badge bg-warning text-dark"><?php echo $r['avg_rating'] ?> / 5</span>
                  <?php else: ?>
                    <span class="text-muted">—</span>
                  <?php endif; ?>
                </td>
                <td class="text-center"><?php echo (int)$r['rating_count'] ?></td>
                <td class="text-end text-muted small">
                  <?php echo $r['last_activity'] && $r['last_activity']!='1970-01-01 00:00:00' ? $r['last_activity'] : '—' ?>
                </td>
              </tr>
            <?php endforeach; endif; ?>
          </tbody>
        </table>
      </div>
    </div>
  </section>
</main>
<?php require_once('inc/footer.php'); ?>
