<?php
declare(strict_types=1);

namespace App\Modules\Children\Models;

use App\Core\Model;
use App\Core\App;

class Child extends Model
{
    protected static string $table = 'children';
    protected static string $primaryKey = 'id';
    protected static bool $timestamps = true;
    protected static bool $softDelete = true;
    protected static bool $dispatchEvents = true;

    protected static array $fillable = [
        'member_id', 'child_order', 'full_name_ar', 'full_name_en',
        'national_id', 'birth_certificate_number', 'date_of_birth',
        'age_years', 'age_months', 'gender', 'relationship',
        'school_faculty', 'nationality', 'classification',
        'addition_fee', 'fee_receipt_number', 'status',
        'is_frozen', 'frozen_at', 'frozen_reason', 'photo_path', 'remarks',
    ];

    public static function getForMember(int $memberId): array
    {
        $db = App::getInstance()->db();
        return $db->select(
            "SELECT * FROM children WHERE member_id = ? AND is_archived = 0 ORDER BY child_order ASC",
            [$memberId]
        );
    }

    public static function countActiveForMember(int $memberId): int
    {
        $db = App::getInstance()->db();
        $row = $db->selectOne(
            "SELECT COUNT(*) as cnt FROM children WHERE member_id = ? AND is_archived = 0 AND status = 'active'",
            [$memberId]
        );
        return (int) ($row['cnt'] ?? 0);
    }

    public static function countActiveUnder18ForMember(int $memberId): int
    {
        $db = App::getInstance()->db();
        $row = $db->selectOne(
            "SELECT COUNT(*) as cnt FROM children WHERE member_id = ? AND is_archived = 0 AND status = 'active' AND age_years < 18",
            [$memberId]
        );
        return (int) ($row['cnt'] ?? 0);
    }

    public static function getNextOrder(int $memberId): int
    {
        return self::countActiveForMember($memberId) + 1;
    }

    public static function nidExistsElsewhere(string $nid, ?int $excludeChildId = null): ?array
    {
        $db = App::getInstance()->db();

        $memberDup = $db->selectOne(
            "SELECT id, full_name_ar, membership_number FROM members WHERE national_id = ? AND is_archived = 0",
            [$nid]
        );
        if ($memberDup) return ['type' => 'member', 'data' => $memberDup];

        $spouseDup = $db->selectOne(
            "SELECT s.id, s.full_name_ar, m.membership_number FROM spouses s JOIN members m ON m.id = s.member_id WHERE s.national_id = ? AND s.is_archived = 0",
            [$nid]
        );
        if ($spouseDup) return ['type' => 'spouse', 'data' => $spouseDup];

        $sql = "SELECT c.id, c.full_name_ar, m.membership_number FROM children c JOIN members m ON m.id = c.member_id WHERE c.national_id = ? AND c.is_archived = 0";
        $params = [$nid];
        if ($excludeChildId) {
            $sql .= " AND c.id != ?";
            $params[] = $excludeChildId;
        }
        $childDup = $db->selectOne($sql, $params);
        if ($childDup) return ['type' => 'child', 'data' => $childDup];

        return null;
    }

    public function getClassificationLabel(): string
    {
        return match ($this->classification) {
            'included'           => 'مشمول (بدون رسوم)',
            'dependent_with_fee' => 'تابع (برسوم)',
            'temporary'          => 'مؤقت',
            'frozen'             => 'مجمد',
            'separated'          => 'منفصل',
            default              => $this->classification,
        };
    }

    public function getRelationshipLabel(): string
    {
        return match ($this->relationship) {
            'son'       => 'ابن',
            'daughter'  => 'ابنة',
            'stepchild' => 'ابن/ابنة الزوج',
            default     => $this->relationship,
        };
    }

    public function getStatusLabel(): string
    {
        return match ($this->status) {
            'active'    => 'نشط',
            'inactive'  => 'غير نشط',
            'frozen'    => 'مجمد',
            'separated' => 'منفصل',
            default     => $this->status,
        };
    }
}