<?php
namespace App\Models;

use App\Core\Model;

class BirthdayWish extends Model
{
    protected $table = 'birthday_wishes';
    protected $primaryKey = 'id';

    public function getWishesForUser($userId)
    {
        $stmt = $this->db->prepare("
            SELECT bw.*, u.name as sender_name, u.avatar as sender_avatar
            FROM birthday_wishes bw
            JOIN users u ON u.id = bw.sender_id
            WHERE bw.recipient_id = ?
            ORDER BY bw.created_at DESC
        ");
        $stmt->execute([$userId]);
        return $stmt->fetchAll(\PDO::FETCH_OBJ);
    }

    public function countWishesForUser($userId)
    {
        $stmt = $this->db->prepare("SELECT COUNT(*) as c FROM birthday_wishes WHERE recipient_id = ?");
        $stmt->execute([$userId]);
        return $stmt->fetch(\PDO::FETCH_OBJ)->c;
    }

    public function sendWish($recipientId, $senderId, $message)
    {
        return $this->create([
            'recipient_id' => $recipientId,
            'sender_id' => $senderId,
            'message' => $message,
        ]);
    }

    public function getBirthdayField()
    {
        $stmt = $this->db->prepare("SELECT * FROM profile_fields WHERE field_name = 'date_of_birth' OR field_name = 'dob' OR LOWER(label) LIKE '%date of birth%' OR LOWER(label) LIKE '%dob%' LIMIT 1");
        $stmt->execute();
        return $stmt->fetch(\PDO::FETCH_OBJ);
    }

    public function getBirthdayMembers($month = null, $day = null)
    {
        if (!$month) $month = date('m');
        $field = $this->getBirthdayField();
        if (!$field) return [];

        $stmt = $this->db->prepare("
            SELECT u.id, u.name, u.avatar, pfv.value as dob
            FROM profile_field_values pfv
            JOIN users u ON u.id = pfv.user_id
            WHERE pfv.field_id = ?
            AND MONTH(STR_TO_DATE(pfv.value, '%Y-%m-%d')) = ?
            AND u.status = 'active'
            ORDER BY DAY(STR_TO_DATE(pfv.value, '%Y-%m-%d')) ASC
        ");
        $stmt->execute([$field->id, (int)$month]);
        return $stmt->fetchAll(\PDO::FETCH_OBJ);
    }

    public function countBirthdaysThisMonth()
    {
        $field = $this->getBirthdayField();
        if (!$field) return 0;

        $stmt = $this->db->prepare("
            SELECT COUNT(*) as c
            FROM profile_field_values pfv
            JOIN users u ON u.id = pfv.user_id
            WHERE pfv.field_id = ?
            AND MONTH(STR_TO_DATE(pfv.value, '%Y-%m-%d')) = ?
            AND u.status = 'active'
        ");
        $month = date('m');
        $stmt->execute([$field->id, (int)$month]);
        return $stmt->fetch(\PDO::FETCH_OBJ)->c;
    }

    public function countBirthdaysToday()
    {
        $field = $this->getBirthdayField();
        if (!$field) return 0;

        $stmt = $this->db->prepare("
            SELECT COUNT(*) as c
            FROM profile_field_values pfv
            JOIN users u ON u.id = pfv.user_id
            WHERE pfv.field_id = ?
            AND MONTH(STR_TO_DATE(pfv.value, '%Y-%m-%d')) = ?
            AND DAY(STR_TO_DATE(pfv.value, '%Y-%m-%d')) = ?
            AND u.status = 'active'
        ");
        $stmt->execute([$field->id, (int)date('m'), (int)date('d')]);
        return $stmt->fetch(\PDO::FETCH_OBJ)->c;
    }
}
