<?php
namespace App\Models;

use App\Core\Model;

class ImmigrantStatus extends Model {
    protected $table = 'immigrant_status';
    protected $primaryKey = 'id';
    protected $fillable = [
        'person_id', 'employer_id', 'status_category', 'visa_type',
        'permit_number', 'date_granted', 'expiry_date', 'is_active',
        'status_condition', 'sponsor_type', 'created_by', 'updated_by'
    ];
    
    /**
     * Get status by person ID
     */
    public function getByPerson($person_id) {
        $stmt = $this->db->prepare("
            SELECT * FROM {$this->table} 
            WHERE person_id = ? AND deleted_at IS NULL
            ORDER BY date_granted DESC
        ");
        $stmt->execute([$person_id]);
        return $stmt->fetchAll();
    }
    
    /**
     * Get current active status
     */
    public function getCurrentStatus($person_id) {
        $stmt = $this->db->prepare("
            SELECT * FROM {$this->table} 
            WHERE person_id = ? AND is_active = 1 AND deleted_at IS NULL
            ORDER BY date_granted DESC 
            LIMIT 1
        ");
        $stmt->execute([$person_id]);
        return $stmt->fetch();
    }
    
    /**
     * Get status by permit number
     */
    public function getByPermitNumber($permit_number) {
        $stmt = $this->db->prepare("
            SELECT * FROM {$this->table} 
            WHERE permit_number = ? AND deleted_at IS NULL
        ");
        $stmt->execute([$permit_number]);
        return $stmt->fetch();
    }
    
    /**
     * Get expiring soon statuses
     */
    public function getExpiringSoon($days = 30) {
        $stmt = $this->db->prepare("
            SELECT s.*, b.surname, b.given_name, b.document_no
            FROM {$this->table} s
            JOIN biographical_information b ON s.person_id = b.person_id
            WHERE s.expiry_date IS NOT NULL 
            AND s.expiry_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL ? DAY)
            AND s.is_active = 1
            AND s.deleted_at IS NULL
            ORDER BY s.expiry_date ASC
        ");
        $stmt->execute([$days]);
        return $stmt->fetchAll();
    }
    
    /**
     * Get expired statuses
     */
    public function getExpired() {
        $stmt = $this->db->query("
            SELECT s.*, b.surname, b.given_name, b.document_no
            FROM {$this->table} s
            JOIN biographical_information b ON s.person_id = b.person_id
            WHERE s.expiry_date < CURDATE()
            AND s.is_active = 1
            AND s.deleted_at IS NULL
            ORDER BY s.expiry_date DESC
        ");
        return $stmt->fetchAll();
    }
    
    /**
     * Get statistics by status category
     */
    public function getStatistics() {
        $stmt = $this->db->query("
            SELECT 
                status_category,
                COUNT(*) as total,
                SUM(CASE WHEN is_active = 1 THEN 1 ELSE 0 END) as active,
                SUM(CASE WHEN expiry_date < CURDATE() THEN 1 ELSE 0 END) as expired
            FROM {$this->table}
            WHERE deleted_at IS NULL
            GROUP BY status_category
            ORDER BY total DESC
        ");
        
        $stats = [];
        while ($row = $stmt->fetch()) {
            $stats[$row['status_category']] = $row;
        }
        
        return $stats;
    }
}