<?php
/**
 * Payment Transactions Plugin
 * Shows payment transactions and statistics
 */

// Register the Payment Transactions menu
register_menu("Transactions", true, "payment_transactions", 'AFTER_REPORTS', 'ion ion-card', '', '', ['Admin', 'SuperAdmin']);

/**
 * Main function - Dashboard view with statistics
 */
function payment_transactions()
{
    global $ui, $config, $admin;
    _admin();

    // Handle search and filters
    $search = isset($_GET['search']) ? trim($_GET['search']) : '';
    $status = isset($_GET['status']) ? trim($_GET['status']) : '';
    $gateway = isset($_GET['gateway']) ? trim($_GET['gateway']) : '';
    $date_from = isset($_GET['date_from']) ? trim($_GET['date_from']) : '';
    $date_to = isset($_GET['date_to']) ? trim($_GET['date_to']) : '';

    // Build query with filters
    $query = ORM::for_table('tbl_payment_gateway')->order_by_desc('id');

    if ($search != '') {
        $query->where_raw(
            "(username LIKE ? OR gateway_trx_id LIKE ? OR plan_name LIKE ? OR payment_method LIKE ? OR price LIKE ?)",
            ["%$search%", "%$search%", "%$search%", "%$search%", "%$search%"]
        );
    }

    if ($status != '' && $status != 'all') {
        $query->where('status', $status);
    }

    if ($gateway != '' && $gateway != 'all') {
        $query->where('gateway', $gateway);
    }

    if ($date_from != '') {
        $query->where_gte('created_date', $date_from . ' 00:00:00');
    }

    if ($date_to != '') {
        $query->where_lte('created_date', $date_to . ' 23:59:59');
    }

    // Get paginated transactions with configurable items per page
    $page = isset($_GET['page']) ? intval($_GET['page']) : 1;
    $items_per_page = isset($_GET['per_page']) ? intval($_GET['per_page']) : 25;
    if ($items_per_page < 10) $items_per_page = 10;
    if ($items_per_page > 100) $items_per_page = 100;
    $offset = ($page - 1) * $items_per_page;

    $total_records = $query->count();
    $transactions = $query->limit($items_per_page)->offset($offset)->find_many();

    // Calculate statistics
    $stats = payment_transactions_calculate_statistics($date_from, $date_to);

    // Get available gateways for filter dropdown
    $gateways = ORM::for_table('tbl_payment_gateway')
        ->select('gateway')
        ->distinct()
        ->where_not_equal('gateway', '')
        ->find_many();

    // Assign variables to the template
    $ui->assign('transactions', $transactions);
    $ui->assign('search', $search);
    $ui->assign('status', $status);
    $ui->assign('gateway', $gateway);
    $ui->assign('date_from', $date_from);
    $ui->assign('date_to', $date_to);
    $ui->assign('gateways', $gateways);
    $ui->assign('stats', $stats);
    $ui->assign('total_records', $total_records);
    $ui->assign('current_page', $page);
    $ui->assign('total_pages', ceil($total_records / $items_per_page));
    $ui->assign('items_per_page', $items_per_page);
    $ui->assign('start_record', ($page - 1) * $items_per_page + 1);
    $ui->assign('end_record', min($page * $items_per_page, $total_records));
    $ui->assign('_title', 'Payment Transactions');
    $ui->assign('_system_menu', 'plugin/payment_transactions');
    $ui->assign('_admin', Admin::_info());

    // Display the template
    $ui->display('payment_transactions.tpl');
}

/**
 * View individual transaction details
 */
function payment_transactions_view()
{
    global $ui, $config, $admin;
    _admin();

    $id = isset($_GET['id']) ? intval($_GET['id']) : 0;

    if ($id == 0) {
        r2(U . 'plugin/payment_transactions', 'e', 'Invalid transaction ID');
        return;
    }

    $transaction = ORM::for_table('tbl_payment_gateway')->find_one($id);

    if (!$transaction) {
        r2(U . 'plugin/payment_transactions', 'e', 'Transaction not found');
        return;
    }

    // Get customer information
    $customer = null;
    if ($transaction->user_id > 0) {
        $customer = ORM::for_table('tbl_customers')->find_one($transaction->user_id);
    } else if ($transaction->username) {
        $customer = ORM::for_table('tbl_customers')->where('username', $transaction->username)->find_one();
    }

    // Get router information
    $router = null;
    if ($transaction->routers_id > 0) {
        $router = ORM::for_table('tbl_routers')->find_one($transaction->routers_id);
    }

    // Get plan information
    $plan = null;
    if ($transaction->plan_id > 0) {
        $plan = ORM::for_table('tbl_plans')->find_one($transaction->plan_id);
    }

    // Get related transaction if invoice exists
    $invoice = null;
    if ($transaction->trx_invoice) {
        $invoice = ORM::for_table('tbl_transactions')->where('invoice', $transaction->trx_invoice)->find_one();
    }

    $ui->assign('transaction', $transaction);
    $ui->assign('customer', $customer);
    $ui->assign('router', $router);
    $ui->assign('plan', $plan);
    $ui->assign('invoice', $invoice);
    $ui->assign('_title', 'Transaction Details #' . $id);
    $ui->assign('_system_menu', 'plugin/payment_transactions');
    $ui->assign('_admin', Admin::_info());

    $ui->display('payment_transactions_details.tpl');
}

