<?php

/**
 * Ai1ec_Database class
 *
 * Class responsible for generic database operations
 *
 * @author     Time.ly Network Inc.
 * @since      2.0
 *
 * @package    AI1EC
 * @subpackage AI1EC.Database
 */
class Ai1ec_Database_Helper {

    /**
     * @var array Map of tables and their parsed definitions
     */
    protected $_schema_delta = array();

    /**
     * @var array List of valid table prefixes
     */
    protected $_prefixes     = array();

    /**
     * @var wpdb Localized instance of wpdb object
     */
    protected $_db = NULL;

    /**
     * @var bool If set to true - no operations will be performed
     */
    protected $_dry_run  = false;

    /**
     * Constructor
     *
     * @param Ai1ec_Registry_Object $registry
     *
     * @return void
     */
    public function __construct( Ai1ec_Registry_Object $registry ) {
        $this->_db       = $registry->get( 'dbi.dbi' );
        $this->_prefixes = array(
            $this->_db->get_table_name( 'ai1ec_' ),
            $this->_db->get_table_name(),
            '',
        );
    }

    /**
     * Check if dry run is enabled
     *
     * @param bool $dry Change dryness [optional=NULL]
     *
     * @return bool Dryness of run or previous value
     */
    public function is_dry( $dry = NULL ) {
        if ( NULL !== $dry ) {
            $previous = $this->_dry_run;
            $this->_dry_run = (bool)$dry;
            return $previous;
        }
        return $this->_dry_run;
    }

    /**
     * Get fully-qualified table name given it's abbreviated form
     *
     * @param string $name         Name (abbreviation) of table to check
     * @param bool   $ignore_check Return longest name if no table exist [false]
     *
     * @return string Fully-qualified table name
     *
     * @throws Ai1ec_Database_Schema_Exception If no table matches
     */
    public function table( $name, $ignore_check = false ) {
        $existing  = $this->get_all_tables();
        $table     = NULL;
        $candidate = NULL;
        foreach ( $this->_prefixes as $prefix ) {
            $candidate = $prefix . $name;
            $index     = strtolower( $candidate );
            if ( isset( $existing[$index] ) ) {
                $table = $existing[$index];
                break;
            }
        }
        if ( NULL === $table ) {
            if ( true === $ignore_check ) {
                return $candidate;
            }
            throw new Ai1ec_Database_Schema_Exception(
                'Table \'' . $name . '\' does not exist'
            );
        }
        return $table;
    }

    /**
     * Drop given indices from table
     *
     * @param string       $table   Name of table to modify
     * @param string|array $indices List, or single, of indices to remove
     *
     * @return bool Success
     *
     * @throws Ai1ec_Database_Schema_Exception If table is not found
     */
    public function drop_indices( $table, $indices ) {
        if ( ! is_array( $indices ) ) {
            $indices = array( (string)$indices );
        }
        $table    = $this->table( $table );
        $existing = $this->get_indices( $table );
        $removed  = 0;
        foreach ( $indices as $index ) {
            if (
                ! isset( $existing[$index] ) ||
                $this->_dry_query(
                    'ALTER TABLE ' . $table . ' DROP INDEX ' . $index
                )
            ) {
                ++$removed;
            }
        }
        return ( count( $indices ) === $removed );
    }

    /**
     * Create indices for given table
     *
     * Input ({@see $indices}) must be the same, as output of
     * method {@see self::get_indices()}.
     *
     * @param string $table   Name of table to create indices for
     * @param array  $indices Indices representation to be created
     *
     * @return bool Success
     *
     * @throws Ai1ec_Database_Schema_Exception If table is not found
     */
    public function create_indices( $table, array $indices ) {
        $table = $this->table( $table );
        foreach ( $indices as $name => $definition ) {
            $query = 'ALTER TABLE ' . $table . ' ADD ';
            if ( $definition['unique'] ) {
                $query .= 'UNIQUE ';
            }
            $query .= 'KEY ' . $name . ' (' .
                implode( ', ', $definition['columns'] ) .
                ')';
            if ( ! $this->_dry_query( $query ) ) {
                return false;
            }
        }
        return true;
    }

