<?php
/**
 * Finance Service - Shared Backend Utilities
 * 
 * Provides reusable helper functions for connecting external sub-modules
 * (Invoicing, Expenditure) to the bank account transaction tracking system.
 * 
 * Usage:
 * require_once __DIR__ . '/../includes/finance_service.php';
 * $financeService = new FinanceService($mysqli);
 * $financeService->recordIncome($invoiceId, $bankAccountId, $amount);
 */

require_once __DIR__ . '/db.php';

class FinanceService {
    private $mysqli;
    private $lastVoucherNumber = null;
    private $lastTransactionId = null;
    
    public function __construct($mysqli = null) {
        $this->mysqli = $mysqli ?? db();
        $this->ensureSchema();
    }
    
    /**
     * Ensure finance module tables exist
     */
    private function ensureSchema() {
        // Bank Accounts Table
        $this->mysqli->query("CREATE TABLE IF NOT EXISTS bank_accounts (
            id INT AUTO_INCREMENT PRIMARY KEY,
            account_name VARCHAR(150) NOT NULL,
            account_number VARCHAR(50) NOT NULL UNIQUE,
            bank_name VARCHAR(100) DEFAULT NULL,
            branch_name VARCHAR(100) DEFAULT NULL,
            ifsc_code VARCHAR(20) DEFAULT NULL,
            swift_code VARCHAR(20) DEFAULT NULL,
            currency VARCHAR(3) NOT NULL DEFAULT 'LKR',
            initial_balance DECIMAL(15,2) NOT NULL DEFAULT 0.00,
            current_balance DECIMAL(15,2) NOT NULL DEFAULT 0.00,
            account_type ENUM('Current','Savings','Fixed Deposit','Recurring Deposit','Other') DEFAULT 'Current',
            is_active TINYINT(1) NOT NULL DEFAULT 1,
            is_default TINYINT(1) NOT NULL DEFAULT 0,
            description TEXT,
            opening_date DATE DEFAULT NULL,
            created_by INT DEFAULT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            INDEX account_name (account_name),
            INDEX bank_name (bank_name),
            INDEX is_active (is_active)
        ) ENGINE=InnoDB");
        
        // Transactions Table
        $this->mysqli->query("CREATE TABLE IF NOT EXISTS transactions (
            id INT AUTO_INCREMENT PRIMARY KEY,
            voucher_number VARCHAR(50) NOT NULL UNIQUE,
            transaction_date DATE NOT NULL,
            transaction_type ENUM('Receipt','Payment','Contra','Journal') NOT NULL,
            account_head VARCHAR(100) NOT NULL,
            sub_account_head VARCHAR(100) DEFAULT NULL,
            bank_account_id INT DEFAULT NULL,
            party_id INT DEFAULT NULL,
            party_type ENUM('Customer','Supplier','Employee','Other') DEFAULT NULL,
            party_name VARCHAR(150) DEFAULT NULL,
            amount DECIMAL(15,2) NOT NULL DEFAULT 0.00,
            amount_in_words VARCHAR(255) DEFAULT NULL,
            mode_of_payment ENUM('Cash','Cheque','Bank Transfer','Online Payment','Credit Card','Debit Card','Draft','Other') DEFAULT 'Bank Transfer',
            cheque_number VARCHAR(50) DEFAULT NULL,
            cheque_date DATE DEFAULT NULL,
            bank_name VARCHAR(100) DEFAULT NULL,
            narration TEXT,
            document_reference VARCHAR(100) DEFAULT NULL,
            document_date DATE DEFAULT NULL,
            cost_center VARCHAR(100) DEFAULT NULL,
            project_code VARCHAR(50) DEFAULT NULL,
            tax_amount DECIMAL(15,2) DEFAULT 0.00,
            tax_percent DECIMAL(5,2) DEFAULT 0.00,
            tax_type VARCHAR(50) DEFAULT NULL,
            is_reconciled TINYINT(1) NOT NULL DEFAULT 0,
            reconciled_date DATE DEFAULT NULL,
            reconciled_by INT DEFAULT NULL,
            is_approved TINYINT(1) NOT NULL DEFAULT 1,
            approved_by INT DEFAULT NULL,
            approved_at TIMESTAMP NULL,
            approval_status ENUM('Pending','Approved','Rejected') NOT NULL DEFAULT 'Approved',
            rejection_reason VARCHAR(255) DEFAULT NULL,
            entered_by INT DEFAULT NULL,
            entered_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            modified_by INT DEFAULT NULL,
            modified_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            is_cancelled TINYINT(1) NOT NULL DEFAULT 0,
            cancelled_by INT DEFAULT NULL,
            cancelled_at TIMESTAMP NULL,
            cancellation_reason VARCHAR(255) DEFAULT NULL,
            related_transaction_id INT DEFAULT NULL,
            reference_id INT DEFAULT NULL,
            reference_type VARCHAR(50) DEFAULT NULL,
            notes TEXT,
            INDEX transaction_date (transaction_date),
            INDEX transaction_type (transaction_type),
            INDEX account_head (account_head),
            INDEX bank_account_id (bank_account_id),
            INDEX party_id (party_id),
            INDEX is_reconciled (is_reconciled),
            INDEX is_approved (is_approved),
            INDEX approval_status (approval_status),
            INDEX is_cancelled (is_cancelled),
            CONSTRAINT fk_transaction_bank_account FOREIGN KEY (bank_account_id) REFERENCES bank_accounts(id) ON DELETE SET NULL
        ) ENGINE=InnoDB");
        
        // Transaction Attachments Table
        $this->mysqli->query("CREATE TABLE IF NOT EXISTS transaction_attachments (
            id INT AUTO_INCREMENT PRIMARY KEY,
            transaction_id INT NOT NULL,
            file_name VARCHAR(255) NOT NULL,
            file_path VARCHAR(500) NOT NULL,
            file_type VARCHAR(50) DEFAULT NULL,
            file_size INT DEFAULT NULL,
            uploaded_by INT DEFAULT NULL,
            uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            description VARCHAR(255) DEFAULT NULL,
            INDEX transaction_id (transaction_id),
            CONSTRAINT fk_attachment_transaction FOREIGN KEY (transaction_id) REFERENCES transactions(id) ON DELETE CASCADE
        ) ENGINE=InnoDB");
    }
    
