<?php
/**
 * Pension Service
 * Handles all pension-related operations
 * Kenyan Pension & Life Insurance Management System
 * 
 * @package Classes
 * @author Senior Fintech Engineer
 */

class PensionService {
    private $conn;
    
    /**
     * Constructor
     * 
     * @param mysqli $conn Database connection
     */
    public function __construct($conn) {
        $this->conn = $conn;
    }
    
    /**
     * Create a new pension scheme
     * 
     * @param array $schemeData Scheme data
     * @return array Result with scheme_id or error
     */
    public function createScheme($schemeData) {
        try {
            // Validate required fields
            $required = ['scheme_code', 'scheme_name', 'scheme_type', 'retirement_age'];
            foreach ($required as $field) {
                if (empty($schemeData[$field])) {
                    return ['success' => false, 'message' => "Field {$field} is required"];
                }
            }
            
            // Start transaction
            $this->conn->begin_transaction();
            
            // Insert scheme
            $sql = "INSERT INTO pension_schemes (
                scheme_code, scheme_name, scheme_type, retirement_age,
                early_exit_max_cash_percentage, early_exit_balance_action,
                retirement_commutation_percentage, retirement_balance_action,
                disability_full_withdrawal_allowed, emigration_full_withdrawal_allowed,
                investment_return_allocation_frequency, trust_deed_reference,
                rba_registration_number, is_active
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
            
            $stmt = $this->conn->prepare($sql);
            $stmt->bind_param(
                "sssidsdsiisssi",
                $schemeData['scheme_code'],
                $schemeData['scheme_name'],
                $schemeData['scheme_type'],
                $schemeData['retirement_age'],
                $schemeData['early_exit_max_cash_percentage'] ?? 50.00,
                $schemeData['early_exit_balance_action'] ?? 'DEFER',
                $schemeData['retirement_commutation_percentage'] ?? null,
                $schemeData['retirement_balance_action'] ?? 'ANNUITY',
                $schemeData['disability_full_withdrawal_allowed'] ?? 1,
                $schemeData['emigration_full_withdrawal_allowed'] ?? 1,
                $schemeData['investment_return_allocation_frequency'] ?? 'QUARTERLY',
                $schemeData['trust_deed_reference'] ?? null,
                $schemeData['rba_registration_number'] ?? null,
                $schemeData['is_active'] ?? 1
            );
            
            if (!$stmt->execute()) {
                throw new Exception("Failed to create scheme: " . $stmt->error);
            }
            
            $scheme_id = $this->conn->insert_id;
            
            // Commit transaction
            $this->conn->commit();
            
            // Log activity
            $this->logActivity($scheme_id, 'SCHEME_CREATED', 'FINANCIAL', $scheme_id, null, $schemeData);
            
            return [
                'success' => true,
                'message' => 'Scheme created successfully',
                'scheme_id' => $scheme_id
            ];
            
        } catch (Exception $e) {
            $this->conn->rollback();
            error_log("PensionService::createScheme Error: " . $e->getMessage());
            return [
                'success' => false,
                'message' => 'Failed to create scheme: ' . $e->getMessage()
            ];
        }
    }
    
    /**
     * Get scheme by ID
     * 
     * @param int $scheme_id Scheme ID
     * @return array|null Scheme data or null
     */
    public function getScheme($scheme_id) {
        $sql = "SELECT * FROM pension_schemes WHERE scheme_id = ?";
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param("i", $scheme_id);
        $stmt->execute();
        $result = $stmt->get_result();
        
        return $result->fetch_assoc();
    }
    
    /**
     * Get all schemes with filters
     * 
     * @param array $filters Search filters
     * @return array Schemes list
     */
    public function getSchemes($filters = []) {
        $where = ["is_active = 1"];
        $params = [];
        $types = '';
        
        if (!empty($filters['scheme_type'])) {
            $where[] = "scheme_type = ?";
            $params[] = $filters['scheme_type'];
            $types .= 's';
        }
        
        if (!empty($filters['search'])) {
            $where[] = "(scheme_name LIKE ? OR scheme_code LIKE ?)";
            $searchTerm = '%' . $filters['search'] . '%';
            $params[] = $searchTerm;
            $params[] = $searchTerm;
            $types .= 'ss';
        }
        
        $whereClause = implode(' AND ', $where);
        
        $sql = "SELECT ps.*, 
                       COUNT(DISTINCT m.member_id) as total_members,
                       COUNT(DISTINCT e.employer_id) as total_employers
                FROM pension_schemes ps
                LEFT JOIN members m ON ps.scheme_id = m.scheme_id AND m.is_active = 1
                LEFT JOIN employers e ON ps.scheme_id = e.scheme_id AND e.is_active = 1
                WHERE $whereClause
                GROUP BY ps.scheme_id
                ORDER BY ps.created_at DESC";
        
        if (!empty($params)) {
            $stmt = $this->conn->prepare($sql);
            $stmt->bind_param($types, ...$params);
            $stmt->execute();
            $result = $stmt->get_result();
        } else {
            $result = $this->conn->query($sql);
        }
        
        $schemes = [];
        while ($row = $result->fetch_assoc()) {
            $schemes[] = $row;
        }
        
        return $schemes;
    }
    