    /**
     * get_indices method
     *
     * Get map of indices defined for table.
     *
     * @NOTICE: no optimization will be performed here, and response will not
     * be cached, to allow checking result of DDL statements.
     *
     * Returned array structure (example):
     * array(
     *     'index_name' => array(
     *         'name'    => 'index_name',
     *         'columns' => array(
     *             'column1',
     *             'column2',
     *             'column3',
     *         ),
     *         'unique'  => true,
     *     ),
     * )
     *
     * @param string $table Name of table to retrieve index names for
     *
     * @return array Map of index names and their representation
     *
     * @throws Ai1ec_Database_Schema_Exception If table is not found
     */
    public function get_indices( $table ) {
        $sql_query = 'SHOW INDEXES FROM ' . $this->table( $table );
        $result    = $this->_db->get_results( $sql_query );
        $indices   = array();
        foreach ( $result as $index ) {
            $name = $index->Key_name;
            if ( ! isset( $indices[$name] ) ) {
                $indices[$name] = array(
                    'name'    => $name,
                    'columns' => array(),
                    'unique'  => ! (bool)intval( $index->Non_unique ),
                );
            }
            $indices[$name]['columns'][$index->Column_name] = $index->Sub_part;
        }
        return $indices;
    }

    /**
     * Perform query, unless `dry_run` is selected. In later case just output
     * the final query and return true.
     *
     * @param string $query SQL Query to execute
     *
     * @return mixed Query state, or true in dry run mode
     */
    public function _dry_query( $query ) {
        if ( $this->is_dry() ) {
            pr( $query );
            return true;
        }
        $result = $this->_db->query( $query );
        if ( AI1EC_DEBUG ) {
            echo '<h4>', $query, '</h4><pre>', var_export( $result, true ), '</pre>';
        }
        return $result;
    }

    /**
     * Check if given table exists
     *
     * @param string $table Name of table to check
     *
     * @return bool Existance
     */
    public function table_exists( $table ) {
        $map = $this->get_all_tables();
        return isset( $map[strtolower( $table )] );
    }

    /**
     * Return a list of all tables currently present
     *
     * @return array Map of tables present
     */
    public function get_all_tables() {
        /**
         * @TODO: refactor using dbi.dbi::get_tables
         */
        $sql_query = 'SHOW TABLES LIKE \'' .
            $this->_db->get_table_name() .
            '%\'';
        $result    = $this->_db->get_col( $sql_query );
        $tables    = array();
        foreach ( $result as $table ) {
            $tables[strtolower( $table )] = $table;
        }
        return $tables;
    }

    /**
     * apply_delta method
     *
     * Attempt to parse and apply given database tables definition, as a delta.
     * Some validation is made prior to calling DB, and fields/indexes are also
     * checked for consistency after sending queries to DB.
     *
     * NOTICE: only "CREATE TABLE" statements are handled. Others will, likely,
     * be ignored, if passed through this method.
     *
     * @param string|array $query Single or multiple queries to perform on DB
     *
     * @return bool Success
     *
     * @throws Ai1ec_Database_Error In case of any error
     */
    public function apply_delta( $query ) {
        if ( ! function_exists( 'dbDelta' ) ) {
            require_once ABSPATH . 'wp-admin' . DIRECTORY_SEPARATOR .
                'includes' . DIRECTORY_SEPARATOR . 'upgrade.php';
        }
        $success = false;
        $this->_schema_delta = array();
        $queries = $this->_prepare_delta( $query );
        $result  = dbDelta( $queries );
        $success = $this->_check_delta();
        return $success;
    }

    /**
     * get_notices_helper method
     *
     * DIP implementing method, to give access to Ai1ec_Deferred_Rendering_Helper.
     *
     * @param Ai1ec_Deferred_Rendering_Helper $replacement Notices implementor
     *
     * @return Ai1ec_Deferred_Rendering_Helper Instance of notices implementor
     */
    public function get_notices_helper(
        Ai1ec_Deferred_Rendering_Helper $replacement = NULL
    ) {
        static $helper = NULL;
        if ( NULL !== $replacement ) {
            $helper = $replacement;
        }
        if ( NULL === $helper ) {
            $helper = Ai1ec_Deferred_Rendering_Helper::get_instance();
        }
        return $helper;
    }

