<?php

if (!defined('ABSPATH')) {
    die('You are not allowed to call this page directly.');
}

class MeprSubscription extends MeprBaseMetaModel implements MeprProductInterface, MeprTransactionInterface
{
    /**
     * Instance Variables & Methods
     **/

    /**
     * String value for pending subscription status.
     *
     * @var string
     */
    public static $pending_str   = 'pending';

    /**
     * String value for active subscription status.
     *
     * @var string
     */
    public static $active_str    = 'active';

    /**
     * String value for suspended subscription status.
     *
     * @var string
     */
    public static $suspended_str = 'suspended';

    /**
     * String value for cancelled subscription status.
     *
     * @var string
     */
    public static $cancelled_str = 'cancelled';

    /**
     * Array of valid subscription statuses.
     *
     * @var array
     */
    public $statuses;

    /***
     * Instance Methods
     ***/
    /**
     * Constructor for the MeprSubscription class.
     *
     * @param mixed $obj Optional object or ID to initialize the subscription.
     */
    public function __construct($obj = null)
    {
        parent::__construct($obj);
        $this->statuses = [
            self::$pending_str,
            self::$active_str,
            self::$suspended_str,
            self::$cancelled_str,
        ];

        $this->initialize(
            [
                'id'                         => 0,
                'subscr_id'                  => 'mp-sub-' . uniqid(),
                'gateway'                    => 'manual',
                'user_id'                    => 0,
                'product_id'                 => 0,
                'coupon_id'                  => 0,
                'price'                      => 0.00,
                'period'                     => 1,
                'period_type'                => 'months',
                'limit_cycles'               => false,
                'limit_cycles_num'           => 0,
                'limit_cycles_action'        => null,
                'limit_cycles_expires_after' => '1',
                'limit_cycles_expires_type'  => 'days',
                'prorated_trial'             => false,
                'trial'                      => false,
                'trial_days'                 => 0,
                'trial_amount'               => 0.00,
                'trial_tax_amount'           => 0.00,
                'trial_tax_reversal_amount'  => 0.00,
                'trial_total'                => 0.00,
                'status'                     => MeprSubscription::$pending_str,
                'created_at'                 => null,
                'total'                      => 0.00,
                'tax_rate'                   => 0.00,
                'tax_amount'                 => 0.00,
                'tax_reversal_amount'        => 0.00,
                'tax_desc'                   => '',
                'tax_class'                  => 'standard',
                'cc_last4'                   => null,
                'cc_exp_month'               => null,
                'cc_exp_year'                => null,
                'token'                      => null,
                'order_id'                   => 0,
            ],
            $obj
        );
    }

    /**
     * Validates the subscription properties.
     *
     * @return void
     */
    public function validate()
    {
        $p = new MeprProduct();

        $this->validate_not_empty($this->subscr_id, 'subscr_id');
        $this->validate_not_empty($this->gateway, 'gateway');
        $this->validate_is_numeric($this->user_id, 1, null, 'user_id');
        $this->validate_is_numeric($this->product_id, 1, null, 'product_id');
        $this->validate_is_numeric($this->coupon_id, 0, null, 'coupon_id'); // Accept no coupon (0) here.
        $this->validate_is_currency($this->price, 0, null, 'price');
        $this->validate_is_numeric($this->period, 1, null, 'period');
        $this->validate_is_in_array($this->period_type, $p->period_types, 'period_type');

        $this->validate_is_bool($this->limit_cycles, 'limit_cycles');
        if ($this->limit_cycles) {
            $this->validate_is_numeric($this->limit_cycles_num, 1, null, 'limit_cycles_num');
            $this->validate_is_in_array($this->limit_cycles_action, $this->limit_cycles_actions, 'limit_cycles_action');
            if ('expires_after' == $this->limit_cycles_action) {
                $this->validate_is_numeric($this->limit_cycles_expires_after, 1, null, 'limit_cycles_expires_after');
                $this->validate_not_empty($this->limit_cycles_expires_type, 'limit_cycles_expires_type');
            }
        }

        $this->validate_is_bool($this->prorated_trial, 'prorated_trial');

        $this->validate_is_bool($this->trial, 'trial');
        if ($this->trial) {
            $this->validate_is_numeric($this->trial_days, 0.00, null, 'trial_days');
            $this->validate_is_currency($this->trial_amount, 0.00, null, 'trial_amount');
            $this->validate_is_currency($this->trial_tax_amount, 0.00, null, 'trial_tax_amount');
            $this->validate_is_currency($this->trial_total, 0.00, null, 'trial_total');
        }

        $this->validate_is_in_array($this->status, $this->statuses, 'status');
        if (!empty($this->created_at)) {
            $this->validate_is_date($this->created_at, 'created_at');
        }

        $this->validate_is_currency($this->total, 0, null, 'total');
        $this->validate_is_numeric($this->tax_rate, 0, null, 'tax_rate');
        $this->validate_is_currency($this->tax_amount, 0.00, null, 'tax_amount');
        // $this->validate_not_empty($this->tax_desc, 'tax_desc');
        $this->validate_not_empty($this->tax_class, 'tax_class');

        if (!empty($this->cc_last4)) {
            $this->cc_last4 = str_pad(trim($this->cc_last4), 4, '0', STR_PAD_LEFT);
            $this->validate_regex('/^\d{4}$/', $this->cc_last4, 'cc_last4');
        }
        if (!empty($this->cc_exp_month)) {
            $this->validate_regex('/^0?([1-9]|1[012])$/', trim($this->cc_exp_month), 'cc_exp_month');
        }
        if (!empty($this->cc_exp_year)) {
            $this->validate_regex('/^\d{2}(\d{2})?$/', $this->cc_exp_year, 'cc_exp_year');
        }
    }

    /**
     * Stores the subscription in the database.
     *
     * @return integer The ID of the stored subscription.
     */
    public function store()
    {
        $old_sub = new self($this->id);

        if (isset($this->id) && !is_null($this->id) && (int)$this->id > 0) {
            $this->id = self::update($this);
        } else {
            $this->id = self::create($this);
        }

        // Keep this hook at the bottom of this function
        // This should happen after everything is done processing including the subscr txn_count.
        MeprHooks::do_action('mepr_subscription_transition_status', $old_sub->status, $this->status, $this);
        MeprHooks::do_action('mepr_subscription_stored', $this);
        MeprHooks::do_action('mepr_subscription_saved', $this);
        MeprHooks::do_action('mepr_subscription_status_' . $this->status, $this);
        MeprHooks::do_action('mepr_subscription_compare', $this, $old_sub);

        // DEPRECATED ... please use the actions above instead.
        MeprHooks::do_action('mepr-subscr-transition-status', $old_sub->status, $this->status, $this);
        MeprHooks::do_action('mepr-subscr-store', $this);
        MeprHooks::do_action('mepr-subscr-status-' . $this->status, $this);

        return $this->id;
    }

    /**
     * Creates a new subscription record in the database.
     *
     * @param MeprSubscription $sub The subscription object to create.
     *
     * @return integer The ID of the created subscription.
     */
    public static function create($sub)
    {
        $mepr_db = new MeprDb();

        if (is_null($sub->created_at)) {
            $sub->created_at = MeprUtils::db_now();
        }

        $args = $sub->get_values();

        return MeprHooks::apply_filters('mepr_create_subscription', $mepr_db->create_record($mepr_db->subscriptions, $args, false), $args, $sub->user_id);
    }

    /**
     * Updates an existing subscription record in the database.
     *
     * @param MeprSubscription $sub The subscription object to update.
     *
     * @return integer The ID of the updated subscription.
     */
    public static function update($sub)
    {
        $mepr_db = new MeprDb();
        $args    = $sub->get_values();

        $str = MeprUtils::object_to_string($args);

        return MeprHooks::apply_filters('mepr_update_subscription', $mepr_db->update_record($mepr_db->subscriptions, $sub->id, $args), $args, $sub->user_id);
    }

    /**
     * Destroys the subscription and its associated transactions.
     *
     * @return boolean True on success, false on failure.
     */
    public function destroy()
    {
        $mepr_db = new MeprDb();
        $id      = $this->id;
        $args    = compact('id');
        $sub     = self::get_one($id);

        $txns = MeprTransaction::get_all_by_subscription_id($id);

        if (!empty($txns)) {
            foreach ($txns as $txn) {
                $kill_txn = new MeprTransaction($txn->id);
                $kill_txn->destroy();
            }
        }

        $subscription_id = $this->id;

        MeprHooks::do_action('mepr_subscription_pre_delete', $subscription_id);

        $res = $mepr_db->delete_records($mepr_db->subscriptions, $args);

        MeprHooks::do_action('mepr_subscription_deleted', $this);

        return $res;
    }

    /**
     * Retrieves the attributes of the subscription.
     *
     * @return array The attributes of the subscription.
     */
    public function get_attrs()
    {
        return array_keys((array)$this->rec);
    }

    /**
     * Returns the trial days for the subscription.
     *
     * @param integer $value The value to extend the trial by.
     *
     * @return integer The trial days.
     */
    public function get_extend_trial_days($value)
    {
        return $this->trial ? $value : 0;
    }

    /**
     * Returns the trial amount based on the trial status.
     *
     * @param float $value The value to extend the trial by.
     *
     * @return float The trial amount.
     */
    public function get_extend_trial_amount($value)
    {
        return $this->trial ? $value : 0.00;
    }

    /**
     * Checks if a subscription exists by ID.
     *
     * @param integer $id The ID of the subscription.
     *
     * @return boolean True if the subscription exists, false otherwise.
     */
    public static function exists($id)
    {
        $mepr_db = MeprDb::fetch();
        return $mepr_db->record_exists($mepr_db->subscriptions, compact('id'));
    }

    /**
     * Retrieves a single subscription by ID.
     *
     * @param integer $id          The ID of the subscription.
     * @param string  $return_type The return type (default: OBJECT).
     *
     * @return mixed The subscription object or array.
     */
    public static function get_one($id, $return_type = OBJECT)
    {
        $mepr_db = new MeprDb();
        $args    = compact('id');

        return $mepr_db->get_one_record($mepr_db->subscriptions, $args, $return_type);
    }

    /**
     * Get a subscription by subscription ID.
     *
     * @param string $subscr_id The subscription ID to search for.
     *
     * @return MeprSubscription|false The subscription instance, or false if not found.
     */
    public static function get_one_by_subscr_id($subscr_id)
    {
        global $wpdb;
        $mepr_db = new MeprDb();

        $sql = "
      SELECT sub.id
        FROM {$mepr_db->subscriptions} AS sub
       WHERE sub.subscr_id=%s
       ORDER BY sub.id DESC
       LIMIT 1
    ";

        $sql = $wpdb->prepare($sql, $subscr_id);
        $sub_id = $wpdb->get_var($sql);
        if ($sub_id) {
            return new MeprSubscription($sub_id);
        } else {
            return false;
        }
    }

    /**
     * Searches for subscriptions by subscription ID.
     *
     * @param string $search The subscription ID to search for.
     *
     * @return array The list of matching subscriptions.
     */
    public static function search_by_subscr_id($search)
    {
        global $wpdb;

        $mepr_db = new MeprDb();

        $sql  = "SELECT id FROM {$mepr_db->subscriptions} WHERE subscr_id LIKE %s";
        $sql  = $wpdb->prepare($sql, "{$search}%");
        $ids  = $wpdb->get_col($sql);
        $subs = [];

        if (!empty($ids)) {
            foreach ($ids as $id) {
                $subs[] = new MeprSubscription($id);
            }
        }

        return $subs;
    }

