<?php
namespace App\Models;

use App\Core\Model;

class TravelHistory extends Model {
    protected $table = 'travel_history';
    protected $primaryKey = 'id';
    protected $fillable = [
        'person_id', 'traveled_on_own_free_will', 'purpose_of_travel',
        'departure_date', 'arrival_date', 'departure_country',
        'departure_state_province', 'departure_town', 'transportation_type',
        'travel_document_type', 'port_of_entry', 'created_by', 'updated_by'
    ];
    
    /**
     * Get travel history 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 departure_date DESC
        ");
        $stmt->execute([$person_id]);
        return $stmt->fetchAll();
    }
    
    /**
     * Get recent travel by person
     */
    public function getRecentByPerson($person_id, $limit = 5) {
        $stmt = $this->db->prepare("
            SELECT * FROM {$this->table} 
            WHERE person_id = ? AND deleted_at IS NULL
            ORDER BY departure_date DESC
            LIMIT ?
        ");
        $stmt->execute([$person_id, $limit]);
        return $stmt->fetchAll();
    }
    
    /**
     * Get travel statistics by purpose
     */
    public function getStatsByPurpose() {
        $stmt = $this->db->query("
            SELECT 
                purpose_of_travel,
                COUNT(*) as count
            FROM {$this->table}
            WHERE deleted_at IS NULL
            GROUP BY purpose_of_travel
            ORDER BY count DESC
        ");
        
        $stats = [];
        while ($row = $stmt->fetch()) {
            $stats[$row['purpose_of_travel']] = $row['count'];
        }
        
        return $stats;
    }
    
    /**
     * Get travel statistics by country
     */
    public function getStatsByCountry() {
        $stmt = $this->db->query("
            SELECT 
                departure_country,
                COUNT(*) as count
            FROM {$this->table}
            WHERE deleted_at IS NULL
            GROUP BY departure_country
            ORDER BY count DESC
            LIMIT 20
        ");
        
        return $stmt->fetchAll();
    }
    
    /**
     * Search travel history
     */
    public function search($params) {
        $sql = "SELECT t.*, b.surname, b.given_name, b.document_no 
                FROM {$this->table} t
                JOIN biographical_information b ON t.person_id = b.person_id
                WHERE t.deleted_at IS NULL";
        $conditions = [];
        $values = [];
        
        if (!empty($params['person_id'])) {
            $conditions[] = "t.person_id = ?";
            $values[] = $params['person_id'];
        }
        
        if (!empty($params['purpose'])) {
            $conditions[] = "t.purpose_of_travel = ?";
            $values[] = $params['purpose'];
        }
        
        if (!empty($params['country'])) {
            $conditions[] = "t.departure_country LIKE ?";
            $values[] = '%' . $params['country'] . '%';
        }
        
        if (!empty($params['date_from'])) {
            $conditions[] = "t.departure_date >= ?";
            $values[] = $params['date_from'];
        }
        
        if (!empty($params['date_to'])) {
            $conditions[] = "t.arrival_date <= ?";
            $values[] = $params['date_to'];
        }
        
        if (!empty($conditions)) {
            $sql .= " AND " . implode(' AND ', $conditions);
        }
        
        $sql .= " ORDER BY t.departure_date DESC LIMIT 100";
        
        $stmt = $this->db->prepare($sql);
        $stmt->execute($values);
        
        return $stmt->fetchAll();
    }
}