    /**
     * Post contribution
     * 
     * @param array $contributionData Contribution data
     * @return array Result
     */
    public function postContribution($contributionData) {
        try {
            // Validate required fields
            $required = ['member_id', 'account_id', 'contribution_type', 'contribution_period', 'contribution_amount'];
            foreach ($required as $field) {
                if (empty($contributionData[$field])) {
                    return ['success' => false, 'message' => "Field {$field} is required"];
                }
            }
            
            // Start transaction
            $this->conn->begin_transaction();
            
            // Insert contribution
            $sql = "INSERT INTO contributions (
                member_id, account_id, contribution_type, contribution_period,
                contribution_amount, employee_contribution, employer_contribution,
                status, reference_number, payment_method
            ) VALUES (?, ?, ?, ?, ?, ?, ?, 'PENDING', ?, ?)";
            
            $stmt = $this->conn->prepare($sql);
            $stmt->bind_param(
                "iissddddss",
                $contributionData['member_id'],
                $contributionData['account_id'],
                $contributionData['contribution_type'],
                $contributionData['contribution_period'],
                $contributionData['contribution_amount'],
                $contributionData['employee_contribution'] ?? null,
                $contributionData['employer_contribution'] ?? null,
                $contributionData['reference_number'] ?? null,
                $contributionData['payment_method'] ?? null
            );
            
            if (!$stmt->execute()) {
                throw new Exception("Failed to post contribution: " . $stmt->error);
            }
            
            $contribution_id = $this->conn->insert_id;
            
            // Process contribution (update account and mark as processed)
            $this->processContribution($contribution_id);
            
            // Commit transaction
            $this->conn->commit();
            
            // Log activity
            $this->logActivity($contributionData['member_id'], 'CONTRIBUTION_POSTED', 'CONTRIBUTION', $contribution_id, null, $contributionData);
            
            return [
                'success' => true,
                'message' => 'Contribution posted successfully',
                'contribution_id' => $contribution_id
            ];
            
        } catch (Exception $e) {
            $this->conn->rollback();
            error_log("PensionService::postContribution Error: " . $e->getMessage());
            return [
                'success' => false,
                'message' => 'Failed to post contribution: ' . $e->getMessage()
            ];
        }
    }
    
    /**
     * Process contribution (update account balances)
     * 
     * @param int $contribution_id Contribution ID
     */
    /**
     * Process contribution and optionally generate eTIMS invoice
     */
    private function processContribution($contribution_id) {
        // Get contribution details
        $sql = "SELECT * FROM contributions WHERE contribution_id = ?";
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param("i", $contribution_id);
        $stmt->execute();
        $contribution = $stmt->get_result()->fetch_assoc();
        
        if (!$contribution) {
            return;
        }
        
        // Update member account
        if ($contribution['contribution_type'] === 'EMPLOYEE') {
            $updateSql = "UPDATE member_accounts 
                          SET total_employee_contributions = total_employee_contributions + ?,
                              updated_at = CURRENT_TIMESTAMP
                          WHERE account_id = ?";
            $amount = $contribution['contribution_amount'];
        } else {
            $updateSql = "UPDATE member_accounts 
                          SET total_employer_contributions = total_employer_contributions + ?,
                              updated_at = CURRENT_TIMESTAMP
                          WHERE account_id = ?";
            $amount = $contribution['contribution_amount'];
        }
        
        $updateStmt = $this->conn->prepare($updateSql);
        $updateStmt->bind_param("di", $amount, $contribution['account_id']);
        $updateStmt->execute();
        
        // Mark contribution as processed
        $processSql = "UPDATE contributions 
                       SET status = 'PROCESSED', processed_at = CURRENT_TIMESTAMP 
                       WHERE contribution_id = ?";
        $processStmt = $this->conn->prepare($processSql);
        $processStmt->bind_param("i", $contribution_id);
        $processStmt->execute();
        
        // Recalculate accrued benefits
        $this->recalculateAccruedBenefits($contribution['member_id']);
        
        // Optionally generate eTIMS invoice (if enabled in settings)
        $etimsEnabled = $this->conn->query("SELECT setting_value FROM system_settings WHERE setting_key = 'etims_auto_generate'")->fetch_assoc();
        if ($etimsEnabled && $etimsEnabled['setting_value'] === '1') {
            try {
                require_once __DIR__ . '/ETIMSService.php';
                $etimsService = new ETIMSService($this->conn);
                $etimsService->generateContributionInvoice($contribution_id);
            } catch (Exception $e) {
                // Log error but don't fail contribution processing
                error_log("eTIMS invoice generation failed: " . $e->getMessage());
            }
        }
    }
    
    /**
     * Recalculate accrued benefits for a member
     * 
     * @param int $member_id Member ID
     */
    public function recalculateAccruedBenefits($member_id) {
        // Call stored procedure
        $sql = "CALL sp_calculate_accrued_benefits(?, CURDATE())";
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param("i", $member_id);
        $stmt->execute();
        $stmt->close();
    }
    
    /**
     * Allocate fund returns to members
     * 
     * @param array $returnData Fund return data
     * @return array Result
     */
    public function allocateFundReturns($returnData) {
        try {
            // Validate required fields
            $required = ['scheme_id', 'return_period_start', 'return_period_end', 'return_percentage', 'return_amount'];
            foreach ($required as $field) {
                if (empty($returnData[$field])) {
                    return ['success' => false, 'message' => "Field {$field} is required"];
                }
            }
            
            // Start transaction
            $this->conn->begin_transaction();
            
            // Insert fund return record
            $sql = "INSERT INTO fund_returns (
                scheme_id, return_period_start, return_period_end,
                return_percentage, return_amount, allocation_frequency,
                allocation_date, status
            ) VALUES (?, ?, ?, ?, ?, ?, CURDATE(), 'PENDING')";
            
            $stmt = $this->conn->prepare($sql);
            $stmt->bind_param(
                "issddss",
                $returnData['scheme_id'],
                $returnData['return_period_start'],
                $returnData['return_period_end'],
                $returnData['return_percentage'],
                $returnData['return_amount'],
                $returnData['allocation_frequency'] ?? 'QUARTERLY'
            );
            
            if (!$stmt->execute()) {
                throw new Exception("Failed to create fund return: " . $stmt->error);
            }
            
            $return_id = $this->conn->insert_id;
            
            // Allocate returns to individual members
            $this->allocateReturnsToMembers($return_id, $returnData);
            
            // Mark as allocated
            $updateSql = "UPDATE fund_returns SET status = 'ALLOCATED', allocated_at = CURRENT_TIMESTAMP WHERE return_id = ?";
            $updateStmt = $this->conn->prepare($updateSql);
            $updateStmt->bind_param("i", $return_id);
            $updateStmt->execute();
            
            // Commit transaction
            $this->conn->commit();
            
            return [
                'success' => true,
                'message' => 'Fund returns allocated successfully',
                'return_id' => $return_id
            ];
            
        } catch (Exception $e) {
            $this->conn->rollback();
            error_log("PensionService::allocateFundReturns Error: " . $e->getMessage());
            return [
                'success' => false,
                'message' => 'Failed to allocate fund returns: ' . $e->getMessage()
            ];
        }
    }
    
    /**
     * Allocate returns to individual members
     * 
     * @param int $return_id Fund return ID
     * @param array $returnData Return data
     */
    private function allocateReturnsToMembers($return_id, $returnData) {
        // Get all active members in the scheme
        $sql = "SELECT m.member_id, ma.account_id, ma.accrued_benefits
                FROM members m
                INNER JOIN member_accounts ma ON m.member_id = ma.member_id
                WHERE m.scheme_id = ? AND m.is_active = 1 AND ma.account_status = 'ACTIVE'";
        
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param("i", $returnData['scheme_id']);
        $stmt->execute();
        $result = $stmt->get_result();
        
        $totalAccrued = 0;
        $members = [];
        
        while ($row = $result->fetch_assoc()) {
            $members[] = $row;
            $totalAccrued += $row['accrued_benefits'];
        }
        
        if ($totalAccrued == 0) {
            return; // No members to allocate to
        }
        
        // Allocate returns proportionally based on accrued benefits
        foreach ($members as $member) {
            $allocationPercentage = ($member['accrued_benefits'] / $totalAccrued) * 100;
            $allocationAmount = ($returnData['return_amount'] * $allocationPercentage) / 100;
            
            // Insert member fund return allocation
            $allocSql = "INSERT INTO member_fund_returns (
                return_id, member_id, account_id, allocation_amount, allocation_percentage
            ) VALUES (?, ?, ?, ?, ?)";
            
            $allocStmt = $this->conn->prepare($allocSql);
            $allocStmt->bind_param("iiidd", $return_id, $member['member_id'], $member['account_id'], $allocationAmount, $allocationPercentage);
            $allocStmt->execute();
            
            // Recalculate accrued benefits for this member
            $this->recalculateAccruedBenefits($member['member_id']);
        }
    }
    
    /**
     * Log activity
     */
    private function logActivity($user_id, $event_type, $event_category, $entity_id, $old_value, $new_value) {
        $sql = "INSERT INTO audit_logs (
            event_type, event_category, member_id, event_description,
            old_value, new_value, performed_by, ip_address, created_at
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP)";
        
        $stmt = $this->conn->prepare($sql);
        $event_description = "Pension operation: $event_type";
        $performed_by = $_SESSION['username'] ?? 'SYSTEM';
        $ip_address = $_SERVER['REMOTE_ADDR'] ?? null;
        
        $old_json = $old_value ? json_encode($old_value) : null;
        $new_json = $new_value ? json_encode($new_value) : null;
        
        $stmt->bind_param("ssisssss", 
            $event_type, $event_category, $entity_id, $event_description,
            $old_json, $new_json, $performed_by, $ip_address
        );
        
        $stmt->execute();
    }
}