    /**
     * Generate unique voucher number
     * Format: VCH/YYYY/MMDD/XXXX
     */
    private function generateVoucherNumber($type, $date = null) {
        $date = $date ?? date('Y-m-d');
        $year = date('Y', strtotime($date));
        $month = date('m', strtotime($date));
        $day = date('d', strtotime($date));
        
        $prefix = 'VCH/' . $year . '/' . $month . $day . '/';
        $like = $prefix . '%';
        
        $stmt = $this->mysqli->prepare("SELECT voucher_number FROM transactions WHERE voucher_number LIKE ? ORDER BY id DESC LIMIT 1");
        $stmt->bind_param('s', $like);
        $stmt->execute();
        $result = $stmt->get_result();
        $row = $result->fetch_assoc();
        $stmt->close();
        
        $next = 1;
        if ($row && preg_match('/\/(\d{4})$/', $row['voucher_number'], $matches)) {
            $next = intval($matches[1]) + 1;
        }
        
        return $prefix . str_pad($next, 4, '0', STR_PAD_LEFT);
    }
    
    /**
     * Convert amount to words
     */
    private function amountInWords($amount, $currency = 'LKR') {
        $amount = floatval($amount);
        $whole = floor($amount);
        $decimal = round(($amount - $whole) * 100);
        
        $ones = ['', 'One', 'Two', 'Three', 'Four', 'Five', 'Six', 'Seven', 'Eight', 'Nine'];
        $teens = ['Ten', 'Eleven', 'Twelve', 'Thirteen', 'Fourteen', 'Fifteen', 'Sixteen', 'Seventeen', 'Eighteen', 'Nineteen'];
        $tens = ['', '', 'Twenty', 'Thirty', 'Forty', 'Fifty', 'Sixty', 'Seventy', 'Eighty', 'Ninety'];
        $places = ['', 'Thousand', 'Million', 'Billion'];
        
        $convertNumber = function($num) use (&$convertNumber, $ones, $teens, $tens) {
            if ($num < 10) return $ones[$num];
            if ($num < 20) return $teens[$num - 10];
            if ($num < 100) return $tens[floor($num / 10)] . ($num % 10 ? ' ' . $ones[$num % 10] : '');
            if ($num < 1000) return $ones[floor($num / 100)] . ' Hundred' . ($num % 100 ? ' and ' . $convertNumber($num % 100) : '');
            return '';
        };
        
        $convertWhole = function($num) use ($places, $convertNumber) {
            if ($num == 0) return 'Zero';
            
            $parts = [];
            $place = 0;
            while ($num > 0) {
                $chunk = $num % 1000;
                if ($chunk > 0) {
                    $part = $convertNumber($chunk);
                    if (isset($places[$place]) && $places[$place]) {
                        $part .= ' ' . $places[$place];
                    }
                    array_unshift($parts, $part);
                }
                $num = floor($num / 1000);
                $place++;
            }
            return implode(', ', $parts);
        };
        
        $words = $convertWhole($whole);
        if ($decimal > 0) {
            $words .= ' and ' . $convertNumber($decimal) . ' Cents';
        }
        
        $currencyNames = ['LKR' => 'Sri Lankan Rupees', 'USD' => 'US Dollars'];
        return $words . ' ' . ($currencyNames[$currency] ?? $currency);
    }
    
