<?php

/**
 * @copyright Copyright (c) 2009-2018 ThemeCatcher (http://www.themecatcher.net)
 */
class Quform_Repository
{
    /**
     * Get the name of the forms table with the WP prefix added
     *
     * @return string
     */
    public function getFormsTableName()
    {
        global $wpdb;

        return $wpdb->prefix . 'quform_forms';
    }

    /**
     * Get the name of the entries table with the WP prefix added
     *
     * @return string
     */
    public function getEntriesTableName()
    {
        global $wpdb;

        return $wpdb->prefix . 'quform_entries';
    }

    /**
     * Get the name of the entry data table with the WP prefix added
     *
     * @return string
     */
    public function getEntryDataTableName()
    {
        global $wpdb;

        return $wpdb->prefix . 'quform_entry_data';
    }

    /**
     * Get the name of the entry labels table with the WP prefix added
     *
     * @return string
     */
    public function getEntryLabelsTableName()
    {
        global $wpdb;

        return $wpdb->prefix . 'quform_entry_labels';
    }

    /**
     * Get the name of the entry label mapping table with the WP prefix added
     *
     * @return string
     */
    public function getEntryEntryLabelsTableName()
    {
        global $wpdb;

        return $wpdb->prefix . 'quform_entry_entry_labels';
    }