    /**
     * Retrieves all active subscriptions by user ID.
     *
     * @param integer $user_id         The user ID.
     * @param string  $order           The order clause.
     * @param string  $limit           The limit clause.
     * @param boolean $count           Whether to return the count only.
     * @param boolean $look_for_lapsed Whether to look for lapsed subscriptions.
     *
     * @return mixed The list of active subscriptions or the count.
     */
    public static function get_all_active_by_user_id($user_id, $order = '', $limit = '', $count = false, $look_for_lapsed = false)
    {
        global $wpdb;

        $mepr_db = new MeprDb();

        $order  = empty($order) ? '' : " ORDER BY {$order}";
        $limit  = empty($limit) ? '' : " LIMIT {$limit}";
        $fields = $count ? 'COUNT(*)' : 'sub.*';

        $sql = "
      SELECT {$fields}
        FROM {$mepr_db->subscriptions} AS sub
          JOIN {$mepr_db->transactions} AS t
            ON sub.id = t.subscription_id
        WHERE t.user_id = %d
          AND t.status IN(%s,%s)
          AND sub.status <> %s
    ";

        // Canceled subscriptions are not "lapsed" - don't change this or it breaks sub -> lifetime -> sub upgrades.
        if ($look_for_lapsed) {
            $sql .= "
          AND ( sub.status <> %s OR sub.status = %s and t.expires_at > %s )
        {$order}{$limit}
      ";

            $sql = $wpdb->prepare($sql, $user_id, MeprTransaction::$complete_str, MeprTransaction::$confirmed_str, MeprSubscription::$pending_str, MeprSubscription::$cancelled_str, MeprSubscription::$cancelled_str, MeprUtils::db_now());
        } else {
            $sql .= "
          AND t.expires_at > %s
        {$order}{$limit}
      ";

            $sql = $wpdb->prepare($sql, $user_id, MeprTransaction::$complete_str, MeprTransaction::$confirmed_str, MeprSubscription::$pending_str, MeprUtils::db_now());
        }

        if ($count) {
            return $wpdb->get_var($sql);
        } else {
            return $wpdb->get_results($sql);
        }
    }

    /**
     * Retrieves all subscriptions.
     *
     * @return array The list of all subscriptions.
     */
    public static function get_all()
    {
        global $wpdb;

        $mepr_db = new MeprDb();

        $sql = "SELECT * FROM {$mepr_db->subscriptions}";

        return $wpdb->get_results($sql);
    }

    /**
     * Checks if a subscription exists by subscription ID.
     *
     * @param string $subscr_id The subscription ID.
     *
     * @return boolean True if the subscription exists, false otherwise.
     */
    public static function subscription_exists($subscr_id)
    {
        return is_object(self::get_one_by_subscr_id($subscr_id));
    }

    /**
     * Sets the membership ID to 0 if for some reason a membership is deleted.
     *
     * @param integer $id The ID of the subscription.
     *
     * @return void
     */
    public static function nullify_product_id_on_delete($id)
    {
        global $wpdb, $post_type;

        $mepr_db = new MeprDb();

        $q = "
      UPDATE {$mepr_db->subscriptions}
         SET product_id = 0
       WHERE product_id = %d
    ";

        $wpdb->query($wpdb->prepare($q, $id));
    }

    /**
     * Sets the user ID to 0 if for some reason a user is deleted.
     *
     * @param integer $id The ID of the subscription.
     *
     * @return void
     */
    public static function nullify_user_id_on_delete($id)
    {
        global $wpdb;

        $mepr_db = new MeprDb();

        $q = "
      UPDATE {$mepr_db->subscriptions}
         SET user_id = 0
       WHERE user_id = %d
    ";

        $wpdb->query($wpdb->prepare($q, $id));
    }

    /**
     * Retrieves the account subscription table.
     *
     * @param string  $order_by     The column to order by.
     * @param string  $order        The order direction.
     * @param string  $paged        The page number.
     * @param string  $search       The search term.
     * @param string  $search_field The field to search in.
     * @param integer $perpage      The number of items per page.
     * @param boolean $countonly    Whether to return the count only.
     * @param array   $params       Additional parameters.
     * @param string  $encols       The columns to include.
     *
     * @return array The subscription table data.
     */
    public static function account_subscr_table(
        $order_by = '',
        $order = '',
        $paged = '',
        $search = '',
        $search_field = 'any',
        $perpage = 10,
        $countonly = false,
        $params = null,
        $encols = 'all'
    ) {
        global $wpdb;

        // Get the individual queries.
        $lsql = self::lifetime_subscr_table(
            '',
            '',
            '',
            $search,
            $search_field,
            0,
            $countonly,
            $params,
            $encols,
            true
        );

        $sql = self::subscr_table(
            '',
            '',
            '',
            $search,
            $search_field,
            0,
            $countonly,
            $params,
            $encols,
            true
        );

        /*
            -- Ordering parameters --
        */
        // Parameters that are going to be used to order the result.
        $order_by = (!empty($order_by) and !empty($order)) ? ($order_by = ' ORDER BY ' . $order_by . ' ' . $order) : '';

        // Page Number.
        if (empty($paged) or !is_numeric($paged) or $paged <= 0) {
            $paged = 1;
        }

        $limit = '';
        // Adjust the query to take pagination into account.
        if (!empty($paged) and !empty($perpage)) {
            $offset = ($paged - 1) * $perpage;
            $limit  = ' LIMIT ' . (int)$offset . ',' . (int)$perpage;
        }

        $wpdb->query('SET SQL_BIG_SELECTS=1');

        $asql  = "({$lsql['query']}) UNION ({$sql['query']}){$order_by}{$limit}";
        $acsql = "SELECT (({$lsql['total_query']}) + ({$sql['total_query']}))";

        $results = $wpdb->get_results($asql);
        $count   = $wpdb->get_var($acsql);

        return compact('results', 'count');
    }

    /**
     * Retrieves the subscription table.
     *
     * @param string  $order_by     The column to order by.
     * @param string  $order        The order direction.
     * @param string  $paged        The page number.
     * @param string  $search       The search term.
     * @param string  $search_field The field to search in.
     * @param integer $perpage      The number of items per page.
     * @param boolean $countonly    Whether to return the count only.
     * @param array   $params       Additional parameters.
     * @param string  $encols       The columns to include.
     * @param boolean $queryonly    Whether to return the query only.
     *
     * @return array The subscription table data.
     */
    public static function subscr_table(
        $order_by = '',
        $order = '',
        $paged = '',
        $search = '',
        $search_field = '',
        $perpage = 10,
        $countonly = false,
        $params = null,
        $encols = 'all',
        $queryonly = false
    ) {
        global $wpdb;
        $mepr_options = MeprOptions::fetch();
        $pmt_methods  = $mepr_options->payment_methods();
        $mepr_db      = new MeprDb();
        $en           = function ($c, $e) {
            return (!is_array($e) || in_array($c, $e));
        };

        if (is_null($params)) {
            $params = $_GET;
        }

        if (!empty($pmt_methods)) {
            $gateway = '(SELECT CASE sub.gateway';

            foreach ($pmt_methods as $method) {
                $gateway .= $wpdb->prepare(' WHEN %s THEN %s', $method->id, "{$method->label} ({$method->name})");
            }

            $gateway .= $wpdb->prepare(' ELSE %s END)', __('Unknown', 'memberpress'));
        } else {
            $gateway = 'sub.gateway';
        }

        // The transaction count.
        $txn_count = $wpdb->prepare(
            "
      (SELECT COUNT(*)
         FROM {$mepr_db->transactions} AS txn_cnt
        WHERE txn_cnt.subscription_id=sub.id
          AND txn_cnt.status=%s)",
            MeprTransaction::$complete_str
        );

        $active = $wpdb->prepare(
            '
      (SELECT
         CASE
           WHEN expiring_txn.expires_at = 0
             OR expiring_txn.expires_at = %s
           THEN %s
           WHEN expiring_txn.expires_at IS NULL
             OR expiring_txn.expires_at < %s
           THEN %s
           WHEN expiring_txn.status IN (%s,%s)
            AND expiring_txn.txn_type IN (%s,%s,%s)
           THEN %s
         ELSE %s
       END
      )',
            MeprUtils::db_lifetime(),
            '<span class="mepr-active">' . __('Yes', 'memberpress') . '</span>',
            MeprUtils::db_now(),
            '<span class="mepr-inactive">' . __('No', 'memberpress') . '</span>',
            MeprTransaction::$confirmed_str,
            MeprTransaction::$complete_str,
            MeprTransaction::$subscription_confirmation_str,
            MeprTransaction::$sub_account_str,
            MeprTransaction::$woo_txn_str,
            '<span class="mepr-active">' . __('Yes', 'memberpress') . '</span>',
            '<span class="mepr-active">' . __('Yes', 'memberpress') . '</span>'
        );

        $fname = "
      (SELECT um_fname.meta_value
         FROM {$wpdb->usermeta} AS um_fname
        WHERE um_fname.user_id = u.ID
          AND um_fname.meta_key = 'first_name'
        LIMIT 1)
    ";

        $lname = "
      (SELECT um_lname.meta_value
         FROM {$wpdb->usermeta} AS um_lname
        WHERE um_lname.user_id = u.ID
          AND um_lname.meta_key = 'last_name'
        LIMIT 1)
    ";

        $cols = ['sub_type' => "'subscription'"];
        if ($en('id', $encols)) {
            $cols['id'] = 'sub.id';
        }
        if ($en('user_email', $encols)) {
            $cols['user_email'] = 'u.user_email';
        }
        if ($en('gateway', $encols)) {
            $cols['gateway'] = 'sub.gateway';
        }
        if ($en('member', $encols)) {
            $cols['member'] = 'u.user_login';
        }
        if ($en('first_name', $encols)) {
            $cols['first_name'] = $fname;
        }
        if ($en('last_name', $encols)) {
            $cols['last_name'] = $lname;
        }
        if ($en('product_name', $encols)) {
            $cols['product_name'] = 'prd.post_title';
        }
        if ($en('first_txn_id', $encols)) {
            $cols['first_txn_id'] = 'first_txn.id';
        }
        if ($en('latest_txn_id', $encols)) {
            $cols['latest_txn_id'] = 'last_txn.id';
        }
        if ($en('expiring_txn_id', $encols)) {
            $cols['expiring_txn_id'] = 'expiring_txn.id';
        }
        if ($en('txn_count', $encols)) {
            $cols['txn_count'] = $txn_count;
        }
        if ($en('expires_at', $encols)) {
            $cols['expires_at'] = 'expiring_txn.expires_at';
        }

        $tmp_sub = new MeprSubscription();
        $pms     = $tmp_sub->get_attrs();

        // Add postmeta columns.
        foreach ($pms as $slug) {
            if ($en($slug, $encols)) {
                $cols[$slug] = "sub.{$slug}";
            }
        }

        if (wp_doing_ajax()) {
            $stripe_customer_id_keys = $wpdb->get_col($wpdb->prepare(
                "SELECT DISTINCT meta_key FROM {$wpdb->usermeta} WHERE meta_key LIKE %s",
                $wpdb->esc_like('_mepr_stripe_customer_id_') . '%'
            ));

            if (is_array($stripe_customer_id_keys)) {
                foreach ($stripe_customer_id_keys as $stripe_customer_id_key) {
                    if ($en($stripe_customer_id_key, $encols)) {
                        $cols[$stripe_customer_id_key] = $wpdb->prepare(
                            "
                            IFNULL(
                                (
                                    SELECT meta_value
                                    FROM {$wpdb->usermeta}
                                    WHERE meta_key = %s
                                    AND user_id = u.ID
                                    LIMIT 1
                                ),
                                ''
                            )
                            ",
                            $stripe_customer_id_key
                        );
                    }
                }
            }
        }

        // Very important this comes after the meta columns ...
        // must maintain same order as the lifetime table.
        if ($en('active', $encols)) {
            $cols['active'] = $active;
        }

        $args = [];

        if (array_key_exists('member', $params)) {
            if (empty($params['member'])) {
                $params['member'] = null;
            }
            $args[] = $wpdb->prepare('u.user_login = %s', $params['member']);
        }

        if (isset($params['subscription']) && !empty($params['subscription'])) {
            $args[] = $wpdb->prepare('sub.id = %d', $params['subscription']);
        }

        if (isset($params['prd_id']) && is_numeric($params['prd_id'])) {
            $args[] = $wpdb->prepare('sub.product_id = %d', $params['prd_id']);
        }

        if (isset($params['membership']) && is_numeric($params['membership'])) {
            $args[] = $wpdb->prepare('sub.product_id = %d', $params['membership']);
        }

        if (isset($params['status']) && $params['status'] != 'all') {
            $args[] = $wpdb->prepare('sub.status = %s', $params['status']);
        }

        if (isset($params['gateway']) && $params['gateway'] != 'all') {
            $args[] = $wpdb->prepare('sub.gateway = %s', $params['gateway']);
        }

        if (isset($params['statuses']) && !empty($params['statuses'])) {
            $qry = [];

            foreach ($params['statuses'] as $st) {
                $qry[] = $wpdb->prepare('sub.status = %s', $st);
            }

            $args[] = '(' . implode(' OR ', $qry) . ')';
        }

        $joins = [];

        $important_joins = ['status','user_id','product_id'];

        $joins[] = "/* IMPORTANT */ LEFT JOIN {$wpdb->users} AS u ON u.ID = sub.user_id";
        $joins[] = "/* IMPORTANT */ LEFT JOIN {$wpdb->posts} AS prd ON prd.ID = sub.product_id";

        // The first transaction.
        $joins[] = $wpdb->prepare(
            "LEFT JOIN {$mepr_db->transactions} AS first_txn
         ON first_txn.id=(
           SELECT ft1.id
             FROM {$mepr_db->transactions} AS ft1
            WHERE ft1.subscription_id=sub.id
              AND ft1.status IN (%s,%s)
            ORDER BY ft1.id ASC
            LIMIT 1
         )",
            MeprTransaction::$confirmed_str,
            MeprTransaction::$complete_str
        );

        // The last transaction made.
        $joins[] = $wpdb->prepare(
            "LEFT JOIN {$mepr_db->transactions} AS last_txn
         ON last_txn.id=(
           SELECT lt1.id
             FROM {$mepr_db->transactions} AS lt1
            WHERE lt1.subscription_id=sub.id
              AND lt1.status IN (%s,%s)
            ORDER BY lt1.id DESC
            LIMIT 1
         )",
            MeprTransaction::$confirmed_str,
            MeprTransaction::$complete_str
        );

        // The transaction associated with this subscription with the latest expiration date.
        $joins[] = $wpdb->prepare(
            "LEFT JOIN {$mepr_db->transactions} AS expiring_txn
         ON expiring_txn.id = (
           SELECT t.id
            FROM {$mepr_db->transactions} AS t
           WHERE t.subscription_id=sub.id
             AND t.status IN (%s,%s)
             AND ( t.expires_at = %s
                   OR ( t.expires_at <> %s
                        AND t.expires_at=(
                          SELECT MAX(t2.expires_at)
                            FROM {$mepr_db->transactions} as t2
                           WHERE t2.subscription_id=sub.id
                             AND t2.status IN (%s,%s)
                        )
                      )
                 )
           ORDER BY t.expires_at
           LIMIT 1
         )",
            MeprTransaction::$confirmed_str,
            MeprTransaction::$complete_str,
            MeprUtils::db_lifetime(),
            MeprUtils::db_lifetime(),
            MeprTransaction::$confirmed_str,
            MeprTransaction::$complete_str
        );

        return MeprDb::list_table(
            MeprHooks::apply_filters('mepr_recurring_subscriptions_table_cols', $cols),
            MeprHooks::apply_filters('mepr_recurring_subscriptions_table_from', "{$mepr_db->subscriptions} AS sub"),
            MeprHooks::apply_filters('mepr_recurring_subscriptions_table_joins', $joins),
            MeprHooks::apply_filters('mepr_recurring_subscriptions_table_args', $args),
            $order_by,
            $order,
            $paged,
            $search,
            $search_field,
            $perpage,
            $countonly,
            $queryonly
        );
    }

