<?php
/**
 * Simple table access
 *
 * $Id$
 *
 * @author gERD Schaufelberger <gerd@php-tools.net>
 * @license PHP License
 * @package WB
 * @subpackage db
 */

WBClass::load( 'WBDatasource_SQLCommon'
            , 'WBDatasource_Callback'
            , 'WBDatasource_TableMicroCache'
            , 'WBEvent'
            , 'WBLog' );

/**
 * Simple table access
 *
 * @version 0.7.0
 * @package WB
 * @subpackage db
 */
class WBDatasource_Table extends WBDatasource_SQLCommon
{
    /**
     * Prefix for triggered events
     * @var string
     */
    protected $eventPrefix  =   'table';

    /**
     * database connection
     * @var WBDatasource_SQL
     */
    protected $db;

    /**
     * microcache
     * @var WBDatasource_TableMicroCache
     */
    private $mc;

   /**
    * database name
    * @var string
    */
    protected $dbName = '';

   /**
    * micro cache
    * @var array
    */
    static protected $cache =   array();

    /**
     * list of ids to preload
     * @var array
     */
    static protected $preload =   array();

   /**
    * number of rows found by last SELECT
    * @var int
    */
    protected $lastFound = 0;

   /**
    * logger
    * @var WBLog
    */
    protected $log;

   /**
    * constructor
    *
    * The parameter will be loaded from config.xml, section "db", you may
    * overwrite the list of tables. This will cause to load the table list
    * from a different file.
    *
    * Parameter:
    * - "tablelist": config file which contains the list of known tables.
    *
    * This class utilizes @link WBDatasource_SQL this implies that you may
    * use all constructor parameter of this class, as well.
    *
    * @param array $parameter
    */
    public function __construct( $parameter = array() )
    {
        parent::__construct( $parameter );

        $this->log      = 	WBLog::start( __CLASS__ );
        $this->db       =   WBClass::create( 'WBDatasource_SQL', $parameter );
        $this->loadTables( $parameter );

        $this->dbName   =   $this->db->getId();
        $this->mc       =   WBClass::create('WBDatasource_TableMicroCache', array('name' => $this->dbName));
    }

    /**
     * Get database name
     *
     * Return database identifier name.
     *
     * @return string
     */
    public function getDbName()
    {
        return $this->dbName;
    }

