<?php

namespace WPPayForm\App\Models;

if (!defined('ABSPATH')) {
    exit;
}

use ExactLinks\Framework\Database\Orm\Builder;
use WPPayForm\App\Http\Controllers\FormController;
use WPPayForm\Framework\Foundation\App;
use WPPayForm\Framework\Support\Arr;
use WPPayForm\App\Models\SubscriptionTransaction;
use WPPayForm\App\Models\Subscription;
use WPPayForm\App\Models\Transaction;
use WPPayForm\App\Services\GeneralSettings;
use WPPayForm\App\Models\Form;

/**
 * Manage Submission
 * @since 1.0.0
 */
class Submission extends Model
{
    protected $table = 'wpf_submissions';
    public $metaGroup = 'wpf_submissions';
    
    public function form()
    {
        return $this->belongsTo(Form::class, 'form_id', 'id');
    }

    public function transactions()
    {
        return $this->hasMany(Transaction::class, 'submission_id', 'id');
    }

    public function subscriptions()
    {
        return $this->hasMany(Subscription::class, 'submission_id', 'id');
    }

    public function index($formId, $request)
    {
        $searchString = sanitize_text_field(Arr::get($request, 'search_string'));
        $page = absint(Arr::get($request, 'page_number'), 0);
        $perPage = absint(Arr::get($request, 'per_page'), 5000);
        $skip = ($page - 1) * $perPage;

        $wheres = array();
        // Get date filters
        $startDate = $this->sanitizeDateInput(Arr::get($request, 'start_date'));
        $endDate = $this->sanitizeDateInput(Arr::get($request, 'end_date'));

        $paymentStatus = Arr::get($request, 'payment_status', false);

        $status = Arr::get($request, 'status', false);

        if ($paymentStatus) {
            $wheres['payment_status'] = sanitize_text_field($paymentStatus);
        }

        if ($status) {
            $wheres['status'] = sanitize_text_field($status);
        }

        if ('total' === $paymentStatus) {
            unset($wheres['payment_status']);
        }

        $submissions = $this->getAll($formId, $wheres, $perPage, $skip, 'DESC', $searchString, $startDate, $endDate);

        if (empty($submissions->items)) {
            return [
                'submissions' => [],
                'total' => 0,
                'hasPaymentItem' => $formId ? Form::hasPaymentFields($formId) : true,
            ];
        }

        // Batch process submissions for better performance
        $processedSubmissions = $this->processSubmissionsInBatch($submissions->items, $formId);

        $result = [
            'submissions' => $processedSubmissions,
            'total' => (int) $submissions->total,
            'hasPaymentItem' => $formId ? Form::hasPaymentFields($formId) : true,
        ];
        return $result;
    }

    public function sanitizeDateInput($date)
    {
        if (!isset($date)) {
            return;
        }
        return sanitize_text_field($date);
    }


    public function searchString($searchString, $query)
    {

        if (!$searchString) {
            return;
        }

        $query->where(function ($query) use ($searchString) {
            $fields = [
                'customer_name',
                'customer_email',
                'payment_method',
                'payment_total',
                'form_data_formatted',
                'created_at',
            ];

            foreach ($fields as $field) {
                $query->orWhere("wpf_submissions.$field", 'LIKE', "%{$searchString}%");
            }
        });

        return $query;
    }


    public function dateFilter($query, $startDate, $endDate)
    {
        if (isset($startDate) && isset($endDate)) {
            $endOfDay = wp_date('Y-m-d', strtotime($endDate)) . ' 23:59:59';
            $query->whereBetween('created_at', [$startDate, $endOfDay]);
        }
        return $query;
    }

    public function paymentStatus($paymentStatus, $query)
    {

        if (!empty($paymentStatus)) {
            if ($paymentStatus == 'abandoned') {
                $query->where('payment_status', 'pending');
                $query = $this->makeQueryAbandoned($query, '<', true);
            } else {
                $query->where('payment_status', $paymentStatus);
            }
        }
        return $query;
    }

