<?php

class Wallet extends Model {

    public function getOrCreateWallet($entity_type, $entity_id) {
        $this->db->query("SELECT * FROM wallets WHERE entity_type = :type AND entity_id = :id");
        $this->db->bind(':type', $entity_type);
        $this->db->bind(':id', $entity_id);
        $wallet = $this->db->single();

        if ($wallet) {
            return $wallet;
        }

        // Wallet doesn't exist, let's try to create it.
        try {
            $settingModel = new Setting();
            $settings = $settingModel->getAll();
            $currency = $settings['system_currency'] ?? 'USD';

            $this->db->query("INSERT INTO wallets (entity_type, entity_id, balance, currency) VALUES (:type, :id, 0.00, :currency)");
            $this->db->bind(':type', $entity_type);
            $this->db->bind(':id', $entity_id);
            $this->db->bind(':currency', $currency);
            
            if ($this->db->execute()) {
                $wallet_id = $this->db->lastInsertId();
                // Manually construct an object that mimics the structure of a fetched row
                return (object) [
                    'id' => $wallet_id, 
                    'entity_type' => $entity_type, 
                    'entity_id' => $entity_id, 
                    'balance' => 0.00, 
                    'currency' => $currency,
                    'created_at' => date('Y-m-d H:i:s'),
                    'updated_at' => date('Y-m-d H:i:s')
                ];
            }
        } catch (PDOException $e) {
            // Check if the error is a duplicate entry error (code 23000)
            if ($e->getCode() == '23000') {
                // The wallet was created by another process between our SELECT and INSERT.
                // Let's fetch it again.
                $this->db->query("SELECT * FROM wallets WHERE entity_type = :type AND entity_id = :id");
                $this->db->bind(':type', $entity_type);
                $this->db->bind(':id', $entity_id);
                return $this->db->single();
            }
            // If it's another error, we fall through to return false.
            error_log("Wallet Create Error: " . $e->getMessage());
        }
        return false;
    }

    public function getBalance($wallet_id) {
        if (empty($wallet_id) || !is_numeric($wallet_id)) {
            return 0.00;
        }

        $this->db->query("SELECT balance FROM wallets WHERE id = :id");
        $this->db->bind(':id', $wallet_id);
        $row = $this->db->single();
        return $row ? round((float)$row->balance, 2) : 0.00;
    }

    public function deposit($wallet_id, $amount, $description, $reference) {
        if ($amount <= 0) {
            return false;
        }

        $should_commit = false;
        if (!$this->db->inTransaction()) {
            $this->db->beginTransaction();
            $should_commit = true;
        }

        try {
            // Update Balance
            $this->db->query("UPDATE wallets SET balance = balance + :amount, updated_at = NOW() WHERE id = :id");
            $this->db->bind(':amount', $amount);
            $this->db->bind(':id', $wallet_id);
            $this->db->execute();

            if ($this->db->rowCount() === 0) {
                throw new Exception("Failed to update balance");
            }

            // Log Transaction
            $this->createTransaction($wallet_id, 'deposit', $amount, $description, $reference, 'completed');

            if ($should_commit) {
                $this->db->commit();
            }
            return true;
        } catch (Exception $e) {
            error_log("Wallet Deposit Error: " . $e->getMessage());
            if ($should_commit) {
                $this->db->rollBack();
            }
            return false;
        }
    }

    public function withdraw($wallet_id, $amount, $description, $reference, $type = 'payment') {
        if ($amount <= 0) {
            return false;
        }

        $should_commit = false;
        if (!$this->db->inTransaction()) {
            $this->db->beginTransaction();
            $should_commit = true;
        }

        try {
            // Update Balance (Deduct)
            $this->db->query("UPDATE wallets SET balance = balance - :amount_deduct, updated_at = NOW() WHERE id = :id AND balance >= :amount_check");
            $this->db->bind(':amount_deduct', $amount);
            $this->db->bind(':id', $wallet_id);
            $this->db->bind(':amount_check', $amount);
            $this->db->execute();

            if ($this->db->rowCount() === 0) {
                throw new Exception("Insufficient funds");
            }

            // Log Transaction (Positive amount, type determines direction in UI)
            $this->createTransaction($wallet_id, $type, $amount, $description, $reference, 'completed');

            if ($should_commit) {
                $this->db->commit();
            }
            return true;
        } catch (Exception $e) {
            error_log("Wallet Withdraw Error: " . $e->getMessage());
            if ($should_commit) {
                $this->db->rollBack();
            }
            return false;
        }
    }