    /**
     * Get bank account currency
     */
    private function getBankCurrency($bankAccountId) {
        if (!$bankAccountId) return 'LKR';
        $stmt = $this->mysqli->prepare("SELECT currency FROM bank_accounts WHERE id = ?");
        $stmt->bind_param('i', $bankAccountId);
        $stmt->execute();
        $result = $stmt->get_result();
        $row = $result->fetch_assoc();
        $stmt->close();
        return $row['currency'] ?? 'LKR';
    }
    
    /**
     * Get bank account details
     */
    private function getBankAccount($bankAccountId) {
        $stmt = $this->mysqli->prepare("SELECT * FROM bank_accounts WHERE id = ? AND is_active = 1");
        $stmt->bind_param('i', $bankAccountId);
        $stmt->execute();
        $result = $stmt->get_result();
        $row = $result->fetch_assoc();
        $stmt->close();
        return $row;
    }
    
    /**
     * Get default bank account ID
     */
    public function getDefaultBankAccountId() {
        $stmt = $this->mysqli->prepare("SELECT id FROM bank_accounts WHERE is_default = 1 AND is_active = 1 LIMIT 1");
        $stmt->execute();
        $result = $stmt->get_result();
        $row = $result->fetch_assoc();
        $stmt->close();
        return $row ? $row['id'] : null;
    }
    
    /**
     * Update bank account balance
     */
    private function updateBankBalance($bankAccountId, $amount, $isIncrement) {
        if (!$bankAccountId) return false;
        
        $sql = $isIncrement 
            ? "UPDATE bank_accounts SET current_balance = current_balance + ? WHERE id = ?"
            : "UPDATE bank_accounts SET current_balance = current_balance - ? WHERE id = ?";
        
        $stmt = $this->mysqli->prepare($sql);
        $stmt->bind_param('di', $amount, $bankAccountId);
        $result = $stmt->execute();
        $stmt->close();
        
        return $result;
    }
    