    // Okay, these are actually transactions but to the unwashed masses ... they're subscriptions.
    /**
     * Generates a table of lifetime subscriptions.
     *
     * @param string  $order_by     The column to order by.
     * @param string  $order        The order to sort the results.
     * @param integer $paged        The page number to display.
     * @param string  $search       The search query.
     * @param string  $search_field The field to search.
     * @param integer $perpage      The number of results per page.
     * @param boolean $countonly    Whether to count only.
     * @param array   $params       The parameters.
     * @param string  $encols       The columns to display.
     * @param boolean $queryonly    Whether to return a query only.
     *
     * @return array The list of lifetime subscriptions.
     */
    public static function lifetime_subscr_table(
        $order_by = '',
        $order = '',
        $paged = '',
        $search = '',
        $search_field = 'any',
        $perpage = 10,
        $countonly = false,
        $params = null,
        $encols = 'all',
        $queryonly = false
    ) {
        global $wpdb;
        $mepr_options = MeprOptions::fetch();
        $pmt_methods  = $mepr_options->payment_methods();
        $mepr_db      = new MeprDb();
        $en           = function ($c, $e) {
            return (!is_array($e) || in_array($c, $e));
        };

        if (is_null($params)) {
            $params = $_GET;
        }

        if (!empty($pmt_methods)) {
            $gateway = '(SELECT CASE txn.gateway';

            foreach ($pmt_methods as $method) {
                $gateway .= $wpdb->prepare(' WHEN %s THEN %s', $method->id, "{$method->label} ({$method->name})");
            }

            $gateway .= $wpdb->prepare(' ELSE %s END)', __('Unknown', 'memberpress'));
        } else {
            $gateway = 'txn.gateway';
        }

        $fname = "(SELECT um_fname.meta_value FROM {$wpdb->usermeta} AS um_fname WHERE um_fname.user_id = u.ID AND um_fname.meta_key = 'first_name' LIMIT 1)";
        $lname = "(SELECT um_lname.meta_value FROM {$wpdb->usermeta} AS um_lname WHERE um_lname.user_id = u.ID AND um_lname.meta_key = 'last_name' LIMIT 1)";

        $cols = ['sub_type' => "'transaction'"];

        if ($en('id', $encols)) {
            $cols['id'] = 'txn.id';
        }
        if ($en('user_email', $encols)) {
            $cols['user_email'] = 'u.user_email';
        }
        if ($en('gateway', $encols)) {
            $cols['gateway'] = $gateway;
        }
        if ($en('member', $encols)) {
            $cols['member'] = 'u.user_login';
        }
        if ($en('first_name', $encols)) {
            $cols['first_name'] = $fname;
        }
        if ($en('last_name', $encols)) {
            $cols['last_name'] = $lname;
        }
        if ($en('product_name', $encols)) {
            $cols['product_name'] = 'prd.post_title';
        }
        if ($en('first_txn_id', $encols)) {
            $cols['first_txn_id'] = 'txn.id';
        }
        if ($en('latest_txn_id', $encols)) {
            $cols['latest_txn_id'] = 'txn.id';
        }
        if ($en('expiring_txn_id', $encols)) {
            $cols['expiring_txn_id'] = 'txn.id';
        }
        if ($en('txn_count', $encols)) {
            $cols['txn_count'] = $wpdb->prepare('%s', 1);
        }
        if ($en('expires_at', $encols)) {
            $cols['expires_at'] = 'txn.expires_at';
        }

        if ($en('subscr_id', $encols)) {
            $cols['subscr_id'] = 'txn.trans_num';
        }
        if ($en('user_id', $encols)) {
            $cols['user_id'] = 'txn.user_id';
        }
        if ($en('product_id', $encols)) {
            $cols['product_id'] = 'txn.product_id';
        }
        if ($en('coupon_id', $encols)) {
            $cols['coupon_id'] = 'txn.coupon_id';
        }
        if ($en('coupon', $encols)) {
            $cols['coupon'] = 'c.post_title';
        }
        if ($en('price', $encols)) {
            $cols['price'] = 'txn.amount';
        }
        if ($en('period', $encols)) {
            $cols['period'] = $wpdb->prepare('%d', 1);
        }
        if ($en('period_type', $encols)) {
            $cols['period_type'] = $wpdb->prepare('%s', 'lifetime');
        }
        if ($en('prorated_trial', $encols)) {
            $cols['prorated_trial'] = $wpdb->prepare('%d', 0);
        }
        if ($en('trial', $encols)) {
            $cols['trial'] = $wpdb->prepare('%d', 0);
        }
        if ($en('trial_days', $encols)) {
            $cols['trial_days'] = $wpdb->prepare('%d', 0);
        }
        if ($en('trial_amount', $encols)) {
            $cols['trial_amount'] = $wpdb->prepare('%f', 0.00);
        }
        if ($en('trial_tax_amount', $encols)) {
            $cols['trial_tax_amount'] = $wpdb->prepare('%f', 0.00);
        }
        if ($en('trial_total', $encols)) {
            $cols['trial_total'] = $wpdb->prepare('%f', 0.00);
        }
        if ($en('status', $encols)) {
            $cols['status'] = $wpdb->prepare('%s', __('None', 'memberpress'));
        }
        if ($en('created_at', $encols)) {
            $cols['created_at'] = 'txn.created_at';
        }
        if ($en('active', $encols)) {
            $cols['active'] = $wpdb->prepare(
                '
        (SELECT
           CASE
             WHEN txn.status IN (%s,%s)
              AND ( txn.expires_at = %s OR
                    txn.expires_at >= %s )
             THEN %s
           ELSE %s
         END)',
                MeprTransaction::$complete_str,
                MeprTransaction::$confirmed_str,
                MeprUtils::db_lifetime(),
                MeprUtils::db_now(),
                '<span class="mepr-active">' . __('Yes', 'memberpress') . '</span>',
                '<span class="mepr-inactive">' . __('No', 'memberpress') . '</span>'
            );
        }

        $args = ['(txn.subscription_id IS NULL OR txn.subscription_id <= 0)'];

        if (array_key_exists('member', $params)) {
            if (empty($params['member'])) {
                $params['member'] = null;
            }
            $args[] = $wpdb->prepare('u.user_login = %s', $params['member']);
        }

        if (isset($params['subscription']) and !empty($params['subscription'])) {
            $args[] = $wpdb->prepare('txn.id = %d', $params['subscription']);
        }

        if (isset($params['prd_id']) && is_numeric($params['prd_id'])) {
            $args[] = $wpdb->prepare('prd.ID = %d', $params['prd_id']);
        }

        if (isset($params['membership']) && is_numeric($params['membership'])) {
            $args[] = $wpdb->prepare('prd.ID = %d', $params['membership']);
        }

        if (isset($params['gateway']) && $params['gateway'] != 'all') {
            $args[] = $wpdb->prepare('txn.gateway = %s', $params['gateway']);
        }

        if (isset($params['statuses']) && !empty($params['statuses'])) {
            $qry = [];

            foreach ($params['statuses'] as $st) {
                // Map subscription status to transaction status.
                $txn_status = MeprTransaction::map_subscr_status($st);

                if (!$txn_status) {
                    continue;
                }

                if (!is_array($txn_status)) {
                    $txn_status = [$txn_status];
                }

                foreach ($txn_status as $txn_st) {
                    $qry[] = $wpdb->prepare('txn.status=%s', $txn_st);
                }
            }

            $args[] = '(' . implode(' OR ', $qry) . ')';
        }

        $joins   = [];
        $joins[] = "/* IMPORTANT */ LEFT JOIN {$wpdb->users} AS u ON u.ID = txn.user_id";
        $joins[] = "/* IMPORTANT */ LEFT JOIN {$wpdb->posts} AS prd ON prd.ID = txn.product_id";
        $joins[] = "/* IMPORTANT */ LEFT JOIN {$wpdb->posts} AS c ON c.ID = txn.coupon_id";

        if ($en('period_type', $encols)) {
            $joins[] = $wpdb->prepare("LEFT JOIN {$wpdb->postmeta} AS pm_period_type ON pm_period_type.post_id = prd.ID AND pm_period_type.meta_key = %s", MeprProduct::$period_type_str);
        }

        return MeprDb::list_table(
            MeprHooks::apply_filters('mepr_nonrecurring_subscriptions_table_cols', $cols),
            MeprHooks::apply_filters('mepr_nonrecurring_subscriptions_table_from', "{$mepr_db->transactions} AS txn"),
            MeprHooks::apply_filters('mepr_nonrecurring_subscriptions_table_joins', $joins),
            MeprHooks::apply_filters('mepr_nonrecurring_subscriptions_table_args', $args),
            $order_by,
            $order,
            $paged,
            $search,
            $search_field,
            $perpage,
            $countonly,
            $queryonly
        );
    }