    public function transfer($from_wallet_id, $to_wallet_id, $amount, $description) {
        if ($amount <= 0) {
            return false;
        }

        $should_commit = false;
        if (!$this->db->inTransaction()) {
            $this->db->beginTransaction();
            $should_commit = true;
        }

        try {
            // Atomically deduct from Sender and check balance in one query
            $this->db->query("UPDATE wallets SET balance = balance - :amount_deduct, updated_at = NOW() WHERE id = :id AND balance >= :amount_check");
            $this->db->bind(':amount_deduct', $amount);
            $this->db->bind(':id', $from_wallet_id);
            $this->db->bind(':amount_check', $amount);
            $this->db->execute();
            
            if ($this->db->rowCount() === 0) {
                // If no rows were updated, it means balance was insufficient.
                throw new Exception("Insufficient funds");
            }

            // Add to Receiver
            $this->db->query("UPDATE wallets SET balance = balance + :amount, updated_at = NOW() WHERE id = :id");
            $this->db->bind(':amount', $amount);
            $this->db->bind(':id', $to_wallet_id);
            $this->db->execute();

            // Log Transactions
            $ref = uniqid('TRF-');
            if (!$this->createTransaction($from_wallet_id, 'transfer_out', $amount, "Transfer to Wallet #$to_wallet_id: $description", $ref, 'completed')) {
                throw new Exception("Failed to log sender transaction");
            }
            if (!$this->createTransaction($to_wallet_id, 'transfer_in', $amount, "Transfer from Wallet #$from_wallet_id: $description", $ref, 'completed')) {
                throw new Exception("Failed to log receiver transaction");
            }

            if ($should_commit) {
                $this->db->commit();
            }
            return true;
        } catch (Exception $e) {
            error_log("Wallet Transfer Error: " . $e->getMessage());
            if ($should_commit) {
                $this->db->rollBack();
            }
            return false;
        }
    }

    public function requestWithdrawal($wallet_id, $amount, $details) {
        $should_commit = false;
        if (!$this->db->inTransaction()) {
            $this->db->beginTransaction();
            $should_commit = true;
        }

        try {
            // Atomically deduct immediately (reserve funds) and check balance
            $this->db->query("UPDATE wallets SET balance = balance - :amount_deduct, updated_at = NOW() WHERE id = :id AND balance >= :amount_check");
            $this->db->bind(':amount_deduct', $amount);
            $this->db->bind(':id', $wallet_id);
            $this->db->bind(':amount_check', $amount);
            $this->db->execute();
            
            if ($this->db->rowCount() === 0) {
                throw new Exception("Insufficient funds");
            }

            // Create Transaction with pending status
            $this->createTransaction($wallet_id, 'withdrawal', $amount, $details, uniqid('WDR-'), 'pending');

            if ($should_commit) {
                $this->db->commit();
            }
            return true;
        } catch (Exception $e) {
            error_log("Wallet Request Withdrawal Error: " . $e->getMessage());
            if ($should_commit) {
                $this->db->rollBack();
            }
            return false;
        }
    }

    public function processWithdrawal($transaction_id, $action, $note, $reviewer_role) {
        $this->db->query("SELECT * FROM wallet_transactions WHERE id = :id");
        $this->db->bind(':id', $transaction_id);
        $transaction = $this->db->single();

        if (!$transaction) return false;

        if ($action === 'reject') {
            $should_commit = false;
            if (!$this->db->inTransaction()) {
                $this->db->beginTransaction();
                $should_commit = true;
            }
            try {

                // Refund the wallet
                $this->db->query("UPDATE wallets SET balance = balance + :amount, updated_at = NOW() WHERE id = :id");
                $this->db->bind(':amount', $transaction->amount);
                $this->db->bind(':id', $transaction->wallet_id);
                $this->db->execute();

                // Update transaction status to 'failed'
                $new_desc = $transaction->description . " [Rejected: $note]";
                $this->db->query("UPDATE wallet_transactions SET status = 'failed', description = :desc WHERE id = :id");
                $this->db->bind(':desc', $new_desc);
                $this->db->bind(':id', $transaction_id);
                $this->db->execute();

                if ($should_commit) {
                    $this->db->commit();
                }
                return true;
            } catch (Exception $e) {
                error_log("Wallet Process Withdrawal Error: " . $e->getMessage());
                if ($should_commit) {
                    $this->db->rollBack();
                }
                return false;
            }
        } elseif ($action === 'approve') {
            try {
                $this->db->query("UPDATE wallet_transactions SET status = 'completed' WHERE id = :id");
                $this->db->bind(':id', $transaction_id);
                return $this->db->execute();
            } catch (Exception $e) {
                error_log("Wallet Process Withdrawal (Approve) Error: " . $e->getMessage());
                return false;
            }
        }
        return false;
    }

