SocialDAO.php

<?php

namespace Aiir\Apps\SocialPublisher;

use Aiir\Apps\SocialPublisher\Models\Post as Post;
use Aiir\Apps\SocialPublisher\Models\Group as Group;
use Aiir\Apps\SocialPublisher\Models\Account as Account;
use Aiir\Apps\SocialPublisher\Models\Variable as Variable;
use Aiir\Apps\SocialPublisher\Models\AccountVariable as AccountVariable;



class SocialDAO
{

    private $db;
    private $clientId;
    private $siteId;
    private $userId;
    private $accounts;


    public function __construct($db)
    {
        $this->db = $db;
    }

    public function setClientId($id)
    {
        $this->clientId = $id;
    }

    public function setUserId($id)
    {
        $this->userId = $id;
    }

    public function setSiteId($id)
    {
        $this->siteId = $id;
    }


    /**
     * Retrieve an array of Account objects within the client scope
     *
     * @return array - Account objects
     */
    public function getClientAccounts()
    {
        $accounts = $this->db->query('SELECT * FROM account
                                      WHERE client_id = :client_id
                                      ORDER BY account.name',
            [
                'client_id' => $this->clientId
            ]);

        $accounts = $this->getAccountObjects($accounts);

        return $accounts;
    }


    /**
     * Add a Social Account to the database
     *
     * @param Account $account
     * @return Account
     */
    public function createAccount(Account $account)
    {
        $this->db->query("INSERT INTO account
                          (network, client_id, network_user_id, cat, name, token, secret, assoc_id, expires, email, parent_token)
                          VALUES (:network, :client_id, :network_user_id, :cat, :name, :token, :secret, :assoc_id, :expires, :email, :parent_token)",
            [
                'network'         => $account->getNetwork(),
                'client_id'       => $this->clientId,
                'network_user_id' => $account->getNetworkUserId(),
                'cat'             => $account->getCat(),
                'name'            => $account->getName(),
                'token'           => $account->getToken(),
                'secret'          => $account->getSecret(),
                'assoc_id'        => $account->getAssocId(),
                'expires'         => $account->getExpires(),
                'email'           => $account->getEmail(),
                'parent_token'    => $account->getParentToken()
            ]);

        $id = $this->db->lastInsertId();
        $account->setId($id);

        $this->addAccountToStudioInbox($account);

        return $account;
    }


    /**
     * Update an existing Social Account
     *
     * @param Account $account
     * @return Account $account
     */
    public function updateAccount(Account $account)
    {
        $this->db->query('UPDATE account
                          SET name = :name,
                          web_url = :web_url,
                          email = :email,
                          loc_pref = :loc_pref
                          WHERE id = :id
                            AND client_id = :client_id
                          LIMIT 1',
            [
                'name'      => $account->getName(),
                'web_url'   => $account->getWebUrl(),
                'email'     => $account->getEmail(),
                'loc_pref'  => $account->getLocPref(),
                'id'        => $account->getId(),
                'client_id' => $this->clientId
            ]);

        $this->updateAccountInStudioInbox($account);

        return $account;
    }


    /**
     * Update a Facebook Social Account - use for FB integration
     *
     * @param Account $account
     * @return Account
     */
    public function updateFacebookAccount(Account $account)
    {
        $this->db->query('UPDATE account
                          SET name = :name,
                          token = :token,
                          expires = :expires
                          WHERE id = :id
                          LIMIT 1',
            [
                'name'    => $account->getName(),
                'token'   => $account->getToken(),
                'expires' => $account->getExpires()
            ]);

        return $account;
    }


    /**
     * Delete a Social Account
     *
     * @param Account $account
     * @return Account|null
     */
    public function deleteAccount(Account $account)
    {
        $this->db->query('DELETE FROM account
                          WHERE id = :id
                          LIMIT 1',
            [
                'id' => $account->getId()
            ]);

        $this->deleteAccountFromStudioInbox($account);

        $account = null;

        return $account;
    }


    /**
     * Add a Social Account to Studio Inbox
     *
     * @param Account $account
     * @return Account
     */
    private function addAccountToStudioInbox(Account $account)
    {
        $this->db->query('UPDATE studioinbox.source
                          SET name = :name,
                          type = :type
                          WHERE soc_acc_id = :soc_acc_id
                            AND client_id = :client_id
                          LIMIT 1',
            [
                'name'       => $account->getName(),
                'type'       => $account->getNetwork(),
                'soc_acc_id' => $account->getId(),
                'client_id'  => $this->clientId
            ]);

        return $account;
    }