    /**
     * Batch process submissions for better performance
     */
    private function processSubmissionsInBatch($submissions, $formId)
    {
        // Get all submission IDs for batch operations
        $submissionIds = $submissions->pluck('id')->toArray();

        // Batch get currency settings once
        $currencySettings = GeneralSettings::getGlobalCurrencySettings();

        // Batch check subscriptions
        $subscriptionStatuses = $this->checkSubscriptionsBySubmissionIds($submissionIds);

        // Batch get submission entries if needed
        $submissionEntries = $this->getSubmissionEntriesbatch($submissionIds);

        // Process each submission
        $processedSubmissions = [];
        foreach ($submissions as $submission) {
            $submissionId = $submission->id;

            // Set currency settings
            $currencySettings['currency_sign'] = GeneralSettings::getCurrencySymbol($submission->currency);
            $submission->currencySettings = $currencySettings;

            // Set subscription status
            $submission->hasSubscription = $subscriptionStatuses[$submissionId] ?? false;

            // Set submission entry
            $submission->submissionEntry = $submissionEntries[$submissionId] ?? null;

            $processedSubmissions[] = $submission;
        }

        // Apply filters
        $submissionItems = apply_filters('wppayform/form_entries', $processedSubmissions, $formId);

        // Apply recurring info filter in batch
        $finalSubmissions = [];
        foreach ($submissionItems as $submissionItem) {
            $finalSubmissions[] = apply_filters('wppayform/form_entry_recurring_info', $submissionItem);
        }

        return $finalSubmissions;
    }

    /**
     * Batch check subscription statuses
     */
    private function checkSubscriptionsBySubmissionIds($submissionIds)
    {
         if (empty($submissionIds)) {
            return [];
        }

        // Get all subscriptions in one query with minimal data
        $subscriptions = (new Subscription())
            ->whereIn('submission_id', $submissionIds)
            ->pluck('submission_id')
            ->toArray();
        
        // Create status array
        $statuses = array_fill_keys($submissionIds, false);
        foreach ($subscriptions as $submissionId) {
            $statuses[$submissionId] = true;
        }
        
        return $statuses;
    }

    /**
     * Batch get submission entries
     */
    private function getSubmissionEntriesbatch($submissionIds)
    {
        if (empty($submissionIds)) {
            return [];
        }

        $batchSize = 20; // Optimized for performance and avoiding DB overload
        $entries = [];

        foreach (array_chunk($submissionIds, $batchSize) as $chunk) {
            $chunkResults = $this->whereIn('id', $chunk)
                ->with([
                    'transactions:id,submission_id,payment_total',
                    'orderItems:id,submission_id,type'
                ])
                ->get(['id', 'form_id', 'payment_total']);

            foreach ($chunkResults as $entry) {
                $entries[$entry->id] = $entry;
            }

            // Prevent DB overload on large loads
            if (count($submissionIds) > 100) {
                usleep(1000); // 1ms delay between chunks
            }
        }

        return $entries;
    }

    public function createSubmission($submission)
    {
        return $this->create($submission);
    }

    public function getSubmissionByEmail($email, $form_id)
    {
        return $this->where('customer_email', $email)->where('form_id', $form_id)->first();
    }

    public function getNewEntriesCount()
    {
        return $this->where('status', 'new')->count();
    }