   /**
    * Save row
    *
    * Save $data to table
    *
    * @param string $table
    * @param string $id or '__new'
    * @param array $data
    * @return string $id of save row
    */
    public function save($table, $id, $data, $clause = array())
    {
        $c  =   $this->getTableInfo($table);

        $query  =   array(
            'UPDATE',
            $c['table'],
            );

        $log    =   array(
                'action'    =>  'save',
                'table'     =>  $table,
                'type'      =>  'update',
                'id'        =>  null
            );

        $now    =   gmdate('Y-m-d H:i:s');

        // INSERT record(s)
        if ($id == '__new') {
            $query[0]       =   'INSERT INTO';

            // force numeric array
            if (!isset($data[0])) {
                $data   =   array($data);
            }

            $keys   =   array_keys($data[0]);
            if (isset($data[0]['email']) && isset($c['email']) && 'split' == $c['email']) {
                $eKey   =   array_search('email', $keys);
                unset($keys[$eKey]);
                $keys   =   array_values($keys);
                $keys[] =   'emaillocal';
                $keys[] =   'emaildomain';
            }

            if (!empty($c['created']) && !isset($data[0][$c['created']])) {
                $keys[] =   $c['created'];
            }
            if (!empty($c['changed']) && !isset($data[0][$c['changed']])) {
                $keys[] =   $c['changed'];
            }
            $query[]    =   '(' . implode(', ', $keys) . ')';

            $values =   array();
            foreach ($data as $d) {

                // split e-mail column?
                if (isset($d['email']) && isset($c['email']) && 'split' == $c['email']) {
                    $email  =   explode('@', $d['email']);
                    $d['emaillocal']    =   $email[0];
                    if (empty($email[1])) {
                        $email[1]   =   '';
                    }
                    $d['emaildomain']   =   $email[1];
                    unset($d['email']);
                }

                $this->trigger('beforesave', $table, '__new', array(), $d);
                if (!empty($c['created'])) {
                    $d[$c['created']]   =   $now;
                }
                if (!empty($c['changed'])) {
                    $d[$c['changed']]   =   $now;
                }

                // make sure all coulumns are set properly
                $v        =   array();
                foreach ($keys as $k) {
                    if (!isset($d[$k])){
                        $d[$k]  =   '';
                    }
                    $v[]    =   $this->db->quote($d[$k]);
                }
                $values[]   =   '(' . implode(', ', $v) . ')';
            }

            $query[]    =   'VALUES';
            $query[]    =   implode(', ', $values);

            // execute query
            $q      =   implode(' ', $query);
            $res    =   $this->db->query($q);
            $dbc    =   $this->db->connect();
            $id     =   $dbc->insert_id;

            $log['type']    =   'insert';
            $log['id']      =   $id;
            $this->log->notice($log);
            if (1 < count($data)) {
                return $id;
            }
            $this->trigger('save', $table, $id, array(), $data[0]);
            return $id;
        }

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


        // UPDATE record(s)
        if (empty($id) && empty($clause)) {
            WBClass::load('WBException_Argument');
            throw new WBException_Argument('Neither id nore clause given.', 6, __CLASS__);
        }

        $this->trigger('beforesave', $table, $id, $clause, $data);

        // id given
        if (!empty($id)) {
            $this->mc->setTable($table)->flush($id);
            $clause[]    =  array(
                            'field' =>  $c['primary'],
                            'value' =>  $id
                            );
        }

        // add changed timestamp
        if (!empty($c['changed'])) {
            $data[$c['changed']]    =   $now;
        }

        // build set
        $set        =   array();
        if (isset($data['email']) && isset($c['email']) && 'split' == $c['email']) {
            list($data['emaillocal'], $data['emaildomain'])    =   explode('@', $data['email']);
            unset($data['email']);
        }

        foreach ($data as $k => $v) {
            $set[]  =   $k . '=' . $this->db->quote($v);
        }
        $query[]    =   'SET';
        $query[]    =   implode(', ', $set);

        // build where
        $where      =   array();
        $this->_clause2Where($table, $clause, $where);
        $query[]    =   'WHERE';
        $query[]    =   implode(' AND ', $where);

        $q          =   implode(' ', $query);
        $res        =   $this->db->query($q);

        // return new id, if id was saved as well!
        if (isset($data[$c['primary']])) {
            $id =   $data[$c['primary']];
        }
        $log['id']  =   $id;
        $this->log->notice($log);
        $this->trigger('save', $table, $id, $clause, $data);
        return $id;
    }

   /**
    * Fetch last found rows
    *
    * @return int
    */
    public function getLastCount()
    {
        return $this->lastFound;
    }

   /**
    * fetch list of ids from table
    *
    * @param string $table
    * @param string|null $foreign
    * @param array $clause
    * @param array $options
    * @return array list of ids
    */
    public function getIds( $table, $foreign = null, $clause = array(), $options = array() )
    {
        $c  =   $this->getTableInfo( $table );
        if( !isset( $c['primary'] ) || empty( $c['primary'] ) ) {
            WBClass::load( 'WBException_Config' );
            throw new WBException_Config( 'The table "' . $table.'" does not have a primary key! Maybe the config is corrupt.', 2, __CLASS__ );
        }

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

        $options['column']   =   array( $c['primary'] );
        return $this->getColumn($table, $foreign, $clause, $options);
    }

   /**
    * fetch single column from table
    *
    * Get list of column values instead of associative array.
    * This method actually fetches ALL columns from the database
    * to fill preload caches.
    *
    * @param string $table
    * @param string|null $foreign
    * @param array $clause
    * @param array $options
    * @return array list of ids
    */
    public function getColumn($table, $foreign = null, $clause = array(), $options = array())
    {
        if( !is_array( $options ) ) {
            $options = array();
        }

        if (!isset($options['column'])) {
            WBClass::load( 'WBException_Argument' );
            throw new WBException_Argument( 'The option "column" is required but not given.', 2, __CLASS__ );
        }
        $col    =   $options['column'];
        unset($options['column']);
        if (is_array($col)) {
            $col    =   $col[0];
        }

        $list   =   array();
        $all    =   $this->get($table, null, $foreign, $clause, $options );
        foreach ($all as $a) {
            $list[] =   $a[$col];
        }
        return $list;
    }