    /**
     * Update a Social Account in Studio Inbox
     *
     * @param Account $account
     * @return Account
     */
    private function updateAccountInStudioInbox(Account $account)
    {
        $this->db->query('UPDATE studioinbox.source
                          SET name = :name
                          WHERE soc_acc_id = :soc_acc_id
                            AND client_id = :client_id
                          LIMIT 1',
            [
                'name' => $account->getName(),
                'soc_acc_id' => $account->getId(),
                'client_id' => $this->clientId
            ]);

        return $account;
    }


    /**
     * Update a Social Account within Studio Inbox
     *
     * @param Account $account
     * @return Account
     */
    private function deleteAccountFromStudioInbox(Account $account)
    {
        $this->db->query('DELETE FROM studioinbox.source
                          WHERE soc_acc_id = :soc_acc_id
                            AND client_id = :client_id
                          LIMIT 1',
            [
                'soc_acc_id' => $account->getId(),
                'client_id'  => $this->clientId
            ]);

        return true;
    }


    /**
     * Use to retrieve a Users Social Account access
     */
    public function getUserAccounts()
    {
        $accounts = $this->db->query('SELECT * FROM account
                                      INNER JOIN user_account
                                        ON account.id = user_account.account_id
                                      WHERE user_account.user_id = :user_id',
            [
                'user_id' => $this->userId
            ]);

        $accounts = $this->getAccountObjects($accounts);

        return $accounts;
    }


    /**
     * Use to retrieve Post objects
     */
    public function getPosts(array $filters)
    {
        $queryCSV = $this->prepareQueryWithAccounts($this->accounts);
        $whereClause = $this->createQueryWhereClause($filters);

        $params = array_merge($whereClause['params'], $queryCSV['params']);

        $posts = $this->db->query('SELECT post.*, GROUP_CONCAT(account_id) AS accounts
                                     FROM social.post post
                                     INNER JOIN social.post_account acc_post
                                        ON acc_post.post_id = post.id
                                     WHERE ' . $whereClause['where'] . '
                                        AND acc_post.account_id IN (' . $queryCSV['csv'] . ')
                                     GROUP BY post.id',
            $params);

        $posts = $this->getPostObjects($posts, $this->accounts);

        return $posts;
    }


    /**
     * Retrieves a single post from the database and returns the record as a Post object
     *
     * @param int $id
     * @return mixed
     */
    public function getPost(int $id)
    {
        $queryCSV = $this->preapreQueryWithAccounts($this->accounts);
        $queryCSV['params']['id'] = $id;

        $post = $this->db->query('SELECT post.*, GROUP_CONCAT(account_id) AS accounts FROM social.post post
                                  INNER JOIN social.post_account acc_post
                                      ON acc_post.post_id = post.id
                                  WHERE id = :id
                                    AND acc_post.status = 0
                                    AND acc_post.account_id IN (' . $queryCSV['csv'] . ')
                                  GROUP BY id
                                  LIMIT 1',
            $queryCSV['params']);

        if (empty($post)) {
            return false;
        }

        $post = $this->getPostObjects($post, $this->accounts);

        return $post[0];
    }


    /**
     * Retrieves a single post from the database and returns the record as a Post object
     *
     * @param string $token
     * @return mixed
     */
    public function getPostByToken($token)
    {
        $post = $this->db->query('SELECT post.*, GROUP_CONCAT(account_id) AS accounts FROM social.post post
                                  INNER JOIN social.post_account acc_post
                                      ON acc_post.post_id = post.id
                                  WHERE token = :token
                                  GROUP BY id
                                  LIMIT 1',
            [
                'token' => $token
            ]);

        if (empty($post)) {
            return false;
        }

        $accounts = explode(',', $post[0]['accounts']);
        $accounts = $this->getAccounts($accounts);

        $post = $this->getPostObjects($post, $accounts);

        return $post[0];
    }


