<?php
// submission_queries.php

function build_filter_clause(array $filters): array
{
    $clauses = ['s.is_verified = 1'];
    $values = [];
    $types = '';

    if (!empty($filters['county'])) {
        $clauses[] = "s.county LIKE ?";
        $values[] = '%' . $filters['county'] . '%';
        $types .= 's';
    }

    if (!empty($filters['q'])) {
        $clauses[] = "(s.title LIKE ? OR s.org_name LIKE ? OR s.story LIKE ?)";
        $lk = '%' . $filters['q'] . '%';
        $values = array_merge($values, [$lk, $lk, $lk]);
        $types .= 'sss';
    }

    if (!empty($filters['sector'])) {
        $clauses[] = "(s.org_sector LIKE ? OR s.role = ?)";
        $values[] = '%"sector":"' . $filters['sector'] . '"%';
        $values[] = $filters['sector'];
        $types .= 'ss';
    }

    if (!empty($filters['subcategory'])) {
        $clauses[] = "(s.thematic_categories LIKE ? OR s.enabling_pillars LIKE ? OR s.service_chains LIKE ?)";
        $lk_sub = '%"' . $filters['subcategory'] . '"%';
        $values = array_merge($values, [$lk_sub, $lk_sub, $lk_sub]);
        $types .= 'sss';
    }

    if (!empty($filters['rating']) && is_numeric($filters['rating'])) {
        // Rating is handled later in the main query via HAVING or join
        // For now, returning standard clauses
    }

    return [implode(' AND ', $clauses), $values, $types];
}

function count_submissions($conn, array $filters): int
{
    list($where, $values, $types) = build_filter_clause($filters);
    $rating_filter = !empty($filters['rating']) && is_numeric($filters['rating']) ? (int) $filters['rating'] : 0;

    if ($rating_filter > 0) {
        $sql = "SELECT COUNT(DISTINCT s.id) as total FROM submissions s LEFT JOIN ratings r ON s.id = r.item_id AND r.item_type = 'submission' WHERE $where GROUP BY s.id HAVING IFNULL(AVG(r.rating), 0) >= ?";
        $stmt = $conn->prepare("SELECT COUNT(*) as final_total FROM ($sql) as sub");
        if ($types) {
            // We need to bind the types + the rating type
            $stmt->bind_param($types . 'i', ...array_merge($values, [$rating_filter]));
        } else {
            $stmt->bind_param('i', $rating_filter);
        }
    } else {
        $sql = "SELECT COUNT(*) as total FROM submissions s WHERE $where";
        $stmt = $conn->prepare($sql);
        if ($types) {
            $stmt->bind_param($types, ...$values);
        }
    }

    $stmt->execute();
    $res = $stmt->get_result()->fetch_assoc();
    return $res['total'] ?? ($res['final_total'] ?? 0);
}

function fetch_submissions($conn, array $filters, string $sort, int $page, int $per_page): array
{
    list($where, $values, $types) = build_filter_clause($filters);
    $offset = ($page - 1) * $per_page;
    $rating_filter = !empty($filters['rating']) && is_numeric($filters['rating']) ? (int) $filters['rating'] : 0;

    $order_sql = match ($sort) {
        'oldest' => 's.created_at ASC',
        'newest' => 's.created_at DESC',
        'az' => 's.org_name ASC',
        'top-rated' => 'avg_rat DESC, s.created_at DESC',
        default => 's.view_count DESC, s.created_at DESC',
    };

    if ($sort === 'top-rated' || $rating_filter > 0) {
        $sql = "SELECT s.*, IFNULL(s.view_count, 0) as view_count, IFNULL(AVG(r.rating), 0) as avg_rat 
                 FROM submissions s LEFT JOIN ratings r ON s.id = r.item_id AND r.item_type = 'submission' 
                 WHERE $where GROUP BY s.id";
        if ($rating_filter > 0) {
            $sql .= " HAVING avg_rat >= ?";
            $values[] = $rating_filter;
            $types .= 'i';
        }
        $sql .= " ORDER BY $order_sql LIMIT ? OFFSET ?";
    } else {
        $sql = "SELECT s.*, IFNULL(s.view_count, 0) as view_count 
                 FROM submissions s 
                 WHERE $where ORDER BY $order_sql LIMIT ? OFFSET ?";
    }

    $stmt = $conn->prepare($sql);
    $values[] = $per_page;
    $values[] = $offset;
    $types .= 'ii';

    if ($types) {
        $stmt->bind_param($types, ...$values);
    }

    $stmt->execute();
    return $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
}
?>