   /**
    * fetch pager
    *
    * Use the same parameter as for @link get(), but skip the parameter "id"!
    *
    * @param string $pagerId
    * @param string $table
    * @param string $foreign
    * @param array $clause
    * @param array $options
    * @return WBDatasource_Pager
    */
    public function getPager( $pagerId, $table, $foreign = null, $clause = array(), $options = array() )
    {
        if( isset( $options['limit'] ) ) {
            $limit  =   $options['limit'];
        } else {
            $c  =   $this->getTableInfo($table);
            if( !isset( $c['limit'] ) ) {
                WBClass::load( 'WBException_Config' );
                throw new WBException_Config( 'The pager only works in case a limit is set. Set limit in config or set via options.', 5, __CLASS__ );
            }
            $limit  =  $c['limit'];
        }

        $parameter  =   array(
                                'id'        =>  $pagerId,
                                'source'    =>  $this,
                                'limit'     =>  $limit,
                                'options'   =>  4
                            );

        $pager  =   WBClass::create( 'WBDatasource_Pager', $parameter );
        $pager->setArgs( $table, null, $foreign, $clause, $options );
        return $pager;
    }

   /**
    * count rows in table
    *
    * @param string $table
    * @param string $id
    * @param string|null $foreign if foreign key
    * @param array $clause additional clause
    * @param array $options parameter
    * @return int $count
    */
    public function count($table, $id = null, $foreign = null, $clause = array(), $options = array())
    {
        $query      =   array( 'SELECT' );
        if (isset($options['distinct']) && !empty($options['distinct'])) {
            $query[]    =   'count(DISTINCT '. $options['distinct'] .')';
        }
        else {
            $query[]    =   'count(*)';
        }
        $c          =   $this->getTableInfo($table);
        $query[]    =   'FROM ' . $c['table'];
        $this->addJoin($table, $options, $query);

        // add id
        if($id) {
            $clause[]   =   array('field' => $c['primary'], 'value' => $id);
        }

        $this->_addDeletedFlag2Clause($table, $clause, $options);
        $this->_addForeignKey2Clause($table, $foreign, $clause);

        $where  =   array();
        $this->_clause2Where($table, $clause, $where);
        if (!empty($where)) {
            $query[]    =   'WHERE ' . implode(' AND ', $where);
        }

        $res    =   $this->db->query(implode(' ', $query));
        $row    =   $res->fetch_row();
        $count  =   $row[0];
        $res->free_result();

        $log    =   array(
            'action'    =>  'count',
            'table'     =>  $table,
            'foreign'   =>  $foreign,
            'count'     =>  $count
        );
        $this->log->debug($log);
        return $count;
    }

   /**
    * fetch rows
    *
    * Options may contain:
    *  - keepfound
    *  - limit
    *  - order
    *  - column
    *  - callback
    *  - flush
    *  - withIdAlias add alias for primary key
    *
    * @param string $table
    * @param string|null $id key
    * @param string|null $foreign if foreign key
    * @param array $clause additional clause
    * @param array $options general options
    * @return array list
    */
    public function get($table, $id = null, $foreign = null, $clause = array(), $options = array())
    {
        $c  =   $this->getTableInfo($table);

        // primary key
        $p  =   null;
        if (isset($c['primary'])) {
            $p  =   $c['primary'];
        }

        // fetch preload entries
        $pIds   =   $this->mc->setTable($table)->getPreload();
        if ($id && !empty($pIds)) {
            $pClause    =   array();
            $pClause[]  =   array(
                            'field'     =>  $p,
                            'value'     =>  $pIds,
                            'relation'  =>  'in'
                            );
            $this->get($table, null, null, $pClause);
        }

        if (!isset($options['flush'])) {
            $options['flush']   =   false;
        }
        if (!isset($options['withIdAlias'])) {
            $options['withIdAlias']  =   true;
        }
        if ($options['withIdAlias'] && empty($p)) {
            $options['withIdAlias']  =   false;
        }

        // verify callback
        if (isset($options['callback'])) {
            if (!($options['callback'] instanceof WBDatasource_Callback)) {
                WBClass::load('WBException_Argument');
                throw new WBException_Argument('The callback object must be an instance of "WBDatasource_Callback"', 3, __CLASS__);
            }
        } else {
            $options['callback']    =   null;
        }

        $cache  =   'miss';
        $cached =   $this->mc->setTable($table)->get($id);
        if ($id && !empty($cached)) {

            // load from cache
            $this->translate($table, $cached);
            $list   =   array($cached);
            $cache  =   'hit';
        } else {

            // fetch from database
            $res    =   $this->_queryGet($table, $id, $foreign, $clause, $options);
            $list   =   array();
            while ($l = $res->fetch_assoc()) {

                // merge e-mail column?
                if (isset($c['email']) && 'split' == $c['email']) {
                    $l['email'] =   $l['emaillocal'] . '@' . $l['emaildomain'];
                    if (empty($l['emaillocal'])) {
                        $l['email'] =   '';
                    }
                }

                // destroy after walkthrough
                if ($options['flush']) {
                    if ($options['withIdAlias']) {
                        $l['id']    =   $l[$p];
                    }

                    if ($options['callback']) {
                        $k  =   null;
                        if ($p) {
                            $k  =   $l[$p];
                        }
                        $options['callback']->onDatasourceGet($table, $k, $l);
                    }

                    continue;
                }

                // cache only complete records
                if (!isset($options['column'])) {

                    // cache by primary key
                    if ($c['primary']) {
                        $this->mc->setTable($table)->add($l[$c['primary']], $l);
                    }

                    // remember foreign ids for preload
                    foreach ($c['foreign'] as $fTable) {

                        // if not cached, add to preload list
                        $f  =   $this->getTableInfo($fTable);
                        $this->mc->setTable($fTable)->addPreload($l[$f['primary']]);
                    }
                }

                $this->translate($table, $l);

                $list[] =   $l;
            }
            $res->free_result();
        }

        // second run through list
        foreach ($list as $i => &$l) {
            if ($options['withIdAlias']) {
                $l['id']    =   $l[$p];
            }

            if ($options['callback']) {
                $k  =   null;
                if ($p) {
                    $k  =   $l[$p];
                }
                $options['callback']->onDatasourceGet($table, $k, $l);
            }

            // flush result
            if ($options['flush']) {
                unset($list[$i]);
            }
        }

        $log    =   array(
                'action'    =>  'get',
                'table'     =>  $table,
                'id'        =>  strval($id),
                'foreign'   =>  $foreign,
                'count'     =>  count($list),
                'cache'     =>  $cache
            );
        $this->log->debug( $log );
        return $list;
    }

