<?php

declare(strict_types=1);

namespace App\Models;

class Event extends BaseModel
{
    protected string $table = 'events';
    protected string $primaryKey = 'id';

    public function findAllActive(): array
    {
        return $this->query(
            "SELECT * FROM {$this->table} WHERE status = 'active' ORDER BY start_date DESC LIMIT 100"
        );
    }

    public function findWithPagination(int $page = 1, int $perPage = 15, array $filters = []): array
    {
        $where = [];
        $params = [];

        if (!empty($filters['search'])) {
            $where[] = "(name LIKE :search1 OR location LIKE :search2)";
            $params['search1'] = '%' . $filters['search'] . '%';
            $params['search2'] = '%' . $filters['search'] . '%';
        }

        if (!empty($filters['status'])) {
            $where[] = "status = :status";
            $params['status'] = $filters['status'];
        }

        $from = $this->table;
        $joinUser = !empty($filters['user_id']);
        if ($joinUser) {
            $from .= " e";
            $where[] = "eu.user_id = :user_id";
            $params['user_id'] = (int) $filters['user_id'];
        }

        $whereClause = !empty($where) ? 'WHERE ' . implode(' AND ', $where) : '';
        $perPage = max(1, $perPage);
        $offset = ($page - 1) * $perPage;

        $joinClause = $joinUser ? " INNER JOIN event_users eu ON e.id = eu.event_id" : "";

        $allowedSortFields = ['name', 'start_date', 'end_date', 'created_at'];
        $sortBy = !empty($filters['sort_by']) && in_array($filters['sort_by'], $allowedSortFields)
            ? ($joinUser ? 'e.' : '') . $filters['sort_by']
            : ($joinUser ? 'e.' : '') . 'created_at';
        $sortOrder = !empty($filters['sort_order']) && strtolower($filters['sort_order']) === 'asc' ? 'ASC' : 'DESC';

        $total = (int) $this->queryOne(
            "SELECT COUNT(*) as cnt FROM {$from}{$joinClause} {$whereClause}",
            $params
        )['cnt'];

        $selectFields = $joinUser ? "e.*" : "*";
        $items = $this->query(
            "SELECT {$selectFields} FROM {$from}{$joinClause} {$whereClause}
             ORDER BY {$sortBy} {$sortOrder}
             LIMIT {$perPage} OFFSET {$offset}",
            $params
        );

        return [
            'items' => $items,
            'total' => $total,
            'page' => $page,
            'per_page' => $perPage,
            'total_pages' => (int) ceil($total / max(1, $perPage)),
        ];
    }
}