    private function getAccounts(array $accounts)
    {
        $queryCSV = prepareQueryCSV($accounts);

        $accounts = $this->db->query('SELECT * FROM account
                                      WHERE id IN (' . $queryCSV['csv'] . ')',
            $queryCSV['params']);

        $accounts = $this->getAccountObjects($accounts);

        return $accounts;
    }


    /**
     * Creates a post in the database and returns a Post object
     *
     * @param array $data
     * @return \Aiir\Apps\SocialPublisher\Post
     */
    public function createPost(array $data, array $accIds)
    {
        $insert['keys'] = implode(',', array_keys($data));
        $insert['vals'] = ':' . implode(',:', array_keys($data));
        $insert['params'] = $data;

        $this->db->query('INSERT INTO post
                          (' . $insert['keys'] . ')
                          VALUES (' . $insert['vals'] . ')',
            $insert['params']);

        $data['id'] = $this->db->lastInsertId();

        $post = new Post($data);

        if (!empty($accIds)) {
            $this->addPostToAccounts($post, $accIds);
        }

        return $post;
    }


    /**
     * Updates a post in the database and returns the updated Post object
     *
     * @return \Aiir\Apps\SocialPublisher\Post $post
     */
    public function updatePost(Post $post, array $accounts)
    {
        $this->db->query('UPDATE post
                          SET message = :message,
                              scheduled_dt = :scheduled_dt,
                              modified_user_id = :modified_user_id,
                              modified_dt = :modified_dt
                          WHERE id = :id',
            [
                'id'               => $post->getId(),
                'message'          => $post->getMessage(),
                'scheduled_dt'     => $post->getScheduledDt(),
                'modified_user_id' => $post->getModifiedUserId(),
                'modified_dt'      => $post->getModifiedDt()
            ]);

        $this->removePostFromAccounts($post);
        $this->addPostToAccounts($post, $accounts);

        return $post;
    }


    /**
     * Delete a post from the database
     *
     * @param int $id
     * @return mixed
     */
    public function deletePost(int $id)
    {
        $queryCSV = $this->prepareQueryWithAccounts($this->accounts);

        $queryCSV['params']['post_id'] = $id;

        $result = $this->db->query('DELETE post, post_account
                                    FROM post
                                    INNER JOIN post_account
                                      ON post.id = post_account.post_id
                                    WHERE post.id = :post_id
                                      AND post_account.account_id IN (' . $queryCSV['csv'] . ')',
            $queryCSV['params']);

        return $result;
    }


    /**
     * User Management
     * Retrieve a list of Social Accounts within the users scope
     * that can be assigned to the user
     *
     * How we do this
     * 1. Retrieve site ids within the users scope with the first query
     * 2. Retrieve social accounts based upon the client id of above sites
     *
     * Note: you must set the $userId and $clientId first
     *
     * @return array - Account objects
     */
    public function getUserAccountsSelection()
    {
        $sites = $this->getUserSiteAccess();

        $siteIds = [];
        foreach ($sites as $site) {
            $siteIds[] = $site['userAssocSite'];
        }

        $queryCSV = prepareQueryCSV($siteIds);

        $accounts = $this->getAccountsFromUserSites($queryCSV);
        $accounts = $this->getAccountObjects($accounts);

        return $accounts;
    }

    /**
     * When managing or adding a new user this method is used
     * to populate the social account selector
     *
     * @param array $siteIds
     * @return array - Account objects
     */
    public function getUserManagementSocialSelection(array $siteIds)
    {
        $queryCSV = prepareQueryCSV($siteIds);

        $accounts = $this->getAccountsFromUserSites($queryCSV);
        $groups = $this->getGroupsFromUserSites($queryCSV);

        return [$accounts, $groups];
    }


    /**
     * Add a new social group to the database
     *
     * @param array $data
     * @return Group
     */
    public function addGroup(array $data)
    {
        $this->db->query('INSERT INTO accgroup
                          (name, client_id)
                          VALUES (:name, :client_id)',
            [
                'name'      => $data['name'],
                'client_id' => $this->clientId
            ]);

        $data['id'] = $this->db->lastInsertId();
        $data['client_id'] = $this->clientId;

        $group = new Group($data);

        $this->addAccountsToGroup($group, $data['accounts']);
        $group->injectAccounts($this->accounts, $data['accounts']);

        return $group;
    }


