<?php
declare(strict_types=1);

require_once __DIR__ . '/../config/database.php';
require_once __DIR__ . '/support_common.php';

try {
    rvu_support_ensure_tables($pdo);
    $user = rvu_support_require_user($pdo);
    $isAdmin = rvu_support_is_admin($user['role'] ?? '');
    $method = strtoupper($_SERVER['REQUEST_METHOD'] ?? 'GET');

    if ($method === 'GET') {
        $id = isset($_GET['id']) && ctype_digit((string)$_GET['id'])
            ? (int)$_GET['id']
            : null;

        if ($id !== null && $id > 0) {
            $thread = rvu_support_fetch_thread_basic($pdo, $id);
            if (!$thread) {
                rvu_support_json(['detail' => 'Conversation not found.'], 404);
            }
            if (!rvu_support_can_access($user, $thread)) {
                rvu_support_json(['detail' => 'You do not have access to this conversation.'], 403);
            }
            $row = rvu_support_thread_row($pdo, $id, $user);
            rvu_support_json(['data' => $row]);
        }

        $scope = strtolower(trim((string)($_GET['scope'] ?? 'open')));
        $status = strtolower(trim((string)($_GET['status'] ?? '')));
        $query = trim((string)($_GET['q'] ?? ''));
        $page = max(1, (int)($_GET['page'] ?? 1));
        $limit = max(1, min(100, (int)($_GET['limit'] ?? 30)));
        $offset = ($page - 1) * $limit;

        if ($scope === 'all' && !$isAdmin) {
            rvu_support_json(['detail' => 'Administrator access is required for the full support inbox.'], 403);
        }

        $where = [];
        $params = [];

        if (!$isAdmin) {
            $where[] = 't.author_id = ?';
            $params[] = (int)$user['id'];
        }

        if ($scope === 'open') {
            $where[] = "t.status IN ('waiting_admin', 'waiting_staff')";
        } elseif ($scope === 'resolved') {
            $where[] = "t.status = 'resolved'";
        } elseif ($scope !== 'all') {
            rvu_support_json(['detail' => 'Invalid support conversation scope.'], 400);
        }

        if ($status !== '' && $status !== 'all') {
            if (!in_array($status, ['waiting_admin', 'waiting_staff', 'resolved'], true)) {
                rvu_support_json(['detail' => 'Invalid support status filter.'], 400);
            }
            $where[] = 't.status = ?';
            $params[] = $status;
        }

        if ($query !== '') {
            $where[] = "(
                LOWER(t.title) LIKE LOWER(?) OR
                LOWER(t.category) LIKE LOWER(?) OR
                LOWER(COALESCE(t.campus, '')) LIKE LOWER(?) OR
                LOWER(COALESCE(u.full_name, '')) LIKE LOWER(?) OR
                EXISTS(
                    SELECT 1 FROM staff_support_messages sm
                    WHERE sm.thread_id = t.id
                      AND LOWER(COALESCE(sm.body, '')) LIKE LOWER(?)
                )
            )";
            $needle = '%' . $query . '%';
            array_push($params, $needle, $needle, $needle, $needle, $needle);
        }

        $whereSql = $where ? 'WHERE ' . implode(' AND ', $where) : '';
        $readColumn = $isAdmin ? 'admin_last_read_at' : 'staff_last_read_at';

        $countSql = "SELECT COUNT(*) FROM staff_support_threads t LEFT JOIN users u ON u.id = t.author_id $whereSql";
        $countStmt = $pdo->prepare($countSql);
        $countStmt->execute($params);
        $total = (int)$countStmt->fetchColumn();

        $sql = "SELECT
            t.id,
            t.author_id,
            t.title,
            t.category,
            t.priority,
            t.campus,
            t.status,
            t.created_at,
            t.updated_at,
            t.resolved_at,
            COALESCE(u.full_name, 'Staff Member') AS author_name,
            COALESCE(u.role, 'staff') AS author_role,
            COALESCE(u.profile_picture, '') AS author_profile_picture,
            COALESCE((SELECT COUNT(*) FROM staff_support_messages m WHERE m.thread_id = t.id), 0) AS message_count,
            COALESCE((
                SELECT COUNT(*)
                FROM staff_support_media a
                INNER JOIN staff_support_messages ma ON ma.id = a.message_id
                WHERE ma.thread_id = t.id
            ), 0) AS attachment_count,
            COALESCE((
                SELECT lm.body
                FROM staff_support_messages lm
                WHERE lm.thread_id = t.id
                ORDER BY lm.created_at DESC, lm.id DESC
                LIMIT 1
            ), '') AS last_message,
            COALESCE((
                SELECT lu.full_name
                FROM staff_support_messages lm2
                LEFT JOIN users lu ON lu.id = lm2.sender_user_id
                WHERE lm2.thread_id = t.id
                ORDER BY lm2.created_at DESC, lm2.id DESC
                LIMIT 1
            ), '') AS last_sender_name,
            (
                SELECT lm3.created_at
                FROM staff_support_messages lm3
                WHERE lm3.thread_id = t.id
                ORDER BY lm3.created_at DESC, lm3.id DESC
                LIMIT 1
            ) AS last_message_at,
            COALESCE((
                SELECT COUNT(*)
                FROM staff_support_messages um
                WHERE um.thread_id = t.id
                  AND um.sender_user_id <> ?
                  AND um.created_at > COALESCE(t.$readColumn, TIMESTAMPTZ '1970-01-01 00:00:00+00')
            ), 0) AS unread_count
          FROM staff_support_threads t
          LEFT JOIN users u ON u.id = t.author_id
          $whereSql
          ORDER BY COALESCE((
              SELECT MAX(mm.created_at) FROM staff_support_messages mm WHERE mm.thread_id = t.id
          ), t.updated_at, t.created_at) DESC, t.id DESC
          LIMIT $limit OFFSET $offset";

        $listParams = array_merge([(int)$user['id']], $params);
        $stmt = $pdo->prepare($sql);
        $stmt->execute($listParams);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

        $unreadTotal = 0;
        foreach ($rows as &$row) {
            $row['id'] = (int)$row['id'];
            $row['author_id'] = (int)$row['author_id'];
            $row['message_count'] = (int)$row['message_count'];
            $row['attachment_count'] = (int)$row['attachment_count'];
            $row['unread_count'] = (int)$row['unread_count'];
            $unreadTotal += $row['unread_count'];
        }
        unset($row);

        rvu_support_json([
            'data' => $rows,
            'unread_total' => $unreadTotal,
            'pagination' => [
                'page' => $page,
                'limit' => $limit,
                'total' => $total,
                'has_more' => ($offset + count($rows)) < $total,
            ],
        ]);
    }

    if ($method === 'POST') {
        if ($isAdmin) {
            rvu_support_json(['detail' => 'Administrators reply to staff conversations instead of creating staff questions.'], 403);
        }

        $input = rvu_support_input();
        $title = rvu_support_clean_text($input['title'] ?? null, 180, true);
        $category = rvu_support_clean_text($input['category'] ?? 'General Support', 80, true);
        $priority = rvu_support_clean_text($input['priority'] ?? 'Normal', 20, true);
        $body = rvu_support_clean_text($input['body'] ?? null, 8000, true);

        if (!in_array($priority, ['Normal', 'Important', 'Urgent'], true)) {
            rvu_support_json(['detail' => 'Invalid priority.'], 400);
        }

        $campus = trim((string)($user['campus'] ?? ''));
        $pdo->beginTransaction();
        try {
            $stmt = $pdo->prepare(
                "INSERT INTO staff_support_threads (
                    author_id, title, category, priority, campus, status,
                    staff_last_read_at, created_at, updated_at
                 ) VALUES (?, ?, ?, ?, ?, 'waiting_admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
                 RETURNING id"
            );
            $stmt->execute([
                (int)$user['id'],
                $title,
                $category,
                $priority,
                $campus !== '' ? $campus : null,
            ]);
            $threadId = (int)$stmt->fetchColumn();

            $messageStmt = $pdo->prepare(
                "INSERT INTO staff_support_messages (
                    thread_id, sender_user_id, sender_role, body
                 ) VALUES (?, ?, ?, ?)
                 RETURNING id"
            );
            $messageStmt->execute([
                $threadId,
                (int)$user['id'],
                (string)($user['role'] ?? 'staff'),
                $body,
            ]);
            $messageId = (int)$messageStmt->fetchColumn();
            $pdo->commit();

            rvu_support_json([
                'message' => 'Question submitted successfully.',
                'thread_id' => $threadId,
                'message_id' => $messageId,
            ], 201);
        } catch (Throwable $e) {
            if ($pdo->inTransaction()) $pdo->rollBack();
            throw $e;
        }
    }

    if ($method === 'PUT') {
        $id = isset($_GET['id']) && ctype_digit((string)$_GET['id'])
            ? (int)$_GET['id']
            : 0;
        if ($id <= 0) {
            rvu_support_json(['detail' => 'A valid conversation ID is required.'], 400);
        }

        $thread = rvu_support_fetch_thread_basic($pdo, $id);
        if (!$thread) {
            rvu_support_json(['detail' => 'Conversation not found.'], 404);
        }
        if (!rvu_support_can_access($user, $thread)) {
            rvu_support_json(['detail' => 'You do not have access to this conversation.'], 403);
        }

        $input = rvu_support_input();
        $status = strtolower(rvu_support_clean_text($input['status'] ?? null, 30, true));

        if ($status === 'resolved') {
            if (!$isAdmin) {
                rvu_support_json(['detail' => 'Only an administrator can mark a conversation resolved.'], 403);
            }
            $stmt = $pdo->prepare(
                "UPDATE staff_support_threads
                 SET status = 'resolved', resolved_at = CURRENT_TIMESTAMP, updated_at = CURRENT_TIMESTAMP
                 WHERE id = ?"
            );
            $stmt->execute([$id]);
        } elseif ($status === 'waiting_admin' || $status === 'reopen') {
            if (!$isAdmin && (int)$thread['author_id'] !== (int)$user['id']) {
                rvu_support_json(['detail' => 'You cannot reopen this conversation.'], 403);
            }
            $stmt = $pdo->prepare(
                "UPDATE staff_support_threads
                 SET status = 'waiting_admin', resolved_at = NULL, updated_at = CURRENT_TIMESTAMP
                 WHERE id = ?"
            );
            $stmt->execute([$id]);
        } else {
            rvu_support_json(['detail' => 'Unsupported conversation status.'], 400);
        }

        rvu_support_json(['message' => 'Conversation status updated.']);
    }

    rvu_support_json(['detail' => 'Method not allowed.'], 405);
} catch (PDOException $e) {
    rvu_support_json(['detail' => 'Support database error: ' . $e->getMessage()], 500);
} catch (Throwable $e) {
    rvu_support_json(['detail' => 'Support server error: ' . $e->getMessage()], 500);
}
