<?php
/*
* Account.inc.php
*
* This is the domain logic managing the fundamental behaviors and data of Accounts.
*
* @author   Quinn Comendant <quinn@strangecode.com>
* @version  1.0
* @since    20 Aug 2014 17:20:09
*/
require_once 'models/Model.inc.php';

class Account extends Model
{
    // The database fields used by this model (this should be overwritten by each subclass).
    public static $fields = array(
        'account_id' => '',
        'organization' => '',
        'address1' => '',
        'address2' => '',
        'city' => '',
        'state' => '',
        'zip' => '',
        'country' => '',
        'url' => '',
        'notes' => '',
        'available_credit' => '',
        'used_credit' => '',
        'recharge_amount' => '',
        'added_datetime' => '',
        'added_by_user_id' => '',
        'modified_datetime' => '',
        'modified_by_user_id' => '',
        'stripe_customer_id' => '',
        'cc_cardholder' => '',
        'cc_number' => '',
        'cc_cvc' => '',
        'cc_exp_m' => '',
        'cc_exp_y' => '',
    );

    /*
    *
    *
    * @access   public
    * @param
    * @return
    * @author   Quinn Comendant <quinn@strangecode.com>
    * @version  1.0
    * @since    16 Nov 2014 17:45:40
    */
    static public function insert($frm)
    {
        global $auth, $cache;
        $db =& DB::getInstance();
        $app =& App::getInstance();

        // Remove any stale cached list data.
        $cache->delete('account list');

        $frm = array_map('trim', $frm);

        $stripe_customer_id = '';
        if (isset($frm['cc_number']) && '' != $frm['cc_number']) {
            // Create a Stripe Customer if a credit card is submitted.
            if (false === ($stripe_customer_id = self::stripeCustomerCreate($frm))) {
                // If there is an error creating the stripe customer, don't enter a stripe_customer_id in our DB.
                $stripe_customer_id = '';
            }
        }

        // Insert record data.
        $app->logMsg(sprintf('%s with data %s', __METHOD__, getDump($frm)), LOG_DEBUG, __FILE__, __LINE__);
        $db->query("
            INSERT INTO account_tbl (
                organization,
                address1,
                address2,
                city,
                state,
                zip,
                country,
                url,
                notes,
                stripe_customer_id,
                available_credit,
                used_credit,
                recharge_amount,
                added_datetime,
                added_by_user_id,
                modified_datetime,
                modified_by_user_id
            ) VALUES (
                '" . $db->escapeString($frm['organization']) . "',
                '" . $db->escapeString($frm['address1']) . "',
                '" . $db->escapeString($frm['address2']) . "',
                '" . $db->escapeString($frm['city']) . "',
                '" . $db->escapeString($frm['state']) . "',
                '" . $db->escapeString($frm['zip']) . "',
                '" . $db->escapeString($frm['country']) . "',
                '" . $db->escapeString($frm['url']) . "',
                '" . $db->escapeString($frm['notes']) . "',
                '" . $db->escapeString($stripe_customer_id) . "',
                '" . $db->escapeString($frm['available_credit']) . "',
                '" . $db->escapeString($frm['used_credit']) . "',
                '" . $db->escapeString($frm['recharge_amount']) . "',
                NOW(),
                '" . $db->escapeString($auth->get('user_id')) . "',
                NOW(),
                '" . $db->escapeString($auth->get('user_id')) . "'
            )
        ");
        $last_insert_id = mysql_insert_id($db->getDBH());

        // Create version.
        $version = Version::getInstance($auth);
        $version->create('account_tbl', 'account_id', $last_insert_id, $frm['organization']);

        return $last_insert_id;
    }

    /*
    *
    *
    * @access   public
    * @param
    * @return
    * @author   Quinn Comendant <quinn@strangecode.com>
    * @version  1.0
    * @since    16 Nov 2014 17:45:40
    */
    static public function update($frm)
    {
        global $auth, $lock, $cache;
        $db =& DB::getInstance();
        $app =& App::getInstance();

        $lock->select('account_tbl', 'account_id', $frm['account_id']);
        if ($lock->isLocked() && !$lock->isMine()) {
            $lock->dieErrorPage();
        }

        // Remove any stale cached list data.
        $cache->delete('account list');

        $frm = array_map('trim', $frm);

        $stripe_customer_id = '';
        $payment_type_clause = '';
        if (isset($frm['stripe_token']) && '' != $frm['stripe_token']) {
            // We need to synchronize Stripe customers with our accounts. If only they had a "ON DUPLICATE KEY UPDATE" function.
            // Only do an update if we know the stripe_customer_id and it exists on Stripe's end.
            // All other cases require adding a new record.

            // Get current account data.
            $account = parent::get(array('account_id' => $frm['account_id']));

            // This is required by stripeCustomerUpdate()
            $frm['stripe_customer_id'] = $account['stripe_customer_id'];

            // Default action is to add this user to stripe.
            $stripe_action = 'add';
            if ('' != $account['stripe_customer_id']) {
                // If we have a stripe_customer_id, try to match it with a record at Stripe.
                try {
                    $stripe_customer = Stripe_Customer::retrieve($account['stripe_customer_id']);
                    if ($stripe_customer->id == $account['stripe_customer_id']) {
                        // If there is one we update,
                        $stripe_action = 'update';
                    }
                } catch (Stripe_InvalidRequestError $e) {
                    // Just silence the error that would occur if the customer doesn't exist.
                }
            }

            switch ($stripe_action) {
                case 'add':
                default:
                    // Add Stripe customer, but only if a credit card is submitted.
                    if (false !== ($stripe_customer_id = self::stripeCustomerCreate($frm))) {
                        // If stripeCustomerCreate() returns true, we add their stripe details to our DB.
                        // If it fails, we update our DB without the CC details (and display errors via stripeCustomerCreate()).
                        $payment_type_clause = "stripe_customer_id = '" . $db->escapeString($stripe_customer_id) . "',";
                    } else {
                        $app->raiseMsg(sprintf(_("There was an error saving your credit card details. We got everything else, but please try again with the credit card."), null), MSG_WARNING, __FILE__, __LINE__);
                    }
                    break;

                case 'update':
                    // Update existing Stripe customer.
                    if (!self::stripeCustomerUpdate($frm)) {
                        $app->raiseMsg(sprintf(_("There was an error saving your credit card details. We got everything else, but please try again with the credit card."), null), MSG_WARNING, __FILE__, __LINE__);
                    }
                    break;
            }
        }

        // Create the record under the user's account, unless an account_id was provided.
        $account_id = isset($frm['account_id']) && is_numeric($frm['account_id']) ? $frm['account_id'] : $auth->get('account_id');

        // Update record data.
        $app->logMsg(sprintf('%s with data %s', __METHOD__, getDump($frm)), LOG_DEBUG, __FILE__, __LINE__);
        $db->query("
            UPDATE account_tbl SET
                organization = '" . $db->escapeString($frm['organization']) . "',
                address1 = '" . $db->escapeString($frm['address1']) . "',
                address2 = '" . $db->escapeString($frm['address2']) . "',
                city = '" . $db->escapeString($frm['city']) . "',
                state = '" . $db->escapeString($frm['state']) . "',
                zip = '" . $db->escapeString($frm['zip']) . "',
                country = '" . $db->escapeString($frm['country']) . "',
                url = '" . $db->escapeString($frm['url']) . "',
                notes = '" . $db->escapeString($frm['notes']) . "',
                $payment_type_clause
                available_credit = '" . $db->escapeString($frm['available_credit']) . "',
                used_credit = '" . $db->escapeString($frm['used_credit']) . "',
                recharge_amount = '" . $db->escapeString($frm['recharge_amount']) . "',
                modified_datetime = NOW(),
                modified_by_user_id = '" . $db->escapeString($auth->get('user_id')) . "'
            WHERE account_id = '" . $db->escapeString($frm['account_id']) . "'
        ");

        // Create version.
        $version = Version::getInstance($auth);
        $version->create('account_tbl', 'account_id', $frm['account_id'], $frm['organization']);

        // Unlock record.
        $lock->remove();
    }

    /*
    *
    *
    * @access   public
    * @param
    * @return
    * @author   Quinn Comendant <quinn@strangecode.com>
    * @version  1.0
    * @since    16 Nov 2014 17:45:40
    */
    static public function delete($id)
    {
        global $lock, $cache, $locally_carried_queries;
        $db =& DB::getInstance();
        $app =& App::getInstance();

        $lock->select('account_tbl', 'account_id', $id);
        if ($lock->isLocked() && !$lock->isMine()) {
            $lock->dieErrorPage();
        }

        // Remove any stale cached list data.
        $cache->delete('account list');

        // Delete the record.
        $db->query("DELETE FROM account_tbl WHERE account_id = '" . $db->escapeString($id) . "'");

        // Unlock record.
        $lock->remove();
    }

    /*
    *
    *
    * @access   public
    * @param
    * @return
    * @author   Quinn Comendant <quinn@strangecode.com>
    * @version  1.0
    * @since    16 Nov 2014 17:45:40
    */
    static public function getPaginatedList($where_clause='')
    {
        global $page, $so, $cache;
        $db =& DB::getInstance();
        $app =& App::getInstance();

        $where_clause .= (empty($where_clause) ? 'WHERE' : ' AND') . " user_tbl.user_type = 'primary'";

        // Build search query if available.
        if (getFormData('q', false)) {
            $qry_words = preg_split('/[^\w]/', getFormData('q'));
            for ($i=0; $i<sizeof($qry_words); $i++) {
                $where_clause .= (empty($where_clause) ? 'WHERE' : ' AND') . "
                    (
                        account_tbl.organization LIKE '%" . $db->escapeString($qry_words[$i]) . "%'
                        OR account_tbl.address1 LIKE '%" . $db->escapeString($qry_words[$i]) . "%'
                        OR account_tbl.address2 LIKE '%" . $db->escapeString($qry_words[$i]) . "%'
                        OR account_tbl.city LIKE '%" . $db->escapeString($qry_words[$i]) . "%'
                        OR account_tbl.state LIKE '%" . $db->escapeString($qry_words[$i]) . "%'
                        OR account_tbl.zip LIKE '%" . $db->escapeString($qry_words[$i]) . "%'
                        OR account_tbl.country LIKE '%" . $db->escapeString($qry_words[$i]) . "%'
                        OR account_tbl.url LIKE '%" . $db->escapeString($qry_words[$i]) . "%'
                        OR account_tbl.notes LIKE '%" . $db->escapeString($qry_words[$i]) . "%'
                        OR user_tbl.first_name LIKE '%" . $db->escapeString($qry_words[$i]) . "%'
                        OR user_tbl.last_name LIKE '%" . $db->escapeString($qry_words[$i]) . "%'
                        OR user_tbl.email LIKE '%" . $db->escapeString($qry_words[$i]) . "%'
                    )
                ";
            }
        }

        if (getFormData('filter_country', false)) {
            // Limit by filter.
            $where_clause .= (empty($where_clause) ? 'WHERE' : ' AND') . " account_tbl.country = '" . $db->escapeString(getFormData('filter_country')) . "'";
        }

        // Count the total number of records so we can do something about the page numbers.
        $qid = $db->query("
            SELECT COUNT(*)
            FROM account_tbl
            LEFT JOIN user_tbl ON (account_tbl.account_id = user_tbl.account_id)
            $where_clause
        ");
        list($num_results) = mysql_fetch_row($qid);

        // Set page numbers now we know (needed for next step).
        $page->setTotalItems($num_results);
        $page->calculate();

        // Final SQL, with sort and page limiters.
        $sql = "
            SELECT
                account_tbl.*,
                (SELECT COUNT(*) FROM user_tbl WHERE user_tbl.account_id = account_tbl.account_id) AS num_users,
                (SELECT COUNT(*) FROM survey_tbl WHERE survey_tbl.account_id = account_tbl.account_id) AS num_surveys,
                a1.username AS added_by_username,
                a2.username AS modified_by_username
            FROM account_tbl
            LEFT JOIN user_tbl a1 ON (account_tbl.added_by_user_id = a1.user_id)
            LEFT JOIN user_tbl a2 ON (account_tbl.modified_by_user_id = a2.user_id)
            LEFT JOIN user_tbl ON (account_tbl.account_id = user_tbl.account_id)
            $where_clause
            " . $so->getSortOrderSQL() . "
            " . $page->getLimitSQL() . "
        ";

        // Use a cache hash to determine if the result-set has changed.
        // A unique key for this query, with the total_items in case db records
        // were added since the last cache. This identifies a unique set of
        // cached data, but we must refer to the list that is cached by a more
        // generic name. so that we can flush the cache (if records updated)
        // without knowing the hash.
        $cache_hash = md5($sql . '|' . $page->total_items);
        $tmp_prefs = new Prefs('session', array('storagetype' => 'session'));
        if ($tmp_prefs->get('cache_hash') != $cache_hash) {
            $cache->delete('account list');
            $tmp_prefs->set('cache_hash', $cache_hash);
        }

        // First try to return from the cache.
        if ($cache->exists('account list')) {
            return $cache->get('account list');
        }

        // The list was not cached, so issue the real query.
        $qid = $db->query($sql);
        $list = array();
        while ($row = mysql_fetch_assoc($qid)) {
            $list[] = $row;
        }

        // Save this list into the cache.
        if (isset($list) && !empty($list)) {
            $cache->set('account list', $list);
        }

        return $list;
    }

    /*
    *
    *
    * @access   public
    * @param
    * @param
    * @param
    * @param
    * @param
    * @param
    * @return
    * @author   Quinn Comendant <quinn@strangecode.com>
    * @version  1.0
    * @since    17 Nov 2014 00:33:41
    */
    static public function stripeCustomerCreate($frm)
    {
        global $app;

        if (!isset($frm['stripe_token']) || '' == $frm['stripe_token']) {
            $app->logMsg(sprintf('Unable to create Stripe customer without token.', null), LOG_ERR, __FILE__, __LINE__);
            return false;
        }


        try {
            // Add new customer to Stripe.
            // This may occur when adding an new account, or updating an account which doesn't yet have a CC stored at Stripe.
            $user = User::get(array('account_id' => $frm['account_id'], 'user_type' => 'primary'));
            $stripe_customer = Stripe_Customer::create(array(
                'card' => $frm['stripe_token'],
                'email' => $user['email'],
                'description' => $frm['organization'],
            ));
            $app->logMsg(sprintf('Stripe customer added: %s (%s)', $frm['organization'], $stripe_customer->id), LOG_INFO, __FILE__, __LINE__);
            return $stripe_customer->id;
        } catch (Stripe_CardError $e) {
            $body = $e->getJsonBody();
            $err = $body['error'];
            $app->logMsg(sprintf('Stripe card error: %s.', $err['message']), LOG_ERR, __FILE__, __LINE__);
            $app->raiseMsg(sprintf(_("Credit card error: %s."), $err['message']), MSG_NOTICE, __FILE__, __LINE__);
            return false;
        } catch (Stripe_InvalidRequestError $e) {
            $body = $e->getJsonBody();
            $err = $body['error'];
            $app->logMsg(sprintf('Stripe invalid request: %s.', $err['message']), LOG_ERR, __FILE__, __LINE__);
            // $app->raiseMsg(sprintf(_("Stripe invalid request: %s."), $err['message']), MSG_ERR, __FILE__, __LINE__);
            return false;
        } catch (Stripe_AuthenticationError $e) {
            $body = $e->getJsonBody();
            $err = $body['error'];
            $app->logMsg(sprintf('Stripe authentication error: %s.', $err['message']), LOG_ERR, __FILE__, __LINE__);
            // $app->raiseMsg(sprintf(_("Stripe authentication error: %s."), $err['message']), MSG_ERR, __FILE__, __LINE__);
            return false;
        } catch (Stripe_Error $e) {
            $body = $e->getJsonBody();
            $err = $body['error'];
            $app->logMsg(sprintf('Stripe generic error: %s.', $err['message']), LOG_ERR, __FILE__, __LINE__);
            // $app->raiseMsg(sprintf(_("Stripe generic error: %s."), $err['message']), MSG_ERR, __FILE__, __LINE__);
            return false;
        } catch (Exception $e) {
            // Something else happened, completely unrelated to Stripe
            $app->logMsg(sprintf('Stripe unknown error: %s', $e->getMessage()), LOG_ERR, __FILE__, __LINE__);
            // $app->raiseMsg(sprintf(_("An unknown error occured: %s."), $e->getMessage()), MSG_ERR, __FILE__, __LINE__);
            return false;
        }
    }

    /*
    *
    *
    * @access   public
    * @param
    * @param
    * @param
    * @param
    * @param
    * @param
    * @return
    * @author   Quinn Comendant <quinn@strangecode.com>
    * @version  1.0
    * @since    17 Nov 2014 00:33:34
    */
    static public function stripeCustomerUpdate($frm)
    {
        global $app;

        try {
            // Updating existing Stripe Customer.
            $user = User::get(array('account_id' => $frm['account_id'], 'user_type' => 'primary'));
            $stripe_customer = Stripe_Customer::retrieve($frm['stripe_customer_id']);
            if (isset($frm['stripe_token']) && '' != $frm['stripe_token']) {
                // Only update card if a new one is submitted.
                $stripe_customer->card = $frm['stripe_token'];
            }
            $stripe_customer->description = $frm['organization'];
            $stripe_customer->email = $user['email'];
            $stripe_customer->save();
            $app->logMsg(sprintf('Stripe customer updated: %s (%s)', $frm['organization'], $stripe_customer->id), LOG_INFO, __FILE__, __LINE__);
            // $app->raiseMsg(sprintf(_("%s CC details updated with Stripe."), $frm['organization']), MSG_SUCCESS, __FILE__, __LINE__);
        } catch (Stripe_CardError $e) {
            $body = $e->getJsonBody();
            $err = $body['error'];
            $app->logMsg(sprintf('Stripe card error: %s.', $err['message']), LOG_ERR, __FILE__, __LINE__);
            $app->raiseMsg(sprintf(_("Credit card error: %s."), $err['message']), MSG_NOTICE, __FILE__, __LINE__);
            return false;
        } catch (Stripe_InvalidRequestError $e) {
            $body = $e->getJsonBody();
            $err = $body['error'];
            $app->logMsg(sprintf('Stripe invalid request: %s.', $err['message']), LOG_ERR, __FILE__, __LINE__);
            // $app->raiseMsg(sprintf(_("Stripe invalid request: %s."), $err['message']), MSG_ERR, __FILE__, __LINE__);
            return false;
        } catch (Stripe_AuthenticationError $e) {
            $body = $e->getJsonBody();
            $err = $body['error'];
            $app->logMsg(sprintf('Stripe authentication error: %s.', $err['message']), LOG_ERR, __FILE__, __LINE__);
            // $app->raiseMsg(sprintf(_("Stripe authentication error: %s."), $err['message']), MSG_ERR, __FILE__, __LINE__);
            return false;
        } catch (Stripe_Error $e) {
            $body = $e->getJsonBody();
            $err = $body['error'];
            $app->logMsg(sprintf('Stripe generic error: %s.', $err['message']), LOG_ERR, __FILE__, __LINE__);
            $app->raiseMsg(sprintf(_("Stripe generic error: %s."), $err['message']), MSG_ERR, __FILE__, __LINE__);
            return false;
        } catch (Exception $e) {
            // Something else happened, completely unrelated to Stripe
            $app->logMsg(sprintf('Stripe unknown error: %s', $e->getMessage()), LOG_ERR, __FILE__, __LINE__);
            // $app->raiseMsg(sprintf(_("An unknown error occured: %s."), $e->getMessage()), MSG_ERR, __FILE__, __LINE__);
            return false;
        }

        return true;
    }

    /*
    *
    *
    * @access   public
    * @param
    * @param
    * @param
    * @param
    * @param
    * @param
    * @return
    * @author   Quinn Comendant <quinn@strangecode.com>
    * @version  1.0
    * @since    17 Nov 2014 00:30:17
    */
    // static public function sendAdminEmail($account_id, $subject_string, $action='new_account')
    // {
    //     global $app, $auth;
    //
    //     $account = parent::get(array('account_id' => $account_id));
    //     $user = User::get(array('account_id' => $account_id, 'user_type' => 'primary'));
    //
    //     $email = new Email(array(
    //         'from' => sprintf('"%s %s" <%s>', $user['first_name'], $user['last_name'], $user['email']),
    //         'to' => 'Strangecode Helmsmen <quinn@strangecode.com>',
    //         'subject' => sprintf($subject_string, $account['organization']),
    //     ));
    //
    //     $cc_number = preg_replace('/[^\d]/', '', getFormData('cc_number'));
    //     $payment_details = sprintf("%s\n••••••••••••%s (%s/%s)\nStripe one-use token: %s\nStripe customer ID: %s\n%s",
    //         getFormData('cc_cardholder'),
    //         substr(trim(getFormData('cc_number')), -4),
    //         getFormData('cc_exp_m'),
    //         getFormData('cc_exp_y'),
    //         getFormData('stripe_token', 'NONE'),
    //         ('' == $account['stripe_customer_id'] ? 'NONE' : $account['stripe_customer_id'])
    //     );
    //
    //     $email_replace_values = array(
    //         'ACCOUNT_ID' => addSignature($account_id, null, 5),
    //         'SITE_NAME' => $app->getParam('site_name'),
    //         'SITE_URL' => $app->getParam('site_url'),
    //         'SITE_EMAIL' => $app->getParam('site_email'),
    //         'COMPANY' => $account['company'],
    //         'FIRST_NAME' => $user['first_name'],
    //         'LAST_NAME' => $user['last_name'],
    //         'EMAIL' => $user['email'],
    //         'PHONE' => $user['phone'],
    //         'ADDRESS' => $account['street'],
    //         'CITY' => $account['city'],
    //         'STATE' => $account['state'],
    //         'ZIP_CODE' => $account['zip'],
    //         'COUNTRY' => $account['country'],
    //         'PAYMENT_DETAILS' => $payment_details,
    //         'ADMIN_EDIT_ACCOUNT_LINK' => sprintf('%s/accounts.php?op=edit&account_id=%s', $app->getParam('site_url'), $account_id),
    //     );
    //
    //     switch ($action) {
    //     case 'new_account':
    //         $email->setTemplate('new_account.eml');
    //         $email->replace($email_replace_values);
    //         break;
    //     }
    //
    //     $email->send();
    // }

    /*
    *
    *
    * @access   public
    * @param
    * @param
    * @param
    * @param
    * @param
    * @param
    * @return
    * @author   Quinn Comendant <quinn@strangecode.com>
    * @version  1.0
    * @since    17 Nov 2014 00:30:24
    */
    // static public function sendClientEmail($account_id, $subject_string, $action='new_account')
    // {
    //     global $app, $auth, $domains_for_transfer;
    //
    //     $account = parent::get(array('account_id' => $account_id));
    //     $user = User::get(array('account_id' => $account_id, 'user_type' => 'primary'));
    //
    //     $email = new Email(array(
    //         'from' => 'Strangecode <hosting@strangecode.com>',
    //         'to' => sprintf('"%s %s" <%s>', $user['first_name'], $user['last_name'], $user['email']),
    //         'subject' => sprintf($subject_string, $account['organization']),
    //     ));
    //
    //     $email_replace_values = array(
    //         'ACCOUNT_ID' => addSignature($account_id, null, 5),
    //         'SITE_NAME' => $app->getParam('site_name'),
    //         'SITE_URL' => $app->getParam('site_url'),
    //         'SITE_EMAIL' => $app->getParam('site_email'),
    //         'COMPANY' => $account['company'],
    //         'FIRST_NAME' => $user['first_name'],
    //         'LAST_NAME' => $user['last_name'],
    //         'EMAIL' => $user['email'],
    //         'PHONE' => $user['phone'],
    //         'ADDRESS' => $account['street'],
    //         'CITY' => $account['city'],
    //         'STATE' => $account['state'],
    //         'ZIP_CODE' => $account['zip'],
    //         'COUNTRY' => $account['country'],
    //     );
    //
    //     switch ($action) {
    //     case 'new_account':
    //         $email->setTemplate('new_account.eml');
    //         $email->replace($email_replace_values);
    //         break;
    //     }
    //
    //     $email->send();
    // }
}