    /**
     * Get all form rows
     *
     * @param   bool|null  $active  Select all (null), only active (true) or inactive (false) forms
     * @return  array
     */
    public function all($active = null)
    {
        global $wpdb;

        $sql = "SELECT * FROM " . $this->getFormsTableName() . " WHERE trashed = 0";

        if ($active !== null) {
            $sql .= $wpdb->prepare(" AND active = %d", $active ? 1 : 0);
        }

        $sql .= " ORDER BY id ASC";

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

    /**
     * Get form rows, including counts of read and unread entries
     *
     * @deprecated  2.1.0
     * @param       array  $args  The query args
     * @return      array
     */
    public function getFormsForListTable(array $args = array())
    {
        _deprecated_function(__METHOD__, '2.1.0', 'Quform_Repository::getForms()');

        return $this->getForms($args);
    }

    /**
     * Get form rows, including counts of read and unread entries
     *
     * @param   array  $args  The query args
     * @return  array
     */
    public function getForms(array $args = array())
    {
        global $wpdb;

        $args = wp_parse_args($args, array(
            'active' => null,
            'orderby' => 'updated_at',
            'order' => 'DESC',
            'trashed' => false,
            'offset' => 0,
            'limit' => 20,
            'search' => ''
        ));

        $sql = "SELECT SQL_CALC_FOUND_ROWS f.id, f.name, f.active, f.trashed, f.updated_at,
                COALESCE(e.cnt, 0) AS entries,
                COALESCE(u.cnt, 0) AS unread
                FROM " . $this->getFormsTableName() . " f
                LEFT JOIN ( SELECT form_id, COUNT(*) AS cnt FROM " . $this->getEntriesTableName() . " GROUP BY form_id ) e
                ON f.id = e.form_id
                LEFT JOIN ( SELECT form_id, COUNT(*) AS cnt FROM " . $this->getEntriesTableName() . " WHERE unread = 1 GROUP BY form_id ) u
                ON f.id = u.form_id";

        $where = array($wpdb->prepare('trashed = %d', $args['trashed'] ? 1 : 0));

        if ($args['active'] !== null) {
            $where[] = $wpdb->prepare('active = %d', $args['active'] ? 1 : 0);
        }

        if (Quform::isNonEmptyString($args['search'])) {
            $args['search'] = $wpdb->esc_like($args['search']);
            $where[] = $wpdb->prepare("name LIKE '%s'", '%' . $args['search'] . '%');
        }

        $sql .= " WHERE " . join(' AND ', $where);

        // Sanitize order/limit
        $args['orderby'] = in_array($args['orderby'], array('id', 'name', 'entries', 'active', 'created_at', 'updated_at')) ? $args['orderby'] : 'updated_at';
        $args['order'] = strtoupper($args['order']);
        $args['order'] = in_array($args['order'], array('ASC', 'DESC')) ? $args['order'] : 'DESC';
        $args['limit'] = (int) $args['limit'];
        $args['offset'] = (int) $args['offset'];

        $sql .= " ORDER BY `{$args['orderby']}` {$args['order']} LIMIT {$args['limit']} OFFSET {$args['offset']}";

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

    /**
     * Get all form configs
     *
     * @param   bool|null  $active  Select all (null), only active (true) or inactive (false) forms
     * @return  array
     */
    public function allForms($active = null)
    {
        $forms = array();
        $rows = $this->all($active);

        foreach ($rows as $row) {
            $config = maybe_unserialize(base64_decode($row['config']));

            if (is_array($config)) {
                $config = $this->addRowDataToConfig($row, $config);
                $forms[] = $config;
            }
        }

        return $forms;
    }

    /**
     * Get the array of form configs with the given IDs
     *
     * @param   array  $ids
     * @return  array
     */
    public function getFormsById(array $ids)
    {
        global $wpdb;
        $forms = array();
        $ids = $this->prepareIds($ids);

        if ($ids == '') {
            return $forms;
        }

        $sql = "SELECT * FROM " . $this->getFormsTableName() . " WHERE id IN ($ids)";

        $rows = $wpdb->get_results($sql, ARRAY_A);

        if (is_array($rows)) {
            foreach ($rows as $row) {
                $config = maybe_unserialize(base64_decode($row['config']));
                $config = $this->addRowDataToConfig($row, $config);
                $forms[] = $config;
            }
        }

        return $forms;
    }

    /**
     * Get the forms list in array format to be easily used by an HTML &lt;select&gt;
     *
     * @param   bool|null  $active  Select all (null), only active (true) or inactive (false) forms
     * @return  array
     */
    public function formsToSelectArray($active = null)
    {
        $rows = $this->all($active);
        $forms = array();

        foreach ($rows as $row) {
            if ($row['active']) {
                $forms[$row['id']] = $row['name'];
            } else {
                $forms[$row['id']] = sprintf(__('%s (inactive)', 'quform'), $row['name']);
            }
        }

        return $forms;
    }

    /**
     * Get the count of forms
     *
     * @param   bool|null  $active   Select all (null), only active (true) or inactive (false) forms
     * @param   bool       $trashed  Select trashed forms
     * @return  int
     */
    public function count($active = null, $trashed = false)
    {
        global $wpdb;

        $sql = "SELECT COUNT(*) FROM " . $this->getFormsTableName();

        $where = array($wpdb->prepare('trashed = %d', $trashed ? 1 : 0));

        if ($active !== null) {
            $where[] = $wpdb->prepare('active = %d', $active ? 1 : 0);
        }

        $sql .= " WHERE " . join(' AND ', $where);

        return (int) $wpdb->get_var($sql);
    }

    /**
     * Does the form exist with the given ID?
     *
     * @param   int   $id
     * @return  bool
     */
    public function exists($id)
    {
        global $wpdb;

        $sql = $wpdb->prepare("SELECT id FROM " . $this->getFormsTableName() . " WHERE id = %d", (int) $id);

        return $wpdb->get_var($sql) !== null;
    }

    /**
     * Does the entry exist with the given ID?
     *
     * @param   int   $id
     * @return  bool
     */
    public function entryExists($id)
    {
        global $wpdb;

        $sql = $wpdb->prepare("SELECT id FROM " . $this->getEntriesTableName() . " WHERE id = %d", (int) $id);

        return $wpdb->get_var($sql) !== null;
    }

    /**
     * Find a form by ID
     *
     * @param   int  $id
     * @return  array|null
     */
    public function find($id)
    {
        global $wpdb;

        $sql = "SELECT * FROM " . $this->getFormsTableName() . " WHERE id = %d";

        return $wpdb->get_row($wpdb->prepare($sql, $id), ARRAY_A);
    }

    /**
     * Get the first non-trashed form row
     *
     * @return array|null
     */
    public function first()
    {
        global $wpdb;

        $sql = "SELECT * FROM " . $this->getFormsTableName() . " WHERE trashed = 0 LIMIT 1";

        return $wpdb->get_row($sql, ARRAY_A);
    }

    /**
     * Get the config array for the form with the given ID
     *
     * @param   int         $id  The form ID
     * @return  array|null       The config array or null if the form doesn't exist
     */
    public function getConfig($id)
    {
        $row = $this->find($id);

        if ($row === null) {
            return null;
        }

        $config = maybe_unserialize(base64_decode($row['config']));

        if (is_array($config)) {
            $config = $this->addRowDataToConfig($row, $config);
        } else {
            $config = null;
        }

        return $config;
    }

    /**
     * Get the config array for the first form in the database
     *
     * @return array|null The config array or null if the form doesn't exist
     */
    public function firstConfig()
    {
        $row = $this->first();

        if ($row === null) {
            return null;
        }

        $config = maybe_unserialize(base64_decode($row['config']));
        $config = $this->addRowDataToConfig($row, $config);

        return $config;
    }

    /**
     * On plugin activation - create the database tables
     */
    public function activate()
    {
        require_once ABSPATH . 'wp-admin/includes/upgrade.php';

        $this->createFormsTable();
        $this->createEntriesTable();
        $this->createEntryDataTable();
        $this->createEntryLabelsTable();
        $this->createEntryEntryLabelsTable();
    }

    /**
     * Create the forms table
     */
    protected function createFormsTable()
    {
        global $wpdb;

        $sql = "CREATE TABLE " . $this->getFormsTableName() . " (
            id int UNSIGNED NOT NULL AUTO_INCREMENT,
            name varchar(64) NOT NULL,
            config longtext NOT NULL,
            active boolean NOT NULL DEFAULT 1,
            trashed boolean NOT NULL DEFAULT 0,
            created_at datetime NOT NULL,
            updated_at datetime NOT NULL,
            PRIMARY KEY  (id),
            KEY active (active),
            KEY trashed (trashed)
        ) " . $wpdb->get_charset_collate() . ";";

        dbDelta($sql);
    }

    /**
     * Create the entries table
     */
    protected function createEntriesTable()
    {
        global $wpdb;

        $sql = "CREATE TABLE " . $this->getEntriesTableName() . " (
            id int UNSIGNED NOT NULL AUTO_INCREMENT,
            form_id int UNSIGNED NOT NULL,
            unread tinyint (1) UNSIGNED NOT NULL DEFAULT 1,
            ip varchar(45) NOT NULL,
            form_url varchar(512) NOT NULL,
            referring_url varchar(512) NOT NULL,
            post_id bigint(20) UNSIGNED,
            created_by bigint(20) UNSIGNED,
            created_at datetime NOT NULL,
            updated_at datetime NOT NULL,
            status varchar(20) NOT NULL DEFAULT 'normal',
            PRIMARY KEY  (id),
            KEY form_id (form_id),
            KEY status (status)
        ) " . $wpdb->get_charset_collate() . ";";

        dbDelta($sql);
    }