/**
 * Export transactions to CSV
 */
function payment_transactions_export()
{
    global $admin;
    _admin();

    $search = isset($_GET['search']) ? trim($_GET['search']) : '';
    $status = isset($_GET['status']) ? trim($_GET['status']) : '';
    $gateway = isset($_GET['gateway']) ? trim($_GET['gateway']) : '';
    $date_from = isset($_GET['date_from']) ? trim($_GET['date_from']) : '';
    $date_to = isset($_GET['date_to']) ? trim($_GET['date_to']) : '';

    // Build query with same filters
    $query = ORM::for_table('tbl_payment_gateway')->order_by_desc('id');

    if ($search != '') {
        $query->where_raw(
            "(username LIKE ? OR gateway_trx_id LIKE ? OR plan_name LIKE ? OR payment_method LIKE ? OR price LIKE ?)",
            ["%$search%", "%$search%", "%$search%", "%$search%", "%$search%"]
        );
    }

    if ($status != '' && $status != 'all') {
        $query->where('status', $status);
    }

    if ($gateway != '' && $gateway != 'all') {
        $query->where('gateway', $gateway);
    }

    if ($date_from != '') {
        $query->where_gte('created_date', $date_from . ' 00:00:00');
    }

    if ($date_to != '') {
        $query->where_lte('created_date', $date_to . ' 23:59:59');
    }

    $transactions = $query->find_many();

    // Set headers for CSV download
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename=payment_transactions_' . date('Y-m-d_His') . '.csv');

    // Create output stream
    $output = fopen('php://output', 'w');

    // Write CSV header
    fputcsv($output, [
        'ID', 'Username', 'User ID', 'Gateway', 'Transaction ID', 'Plan Name', 
        'Router', 'Price', 'Payment Method', 'Payment Channel', 
        'Created Date', 'Paid Date', 'Expired Date', 'Invoice', 'Status'
    ]);

    // Write data rows
    foreach ($transactions as $trx) {
        $status_text = payment_transactions_get_status_text($trx->status);
        
        fputcsv($output, [
            $trx->id,
            $trx->username,
            $trx->user_id,
            $trx->gateway,
            $trx->gateway_trx_id,
            $trx->plan_name,
            $trx->routers,
            $trx->price,
            $trx->payment_method,
            $trx->payment_channel,
            $trx->created_date,
            $trx->paid_date,
            $trx->expired_date,
            $trx->trx_invoice,
            $status_text
        ]);
    }

    fclose($output);
    exit;
}

/**
 * Calculate payment statistics
 */
function payment_transactions_calculate_statistics($date_from = '', $date_to = '')
{
    $stats = [];

    // Base query for date filtering
    $base_query = ORM::for_table('tbl_payment_gateway');
    if ($date_from != '') {
        $base_query->where_gte('created_date', $date_from . ' 00:00:00');
    }
    if ($date_to != '') {
        $base_query->where_lte('created_date', $date_to . ' 23:59:59');
    }

    // Total transactions
    $stats['total_transactions'] = (clone $base_query)->count();

    // Transactions by status
    $stats['unpaid_count'] = (clone $base_query)->where('status', 1)->count();
    $stats['paid_count'] = (clone $base_query)->where('status', 2)->count();
    $stats['failed_count'] = (clone $base_query)->where('status', 3)->count();
    $stats['canceled_count'] = (clone $base_query)->where('status', 4)->count();

    // Total revenue (paid transactions)
    $paid_transactions = (clone $base_query)->where('status', 2)->find_many();
    $total_revenue = 0;
    foreach ($paid_transactions as $trx) {
        $total_revenue += floatval($trx->price);
    }
    $stats['total_revenue'] = $total_revenue;

    // Potential revenue (unpaid transactions)
    $unpaid_transactions = (clone $base_query)->where('status', 1)->find_many();
    $potential_revenue = 0;
    foreach ($unpaid_transactions as $trx) {
        $potential_revenue += floatval($trx->price);
    }
    $stats['potential_revenue'] = $potential_revenue;

    // Average transaction value
    if ($stats['paid_count'] > 0) {
        $stats['average_transaction'] = $total_revenue / $stats['paid_count'];
    } else {
        $stats['average_transaction'] = 0;
    }

    // Success rate
    if ($stats['total_transactions'] > 0) {
        $stats['success_rate'] = ($stats['paid_count'] / $stats['total_transactions']) * 100;
    } else {
        $stats['success_rate'] = 0;
    }

    // Top payment methods
    $payment_methods = (clone $base_query)
        ->where('status', 2)
        ->where_not_equal('payment_method', '')
        ->select('payment_method')
        ->selectExpr('COUNT(*)', 'count')
        ->selectExpr('SUM(CAST(price AS DECIMAL(10,2)))', 'total')
        ->group_by('payment_method')
        ->order_by_desc('count')
        ->limit(5)
        ->find_many();
    $stats['top_payment_methods'] = $payment_methods;

    // Top gateways
    $gateways = (clone $base_query)
        ->where('status', 2)
        ->where_not_equal('gateway', '')
        ->select('gateway')
        ->selectExpr('COUNT(*)', 'count')
        ->selectExpr('SUM(CAST(price AS DECIMAL(10,2)))', 'total')
        ->group_by('gateway')
        ->order_by_desc('count')
        ->limit(5)
        ->find_many();
    $stats['top_gateways'] = $gateways;

    // Daily revenue trend (last 30 days)
    $daily_revenue = (clone $base_query)
        ->where('status', 2)
        ->where_gte('paid_date', date('Y-m-d', strtotime('-30 days')))
        ->selectExpr('DATE(paid_date)', 'date')
        ->selectExpr('COUNT(*)', 'count')
        ->selectExpr('SUM(CAST(price AS DECIMAL(10,2)))', 'revenue')
        ->group_by('date')
        ->order_by_asc('date')
        ->find_many();
    $stats['daily_revenue'] = $daily_revenue;

    // Most popular plans
    $top_plans = (clone $base_query)
        ->where('status', 2)
        ->select('plan_name')
        ->selectExpr('COUNT(*)', 'count')
        ->selectExpr('SUM(CAST(price AS DECIMAL(10,2)))', 'revenue')
        ->group_by('plan_name')
        ->order_by_desc('count')
        ->limit(5)
        ->find_many();
    $stats['top_plans'] = $top_plans;

    return $stats;
}