    public function distributePaymentWithCommission($amount, $type, $dest_entity_type, $dest_entity_id, $description, $ref, $source_wallet_id = null) {
        $settingModel = new Setting();
        $settings = $settingModel->getAll();
        
        // Determine commission
        $site_comm_pct = 0;
        $board_comm_pct = 0;
        
        if ($type === 'referee_fee') {
            $site_comm_pct = $settings['site_commission_referee'] ?? 0;
        } elseif ($type === 'fine') {
            $site_comm_pct = $settings['site_commission_fine'] ?? 0;
        } elseif ($type === 'membership') {
            $site_comm_pct = $settings['site_commission_percent'] ?? 0;
            $board_comm_pct = $settings['board_commission_percent'] ?? 0;
        } elseif ($type === 'registration_fee') {
            $site_comm_pct = $settings['site_commission_registration'] ?? 0;
        }

        $site_comm_amount = ($amount * $site_comm_pct) / 100;
        $board_comm_amount = ($amount * $board_comm_pct) / 100;
        $net_amount = $amount - $site_comm_amount - $board_comm_amount;

        try {
            $this->db->beginTransaction();

            // 1. Deduct from Source (if applicable)
            if ($source_wallet_id) {
                // Atomically deduct from source and check balance
                $this->db->query("UPDATE wallets SET balance = balance - :amount_deduct WHERE id = :id AND balance >= :amount_check");
                $this->db->bind(':amount_deduct', $amount);
                $this->db->bind(':id', $source_wallet_id);
                $this->db->bind(':amount_check', $amount);
                $this->db->execute();

                if ($this->db->rowCount() === 0) {
                    // Insufficient funds or error
                    error_log("Wallet Distribute Error: Insufficient funds or update failed for Source Wallet ID: $source_wallet_id");
                    $this->db->rollBack();
                    return false;
                }
                
                $this->createTransaction($source_wallet_id, $type, $amount, $description . " (Out)", $ref, 'completed');
            }

            // 2. Credit Destination
            if ($net_amount > 0) {
                $dest_wallet = $this->getOrCreateWallet($dest_entity_type, $dest_entity_id);
                if (!$dest_wallet) throw new Exception("Destination wallet could not be created.");
                if (!$this->deposit($dest_wallet->id, $net_amount, $description . " (Net)", $ref)) {
                    throw new Exception("Failed to deposit to destination wallet.");
                }
            }

            // 3. Credit Site Commission
            if ($site_comm_amount > 0) {
                $site_wallet = $this->getOrCreateWallet('site', 0); // System wallet
                if (!$site_wallet) throw new Exception("Site wallet could not be created.");
                if (!$this->deposit($site_wallet->id, $site_comm_amount, $description, $ref)) {
                    throw new Exception("Failed to deposit to site wallet.");
                }
            }

            // 4. Credit Board Commission
            if ($board_comm_amount > 0) {
                $board_wallet = $this->getOrCreateWallet('board', 0); // Board wallet
                if (!$board_wallet) throw new Exception("Board wallet could not be created.");
                if (!$this->deposit($board_wallet->id, $board_comm_amount, "Board Comm: $description", $ref)) {
                    throw new Exception("Failed to deposit to board wallet.");
                }
            }

            $this->db->commit();
            return true;
        } catch (Exception $e) {
            error_log("Wallet Distribute Error: " . $e->getMessage());
            $this->db->rollBack();
            return false;
        }
    }

    public function createTransaction($wallet_id, $type, $amount, $description, $reference, $status) {
        // Basic validation to ensure no zero or negative amount transactions are logged.
        if (!is_numeric($amount) || $amount <= 0) {
            return false;
        }

        $this->db->query("INSERT INTO wallet_transactions (wallet_id, type, amount, description, reference, status, created_at) VALUES (:wid, :type, :amt, :desc, :ref, :status, NOW())");
        $this->db->bind(':wid', $wallet_id);
        $this->db->bind(':type', $type);
        $this->db->bind(':amt', $amount);
        $this->db->bind(':desc', $description);
        $this->db->bind(':ref', $reference);
        $this->db->bind(':status', $status);
        return $this->db->execute();
    }

    public function getTransactions($wallet_id, $limit = 50, $offset = 0) {
        $this->db->query("SELECT * FROM wallet_transactions WHERE wallet_id = :wid ORDER BY created_at DESC LIMIT :limit OFFSET :offset");
        $this->db->bind(':wid', $wallet_id);
        $this->db->bind(':limit', $limit, PDO::PARAM_INT);
        $this->db->bind(':offset', $offset, PDO::PARAM_INT);
        return $this->db->resultSet();
    }