    public function getAll($formId = false, $wheres = array(), $perPage = false, $skip = false, $orderBy = 'DESC', $searchString = false, $startDate = null, $endDate = null)
    {
        $resultQuery = $this->select(array('wpf_submissions.*', 'posts.post_title'))
            ->join('posts', 'posts.ID', '=', 'wpf_submissions.form_id')
            ->orderBy('wpf_submissions.id', $orderBy);

        if ($formId) {
            $resultQuery->where('wpf_submissions.form_id', $formId);
        }

        $queryType = Arr::get($wheres, 'payment_status', false);
        
        if (isset($wheres) && $queryType === 'abandoned') {
            $wheres['payment_status'] = 'pending';
            $resultQuery = self::makeQueryAbandoned($resultQuery, '<', true);
        }

        if (isset($wheres) && ($queryType === 'all-payments')) {
            unset($wheres['payment_status']);
            $resultQuery->where('wpf_submissions.payment_method', '!=', '');
        }

        if (isset($wheres) && $queryType === 'total') {
            unset($wheres['payment_status']);
        }


        if($queryType !== 'subscription' && $queryType !== 'coupon' && is_array($wheres)) {
            foreach ($wheres as $whereKey => $where) {
                $resultQuery->where('wpf_submissions.' . $whereKey, '=', $where);
            }
        }

        if ($searchString) {
            $resultQuery = $this->searchString($searchString, $resultQuery);
        }

        if ($startDate && $endDate) {
            $resultQuery = $this->dateFilter($resultQuery, $startDate, $endDate);
        }

        // if ($queryType !== null) {
        //     $resultQuery->where('payment_status', $queryType);
        // }

        if ($queryType === 'subscription') {
            $subsModel = new Subscription();
            $subscriptionEntryIds = $subsModel->getSubscriptionEntryIds();

            $resultQuery = $resultQuery->whereIn('wpf_submissions.id', $subscriptionEntryIds);

            // $resultQuery = $resultQuery->where(function($query) use ($subscriptionIds) {
            //     foreach($subscriptionIds as $subscription) {
            //         $query->orWhere('wpf_submissions.id', $subscription);
            //     }
            // });
        }

        if ($queryType === 'coupon') {
            $resultQuery = $resultQuery->whereHas('orderItems', function ($query) {
                $query->where('type', 'discount');
            });
        }
        
        $totalItems = $resultQuery->count();

        if ($perPage) {
            $resultQuery->limit($perPage);
        }
        if ($skip) {
            $resultQuery->offset($skip);
        }

        $results = $resultQuery->get();

        $formattedResults = array();

        foreach ($results as $result) {
            $result->form_data_raw = wppayform_safeUnserialize($result->form_data_raw);
            $result->form_data_formatted = wppayform_safeUnserialize($result->form_data_formatted);
            $result->payment_total += (new Subscription())->getSubscriptionPaymentTotal($result->form_id, $result->id);
            $formattedResults[] = $result;
        }

        return (object) array(
            'items' => $results,
            'total' => $totalItems,
        );
    }