    /**
     * Use to retrieve a collection of Social Group Objects within the users client scope
     *
     * Note: your must set the $clientId to the id of the current client
     *
     * @return array
     */
    public function getGroups()
    {
        $results = $this->db->query('SELECT *, GROUP_CONCAT(account_id) AS acc_id  FROM accgroup gp
                                     INNER JOIN accgroup_account ga
                                        ON gp.id = ga.group_id
                                     WHERE client_id = :client_id
                                     GROUP BY gp.id
                                     ORDER BY gp.name',
            [
                'client_id' => $this->clientId
            ]);

        $group = $this->getGroupObjects($results);

        return $group;
    }


    /**
     * Use to retrieve a single Social Group object by ID within the users client scope
     *
     * @param int $id
     * @return Group
     */
    public function getGroup(int $id)
    {
        $group = $this->db->query('SELECT *, GROUP_CONCAT(account_id) AS acc_id FROM accgroup gp
                                   INNER JOIN accgroup_account ga
                                      ON gp.id = ga.group_id
                                   WHERE gp.id = :id
                                      AND client_id = :client_id',
            [
                'id'        => $id,
                'client_id' => $this->clientId
            ]);

        $group = $this->getGroupObjects($group);

        return $group[0];
    }


    /**
     * Use to delete a Social Group from the database by ID
     * Note: Social Group must be within users client scope
     *
     * @param int $id
     * @return mixed
     */
    public function deleteGroup(int $id)
    {
        $result = $this->db->query('DELETE FROM accgroup
                                    WHERE id = :id
                                        AND client_id = :client_id',
            [
                'id'        => $id,
                'client_id' => $this->clientId
            ]);

        return $result;
    }


    /**
     * Use to update a Social Group in the database
     *
     * @param Group $group
     * @param $accounts
     * @return Group
     */
    public function updateGroup(Group $group, $accounts)
    {
        $this->db->query('UPDATE accgroup 
                          SET name = :name 
                          WHERE id = :id',
            [
                'id'   => $group->getId(),
                'name' => $group->getName()
            ]);

        $this->removeAccountsFromGroups($group);
        $this->addAccountsToGroup($group, $accounts);

        $group->injectAccounts($this->accounts, $accounts);

        return $group;
    }


    public function addUserToAccounts(array $accounts)
    {
        $params = [
            'user_id' => $this->userId
        ];

        $rows = [];

        foreach ($accounts as $account) {
            $params['account_' . $account] = $account;
            $rows[] = '(:user_id, :account_' . $account . ')';
        }

        $queryRows = implode(',', $rows);

        $this->db->query('INSERT INTO user_account
                          (user_id, account_id)
                          VALUES ' . $queryRows,
            $params);

        return true;
    }


    public function updateUserAccounts(array $accounts)
    {
        $this->removeUserFromAccounts();
        $this->addUserToAccounts($accounts);
    }


    public function getVariable(int $id)
    {
        $result = $this->db->query('SELECT * FROM variable
                                    WHERE variable.id = :id',
            [
                'id' => $id
            ]);

        $variable = new Variable($result[0]);

        $values = $this->db->query('SELECT * FROM account_variable
                                    WHERE variable_id = :variable_id',
            [
                'variable_id' => $id
            ]);

        $vals = [];
        foreach ($values as $value) {
            $vals[$value['account_id']] = $value['val'];
        }

        $variable->setValues($vals);

        return $variable;
    }


    public function getVariables()
    {
        $results = $this->db->query('SELECT * FROM variable
                                     WHERE client_id = :client_id',
            [
                'client_id' => $this->clientId
            ]);

        $variables = [];
        foreach ($results as $variable) {
            $variables[] = new Variable($variable);
        }

        return $variables;
    }


    public function createVariable(array $data)
    {
        $this->db->query('INSERT INTO variable
                          (name, client_id)
                          VALUES (:name, :client_id)',
            [
                'name'      => $data['name'],
                'client_id' => $this->clientId
            ]);

        $data['id'] = $this->db->lastInsertId();

        $variable = new Variable($data);

        $accounts = array_filter($data['accounts']);

        if ($accounts) {
            $this->addVariableToAccounts($variable, $accounts);
        }

        return $variable;
    }