/**
 * Get status text from status code
 */
function payment_transactions_get_status_text($status)
{
    switch ($status) {
        case 1:
            return 'Unpaid';
        case 2:
            return 'Paid';
        case 3:
            return 'Failed';
        case 4:
            return 'Canceled';
        default:
            return 'Unknown';
    }
}

/**
 * Get status badge class
 */
function payment_transactions_get_status_badge($status)
{
    switch ($status) {
        case 1:
            return 'warning';
        case 2:
            return 'success';
        case 3:
            return 'danger';
        case 4:
            return 'default';
        default:
            return 'info';
    }
}

/**
 * Refund transaction
 */
function payment_transactions_refund()
{
    global $admin;
    _admin();

    $id = isset($_GET['id']) ? intval($_GET['id']) : 0;
    $reason = isset($_POST['refund_reason']) ? trim($_POST['refund_reason']) : 'No reason provided';

    if ($id == 0) {
        r2(U . 'plugin/payment_transactions', 'e', 'Invalid transaction ID');
        return;
    }

    $transaction = ORM::for_table('tbl_payment_gateway')->find_one($id);

    if (!$transaction) {
        r2(U . 'plugin/payment_transactions', 'e', 'Transaction not found');
        return;
    }

    if ($transaction->status != 2) {
        r2(U . 'plugin/payment_transactions', 'e', 'Only paid transactions can be refunded');
        return;
    }

    // Update transaction status
    $transaction->status = 4; // Canceled/Refunded
    $transaction->save();

    // Log the refund
    $admin_username = $admin['username'];
    Message::sendTelegram("#Refund - Transaction #{$id} refunded by {$admin_username}. Reason: {$reason}");

    r2(U . 'plugin/payment_transactions_view&id=' . $id, 's', 'Transaction refunded successfully');
}

/**
 * Get revenue chart data
 */
function payment_transactions_chart_data()
{
    global $admin;
    _admin();

    $days = isset($_GET['days']) ? intval($_GET['days']) : 30;
    if ($days < 7) $days = 7;
    if ($days > 365) $days = 365;

    $daily_data = ORM::for_table('tbl_payment_gateway')
        ->where('status', 2)
        ->where_gte('paid_date', date('Y-m-d', strtotime("-{$days} days")))
        ->selectExpr('DATE(paid_date)', 'date')
        ->selectExpr('COUNT(*)', 'count')
        ->selectExpr('SUM(CAST(price AS DECIMAL(10,2)))', 'revenue')
        ->group_by('date')
        ->order_by_asc('date')
        ->find_array();

    header('Content-Type: application/json');
    echo json_encode($daily_data);
    exit;
}

/**
 * Print invoice for transaction
 */
function payment_transactions_print_invoice()
{
    global $ui, $config, $admin;
    _admin();

    $id = isset($_GET['id']) ? intval($_GET['id']) : 0;

    if ($id == 0) {
        r2(U . 'plugin/payment_transactions', 'e', 'Invalid transaction ID');
        return;
    }

    $transaction = ORM::for_table('tbl_payment_gateway')->find_one($id);

    if (!$transaction) {
        r2(U . 'plugin/payment_transactions', 'e', 'Transaction not found');
        return;
    }

    $customer = null;
    if ($transaction->user_id > 0) {
        $customer = ORM::for_table('tbl_customers')->find_one($transaction->user_id);
    }

    $ui->assign('transaction', $transaction);
    $ui->assign('customer', $customer);
    $ui->assign('config', $config);
    $ui->display('payment_transactions_invoice.tpl');
}