    public static function findMetaValueById($form_id) {
        global $wpdb;
        
        $postTable = esc_sql($wpdb->prefix . 'posts');
        $metaTable = esc_sql($wpdb->prefix . 'postmeta');
        $key = 'wppayform_paymentform_builder_settings';

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Table names are escaped and safe
        $formSettings = $wpdb->get_var(
            $wpdb->prepare(
                "
                SELECT pm.meta_value
                FROM {$postTable} p
                JOIN {$metaTable} pm ON p.ID = pm.post_id
                WHERE p.ID = %d AND pm.meta_key = %s
                ",
                $form_id,
                $key
            )
        );

        $result = [  
            'donation_goals' => null,  
            'show_statistic' => null,  
            'progress_bar' => null  
        ];  
        
        if ($formSettings !== null) {  
            $unserializedData = wppayform_safeUnserialize($formSettings);  
            
            // Find the donation_item field  
            foreach ($unserializedData as $field) {  
                if ($field['type'] === 'donation_item' && isset($field['field_options']['pricing_details'])) {  
                    $pricingDetails = $field['field_options']['pricing_details'];  
                    
                    $result['donation_goals'] = $pricingDetails['donation_goals'] ?? null;  
                    $result['show_statistic'] = $pricingDetails['show_statistic'] ?? null;  
                    $result['progress_bar'] = $pricingDetails['progress_bar'] ?? null;  
                    
                    break;  
                }  
            }  
        }  
        
        return $result; 
    }
    public function getDonationItem($form_id, $searchText = null, $orderByKey = null, $orderByVal = '', $skip = 0, $perPage = null)
    {
        $form_id = sanitize_text_field($form_id);
        $option_key = "wppayform_donation_leaderboard_settings";
        $form_id = $form_id == 0 ? null : $form_id;
        if($form_id != null) {
            $option_key = "wppayform_donation_leaderboard_settings_" . $form_id;
        }

        $leaderboard_settings = get_option($option_key, array(
            'enable_donation_for' => 'all',
            'template_id' => 3,
            'enable_donation_for_specific' => [],
            'orderby' => 'grand_total'
        ));
        
        $donation_for = Arr::get($leaderboard_settings, 'enable_donation_for', 'all');
        $specific_form = Arr::get($leaderboard_settings, 'enable_donation_for_specific', []);

        $searchText = sanitize_text_field($searchText);
        $orderByKey = sanitize_text_field($orderByKey);
        $orderByVal = sanitize_text_field($orderByVal);
        $metaValue = $this->findMetaValueById($form_id);
        $donationGoal = Arr::get($metaValue, 'donation_goals', '');
        $showStatistic = Arr::get($metaValue, 'show_statistic', '');
        $progress_bar = Arr::get($metaValue, 'progress_bar', '');

        $skip = absint($skip);
        $perPage = absint($perPage);
        $perPage = $perPage == 0 ? null : $perPage;
        $query = $this
            ->newQuery()
            ->where('payment_status', 'paid')
            ->where(function ($query) {
                return $query->whereHas('orderItems', function ($query) {
                    $query->where('parent_holder', 'like', 'donation_item%');
                })
                    ->orWhereHas('subscriptionItems', function ($query) {
                        $query->where('element_id', 'like', 'donation_item%');
                    });
            })
            ->withSum(
                [
                    'orderItems' => function ($query) {
                        $query->where('parent_holder', 'like', 'donation_item%');
                    }
                ],
                'line_total'
            )
            ->withSum(
                [
                    'subscriptionItems' => function ($query) {
                        $query->where('element_id', 'like', 'donation_item%');
                    }
                ],
                'payment_total'
            )
            ->when($donation_for == 'specific', function ($query) use ($specific_form) {
                $query->whereIn('form_id', $specific_form);
            })
            ->when($searchText, function ($query, $searchText) {
                $query->where('customer_name', 'like', '%' . $searchText . '%');
            })
            ->when($form_id, function ($query, $form_id) {
                $query->where('form_id', $form_id);
            })
            ->get()
            ->map(function ($item) {
                $item->grand_total = $item->order_items_sum_line_total + $item->subscription_items_sum_payment_total;
                $item->grand_total = $item->grand_total / 100;
                return $item;
            })
            ->groupBy('customer_email')
            ->map(function ($item) use ($form_id) {
                return [
                    'customer_name' => $item->first()->customer_name,
                    "currency" => GeneralSettings::getCurrencySymbol($item->first()->currency),
                    'customer_email' => md5($item->first()->customer_email),
                    'grand_total' => $item->sum('grand_total'),
                    'created_at' => $item->first()->created_at,
                    'donations_count' => $this->newQuery()
                ->where('customer_email', $item->first()->customer_email)
                ->where('form_id', $form_id)
                ->count(),
                ];
            });
        $total = $query->count();
        $total_donations = $query->sum('donations_count');
        $donationItems = $query
            ->skip(null)
            ->take($perPage)
            ->sortBy($orderByKey, SORT_REGULAR, $orderByVal);
        
        $total_raised_amount = 0;

        foreach($donationItems as $donationItem) {
            $total_raised_amount += Arr::get($donationItem, 'grand_total', 0);
        }

        $initial_raised_amount = Arr::get($leaderboard_settings, 'initial_raised_amount', 0);
        $total_raised_amount = intval($total_raised_amount) + intval($initial_raised_amount);

        if ($donationGoal > 0) {  
            $percent = round(($total_raised_amount / $donationGoal) * 100, 2);  
        } else {  
            $percent = 0;
        }  
        $topThreeDonors = $query->sortByDesc('grand_total')->take(3);

        return array(
            'topThreeDonars' => $topThreeDonors->toArray(),
            'donars' => $donationItems->toArray(),
            'has_more_data' => $total > $perPage ? true : false,
            'total' => $total,
            'total_raised_amount' => $total_raised_amount,
            'donation_goal' => $donationGoal,
            'percent' => $percent,
            'total_donations' => $total_donations,
            'show_statistic' => $showStatistic,
            'progress_bar' => $progress_bar
        );
    }

    public function orderItems()
    {
        return $this->hasMany(OrderItem::class, 'submission_id', 'id');
    }

    public function subscriptionItems()
    {
        return $this->hasMany(Subscription::class, 'submission_id', 'id');
    }

    public function getDiscounts ($submissionId, $form_id, $result)
    {
        $discounts = (new OrderItem())->getDiscountItems($submissionId);

            $totalDiscount = 0;
            if (isset($discounts)) {
                foreach ($discounts as $discount) {
                    $totalDiscount += intval($discount->line_total);
                }
            }
            $totalWithoutTax = 0;
            $orderTotal = 0;
            if (!empty($result->order_items)) {
                foreach ($result->order_items as $items) {
                    $orderTotal += intval($items->line_total);
                }
            }

            $subsTotal = intval((new Subscription())->getSubscriptionPaymentTotal($form_id, $submissionId));
            $totalWithoutTax = $orderTotal + $subsTotal;
            $percentDiscount = 0;
            if ($totalWithoutTax) {
                $percentDiscount = intval(($totalDiscount * 100) / $totalWithoutTax, 2);
            }

            return  array(
                'applied' => $discounts,
                'total' => $totalDiscount,
                'percent' => $percentDiscount
            );
    }

