<?php
/**
 * ============================================================================
 * extension/includes/ExtensionSystem.php
 * Base Extension System Class - OOP Structure
 * ============================================================================
 */

class ExtensionSystem {
    protected $db;
    protected $userId;
    protected $userAssignment;
    protected $userRole;
    protected $userLevel;
    protected $permissions;
    
    public function __construct($database, $userId) {
        // Accept either Database object or PDO directly
        if ($database instanceof PDO) {
            $this->db = $database;
        } elseif (is_object($database) && method_exists($database, 'getConnection')) {
            $this->db = $database->getConnection();
        } else {
            throw new Exception("Invalid database parameter. Expected Database object or PDO instance.");
        }
        
        $this->userId = $userId;
        $this->loadUserAssignment();
    }
    
    /**
     * Load current user's extension assignment
     */
    private function loadUserAssignment() {
        $sql = "SELECT ea.*, er.role_name, er.role_level, er.permissions, er.organization_type
                FROM extension_assignments ea
                INNER JOIN extension_roles er ON ea.role_id = er.id
                WHERE ea.user_id = ? AND ea.is_active = 1
                ORDER BY ea.created_at DESC
                LIMIT 1";
        
        $stmt = $this->db->prepare($sql);
        $stmt->execute([$this->userId]);
        $this->userAssignment = $stmt->fetch(PDO::FETCH_ASSOC);
        
        if ($this->userAssignment) {
            $this->userRole = $this->userAssignment['role_name'];
            $this->userLevel = $this->userAssignment['assigned_level'];
            $this->permissions = json_decode($this->userAssignment['permissions'], true) ?? [];
        }
    }
    
    /**
     * Check if user has extension assignment
     */
    public function hasAssignment() {
        return !empty($this->userAssignment);
    }
    
    /**
     * Get user's assignment level
     */
    public function getUserLevel() {
        return $this->userLevel ?? null;
    }
    
    /**
     * Get user's role
     */
    public function getUserRole() {
        return $this->userRole ?? null;
    }
    
    /**
     * Get role information
     */
    public function getRoleInfo() {
        if (!$this->hasAssignment()) {
            return [
                'role_name' => 'Not Assigned',
                'role_level' => null,
                'organization_type' => null,
                'permissions' => []
            ];
        }
        
        return [
            'role_name' => $this->userAssignment['role_name'] ?? 'Unknown',
            'role_level' => $this->userAssignment['role_level'] ?? null,
            'organization_type' => $this->userAssignment['organization_type'] ?? null,
            'permissions' => $this->permissions
        ];
    }
    
    /**
     * Check if user has specific permission
     */
    public function hasPermission($permission) {
        if (!$this->hasAssignment()) {
            return false;
        }
        
        // Check for full access
        if (isset($this->permissions['manage_all']) && $this->permissions['manage_all']) {
            return true;
        }
        
        return isset($this->permissions[$permission]) && $this->permissions[$permission];
    }
    
