<?php
/**
 * ITRPOS Financial Helpers
 * Contains functions for double-entry accounting system
 */

/**
 * Create a new journal entry
 * @param mysqli $conn Database connection
 * @param string $source_type Source type (sale, purchase, expense, etc.)
 * @param int $reference_id Reference ID from source table
 * @param string $description Description of the journal entry
 * @param int $store_id Store ID
 * @return int|false Journal entry ID or false on failure
 */
function createJournalEntry($conn, $source_type, $reference_id, $description, $store_id = 1) {
    try {
        // Generate unique entry number
        $entry_number = generateJournalEntryNumber($conn, $store_id);
        
        $stmt = $conn->prepare("
            INSERT INTO journal_entries (entry_number, source_type, reference_id, description, entry_date, store_id, status, created_at)
            VALUES (?, ?, ?, ?, CURDATE(), ?, 'draft', NOW())
        ");
        $stmt->bind_param("siisi", $entry_number, $source_type, $reference_id, $description, $store_id);
        
        if ($stmt->execute()) {
            $journal_entry_id = $stmt->insert_id;
            $stmt->close();
            return $journal_entry_id;
        } else {
            $stmt->close();
            return false;
        }
    } catch (Exception $e) {
        error_log("Error creating journal entry: " . $e->getMessage());
        return false;
    }
}

/**
 * Generate unique journal entry number
 * @param mysqli $conn Database connection
 * @param int $store_id Store ID
 * @return string Unique entry number
 */
function generateJournalEntryNumber($conn, $store_id = 1) {
    $prefix = 'JE';
    $year = date('Y');
    $month = date('m');
    
    // Get the last entry number for this month
    $stmt = $conn->prepare("
        SELECT entry_number 
        FROM journal_entries 
        WHERE store_id = ? AND entry_number LIKE ? 
        ORDER BY entry_number DESC 
        LIMIT 1
    ");
    $pattern = $prefix . $year . $month . '%';
    $stmt->bind_param("is", $store_id, $pattern);
    $stmt->execute();
    $result = $stmt->get_result();
    $stmt->close();
    
    if ($row = $result->fetch_assoc()) {
        $last_number = $row['entry_number'];
        $sequence = intval(substr($last_number, -4)) + 1;
    } else {
        $sequence = 1;
    }
    
    return $prefix . $year . $month . str_pad($sequence, 4, '0', STR_PAD_LEFT);
}

/**
 * Create a journal entry line
 * @param mysqli $conn Database connection
 * @param int $journal_entry_id Journal entry ID
 * @param int $account_id Account ID
 * @param float $debit_amount Debit amount
 * @param float $credit_amount Credit amount
 * @param string $description Line description
 * @return bool Success status
 */
function createJournalLine($conn, $journal_entry_id, $account_id, $debit_amount, $credit_amount, $description) {
    try {
        $stmt = $conn->prepare("
            INSERT INTO journal_entry_lines (journal_entry_id, account_id, debit_amount, credit_amount, description)
            VALUES (?, ?, ?, ?, ?)
        ");
        $stmt->bind_param("iidds", $journal_entry_id, $account_id, $debit_amount, $credit_amount, $description);
        
        $result = $stmt->execute();
        $stmt->close();
        return $result;
    } catch (Exception $e) {
        error_log("Error creating journal line: " . $e->getMessage());
        return false;
    }
}

/**
 * Post a journal entry (mark as posted)
 * @param mysqli $conn Database connection
 * @param int $journal_entry_id Journal entry ID
 * @return bool Success status
 */
function postJournalEntry($conn, $journal_entry_id) {
    try {
        // Verify the entry balances
        $stmt = $conn->prepare("
            SELECT 
                SUM(debit_amount) as total_debits,
                SUM(credit_amount) as total_credits
            FROM journal_entry_lines 
            WHERE journal_entry_id = ?
        ");
        $stmt->bind_param("i", $journal_entry_id);
        $stmt->execute();
        $result = $stmt->get_result();
        $row = $result->fetch_assoc();
        $stmt->close();
        
        // Check if debits equal credits
        if (abs($row['total_debits'] - $row['total_credits']) > 0.01) {
            error_log("Journal entry #$journal_entry_id does not balance. Debits: {$row['total_debits']}, Credits: {$row['total_credits']}");
            return false;
        }
        
        // Mark as posted
        $stmt = $conn->prepare("UPDATE journal_entries SET status = 'posted', posted_at = NOW() WHERE id = ?");
        $stmt->bind_param("i", $journal_entry_id);
        $result = $stmt->execute();
        $stmt->close();
        
        return $result;
    } catch (Exception $e) {
        error_log("Error posting journal entry: " . $e->getMessage());
        return false;
    }
}

/**
 * Get account ID by mapping code
 * @param mysqli $conn Database connection
 * @param string $mapping_code Mapping code (e.g., 'ast_Cash', 'rev_Sale')
 * @return int|false Account ID or false if not found
 */
function getAccountIdByMapping($conn, $mapping_code) {
    try {
        $stmt = $conn->prepare("
            SELECT coa.id 
            FROM chart_of_accounts coa
            JOIN account_mappings am ON coa.id = am.account_id
            WHERE am.mapping_code = ? AND coa.is_active = 1
        ");
        $stmt->bind_param("s", $mapping_code);
        $stmt->execute();
        $result = $stmt->get_result();
        $stmt->close();
        
        if ($row = $result->fetch_assoc()) {
            return $row['id'];
        }
        return false;
    } catch (Exception $e) {
        error_log("Error getting account ID by mapping: " . $e->getMessage());
        return false;
    }
}

/**
 * Get chart of account ID for expense account
 * @param mysqli $conn Database connection
 * @param int $expense_account_id Expense account ID
 * @return int|false Chart of account ID or false if not mapped
 */
function getExpenseAccountId($conn, $expense_account_id) {
    try {
        // Check if the mapping columns exist
        $result = mysqli_query($conn, "SHOW COLUMNS FROM expense_accounts LIKE 'chart_of_account_id'");
        if (!$result || mysqli_num_rows($result) == 0) {
            return false; // Mapping columns don't exist
        }
        
        $stmt = $conn->prepare("
            SELECT chart_of_account_id 
            FROM expense_accounts 
            WHERE id = ? AND chart_of_account_id IS NOT NULL
        ");
        $stmt->bind_param("i", $expense_account_id);
        $stmt->execute();
        $result = $stmt->get_result();
        $stmt->close();
        
        if ($row = $result->fetch_assoc()) {
            return $row['chart_of_account_id'];
        }
        return false;
    } catch (Exception $e) {
        error_log("Error getting expense account mapping: " . $e->getMessage());
        return false;
    }
}

/**
 * Get chart of account ID for bank account
 * @param mysqli $conn Database connection
 * @param int $bank_account_id Bank account ID
 * @return int|false Chart of account ID or false if not mapped
 */
function getBankAccountId($conn, $bank_account_id) {
    try {
        // Check if the mapping columns exist
        $result = mysqli_query($conn, "SHOW COLUMNS FROM bank_accounts LIKE 'chart_of_account_id'");
        if (!$result || mysqli_num_rows($result) == 0) {
            return false; // Mapping columns don't exist
        }
        
        $stmt = $conn->prepare("
            SELECT chart_of_account_id 
            FROM bank_accounts 
            WHERE id = ? AND chart_of_account_id IS NOT NULL
        ");
        $stmt->bind_param("i", $bank_account_id);
        $stmt->execute();
        $result = $stmt->get_result();
        $stmt->close();
        
        if ($row = $result->fetch_assoc()) {
            return $row['chart_of_account_id'];
        }
        return false;
    } catch (Exception $e) {
        error_log("Error getting bank account mapping: " . $e->getMessage());
        return false;
    }
}

/**
 * Get account balance
 * @param mysqli $conn Database connection
 * @param int $account_id Account ID
 * @param int $store_id Store ID
 * @return float Account balance
 */
function getAccountBalance($conn, $account_id, $store_id = 1) {
    try {
        $stmt = $conn->prepare("
            SELECT 
                coa.account_type,
                COALESCE(SUM(CASE WHEN coa.account_type IN ('asset', 'expense') 
                    THEN jel.debit_amount - jel.credit_amount 
                    ELSE jel.credit_amount - jel.debit_amount END), 0) as balance
            FROM chart_of_accounts coa
            LEFT JOIN journal_entry_lines jel ON coa.id = jel.account_id
            LEFT JOIN journal_entries je ON jel.journal_entry_id = je.id AND je.status = 'posted'
            WHERE coa.id = ? AND coa.store_id = ?
            GROUP BY coa.id, coa.account_type
        ");
        $stmt->bind_param("ii", $account_id, $store_id);
        $stmt->execute();
        $result = $stmt->get_result();
        $stmt->close();
        
        if ($row = $result->fetch_assoc()) {
            return $row['balance'];
        }
        return 0;
    } catch (Exception $e) {
        error_log("Error getting account balance: " . $e->getMessage());
        return 0;
    }
}

/**
 * Initialize default chart of accounts if not exists
 * @param mysqli $conn Database connection
 * @param int $store_id Store ID
 * @return bool Success status
 */
function initializeChartOfAccounts($conn, $store_id = 1) {
    try {
        // Check if accounts already exist
        $stmt = $conn->prepare("SELECT COUNT(*) as count FROM chart_of_accounts WHERE store_id = ?");
        $stmt->bind_param("i", $store_id);
        $stmt->execute();
        $result = $stmt->get_result();
        $row = $result->fetch_assoc();
        $stmt->close();
        
        if ($row['count'] > 0) {
            return true; // Already initialized
        }
        
        // Default accounts
        $default_accounts = [
            ['1000', 'Cash in Hand', 'asset', 'Cash and cash equivalents'],
            ['1100', 'Bank Accounts', 'asset', 'Bank account balances'],
            ['1200', 'Accounts Receivable', 'asset', 'Amounts owed by customers'],
            ['1300', 'Inventory', 'asset', 'Stock on hand'],
            ['2000', 'Accounts Payable', 'liability', 'Amounts owed to suppliers'],
            ['3000', 'Owner\'s Equity', 'equity', 'Owner\'s investment'],
            ['4000', 'Sales Revenue', 'revenue', 'Income from sales'],
            ['5000', 'Cost of Goods Sold', 'expense', 'Cost of inventory sold'],
            ['5100', 'Operating Expenses', 'expense', 'General operating expenses']
        ];
        
        $stmt = $conn->prepare("
            INSERT INTO chart_of_accounts (account_code, account_name, account_type, description, store_id, is_active)
            VALUES (?, ?, ?, ?, ?, 1)
        ");
        
        foreach ($default_accounts as $account) {
            $stmt->bind_param("ssssi", $account[0], $account[1], $account[2], $account[3], $store_id);
            $stmt->execute();
        }
        $stmt->close();
        
        // Create account mappings
        $mappings = [
            ['1000', 'ast_Cash'],
            ['1100', 'ast_Bank'],
            ['1200', 'ast_Receivable'],
            ['1300', 'ast_Stock'],
            ['2000', 'liab_Payable'],
            ['3000', 'equity_Owner'],
            ['4000', 'rev_Sale'],
            ['5000', 'exp_CostOfSales'],
            ['5100', 'exp_Operating']
        ];
        
        $stmt = $conn->prepare("
            INSERT INTO account_mappings (account_id, mapping_code)
            SELECT id, ? FROM chart_of_accounts WHERE account_code = ? AND store_id = ?
        ");
        
        foreach ($mappings as $mapping) {
            $stmt->bind_param("ssi", $mapping[1], $mapping[0], $store_id);
            $stmt->execute();
        }
        $stmt->close();
        
        return true;
    } catch (Exception $e) {
        error_log("Error initializing chart of accounts: " . $e->getMessage());
        return false;
    }
}

/**
 * Format currency amount
 * @param float $amount Amount to format
 * @param int $decimals Number of decimal places
 * @return string Formatted amount
 */
function formatCurrency($amount, $decimals = 2) {
    return number_format($amount, $decimals);
}

/**
 * Validate journal entry
 * @param mysqli $conn Database connection
 * @param int $journal_entry_id Journal entry ID
 * @return array Validation result
 */
function validateJournalEntry($conn, $journal_entry_id) {
    try {
        $stmt = $conn->prepare("
            SELECT 
                je.*,
                SUM(jel.debit_amount) as total_debits,
                SUM(jel.credit_amount) as total_credits,
                COUNT(jel.id) as line_count
            FROM journal_entries je
            LEFT JOIN journal_entry_lines jel ON je.id = jel.journal_entry_id
            WHERE je.id = ?
            GROUP BY je.id
        ");
        $stmt->bind_param("i", $journal_entry_id);
        $stmt->execute();
        $result = $stmt->get_result();
        $entry = $result->fetch_assoc();
        $stmt->close();
        
        if (!$entry) {
            return ['valid' => false, 'error' => 'Journal entry not found'];
        }
        
        $errors = [];
        
        // Check if entry has lines
        if ($entry['line_count'] == 0) {
            $errors[] = 'Journal entry has no lines';
        }
        
        // Check if debits equal credits
        if (abs($entry['total_debits'] - $entry['total_credits']) > 0.01) {
            $errors[] = 'Debits and credits do not balance';
        }
        
        // Check if all accounts exist and are active
        $stmt = $conn->prepare("
            SELECT jel.id, coa.account_name
            FROM journal_entry_lines jel
            LEFT JOIN chart_of_accounts coa ON jel.account_id = coa.id
            WHERE jel.journal_entry_id = ? AND (coa.id IS NULL OR coa.is_active = 0)
        ");
        $stmt->bind_param("i", $journal_entry_id);
        $stmt->execute();
        $invalid_lines = $stmt->get_result();
        $stmt->close();
        
        if ($invalid_lines->num_rows > 0) {
            $errors[] = 'Some accounts are invalid or inactive';
        }
        
        return [
            'valid' => empty($errors),
            'errors' => $errors,
            'entry' => $entry
        ];
    } catch (Exception $e) {
        error_log("Error validating journal entry: " . $e->getMessage());
        return ['valid' => false, 'error' => 'Validation error: ' . $e->getMessage()];
    }
}
?>