    public function getSubmission($submissionId, $with = array())
    {
        $result = $this->select(array('wpf_submissions.*', 'posts.post_title'))
            ->join('posts', 'posts.ID', '=', 'wpf_submissions.form_id')
            ->where('wpf_submissions.id', $submissionId)
            ->first();
        $result->form_data_raw = wppayform_safeUnserialize($result->form_data_raw);
        $result->form_data_formatted = wppayform_safeUnserialize($result->form_data_formatted);
        if ($result->user_id) {
            $result->user_profile_url = get_edit_user_link($result->user_id);
        }

        if (in_array('transactions', $with)) {
            $result->transactions = (new Transaction())->getTransactions($submissionId);
            $submissionModel = new Submission();
            $customerPaymentMethod = $submissionModel->getMeta($submissionId, 'customer_payment_method', 'card');
            $expiringAt = $submissionModel->getMeta($submissionId, 'payment_expiring_at', '');
            
            if (!empty($customerPaymentMethod)) {
                $result->customer_payment_method = $customerPaymentMethod;
            }

            if (!empty($expiringAt)) {
                $result->payment_expiring_at = $expiringAt;
            }
        }

        if (in_array('order_items', $with)) {
            $result->order_items = (new OrderItem())->getSingleOrderItems($submissionId);
        }
        if (in_array('discount', $with)) {
            $result->discounts = $this->getDiscounts($submissionId, $result->form_id, $result);
        }

        if (in_array('tax_items', $with)) {
            $result->tax_items = (new OrderItem())->getTaxOrderItems($submissionId);
        }

        if (in_array('activities', $with)) {
            $result->activities = SubmissionActivity::getSubmissionActivity($submissionId);
        }

        if (in_array('subscriptions', $with)) {
            $subscriptionModel = new Subscription();
            $result->subscriptions = $subscriptionModel->getSubscriptions($result->id);
        }
        if (in_array('refunds', $with)) {
            $refundModel = new Refund();
            $result->refunds = $refundModel->getRefunds($result->id);
            $refundTotal = 0;
            if ($result->refunds) {
                foreach ($result->refunds as $refund) {
                    $refundTotal += $refund->payment_total;
                }
            }
            $result->refundTotal = $refundTotal;
        }

        return $result;
    }

    public function getSubmissionByHash($submissionHash, $with = array())
    {
        $submission = $this->where('submission_hash', $submissionHash)
            ->orderBy('id', 'DESC')
            ->first();

        if ($submission) {
            return $this->getSubmission($submission->id, array('transactions', 'order_items', 'tax_items', 'subscriptions', 'discount'));
        }
        return false;
    }

    public function getTotalCount($formId = false, $paymentStatus = false, $searchString = false, $startDate = null, $endDate = null)
    {
        $query = $this->newQuery();

        if ($formId) {
            $query = $query->where('form_id', $formId);
        }

        if (!empty($paymentStatus)) {
            $query = $this->paymentStatus($paymentStatus, $query);
        }

        if ($searchString) {
            $query = $this->searchString($searchString, $query);
        }

        if ($startDate && $endDate) {
            $query = $this->dateFilter($query, $startDate, $endDate);
        }

        if ($paymentStatus !== null && $paymentStatus !== '') {
            $query->where('payment_status', $paymentStatus);
        }

        return $query->count();
    }

    public function makeQueryAbandoned($query, $condition = '<', $payOnly = true)
    {
        $hour = get_option('wppayform_abandoned_time', 3);

        $beforeHour = intval($hour) * 3600;
        $now = current_time('mysql');
        $formatted_date = wp_date('Y-m-d H:i:s', strtotime($now) - $beforeHour);

        $query->where('wpf_submissions.created_at', $condition, $formatted_date);
        if ($payOnly) {
            $query->where('wpf_submissions.payment_method', '!=', '');
        }
        return $query;
    }


