<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsTo;
use Illuminate\Database\Eloquent\Builder;

class ExcelRecord extends Model
{
    use HasFactory;

    protected $fillable = [
        'uploaded_file_id',
        'name',
        'mobile_number',
        'membership_card_number',
        'address',
        'house_number',
        'neighborhood',
        'work_address',
        'home_address',
        'governorate',
        'complaints',
        'notes',
        'expiry_date',
        'membership_type',
        'valid_status',
    ];

    protected $casts = [
        'expiry_date' => 'date',
    ];

    /**
     * Get the uploaded file that owns this record
     */
    public function uploadedFile(): BelongsTo
    {
        return $this->belongsTo(UploadedFile::class);
    }

    /**
     * Scope for full-text search across searchable columns
     */
    public function scopeSearch(Builder $query, ?string $search): Builder
    {
        if (empty($search)) {
            return $query;
        }

        $searchableColumns = config('excel_columns.searchable_columns', []);

        return $query->where(function ($q) use ($search, $searchableColumns) {
            foreach ($searchableColumns as $column) {
                $q->orWhere($column, 'LIKE', "%{$search}%");
            }
        });
    }

    /**
     * Scope for column-specific filtering
     */
    public function scopeFilter(Builder $query, array $filters): Builder
    {
        foreach ($filters as $column => $value) {
            if (!empty($value) && in_array($column, config('excel_columns.filterable_columns', []))) {
                if ($column === 'expiry_date') {
                    // Handle date filtering
                    if (is_array($value) && isset($value['from'])) {
                        $query->where($column, '>=', $value['from']);
                    }
                    if (is_array($value) && isset($value['to'])) {
                        $query->where($column, '<=', $value['to']);
                    }
                } else {
                    $query->where($column, $value);
                }
            }
        }

        return $query;
    }

    /**
     * Scope for sorting
     */
    public function scopeSort(Builder $query, ?string $column, ?string $direction = 'asc'): Builder
    {
        if (!empty($column) && in_array($column, array_merge(
            config('excel_columns.searchable_columns', []),
            config('excel_columns.filterable_columns', [])
        ))) {
            return $query->orderBy($column, $direction ?? 'asc');
        }

        return $query->orderBy('id', 'desc');
    }
}

