<?php
namespace App\Models;

use App\Core\Model;

class Migrant extends Model {
    protected $table = 'biographical_information';
    protected $primaryKey = 'person_id';
    
    public function generatePersonId() {
        $year = date('Y');
        $prefix = 'NIS-MIG-' . $year . '-';
        
        $stmt = $this->db->prepare("
            SELECT MAX(CAST(SUBSTRING(person_id, LENGTH(?) + 1) AS UNSIGNED)) as max_num
            FROM {$this->table}
            WHERE person_id LIKE ? AND deleted_at IS NULL
        ");
        $stmt->execute([$prefix, $prefix . '%']);
        $result = $stmt->fetch();
        
        $next_num = ($result['max_num'] ?? 0) + 1;
        
        return $prefix . str_pad($next_num, 6, '0', STR_PAD_LEFT);
    }
    
    public function create($data) {
        try {
            $columns = implode(', ', array_keys($data));
            $placeholders = ':' . implode(', :', array_keys($data));
            
            $sql = "INSERT INTO {$this->table} ({$columns}) VALUES ({$placeholders})";
            $stmt = $this->db->prepare($sql);
            
            $result = $stmt->execute($data);
            
            if ($result) {
                // For tables with auto-increment ID, return the inserted record
                if (isset($data['person_id'])) {
                    return $this->find($data['person_id']);
                }
                return true;
            }
            
            return false;
        } catch (\PDOException $e) {
            error_log("PDO Error in create(): " . $e->getMessage());
            error_log("SQL: " . ($sql ?? 'No SQL'));
            error_log("Data: " . print_r($data, true));
            throw $e;
        }
    }
    
    /**
     * Get complete profile with all related data including new sections
     */
    public function getFullProfile($person_id) {
        $data = $this->find($person_id);
        if (!$data) return null;
        
        // Get foreign address
        $stmt = $this->db->prepare("SELECT * FROM foreign_address WHERE person_id = ? AND deleted_at IS NULL");
        $stmt->execute([$person_id]);
        $data['foreign_address'] = $stmt->fetchAll();
        
        // Get nigeria residence
        $stmt = $this->db->prepare("SELECT * FROM nigeria_residence WHERE person_id = ? AND deleted_at IS NULL");
        $stmt->execute([$person_id]);
        $data['nigeria_residence'] = $stmt->fetchAll();
        
        // Get employment history
        $stmt = $this->db->prepare("SELECT * FROM employment_history WHERE person_id = ? AND deleted_at IS NULL ORDER BY start_date DESC");
        $stmt->execute([$person_id]);
        $data['employment'] = $stmt->fetchAll();
        
        // Get immigrant status
        $stmt = $this->db->prepare("SELECT * FROM immigrant_status WHERE person_id = ? AND deleted_at IS NULL ORDER BY date_granted DESC");
        $stmt->execute([$person_id]);
        $data['immigrant_status'] = $stmt->fetchAll();
        
        // Get travel history
        $stmt = $this->db->prepare("SELECT * FROM travel_history WHERE person_id = ? AND deleted_at IS NULL ORDER BY departure_date DESC");
        $stmt->execute([$person_id]);
        $data['travel_history'] = $stmt->fetchAll();
        
        // Get medical history
        $stmt = $this->db->prepare("SELECT * FROM medical_history WHERE person_id = ? AND deleted_at IS NULL");
        $stmt->execute([$person_id]);
        $data['medical_history'] = $stmt->fetchAll();
        
        // Get documents
        $stmt = $this->db->prepare("SELECT * FROM uploaded_documents WHERE person_id = ? AND deleted_at IS NULL");
        $stmt->execute([$person_id]);
        $data['documents'] = $stmt->fetchAll();
        
        // Get biometrics
        $stmt = $this->db->prepare("SELECT * FROM biometrics WHERE person_id = ? ORDER BY capture_date DESC");
        $stmt->execute([$person_id]);
        $data['biometrics'] = $stmt->fetchAll();
        
        // Get fingerprints (encrypted)
        if (!empty($data['biometrics'])) {
            $fingerprints = [];
            foreach ($data['biometrics'] as $bio) {
                $stmt = $this->db->prepare("SELECT * FROM fingerprints WHERE biometrics_id = ? ORDER BY finger_position");
                $stmt->execute([$bio['id']]);
                $fingerprints = array_merge($fingerprints, $stmt->fetchAll());
            }
            $data['fingerprints'] = $fingerprints;
        } else {
            $data['fingerprints'] = [];
        }
        
        // Get current status
        $statusModel = new ApplicationStatus();
        $data['current_status'] = $statusModel->getCurrentStatus($person_id);
        $data['status_history'] = $statusModel->getStatusHistory($person_id);
        
        // Get dependants for this migrant
        $data['dependants'] = $this->getDependants($person_id);
        
        return $data;
    }
    
    /**
     * Get complete profile with all sections (alias for getFullProfile)
     */
    public function getCompleteProfile($person_id) {
        return $this->getFullProfile($person_id);
    }
    
    /**
     * Get biometrics for this migrant
     */
    public function getBiometrics($person_id) {
        $stmt = $this->db->prepare("SELECT * FROM biometrics WHERE person_id = ? ORDER BY capture_date DESC");
        $stmt->execute([$person_id]);
        return $stmt->fetchAll();
    }
    
    /**
     * Get fingerprints for this migrant
     */
    public function getFingerprints($person_id) {
        $stmt = $this->db->prepare("
            SELECT f.* FROM fingerprints f
            JOIN biometrics b ON f.biometrics_id = b.id
            WHERE b.person_id = ?
            ORDER BY b.capture_date DESC, f.finger_position
        ");
        $stmt->execute([$person_id]);
        return $stmt->fetchAll();
    }
    
    /**
     * Get immigrant status for this migrant
     */
    public function getImmigrantStatus($person_id) {
        $stmt = $this->db->prepare("SELECT * FROM immigrant_status WHERE person_id = ? AND deleted_at IS NULL ORDER BY date_granted DESC");
        $stmt->execute([$person_id]);
        return $stmt->fetchAll();
    }
    
    /**
     * Get current immigrant status
     */
    public function getCurrentImmigrantStatus($person_id) {
        $stmt = $this->db->prepare("
            SELECT * FROM immigrant_status 
            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 travel history for this migrant
     */
    public function getTravelHistory($person_id) {
        $stmt = $this->db->prepare("SELECT * FROM travel_history WHERE person_id = ? AND deleted_at IS NULL ORDER BY departure_date DESC");
        $stmt->execute([$person_id]);
        return $stmt->fetchAll();
    }
    
    /**
     * Check if migrant is approved
     */
    public function isApproved($person_id) {
        $statusModel = new ApplicationStatus();
        $current = $statusModel->getCurrentStatus($person_id);
        return $current && $current['status'] === ApplicationStatus::STATUS_APPROVED;
    }
    
    /**
     * Check if migrant is rejected
     */
    public function isRejected($person_id) {
        $statusModel = new ApplicationStatus();
        $current = $statusModel->getCurrentStatus($person_id);
        return $current && $current['status'] === ApplicationStatus::STATUS_REJECTED;
    }
    
    /**
     * Check if migrant has complete biometrics
     */
    public function hasCompleteBiometrics($person_id) {
        $fingerprintModel = new Fingerprint();
        $result = $fingerprintModel->hasCompleteSet($person_id);
        return $result['complete'];
    }
    
    /**
     * Get approved migrants
     */
    public function getApprovedMigrants($limit = 100) {
        $sql = "
            SELECT b.*, a.status, a.updated_at as approved_date
            FROM {$this->table} b
            JOIN application_status a ON b.person_id = a.person_id
            WHERE a.status = 'Approved'
            AND a.created_at = (
                SELECT MAX(created_at)
                FROM application_status a2
                WHERE a2.person_id = b.person_id
            )
            AND b.deleted_at IS NULL
            ORDER BY a.updated_at DESC
            LIMIT ?
        ";
        $stmt = $this->db->prepare($sql);
        $stmt->execute([$limit]);
        return $stmt->fetchAll();
    }
    
    /**
     * Get rejected migrants
     */
    public function getRejectedMigrants($limit = 100) {
        $sql = "
            SELECT b.*, a.status, a.remarks as rejection_reason, a.updated_at as rejected_date
            FROM {$this->table} b
            JOIN application_status a ON b.person_id = a.person_id
            WHERE a.status = 'Rejected'
            AND a.created_at = (
                SELECT MAX(created_at)
                FROM application_status a2
                WHERE a2.person_id = b.person_id
            )
            AND b.deleted_at IS NULL
            ORDER BY a.updated_at DESC
            LIMIT ?
        ";
        $stmt = $this->db->prepare($sql);
        $stmt->execute([$limit]);
        return $stmt->fetchAll();
    }
    
    /**
     * Get migrants by status
     */
    public function getByStatus($status, $limit = 100) {
        $sql = "
            SELECT b.*, a.status, a.remarks, a.updated_at as status_date
            FROM {$this->table} b
            JOIN application_status a ON b.person_id = a.person_id
            WHERE a.status = ?
            AND a.created_at = (
                SELECT MAX(created_at)
                FROM application_status a2
                WHERE a2.person_id = b.person_id
            )
            AND b.deleted_at IS NULL
            ORDER BY a.updated_at DESC
            LIMIT ?
        ";
        $stmt = $this->db->prepare($sql);
        $stmt->execute([$status, $limit]);
        return $stmt->fetchAll();
    }
    
    public function search($params) {
        $sql = "SELECT * FROM {$this->table} WHERE deleted_at IS NULL";
        $conditions = [];
        $values = [];
        
        if (!empty($params['surname'])) {
            $conditions[] = "surname LIKE ?";
            $values[] = '%' . $params['surname'] . '%';
        }
        
        if (!empty($params['document_no'])) {
            $conditions[] = "document_no = ?";
            $values[] = $params['document_no'];
        }
        
        if (!empty($params['person_id'])) {
            $conditions[] = "person_id LIKE ?";
            $values[] = '%' . $params['person_id'] . '%';
        }
        
        if (!empty($params['status'])) {
            // This requires a subquery to get current status
            $sql = "SELECT b.*, a.status 
                    FROM {$this->table} b
                    LEFT JOIN application_status a ON b.person_id = a.person_id
                    WHERE b.deleted_at IS NULL";
            
            if (!empty($params['status'])) {
                $conditions[] = "a.status = ?";
                $values[] = $params['status'];
            }
        }
        
        if (!empty($conditions)) {
            $sql .= " AND " . implode(' AND ', $conditions);
        }
        
        $sql .= " ORDER BY created_at DESC LIMIT 100";
        
        $stmt = $this->db->prepare($sql);
        $stmt->execute($values);
        
        return $stmt->fetchAll();
    }
    
    public function getStatistics() {
        $stats = [];
        
        // Total
        $stmt = $this->db->query("SELECT COUNT(*) as total FROM {$this->table} WHERE deleted_at IS NULL");
        $stats['total'] = $stmt->fetch()['total'];
        
        // By nationality
        $stmt = $this->db->query("
            SELECT nationality, COUNT(*) as count 
            FROM {$this->table} 
            WHERE deleted_at IS NULL 
            GROUP BY nationality 
            ORDER BY count DESC 
            LIMIT 10
        ");
        $stats['by_nationality'] = $stmt->fetchAll();
        
        // By status
        $statusModel = new ApplicationStatus();
        $stats['by_status'] = $statusModel->getStatusStats();
        
        // With biometrics
        $stmt = $this->db->query("
            SELECT COUNT(DISTINCT b.person_id) as count
            FROM {$this->table} b
            JOIN biometrics bi ON b.person_id = bi.person_id
            WHERE b.deleted_at IS NULL
        ");
        $stats['with_biometrics'] = $stmt->fetch()['count'];
        
        return $stats;
    }
    
    // =============================================
    // DEPENDANT MANAGEMENT METHODS - FIXED
    // =============================================
    
    /**
     * Get dependants for this migrant
     */
    public function getDependants($person_id) {
        // Check if table exists first
        try {
            $tableCheck = $this->db->query("SHOW TABLES LIKE 'dependants'");
            if ($tableCheck->rowCount() == 0) {
                return [];
            }
            
            $stmt = $this->db->prepare("
                SELECT * FROM dependants 
                WHERE person_id = ? AND deleted_at IS NULL 
                ORDER BY created_at DESC
            ");
            $stmt->execute([$person_id]);
            return $stmt->fetchAll();
        } catch (\Exception $e) {
            error_log("Error getting dependants: " . $e->getMessage());
            return [];
        }
    }
    
    /**
     * Add a dependant to this migrant
     */
    public function addDependant($data) {
        try {
            $sql = "INSERT INTO dependants (person_id, surname, given_name, date_of_birth, gender, document_no, relationship)
                    VALUES (:person_id, :surname, :given_name, :date_of_birth, :gender, :document_no, :relationship)";
            
            $stmt = $this->db->prepare($sql);
            $result = $stmt->execute([
                'person_id' => $data['person_id'],
                'surname' => strtoupper($data['surname']),
                'given_name' => strtoupper($data['given_name']),
                'date_of_birth' => $data['date_of_birth'],
                'gender' => ucfirst($data['gender']),
                'document_no' => $data['document_no'] ?? null,
                'relationship' => $data['relationship']
            ]);
            
            if ($result) {
                return $this->db->lastInsertId();
            }
            return false;
        } catch (\PDOException $e) {
            error_log("Error adding dependant: " . $e->getMessage());
            throw $e;
        }
    }
    
    /**
     * Update a dependant
     */
    public function updateDependant($id, $data) {
        try {
            $sql = "UPDATE dependants 
                    SET surname = :surname, given_name = :given_name, date_of_birth = :date_of_birth,
                        gender = :gender, document_no = :document_no, relationship = :relationship,
                        updated_at = NOW()
                    WHERE id = :id AND deleted_at IS NULL";
            
            $stmt = $this->db->prepare($sql);
            return $stmt->execute([
                'id' => $id,
                'surname' => strtoupper($data['surname']),
                'given_name' => strtoupper($data['given_name']),
                'date_of_birth' => $data['date_of_birth'],
                'gender' => ucfirst($data['gender']),
                'document_no' => $data['document_no'] ?? null,
                'relationship' => $data['relationship']
            ]);
        } catch (\PDOException $e) {
            error_log("Error updating dependant: " . $e->getMessage());
            throw $e;
        }
    }
    
    /**
     * Soft delete a dependant
     */
    public function deleteDependant($id) {
        try {
            $sql = "UPDATE dependants SET deleted_at = NOW() WHERE id = ?";
            $stmt = $this->db->prepare($sql);
            return $stmt->execute([$id]);
        } catch (\PDOException $e) {
            error_log("Error deleting dependant: " . $e->getMessage());
            throw $e;
        }
    }
    
    /**
     * Get a specific dependant by ID
     */
    public function getDependant($id) {
        try {
            $stmt = $this->db->prepare("SELECT * FROM dependants WHERE id = ? AND deleted_at IS NULL");
            $stmt->execute([$id]);
            return $stmt->fetch();
        } catch (\Exception $e) {
            error_log("Error getting dependant: " . $e->getMessage());
            return null;
        }
    }
    
    /**
     * Count dependants for a migrant
     */
    public function countDependants($person_id) {
        try {
            $stmt = $this->db->prepare("SELECT COUNT(*) as total FROM dependants WHERE person_id = ? AND deleted_at IS NULL");
            $stmt->execute([$person_id]);
            $result = $stmt->fetch();
            return $result['total'] ?? 0;
        } catch (\Exception $e) {
            error_log("Error counting dependants: " . $e->getMessage());
            return 0;
        }
    }
}