    public function paymentTotal($formId, $paymentStatus = false, $searchString = false, $startDate = null, $endDate = null)
    {
        $paymentTotal = 0;
        //check for donation item
        $hasDonationItem = false;
        $builderSettings = Form::getBuilderSettings($formId);

        foreach ($builderSettings as $key => $value) {
            if ('donation_item' === $value['type']) {
                $hasDonationItem = true;
            }
        }

        if ($hasDonationItem) {
            return $this->donationTotal($formId, $paymentStatus, $searchString, $startDate, $endDate);
        }

        $DB = App::make('db');
        // $query = $this->select($DB->raw('SUM(payment_total) as payment_total'));
        $query = $this->select('*');


        if ($formId) {
            $query = $query->where('form_id', $formId);
        }

        if (!empty($paymentStatus)) {
            $query = $this->paymentStatus($paymentStatus, $query);
        }

        if ($paymentStatus !== null) {
            $query->where('payment_status', $paymentStatus);
        }

        if ($searchString) {
            $query = $this->searchString($searchString, $query);
        }

        if ($startDate && $endDate) {
            $query = $this->dateFilter($query, $startDate, $endDate);
        }

        $results = $query->get();
       
        $paymentTotal = $results->map(function ($result) {
            $result = apply_filters('wppayform/form_entry_recurring_info', $result);
            $submissionId = $result->id;
            $discountItems = (new OrderItem())->getDiscountItems($submissionId);
            $discount_line_total = $discountItems->sum('line_total');
            $submissionTotal = $result->subscription_payment_total ? $result->subscription_payment_total :  $result->payment_total - $discount_line_total;
            return $submissionTotal;
        })->sum();

        return $paymentTotal;
    }



    public function donationTotal($formId, $paymentStatus = false, $searchString = false, $startDate = null, $endDate = null)
    {
        $paymentTotal = 0;
        $DB = App::make('db');
        // $query = $this->select(['currency', 'payment_total']);
        $query = $this->select(['id', 'currency', 'payment_total']);

        if ($formId) {
            $query = $query->where('form_id', $formId);
        }

        // if ($paymentStatus == 'abandoned') {
        //     $query->where('payment_status', 'pending');
        //     $query = $this->makeQueryAbandoned($query, '<', true);
        // } else {
        //     $query->where('payment_status', $paymentStatus);
        // }

        if (!empty($paymentStatus)) {
            $query = $this->paymentStatus($paymentStatus, $query);
        }

        if ($searchString) {
            $query = $this->searchString($searchString, $query);
        }

        if ($startDate && $endDate) {
            $query = $this->dateFilter($query, $startDate, $endDate);
        }

        if ($paymentStatus !== null) {
            $query->where('payment_status', $paymentStatus);
        }


        $result = $query->get()->toArray();

        $currencySettings = Form::getCurrencySettings($formId);
        $baseCurrency = $currencySettings['currency'];
        $apiKey = '';
        $rates = [];

        $hasDifferentCurrencies = 0;
        if (count($result) > 0) {
            foreach ($result as $key => $value) {
                $currency = $result[$key]['currency'];
                if ($currency != $baseCurrency) {
                    $hasDifferentCurrencies = 1;
                }
            }
        }

        if (isset($currencySettings['currency_conversion_api_key']) && $currencySettings['currency_conversion_api_key'] != '') {
            $apiKey = $currencySettings['currency_conversion_api_key'];
            $cachingInterval = 24;
            if (isset($currencySettings['currency_rate_caching_interval'])) {
                $cachingInterval = Arr::get($currencySettings, 'currency_rate_caching_interval');
            }
        }
        if ($apiKey != '' && count($result) > 0 && $hasDifferentCurrencies) {
            $formController = new FormController();
            $rates = $formController->getCurrencyRates($baseCurrency, $apiKey, $cachingInterval, $formId);
            if (count($rates) > 0) {
                foreach ($result as $key => $value) {
                    $currency = $result[$key]['currency'];
                    if ($currency && $currency != $baseCurrency) {
                        $rawamount = number_format(floatval(floatval($result[$key]['payment_total'] / 100) / $rates[$currency]['value']), 2);
                        $amountFormatted = str_replace(',', '', $rawamount);
                        // Now, you can safely multiply the formatted number by 100
                        $amountInCents = $amountFormatted * 100;
                        $paymentTotal += $amountInCents;
                    } else {
                        $paymentTotal += $result[$key]['payment_total'] ? $result[$key]['payment_total'] : 0;
                    }
                }
            } else {
                $paymentTotal = 0;
            }
        }
        if ($paymentTotal == 0) {
            // Do the usual calculation
            // $query = $this->select($DB->raw('SUM(payment_total) as payment_total'));
            $query = $this->select('id', 'payment_total');

            if ($formId) {
                $query = $query->where('form_id', $formId);
            }

            if (!empty($paymentStatus)) {
                $query = $this->paymentStatus($paymentStatus, $query);
            }

            if ($searchString) {
                $query = $this->searchString($searchString, $query);
            }

            if ($startDate && $endDate) {
                $query = $this->dateFilter($query, $startDate, $endDate);
            }

            if ($paymentStatus !== null) {
                $query->where('payment_status', $paymentStatus);
            }

            //     $result = $query->first();

            //     if ($result && $result->payment_total) {
            //         $paymentTotal = $result->payment_total;
            //     }
            // }
            // if (!$paymentStatus || $paymentStatus == 'paid') {
            //     $paymentTotal += (new Subscription())->getDonationSubscriptionTotal($formId, $baseCurrency, $rates);
            // }

            $results = $query->get();

            foreach ($results as $payment) {

                $paymentTotal += $payment->payment_total;
            }
        }

        foreach ($result as $value) {
            $submissionId = $value['id'];
            if (!$paymentStatus || $paymentStatus == 'paid') {
                $paymentTotal += (new Subscription())->getSubscriptionPaymentTotal($formId, $submissionId, $searchString, $startDate, $endDate);
            }
        }

        return $paymentTotal;
    }