    /**
     * _prepare_delta method
     *
     * Prepare statements for execution.
     * Attempt to parse various SQL definitions and compose the one, that is
     * most likely to be accepted by delta engine.
     *
     * @param string|array $queries Single or multiple queries to perform on DB
     *
     * @return bool Success
     *
     * @throws Ai1ec_Database_Error In case of any error
     */
    protected function _prepare_delta( $queries ) {
        if ( ! is_array( $queries ) ) {
            $queries = explode( ';', $queries );
            $queries = array_filter( $queries );
        }
        $current_table = NULL;
        $ctable_regexp = '#
            \s*CREATE\s+TABLE\s+(?:IF\s+NOT\s+EXISTS\s+)?`?([^ ]+)`?\s*
            \((.+)\)
            ([^()]*)
            #six';
        foreach ( $queries as $query ) {
            if ( preg_match( $ctable_regexp, $query, $matches ) ) {
                $this->_schema_delta[$matches[1]] = array(
                    'tblname' => $matches[1],
                    'cryptic'  => NULL,
                    'creator'  => '',
                    'columns' => array(),
                    'indexes' => array(),
                    'content' => preg_replace( '#`#', '', $matches[2] ),
                    'clauses' => $matches[3],
                );
            }
        }
        $this->_parse_delta();
        $sane_queries = array();
        foreach ( $this->_schema_delta as $table => $definition ) {
            $create = 'CREATE TABLE ' . $table . " (\n";
            foreach ( $definition['columns'] as $column ) {
                $create .= '    ' . $column['create'] . ",\n";
            }
            foreach ( $definition['indexes'] as $index ) {
                $create .= '    ' . $index['create'] . ",\n";
            }
            $create = substr( $create, 0, -2 ) . "\n";
            $create .= ')' . $definition['clauses'];
            $this->_schema_delta[$table]['creator'] = $create;
            $this->_schema_delta[$table]['cryptic'] = md5( $create );
            $sane_queries[] = $create;
        }
        return $sane_queries;
    }

    /**
     * _parse_delta method
     *
     * Parse table application (creation) statements into atomical particles.
     * Here "atomical particles" stands for either columns, or indexes.
     *
     * @return void Method does not return
     *
     * @throws Ai1ec_Database_Error In case of any error
     */
    protected function _parse_delta() {
        foreach ( $this->_schema_delta as $table => $definitions ) {
            $listing = explode( "\n", $definitions['content'] );
            $listing = array_filter( $listing, array( $this, '_is_not_empty_line' ) );
            $lines   = count( $listing );
            $lineno  = 0;
            foreach ( $listing as $line ) {
                ++$lineno;
                $line = trim( preg_replace( '#\s+#', ' ', $line ) );
                $line_new = rtrim( $line, ',' );
                if (
                    $lineno < $lines && $line === $line_new ||
                    $lineno == $lines && $line !== $line_new
                ) {
                    throw new Ai1ec_Database_Error(
                        'Missing comma in line \'' . $line . '\''
                    );
                }
                $line = $line_new;
                unset( $line_new );
                $type = 'indexes';
                if ( false === ( $record = $this->_parse_index( $line ) ) ) {
                    $type   = 'columns';
                    $record = $this->_parse_column( $line );
                }
                if ( isset(
                        $this->_schema_delta[$table][$type][$record['name']]
                ) ) {
                    throw new Ai1ec_Database_Error(
                        'For table `' . $table . '` entry ' . $type .
                        ' named `' . $record['name'] . '` was declared twice' .
                        ' in ' . $definitions
                    );
                }
                $this->_schema_delta[$table][$type][$record['name']] = $record;
            }
        }
    }

    /**
     * _parse_index method
     *
     * Given string attempts to detect, if it is an index, and if yes - parse
     * it to more navigable index definition for future validations.
     * Creates modified index create line, for delta application.
     *
     * @param string $description Single "line" of CREATE TABLE statement body
     *
     * @return array|bool Index definition, or false if input does not look like index
     *
     * @throws Ai1ec_Database_Error In case of any error
     */
    protected function _parse_index( $description ) {
        $description = preg_replace(
            '#^CONSTRAINT(\s+`?[^ ]+`?)?\s+#six',
            '',
            $description
        );
        $details     = explode( ' ', $description );
        $index       = array(
            'name'    => NULL,
            'content' => array(),
            'create'  => '',
        );
        $details[0]  = strtoupper( $details[0] );
        switch ( $details[0] ) {
            case 'PRIMARY':
                $index['name']   = 'PRIMARY';
                $index['create'] = 'PRIMARY KEY ';
                break;

            case 'UNIQUE':
                $name = $details[1];
                if (
                    0 === strcasecmp( 'KEY',   $name ) ||
                    0 === strcasecmp( 'INDEX', $name )
                ) {
                    $name = $details[2];
                }
                $index['name']   = $name;
                $index['create'] = 'UNIQUE KEY ' . $name;
                break;

            case 'KEY':
            case 'INDEX':
                $index['name']   = $details[1];
                $index['create'] = 'KEY ' . $index['name'];
                break;

            default:
                return false;
        }
        $index['content'] = $this->_parse_index_content( $description );
        $index['create'] .= ' (';
        foreach ( $index['content'] as $column => $length ) {
            $index['create'] .= $column;
            if ( NULL !== $length ) {
                $index['create'] .= '(' . $length . ')';
            }
            $index['create'] .= ',';
        }
        $index['create'] = substr( $index['create'], 0, -1 );
        $index['create'] .= ')';
        return $index;
    }