    /**
     * Retrieves the user associated with the subscription.
     *
     * @param boolean $force Whether to force a new user object.
     *
     * @return MeprUser The user object.
     */
    public function user($force = false)
    {
        // Don't do static caching stuff here.
        return new MeprUser($this->user_id);
    }

    /**
     * Retrieves the product associated with the subscription.
     *
     * @return MeprProduct The product object.
     */
    public function product()
    {
        // Don't do static caching stuff here.
        return MeprHooks::apply_filters('mepr_subscription_product', new MeprProduct($this->product_id), $this);
    }

    /**
     * Retrieves the membership group associated with the subscription through its product.
     *
     * @return MeprGroup|false The group object or false if not found.
     */
    public function group()
    {
        $prd = $this->product();

        return $prd->group();
    }

    /**
     * Retrieves the coupon associated with the subscription.
     *
     * @return MeprCoupon|false The coupon object or false if not found.
     */
    public function coupon()
    {
        // Don't do static caching stuff here.
        if (!isset($this->coupon_id) || (int)$this->coupon_id <= 0) {
            return false;
        }

        $coupon = new MeprCoupon($this->coupon_id);

        if (!isset($coupon->ID) || $coupon->ID <= 0) {
            return false;
        }

        return $coupon;
    }

    /**
     * Get the order associated with this subscription
     *
     * @return MeprOrder|false
     */
    public function order()
    {
        // Don't do static caching stuff here.
        if (empty($this->order_id)) {
            return false;
        }

        $order = new MeprOrder($this->order_id);

        if ((int) $order->id <= 0) {
            return false;
        }

        return $order;
    }

    /**
     * Retrieves the first transaction associated with the subscription.
     *
     * @return MeprTransaction|false The first transaction object or false if not found.
     */
    public function first_txn()
    {
        $first_txn_id = $this->first_txn_id;
        return empty($first_txn_id) ? false : new MeprTransaction($this->first_txn_id);
    }

    /**
     * Retrieves the latest transaction associated with the subscription.
     *
     * @return MeprTransaction|false The latest transaction object or false if not found.
     */
    public function latest_txn()
    {
        $latest_txn_id = $this->latest_txn_id;
        return empty($latest_txn_id) ? false : new MeprTransaction($this->latest_txn_id);
    }

    /**
     * Retrieves the expiring transaction associated with the subscription.
     *
     * @return MeprTransaction|false The expiring transaction object or false if not found.
     */
    public function expiring_txn()
    {
        $expiring_txn_id = $this->expiring_txn_id;
        return empty($expiring_txn_id) ? false : new MeprTransaction($this->expiring_txn_id);
    }

    /**
     * Retrieves transactions associated with the subscription.
     *
     * @param boolean $return_objects Whether to return transaction objects.
     * @param string  $where          Additional WHERE clause.
     * @param string  $order          ORDER BY clause.
     *
     * @return array The list of transactions.
     */
    public function transactions($return_objects = true, $where = '', $order = 'created_at')
    {
        global $wpdb;

        $mepr_db = new MeprDb();

        if (!empty($where)) {
            $where = "AND {$where}";
        }

        if (!empty($order) && !preg_match('/ORDER BY/i', $order)) {
            $order = "ORDER BY {$order}";
        }

        $query = "SELECT id FROM {$mepr_db->transactions} AS t
                WHERE t.subscription_id = %d
                {$where}
              {$order}";
        $query = $wpdb->prepare($query, $this->id);
        $res   = $wpdb->get_col($query);

        if ($return_objects and !empty($res)) {
            $txns = [];

            foreach ($res as $id) {
                $txns[] = new MeprTransaction($id);
            }

            return $txns;
        }

        return $res;
    }

    /**
     * Cancels the subscription if payment cycles limit is reached.
     * Uses $trial_offset if a paid trial payment exists.
     *
     * @return void
     */
    public function limit_payment_cycles()
    {
        // Check if limiting is even enabled.
        if (!$this->limit_cycles) {
            return;
        }

        $pm = $this->payment_method();

        if ($pm === false) {
            return; // What else to do here?
        }

        $trial_offset = (($this->trial && $this->trial_amount > 0.00) ? 1 : 0);
        if (1 === $trial_offset && $this->prorated_trial && $this->trial && $this->limit_cycles) {
            $trial_offset = 0;
        }

        // Cancel this subscription if the payment cycles are limited and have been reached.
        if ($this->status == MeprSubscription::$active_str && ($this->txn_count - $trial_offset) >= $this->limit_cycles_num) {
            $_REQUEST['expire'] = true; // Pass the expire request.
            $_REQUEST['silent'] = true; // Don't want to send cancellation notices.
            try {
                $pm->process_cancel_subscription($this->id);
            } catch (Exception $e) {
                // TODO: We might want to actually do something here at some point.
                return;
            }
        }
    }

    /**
     * This should be called from process_cancel_subscription
     *
     * @return void
     */
    public function limit_reached_actions()
    {
        // Check if limiting is even enabled.
        if (!$this->limit_cycles) {
            return;
        }

        if ($this->limit_cycles_action == 'lifetime' || $this->limit_cycles_action == 'expires_after') {
            $txn = $this->latest_txn();

            if (!empty($txn) && $txn instanceof MeprTransaction) {
                if ($this->limit_cycles_action == 'lifetime') {
                    $txn->expires_at = MeprUtils::db_lifetime(); // Lifetime expiration.
                } elseif ($this->limit_cycles_action == 'expires_after') {
                    $expires_at = $this->get_expires_at(strtotime($txn->created_at));

                    switch ($this->limit_cycles_expires_type) {
                        case 'days':
                            $expires_at += MeprUtils::days($this->limit_cycles_expires_after);
                            break;
                        case 'weeks':
                            $expires_at += MeprUtils::weeks($this->limit_cycles_expires_after);
                            break;
                        case 'months':
                            $expires_at += MeprUtils::months($this->limit_cycles_expires_after, strtotime($txn->created_at));
                            break;
                        case 'years':
                            $expires_at += MeprUtils::years($this->limit_cycles_expires_after, strtotime($txn->created_at));
                    }

                    $txn->expires_at = MeprUtils::ts_to_mysql_date($expires_at); // Lifetime expiration.
                }

                $txn->store();
            }
        }

        MeprHooks::do_action('mepr-limit-payment-cycles-reached', $this);
    }

    /**
     * Expires transactions for the subscription.
     *
     * @return void
     */
    public function expire_txns()
    {
        global $wpdb;
        $mepr_db = new MeprDb();
        $time    = time();

        $q = "UPDATE {$mepr_db->transactions}
            SET expires_at = %s
            WHERE subscription_id = %d
              AND subscription_id > 0
              AND expires_at >= %s";

        // Set expiration 1 day in the past so it expires NOW.
        $wpdb->query($wpdb->prepare(
            $q,
            MeprUtils::ts_to_mysql_date($time - MeprUtils::days(1)),
            $this->id,
            MeprUtils::ts_to_mysql_date($time)
        ));
    }

    /**
     * Expires a confirmation transaction when a payment fails, is refunded, or the subscription is cancelled.
     *
     * @return void
     */
    public function expire_confirmation_txn()
    {
        global $wpdb;
        $mepr_db = new MeprDb();

        $q = $wpdb->prepare(
            "
        UPDATE {$mepr_db->transactions}
           SET expires_at = created_at
         WHERE subscription_id = %d
           AND txn_type = %s
           AND expires_at >= %s
      ",
            $this->id,
            MeprTransaction::$subscription_confirmation_str,
            MeprUtils::db_now()
        );

        $wpdb->query($q);
    }

    /**
     * Retrieves the payment method associated with the subscription.
     *
     * @return mixed The payment method object or false if not found.
     */
    public function payment_method()
    {
        $mepr_options = MeprOptions::fetch();

        return $mepr_options->payment_method($this->gateway);
    }

    /**
     * Checks if the subscription is in a free trial.
     *
     * @return boolean True if in a free trial, false otherwise.
     */
    public function in_free_trial()
    {
        return $this->in_trial('free');
    }

    /**
     * Checks if the subscription is in a paid trial.
     *
     * @return boolean True if in a paid trial, false otherwise.
     */
    public function in_paid_trial()
    {
        return $this->in_trial('paid');
    }

    /**
     * Checks if the subscription is in a trial.
     *
     * @param string $type The type of trial to check ('all', 'paid', or 'free').
     *
     * @return boolean True if in a trial, false otherwise.
     */
    public function in_trial($type = 'all')
    {
        // If no sub id then we're still checking out and this should return false, we're not YET in a trial.
        if ($this->id <= 0) {
            return false;
        }

        if ($this->trial) {
            $trial_started = is_null($this->created_at) ? time() : strtotime($this->created_at);
            $trial_ended   = $trial_started + MeprUtils::days($this->trial_days);

            if (($type == 'paid' && (float)$this->trial_amount <= 0.00) || ($type == 'free' && (float)$this->trial_amount > 0.00)) {
                return false;
            }

            return (time() < $trial_ended);
        }

        return false;
    }