    public function updateVariable(Variable $variable, array $accounts)
    {
        $this->db->query('UPDATE variable
                          SET name = :name
                          WHERE id = :id',
            [
                'id'   => $variable->getId(),
                'name' => $variable->getName()
            ]);

        $this->removeAccountsFromVariables($variable);

        $accounts = array_filter($accounts);

        if ($accounts) {
            $this->addVariableToAccounts($variable, $accounts);
            $variable->setValues($accounts);
        }

        return $variable;
    }

    private function removeAccountsFromVariables(Variable $variable)
    {
        $this->db->query('DELETE FROM account_variable
                          WHERE variable_id = :variable_id',
            [
                'variable_id' => $variable->getId()
            ]);

        return true;
    }


    /**
     * Use to delete a Variable from the database by ID
     *
     * @param int $id
     * @return mixed
     */
    public function deleteVariable(int $id)
    {
        $result = $this->db->query('DELETE FROM variable
                                    WHERE id = :id
                                        AND client_id = :client_id',
            [
                'id'        => $id,
                'client_id' => $this->clientId
            ]);

        return $result;
    }


    /**
     * Add Variable and it's value to Accounts
     *
     * @param Variable $variable
     * @param array $accounts
     * @return bool
     */
    private function addVariableToAccounts(Variable $variable, array $accounts)
    {
        $params = [
            'variable_id' => $variable->getId()
        ];

        $rows = [];

        foreach ($accounts as $key => $account) {
            if (!empty($account)) {
                $params['account_' . $key] = $key;
                $params['val_' . $key] = $account;
                $rows[] = '(:account_' . $key . ', :variable_id, :val_' . $key . ')';
            }
        }

        $queryRows = implode(',', $rows);

        $this->db->query('INSERT INTO account_variable
                          (account_id, variable_id, val)
                          VALUES ' . $queryRows,
            $params);

        return true;
    }


    private function removeUserFromAccounts()
    {
        $this->db->query('DELETE FROM user_account
                          WHERE user_id = :user_id',
            [
                'user_id' => $this->userId
            ]);

        return true;
    }


    /**
     * Takes available accounts and prepares them for IN usage in SQL queries
     * This means queries cannot be executed on accounts the user does NOT have access too
     *
     * @param array $accounts
     * @return array
     */
    private function prepareQueryWithAccounts(array $accounts)
    {
        $accountIds = [];
        foreach ($accounts as $account) {
            $accountIds[] = $account->getId();
        }

        return prepareQueryCSV($accountIds);
    }


    /**
     * Builds a where clause which can be used within a SQL query
     *
     * @param array $filters
     * @return string
     */
    private function createQueryWhereClause(array $filters)
    {
        $where = [];
        $params = [];
        foreach ($filters as $key => $filter) {
            $where[] = $filter['field'] . ' ' . $filter['operator'] . ' :' . $filter['field'] . '_' . $key;
            $params[$filter['field'] . '_' . $key] = $filter['value'];
        }
        $clause['where'] = implode(' AND ', $where);
        $clause['params'] = $params;

        return $clause;
    }


    /**
     * Returns an array of social post objects
     *
     * @param array $results
     * @return array
     */
    private function getPostObjects(array $results, array $accounts)
    {
        $posts = [];
        foreach ($results as $post) {

            // Instantiate a new post object
            $postObj = new Post($post);

            // Turn the Social Account ids from a query into an array
            $accIds = explode(',', $post['accounts']);

            $postObj->injectSocialAccounts($accounts, $accIds);

            $posts[] = $postObj;
        }

        return $posts;
    }


    /*
     * Creates the relationship between posts and social accounts the post will be published too
     *
     * @param Post $post
     * @param array $accounts - only needs to be provided when creating a new post
     */
    private function addPostToAccounts(Post $post, array $accounts = null)
    {
        if (empty($accounts)) {
            $accounts = [];
            foreach ($post->getSocialAccounts() as $account) {
                $accounts[] = $account->getId();
            }
        }

        $params = [
            'post_id' => $post->getId()
        ];

        $rows = [];

        foreach ($accounts as $account) {
            $params['account_' . $account] = $account;
            $rows[] = '(:account_' . $account . ', :post_id)';
        }

        $queryRows = implode(',', $rows);

        $this->db->query('INSERT INTO post_account
                          (account_id, post_id)
                          VALUES ' . $queryRows,
            $params);


        return true;
    }