    /**
     * Create the entry data table
     */
    protected function createEntryDataTable()
    {
        global $wpdb;

        $sql = "CREATE TABLE " . $this->getEntryDataTableName() . " (
            entry_id int UNSIGNED NOT NULL,
            element_id int UNSIGNED NOT NULL,
            value mediumtext,
            PRIMARY KEY  (entry_id,element_id),
            KEY element_id (element_id)
        ) " . $wpdb->get_charset_collate() . ";";

        dbDelta($sql);
    }

    /**
     * Create the entry data table
     */
    protected function createEntryLabelsTable()
    {
        global $wpdb;

        $sql = "CREATE TABLE " . $this->getEntryLabelsTableName() . " (
            id int UNSIGNED NOT NULL AUTO_INCREMENT,
            form_id int UNSIGNED NOT NULL,
            name varchar(128) NOT NULL,
            color varchar (32) NOT NULL,
            PRIMARY KEY  (id),
            KEY form_id (form_id)
        ) " . $wpdb->get_charset_collate() . ";";

        dbDelta($sql);
    }

    /**
     * Create the entry data table
     */
    protected function createEntryEntryLabelsTable()
    {
        global $wpdb;

        $sql = "CREATE TABLE " . $this->getEntryEntryLabelsTableName() . " (
            entry_id int UNSIGNED NOT NULL,
            entry_label_id int UNSIGNED NOT NULL,
            PRIMARY KEY  (entry_id,entry_label_id),
            KEY entry_label_id (entry_label_id)
        ) " . $wpdb->get_charset_collate() . ";";

        dbDelta($sql);
    }

    /**
     * Get the database version from the wpdb object
     */
    public function getDbVersion()
    {
        global $wpdb;

        return $wpdb->db_version();
    }

    /**
     * Add the form with the given config
     *
     * @param   array       $config  The form config to add
     * @return  array|bool           The new form config with new auto-generated ID or false on failure
     */
    public function add(array $config)
    {
        global $wpdb;

        // Temporarily save the config parts that are part of the table row and unset them
        $name = $config['name'];
        $active = $config['active'];
        $trashed = $config['trashed'];
        unset($config['id'], $config['name'], $config['active'], $config['trashed']);

        $result = $wpdb->insert($this->getFormsTableName(), array(
            'config' => base64_encode(serialize($config)),
            'name' => Quform::substr($name, 0, 64),
            'active' => $active,
            'trashed' => $trashed,
            'created_at' => current_time('mysql', true),
            'updated_at' => current_time('mysql', true)
        ));

        if ($result === false) {
            return false;
        }

        $config['id'] = $wpdb->insert_id;

        // Restore the config parts that are part of the table row
        $config['name'] = $name;
        $config['active'] = $active;
        $config['trashed'] = $trashed;

        $wpdb->insert($this->getEntryLabelsTableName(), array(
            'form_id' => $config['id'],
            'name' => __('Starred', 'quform'),
            'color' => '#F2D600'
        ));

        return $config;
    }

    /**
     * Save the form with the given config and return the config
     *
     * If the 'id' is present in the config array it will update the form
     * otherwise a new one will be created
     *
     * @param   array  $config  The config
     * @return  array           The updated config array
     */
    public function save(array $config)
    {
        global $wpdb;

        // Temporarily save the config parts that are part of the table row and unset them
        $id = $config['id'];
        $name = $config['name'];
        $active = $config['active'];
        $trashed = $config['trashed'];

        unset($config['id'], $config['name'], $config['active'], $config['trashed']);

        $updateValues = array(
            'config' => base64_encode(serialize($config)),
            'name' => Quform::substr($name, 0, 64),
            'active' => $active,
            'trashed' => $trashed,
            'updated_at' => current_time('mysql', true)
        );

        $updateWhere = array(
            'id' => $id
        );

        $wpdb->update($this->getFormsTableName(), $updateValues, $updateWhere);

        // Restore the config parts that are part of the table row
        $config['id'] = $id;
        $config['name'] = $name;
        $config['active'] = $active;
        $config['trashed'] = $trashed;

        return $config;
    }

    /**
     * Save the entry with the given configuration
     *
     * @param   array  $config   The entry config
     * @param   int    $entryId  The entry ID if we are updating a saved entry, 0 for new entries
     * @return  array            The entry config with new ID if we are adding a new entry
     */
    public function saveEntry(array $config, $entryId = 0)
    {
        global $wpdb;

        if (isset($config['data']) && is_array($config['data'])) {
            $data = $config['data'];
            unset($config['data']);
        } else {
            $data = array();
        }

        $entryId = (int) $entryId;

        if ($entryId == 0 || ! $this->entryExists($entryId)) {
            // Entry doesn't exist in the database, create it to get an ID
            $wpdb->insert($this->getEntriesTableName(), $config);
            $entryId = $wpdb->insert_id;
        } else {
            $wpdb->update($this->getEntriesTableName(), $config, array('id' => $entryId));
        }

        if (count($data)) {
            $this->saveEntryData($entryId, $data);
            $config['data'] = $data;
        }

        $config['id'] = $entryId;

        return $config;
    }

    /**
     * Save the given data to the entry with the given ID
     *
     * @param  int    $entryId
     * @param  array  $data
     */
    public function saveEntryData($entryId, array $data)
    {
        if ( ! count($data)) {
            return;
        }

        global $wpdb;

        $sql = "INSERT INTO " . $this->getEntryDataTableName() . " (entry_id, element_id, value) VALUES ";
        $values = array();
        $placeholders = array();

        foreach ($data as $elementId => $value) {
            $placeholders[] = "(%d, %d, %s)";
            array_push($values, $entryId, $elementId, $value);
        }

        $sql .= $wpdb->prepare(implode(', ', $placeholders), $values);

        $sql .= " ON DUPLICATE KEY UPDATE value = VALUES(value);";

        $wpdb->query($sql);
    }

    /**
     * Activate forms with the given IDs
     *
     * @param   array  $ids  The array of form IDs
     * @return  int          The number of affected rows
     */
    public function activateForms(array $ids)
    {
        global $wpdb;

        $ids = $this->prepareIds($ids);

        if ($ids == '') {
            return 0;
        }

        $sql = "UPDATE " . $this->getFormsTableName() . " SET active = 1 WHERE id IN ($ids)";

        $affectedRows = (int) $wpdb->query($sql);

        return $affectedRows;
    }

    /**
     * Deactivate forms with the given IDs
     *
     * @param   array  $ids  The array of form IDs
     * @return  int          The number of affected rows
     */
    public function deactivateForms(array $ids)
    {
        global $wpdb;

        $ids = $this->prepareIds($ids);

        if ($ids == '') {
            return 0;
        }

        $sql = "UPDATE " . $this->getFormsTableName() . " SET active = 0 WHERE id IN ($ids)";

        $affectedRows = (int) $wpdb->query($sql);

        return $affectedRows;
    }

    /**
     * Trash forms with the given IDs
     *
     * @param   array  $ids  The array of form IDs
     * @return  int          The number of affected rows
     */
    public function trashForms(array $ids)
    {
        global $wpdb;

        $ids = $this->prepareIds($ids);

        if ($ids == '') {
            return 0;
        }

        $sql = "UPDATE " . $this->getFormsTableName() . " SET trashed = 1 WHERE id IN ($ids)";

        $affectedRows = (int) $wpdb->query($sql);

        return $affectedRows;
    }

    /**
     * Untrash forms with the given IDs
     *
     * @param   array  $ids  The array of form IDs
     * @return  int          The number of affected rows
     */
    public function untrashForms(array $ids)
    {
        global $wpdb;

        $ids = $this->prepareIds($ids);

        if ($ids == '') {
            return 0;
        }

        $sql = "UPDATE " . $this->getFormsTableName() . " SET trashed = 0 WHERE id IN ($ids)";

        $affectedRows = (int) $wpdb->query($sql);

        return $affectedRows;
    }

    /**
     * Delete the forms with the IDs in the given array
     *
     * @param   array  $ids  The array of form IDs
     * @return  int          The number of deleted rows
     */
    public function deleteForms(array $ids)
    {
        global $wpdb;

        $ids = $this->prepareIds($ids);

        if ($ids == '') {
            return 0;
        }

        // Delete entry label association
        $wpdb->query("DELETE FROM " . $this->getEntryEntryLabelsTableName() . " WHERE entry_id IN (SELECT id FROM " . $this->getEntriesTableName() . " WHERE form_id IN ($ids))");

        // Delete entry labels
        $wpdb->query("DELETE FROM " . $this->getEntryLabelsTableName() . " WHERE form_id IN ($ids)");

        // Delete entry data
        $wpdb->query("DELETE FROM " . $this->getEntryDataTableName() . " WHERE entry_id IN (SELECT id FROM " . $this->getEntriesTableName() . " WHERE form_id IN ($ids))");

        // Delete entries
        $wpdb->query("DELETE FROM " . $this->getEntriesTableName() . " WHERE form_id IN ($ids)");

        // Delete the forms
        $affectedRows = (int) $wpdb->query("DELETE FROM " . $this->getFormsTableName() . " WHERE id IN ($ids)");

        return $affectedRows;
    }

    /**
     * Duplicate the forms with the IDs in the given array
     *
     * @param   array  $ids  The form ID
     * @return  array        The array of new form IDs
     */
    public function duplicateForms(array $ids)
    {
        $ids = $this->sanitizeIds($ids);
        $newIds = array();

        foreach ($ids as $id) {
            $config = $this->getConfig($id);

            if ( ! is_array($config)) {
                continue;
            }

            $config['active'] = true;
            $config['name'] = sprintf(_x('%s duplicate', 'form name duplicate', 'quform'), $config['name']);

            $config = $this->add($config);

            if (is_array($config)) {
                $newIds[] = $config['id'];
            }
        }

        return $newIds;
    }

    /**
     * Prepare an array of IDs for use in an IN clause
     *
     * @param   array   $ids  The array of IDs
     * @return  string        The sanitized string for the IN clause
     */
    protected function prepareIds(array $ids)
    {
        $ids = $this->sanitizeIds($ids);
        $ids = array_map('esc_sql', $ids);
        $ids = join(',', $ids);

        return $ids;
    }

    /**
     * Sanitize the array of IDs ensuring they are all positive integers
     *
     * @param   array   $ids  The array of IDs
     * @return  array         The array of sanitized IDs
     */
    protected function sanitizeIds(array $ids)
    {
        $sanitized = array();

        foreach ($ids as $id) {
            if ( ! is_numeric($id)) {
                continue;
            }

            $id = (int) $id;

            if ($id > 0) {
                $sanitized[] = $id;
            }
        }

        $sanitized = array_unique($sanitized);

        return $sanitized;
    }

    /**
     * Sanitize the array of IDs ensuring they are all positive integers
     *
     * @deprecated 2.4.0
     * @param   array   $ids  The array of IDs
     * @return  array         The array of sanitized IDs
     */
    protected function sanitiseIds(array $ids)
    {
        _deprecated_function(__METHOD__, '2.4.0', 'Quform_Repository::sanitizeIds()');

        return $this->sanitizeIds($ids);
    }

    /**
     * Get the entries for a specific form
     *
     * @param   Quform_Form  $form  The form instance
     * @param   array        $args  The query args
     * @return  array|null
     */
    public function getEntries(Quform_Form $form, array $args = array())
    {
        global $wpdb;

        $args = wp_parse_args($args, array(
            'unread' => null,
            'orderby' => 'created_at',
            'order' => 'DESC',
            'status' => 'normal',
            'offset' => 0,
            'limit' => 20,
            'search' => '',
            'labels' => array(),
            'label_operator' => 'OR'
        ));

        $sql = "SELECT SQL_CALC_FOUND_ROWS `entries`.*";

        $searchColumns = array(
            'entries.ip',
            'entries.form_url',
            'entries.referring_url',
            'entries.post_id',
            'entries.created_by',
            'entries.created_at',
            'entries.updated_at'
        );

        $validOrderBy = array(
            'id',
            'ip',
            'form_url',
            'referring_url',
            'post_id',
            'created_by',
            'created_at',
            'updated_at'
        );

        foreach ($form->getRecursiveIterator() as $element) {
            if ($element->config('saveToDatabase')) {
                $sql .= $wpdb->prepare(", GROUP_CONCAT(DISTINCT IF (data.element_id = %d, data.value, NULL)) AS element_%d", $element->getId(), $element->getId());
                $searchColumns[] = $wpdb->prepare("element_%d", $element->getId());
                $validOrderBy[] = $wpdb->prepare("element_%d", $element->getId());
            }
        }

        $sql .= ", GROUP_CONCAT(DISTINCT eel.entry_label_id) AS labels";

        $whereClause = array($wpdb->prepare("entries.form_id = %d", $form->getId()));

        if ($args['unread'] !== null) {
            $whereClause[] = $wpdb->prepare("entries.unread = %d", $args['unread'] ? 1 : 0);
        }

        if (Quform::isNonEmptyString($args['status'])) {
            $whereClause[] = $wpdb->prepare("entries.status = %s", $args['status']);
        }

        $sql .= " FROM " . $this->getEntriesTableName() . " entries
LEFT JOIN " . $this->getEntryDataTableName() . " data ON data.entry_id = entries.id
LEFT JOIN " . $this->getEntryEntryLabelsTableName() . " eel ON eel.entry_id = entries.id
WHERE " . join(' AND ', $whereClause) . "
GROUP BY entries.id";

        // Search clause
        if (Quform::isNonEmptyString($args['search']) || count($args['labels'])) {
            $sql .= " HAVING ";
            $having = array();

            if (Quform::isNonEmptyString($args['search'])) {
                $args['search'] = $wpdb->esc_like($args['search']);

                $filteredSearchColumns = array();

                foreach ($searchColumns as $searchColumn) {
                    // Bug fix for searching non-Latin characters on a datetime column
                    if (($searchColumn == 'entries.created_at' || $searchColumn == 'entries.updated_at') && preg_match('/[^\d\-: ]/', $args['search'])) {
                        continue;
                    }

                    $filteredSearchColumns[] = $wpdb->prepare("$searchColumn LIKE '%s'", '%' . $args['search'] . '%');
                }

                $having[] = '(' . join(' OR ', $filteredSearchColumns) . ')';
            }

            if (count($args['labels'])) {
                $labels = array();

                foreach ($args['labels'] as $label) {
                    $label = (int) $label;

                    $labels[] = $wpdb->prepare(
                        "(labels LIKE '%s' OR labels LIKE '%s' OR labels LIKE '%s' OR labels LIKE '%s')",
                        $label,
                        '%,' . $label,
                        $label . ',%',
                        '%,' . $label . ',%'
                    );
                }

                $args['label_operator'] = strtoupper($args['label_operator']) == 'AND' ? 'AND' : 'OR';

                $having[] = '(' . join(sprintf(' %s ', $args['label_operator']), $labels) . ')';
            }

            $sql .= join(' AND ', $having);
        }

        // Sanitize order/limit
        $args['orderby'] = in_array($args['orderby'], $validOrderBy) ? $args['orderby'] : 'created_at';
        $args['order'] = strtoupper($args['order']);
        $args['order'] = in_array($args['order'], array('ASC', 'DESC')) ? $args['order'] : 'DESC';
        $args['limit'] = (int) $args['limit'];
        $args['offset'] = (int) $args['offset'];

        // Order/limit clause
        $sql .= " ORDER BY `{$args['orderby']}` {$args['order']} LIMIT {$args['limit']} OFFSET {$args['offset']}";

        // Maximum display length of a single field value
        $wpdb->query('SET @@GROUP_CONCAT_MAX_LEN = 65535');

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

    /**
     * Get the number of found rows from the last query
     *
     * @return int
     */
    public function getFoundRows()
    {
        global $wpdb;

        return (int) $wpdb->get_var("SELECT FOUND_ROWS()");
    }

    /**
     * Get the count of entries for the given form ID
     *
     * @param   int        $formId
     * @param   bool|null  $unread  Get the count of all (null), unread (true) or read (false) entries
     * @param   string     $status  Filter the result by status
     * @return  int
     */
    public function getEntryCount($formId, $unread = null, $status = 'normal')
    {
        global $wpdb;

        $whereClause = array($wpdb->prepare("form_id = %d", (int) $formId));

        if (is_bool($unread)) {
            $whereClause[] = $wpdb->prepare("unread = %d", $unread ? 1 : 0);
        }

        if (Quform::isNonEmptyString($status)) {
            $whereClause[] = $wpdb->prepare("status = %s", $status);
        }

        $count = $wpdb->get_var("SELECT COUNT(*) FROM " . $this->getEntriesTableName() . " WHERE " . join(' AND ', $whereClause));

        return (int) $count;
    }

    /**
     * Get the entry label data from the given label IDs
     *
     * @param   int    $entryId
     * @return  array
     */
    public function getEntryLabels($entryId)
    {
        global $wpdb;

        $sql = $wpdb->prepare("SELECT * FROM " . $this->getEntryLabelsTableName() . " WHERE `id` IN (SELECT entry_label_id FROM " . $this->getEntryEntryLabelsTableName() . " WHERE entry_id = %d)", $entryId);

        $labels = $wpdb->get_results($sql, ARRAY_A);

        if ( ! is_array($labels)) {
            $labels = array();
        }

        return $labels;
    }

    /**
     * Get the entry labels for the given form
     *
     * @param   int    $formId
     * @return  array
     */
    public function getFormEntryLabels($formId)
    {
        global $wpdb;

        $sql = $wpdb->prepare("SELECT * FROM " . $this->getEntryLabelsTableName() . " WHERE `form_id` = %d", $formId);

        $labels = $wpdb->get_results($sql, ARRAY_A);

        if ( ! is_array($labels)) {
            $labels = array();
        }

        return $labels;
    }

    /**
     * Set the entry labels for the given form
     *
     * @param  int    $formId
     * @param  array  $labels
     */
    public function setFormEntryLabels($formId, array $labels)
    {
        global $wpdb;

        $ids = array();
        $values = array();

        foreach ($labels as $label) {
            if (isset($label['id'])) {
                $ids[] = $label['id'];
            }

            $values[] = $wpdb->prepare(
                '(%d, %d, %s, %s)',
                Quform::get($label, 'id'), // If id omitted, trigger auto increment
                $formId,
                $label['name'],
                $label['color']
            );
        }

        $ids = $this->prepareIds($ids);

        if ($ids == '') {
            // No old IDs - delete all labels
            $wpdb->query($sql = $wpdb->prepare(
                "DELETE el, eel FROM `" . $this->getEntryLabelsTableName() . "` el LEFT JOIN `" . $this->getEntryEntryLabelsTableName() . "` eel ON el.id = eel.entry_label_id WHERE el.form_id = %d",
                $formId
            ));
        } else {
            // Some old IDs - delete those not in the list
            $wpdb->query($sql = $wpdb->prepare(
                "DELETE el, eel FROM `" . $this->getEntryLabelsTableName() . "` el LEFT JOIN `" . $this->getEntryEntryLabelsTableName() . "` eel ON el.id = eel.entry_label_id WHERE el.form_id = %d AND el.id NOT IN ($ids)",
                $formId
            ));
        }

        if (count($values)) {
            $sql = "INSERT INTO " . $this->getEntryLabelsTableName() . " (id, form_id, name, color) VALUES ";
            $sql .= join(', ', $values);
            $sql .= " ON DUPLICATE KEY UPDATE form_id = VALUES(form_id), name = VALUES(name), color = VALUES(color)";

            $wpdb->query($sql);
        }
    }

    /**
     * Delete all entry labels for the given form ID
     *
     * @deprecated  2.2.0
     * @param       int    $formId
     */
    public function deleteFormEntryLabels($formId)
    {
        _deprecated_function(__METHOD__, '2.2.0', 'Quform_Repository::setFormEntryLabels($formId, array())');

        $this->setFormEntryLabels($formId, array());
    }

    /**
     * Add the given entry label to the given entry
     *
     * @param  int  $entryId
     * @param  int  $entryLabelId
     */
    public function addEntryEntryLabel($entryId, $entryLabelId)
    {
        global $wpdb;

        $wpdb->query($wpdb->prepare("INSERT IGNORE INTO " . $this->getEntryEntryLabelsTableName() . " (entry_id, entry_label_id) VALUES (%d, %d)", $entryId, $entryLabelId));
    }

    /**
     * Delete the given entry label from the given entry
     *
     * @param  int  $entryId
     * @param  int  $entryLabelId
     */
    public function deleteEntryEntryLabel($entryId, $entryLabelId)
    {
        global $wpdb;

        $wpdb->query($wpdb->prepare("DELETE FROM " . $this->getEntryEntryLabelsTableName() . " WHERE entry_id = %d AND entry_label_id = %d", $entryId, $entryLabelId));
    }

    /**
     * Get the form ID from the entry ID
     *
     * @param   int  $entryId
     * @return  int
     */
    public function getFormIdFromEntryId($entryId)
    {
        global $wpdb;

        $entryId = (int) $entryId;

        $formId = $wpdb->get_var($wpdb->prepare("SELECT form_id FROM " . $this->getEntriesTableName() . " WHERE `id` = %d", $entryId));

        return (int) $formId;
    }

    /**
     * Get the entry with the given ID
     *
     * @param   int          $entryId
     * @param   Quform_Form  $form
     * @return  object
     */
    public function findEntry($entryId, Quform_Form $form)
    {
        global $wpdb;

        $sql = "SELECT `entries`.*";

        $columns = array();

        foreach ($form->getRecursiveIterator() as $element) {
            if ($element->config('saveToDatabase')) {
                $sql .= $wpdb->prepare(", GROUP_CONCAT(IF (`data`.`element_id` = %d, `data`.`value`, NULL)) AS `element_%d`", $element->getId(), $element->getId());
                $columns['element_' . $element->getId()] = $element;
            }
        }

        $sql .= $wpdb->prepare(" FROM `" . $this->getEntriesTableName() . "` `entries`
LEFT JOIN `" . $this->getEntryDataTableName() . "` `data` ON `data`.`entry_id` = `entries`.`id`
WHERE `entries`.`id` = %d
GROUP BY `data`.`entry_id`", $entryId);

        $wpdb->query('SET @@GROUP_CONCAT_MAX_LEN = 65535');

        return $wpdb->get_row($sql, ARRAY_A);
    }

    /**
     * Is there an existing entry with the same value as the given element
     *
     * @param   Quform_Element_Field  $element
     * @return  bool
     */
    public function hasDuplicateEntry(Quform_Element_Field $element)
    {
        global $wpdb;

        $sql = "SELECT `e`.`id` FROM `" . $this->getEntryDataTableName() . "` ed LEFT JOIN `" .
            $this->getEntriesTableName() . "` e ON `ed`.`entry_id` = `e`.`id`
WHERE `e`.`form_id` = %d
AND `ed`.`element_id` = %d
AND `ed`.`value` = '%s';";

        $result = $wpdb->get_row($wpdb->prepare($sql,
            $element->getForm()->getId(),
            $element->getId(),
            $element->getValueForStorage()
        ));

        return $result !== null;
    }

    /**
     * Mark the entries with the IDs in the given array as read
     *
     * @param   array  $ids  The array of entry IDs
     * @return  int          The number of affected rows
     */
    public function readEntries(array $ids)
    {
        global $wpdb;

        $ids = $this->prepareIds($ids);

        if ($ids == '') {
            return 0;
        }

        $sql = "UPDATE " . $this->getEntriesTableName() . " SET unread = 0 WHERE id IN ($ids)";

        $affectedRows = (int) $wpdb->query($sql);

        return $affectedRows;
    }

    /**
     * Mark the entries with the IDs in the given array as unread
     *
     * @param   array  $ids  The array of entry IDs
     * @return  int          The number of affected rows
     */
    public function unreadEntries(array $ids)
    {
        global $wpdb;

        $ids = $this->prepareIds($ids);

        if ($ids == '') {
            return 0;
        }

        $sql = "UPDATE " . $this->getEntriesTableName() . " SET unread = 1 WHERE id IN ($ids)";

        $affectedRows = (int) $wpdb->query($sql);

        return $affectedRows;
    }

    /**
     * Trash the entries with the IDs in the given array
     *
     * @param   array  $ids  The array of entry IDs
     * @return  int          The number of deleted rows
     */
    public function trashEntries(array $ids)
    {
        global $wpdb;

        $ids = $this->prepareIds($ids);

        if ($ids == '') {
            return 0;
        }

        $sql = "UPDATE " . $this->getEntriesTableName() . " SET status = 'trash' WHERE id IN ($ids)";

        $affectedRows = (int) $wpdb->query($sql);

        return $affectedRows;
    }

    /**
     * Untrash the entries with the IDs in the given array
     *
     * @param   array  $ids  The array of entry IDs
     * @return  int          The number of deleted rows
     */
    public function untrashEntries(array $ids)
    {
        global $wpdb;

        $ids = $this->prepareIds($ids);

        if ($ids == '') {
            return 0;
        }

        $sql = "UPDATE " . $this->getEntriesTableName() . " SET status = 'normal' WHERE id IN ($ids)";

        $affectedRows = (int) $wpdb->query($sql);

        return $affectedRows;
    }

    /**
     * Delete the entries with the IDs in the given array
     *
     * @param   array  $ids  The array of entry IDs
     * @return  int          The number of deleted rows
     */
    public function deleteEntries(array $ids)
    {
        global $wpdb;

        $ids = $this->prepareIds($ids);

        if ($ids == '') {
            return 0;
        }

        // Delete entry label association
        $wpdb->query("DELETE FROM " . $this->getEntryEntryLabelsTableName() . " WHERE entry_id IN ($ids)");

        // Delete entry data
        $wpdb->query("DELETE FROM " . $this->getEntryDataTableName() . " WHERE entry_id IN ($ids)");

        // Delete the entries
        $affectedRows = (int) $wpdb->query("DELETE FROM " . $this->getEntriesTableName() . " WHERE id IN ($ids)");

        return $affectedRows;
    }

    /**
     * Get the count of all unread entries
     *
     * @return int
     */
    public function getAllUnreadEntriesCount()
    {
        global $wpdb;

        $sql = "SELECT COUNT(*) FROM " . $this->getEntriesTableName() . " WHERE unread = 1;";

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

    /**
     * @return mixed
     */
    public function getAllFormsWithUnreadEntries()
    {
        global $wpdb;

        $sql = "SELECT f.id, f.name, (SELECT COUNT(*) FROM " . $this->getEntriesTableName() . " WHERE form_id = f.id AND unread = 1) AS entries FROM " . $this->getFormsTableName() . " f HAVING entries > 0;";

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

    /**
     * Get the most recent entries
     *
     * @param   int|null  $count  Limit to this number of entries
     * @return  array
     */
    public function getRecentEntries($count = null)
    {
        global $wpdb;

        $sql = "SELECT f.name, e.* FROM " . $this->getEntriesTableName() . " e LEFT JOIN " . $this->getFormsTableName() . " f ON e.form_id = f.id ORDER BY e.created_at DESC";

        if (is_numeric($count)) {
            $sql .= $wpdb->prepare(" LIMIT %d", $count);
        }

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

    /**
     * @param   array  $row
     * @param   array  $config
     * @return  array
     */
    protected function addRowDataToConfig(array $row, array $config)
    {
        $config['id'] = (int) $row['id'];
        $config['name'] = $row['name'];
        $config['active'] = $row['active'] == 1;
        $config['trashed'] = $row['trashed'] == 1;

        return $config;
    }

    /**
     * Get the entry export
     *
     * @param Quform_Form $form
     * @param string      $from
     * @param string      $to
     *
     * @return array|null
     */
    public function exportEntries(Quform_Form $form, $from = '', $to = '')
    {
        global $wpdb;

        // Build the query
        $sql = "SELECT `entries`.*";

        foreach ($form->getRecursiveIterator() as $element) { // TODO it's leaves_only so not including list element
            if ($element->config('saveToDatabase')) { // TODO, only query element IDs that are given in the columns?
                $sql .= ", GROUP_CONCAT(if (`data`.`element_id` = {$element->getId()}, value, NULL)) AS `element_{$element->getId()}`";
            }
        }

        $sql .= $wpdb->prepare(" FROM `" . $this->getEntriesTableName() . "` `entries`
LEFT JOIN `" . $this->getEntryDataTableName() . "` `data` ON `data`.`entry_id` = `entries`.`id`
WHERE `entries`.`form_id` = %d AND `entries`.`status` = 'normal'", $form->getId());

        $dateParts = array();
        if ($from) {
            $dateParts[] = $wpdb->prepare('`entries`.`created_at` >= %s', get_gmt_from_date($from . ' 00:00:00'));
        }

        if ($to) {
            $dateParts[] = $wpdb->prepare('`entries`.`created_at` <= %s', get_gmt_from_date($to . ' 23:59:59'));
        }

        if (count($dateParts)) {
            $sql .= ' AND (' .  join(' AND ', $dateParts) . ')';
        }

        $sql .= " GROUP BY `entries`.`id`;";

        $wpdb->query('SET @@GROUP_CONCAT_MAX_LEN = 65535');

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

    /**
     * Get the forms ordered by last updated
     *
     * @return array|null
     */
    public function getFormsByUpdatedAt()
    {
        global $wpdb;

        $sql = "SELECT `id`, `name` FROM " . $this->getFormsTableName() . " WHERE `trashed` = 0 AND `active` = 1 ORDER BY `updated_at` DESC";

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

    /**
     * Called when the plugin is uninstalled from the Tools page
     */
    public function uninstall()
    {
        global $wpdb;

        // Remove the forms tables
        $wpdb->query("DROP TABLE IF EXISTS " . $this->getFormsTableName());
        $wpdb->query("DROP TABLE IF EXISTS " . $this->getEntriesTableName());
        $wpdb->query("DROP TABLE IF EXISTS " . $this->getEntryDataTableName());
        $wpdb->query("DROP TABLE IF EXISTS " . $this->getEntryLabelsTableName());
        $wpdb->query("DROP TABLE IF EXISTS " . $this->getEntryEntryLabelsTableName());

        // Remove the user options
        delete_metadata('user', 0, 'quform_recent_forms', '', true);
        delete_metadata('user', 0, 'quform_forms_order_by', '', true);
        delete_metadata('user', 0, 'quform_forms_order', '', true);
        delete_metadata('user', 0, 'quform_forms_per_page', '', true);
        delete_metadata('user', 0, 'quform_entries_order_by', '', true);
        delete_metadata('user', 0, 'quform_entries_order', '', true);
        delete_metadata('user', 0, 'quform_entries_per_page', '', true);
        delete_metadata('user', 0, 'quform_export_entries_format_settings', '', true);
    }
}