    /**
     * Get subordinates (extension workers below current level)
     */
    public function getSubordinates() {
        if (!$this->hasAssignment()) {
            return [];
        }
        
        $levelHierarchy = [
            'national' => ['regional', 'district', 'subcounty', 'parish', 'village'],
            'regional' => ['district', 'subcounty', 'parish', 'village'],
            'district' => ['subcounty', 'parish', 'village'],
            'subcounty' => ['parish', 'village'],
            'parish' => ['village'],
            'village' => []
        ];
        
        $subordinateLevels = $levelHierarchy[$this->userLevel] ?? [];
        
        if (empty($subordinateLevels)) {
            return [];
        }
        
        // Build query based on current level and location
        $whereConditions = ["ea.assigned_level IN ('" . implode("','", $subordinateLevels) . "')"];
        $params = [];
        
        // Add location filters
        if ($this->userLevel === 'regional' && $this->userAssignment['region_id']) {
            $whereConditions[] = "ea.region_id = ?";
            $params[] = $this->userAssignment['region_id'];
        } elseif ($this->userLevel === 'district' && $this->userAssignment['district_id']) {
            $whereConditions[] = "ea.district_id = ?";
            $params[] = $this->userAssignment['district_id'];
        } elseif ($this->userLevel === 'subcounty' && $this->userAssignment['subcounty_id']) {
            $whereConditions[] = "ea.subcounty_id = ?";
            $params[] = $this->userAssignment['subcounty_id'];
        } elseif ($this->userLevel === 'parish' && $this->userAssignment['parish_id']) {
            $whereConditions[] = "ea.parish_id = ?";
            $params[] = $this->userAssignment['parish_id'];
        }
        
        $sql = "SELECT u.id, u.full_name, u.phone_number, u.email,
                       ea.assigned_level, er.role_name, ea.organization_name,
                       r.region_name, d.district_name, s.subcounty_name, p.parish_name, v.village_name
                FROM extension_assignments ea
                INNER JOIN users u ON ea.user_id = u.id
                INNER JOIN extension_roles er ON ea.role_id = er.id
                LEFT JOIN regions r ON ea.region_id = r.id
                LEFT JOIN districts d ON ea.district_id = d.id
                LEFT JOIN subcounties s ON ea.subcounty_id = s.id
                LEFT JOIN parishes p ON ea.parish_id = p.id
                LEFT JOIN villages v ON ea.village_id = v.id
                WHERE " . implode(' AND ', $whereConditions) . " AND ea.is_active = 1
                ORDER BY ea.assigned_level, u.full_name";
        
        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
    
    /**
     * Get farmers in coverage area
     */
    public function getFarmersInCoverage($limit = null, $offset = 0) {
        if (!$this->hasAssignment()) {
            return [];
        }
        
        $whereConditions = ["u.user_type = 'farmer'"];
        $params = [];
        
        // Add location filters based on level
        if ($this->userLevel === 'national') {
            // National level sees all farmers
        } elseif ($this->userLevel === 'regional' && $this->userAssignment['region_id']) {
            $whereConditions[] = "u.region_id = ?";
            $params[] = $this->userAssignment['region_id'];
        } elseif ($this->userLevel === 'district' && $this->userAssignment['district_id']) {
            $whereConditions[] = "u.district_id = ?";
            $params[] = $this->userAssignment['district_id'];
        } elseif ($this->userLevel === 'subcounty' && $this->userAssignment['subcounty_id']) {
            $whereConditions[] = "u.subcounty_id = ?";
            $params[] = $this->userAssignment['subcounty_id'];
        } elseif ($this->userLevel === 'parish' && $this->userAssignment['parish_id']) {
            $whereConditions[] = "u.parish_id = ?";
            $params[] = $this->userAssignment['parish_id'];
        } elseif ($this->userLevel === 'village' && $this->userAssignment['village_id']) {
            $whereConditions[] = "u.village_id = ?";
            $params[] = $this->userAssignment['village_id'];
        }
        
        $limitClause = $limit ? "LIMIT ? OFFSET ?" : "";
        if ($limit) {
            $params[] = $limit;
            $params[] = $offset;
        }
        
        $sql = "SELECT u.id, u.full_name, u.phone_number, u.email,
                       r.region_name, d.district_name, s.subcounty_name, p.parish_name, v.village_name,
                       up.farm_size_acres, up.farming_type
                FROM users u
                LEFT JOIN user_profiles up ON u.id = up.user_id
                LEFT JOIN regions r ON u.region_id = r.id
                LEFT JOIN districts d ON u.district_id = d.id
                LEFT JOIN subcounties s ON u.subcounty_id = s.id
                LEFT JOIN parishes p ON u.parish_id = p.id
                LEFT JOIN villages v ON u.village_id = v.id
                WHERE " . implode(' AND ', $whereConditions) . "
                ORDER BY u.full_name
                $limitClause";
        
        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
    
    /**
     * Count farmers in coverage area
     */
    public function countFarmersInCoverage() {
        if (!$this->hasAssignment()) {
            return 0;
        }
        
        $whereConditions = ["user_type = 'farmer'"];
        $params = [];
        
        if ($this->userLevel === 'regional' && $this->userAssignment['region_id']) {
            $whereConditions[] = "region_id = ?";
            $params[] = $this->userAssignment['region_id'];
        } elseif ($this->userLevel === 'district' && $this->userAssignment['district_id']) {
            $whereConditions[] = "district_id = ?";
            $params[] = $this->userAssignment['district_id'];
        } elseif ($this->userLevel === 'subcounty' && $this->userAssignment['subcounty_id']) {
            $whereConditions[] = "subcounty_id = ?";
            $params[] = $this->userAssignment['subcounty_id'];
        } elseif ($this->userLevel === 'parish' && $this->userAssignment['parish_id']) {
            $whereConditions[] = "parish_id = ?";
            $params[] = $this->userAssignment['parish_id'];
        } elseif ($this->userLevel === 'village' && $this->userAssignment['village_id']) {
            $whereConditions[] = "village_id = ?";
            $params[] = $this->userAssignment['village_id'];
        }
        
        $sql = "SELECT COUNT(*) as total FROM users WHERE " . implode(' AND ', $whereConditions);
        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        $result = $stmt->fetch(PDO::FETCH_ASSOC);
        return $result['total'] ?? 0;
    }
    
    /**
     * Get dashboard statistics
     */
    public function getDashboardStats() {
        if (!$this->hasAssignment()) {
            return [
                'total_farmers' => 0,
                'subordinates_count' => 0,
                'services_this_month' => 0,
                'pending_reports' => 0,
                'active_resources' => 0
            ];
        }
        
        return [
            'total_farmers' => $this->countFarmersInCoverage(),
            'subordinates_count' => count($this->getSubordinates()),
            'services_this_month' => $this->countServicesThisMonth(),
            'pending_reports' => $this->countPendingReports(),
            'active_resources' => $this->countActiveResources()
        ];
    }
    
    private function countServicesThisMonth() {
        $sql = "SELECT COUNT(*) as total FROM extension_services 
                WHERE officer_id = ? 
                AND MONTH(scheduled_date) = MONTH(CURRENT_DATE)
                AND YEAR(scheduled_date) = YEAR(CURRENT_DATE)";
        $stmt = $this->db->prepare($sql);
        $stmt->execute([$this->userId]);
        $result = $stmt->fetch(PDO::FETCH_ASSOC);
        return $result['total'] ?? 0;
    }
    
    private function countPendingReports() {
        if (!$this->hasPermission('view_reports') && !$this->hasPermission('approve_reports')) {
            return 0;
        }
        
        $sql = "SELECT COUNT(*) as total FROM extension_reports er
                INNER JOIN extension_assignments ea ON er.assignment_id = ea.id
                WHERE er.status IN ('submitted', 'draft')";
        
        // Add filters based on level
        $params = [];
        if ($this->userLevel === 'regional' && $this->userAssignment['region_id']) {
            $sql .= " AND ea.region_id = ?";
            $params[] = $this->userAssignment['region_id'];
        } elseif ($this->userLevel === 'district' && $this->userAssignment['district_id']) {
            $sql .= " AND ea.district_id = ?";
            $params[] = $this->userAssignment['district_id'];
        }
        
        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        $result = $stmt->fetch(PDO::FETCH_ASSOC);
        return $result['total'] ?? 0;
    }
    
    private function countActiveResources() {
        $sql = "SELECT COUNT(*) as total FROM extension_resources 
                WHERE is_published = 1";
        $stmt = $this->db->prepare($sql);
        $stmt->execute();
        $result = $stmt->fetch(PDO::FETCH_ASSOC);
        return $result['total'] ?? 0;
    }
    
    /**
     * Get location name based on level
     */
    public function getCoverageName() {
        if (!$this->hasAssignment()) {
            return 'Not Assigned';
        }
        
        switch ($this->userLevel) {
            case 'national':
                return 'National Level';
            case 'regional':
                return $this->getRegionName($this->userAssignment['region_id']);
            case 'district':
                return $this->getDistrictName($this->userAssignment['district_id']);
            case 'subcounty':
                return $this->getSubcountyName($this->userAssignment['subcounty_id']);
            case 'parish':
                return $this->getParishName($this->userAssignment['parish_id']);
            case 'village':
                return $this->getVillageName($this->userAssignment['village_id']);
            default:
                return 'Unknown';
        }
    }
    
    private function getRegionName($id) {
        if (!$id) return '';
        $stmt = $this->db->prepare("SELECT region_name FROM regions WHERE id = ?");
        $stmt->execute([$id]);
        $result = $stmt->fetch(PDO::FETCH_ASSOC);
        return $result['region_name'] ?? '';
    }
    
    private function getDistrictName($id) {
        if (!$id) return '';
        $stmt = $this->db->prepare("SELECT district_name FROM districts WHERE id = ?");
        $stmt->execute([$id]);
        $result = $stmt->fetch(PDO::FETCH_ASSOC);
        return $result['district_name'] ?? '';
    }
    
    private function getSubcountyName($id) {
        if (!$id) return '';
        $stmt = $this->db->prepare("SELECT subcounty_name FROM subcounties WHERE id = ?");
        $stmt->execute([$id]);
        $result = $stmt->fetch(PDO::FETCH_ASSOC);
        return $result['subcounty_name'] ?? '';
    }
    
    private function getParishName($id) {
        if (!$id) return '';
        $stmt = $this->db->prepare("SELECT parish_name FROM parishes WHERE id = ?");
        $stmt->execute([$id]);
        $result = $stmt->fetch(PDO::FETCH_ASSOC);
        return $result['parish_name'] ?? '';
    }
    
    private function getVillageName($id) {
        if (!$id) return '';
        $stmt = $this->db->prepare("SELECT village_name FROM villages WHERE id = ?");
        $stmt->execute([$id]);
        $result = $stmt->fetch(PDO::FETCH_ASSOC);
        return $result['village_name'] ?? '';
    }
}

/**
 * Helper function to get avatar URL (if not already defined)
 */
if (!function_exists('getAvatarUrl')) {
    function getAvatarUrl($profilePicture = null) {
        if ($profilePicture === null && isset($_SESSION['profile_picture'])) {
            $profilePicture = $_SESSION['profile_picture'];
        }
        
        if (!empty($profilePicture) && file_exists(UPLOAD_PATH . 'profiles/' . $profilePicture)) {
            return APP_URL . '/uploads/profiles/' . $profilePicture;
        }
        
        // Return default avatar
        return APP_URL . '/assets/images/default-avatar.png';
    }
}