    /**
     * Checks if the subscription is in a grace period.
     *
     * @return boolean True if in a grace period, false otherwise.
     */
    public function in_grace_period()
    {
        if ($this->txn_count == 0 && $this->status == self::$active_str) {
            $first_txn = $this->first_txn();

            // The subscription hasn't been cancelled, and it doesn't have any "payment" txn's yet
            // So let's check the confirmation expiration now (using 25 hours as a bit of a leway for the 24 hour grace period).
            if (
                $first_txn != false &&
                $first_txn instanceof MeprTransaction &&
                $first_txn->txn_type == MeprTransaction::$subscription_confirmation_str &&
                strtotime($first_txn->expires_at) >= time() &&
                (strtotime($first_txn->expires_at) - strtotime($first_txn->created_at)) <= MeprUtils::hours(25)
            ) {
                return true;
            }
        }

        return false;
    }

    /**
     * Retrieves the number of days until the subscription expires.
     *
     * @return integer The number of days until expiration.
     */
    public function days_till_expiration()
    {
        $expiring_txn = $this->expiring_txn();

        return $expiring_txn->days_till_expiration();
    }

    /**
     * Retrieves the number of days in the current subscription period.
     *
     * @param boolean $ignore_trial Whether to ignore the trial period.
     *
     * @return integer The number of days in the current period.
     */
    public function days_in_this_period($ignore_trial = false)
    {
        if ($this->in_trial() && !$ignore_trial) {
            $period_seconds = MeprUtils::days($this->trial_days);
        } else {
            if ((int)$this->id > 0) {
                $latest_txn = $this->latest_txn();

                if ($latest_txn == false || !($latest_txn instanceof MeprTransaction)) {
                    $latest_txn             = new MeprTransaction();
                    $latest_txn->created_at = MeprUtils::ts_to_mysql_date(time());
                }
            } else {
                // This could happen when checking upgrade prorated price on a new sub.
                $latest_txn             = new MeprTransaction();
                $latest_txn->created_at = MeprUtils::ts_to_mysql_date(time());
            }

            switch ($this->period_type) {
                case 'weeks':
                    $period_seconds = MeprUtils::weeks($this->period);
                    break;
                case 'months':
                    if (!$this->id) {
                        $period_seconds = MeprUtils::months($this->period, time()); // Probably an upgrade calculation, x months from now.
                    } else {
                        $add_days = 0;

                        if ($this->trial) {
                            $add_days = $this->trial_days;
                        }

                        $renewal_dom    = date('j', strtotime(gmdate('c', strtotime($this->renewal_base_date . " +{$add_days} days"))));
                        $period_seconds = MeprUtils::months($this->period, strtotime($latest_txn->created_at), false, $renewal_dom);
                    }
                    break;
                case 'years':
                    $period_seconds = MeprUtils::years($this->period, strtotime($latest_txn->created_at));
                    break;
                default:
                    return false;
            }
        }

        return intval(round($period_seconds / MeprUtils::days(1)));
    }

    /**
     * Retrieves the timestamp when the trial expires.
     *
     * @return integer The timestamp when the trial expires.
     */
    public function trial_expires_at()
    {
        $created_at = strtotime($this->created_at);

        return ($created_at + MeprUtils::days($this->trial_days));
    }

    /**
     * Checks if the subscription is expired.
     *
     * @param integer $offset The offset in seconds to check expiration.
     *
     * @return boolean True if expired, false otherwise.
     */
    public function is_expired($offset = 0)
    {
        // Check for a lifetime first.
        if (is_null($this->expires_at) || $this->expires_at == MeprUtils::db_lifetime()) {
            return false;
        }

        // Check for a false expires_at date.
        if ($this->expires_at === false) {
            return true;
        }

        $todays_ts  = time() + $offset; // Use the offset to check when a txn will expire.
        $expires_ts = strtotime($this->expires_at);

        return ($expires_ts < $todays_ts);
    }