    public function updateSubmission($submissionId, $data)
    {
        $data['updated_at'] = current_time('mysql');
        return $this->where('id', $submissionId)->update($data);
    }

    public function getParsedSubmission($submission)
    {
        $elements = get_post_meta($submission->form_id, 'wppayform_paymentform_builder_settings', true);
        if (!$elements) {
            return array();
        }

        $parsedSubmission = array();
        $inputValues = $submission->form_data_formatted;

        foreach ($elements as $element) {
            if (Arr::get($element,'type') == 'container') {
                $columns = Arr::get($element, 'field_options.columns', []);
                foreach ($columns as $column) {
                    $fields = Arr::get($column, 'fields', []);
                    foreach ($fields as $field) {
                        if ($field['group'] == 'input') {
                            $fieldId = Arr::get($field, 'id');
                            $parsedSubmission[$fieldId] = $this->getInputEntryData($field, $inputValues, $submission);
                        }  
                    }
                }
            } else {
                if ($element['group'] == 'input') {
                    $elementId = Arr::get($element, 'id');
                    $parsedSubmission[$elementId] = $this->getInputEntryData($element, $inputValues, $submission);
                }
            }
            
        }
        // dd($parsedSubmission);
        return apply_filters('wppayform/parsed_entry', $parsedSubmission, $submission);
    }

    public function getInputEntryData($element, $inputValues, $submission) {

        $elementId = Arr::get($element, 'id');
        $elementValue = apply_filters(
            'wppayform/rendering_entry_value_' . $element['type'],
            Arr::get($inputValues, $elementId),
            $submission,
            $element
        );
        if (is_array($elementValue)) {
            foreach ($elementValue as $key => $value) {
                if (!is_string($value)) {
                    $elementValue[$key] = '';
                }
            }
            $elementValue = implode(', ', $elementValue);
        }
        return  array(
            'label' => $this->getLabel($element),
            'value' => $elementValue,
            'type' => $element['type']
        );
    }

    public function getUnParsedSubmission($submission)
    {
        $elements = get_post_meta($submission->form_id, 'wppayform_paymentform_builder_settings', true);
        if (!$elements) {
            return array();
        }
        $parsedSubmission = array();

        $inputValues = $submission->form_data_formatted;

        foreach ($elements as $element) {
            if ($element['group'] == 'input') {
                $elementId = Arr::get($element, 'id');
                $elementValue = Arr::get($inputValues, $elementId);

                if (is_array($elementValue)) {
                    $elementValue = implode(', ', $elementValue);
                }
                $parsedSubmission[$elementId] = array(
                    'label' => $this->getLabel($element),
                    'value' => $elementValue,
                    'type' => $element['type']
                );
            }
        }

        return apply_filters('wppayform/unparsed_entry', $parsedSubmission, $submission);
    }

    private function getLabel($element)
    {
        $elementId = Arr::get($element, 'id');
        if (!$label = Arr::get($element, 'field_options.admin_label')) {
            $label = Arr::get($element, 'field_options.label');
        }
        if (!$label) {
            $label = $elementId;
        }
        return $label;
    }