    /**
     * Record Income - Creates a Receipt transaction when an invoice is paid
     * 
     * @param int $invoiceId - The invoice ID
     * @param int $bankAccountId - The bank account to receive the payment
     * @param float $amount - The payment amount
     * @param array $extraData - Optional additional data (customer_name, payment_mode, notes, etc.)
     * @return array - Result with success status, transaction_id, and voucher_number
     */
    public function recordIncome($invoiceId, $bankAccountId, $amount, $extraData = []) {
        // Validate bank account
        $bankAccount = $this->getBankAccount($bankAccountId);
        if (!$bankAccount) {
            return ['success' => false, 'message' => 'Invalid or inactive bank account'];
        }
        
        // Validate amount
        $amount = floatval($amount);
        if ($amount <= 0) {
            return ['success' => false, 'message' => 'Amount must be greater than 0'];
        }
        
        // Prepare data
        $transactionDate = $extraData['transaction_date'] ?? date('Y-m-d');
        $voucherNumber = $this->generateVoucherNumber('Receipt', $transactionDate);
        $currency = $bankAccount['currency'];
        $amountInWords = $this->amountInWords($amount, $currency);
        
        $accountHead = $extraData['account_head'] ?? 'Sales Revenue';
        $customerName = $extraData['customer_name'] ?? 'Customer #' . ($extraData['customer_id'] ?? 'Unknown');
        $paymentMode = $extraData['payment_mode'] ?? 'Bank Transfer';
        $narration = $extraData['narration'] ?? 'Invoice payment received - Invoice #' . $invoiceId;
        $costCenter = $extraData['cost_center'] ?? '';
        $enteredBy = $extraData['entered_by'] ?? ($_SESSION['user_id'] ?? null);
        
        $this->mysqli->begin_transaction();
        
        try {
            // Insert transaction
            $stmt = $this->mysqli->prepare("INSERT INTO transactions 
                (voucher_number, transaction_date, transaction_type, account_head,
                 bank_account_id, party_id, party_type, party_name, amount, amount_in_words,
                 mode_of_payment, narration, document_reference, cost_center,
                 reference_id, reference_type, approval_status, is_approved, entered_by)
                VALUES (?, ?, 'Receipt', ?, ?, ?, 'Customer', ?, ?, ?, ?, ?, ?, ?, ?, 'Invoice', 'Approved', 1, ?)");
            
            $partyId = $extraData['customer_id'] ?? null;
            $documentRef = $extraData['document_reference'] ?? 'INV-' . $invoiceId;
            
            // 14 parameters: voucher_number, transaction_date, account_head, bank_account_id, party_id,
            // customer_name, amount, amount_in_words, mode_of_payment, narration, document_reference,
            // cost_center, reference_id, entered_by
            $stmt->bind_param('sssiisdsssssis',
                $voucherNumber,
                $transactionDate,
                $accountHead,
                $bankAccountId,
                $partyId,
                $customerName,
                $amount,
                $amountInWords,
                $paymentMode,
                $narration,
                $documentRef,
                $costCenter,
                $invoiceId,
                $enteredBy
            );
            
            if (!$stmt->execute()) {
                throw new Exception('Failed to create transaction: ' . $stmt->error);
            }
            
            $transactionId = $stmt->insert_id;
            $stmt->close();
            
            // Update bank balance (+ increment)
            if (!$this->updateBankBalance($bankAccountId, $amount, true)) {
                throw new Exception('Failed to update bank balance');
            }
            
            $this->mysqli->commit();
            
            $this->lastVoucherNumber = $voucherNumber;
            $this->lastTransactionId = $transactionId;
            
            return [
                'success' => true,
                'message' => 'Income recorded successfully',
                'transaction_id' => $transactionId,
                'voucher_number' => $voucherNumber,
                'invoice_id' => $invoiceId,
                'bank_account_id' => $bankAccountId,
                'amount' => $amount,
                'currency' => $currency
            ];
            
        } catch (Exception $e) {
            $this->mysqli->rollback();
            return ['success' => false, 'message' => $e->getMessage()];
        }
    }
    
    /**
     * Record Expense - Creates a Payment transaction when an expenditure is recorded
     * 
     * @param int $expenseId - The expense/expenditure ID
     * @param int $bankAccountId - The bank account to deduct from
     * @param float $amount - The expense amount
     * @param array $extraData - Optional additional data (supplier_name, expense_category, notes, etc.)
     * @return array - Result with success status, transaction_id, and voucher_number
     */
    public function recordExpense($expenseId, $bankAccountId, $amount, $extraData = []) {
        // Validate bank account
        $bankAccount = $this->getBankAccount($bankAccountId);
        if (!$bankAccount) {
            return ['success' => false, 'message' => 'Invalid or inactive bank account'];
        }
        
        // Validate amount
        $amount = floatval($amount);
        if ($amount <= 0) {
            return ['success' => false, 'message' => 'Amount must be greater than 0'];
        }
        
        // Check sufficient balance
        if ($bankAccount['current_balance'] < $amount) {
            return ['success' => false, 'message' => 'Insufficient balance in bank account'];
        }
        
        // Prepare data
        $transactionDate = $extraData['transaction_date'] ?? date('Y-m-d');
        $voucherNumber = $this->generateVoucherNumber('Payment', $transactionDate);
        $currency = $bankAccount['currency'];
        $amountInWords = $this->amountInWords($amount, $currency);
        
        $accountHead = $extraData['account_head'] ?? 'Other Expenses';
        $supplierName = $extraData['supplier_name'] ?? 'Supplier #' . ($extraData['supplier_id'] ?? 'Unknown');
        $paymentMode = $extraData['payment_mode'] ?? 'Bank Transfer';
        $narration = $extraData['narration'] ?? 'Expense payment - Expense #' . $expenseId;
        $costCenter = $extraData['cost_center'] ?? '';
        $enteredBy = $extraData['entered_by'] ?? ($_SESSION['user_id'] ?? null);
        
        $this->mysqli->begin_transaction();
        
        try {
            // Insert transaction
            $stmt = $this->mysqli->prepare("INSERT INTO transactions 
                (voucher_number, transaction_date, transaction_type, account_head,
                 bank_account_id, party_id, party_type, party_name, amount, amount_in_words,
                 mode_of_payment, narration, document_reference, cost_center,
                 reference_id, reference_type, approval_status, is_approved, entered_by)
                VALUES (?, ?, 'Payment', ?, ?, ?, 'Supplier', ?, ?, ?, ?, ?, ?, ?, ?, 'Expense', 'Approved', 1, ?)");
            
            $partyId = $extraData['supplier_id'] ?? null;
            $documentRef = $extraData['document_reference'] ?? 'EXP-' . $expenseId;
            
            // 14 parameters
            $stmt->bind_param('sssiisdsssssis',
                $voucherNumber,
                $transactionDate,
                $accountHead,
                $bankAccountId,
                $partyId,
                $supplierName,
                $amount,
                $amountInWords,
                $paymentMode,
                $narration,
                $documentRef,
                $costCenter,
                $expenseId,
                $enteredBy
            );
            
            if (!$stmt->execute()) {
                throw new Exception('Failed to create transaction: ' . $stmt->error);
            }
            
            $transactionId = $stmt->insert_id;
            $stmt->close();
            
            // Update bank balance (- decrement)
            if (!$this->updateBankBalance($bankAccountId, $amount, false)) {
                throw new Exception('Failed to update bank balance');
            }
            
            $this->mysqli->commit();
            
            $this->lastVoucherNumber = $voucherNumber;
            $this->lastTransactionId = $transactionId;
            
            return [
                'success' => true,
                'message' => 'Expense recorded successfully',
                'transaction_id' => $transactionId,
                'voucher_number' => $voucherNumber,
                'expense_id' => $expenseId,
                'bank_account_id' => $bankAccountId,
                'amount' => $amount,
                'currency' => $currency,
                'new_balance' => $bankAccount['current_balance'] - $amount
            ];
            
        } catch (Exception $e) {
            $this->mysqli->rollback();
            return ['success' => false, 'message' => $e->getMessage()];
        }
    }
    
    /**
     * Get the last generated voucher number
     */
    public function getLastVoucherNumber() {
        return $this->lastVoucherNumber;
    }
    
    /**
     * Get the last created transaction ID
     */
    public function getLastTransactionId() {
        return $this->lastTransactionId;
    }
    
    /**
     * Reverse a transaction (for refunds, cancellations)
     */
    public function reverseTransaction($transactionId, $reason = '') {
        $this->mysqli->begin_transaction();
        
        try {
            // Get original transaction
            $stmt = $this->mysqli->prepare("SELECT * FROM transactions WHERE id = ? AND is_cancelled = 0");
            $stmt->bind_param('i', $transactionId);
            $stmt->execute();
            $result = $stmt->get_result();
            $original = $result->fetch_assoc();
            $stmt->close();
            
            if (!$original) {
                throw new Exception('Transaction not found or already cancelled');
            }
            
            $cancelledBy = $_SESSION['user_id'] ?? null;
            $cancelledAt = date('Y-m-d H:i:s');
            
            // Mark as cancelled
            $stmt = $this->mysqli->prepare("UPDATE transactions SET 
                is_cancelled = 1, cancelled_by = ?, cancelled_at = ?, cancellation_reason = ?
                WHERE id = ?");
            $stmt->bind_param('issi', $cancelledBy, $cancelledAt, $reason, $transactionId);
            
            if (!$stmt->execute()) {
                throw new Exception('Failed to cancel transaction');
            }
            $stmt->close();
            
            // Reverse bank balance
            $amount = $original['amount'] + ($original['tax_amount'] ?? 0);
            $bankAccountId = $original['bank_account_id'];
            $type = $original['transaction_type'];
            
            switch ($type) {
                case 'Receipt':
                    // Reverse receipt - decrease balance
                    $this->updateBankBalance($bankAccountId, $amount, false);
                    break;
                case 'Payment':
                    // Reverse payment - increase balance
                    $this->updateBankBalance($bankAccountId, $amount, true);
                    break;
                case 'Contra':
                    // Handle contra reversal if needed
                    break;
            }
            
            $this->mysqli->commit();
            return ['success' => true, 'message' => 'Transaction reversed successfully'];
            
        } catch (Exception $e) {
            $this->mysqli->rollback();
            return ['success' => false, 'message' => $e->getMessage()];
        }
    }
    
    /**
     * Get transaction summary for a reference (Invoice or Expense)
     */
    public function getTransactionByReference($referenceType, $referenceId) {
        $stmt = $this->mysqli->prepare("SELECT * FROM transactions WHERE reference_type = ? AND reference_id = ? AND is_cancelled = 0");
        $stmt->bind_param('si', $referenceType, $referenceId);
        $stmt->execute();
        $result = $stmt->get_result();
        $transactions = [];
        while ($row = $result->fetch_assoc()) {
            $transactions[] = $row;
        }
        $stmt->close();
        return $transactions;
    }
}

// Standalone helper functions for quick access

/**
 * Record income from invoice payment (standalone function)
 */
function recordInvoiceIncome($invoiceId, $bankAccountId, $amount, $extraData = []) {
    $financeService = new FinanceService();
    return $financeService->recordIncome($invoiceId, $bankAccountId, $amount, $extraData);
}

/**
 * Record income payment (standalone function)
 */
function recordIncomePayment($invoiceId, $bankAccountId, $amount, $extraData = []) {
    $financeService = new FinanceService();
    return $financeService->recordIncome($invoiceId, $bankAccountId, $amount, $extraData);
}

/**
 * Get default bank account ID (standalone function)
 */
function getDefaultBankAccountId() {
    $financeService = new FinanceService();
    return $financeService->getDefaultBankAccountId();
}

/**
 * Record expense payment (standalone function)
 */
function recordExpensePayment($expenseId, $bankAccountId, $amount, $extraData = []) {
    $financeService = new FinanceService();
    return $financeService->recordExpense($expenseId, $bankAccountId, $amount, $extraData);
}
?>