   /**
    * Internal interface to execute query
    *
    * @param string $table
    * @param string|null $id key
    * @param string|null $foreign if foreign key
    * @param array $clause additional clause
    * @param array $options general options
    * @return array list
    */
    protected function _queryGet( $table, $id = null, $foreign = null, $clause = array(), $options = array() )
    {
        $query  =   array( 'SELECT' );

        $this->lastFound   =   null;
        if( isset( $options['keepfound'] ) && $options['keepfound'] ) {
            $query[]    =   'SQL_CALC_FOUND_ROWS';
        }

        $c  =   $this->getTableInfo( $table );

        // only select a few comlums
        $columns    =   array( '*' );
        if( isset( $options['column'] ) ) {
            $columns    =   $options['column'];
            if( !is_array( $columns ) ) {
                $columns    =   array( $columns );
            }

            if( isset( $c['primary'] ) && !empty( $c['primary'] ) && !in_array( $c['primary'], $columns ) ) {
                $columns[]  =   $c['primary'];
            }
        }

        foreach ($columns as &$col) {
            if (!is_array($col)) {
                $col    =   $c['table'] . '.' . $col;
                continue;
            }

            $cc     =   $c;
            if (isset($col['table']) && $col['table'] != $table) {
                $cc =   $this->getTableInfo($col['table']);
            }
            $tmp    =   $cc['table'] . '.' . $col['field'];
            if (isset($col['function'])) {
                $tmp    =   sprintf('%s(%s)', $col['function'], $tmp);
            }
            if (isset($col['as'])) {
                $tmp    .=  ' AS ' . $this->db->quote($col['as']);
            }
            $col    =   $tmp;
        }

        $query[]    =   implode( ', ', $columns );
        $query[]    =   'FROM ' . $c['table'];

        $this->addJoin($table, $options, $query);

        // prepare clause
        if( !is_array( $clause ) ) {
            $clause =   array();
        }
        // add id
        if( $id ) {
            $clause[]   =   array( 'field' => $c['primary'], 'value' => $id );
        }

        $this->_addDeletedFlag2Clause($table, $clause, $options);
        $this->_addForeignKey2Clause( $table, $foreign, $clause );

        $where  =   array();
        $this->_clause2Where( $table, $clause, $where );
        if( !empty( $where ) ) {
            $query[]    =   'WHERE ' . implode( ' AND ', $where );
        }

        $this->addGroupBy( $table, $options, $query );
        $this->_addOrderBy( $table, $options, $query );
        $this->_addLimit( $table, $options, $query );

        $q  =   implode( ' ', $query );
        $res =  $this->db->query( $q );

        if( isset( $options['keepfound'] ) && $options['keepfound'] ) {
            $foundRes           =   $this->db->query( 'SELECT FOUND_ROWS()' );
            $lastFound          =   $foundRes->fetch_array( MYSQLI_NUM );
            $this->lastFound    =   $lastFound[0];
            $foundRes->free_result();
        }
        return $res;

    }

