<?php

namespace App\Models;

use App\Core\Model;

class User extends Model
{
    protected $table = 'users';
    protected $primaryKey = 'id';

    public function findByEmail($email)
    {
        $users = $this->where('email', $email);
        return isset($users[0]) ? $users[0] : null;
    }

    public function getRecent($limit = 5)
    {
        $stmt = $this->db->prepare("SELECT * FROM {$this->table} ORDER BY id DESC LIMIT ?");
        $stmt->execute([$limit]);
        return $stmt->fetchAll(\PDO::FETCH_OBJ);
    }

    public function getByRole($role)
    {
        return $this->where('role', $role);
    }

    public function getActiveCount()
    {
        $stmt = $this->db->query("SELECT COUNT(*) as count FROM {$this->table} WHERE status = 'active' AND approval_status = 'approved'");
        return $stmt->fetch(\PDO::FETCH_OBJ)->count;
    }

    public function getByStatus($status)
    {
        return $this->where('status', $status);
    }

    public function getPendingApproval()
    {
        $stmt = $this->db->query("SELECT * FROM {$this->table} WHERE approval_status = 'pending' ORDER BY id DESC");
        return $stmt->fetchAll(\PDO::FETCH_OBJ);
    }

    public function getPendingCount()
    {
        $stmt = $this->db->query("SELECT COUNT(*) as count FROM {$this->table} WHERE approval_status = 'pending'");
        return $stmt->fetch(\PDO::FETCH_OBJ)->count;
    }

    public function getMembersWithProfilesPage($page = 1, $perPage = 20, $search = '')
    {
        $offset = ($page - 1) * $perPage;
        $where = "u.approval_status = 'approved'";
        $params = [];

        if ($search) {
            $where .= " AND (u.first_name LIKE ? OR u.last_name LIKE ? OR u.email LIKE ? OR mp.mobile_phone LIKE ? OR mp.alternative_phone LIKE ? OR u.other_names LIKE ?)";
            $like = "%$search%";
            $params = [$like, $like, $like, $like, $like, $like];
        }

        $countStmt = $this->db->prepare("SELECT COUNT(*) as count FROM users u LEFT JOIN member_profiles mp ON u.id = mp.user_id WHERE $where");
        $countStmt->execute($params);
        $total = $countStmt->fetch(\PDO::FETCH_OBJ)->count;

        $stmt = $this->db->prepare("SELECT u.*, mp.institution_hospital, mp.membership_category, mp.mobile_phone FROM users u LEFT JOIN member_profiles mp ON u.id = mp.user_id WHERE $where ORDER BY u.first_name ASC LIMIT ? OFFSET ?");
        $allParams = array_merge($params, [$perPage, $offset]);
        $stmt->execute($allParams);

        return (object) [
            'data' => $stmt->fetchAll(\PDO::FETCH_OBJ),
            'total' => $total,
            'page' => $page,
            'perPage' => $perPage,
            'lastPage' => max(1, ceil($total / $perPage)),
        ];
    }

    public function getFullName($id)
    {
        $user = $this->find($id);
        if (!$user) return '';
        if ($user->first_name) {
            return trim($user->first_name . ' ' . $user->other_names . ' ' . $user->last_name);
        }
        return $user->name;
    }
}