    /**
     * _parse_column method
     *
     * Parse column to parseable definition.
     * Some valid definitions may still be not recognizes (namely SET and ENUM)
     * thus one shall beware, when attempting to create such.
     * Create alternative create table entry line for delta application.
     *
     * @param string $description Single "line" of CREATE TABLE statement body
     *
     * @return array Column definition
     *
     * @throws Ai1ec_Database_Error In case of any error
     */
    protected function _parse_column( $description ) {
        $column_regexp = '#^
            ([a-z][a-z_]+)\s+
            (
                [A-Z]+
                (?:\s*\(\s*\d+(?:\s*,\s*\d+\s*)?\s*\))?
                (?:\s+UNSIGNED)?
                (?:\s+ZEROFILL)?
                (?:\s+BINARY)?
                (?:
                    \s+CHARACTER\s+SET\s+[a-z][a-z_]+
                    (?:\s+COLLATE\s+[a-z][a-z0-9_]+)?
                )?
            )
            (
                \s+(?:NOT\s+)?NULL
            )?
            (
                \s+DEFAULT\s+[^\s]+
            )?
            (\s+ON\s+UPDATE\s+CURRENT_(?:TIMESTAMP|DATE))?
            (\s+AUTO_INCREMENT)?
            \s*,?\s*
        $#six';
        if ( ! preg_match( $column_regexp, $description, $matches ) ) {
            throw new Ai1ec_Database_Error(
                'Invalid column description ' . $description
            );
        }
        $column = array(
            'name'    => $matches[1],
            'content' => array(),
            'create'  => '',
        );
        if ( 0 === strcasecmp( 'boolean', $matches[2] ) ) {
            $matches[2] = 'tinyint(1)';
        }
        $column['content']['type'] = $matches[2];
        $column['content']['null'] = (
            ! isset( $matches[3] ) ||
            0 !== strcasecmp( 'NOT NULL', trim( $matches[3] ) )
        );
        $column['create'] = $column['name'] . ' ' . $column['content']['type'];
        if ( isset( $matches[3] ) ) {
            $column['create'] .= ' ' .
                implode(
                    ' ',
                    array_map(
                        'trim',
                        array_slice( $matches, 3 )
                    )
                );
        }
        return $column;
    }

    /**
     * _parse_index_content method
     *
     * Parse index content, to a map of columns and their length.
     * All index (content) cases shall be covered, although it is only tested.
     *
     * @param string Single line of CREATE TABLE statement, containing index definition
     *
     * @return array Map of columns and their length, as per index definition
     *
     * @throws Ai1ec_Database_Error In case of any error
     */
    protected function _parse_index_content( $description ) {
        if ( ! preg_match( '#^[^(]+\((.+)\)$#', $description, $matches ) ) {
            throw new Ai1ec_Database_Error(
                'Invalid index description ' . $description
            );
        }
        $columns = array();
        $textual = explode( ',', $matches[1] );
        $column_regexp = '#\s*([^(]+)(?:\s*\(\s*(\d+)\s*\))?\s*#sx';
        foreach ( $textual as $column ) {
            if (
                ! preg_match( $column_regexp, $column, $matches ) || (
                      isset( $matches[2] ) &&
                      (string)$matches[2] !== (string)intval( $matches[2] )
                )
            ) {
                throw new Ai1ec_Database_Error(
                    'Invalid index (columns) description ' . $description .
                    ' as per \'' . $column . '\''
                );
            }
            $matches[1] = trim( $matches[1] );
            $columns[$matches[1]] = NULL;
            if ( isset( $matches[2] ) ) {
                $columns[$matches[1]] = (int)$matches[2];
            }
        }
        return $columns;
    }