    public function deleteSubmission($submissionId)
    {
        foreach ($submissionId as $value) {
            Submission::where('id', intval($value))
                ->delete();

            OrderItem::where('submission_id', intval($value))
                ->delete();

            Refund::where('submission_id', intval($value))
                ->where('transaction_type', 'one_time')
                ->delete();

            SubmissionActivity::where('submission_id', intval($value))
                ->delete();

            Meta::where('option_id', intval($value))->delete();
        }
    }

    public function getEntryCountByPaymentStatus($formId, $paymentStatuses = array(), $period = 'total')
    {
        $query = $this->where('form_id', $formId);
        $DB = App::make('db');
        if ($paymentStatuses && count($paymentStatuses)) {
            $query->whereIn('payment_status', $paymentStatuses);
        }

        if ($period && $period != 'total') {
            $col = 'created_at';
            if ($period == 'day') {
                $year = "YEAR(`{$col}`) = YEAR(NOW())";
                $month = "MONTH(`{$col}`) = MONTH(NOW())";
                $day = "DAY(`{$col}`) = DAY(NOW())";
                $query->where($DB->raw("{$year} AND {$month} AND {$day}"));
            } elseif ($period == 'week') {
                $query->where(
                    $DB->raw("YEARWEEK(`{$col}`, 1) = YEARWEEK(CURDATE(), 1)")
                );
            } elseif ($period == 'month') {
                $year = "YEAR(`{$col}`) = YEAR(NOW())";
                $month = "MONTH(`{$col}`) = MONTH(NOW())";
                $query->where($DB->raw("{$year} AND {$month}"));
            } elseif ($period == 'year') {
                $query->where($DB->raw("YEAR(`{$col}`) = YEAR(NOW())"));
            }
        }
        return $query->count();
    }

    public function changeEntryStatus($formId, $entryId, $newStatus)
    {
        $this->where('form_id', $formId)
            ->where('id', $entryId)
            ->update(['status' => $newStatus]);
        return $newStatus;
    }

    public function updateMeta($submissionId, $metaKey, $metaValue)
    {
        $exist = Meta::where('meta_group', 'wpf_submissions')
            ->where('option_id', $submissionId)
            ->where('meta_key', $metaKey)
            ->first();

        if ($exist) {
            Meta::where('id', $exist->id)
                ->update([
                    'meta_value' => maybe_serialize($metaValue),
                    'updated_at' => current_time('mysql')
                ]);
        } else {
            Meta::create([
                'meta_key' => $metaKey,
                'option_id' => $submissionId,
                'meta_group' => $this->table,
                'meta_value' => maybe_serialize($metaValue),
                'updated_at' => current_time('mysql'),
                'created_at' => current_time('mysql'),
            ]);
        }
    }

    public function getMeta($submissionId, $metaKey, $default = '')
    {
        $exist = Meta::where('meta_group', $this->metaGroup)
            ->where('option_id', $submissionId)
            ->where('meta_key', $metaKey)
            ->first();

        if ($exist) {
            $value = wppayform_safeUnserialize($exist->meta_value);
            // dd($value);
            if ($value) {
                return $value;
            }
        }

        return $default;
    }

    public static function getByCustomerEmail($filter_data = [])
    {
        $startDate = Arr::get($filter_data, 'start_date');
        $endDate = Arr::get($filter_data, 'end_date');
        
        $DB = App::make('db');
        $customers = Submission::select(
            'currency',
            'customer_email',
            'customer_name',
            $DB->raw("SUM(payment_total) as total_paid"),
            $DB->raw("COUNT(*) as submissions")
        )
            ->whereIn('payment_status', ['paid'])
            ->where('payment_total', '>', 0)
            ->groupBy(['customer_email', 'currency'])
            ->orderBy('submissions', 'desc')
            ->when($startDate && $endDate, function ($query) use ($startDate, $endDate) {
                $query->whereBetween('created_at', [$startDate, $endDate]);
            })
            ->limit(10)
            ->get();

        return $customers;
    }

    public static function getSubmissionWithRelations($submissionId, $formId = null)
    {
        $query = self::with(['subscriptions', 'transactions'])
                    ->where('id', $submissionId);

        if ($formId) {
            $query->where('form_id', $formId);
        }

        return $query->first();
    }
}