    public function getNetRevenueFromMemberships($wallet_id) {
        $this->db->query("
            SELECT SUM(amount) as total
            FROM wallet_transactions
            WHERE wallet_id = :wallet_id
            AND type = 'deposit'
            AND description LIKE 'Membership:%(Net)'
        ");
        $this->db->bind(':wallet_id', $wallet_id);
        $row = $this->db->single();
        return $row ? (float)$row->total : 0.00;
    }

    public function getPendingWithdrawals($role) {
        $status = 'pending';
        if ($role === 'finance_manager') $status = 'pending';
        elseif ($role === 'admin') $status = 'pending_admin';
        elseif ($role === 'super_admin') $status = 'pending_super_admin';

        $this->db->query("
            SELECT wt.*, w.entity_type, w.entity_id 
            FROM wallet_transactions wt 
            JOIN wallets w ON wt.wallet_id = w.id 
            WHERE wt.type = 'withdrawal' AND wt.status = :status
        ");
        $this->db->bind(':status', $status);
        return $this->db->resultSet();
    }

    public function getAllWalletsWithDetails() {
        $this->db->query("
            SELECT w.*, 
            CASE 
                WHEN w.entity_type = 'user' THEN CONCAT(u.first_name, ' ', u.last_name)
                WHEN w.entity_type = 'club' THEN c.name
                WHEN w.entity_type = 'team' THEN t.name
                WHEN w.entity_type = 'site' THEN 'Site Commission Wallet'
                WHEN w.entity_type = 'board' THEN 'Board Commission Wallet'
                ELSE w.entity_type
            END as entity_name
            FROM wallets w
            LEFT JOIN users u ON w.entity_type = 'user' AND w.entity_id = u.id
            LEFT JOIN clubs c ON w.entity_type = 'club' AND w.entity_id = c.id
            LEFT JOIN teams t ON w.entity_type = 'team' AND w.entity_id = t.id
        ");
        return $this->db->resultSet();
    }

    public function getTotalSystemTransactionsCount() {
        $this->db->query("SELECT COUNT(*) as count FROM wallet_transactions");
        return $this->db->single()->count;
    }

    public function getAllSystemTransactions($limit, $offset) {
        $this->db->query("
            SELECT wt.*, w.entity_type, w.entity_id,
            u.first_name, u.last_name, u.email as user_email,
            c.name as club_name
            FROM wallet_transactions wt
            JOIN wallets w ON wt.wallet_id = w.id
            LEFT JOIN users u ON w.entity_type = 'user' AND w.entity_id = u.id
            LEFT JOIN clubs c ON w.entity_type = 'club' AND w.entity_id = c.id
            ORDER BY wt.created_at DESC
            LIMIT :limit OFFSET :offset
        ");
        $this->db->bind(':limit', $limit, PDO::PARAM_INT);
        $this->db->bind(':offset', $offset, PDO::PARAM_INT);
        return $this->db->resultSet();
    }

    public function getTransactionsCountByWalletId($wallet_id) {
        $this->db->query("SELECT COUNT(*) as count FROM wallet_transactions WHERE wallet_id = :wallet_id");
        $this->db->bind(':wallet_id', $wallet_id);
        $res = $this->db->single();
        return $res ? $res->count : 0;
    }

    public function getTransactionsByWalletIdWithDetails($wallet_id, $limit, $offset) {
        $this->db->query("
            SELECT wt.*, w.entity_type, w.entity_id,
            u.first_name, u.last_name, u.email as user_email,
            c.name as club_name
            FROM wallet_transactions wt
            JOIN wallets w ON wt.wallet_id = w.id
            LEFT JOIN users u ON w.entity_type = 'user' AND w.entity_id = u.id
            LEFT JOIN clubs c ON w.entity_type = 'club' AND w.entity_id = c.id
            WHERE wt.wallet_id = :wallet_id
            ORDER BY wt.created_at DESC
            LIMIT :limit OFFSET :offset
        ");
        $this->db->bind(':wallet_id', $wallet_id);
        $this->db->bind(':limit', $limit, PDO::PARAM_INT);
        $this->db->bind(':offset', $offset, PDO::PARAM_INT);
        return $this->db->resultSet();
    }

        public function credit($wallet_id, $amount, $description, $reference) {
                if ($amount <= 0) {
                        return false;
                }
                return $this->deposit($wallet_id, $amount, $description, $reference);
        }


}