<?php
namespace App\Models;

use App\Core\Model;

class ApplicationStatus extends Model {
    protected $table = 'application_status';
    protected $primaryKey = 'id';
    protected $fillable = [
        'person_id', 'status', 'remarks', 'reviewed_by', 'reviewed_at', 'created_by'
    ];
    
    const STATUS_DRAFT = 'Draft';
    const STATUS_SUBMITTED = 'Submitted';
    const STATUS_UNDER_REVIEW = 'Under Review';
    const STATUS_APPROVED = 'Approved';
    const STATUS_REJECTED = 'Rejected';
    
    /**
     * Override find method to avoid deleted_at column issues
     */
    public function find($id) {
        $stmt = $this->db->prepare("SELECT * FROM {$this->table} WHERE {$this->primaryKey} = ?");
        $stmt->execute([$id]);
        return $stmt->fetch();
    }
    
    /**
     * Get current status for a migrant
     */
    public function getCurrentStatus($person_id) {
        $stmt = $this->db->prepare("
            SELECT * FROM {$this->table} 
            WHERE person_id = ? 
            ORDER BY created_at DESC 
            LIMIT 1
        ");
        $stmt->execute([$person_id]);
        return $stmt->fetch();
    }
    
    /**
     * Get status history for a migrant
     */
    public function getStatusHistory($person_id) {
        $stmt = $this->db->prepare("
            SELECT s.*, u.full_name as reviewer_name
            FROM {$this->table} s
            LEFT JOIN users u ON s.reviewed_by = u.id
            WHERE s.person_id = ?
            ORDER BY s.created_at DESC
        ");
        $stmt->execute([$person_id]);
        return $stmt->fetchAll();
    }
    
    /**
     * Update application status with approval/rejection
     */
    public function updateStatus($person_id, $status, $remarks = null, $reviewed_by = null) {
        // Check workflow rules
        $current = $this->getCurrentStatus($person_id);
        
        // Validate workflow transition
        if (!$this->isValidTransition($current['status'] ?? null, $status)) {
            throw new \Exception("Invalid status transition from {$current['status']} to $status");
        }
        
        $data = [
            'person_id' => $person_id,
            'status' => $status,
            'remarks' => $remarks,
            'created_by' => $_SESSION['user_id'] ?? null
        ];
        
        if ($reviewed_by) {
            $data['reviewed_by'] = $reviewed_by;
            $data['reviewed_at'] = date('Y-m-d H:i:s');
        }
        
        return $this->create($data);
    }
    
    /**
     * Validate status transition
     */
    protected function isValidTransition($current, $new) {
        $allowed = [
            null => [self::STATUS_DRAFT],
            self::STATUS_DRAFT => [self::STATUS_SUBMITTED],
            self::STATUS_SUBMITTED => [self::STATUS_UNDER_REVIEW, self::STATUS_REJECTED],
            self::STATUS_UNDER_REVIEW => [self::STATUS_APPROVED, self::STATUS_REJECTED],
            self::STATUS_APPROVED => [],
            self::STATUS_REJECTED => [self::STATUS_DRAFT]
        ];
        
        return in_array($new, $allowed[$current] ?? []);
    }
    
    /**
     * Get statistics by status
     */
    public function getStatusStats() {
        $stmt = $this->db->query("
            SELECT 
                status,
                COUNT(*) as count
            FROM (
                SELECT person_id, status
                FROM {$this->table} a
                WHERE created_at = (
                    SELECT MAX(created_at)
                    FROM {$this->table} b
                    WHERE b.person_id = a.person_id
                )
            ) as latest
            GROUP BY status
        ");
        
        $stats = [];
        while ($row = $stmt->fetch()) {
            $stats[$row['status']] = $row['count'];
        }
        
        return $stats;
    }
    
    /**
     * Get pending applications (Submitted or Under Review)
     */
    public function getPendingApplications() {
        $stmt = $this->db->query("
            SELECT 
                a.*,
                b.surname,
                b.given_name,
                b.document_no,
                b.nationality,
                u.full_name as created_by_name
            FROM {$this->table} a
            JOIN biographical_information b ON a.person_id = b.person_id
            LEFT JOIN users u ON a.created_by = u.id
            WHERE a.status IN ('Submitted', 'Under Review')
            AND a.created_at = (
                SELECT MAX(created_at)
                FROM {$this->table} c
                WHERE c.person_id = a.person_id
            )
            ORDER BY a.created_at DESC
        ");
        
        return $stmt->fetchAll();
    }
    
    /**
     * Get approved applications
     */
    public function getApprovedApplications($limit = 50) {
        $stmt = $this->db->prepare("
            SELECT 
                a.*,
                b.surname,
                b.given_name,
                b.document_no,
                b.nationality
            FROM {$this->table} a
            JOIN biographical_information b ON a.person_id = b.person_id
            WHERE a.status = 'Approved'
            AND a.created_at = (
                SELECT MAX(created_at)
                FROM {$this->table} c
                WHERE c.person_id = a.person_id
            )
            ORDER BY a.created_at DESC
            LIMIT ?
        ");
        $stmt->execute([$limit]);
        return $stmt->fetchAll();
    }
    
    /**
     * Get rejected applications
     */
    public function getRejectedApplications($limit = 50) {
        $stmt = $this->db->prepare("
            SELECT 
                a.*,
                b.surname,
                b.given_name,
                b.document_no,
                b.nationality
            FROM {$this->table} a
            JOIN biographical_information b ON a.person_id = b.person_id
            WHERE a.status = 'Rejected'
            AND a.created_at = (
                SELECT MAX(created_at)
                FROM {$this->table} c
                WHERE c.person_id = a.person_id
            )
            ORDER BY a.created_at DESC
            LIMIT ?
        ");
        $stmt->execute([$limit]);
        return $stmt->fetchAll();
    }
}