<?php

namespace App\Models;

use App\Core\Model;

class ContributionType extends Model
{
    protected $table = 'contribution_types';
    protected $primaryKey = 'id';

    public function getActive()
    {
        $stmt = $this->db->query("SELECT * FROM {$this->table} WHERE is_active = 1 ORDER BY name ASC");
        return $stmt->fetchAll(\PDO::FETCH_OBJ);
    }

    public function getMappedMembers($typeId)
    {
        $stmt = $this->db->prepare("
            SELECT mct.*, u.name, u.first_name, u.last_name, u.email, mp.mobile_phone
            FROM member_contribution_types mct
            JOIN users u ON mct.user_id = u.id
            LEFT JOIN member_profiles mp ON mp.user_id = u.id
            WHERE mct.contribution_type_id = ?
            ORDER BY u.first_name ASC
        ");
        $stmt->execute([$typeId]);
        return $stmt->fetchAll(\PDO::FETCH_OBJ);
    }

    public function getMappedTypesForUser($userId)
    {
        $stmt = $this->db->prepare("
            SELECT ct.*, mct.assigned_at, mct.assigned_by
            FROM member_contribution_types mct
            JOIN contribution_types ct ON mct.contribution_type_id = ct.id
            WHERE mct.user_id = ?
            ORDER BY ct.name ASC
        ");
        $stmt->execute([$userId]);
        return $stmt->fetchAll(\PDO::FETCH_OBJ);
    }

    public function assignToMember($userId, $typeId, $assignedBy)
    {
        $stmt = $this->db->prepare("INSERT IGNORE INTO member_contribution_types (user_id, contribution_type_id, assigned_by) VALUES (?, ?, ?)");
        return $stmt->execute([$userId, $typeId, $assignedBy]);
    }

    public function unassignFromMember($userId, $typeId)
    {
        $stmt = $this->db->prepare("DELETE FROM member_contribution_types WHERE user_id = ? AND contribution_type_id = ?");
        return $stmt->execute([$userId, $typeId]);
    }

    public function bulkAssignToAll($typeId, $assignedBy)
    {
        $stmt = $this->db->prepare("
            INSERT IGNORE INTO member_contribution_types (user_id, contribution_type_id, assigned_by)
            SELECT id, ?, ? FROM users
            WHERE approval_status = 'approved' AND status = 'active'
        ");
        return $stmt->execute([$typeId, $assignedBy]);
    }

    public function bulkAssignToSelected($typeId, $userIds, $assignedBy)
    {
        $count = 0;
        foreach ($userIds as $userId) {
            if ($this->assignToMember((int)$userId, $typeId, $assignedBy)) {
                $count++;
            }
        }
        return $count;
    }

    public function getUnmappedApprovedMembers($typeId)
    {
        $stmt = $this->db->prepare("
            SELECT u.id, u.name, u.first_name, u.last_name, u.email
            FROM users u
            WHERE u.approval_status = 'approved' AND u.status = 'active'
            AND u.id NOT IN (
                SELECT user_id FROM member_contribution_types WHERE contribution_type_id = ?
            )
            ORDER BY u.first_name ASC
        ");
        $stmt->execute([$typeId]);
        return $stmt->fetchAll(\PDO::FETCH_OBJ);
    }
}