   /**
    * Delete entry from table
    *
    * SQL delete
    *
    * @param string $table name of table
    * @param string|null $id
    * @param string|id $foreign id to verify delete
    * @param array $clause
    * @return int deleted rows
    */
    public function delete($table, $id, $foreign = null, $clause = array())
    {
        $c		=   $this->getTableInfo($table);

        // prepare clause
        if (!is_array($clause)) {
            $clause =   array();
        }
        // add id
        if ($id) {
        	// clear cache
            $this->mc->setTable($table)->flush($id);

            // add id to clause
            $clause[]	=	array(
                                'field' => $c['primary'],
                                'value' => $id
                            );
        }
        $this->_addForeignKey2Clause($table, $foreign, $clause);

        $q      =   array();
        $q[]    =   'DELETE FROM';
        $q[]    =   $c['table'];
        if (isset($c['delete']) && !empty($c['delete'])) {
            $q[0]  =   'UPDATE';
            $q[]   =   'SET';
            $q[]   =   $c['delete'] . '=1';
            if (!empty($c['changed'])) {
                $q[]   =   ', ' . $c['changed'] . '="' . gmdate('Y-m-d H:i:s') . '"';
            }
        }

        $where	=	array();
        $this->_clause2Where($table, $clause, $where);
        $q[]    =   'WHERE';
        $q[]    =   implode(' AND ', $where);

        $res    =  $this->db->query(implode(' ', $q));

        $eData  =   array(
                        'table'     =>  $table,
                        'id'        =>  $id,
                        'foreign'   =>  $foreign,
                        'clause'    =>  $clause
                );
        WBEvent::trigger('table:delete:' . $table, 'Deleted record from table', $eData);
        return 1;
    }

   /**
    * Undelete entry from table
    *
    * Remove deleted-flag from record. This funtionality has a view requirements:
    * - "id" column is neccessary
    * - deleted-flag, so rows are marked as deleted, not just removed
    *
    * @throws WBException_Argument
    * @throws WBException_Config
    * @param string $table name of table
    * @param string|null $id
    * @return
    */
    public function undelete($table, $id)
    {
        if (empty($id)) {
            $ex =   array(
                        'msg'   =>  'ID required for undelete',
                        'code'  =>  7,
                        'class' =>  __CLASS__
                    );
            throw WBClass::create('WBException_Argument', $ex);
        }

        $c      =   $this->getTableInfo($table);
        if (!isset($c['primary']) || empty($c['primary'])) {
            $ex =   array(
                        'msg'   =>  'Primary key required for undelete',
                        'code'  =>  8,
                        'class' =>  __CLASS__
                    );
            throw WBClass::create('WBException_Config', $ex);
        }
        if (!isset($c['delete']) || empty($c['delete'])) {
            $ex =   array(
                        'msg'   =>  'Delete-flag required for undelete',
                        'code'  =>  9,
                        'class' =>  __CLASS__
                    );
            throw WBClass::create('WBException_Config', $ex);
        }

        $this->mc->setTable($table)->flush($id);

        // add id to clause
        $clause[]   =   array(
                            'field' => $c['primary'],
                            'value' => $id
                        );

        $q      =   array();
        $q[]  =   'UPDATE';
        $q[]   =   $c['table'];
        $q[]   =   'SET';
        $q[]   =   $c['delete'] . '=0';

        $where  =   array();
        $this->_clause2Where($table, $clause, $where);
        $q[]    =   'WHERE';
        $q[]    =   implode(' AND ', $where);

        $res    =  $this->db->query(implode(' ', $q));

        $eData  =   array(
                        'table'     =>  $table,
                        'id'        =>  $id,
                );
        WBEvent::trigger('table:undelete:' . $table, 'Undeleted record of table', $eData);
        return true;

    }

    /**
     * Wipe out cached data
     *
     * Remove all cached entries and start from scratch
     */
    static public function flushCache()
    {
        $mc =   WBClass::create('WBDatasource_TableMicroCache');
        $mc->flushAll();
    }
}