    /*
     * Deletes posts from the look-up table
     */
    private function removePostFromAccounts(Post $post)
    {
        $result = $this->db->query('DELETE FROM post_account 
                                    WHERE post_id = :post_id',
            [
                'post_id' => $post->getId()
            ]);

        return $result;
    }


    /**
     * User Management
     *
     * Returns an array of site ids the user has access too
     *
     * @return mixed
     */
    private function getUserSiteAccess()
    {
        $result = $this->db->query('SELECT userAssocSite FROM users.user user
                                    INNER JOIN users.site site
                                      ON user.userId = site.userId
                                    WHERE user.userId = :user_id',
            [
                'user_id' => $this->userId
            ]);

        return $result;
    }


    /**
     * User Management
     *
     * Returns an array of social accounts
     *
     * @param $queryCSV
     * @return mixed
     */
    private function getAccountsFromUserSites($queryCSV)
    {
        $result = $this->db->query('SELECT account.* FROM account
                                    INNER JOIN sites.site site
                                      ON account.client_id = site.client_id
                                    WHERE site.id IN (' . $queryCSV['csv'] . ')
                                    GROUP BY id
                                    ORDER BY name ASC',
            $queryCSV['params']);

        return $result;
    }


    /**
     * User Management
     *
     * Returns an array of social groups
     *
     * @param $queryCSV
     * @return mixed
     */
    private function getGroupsFromUserSites($queryCSV)
    {
        $result = $this->db->query('SELECT accgroup.*, GROUP_CONCAT(account.id) AS accounts FROM accgroup
                                    INNER JOIN sites.site site
                                        ON accgroup.client_id = site.client_id
                                    INNER JOIN accgroup_account
                                        ON accgroup.id = accgroup_account.group_id
                                    INNER JOIN account
                                        ON accgroup_account.account_id = account.id
                                    WHERE site.id IN (' . $queryCSV['csv'] . ')
                                    GROUP BY id
                                    ORDER BY name ASC',
            $queryCSV['params']);

        return $result;
    }


    /**
     * Provide an array of accounts from an SQL query and receive objects back
     *
     * @param array $accounts
     * @return array - Account objects
     */
    private function getAccountObjects(array $accounts)
    {
        $accObjs = [];
        foreach ($accounts as $account) {
            $accObjs[] = new Account($account);
        }

        $this->getAccountVariables($accObjs);

        $this->accounts = $accObjs;

        return $accObjs;
    }


    private function getAccountVariables(array $accounts)
    {
        $queryCSV = $this->prepareQueryWithAccounts($accounts);

        $variables = $this->db->query('SELECT * FROM account_variable
                                       WHERE account_variable.account_id IN (' . $queryCSV['csv'] . ')',
            $queryCSV['params']);

        foreach ($accounts as $account) {
            $account->injectVariables($variables);
        }

        return $accounts;
    }


    /**
     * Provide an array of social groups from an SQL query and receive objects back
     * Social Group objects have related Social Account objects injected
     *
     * @param array $results
     * @return array
     */
    private function getGroupObjects(array $results)
    {
        $groups = [];
        foreach ($results as $group) {

            $groupObj = new Group($group);

            $accIds = explode(',', $group['acc_id']);
            $groupObj->injectAccounts($this->accounts, $accIds);

            $groups[] = $groupObj;
        }

        return $groups;
    }


    /**
     * Used to remove Accounts from Social Groups
     *
     * @param Group $group
     * @return mixed
     */
    private function removeAccountsFromGroups(Group $group)
    {
        $result = $this->db->query('DELETE FROM accgroup_account
                                    WHERE group_id = :group_id',
            [
                'group_id' => $group->getId()

            ]);

        return $result;
    }


    /**
     * Used to add Accounts to Social Groups
     *
     * @param Group $group
     * @param $accounts
     * @return bool
     */
    private function addAccountsToGroup(Group $group, $accounts)
    {
        $params = [
            'group_id' => $group->getId()
        ];

        $rows = [];

        foreach ($accounts as $account) {
            $params['account_' . $account] = $account;
            $rows[] = '(:group_id, :account_' . $account . ')';
        }

        $queryRows = implode(',', $rows);

        $this->db->query('INSERT INTO accgroup_account
                          (group_id, account_id)
                          VALUES ' . $queryRows,
            $params);


        return true;
    }
}