    /**
     * _check_delta method
     *
     * Given parsed schema definitions (in {@see self::$_schema_delta} map) this
     * method performs checks, to ensure that table exists, columns are of
     * expected type, and indexes match their definition in original query.
     *
     * @return bool Success
     *
     * @throws Ai1ec_Database_Error In case of any error
     */
    protected function _check_delta() {
        if ( empty( $this->_schema_delta ) ) {
            return true;
        }
        foreach ( $this->_schema_delta as $table => $description ) {

            $columns = $this->_db->get_results( 'SHOW FULL COLUMNS FROM ' . $table );
            if ( empty( $columns ) ) {
                throw new Ai1ec_Database_Error(
                    'Required table `' . $table . '` was not created'
                );
            }
            $db_column_names = array();
            foreach ( $columns as $column ) {
                if ( ! isset( $description['columns'][$column->Field] ) ) {
                    if ( $this->_db->query(
                        'ALTER TABLE `' . $table .
                        '` DROP COLUMN `' . $column->Field . '`'
                     ) ) {
                        continue;
                    }
                    continue; // ignore so far
                    //throw new Ai1ec_Database_Error(
                    //    'Unknown column `' . $column->Field .
                    //    '` is present in table `' . $table . '`'
                    //);
                }
                $db_column_names[$column->Field] = $column->Field;
                $type_db = $column->Type;
                $collation = '';
                if ( $column->Collation ) {
                    $collation = ' CHARACTER SET ' .
                        substr(
                            $column->Collation,
                            0,
                            strpos( $column->Collation, '_' )
                        ) . ' COLLATE ' . $column->Collation;
                }
                $type_req = $description['columns'][$column->Field]
                    ['content']['type'];
                if (
                    false !== stripos(
                        $type_req,
                        ' COLLATE '
                    )
                ) {
                    // suspend collation checking
                    //$type_db .= $collation;
                    $type_req = preg_replace(
                        '#^
                            (.+)
                            \s+CHARACTER\s+SET\s+[a-z0-9_]+
                            \s+COLLATE\s+[a-z0-9_]+
                            (.+)?\s*
                        $#six',
                        '$1$2',
                        $type_req
                    );
                }
                $type_db  = strtolower(
                    preg_replace( '#\s+#', '', $type_db )
                );
                $type_req = strtolower(
                    preg_replace( '#\s+#', '', $type_req )
                );
                if ( 0 !== strcmp( $type_db, $type_req ) ) {
                    throw new Ai1ec_Database_Error(
                        'Field `' . $table . '`.`' . $column->Field .
                        '` is of incompatible type'
                    );
                }
                if (
                    'YES' === $column->Null &&
                    false === $description['columns'][$column->Field]
                        ['content']['null'] ||
                    'NO' === $column->Null &&
                    true === $description['columns'][$column->Field]
                        ['content']['null']
                ) {
                    throw new Ai1ec_Database_Error(
                        'Field `' . $table . '`.`' . $column->Field .
                        '` NULLability is flipped'
                    );
                }
            }
            if (
                $missing = array_diff(
                    array_keys( $description['columns'] ),
                    $db_column_names
                )
            ) {
                    throw new Ai1ec_Database_Error(
                        'In table `' . $table . '` fields are missing: ' .
                        implode( ', ', $missing )
                    );
            }

            $indexes = $this->get_indices( $table );

            foreach ( $indexes as $name => $definition ) {
                if ( ! isset( $description['indexes'][$name] ) ) {
                    continue; // ignore so far
                    //throw new Ai1ec_Database_Error(
                    //    'Unknown index `' . $name .
                    //    '` is defined for table `' . $table . '`'
                    //);
                }
                if (
                    $missed = array_diff_assoc(
                        $description['indexes'][$name]['content'],
                        $definition['columns']
                    )
                ) {
                    throw new Ai1ec_Database_Error(
                        'Index `' . $name .
                        '` definition for table `' . $table . '` has invalid ' .
                        ' fields: ' . implode( ', ', array_keys( $missed ) )
                    );
                }
            }

            if (
                $missing = array_diff(
                    array_keys( $description['indexes'] ),
                    array_keys( $indexes )
                )
            ) {
                    throw new Ai1ec_Database_Error(
                        'In table `' . $table . '` indexes are missing: ' .
                        implode( ', ', $missing )
                    );
            }

        }
        return true;
    }

    /**
     * _is_not_empty_line method
     *
     * Helper method, to check that any given line is not empty.
     * Aids array_filter in detecting empty SQL query lines.
     *
     * @param string $line Single line of DB query statement
     *
     * @return bool True if line is not empty, false otherwise
     */
    protected function _is_not_empty_line( $line ) {
        $line = trim( $line );
        return ! empty( $line );
    }

}