    /**
     * Checks if the most recent transaction is a failure.
     *
     * @return boolean True if the most recent transaction is a failure, false otherwise.
     */
    public function latest_txn_failed()
    {
        global $wpdb;
        $mepr_db = new MeprDb();

        $status = $wpdb->get_var("
      SELECT status
        FROM {$mepr_db->transactions}
       WHERE subscription_id = {$this->id}
       ORDER BY id DESC
       LIMIT 1
    ");

        return ($status == MeprTransaction::$failed_str);
    }

    /**
     * Checks if the most recent transaction is a refund.
     *
     * @return boolean True if the most recent transaction is a refund, false otherwise.
     */
    public function latest_txn_refunded()
    {
        global $wpdb;
        $mepr_db = new MeprDb();

        $status = $wpdb->get_var("
      SELECT status
        FROM {$mepr_db->transactions}
       WHERE subscription_id = {$this->id}
       ORDER BY id DESC
       LIMIT 1
    ");

        return ($status == MeprTransaction::$refunded_str);
    }

    /**
     * Checks if the subscription is a lifetime subscription.
     *
     * @return boolean True if it is a lifetime subscription, false otherwise.
     */
    public function is_lifetime()
    {
        return ($this->expires_at == MeprUtils::db_lifetime());
    }

    /**
     * Checks if the subscription is active.
     *
     * @return boolean True if active, false otherwise.
     */
    public function is_active()
    {
        return !$this->is_expired();
    }

    /**
     * Checks if the subscription is cancelled.
     *
     * @return boolean True if cancelled, false otherwise.
     */
    public function is_cancelled()
    {
        return ($this->status == self::$cancelled_str);
    }

    /**
     * Retrieves the credit card number associated with the subscription.
     *
     * @return string The credit card number.
     */
    public function cc_num()
    {
        return MeprUtils::cc_num($this->cc_last4);
    }

    /**
     * Checks if the subscription is an upgrade.
     *
     * @return boolean True if it is an upgrade, false otherwise.
     */
    public function is_upgrade()
    {
        return $this->is_upgrade_or_downgrade('upgrade');
    }

    /**
     * Checks if the subscription is a downgrade.
     *
     * @return boolean True if it is a downgrade, false otherwise.
     */
    public function is_downgrade()
    {
        return $this->is_upgrade_or_downgrade('downgrade');
    }

    /**
     * Checks if the subscription is an upgrade or downgrade.
     *
     * @param string|boolean $type The type to check ('upgrade' or 'downgrade').
     *
     * @return boolean True if it is an upgrade or downgrade, false otherwise.
     */
    public function is_upgrade_or_downgrade($type = false)
    {
        $prd = $this->product();
        $usr = $this->user();

        return ($prd->is_upgrade_or_downgrade($type, $usr));
    }

    /**
     * Sets up the prorated trial.
     * This doesn't store ... do what you will later on
     *
     * @return void
     */
    public function maybe_prorate()
    {
        $mepr_options         = MeprOptions::fetch();
        $usr                  = $this->user();
        $this->prorated_trial = false;

        if (
            $usr->is_logged_in_and_current_user() &&
            $this->is_upgrade_or_downgrade() &&
            $mepr_options->pro_rated_upgrades
        ) {
            $grp          = $this->group();
            $old_lifetime = $usr->lifetime_subscription_in_group($grp->ID);

            // One-time payment upgrade?
            if ($old_lifetime !== false) {
                $old_amount = $old_lifetime->amount;
                $new_amount = ($this->trial) ? $this->trial_amount : $this->price;
                $old_period = $old_lifetime->days_in_this_period();
                $new_period = $this->days_in_this_period(true);
                $days_left  = $old_lifetime->days_till_expiration();

                $r = MeprUtils::calculate_proration($old_amount, $new_amount, $old_period, $new_period, $days_left, $grp->upgrade_path_reset_period, $old_lifetime, $this);
            } else { // Recurring upgrade.
                $old_sub = $usr->subscription_in_group($grp->ID);
                if ($old_sub && $old_sub->id != $this->id && !$old_sub->in_free_trial()) {
                    $r = MeprUtils::calculate_proration_by_subs($old_sub, $this, $grp->upgrade_path_reset_period);
                }
            }

            // Prorations override the trial ... if there is one
            // Only makes sense if the days are greater than 0.
            if (isset($r) && $r->days > 0) {
                $this->prorated_trial = true;
                $this->trial          = true;
                $this->trial_days     = $r->days;
                $this->trial_amount   = MeprUtils::maybe_round_to_minimum_amount($r->proration);

                $prd = $this->product();

                if (get_option('mepr_calculate_taxes') && !$prd->tax_exempt) {
                    $this->set_trial_taxes();
                } else {
                    $this->trial_tax_amount          = 0.00;
                    $this->trial_total               = $this->trial_amount;
                    $this->trial_tax_reversal_amount = 0.00;
                }
            }
        }
    }

    /**
     * Cancels the old subscription if necessary.
     *
     * @param boolean $force_cancel_artificial Whether to force cancel artificial subscriptions.
     *
     * @return mixed The event transaction or false if not applicable.
     */
    public function maybe_cancel_old_sub($force_cancel_artificial = false)
    {
        $mepr_options = MeprOptions::fetch();
        $usr          = $this->user();
        $grp          = $this->group();
        $evt_txn      = false;

        // No group? Not an upgrade then.
        if ($grp === false) {
            return false;
        }

        // No upgrade path here ... not an upgrade.
        if (!$grp->is_upgrade_path) {
            return false;
        }

        $pm = $this->payment_method();
        if (!$force_cancel_artificial && $pm instanceof MeprArtificialGateway && $pm->settings->manually_complete && $pm->settings->no_cancel_up_down_grade) {
            // If this is an artifical gateway and admin must manually approve and do not cancel when admin must manually approve
            // then don't cancel.
            return false;
        }

        try {
            $old_sub = $usr->subscription_in_group($grp->ID, true, $this->id);
            if ($old_sub) {
                // NOTE: This was added for one specific customer, it should only be used at customers own risk,
                // we do not support any custom development or issues that arise from using this hook
                // to override the default group behavior.
                $override_default_behavior = apply_filters('mepr-override-group-default-behavior-sub', false, $old_sub);

                if (!$override_default_behavior) {
                    $evt_txn = $old_sub->latest_txn();
                    $old_sub->expire_txns(); // Expire associated transactions for the old subscription.
                    $_REQUEST['silent'] = true; // Don't want to send cancellation notices.
                    if ($old_sub->status !== MeprSubscription::$cancelled_str) {
                        $old_sub->cancel();
                    }
                }
            } else {
                $old_lifetime_txn = $usr->lifetime_subscription_in_group($grp->ID);
                if ($old_lifetime_txn) {
                    // NOTE: This was added for one specific customer, it should only be used at customers own risk,
                    // we do not support any custom development or issues that arise from using this hook
                    // to override the default group behavior.
                    $override_default_behavior = apply_filters('mepr-override-group-default-behavior-lt', false, $old_lifetime_txn);

                    if (!$override_default_behavior) {
                        $old_lifetime_txn->expires_at = MeprUtils::ts_to_mysql_date(time() - MeprUtils::days(1));
                        $old_lifetime_txn->store();
                        $evt_txn = $old_lifetime_txn;
                    }
                }
            }
        } catch (Exception $e) {
            // Nothing for now.
        }

        if (!empty($evt_txn)) {
            MeprHooks::do_action('mepr-changing-subscription', $this->latest_txn(), $evt_txn);
        }

        return $evt_txn;
    }

    /**
     * Gets the value for 'expires_at' for the given created_at time for this membership.
     *
     * @param integer|null $created_ts The timestamp of creation.
     *
     * @return integer The expiration timestamp.
     */
    public function get_expires_at($created_ts = null)
    {
        $mepr_options = MeprOptions::fetch();

        if (is_null($created_ts)) {
            $created_ts = time();
        }

        $expires_ts = $created_ts;
        $period     = $this->period;

        // Used in monthly / yearly calcs.
        $renewal_date_ts = MeprUtils::db_date_to_ts($this->renewal_base_date);
        if ($this->trial && $this->created_at == $this->renewal_base_date) {
            $renewal_date_ts += MeprUtils::days($this->trial_days); // Account for trial periods (but not if paused & resumed with Stripe).
        }

        switch ($this->period_type) {
            case 'days':
                $expires_ts += MeprUtils::days($period) + MeprUtils::days($mepr_options->grace_expire_days);
                break;
            case 'weeks':
                $expires_ts += MeprUtils::weeks($period) + MeprUtils::days($mepr_options->grace_expire_days);
                break;
            case 'months':
                $renewal_dom = gmdate('j', $renewal_date_ts);

                $expires_ts += MeprUtils::months($period, $created_ts, false, $renewal_dom);

                $days_till_expire = floor(( $expires_ts - $created_ts ) / MeprUtils::days(1));

                // Fixes bug 1136 early/late monthly renewals.
                if ($period == 1) {
                    if ($days_till_expire < ( 27 - $mepr_options->grace_expire_days )) {
                        // Early renewal - we need to add a month in this case.
                        $expires_ts += MeprUtils::months($period, $expires_ts, false, $renewal_dom);
                    } elseif ($days_till_expire > 32) {
                        // Late renewal - we need to minus a month in this case.
                        $expires_ts -= MeprUtils::months($period, $expires_ts, true, $renewal_dom);
                    }

                    $new_days_till_expire = floor(( $expires_ts - $created_ts ) / MeprUtils::days(1));

                    // One final check, if we're still outside of tolerance just add 1 month from the created_ts like we used to.
                    if (( $new_days_till_expire < ( 27 - $mepr_options->grace_expire_days ) ) || $new_days_till_expire > 32) {
                        $expires_ts = ( $created_ts + MeprUtils::months($period, $created_ts) );
                    }
                }
                // In the future we need to find a way to make this work with
                // non-monthly renewals too. Like quarterly, semi-annual, or custom periods
                // But those are pretty rare in actual use, so monthly fixes ($period = 1) here is good enogh for now
                // else {
                // }.
                $expires_ts += MeprUtils::days($mepr_options->grace_expire_days);
                break;
            case 'years':
                $day_num     = gmdate('j', $renewal_date_ts);
                $month_num   = gmdate('n', $renewal_date_ts);
                $period_days = $period * 365;
                $tolerance   = 14; // Max number of days from the renewal date to be considered an early/late renewal.

                $expires_ts += MeprUtils::years($period, $created_ts, false, $day_num, $month_num);

                $days_till_expire = floor(( $expires_ts - $created_ts ) / MeprUtils::days(1));

                // Make sure we haven't under cut the expiration time.
                if ($days_till_expire < ( $period_days - $tolerance )) {
                    // Early renewal - we need to add a year in this case.
                    $expires_ts += MeprUtils::years($period, $expires_ts, false, $day_num, $month_num);
                } elseif ($days_till_expire > ( $period_days + $tolerance )) {
                    // Late renewal - we need to minus a year in this case.
                    $expires_ts -= MeprUtils::years($period, $expires_ts, true, $day_num, $month_num);
                }

                $new_days_till_expire = floor(( $expires_ts - $created_ts ) / MeprUtils::days(1));

                // One final check, if we're still outside of tolerance just add 1 year from the created_ts like we used to.
                if (( $new_days_till_expire < ( $period_days - $tolerance ) ) || ( $new_days_till_expire > ( $period_days + $tolerance ) )) {
                    $expires_ts = ( $created_ts + MeprUtils::years($period, $created_ts) );
                }

                $expires_ts += MeprUtils::days($mepr_options->grace_expire_days);
                break;
            default:
                $expires_ts = null;
        }

        return $expires_ts;
    }

    /**
     * Loads product variables into the subscription.
     *
     * @param MeprProduct $prd          The product object.
     * @param string|null $cpn_code     The coupon code.
     * @param boolean     $set_subtotal Whether to set the subtotal.
     *
     * @return void
     */
    public function load_product_vars($prd, $cpn_code = null, $set_subtotal = false)
    {
        $mock_cpn = (object)[
            'post_title' => null,
            'ID'         => 0,
            'trial'      => 0,
        ];

        if (empty($cpn_code) || !MeprCoupon::is_valid_coupon_code($cpn_code, $prd->ID)) {
            $cpn = $mock_cpn;
        } else {
            $cpn = MeprCoupon::get_one_from_code($cpn_code);
            if (!$cpn) {
                $cpn = $mock_cpn;
            }
        }

        $this->product_id                 = $prd->ID;
        $this->coupon_id                  = $cpn->ID;
        $this->period                     = $prd->period;
        $this->period_type                = $prd->period_type;
        $this->limit_cycles               = $prd->limit_cycles;
        $this->limit_cycles_num           = $prd->limit_cycles_num;
        $this->limit_cycles_action        = $prd->limit_cycles_action;
        $this->limit_cycles_expires_after = $prd->limit_cycles_expires_after;
        $this->limit_cycles_expires_type  = $prd->limit_cycles_expires_type;
        $this->trial                      = $prd->trial;
        $this->trial_days                 = $prd->trial ? $prd->trial_days : 0;
        $this->trial_amount               = MeprUtils::maybe_round_to_minimum_amount($prd->trial_amount);

        // If trial only once is set and the member has
        // already had a trial then get rid of it.
        if ($prd->trial_once && $prd->trial_is_expired()) {
            $this->trial                     = false;
            $this->trial_days                = 0;
            $this->trial_amount              = 0.00;
            $this->trial_tax_amount          = 0.00;
            $this->trial_total               = 0.00;
            $this->trial_tax_reversal_amount = 0.00;
        }

        if ($set_subtotal) {
            $this->set_subtotal(MeprUtils::maybe_round_to_minimum_amount($prd->adjusted_price()));
        } else {
            $this->price = MeprUtils::maybe_round_to_minimum_amount($prd->adjusted_price());
        }

        // This will only happen with a real coupon.
        if ($cpn instanceof MeprCoupon) {
            $cpn->maybe_apply_trial_override($this);

            // We can't do this above because we don't want to
            // screw up the price before applying the trial override.
            if ($set_subtotal) {
                $this->set_subtotal(MeprUtils::maybe_round_to_minimum_amount($prd->adjusted_price($cpn->post_title)));
            } else {
                $this->price = MeprUtils::maybe_round_to_minimum_amount($prd->adjusted_price($cpn->post_title));
            }
        }

        MeprHooks::do_action('mepr_subscription_applied_product_vars', $this);
    }

    /**
     * Determines if the subscription can perform a certain capability.
     *
     * @param string $cap The capability to check.
     *
     * @return boolean True if the capability can be performed, false otherwise.
     */
    public function can($cap)
    {
        $pm = $this->payment_method();

        if ($pm != false and is_object($pm)) {
            return $pm->can($cap);
        }

        return false;
    }

    /**
     * Suspends the subscription.
     *
     * @return boolean True on success, false on failure.
     */
    public function suspend()
    {
        if ($this->can('suspend-subscriptions')) {
            try {
                $pm = $this->payment_method();
                return $pm->process_suspend_subscription($this->id);
            } catch (Exception $e) {
                return false;
            }
        }

        return false;
    }

    /**
     * Resumes the subscription.
     *
     * @return boolean True on success, false on failure.
     *
     * @throws Exception If the subscription could not be resumed.
     */
    public function resume()
    {
        if ($this->can('resume-subscriptions')) {
            $pm = $this->payment_method();
            return $pm->process_resume_subscription($this->id);
        }

        return false;
    }

    /**
     * Cancels the subscription.
     *
     * @return boolean True on success, false on failure.
     */
    public function cancel()
    {
        if ($this->can('cancel-subscriptions')) {
            $pm = $this->payment_method();
            return $pm->process_cancel_subscription($this->id);
        }

        return false;
    }

    /**
     * Checks if the credit card is expiring before the next payment.
     *
     * @return boolean True if expiring before the next payment, false otherwise.
     */
    public function cc_expiring_before_next_payment()
    {
        $next_billing_at = $this->next_billing_at;
        $exp_month       = $this->cc_exp_month;
        $exp_year        = $this->cc_exp_year;

        if ($next_billing_at && $exp_month && $exp_year) {
            $cc_exp_ts       = mktime(0, 0, 0, $exp_month, 1, $exp_year);
            $next_billing_ts = strtotime($next_billing_at);
            return ( $cc_exp_ts < $next_billing_ts );
        }

        return false;
    }

    /**
     * Retrieves the update URL for the subscription.
     *
     * @return string The update URL.
     */
    public function update_url()
    {
        $mepr_options = MeprOptions::fetch();

        return $mepr_options->account_page_url("action=update&sub={$this->id}");
    }

    /**
     * Retrieves the upgrade URL for the subscription.
     *
     * @return string The upgrade URL.
     */
    public function upgrade_url()
    {
        $mepr_options = MeprOptions::fetch();

        $grp = $this->group();
        if ($grp && $grp->is_upgrade_path) {
            return $mepr_options->account_page_url("action=upgrade&sub={$this->id}");
        }

        return '';
    }

    /**
     * Calculates the catchup for the subscription.
     * ONLY FOR AUTHORIZE.NET CURRENTLY but could be used for Manual Subscriptions / PayPal Reference txn's eventually
     *
     * @param string $type The type of catchup to calculate ('proration', 'full', 'period', or 'none').
     *
     * @return object The catchup calculation.
     */
    public function calculate_catchup($type = 'proration')
    {
        /*
         * $types can be any of the following
         *
         * none       = no payment
         * full       = from expiration date of last txn until next billing date
         * period     = full amount for current period -- regardless of date
         * proration  = prorated amount for current period only (default)
         *
         */

        // If type is none, or the subscription hasn't expired -- return false.
        if ($type == 'none' || !$this->is_expired()) {
            return false;
        }

        // Calculate Next billing time.
        $expired_at                = strtotime($this->expires_at);
        $now                       = time();
        $time_elapsed              = $now - $expired_at;
        $periods_elapsed           = (int)ceil($time_elapsed / MeprUtils::days($this->days_in_this_period())); // We want to round this up to INT.
        $next_billing              = $now;
        $subscription_cost_per_day = (float)((float)$this->price / $this->days_in_this_period());

        // $periods_elapsed should never be 0, but just in case:
        if ($periods_elapsed <= 0) {
            $periods_elapsed = 1;
        }

        switch ($this->period_type) {
            case 'weeks':
                $next_billing = $expired_at + MeprUtils::weeks($periods_elapsed * $this->period);
                break;
            case 'months':
                $renewal_dom  = date('j', strtotime($this->renewal_base_date));
                $next_billing = $expired_at + MeprUtils::months($periods_elapsed * $this->period, $expired_at, false, $renewal_dom);
                break;
            case 'years':
                $next_billing = $expired_at + MeprUtils::years($periods_elapsed * $this->period, $expired_at);
                break;
        }

        // Handle $type = period.
        if ($type == 'period') {
            $full_price = MeprUtils::format_float($this->price);

            return (object)[
                'proration'    => $full_price,
                'next_billing' => $next_billing,
            ];
        }

        // Handle $type = full.
        if ($type == 'full') {
            // Multiply $this->price * $periods_elapsed to get a nice pretty catchup number.
            $sub_price      = MeprUtils::format_float($this->price);
            $full_proration = MeprUtils::format_float(($sub_price * $periods_elapsed));

            return (object)[
                'proration'    => $full_proration,
                'next_billing' => $next_billing,
            ];
        }

        // All other $types have been handled, so if we made it here just calculate $type = 'proration'.
        $seconds_till_billing = $next_billing - $now;
        $days_till_billing    = (int)($seconds_till_billing / MeprUtils::days(1));
        $proration            = MeprUtils::format_float($subscription_cost_per_day * $days_till_billing);

        return (object)compact('proration', 'next_billing');
    }

    /**
     * Checks if the first real payment failed.
     *
     * @return boolean True if the first real payment failed, false otherwise.
     */
    public function first_real_payment_failed()
    {
        if ($this->txn_count > 1) {
            return false;
        }

        if ($this->trial && $this->trial_amount > 0.00) {
            // Only the trial payment exists?
            if ($this->txn_count == 1) {
                // Check for failed.
                if ($this->has_a_txn_failed()) {
                    return true;
                }
            }
        } else {
            // No real payments recorded yet?
            if ($this->txn_count == 0) {
                // Check for failed.
                if ($this->has_a_txn_failed()) {
                    return true;
                }
            }
        }

        return false;
    }

    /**
     * Checks if the subscription has a failed transaction.
     *
     * @return boolean True if the subscription has a failed transaction, false otherwise.
     */
    public function has_a_txn_failed()
    {
        global $wpdb;
        $mepr_db = new MeprDb();

        $q   = $wpdb->prepare("SELECT COUNT(*) FROM {$mepr_db->transactions} WHERE status = %s AND subscription_id = %d", MeprTransaction::$failed_str, $this->id);
        $res = $wpdb->get_var($q);

        if ($res) {
            return true;
        }

        return false;
    }

    /**
     * Applies taxes to the subscription.
     *
     * @param float   $subtotal     The subtotal amount.
     * @param integer $num_decimals The number of decimals for rounding.
     * @param float   $gross        The gross amount.
     */
    public function apply_tax($subtotal, $num_decimals = 2, $gross = 0.00)
    {
        $usr             = $this->user();
        $prd             = $this->product();
        $calculate_taxes = get_option('mepr_calculate_taxes');

        // Now try to calculate tax info from the user info.
        if ($prd->tax_exempt) {
            list($this->price, $this->total, $this->tax_rate, $this->tax_amount, $this->tax_desc, $this->tax_class, $this->tax_reversal_amount) = [$gross, $gross, 0.00, 0.00, '', 'standard', 0.00];
            if ($this->trial) {
                $this->trial_total               = $this->trial_amount;
                $this->trial_tax_amount          = 0.00;
                $this->trial_tax_reversal_amount = 0.00;
            }
        } elseif ($calculate_taxes) {
            list($this->price, $this->total, $this->tax_rate, $this->tax_amount, $this->tax_desc, $this->tax_class, $this->tax_reversal_amount) = $usr->calculate_tax($subtotal, $num_decimals, $prd->ID);
            if ($this->trial) {
                $this->set_trial_taxes($num_decimals);
            }
        } else { // If all else fails, let's blank out the tax info.
            list($this->price, $this->total, $this->tax_rate, $this->tax_amount, $this->tax_desc, $this->tax_class, $this->tax_reversal_amount) = [$subtotal, $subtotal, 0.00, 0.00, '', 'standard', 0.00];
            if ($this->trial) {
                $this->trial_total               = $this->trial_amount;
                $this->trial_tax_amount          = 0.00;
                $this->trial_tax_reversal_amount = 0.00;
            }
        }
        MeprHooks::do_action('mepr_subscription_apply_tax', $this);
    }

    /**
     * Sets up the transaction total, subtotal, and tax based on a subtotal value.
     * This method also checks for inclusive vs exclusive tax.
     *
     * @param float $subtotal The subtotal amount.
     *
     * @return void
     */
    public function set_subtotal($subtotal)
    {
        $mepr_options = MeprOptions::fetch();

        if ($mepr_options->attr('tax_calc_type') == 'inclusive') {
            $usr      = $this->user();
            $subtotal = $usr->calculate_subtotal($subtotal, null, 2, $this->product());
        }

        $this->apply_tax($subtotal, 2, $subtotal);
    }

    /**
     * Sets up the trial taxes for the subscription.
     *
     * @param integer $num_decimals The number of decimals for rounding.
     *
     * @return void
     */
    public function set_trial_taxes($num_decimals = 2)
    {
        $mepr_options = MeprOptions::fetch();

        $usr = $this->user();

        if ($mepr_options->attr('tax_calc_type') == 'inclusive') {
            $subtotal    = $usr->calculate_subtotal($this->trial_amount, null, 2, $this->product());
            $trial_taxes = $usr->calculate_tax($subtotal, $num_decimals);
        } else {
            $trial_taxes = $usr->calculate_tax($this->trial_amount, $num_decimals);
        }

        $this->trial_amount              = $trial_taxes[0];
        $this->trial_total               = $trial_taxes[1];
        $this->trial_tax_amount          = $trial_taxes[3];
        $this->trial_tax_reversal_amount = $trial_taxes[6];
    }

    /**
     * Sets up the transaction total, subtotal, and tax based on a gross value.
     * This will never check for tax inclusion because it's the gross
     * it doesn't matter (since we already know the gross amount).
     *
     * @param float $gross The gross amount.
     *
     * @return void
     */
    public function set_gross($gross)
    {
        $usr      = $this->user();
        $prd      = $this->product();
        $tax_rate = $usr->tax_rate($prd->ID);
        $subtotal = $usr->calculate_subtotal($gross, $tax_rate->reversal ? 0 : null, 2, $prd);

        $this->apply_tax($subtotal, 2, $gross);
    }

    /**
     * Retrieves the tax information for the subscription.
     *
     * @return array The tax information.
     */
    public function tax_info()
    {
        return [$this->price, $this->total, $this->tax_rate, $this->tax_amount, $this->tax_desc, $this->tax_class];
    }

    /*****
     * MAGIC METHOD HANDLERS
     *****/
    /**
     * Handles the magic method for getting the first transaction ID.
     *
     * @param string $mgm The magic method type.
     * @param string $val The value to set.
     *
     * @return mixed The first transaction ID or true.
     */
    protected function mgm_first_txn_id($mgm, $val = '')
    {
        global $wpdb;
        $mepr_db = new MeprDb();
        $where   = '';

        switch ($mgm) {
            case 'get':
                // Get the first real payment.
                if (isset($_REQUEST['mepr_get_real_payment'])) {
                    $where = $wpdb->prepare('AND t.txn_type = %s AND t.status = %s', MeprTransaction::$payment_str, MeprTransaction::$complete_str);
                }

                $q = $wpdb->prepare(
                    "
            SELECT t.id
              FROM {$mepr_db->transactions} AS t
             WHERE t.subscription_id = %d
                   {$where}
             ORDER BY t.id ASC
             LIMIT 1
          ",
                    $this->rec->id
                );

                $txn_id = $wpdb->get_var($q);

                // No real payments yet, so let's look for a confirmation?
                if (empty($txn_id)) {
                      $q = $wpdb->prepare(
                          "
              SELECT t.id
                FROM {$mepr_db->transactions} AS t
               WHERE t.subscription_id = %d
               ORDER BY t.id ASC
               LIMIT 1
            ",
                          $this->rec->id
                      );

                        $txn_id = $wpdb->get_var($q);
                }

                return empty($txn_id) ? false : $txn_id;
            default:
                return true;
        }
    }

    /**
     * Handles the magic method for getting the latest transaction ID.
     *
     * @param string $mgm The magic method type.
     * @param string $val The value to set.
     *
     * @return mixed The latest transaction ID or true.
     */
    protected function mgm_latest_txn_id($mgm, $val = '')
    {
        global $wpdb;
        $mepr_db = new MeprDb();

        switch ($mgm) {
            case 'get':
                $q = $wpdb->prepare(
                    "
            SELECT t.id
              FROM {$mepr_db->transactions} AS t
             WHERE t.subscription_id = %d
               AND t.status IN (%s, %s)
             ORDER BY t.id DESC
             LIMIT 1
          ",
                    $this->rec->id,
                    MeprTransaction::$complete_str,
                    MeprTransaction::$confirmed_str
                );

                $id = $wpdb->get_var($q);
                return empty($id) ? false : $id;
            default:
                return true;
        }
    }

    /**
     * Handles the magic method for getting the expiring transaction ID.
     *
     * @param string $mgm The magic method type.
     * @param string $val The value to set.
     *
     * @return mixed The expiring transaction ID or true.
     */
    protected function mgm_expiring_txn_id($mgm, $val = '')
    {
        global $wpdb;
        $mepr_db = new MeprDb();

        switch ($mgm) {
            case 'get':
                $q = $wpdb->prepare(
                    "
          SELECT t.id
            FROM {$mepr_db->transactions} AS t
           WHERE t.subscription_id=%d
             AND t.status IN (%s,%s)
             AND ( t.expires_at = %s
               OR ( t.expires_at <> %s
                 AND t.expires_at=(
                   SELECT MAX(t2.expires_at)
                     FROM {$mepr_db->transactions} as t2
                    WHERE t2.subscription_id=%d
                      AND t2.status IN (%s,%s)
                 )
               )
             ) " .
                    // If there's a lifetime and an expires at, favor the lifetime.
                    '
            ORDER BY t.expires_at, t.status ASC
            LIMIT 1
          ',
                    $this->rec->id,
                    MeprTransaction::$confirmed_str,
                    MeprTransaction::$complete_str,
                    MeprUtils::db_lifetime(),
                    MeprUtils::db_lifetime(),
                    $this->rec->id,
                    MeprTransaction::$confirmed_str,
                    MeprTransaction::$complete_str
                );

                $id = $wpdb->get_var($q);
                return empty($id) ? false : $id;
            default:
                return true;
        }
    }

    /**
     * Handles the magic method for getting the transaction count.
     *
     * @param string $mgm The magic method type.
     * @param string $val The value to set.
     *
     * @return integer|true The transaction count or true.
     */
    protected function mgm_txn_count($mgm, $val = '')
    {
        global $wpdb;
        $mepr_db = new MeprDb();

        switch ($mgm) {
            case 'get':
                $q = $wpdb->prepare(
                    "
            SELECT COUNT(*)
              FROM {$mepr_db->transactions} AS t
             WHERE t.subscription_id = %d
               AND t.subscription_id > 0
               AND t.status = %s
          ",
                    $this->rec->id,
                    MeprTransaction::$complete_str
                );

                return $wpdb->get_var($q);
            default:
                return true;
        }
    }

    /**
     * Handles the magic method for getting the expiration date.
     *
     * @param string $mgm The magic method type.
     * @param string $val The value to set.
     *
     * @return string|true The expiration date or true.
     */
    protected function mgm_expires_at($mgm, $val = '')
    {
        global $wpdb;

        $mepr_db = new MeprDb();

        switch ($mgm) {
            case 'get':
                if ($this->status == self::$pending_str) { // Pending subs should not be active - see self::is_active().
                    return false;
                }

                $q = $wpdb->prepare(
                    "
          SELECT t.expires_at
            FROM {$mepr_db->transactions} AS t
           WHERE t.subscription_id=%d
             AND t.status IN (%s,%s)
             AND ( t.expires_at = %s
               OR ( t.expires_at <> %s
                 AND t.expires_at=(
                   SELECT MAX(t2.expires_at)
                     FROM {$mepr_db->transactions} as t2
                    WHERE t2.subscription_id=%d
                      AND t2.status IN (%s,%s)
                 )
               )
             )
        " .
                    // If there's a lifetime and an expires at, favor the lifetime.
                    '
           ORDER BY t.expires_at
           LIMIT 1
        ',
                    $this->rec->id,
                    MeprTransaction::$confirmed_str,
                    MeprTransaction::$complete_str,
                    MeprUtils::db_lifetime(),
                    MeprUtils::db_lifetime(),
                    $this->rec->id,
                    MeprTransaction::$confirmed_str,
                    MeprTransaction::$complete_str
                );

                $expires_at = $wpdb->get_var($q);

                if (!$this->id || is_null($expires_at) || false === $expires_at) {
                      // First check if latest txn was a refund, if so we're not active.
                    if ($this->latest_txn_refunded()) {
                        return false;
                    }

                      $expires_at = $this->get_expires_at();
                      // Convert to mysql date.
                      $expires_at = MeprUtils::ts_to_mysql_date($expires_at);
                }

                return $expires_at;
            default:
                return true;
        }
    }

    /**
     * Handles the magic method for getting the next billing date.
     *
     * @param string $mgm   The magic method type.
     * @param string $value The value to set.
     *
     * @return string|true The next billing date or true.
     */
    protected function mgm_next_billing_at($mgm, $value = '')
    {
        global $wpdb;

        $mepr_db = new MeprDb();

        switch ($mgm) {
            case 'get':
                if (
                    $this->status == MeprSubscription::$active_str and
                    !empty($this->expires_at) and
                    $this->expires_at != MeprUtils::db_lifetime() and
                    ( !$this->limit_cycles or
                    ( $this->limit_cycles and
                    $this->txn_count < $this->limit_cycles_num ) )
                ) {
                    return $this->expires_at;
                } else {
                    return false;
                }
            default:
                return true;
        }
    }

    /**
     * Handles the magic method for getting or setting the ID.
     *
     * @param string $mgm   The magic method type.
     * @param string $value The value to set.
     *
     * @return integer|true The ID or true.
     */
    protected function mgm_ID($mgm, $value = '')
    {
        global $wpdb;

        $mepr_db = new MeprDb();

        switch ($mgm) {
            case 'get':
                return $this->rec->id;
            case 'set':
                $this->rec->id = $value;
                break;
            default:
                return true;
        }
    }

    /**
     * This is the effective start date for the subscription. Under normal circumstances
     * this is just the created_at date but when a subscription is paused and resumed
     * the startdate will reset to whenever the latest resume_at date occurs.
     *
     * @param string $mgm   The magic method.
     * @param mixed  $value The value to set.
     *
     * @return string The prepared column statement.
     */
    protected function mgm_renewal_base_date($mgm, $value = '')
    {
        global $wpdb;

        $mepr_db = new MeprDb();

        switch ($mgm) {
            case 'get':
                $pm = $this->payment_method();
                if ($pm instanceof MeprBaseGateway) {
                    return $pm->get_renewal_base_date($this);
                }
                return $this->created_at;
            case 'set':
                return false;
            default:
                return true;
        }
    }

    /**
     * Specifies the attributes for upgrading subscriptions to the new table.
     *
     * @return array The attributes for upgrading subscriptions.
     */
    public static function upgrade_attrs()
    {
        return [
            'subscr_id'                  => "CONCAT('mp-sub-',UUID_SHORT())",
            'gateway'                    => 'manual',
            'user_id'                    => 0,
            'product_id'                 => 0,
            'coupon_id'                  => 0,
            'price'                      => 0.00,
            'total'                      => '{{price}}',
            'period'                     => 1,
            'period_type'                => 'months',
            'limit_cycles'               => false,
            'limit_cycles_num'           => 0,
            'limit_cycles_action'        => null,
            'limit_cycles_expires_after' => '1',
            'limit_cycles_expires_type'  => 'days',
            'prorated_trial'             => false,
            'trial'                      => false,
            'trial_days'                 => 0,
            'trial_amount'               => 0.00,
            'status'                     => MeprSubscription::$pending_str,
            'created_at'                 => null,
            'tax_rate'                   => 0.00,
            'tax_amount'                 => 0.00,
            'tax_desc'                   => '',
            'tax_class'                  => 'standard',
            'cc_last4'                   => null,
            'cc_exp_month'               => null,
            'cc_exp_year'                => null,
        ];
    }

    /**
     * Prepares a column statement for SQL queries.
     *
     * @param string $slug    The column slug.
     * @param mixed  $default The default value.
     *
     * @return string The prepared column statement.
     */
    private static function col_stmt($slug, $default)
    {
        global $wpdb;

        if (is_null($default)) {
            // A left join will naturally produce a NULL value if not found...no IFNULL needed.
            $col = "pm_{$slug}.meta_value";
        } elseif ($slug == 'subscr_id') {
            $col = "IFNULL(pm_{$slug}.meta_value,{$default})";
        } elseif (is_integer($default)) {
            $col = $wpdb->prepare("IFNULL(pm_{$slug}.meta_value,%d)", $default);
        } elseif (is_float($default)) {
            $col = $wpdb->prepare("IFNULL(pm_{$slug}.meta_value,%f)", $default);
        } else {
            $col = $wpdb->prepare("IFNULL(pm_{$slug}.meta_value,%s)", $default);
        }

        return $col;
    }

    /**
     * This is specifically used to migrate subscriptions to its new table.
     *
     * @param integer|null $subscription_id          The subscription ID.
     * @param boolean      $exclude_already_upgraded Whether to exclude already upgraded subscriptions.
     * @param string       $limit                    The limit for the query.
     *
     * @return array The upgrade query.
     */
    public static function upgrade_query($subscription_id = null, $exclude_already_upgraded = false, $limit = '')
    {
        global $wpdb;

        $mepr_options = MeprOptions::fetch();
        $mepr_db      = MeprDb::fetch();

        // $cols = array('id' => 'DISTINCT pst.ID');
        $cols = ['id' => 'pst.ID'];

        // Add postmeta columns
        // Must be the same order and name as the table itself.
        $pms = self::upgrade_attrs();

        foreach ($pms as $slug => $default) {
            if (is_string($default) && preg_match('/^\{\{([^\{\}]*)\}\}$/', $default, $m)) {
                $cols[$slug] = "IFNULL(pm_{$slug}.meta_value," . self::col_stmt($m[1], $pms[$m[1]]) . ')';
            } else {
                $cols[$slug] = self::col_stmt($slug, $default);
            }
        }

        // The database can handle these
        // $cols['tax_compound'] = 0;
        // $cols['tax_shipping'] = 1;.
        $args = [$wpdb->prepare('pst.post_type = %s', 'mepr-subscriptions')];

        // Don't upgrade any that are already upgraded.
        if ($exclude_already_upgraded) {
            $args[] = "pst.ID NOT IN (SELECT id FROM {$mepr_db->subscriptions})";
        }

        if (!is_null($subscription_id)) {
            $args[] = $wpdb->prepare('pst.ID = %d', $subscription_id);
        }

        $joins = [];
        // $ignore_cols = array('tax_compound','tax_shipping');
        // Add postmeta joins
        foreach ($pms as $slug => $default) {
            $joins[] = self::join_pm($slug, 'LEFT JOIN');
        }

        if ($limit === false) {
            $paged   = '';
            $perpage = 0;
        } else {
            $paged   = 1;
            $perpage = $limit;
        }

        $order_by = 'ID';
        $order    = 'DESC';

        return MeprDb::list_table(
            $cols,
            "{$wpdb->posts} AS pst",
            $joins,
            $args,
            $order_by,
            $order,
            $paged,
            '',
            'any',
            $perpage,
            false,
            true
        );
    }

    /**
     * Upgrades the subscription table.
     *
     * @param integer|null $subscription_id          The subscription ID.
     * @param boolean      $exclude_already_upgraded Whether to exclude already upgraded subscriptions.
     * @param string       $limit                    The limit for the query.
     *
     * @return void
     * @throws MeprDbMigrationException If the database migration fails.
     */
    public static function upgrade_table($subscription_id = null, $exclude_already_upgraded = false, $limit = '')
    {
        global $wpdb;

        $mepr_db = MeprDb::fetch();

        $subq  = self::upgrade_query($subscription_id, $exclude_already_upgraded, $limit);
        $attrs = 'id,' . implode(',', array_keys(self::upgrade_attrs()));

        $subq = "INSERT IGNORE INTO {$mepr_db->subscriptions} ({$attrs}) {$subq['query']}";
        $res  = $wpdb->query($subq);

        if ($res === false) { // $res will never return a WP_Error
            throw new MeprDbMigrationException(sprintf(
                // Translators: %1$s: last error message, %2$s: query.
                __('MemberPress database migration failed: %1$s %2$s', 'memberpress'),
                $wpdb->last_error,
                $subq
            ));
        }
    }

    /**
     * Joins the postmeta table to the subscription table.
     * STILL USING THIS TO MIGRATE THE DATABASE
     *
     * @param string $slug The column slug.
     * @param string $join The join type.
     * @param string $post The post table.
     *
     * @return string The join statement.
     */
    private static function join_pm($slug, $join = 'LEFT JOIN', $post = 'pst')
    {
        global $wpdb;
        $vals = self::legacy_str_vals();

        $class = new ReflectionClass('MeprSubscription');
        $val   = $vals[$slug];

        return $wpdb->prepare("{$join} {$wpdb->postmeta} AS pm_{$slug}
                                 ON pm_{$slug}.post_id = {$post}.ID
                                AND pm_{$slug}.meta_key = %s", $val);
    }

    /**
     * Returns the legacy string values for the subscription table.
     * STILL USING THIS TO MIGRATE THE DATABASE
     *
     * @return array The legacy string values.
     */
    private static function legacy_str_vals()
    {
        return [
            'subscr_id'                  => '_mepr_subscr_id',
            'user_id'                    => '_mepr_subscr_user_id',
            'gateway'                    => '_mepr_subscr_gateway',
            'product_id'                 => '_mepr_subscr_product_id',
            'coupon_id'                  => '_mepr_subscr_coupon_id',
            'price'                      => '_mepr_subscr_price',
            'period'                     => '_mepr_subscr_period',
            'period_type'                => '_mepr_subscr_period_type',
            'limit_cycles'               => '_mepr_subscr_limit_cycles',
            'limit_cycles_num'           => '_mepr_subscr_limit_cycles_num',
            'limit_cycles_action'        => '_mepr_subscr_limit_cycles_action',
            'limit_cycles_expires_after' => '_mepr_subscr_limit_cycles_expires_after',
            'limit_cycles_expires_type'  => '_mepr_subscr_limit_cycles_expires_type',
            'prorated_trial'             => '_mepr_subscr_prorated_trial',
            'trial'                      => '_mepr_subscr_trial',
            'trial_days'                 => '_mepr_subscr_trial_days',
            'trial_amount'               => '_mepr_subscr_trial_amount',
            'status'                     => '_mepr_subscr_status',
            'created_at'                 => '_mepr_subscr_created_at',
            'cc_last4'                   => '_mepr_subscr_cc_last4',
            'cc_exp_month'               => '_mepr_subscr_cc_month_exp',
            'cc_exp_year'                => '_mepr_subscr_cc_year_exp',
            'total'                      => '_mepr_subscr_total',
            'tax_rate'                   => '_mepr_subscr_tax_rate',
            'tax_amount'                 => '_mepr_subscr_tax_amount',
            'tax_desc'                   => '_mepr_subscr_tax_desc',
            'tax_class'                  => '_mepr_subscr_tax_class',
            'cpt'                        => 'mepr-subscriptions',
        ];
    }
}
