<?php
// dashboard-api.php - COMPLETE MEMBER DASHBOARD API
// ============================================
// INITIALIZATION
// ============================================

// Error handling configuration
error_reporting(E_ALL);
ini_set('display_errors', 0); // Don't display errors to users

// Load CORS configuration FIRST
require_once __DIR__ . '/includes/cors.php';
configureCors();

// Load environment variables
require_once __DIR__ . '/includes/config_loader.php';

// Load dependencies
require_once __DIR__ . '/includes/config.php';
require_once __DIR__ . '/includes/auth_functions.php'; // Your existing auth functions
require_once __DIR__ . '/includes/sms_helper.php';
require_once __DIR__ . '/includes/email_helper.php'; // If you have separate email helper
require_once __DIR__ . '/auth/AuditLogger.php';
require_once __DIR__ . '/auth/JWTManager.php';

// Set response headers
header('Content-Type: application/json');
header('Cache-Control: no-cache, no-store, must-revalidate');
header('Pragma: no-cache');
header('Expires: 0');

// Add security headers
header('X-Frame-Options: DENY');
header('X-Content-Type-Options: nosniff');
header('X-XSS-Protection: 1; mode=block');
header('Referrer-Policy: strict-origin-when-cross-origin');

// Handle preflight OPTIONS request
if ($_SERVER['REQUEST_METHOD'] === 'OPTIONS') {
    header('Access-Control-Max-Age: 86400');
    header('Content-Length: 0');
    http_response_code(200);
    exit();
}

// Start session
if (session_status() === PHP_SESSION_NONE) {
    session_name('MEMBER_PORTAL_SESSION');
    session_set_cookie_params([
        'lifetime' => 0,
        'path' => '/',
        'domain' => $_SERVER['HTTP_HOST'],
        'secure' => true,
        'httponly' => true,
        'samesite' => 'Lax'
    ]);
    
    session_start();
    
    // Regenerate ID periodically
    if (empty($_SESSION['session_regenerated'])) {
        session_regenerate_id(true);
        $_SESSION['session_regenerated'] = time();
    }
}

// Request deduplication
$requestId = uniqid('dash_', true);
$_SERVER['REQUEST_TIME_FLOAT'] = $_SERVER['REQUEST_TIME_FLOAT'] ?? microtime(true);

// Continue with the rest of the code...

// Initialize Audit Logger
try {
    $auditLogger = new AuditLogger($pdo, null, 'DashboardSystem');
} catch (Exception $e) {
    error_log("[$requestId] Audit Logger initialization failed: " . $e->getMessage());
    $auditLogger = null;
}

// Initialize JWT Manager
$jwtManager = null;
try {
    $jwtSecretKey = getenv('JWT_SECRET_KEY');
    if (!empty($jwtSecretKey) && strlen($jwtSecretKey) >= 32) {
        $jwtManager = new JWTManager($pdo);
    }
} catch (Exception $e) {
    error_log("[$requestId] JWT Manager initialization failed: " . $e->getMessage());
}

// ============================================
// HELPER FUNCTIONS
// ============================================

function sendResponse($data, $statusCode = 200) {
    http_response_code($statusCode);
    echo json_encode($data, JSON_PRETTY_PRINT | JSON_UNESCAPED_SLASHES);
    exit();
}

function getJsonInput() {
    $input = json_decode(file_get_contents('php://input'), true);
    if (json_last_error() !== JSON_ERROR_NONE) {
        return null;
    }
    return $input;
}

function safeArrayGet($array, $key, $default = null) {
    if (is_array($array) && array_key_exists($key, $array)) {
        return $array[$key];
    }
    return $default;
}

