<?php
declare(strict_types=1);

namespace App\Modules\HR\Models;

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

class HrDepartment extends Model
{
    protected static string $table = 'hr_departments';
    protected static string $primaryKey = 'id';
    protected static bool $timestamps = true;
    protected static bool $softDelete = true;
    protected static bool $autoTrackAuthor = true;

    protected static array $fillable = [
        'department_code', 'department_type', 'name_ar', 'name_en', 'parent_id', 'manager_employee_id',
        'branch_id', 'description_ar', 'is_active', 'sort_order', 'staffing_capacity',
    ];

    public static function allActive(): array
    {
        return static::query()
            ->where('is_active', '=', 1)
            ->orderBy('name_ar', 'ASC')
            ->get();
    }

    public static function allIdaras(): array
    {
        return static::query()
            ->where('is_active', '=', 1)
            ->where('department_type', '=', 'idara')
            ->orderBy('name_ar', 'ASC')
            ->get();
    }

    public static function allAqsam(): array
    {
        return static::query()
            ->where('is_active', '=', 1)
            ->where('department_type', '=', 'qism')
            ->orderBy('name_ar', 'ASC')
            ->get();
    }

    public static function getAqsamByIdara(int $idaraId): array
    {
        return static::query()
            ->where('is_active', '=', 1)
            ->where('department_type', '=', 'qism')
            ->where('parent_id', '=', $idaraId)
            ->orderBy('name_ar', 'ASC')
            ->get();
    }

    public static function getTree(): array
    {
        $all = static::allActive();
        return self::buildTree($all, null);
    }

    private static function buildTree(array $items, ?int $parentId): array
    {
        $tree = [];
        foreach ($items as $item) {
            $pid = $item['parent_id'] ? (int) $item['parent_id'] : null;
            if ($pid === $parentId) {
                $children = self::buildTree($items, (int) $item['id']);
                $item['children'] = $children;
                $tree[] = $item;
            }
        }
        return $tree;
    }

    public static function search(array $filters, int $perPage = 25, int $page = 1): array
    {
        $db = App::getInstance()->db();
        $where = 'd.is_archived = 0';
        $params = [];

        if (!empty($filters['q'])) {
            $search = '%' . $filters['q'] . '%';
            $where .= ' AND (d.name_ar LIKE ? OR d.name_en LIKE ? OR d.department_code LIKE ?)';
            $params[] = $search;
            $params[] = $search;
            $params[] = $search;
        }
        if (!empty($filters['branch_id'])) {
            $where .= ' AND d.branch_id = ?';
            $params[] = (int) $filters['branch_id'];
        }
        if (!empty($filters['department_type'])) {
            $where .= ' AND d.department_type = ?';
            $params[] = $filters['department_type'];
        }
        if (!empty($filters['parent_id'])) {
            $where .= ' AND d.parent_id = ?';
            $params[] = (int) $filters['parent_id'];
        }
        if (isset($filters['is_active']) && $filters['is_active'] !== '') {
            $where .= ' AND d.is_active = ?';
            $params[] = (int) $filters['is_active'];
        }

        $countRow = $db->selectOne("SELECT COUNT(*) as cnt FROM hr_departments d WHERE {$where}", $params);
        $total = (int) ($countRow['cnt'] ?? 0);
        $offset = ($page - 1) * $perPage;

        $rows = $db->select(
            "SELECT d.*, p.name_ar as parent_name, b.name_ar as branch_name
             FROM hr_departments d
             LEFT JOIN hr_departments p ON p.id = d.parent_id
             LEFT JOIN branches b ON b.id = d.branch_id
             WHERE {$where}
             ORDER BY d.department_type ASC, d.name_ar ASC
             LIMIT {$perPage} OFFSET {$offset}",
            $params
        );

        return ['data' => $rows, 'pagination' => Pagination::paginate($total, $perPage, $page)];
    }
}