function validateDashboardRequest($requestId) {
    global $jwtManager, $auditLogger, $pdo;
    
    // Check authentication via multiple methods
    $authenticated = false;
    $memberId = null;
    $authType = 'unknown';
    
    // Method 1: Session authentication
    if (!empty($_SESSION['member_id']) && 
        !empty($_SESSION['authenticated']) && 
        $_SESSION['authenticated'] === true) {
        
        // Verify 2FA if required
        if (isset($_SESSION['2fa_enabled']) && $_SESSION['2fa_enabled'] === true) {
            if (empty($_SESSION['2fa_verified']) || $_SESSION['2fa_verified'] !== true) {
                error_log("[$requestId] 2FA not verified for session");
                return [false, null, '2fa_required'];
            }
        }
        
        $memberId = $_SESSION['member_id'];
        $authenticated = true;
        $authType = 'session';
        
        // Update last activity
        $_SESSION['last_activity'] = time();
        $_SESSION['last_api_request'] = time();
        
        error_log("[$requestId] Session authentication successful for member: $memberId");
    }
    
    // Method 2: JWT authentication
    if (!$authenticated && $jwtManager) {
        $authHeader = $_SERVER['HTTP_AUTHORIZATION'] ?? '';
        if (preg_match('/Bearer\s+(.*)/i', $authHeader, $matches)) {
            $token = $matches[1];
            
            try {
                $decoded = $jwtManager->validateAccessToken($token);
                if ($decoded && isset($decoded['mid'])) {
                    $memberId = $decoded['mid'];
                    $authenticated = true;
                    $authType = 'jwt';
                    
                    // Optional: Validate against stored token
                    $tokenValid = $jwtManager->validateStoredToken($memberId, $token, 'member');
                    if (!$tokenValid) {
                        error_log("[$requestId] Stored token validation failed");
                        $authenticated = false;
                        $memberId = null;
                    }
                    
                    error_log("[$requestId] JWT authentication successful for member: $memberId");
                }
            } catch (Exception $e) {
                error_log("[$requestId] JWT validation failed: " . $e->getMessage());
            }
        }
    }
    
    // Method 3: Hybrid authentication (session + JWT)
    if (!$authenticated && $jwtManager && isset($_COOKIE['refresh_token'])) {
        try {
            $refreshToken = $_COOKIE['refresh_token'];
            $newTokens = $jwtManager->refreshAccessToken($refreshToken);
            
            if ($newTokens && isset($newTokens['member_id'])) {
                $memberId = $newTokens['member_id'];
                $authenticated = true;
                $authType = 'hybrid';
                
                error_log("[$requestId] Hybrid authentication successful for member: $memberId");
            }
        } catch (Exception $e) {
            error_log("[$requestId] Hybrid authentication failed: " . $e->getMessage());
        }
    }
    
    if (!$authenticated) {
        if ($auditLogger) {
            $auditLogger->log(0, 'dashboard_unauthorized', [
                'ip' => $_SERVER['REMOTE_ADDR'] ?? 'unknown',
                'auth_type' => $authType,
                'request_id' => $requestId,
                'session_id' => session_id(),
                'user_agent' => $_SERVER['HTTP_USER_AGENT'] ?? 'unknown'
            ]);
        }
        
        return [false, null, 'unauthorized'];
    }
    
    // Check session timeout (30 minutes)
    if ($authType === 'session' || $authType === 'hybrid') {
        $lastActivity = $_SESSION['last_activity'] ?? 0;
        if ((time() - $lastActivity) > 1800) {
            error_log("[$requestId] Session expired for member: $memberId");
            session_destroy();
            
            if ($auditLogger) {
                $auditLogger->log($memberId, 'dashboard_session_expired', [
                    'ip' => $_SERVER['REMOTE_ADDR'] ?? 'unknown',
                    'request_id' => $requestId,
                    'session_age' => time() - $lastActivity
                ]);
            }
            
            return [false, null, 'session_expired'];
        }
    }
    
    // Verify member exists and is active
    try {
        $stmt = $pdo->prepare("
            SELECT a.member_id, a.username, a.email, a.status,
                   m.first_name, m.last_name, m.kyc_verified
            FROM member_portal_accounts a
            JOIN members m ON m.id = a.member_id
            WHERE a.member_id = ? AND a.status = 'active'
        ");
        $stmt->execute([$memberId]);
        $account = $stmt->fetch(PDO::FETCH_ASSOC);
        
        if (!$account) {
            error_log("[$requestId] Member account not found or inactive: $memberId");
            
            if ($auditLogger) {
                $auditLogger->log($memberId, 'dashboard_account_inactive', [
                    'ip' => $_SERVER['REMOTE_ADDR'] ?? 'unknown',
                    'request_id' => $requestId
                ]);
            }
            
            return [false, null, 'account_inactive'];
        }
        
        // Check KYC if required
        if (!$account['kyc_verified']) {
            error_log("[$requestId] KYC not verified for member: $memberId");
            return [false, null, 'kyc_required'];
        }
        
        // Log successful authentication
        if ($auditLogger) {
            $auditLogger->log($memberId, 'dashboard_authenticated', [
                'auth_type' => $authType,
                'ip' => $_SERVER['REMOTE_ADDR'] ?? 'unknown',
                'request_id' => $requestId,
                'session_id' => session_id()
            ]);
        }
        
        return [true, $memberId, $authType];
        
    } catch (Exception $e) {
        error_log("[$requestId] Account verification failed: " . $e->getMessage());
        return [false, null, 'verification_error'];
    }
}

function getDashboardMetrics($memberId, $requestId) {
    global $pdo;
    
    $metrics = [
        'total_savings' => 0.0,
        'total_loans' => 0.0,
        'total_balance' => 0.0,
        'account_count' => 0,
        'recent_transactions' => 0,
        'pending_requests' => 0,
        'monthly_savings' => 0.0,
        'monthly_withdrawals' => 0.0
    ];
    
    try {
        // Get account balances
        $stmt = $pdo->prepare("
            SELECT 
                SUM(CASE WHEN at.account_type = 'savings' THEN COALESCE(ma.available_balance, 0) ELSE 0 END) as total_savings,
                SUM(CASE WHEN at.account_type = 'loan' THEN COALESCE(ma.available_balance, 0) ELSE 0 END) as total_loans,
                COUNT(DISTINCT ma.id) as account_count
            FROM member_accounts ma
            JOIN account_types at ON ma.account_type_id = at.id
            WHERE ma.member_id = ? AND ma.status = 'active'
        ");
        $stmt->execute([$memberId]);
        $accountData = $stmt->fetch(PDO::FETCH_ASSOC);
        
        if ($accountData) {
            $metrics['total_savings'] = (float)($accountData['total_savings'] ?? 0);
            $metrics['total_loans'] = (float)($accountData['total_loans'] ?? 0);
            $metrics['total_balance'] = $metrics['total_savings'] - $metrics['total_loans'];
            $metrics['account_count'] = (int)($accountData['account_count'] ?? 0);
        }
        
        // Get recent transaction count (last 7 days)
        $stmt = $pdo->prepare("
            SELECT COUNT(*) as recent_count
            FROM transactions
            WHERE member_id = ? 
            AND transaction_date >= DATE_SUB(NOW(), INTERVAL 7 DAY)
            AND amount != 0
        ");
        $stmt->execute([$memberId]);
        $recentData = $stmt->fetch(PDO::FETCH_ASSOC);
        $metrics['recent_transactions'] = (int)($recentData['recent_count'] ?? 0);
        
        // Get pending requests count
        try {
            $stmt = $pdo->prepare("
                SELECT COUNT(*) as pending_count
                FROM member_requests
                WHERE member_id = ? 
                AND status = 'pending'
            ");
            $stmt->execute([$memberId]);
            $pendingData = $stmt->fetch(PDO::FETCH_ASSOC);
            $metrics['pending_requests'] = (int)($pendingData['pending_count'] ?? 0);
        } catch (Exception $e) {
            // Table might not exist
            error_log("[$requestId] Member requests table not accessible: " . $e->getMessage());
        }
        
        // Get monthly savings/deposits
        $currentMonth = date('Y-m');
        $stmt = $pdo->prepare("
            SELECT 
                SUM(CASE WHEN transaction_type = 'cr' THEN amount ELSE 0 END) as deposits,
                SUM(CASE WHEN transaction_type = 'dr' THEN amount ELSE 0 END) as withdrawals
            FROM transactions t
            JOIN account_types at ON t.account_type_id = at.id
            WHERE t.member_id = ?
            AND at.account_type = 'savings'
            AND DATE_FORMAT(t.transaction_date, '%Y-%m') = ?
        ");
        $stmt->execute([$memberId, $currentMonth]);
        $monthlyData = $stmt->fetch(PDO::FETCH_ASSOC);
        
        $metrics['monthly_savings'] = (float)($monthlyData['deposits'] ?? 0);
        $metrics['monthly_withdrawals'] = (float)($monthlyData['withdrawals'] ?? 0);
        
        error_log("[$requestId] Metrics calculated for member: $memberId");
        
        return $metrics;
        
    } catch (Exception $e) {
        error_log("[$requestId] Error calculating metrics: " . $e->getMessage());
        return $metrics;
    }
}

function getAccountsData($memberId, $requestId) {
    global $pdo;
    
    try {
        $stmt = $pdo->prepare("
            SELECT 
                ma.id,
                ma.account_number,
                ma.sub_account_code,
                ma.available_balance,
                ma.status,
                ma.created_at,
                ma.updated_at,
                ma.last_transaction_date,
                at.id as account_type_id,
                at.account_name,
                at.description,
                at.account_type,
                at.type_code
            FROM member_accounts ma
            JOIN account_types at ON ma.account_type_id = at.id
            WHERE ma.member_id = ? AND ma.status = 'active'
            ORDER BY at.account_type, ma.sub_account_code
        ");
        $stmt->execute([$memberId]);
        $accounts = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        // Organize by account type
        $organizedAccounts = [];
        foreach ($accounts as $account) {
            $typeId = $account['account_type_id'];
            
            if (!isset($organizedAccounts[$typeId])) {
                $organizedAccounts[$typeId] = [
                    'account_type_id' => $typeId,
                    'account_name' => $account['account_name'] ?? 'Unnamed Account',
                    'description' => $account['description'] ?? '',
                    'type' => $account['account_type'] ?? 'savings',
                    'type_code' => $account['type_code'] ?? 'SAV',
                    'sub_accounts' => []
                ];
            }
            
            // Determine account label based on sub_account_code
            $subAccountCode = (int)($account['sub_account_code'] ?? 0);
            $accountLabel = match($subAccountCode) {
                1 => 'Principal',
                2 => 'Interest',
                default => 'General'
            };
            
            // Get last updated timestamp
            $lastUpdated = $account['last_updated'] ?? $account['updated_at'] ?? $account['created_at'] ?? date('Y-m-d H:i:s');
            
            $organizedAccounts[$typeId]['sub_accounts'][$subAccountCode] = [
                'id' => (int)($account['id'] ?? 0),
                'account_number' => $account['account_number'] ?? 'N/A',
                'sub_account_code' => $subAccountCode,
                'available_balance' => isset($account['available_balance']) ? (float)$account['available_balance'] : 0.0,
                'account_label' => $accountLabel,
                'last_updated' => $lastUpdated,
                'status' => $account['status'] ?? 'active',
                'formatted_balance' => formatCurrency(isset($account['available_balance']) ? (float)$account['available_balance'] : 0.0)
            ];
        }
        
        // Convert to indexed array
        $result = array_values($organizedAccounts);
        
        error_log("[$requestId] Accounts data fetched for member: $memberId - Found " . count($result) . " account types");
        
        return $result;
        
    } catch (Exception $e) {
        error_log("[$requestId] Error fetching accounts: " . $e->getMessage());
        return [];
    }
}

function getRecentTransactions($memberId, $requestId, $limit = 10) {
    global $pdo;

    try {
        // -----------------------------
        // Normalize & validate inputs
        // -----------------------------
        $memberId = (int)$memberId;
        $limit    = max(5, min((int)$limit, 100)); // enforce 1–100 range

        error_log("[$requestId] Fetching recent transactions for member {$memberId} (limit: {$limit})");

        // -----------------------------
        // Prepare SQL
        // -----------------------------
        $stmt = $pdo->prepare("
            SELECT 
                t.id,
                t.transaction_number,
                t.description,
                t.amount,
                t.transaction_type,
                t.transaction_date,
                t.created_at,
                t.account_type_id,
                t.sub_account_code,
                at.account_name,
                at.type_code,
                u.username AS created_by_name
            FROM transactions t
            JOIN account_types at ON t.account_type_id = at.id
            LEFT JOIN users u ON t.created_by = u.id
            WHERE t.member_id = ? 
              AND t.amount != 0
            ORDER BY t.transaction_date DESC, t.created_at DESC
            LIMIT ?
        ");

        // -----------------------------
        // Bind parameters safely
        // -----------------------------
        $stmt->bindValue(1, $memberId, PDO::PARAM_INT);
        $stmt->bindValue(2, $limit, PDO::PARAM_INT);
        $stmt->execute();

        $transactions = $stmt->fetchAll(PDO::FETCH_ASSOC);

        error_log("[$requestId] Raw transactions fetched: " . count($transactions));

        // -----------------------------
        // Format transactions
        // -----------------------------
        $formattedTransactions = [];

        foreach ($transactions as $transaction) {
            $amount = (float)($transaction['amount'] ?? 0);

            $transactionType = strtolower($transaction['transaction_type'] ?? 'dr');
            $isCredit = in_array($transactionType, ['cr', 'credit'], true);

            $date = $transaction['transaction_date'] ?? null;
            $timestamp = $date ? strtotime($date) : time();

            $formattedTransactions[] = [
                'id'                  => (int)($transaction['id'] ?? 0),
                'transaction_number'  => $transaction['transaction_number'] ?? '',
                'description'         => $transaction['description'] ?? 'Transaction',
                'amount'              => $amount,
                'transaction_type'    => $transactionType,
                'transaction_date'    => $date,
                'account_name'        => $transaction['account_name'] ?? 'Unknown Account',
                'type_code'           => $transaction['type_code'] ?? 'GEN',
                'created_by'          => $transaction['created_by_name'] ?? 'System',
                'created_at'          => $transaction['created_at'] ?? null,
                'formatted_amount'    => formatCurrency($amount),
                'formatted_date'      => date('M j, Y', $timestamp),
                'formatted_time'      => date('H:i', $timestamp),
                'amount_class'        => $isCredit ? 'text-success' : 'text-danger',
                'type_badge'          => $isCredit ? 'success' : 'danger',
                'is_credit'           => $isCredit
            ];
        }

        error_log("[$requestId] Final formatted transactions count: " . count($formattedTransactions));

        return $formattedTransactions;

    } catch (Exception $e) {
        error_log("[$requestId] Error fetching transactions: " . $e->getMessage());
        return [];
    }
}




function getAccountStatement($memberId, $input, $requestId) {
    global $pdo;
    
    // Validate input
    $accountId = $input['account_id'] ?? null;
    $startDate = $input['start_date'] ?? date('Y-m-01');
    $endDate = $input['end_date'] ?? date('Y-m-t');
    $format = $input['format'] ?? 'json';
    
    if (!$accountId) {
        throw new Exception('Account ID is required');
    }
    
    // Validate dates
    $startTimestamp = strtotime($startDate);
    $endTimestamp = strtotime($endDate);
    
    if (!$startTimestamp || !$endTimestamp) {
        throw new Exception('Invalid date format. Use YYYY-MM-DD');
    }
    
    if ($startTimestamp > $endTimestamp) {
        throw new Exception('Start date cannot be after end date');
    }
    
    // Validate date range (max 365 days)
    $daysDifference = ($endTimestamp - $startTimestamp) / (60 * 60 * 24);
    if ($daysDifference > 365) {
        throw new Exception('Date range cannot exceed 365 days');
    }
    
    // Validate account belongs to member
    $stmt = $pdo->prepare("
        SELECT ma.*, at.account_name, at.type_code
        FROM member_accounts ma
        JOIN account_types at ON ma.account_type_id = at.id
        WHERE ma.id = ? AND ma.member_id = ? AND ma.status = 'active'
    ");
    $stmt->execute([$accountId, $memberId]);
    $account = $stmt->fetch(PDO::FETCH_ASSOC);
    
    if (!$account) {
        throw new Exception('Account not found or access denied');
    }
    
    // Get opening balance
    $openingBalance = getOpeningBalance($pdo, $account['account_type_id'], $account['sub_account_code'], $startDate, $memberId);
    
    // Process transactions
    $stmt = $pdo->prepare("
        SELECT 
            t.id,
            t.transaction_number,
            t.description,
            t.amount,
            t.transaction_type,
            t.transaction_date,
            t.created_at,
            at.type_code, 
            u.username as created_by_name
        FROM transactions t
        JOIN account_types at ON t.account_type_id = at.id
        LEFT JOIN users u ON t.created_by = u.id
        WHERE t.member_id = ? 
        AND t.account_type_id = ?
        AND t.sub_account_code = ?
        AND t.transaction_date BETWEEN ? AND ?
        AND t.amount != 0
        ORDER BY t.transaction_date, t.created_at
    ");
    $stmt->execute([$memberId, $account['account_type_id'], $account['sub_account_code'], $startDate, $endDate]);
    $transactions = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    $currentBalance = $openingBalance;
    $periodCredits = 0.0;
    $periodDebits = 0.0;
    $isLoanAccount = (strtolower($account['type_code'] ?? '') === 'loan');
    
    $processedTransactions = [];
    foreach ($transactions as $transaction) {
        $amount = (float)($transaction['amount'] ?? 0);
        $transactionType = $transaction['transaction_type'] ?? 'dr';
        
        // Record period totals
        if ($transactionType === 'cr') {
            $periodCredits += $amount;
        } else {
            $periodDebits += $amount;
        }
        
        // Calculate running balance
        $transaction['opening_balance'] = $currentBalance;
        
        if ($isLoanAccount) {
            // For loans: Debits increase balance, credits decrease
            $currentBalance += ($transactionType === 'dr') ? $amount : -$amount;
        } else {
            // For savings: Credits increase balance, debits decrease
            $currentBalance += ($transactionType === 'cr') ? $amount : -$amount;
        }
        
        $transaction['running_balance'] = $currentBalance;
        $transaction['formatted_date'] = date('d/m/Y', strtotime($transaction['transaction_date']));
        $transaction['formatted_amount'] = formatCurrency($amount);
        $transaction['formatted_running_balance'] = formatBalance($currentBalance, $isLoanAccount);
        
        $processedTransactions[] = $transaction;
    }
    
    $closingBalance = $currentBalance;
    $periodNet = $periodCredits - $periodDebits;
    
    // Get company info
    $companyStmt = $pdo->query("
        SELECT 
            company_name, 
            address as company_address, 
            phone_number as company_phone, 
            email as company_email,
            website as company_website,
            company_logo
        FROM company_settings 
        LIMIT 1
    ");
    $companyInfo = $companyStmt->fetch(PDO::FETCH_ASSOC);
    
    // Get member info
    $memberStmt = $pdo->prepare("
        SELECT 
            m.first_name, 
            m.last_name, 
            m.phone_number, 
            m.personal_number,
            m.email_address, 
            m.address,
            m.id_number
        FROM members m
        WHERE m.id = ?
    ");
    $memberStmt->execute([$memberId]);
    $memberInfo = $memberStmt->fetch(PDO::FETCH_ASSOC);
    
    // Generate statement data
    $statementData = [
        'statement_info' => [
            'statement_number' => 'ST-' . time() . '-' . $memberId,
            'generated_date' => date('Y-m-d H:i:s'),
            'generated_timestamp' => time(),
            'period_start' => $startDate,
            'period_end' => $endDate,
            'account_name' => $account['account_name'] ?? 'Account',
            'sub_account_label' => match((int)($account['sub_account_code'] ?? 0)) {
                1 => 'Principal',
                2 => 'Interest',
                default => 'Account'
            },
            'account_number' => $account['account_number'] ?? 'N/A'
        ],
        'company_info' => $companyInfo ?: [
            'company_name' => 'Creative SACCO',
            'company_address' => 'Nairobi, Kenya',
            'company_phone' => '+254 700 000 000',
            'company_email' => 'info@creativesacco.com',
            'company_website' => 'www.creativesacco.com'
        ],
        'member_info' => $memberInfo ?: [
            'first_name' => 'Member',
            'last_name' => 'Unknown',
            'phone_number' => 'N/A',
            'personal_number' => 'N/A',
            'email_address' => 'N/A',
            'address' => 'N/A'
        ],
        'account_info' => [
            'account_id' => (int)$accountId,
            'account_number' => $account['account_number'] ?? 'N/A',
            'sub_account_code' => (int)($account['sub_account_code'] ?? 0),
            'is_loan_account' => $isLoanAccount,
            'account_type' => $isLoanAccount ? 'Loan' : 'Savings'
        ],
        'summary' => [
            'opening_balance' => $openingBalance,
            'closing_balance' => $closingBalance,
            'period_credits' => $periodCredits,
            'period_debits' => $periodDebits,
            'period_net' => $periodNet,
            'transaction_count' => count($processedTransactions),
            'formatted_opening_balance' => formatBalance($openingBalance, $isLoanAccount),
            'formatted_closing_balance' => formatBalance($closingBalance, $isLoanAccount),
            'formatted_period_credits' => formatCurrency($periodCredits),
            'formatted_period_debits' => formatCurrency($periodDebits),
            'formatted_period_net' => formatCurrency($periodNet)
        ],
        'transactions' => $processedTransactions
    ];
    
    error_log("[$requestId] Statement generated for account: $accountId, transactions: " . count($processedTransactions));
    
    return $statementData;
}

function getOpeningBalance($pdo, $accountTypeId, $subAccountCode, $startDate, $memberId) {
    // Get base opening balance from account
    $stmt = $pdo->prepare("
        SELECT COALESCE(SUM(opening_balance), 0) as base_balance
        FROM member_accounts 
        WHERE account_type_id = ? 
        AND sub_account_code = ?
        AND member_id = ?
        AND status = 'active'
    ");
    $stmt->execute([$accountTypeId, $subAccountCode, $memberId]);
    $baseBalance = (float)$stmt->fetchColumn();
    
    // Get net effect from prior transactions
    $stmt = $pdo->prepare("
        SELECT 
            COALESCE(SUM(CASE WHEN transaction_type = 'cr' THEN amount ELSE 0 END), 0) AS total_credits,
            COALESCE(SUM(CASE WHEN transaction_type = 'dr' THEN amount ELSE 0 END), 0) AS total_debits
        FROM transactions 
        WHERE account_type_id = ? 
        AND sub_account_code = ?
        AND transaction_date < ?
        AND member_id = ?
        AND amount != 0
    ");
    $stmt->execute([$accountTypeId, $subAccountCode, $startDate, $memberId]);
    $priorTransactions = $stmt->fetch(PDO::FETCH_ASSOC);
    
    // Determine if this is a loan account
    $stmt = $pdo->prepare("SELECT type_code FROM account_types WHERE id = ?");
    $stmt->execute([$accountTypeId]);
    $accountType = $stmt->fetch(PDO::FETCH_ASSOC);
    $isLoanAccount = (strtolower($accountType['type_code'] ?? '') === 'loan');
    
    $netEffect = ($priorTransactions['total_credits'] ?? 0) - ($priorTransactions['total_debits'] ?? 0);
    
    return $isLoanAccount 
        ? $baseBalance + $netEffect
        : $baseBalance + $netEffect;
}

function getQuickStats($memberId, $requestId) {
    global $pdo;
    
    $stats = [
        'monthly_savings' => 0.0,
        'monthly_withdrawals' => 0.0,
        'monthly_loans' => 0.0,
        'monthly_repayments' => 0.0,
        'year_to_date_savings' => 0.0,
        'year_to_date_loans' => 0.0,
        'last_transaction_date' => null,
        'average_monthly_savings' => 0.0
    ];
    
    try {
        $currentMonth = date('Y-m');
        $currentYear = date('Y');
        
        // Monthly savings (deposits)
        $stmt = $pdo->prepare("
            SELECT COALESCE(SUM(amount), 0) as total
            FROM transactions t
            JOIN account_types at ON t.account_type_id = at.id
            WHERE t.member_id = ?
            AND at.account_type = 'savings'
            AND t.transaction_type = 'cr'
            AND DATE_FORMAT(t.transaction_date, '%Y-%m') = ?
        ");
        $stmt->execute([$memberId, $currentMonth]);
        $monthlySavings = $stmt->fetch(PDO::FETCH_ASSOC);
        $stats['monthly_savings'] = (float)$monthlySavings['total'];
        
        // Monthly withdrawals
        $stmt = $pdo->prepare("
            SELECT COALESCE(SUM(amount), 0) as total
            FROM transactions t
            JOIN account_types at ON t.account_type_id = at.id
            WHERE t.member_id = ?
            AND at.account_type = 'savings'
            AND t.transaction_type = 'dr'
            AND DATE_FORMAT(t.transaction_date, '%Y-%m') = ?
        ");
        $stmt->execute([$memberId, $currentMonth]);
        $monthlyWithdrawals = $stmt->fetch(PDO::FETCH_ASSOC);
        $stats['monthly_withdrawals'] = (float)$monthlyWithdrawals['total'];
        
        // Year to date savings
        $stmt = $pdo->prepare("
            SELECT COALESCE(SUM(amount), 0) as total
            FROM transactions t
            JOIN account_types at ON t.account_type_id = at.id
            WHERE t.member_id = ?
            AND at.account_type = 'savings'
            AND t.transaction_type = 'cr'
            AND YEAR(t.transaction_date) = ?
        ");
        $stmt->execute([$memberId, $currentYear]);
        $ytdSavings = $stmt->fetch(PDO::FETCH_ASSOC);
        $stats['year_to_date_savings'] = (float)$ytdSavings['total'];
        
        // Last transaction date
        $stmt = $pdo->prepare("
            SELECT MAX(transaction_date) as last_date
            FROM transactions
            WHERE member_id = ?
        ");
        $stmt->execute([$memberId]);
        $lastDate = $stmt->fetch(PDO::FETCH_ASSOC);
        $stats['last_transaction_date'] = $lastDate['last_date'];
        
        // Calculate average monthly savings (last 6 months)
        $stmt = $pdo->prepare("
            SELECT 
                DATE_FORMAT(transaction_date, '%Y-%m') as month,
                COALESCE(SUM(CASE WHEN transaction_type = 'cr' THEN amount ELSE 0 END), 0) as monthly_savings
            FROM transactions t
            JOIN account_types at ON t.account_type_id = at.id
            WHERE t.member_id = ?
            AND at.account_type = 'savings'
            AND t.transaction_date >= DATE_SUB(NOW(), INTERVAL 6 MONTH)
            GROUP BY DATE_FORMAT(transaction_date, '%Y-%m')
            ORDER BY month DESC
            LIMIT 6
        ");
        $stmt->execute([$memberId]);
        $monthlyAverages = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        $totalMonths = count($monthlyAverages);
        $totalSavings = 0;
        foreach ($monthlyAverages as $avg) {
            $totalSavings += (float)$avg['monthly_savings'];
        }
        
        $stats['average_monthly_savings'] = $totalMonths > 0 ? $totalSavings / $totalMonths : 0;
        
        return $stats;
        
    } catch (Exception $e) {
        error_log("[$requestId] Error fetching quick stats: " . $e->getMessage());
        return $stats;
    }
}

function getMemberInfo($memberId, $requestId) {
    global $pdo;
    
    try {
        $stmt = $pdo->prepare("
            SELECT 
                m.id,
                m.first_name,
                m.last_name,
                m.email_address,
                m.phone_number,
                m.personal_number,
                m.id_number,
                m.address,
                m.date_of_birth,
                m.gender,
                m.kyc_verified,
                m.kyc_verified_at,
                m.registration_date,
                m.status as member_status,
                a.username,
                a.email as portal_email,
                a.status as portal_status,
                a.last_login,
                ms.two_factor_enabled,
                ms.two_factor_method,
                m.communication_preference
            FROM members m
            LEFT JOIN member_portal_accounts a ON m.id = a.member_id
            LEFT JOIN member_settings ms ON m.id = ms.member_id
            WHERE m.id = ?
        ");
        $stmt->execute([$memberId]);
        $memberData = $stmt->fetch(PDO::FETCH_ASSOC);
        
        if (!$memberData) {
            return null;
        }
        
        // Format member info
        $memberInfo = [
            'basic_info' => [
                'member_id' => (int)$memberId,
                'full_name' => trim(($memberData['first_name'] ?? '') . ' ' . ($memberData['last_name'] ?? '')),
                'first_name' => $memberData['first_name'] ?? '',
                'last_name' => $memberData['last_name'] ?? '',
                'email' => $memberData['email_address'] ?? $memberData['portal_email'] ?? '',
                'phone' => $memberData['phone_number'] ?? '',
                'personal_number' => $memberData['personal_number'] ?? '',
                'id_number' => $memberData['id_number'] ?? '',
                'address' => $memberData['address'] ?? '',
                'date_of_birth' => $memberData['date_of_birth'] ?? null,
                'gender' => $memberData['gender'] ?? '',
                'registration_date' => $memberData['registration_date'] ?? null,
                'membership_years' => $memberData['registration_date'] ? 
                    floor((time() - strtotime($memberData['registration_date'])) / (365 * 24 * 60 * 60)) : 0
            ],
            'portal_info' => [
                'username' => $memberData['username'] ?? '',
                'portal_email' => $memberData['portal_email'] ?? '',
                'last_login' => $memberData['last_login'] ?? null,
                'portal_status' => $memberData['portal_status'] ?? 'inactive'
            ],
            'security_info' => [
                'kyc_verified' => (bool)($memberData['kyc_verified'] ?? false),
                'kyc_verified_at' => $memberData['kyc_verified_at'] ?? null,
                'two_factor_enabled' => (bool)($memberData['two_factor_enabled'] ?? false),
                'two_factor_method' => $memberData['two_factor_method'] ?? 'email',
                'communication_preference' => $memberData['communication_preference'] ?? 'email'
            ],
            'status_info' => [
                'member_status' => $memberData['member_status'] ?? 'inactive',
                'portal_status' => $memberData['portal_status'] ?? 'inactive',
                'is_active' => ($memberData['member_status'] ?? '') === 'active' && 
                              ($memberData['portal_status'] ?? '') === 'active'
            ]
        ];
        
        error_log("[$requestId] Member info fetched for ID: $memberId");
        
        return $memberInfo;
        
    } catch (Exception $e) {
        error_log("[$requestId] Error fetching member info: " . $e->getMessage());
        return null;
    }
}

function getCompanyInfo($requestId) {
    global $pdo;

    try {
        error_log("[$requestId] Fetching company info");

        $stmt = $pdo->query("
            SELECT 
                company_name,
                address,
                phone_number,
                email,
                website,
                company_logo,
                Cs_Number,
                currency
            FROM company_settings
            LIMIT 1
        ");

        $companyData = $stmt->fetch(PDO::FETCH_ASSOC);

        // Debug: no data found
        if (!$companyData) {
            error_log("[$requestId] No company settings found");
            throw new Exception("Company settings table empty");
        }

        error_log("[$requestId] Company info fetched successfully");
        return $companyData;

    } catch (Exception $e) {
        error_log("[$requestId] Error fetching company info: " . $e->getMessage());

        // Safe fallback data
        return [
            'company_name'  => 'Creative SACCO',
            'address'       => 'Nairobi, Kenya',
            'phone_number'  => '+254 700 000 000',
            'email'         => 'info@creativesacco.com',
            'website'       => 'www.creativesacco.com',
            'company_logo'  => null,
            'Cs_Number'     => null,
            'currency'      => 'KES'
        ];
    }
}


function formatCurrency($amount) {
    return number_format((float)$amount, 2, '.', ',');
}

function formatBalance($amount, $isLoanAccount = false) {
    $amount = (float)$amount;
    
    if ($amount == 0) return number_format($amount, 2);
    
    $formatted = number_format(abs($amount), 2);
    if (($isLoanAccount && $amount > 0) || (!$isLoanAccount && $amount < 0)) {
        return $formatted . ' Dr';
    } else {
        return $formatted . ' Cr';
    }
}

// ============================================
// API ENDPOINTS
// ============================================

// GET: Dashboard data
if ($_SERVER['REQUEST_METHOD'] === 'GET') {
    try {
        error_log("[$requestId] === DASHBOARD GET REQUEST START ===");
        
        // Validate authentication
        list($authenticated, $memberId, $authType) = validateDashboardRequest($requestId);
        
        if (!$authenticated) {
            error_log("[$requestId] Authentication failed: $authType");
            
            sendResponse([
                'success' => false,
                'message' => 'Authentication required',
                'data' => [
                    'auth_required' => true,
                    'auth_type' => $authType,
                    'redirect_to' => 'login.php'
                ],
                'meta' => [
                    'request_id' => $requestId,
                    'timestamp' => time(),
                    'status' => 'unauthorized'
                ]
            ], 401);
        }
        
        error_log("[$requestId] Member authenticated: $memberId, auth type: $authType");
        
        // Get query parameters
        $withMetrics = isset($_GET['with_metrics']) && $_GET['with_metrics'] === 'true';
        $withAccounts = isset($_GET['with_accounts']) && $_GET['with_accounts'] === 'true';
        $withTransactions = isset($_GET['with_transactions']) && $_GET['with_transactions'] === 'true';
        $withStats = isset($_GET['with_stats']) && $_GET['with_stats'] === 'true';
        $withMemberInfo = isset($_GET['with_member_info']) && $_GET['with_member_info'] === 'true';
        $withCompanyInfo = isset($_GET['with_company_info']) && $_GET['with_company_info'] === 'true';
        $limit = isset($_GET['limit']) ? min((int)$_GET['limit'], 100) : 10; // Max 100 transactions
        
        // If no specific parameters, get everything
        if (!$withMetrics && !$withAccounts && !$withTransactions && !$withStats && !$withMemberInfo && !$withCompanyInfo) {
            $withMetrics = $withAccounts = $withTransactions = $withStats = $withMemberInfo = $withCompanyInfo = true;
        }
        
        $responseData = [
            'member_id' => (int)$memberId,
            'auth_type' => $authType,
            'session_id' => session_id(),
            'timestamp' => date('Y-m-d H:i:s'),
            'server_time' => time()
        ];
        
        // Get metrics if requested
        if ($withMetrics) {
            $responseData['metrics'] = getDashboardMetrics($memberId, $requestId);
        }
        
        // Get accounts if requested
        if ($withAccounts) {
            $responseData['accounts'] = getAccountsData($memberId, $requestId);
        }
        
        // Get transactions if requested
        if ($withTransactions) {
            $responseData['recent_transactions'] = getRecentTransactions($memberId, $limit, $requestId);
        }
        
        // Get quick stats if requested
        if ($withStats) {
            $responseData['quick_stats'] = getQuickStats($memberId, $requestId);
        }
        
        // Get member info if requested
        if ($withMemberInfo) {
            $memberInfo = getMemberInfo($memberId, $requestId);
            if ($memberInfo) {
                $responseData['member_info'] = $memberInfo;
            }
        }
        
        // Get company info if requested
        if ($withCompanyInfo) {
            $responseData['company_info'] = getCompanyInfo($requestId);
        }
        
        // Log successful dashboard access
        if ($auditLogger) {
            $auditLogger->log($memberId, 'dashboard_accessed', [
                'auth_type' => $authType,
                'ip' => $_SERVER['REMOTE_ADDR'] ?? 'unknown',
                'request_id' => $requestId,
                'session_id' => session_id(),
                'endpoint' => 'dashboard_api',
                'data_requested' => [
                    'metrics' => $withMetrics,
                    'accounts' => $withAccounts,
                    'transactions' => $withTransactions,
                    'stats' => $withStats,
                    'member_info' => $withMemberInfo,
                    'company_info' => $withCompanyInfo
                ]
            ]);
        }
        
        $responseTime = microtime(true) - $_SERVER['REQUEST_TIME_FLOAT'];
        
        sendResponse([
            'success' => true,
            'message' => 'Dashboard data retrieved successfully',
            'data' => $responseData,
            'meta' => [
                'request_id' => $requestId,
                'timestamp' => time(),
                'api_version' => '1.0',
                'endpoint' => 'dashboard_summary',
                'response_time' => round($responseTime, 3),
                'memory_usage' => round(memory_get_usage() / 1024 / 1024, 2) . ' MB',
                'server' => gethostname()
            ]
        ]);
        
    } catch (Exception $e) {
        error_log("[$requestId] GET endpoint error: " . $e->getMessage());
        error_log("[$requestId] Stack trace: " . $e->getTraceAsString());
        
        sendResponse([
            'success' => false,
            'message' => 'Failed to load dashboard data',
            'error' => 'Internal server error',
            'error_details' => DEBUG ? $e->getMessage() : null,
            'meta' => [
                'request_id' => $requestId,
                'timestamp' => time(),
                'support_contact' => 'support@creativesacco.com'
            ]
        ], 500);
    }
}

// POST: Generate statement or specific data
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    try {
        error_log("[$requestId] === DASHBOARD POST REQUEST START ===");
        
        // Validate authentication
        list($authenticated, $memberId, $authType) = validateDashboardRequest($requestId);
        
        if (!$authenticated) {
            sendResponse([
                'success' => false,
                'message' => 'Authentication required',
                'data' => [
                    'auth_required' => true,
                    'auth_type' => $authType,
                    'redirect_to' => 'login.php'
                ],
                'meta' => [
                    'request_id' => $requestId,
                    'timestamp' => time(),
                    'status' => 'unauthorized'
                ]
            ], 401);
        }
        
        $input = getJsonInput();
        
        if (!$input) {
            sendResponse([
                'success' => false,
                'message' => 'Invalid JSON input',
                'meta' => [
                    'request_id' => $requestId,
                    'timestamp' => time()
                ]
            ], 400);
        }
        
        $action = $input['action'] ?? 'unknown';
        
        switch ($action) {
            case 'generate_statement':
                try {
                    $statementData = getAccountStatement($memberId, $input, $requestId);
                    
                    // Log statement generation
                    if ($auditLogger) {
                        $auditLogger->log($memberId, 'statement_generated', [
                            'account_id' => $input['account_id'] ?? null,
                            'start_date' => $input['start_date'] ?? null,
                            'end_date' => $input['end_date'] ?? null,
                            'format' => $input['format'] ?? 'json',
                            'ip' => $_SERVER['REMOTE_ADDR'] ?? 'unknown',
                            'request_id' => $requestId,
                            'transaction_count' => count($statementData['transactions'] ?? [])
                        ]);
                    }
                    
                    sendResponse([
                        'success' => true,
                        'message' => 'Statement generated successfully',
                        'data' => $statementData,
                        'meta' => [
                            'request_id' => $requestId,
                            'timestamp' => time(),
                            'action' => 'generate_statement',
                            'download_url' => 'download.php?type=statement&id=' . $requestId,
                            'statement_number' => $statementData['statement_info']['statement_number'] ?? ''
                        ]
                    ]);
                    
                } catch (Exception $e) {
                    error_log("[$requestId] Statement generation error: " . $e->getMessage());
                    
                    sendResponse([
                        'success' => false,
                        'message' => 'Failed to generate statement',
                        'error' => $e->getMessage(),
                        'meta' => [
                            'request_id' => $requestId,
                            'timestamp' => time()
                        ]
                    ], 400);
                }
                break;
                
            case 'refresh_data':
                // Refresh specific data sections
                $refreshSections = $input['sections'] ?? ['all'];
                
                $responseData = [
                    'member_id' => (int)$memberId,
                    'refreshed_at' => date('Y-m-d H:i:s'),
                    'refreshed_timestamp' => time()
                ];
                
                if (in_array('all', $refreshSections) || in_array('metrics', $refreshSections)) {
                    $responseData['metrics'] = getDashboardMetrics($memberId, $requestId);
                }
                
                if (in_array('all', $refreshSections) || in_array('accounts', $refreshSections)) {
                    $responseData['accounts'] = getAccountsData($memberId, $requestId);
                }
                
                if (in_array('all', $refreshSections) || in_array('transactions', $refreshSections)) {
                    $limit = isset($input['transaction_limit']) ? min((int)$input['transaction_limit'], 100) : 10;
                    $responseData['recent_transactions'] = getRecentTransactions($memberId, $limit, $requestId);
                }
                
                if (in_array('all', $refreshSections) || in_array('stats', $refreshSections)) {
                    $responseData['quick_stats'] = getQuickStats($memberId, $requestId);
                }
                
                if ($auditLogger) {
                    $auditLogger->log($memberId, 'dashboard_data_refreshed', [
                        'sections' => $refreshSections,
                        'ip' => $_SERVER['REMOTE_ADDR'] ?? 'unknown',
                        'request_id' => $requestId
                    ]);
                }
                
                sendResponse([
                    'success' => true,
                    'message' => 'Data refreshed successfully',
                    'data' => $responseData,
                    'meta' => [
                        'request_id' => $requestId,
                        'timestamp' => time(),
                        'action' => 'refresh_data',
                        'sections_refreshed' => $refreshSections
                    ]
                ]);
                break;
                
            case 'get_account_details':
                // Get detailed account information
                $accountId = $input['account_id'] ?? null;
                
                if (!$accountId) {
                    sendResponse([
                        'success' => false,
                        'message' => 'Account ID is required',
                        'meta' => [
                            'request_id' => $requestId,
                            'timestamp' => time()
                        ]
                    ], 400);
                }
                
                try {
                    $stmt = $pdo->prepare("
                        SELECT 
                            ma.*,
                            at.account_name,
                            at.description,
                            at.account_type,
                            at.type_code
                        FROM member_accounts ma
                        JOIN account_types at ON ma.account_type_id = at.id
                        WHERE ma.id = ? AND ma.member_id = ? AND ma.status = 'active'
                    ");
                    $stmt->execute([$accountId, $memberId]);
                    $accountDetails = $stmt->fetch(PDO::FETCH_ASSOC);
                    
                    if (!$accountDetails) {
                        sendResponse([
                            'success' => false,
                            'message' => 'Account not found or access denied',
                            'meta' => [
                                'request_id' => $requestId,
                                'timestamp' => time()
                            ]
                        ], 404);
                    }
                    
                    // Get recent transactions for this account
                    $stmt = $pdo->prepare("
                        SELECT 
                            t.*,
                            at.account_name
                        FROM transactions t
                        JOIN account_types at ON t.account_type_id = at.id
                        WHERE t.member_id = ?
                        AND t.account_type_id = ?
                        AND t.sub_account_code = ?
                        ORDER BY t.transaction_date DESC
                        LIMIT 20
                    ");
                    $stmt->execute([
                        $memberId,
                        $accountDetails['account_type_id'],
                        $accountDetails['sub_account_code']
                    ]);
                    $accountTransactions = $stmt->fetchAll(PDO::FETCH_ASSOC);
                    
                    // Format account details
                    $formattedDetails = [
                        'account_info' => [
                            'id' => (int)$accountDetails['id'],
                            'account_number' => $accountDetails['account_number'],
                            'account_name' => $accountDetails['account_name'],
                            'description' => $accountDetails['description'],
                            'account_type' => $accountDetails['account_type'],
                            'sub_account_code' => (int)$accountDetails['sub_account_code'],
                            'sub_account_label' => match((int)$accountDetails['sub_account_code']) {
                                1 => 'Principal',
                                2 => 'Interest',
                                default => 'General'
                            },
                            'available_balance' => (float)$accountDetails['available_balance'],
                            'opening_balance' => (float)$accountDetails['opening_balance'],
                            'status' => $accountDetails['status'],
                            'created_at' => $accountDetails['created_at'],
                            'last_updated' => $accountDetails['last_updated'] ?? $accountDetails['updated_at']
                        ],
                        'recent_transactions' => $accountTransactions,
                        'transaction_count' => count($accountTransactions)
                    ];
                    
                    sendResponse([
                        'success' => true,
                        'message' => 'Account details retrieved successfully',
                        'data' => $formattedDetails,
                        'meta' => [
                            'request_id' => $requestId,
                            'timestamp' => time(),
                            'action' => 'get_account_details'
                        ]
                    ]);
                    
                } catch (Exception $e) {
                    error_log("[$requestId] Account details error: " . $e->getMessage());
                    sendResponse([
                        'success' => false,
                        'message' => 'Failed to retrieve account details',
                        'error' => $e->getMessage(),
                        'meta' => [
                            'request_id' => $requestId,
                            'timestamp' => time()
                        ]
                    ], 500);
                }
                break;
                
            default:
                sendResponse([
                    'success' => false,
                    'message' => 'Invalid action specified',
                    'meta' => [
                        'request_id' => $requestId,
                        'timestamp' => time(),
                        'allowed_actions' => ['generate_statement', 'refresh_data', 'get_account_details']
                    ]
                ], 400);
        }
        
    } catch (Exception $e) {
        error_log("[$requestId] POST endpoint error: " . $e->getMessage());
        error_log("[$requestId] Stack trace: " . $e->getTraceAsString());
        
        sendResponse([
            'success' => false,
            'message' => 'Failed to process request',
            'error' => 'Internal server error',
            'error_details' => DEBUG ? $e->getMessage() : null,
            'meta' => [
                'request_id' => $requestId,
                'timestamp' => time(),
                'support_contact' => 'support@creativesacco.com'
            ]
        ], 500);
    }
}

// Invalid method
sendResponse([
    'success' => false,
    'message' => 'Method not allowed',
    'meta' => [
        'request_id' => $requestId,
        'timestamp' => time(),
        'allowed_methods' => ['GET', 'POST']
    ]
], 405);

error_log("[$requestId] === DASHBOARD REQUEST END ===");
error_log("[$requestId] Total execution time: " . round(microtime(true) - $_SERVER['REQUEST_TIME_FLOAT'], 3) . " seconds");
?>