<?php

// some standard query chunks to avoid copy/paste errors
define('DBQ_CONDITION_PUBLIC_LIST', "list_featured != 'deleted' and list_featured != 'only you' and list_type != 'video' and list_id != 3171 ");


// -------------------------------------------------------------------------------------------------
// a class to wrap up low-level SQL operations on the listopia DB. this class knows nothing about
// the DB structure, it's just here to neatly hide any SQL guts from the template code
//
// the original sql wrapper by Ciprian Mocanu <http://www.mbe.ro> <ciprian@mbe.ro> (version 1.56)
//
class ListopiaDB
{
  var $con;
  
  // -----------------------------------------------------------------------------------------------
  function __construct() 
  {
    // we determine the auth credentials based on a guess as to where the code is executing
    // use the server name to make an estimate
    $serverCheck = strtolower( $_SERVER['SERVER_NAME'] );
    
    // the real deal. Log needs to fill these in at some point
    if (strpos($serverCheck, "listopia") !== false || strpos($serverCheck, "jonblyth") !== false )
    {
      // $db = array(
      //   'host' => 'localhost',
      //   'user' => 'jonblyth_listopia',   // formerly jonblyth_listopia
      //   'pass' => 'i]D98NCjRjx#',    // formerly i]D98NCjRjx#
      //   'db'   => 'jonblyth_listopia'
      // );
      $db = array(
        'host' => 'localhost',
        'user' => 'jonblyth_newlist',   // formerly jonblyth_listopia
        'pass' => 'hot69time!',    // formerly i]D98NCjRjx#
        'db'   => 'jonblyth_newlist'
      );

    }
    // localhost means we're running on Abyss or whatever. use KJP local login
    else if (strpos($serverCheck, "127.0") !== false ) 
    {
      $db = array(
        'host' => 'localhost',
        'user' => 'root',
        'pass' => '',
        'db'   => 'listopia'
      );
    }
    // localhost means we're running on Abyss or whatever. use KJP local login
    else if (strpos($serverCheck, "localhost") !== false ) 
    {
      $db = array(
        'host' => 'localhost',
        'user' => 'kodiak',
        'pass' => 'kodiak',
        'db'   => 'listopia'
      );
    }
    // the code has been stolen by the Chinese
    else
    {
      die ('[ListopiaDB] ... where the hell is this code executing? ' . $serverCheck);
    }

    FB::info('[ListopiaDB] connecting to '.$db['host'].' for db '.$db['db']);
    
    $this->con = new mysqli($db['host'], $db['user'], $db['pass'], $db['db']);

    if ($this->con->connect_errno) {
      die ('[ListopiaDB] Error connecting to '.$db['host'].' .. proper fucked, son.'.$this->con->connect_error);
    }
      
    FB::info('[ListopiaDB] info: ' . $this->con->server_info);
  }
  
  // -----------------------------------------------------------------------------------------------
  function __destruct() 
  {
    FB::info('[ListopiaDB] disconnecting');
    $this->con->close();
  }

  function escape_string($st)
  {
    return $this->con->real_escape_string($st);
  }

  // -----------------------------------------------------------------------------------------------
  function raw_query($s) 
  {
    if (!$q = $this->con->query($s)) 
      return false;

    $line = $q->fetch_array(MYSQLI_NUM);

    $q->close();

    return $line;
  }

  // -----------------------------------------------------------------------------------------------
  function query($s='', $rows=false, $organize=true, $types=false) 
  {
    // FB::log($s);
    
    if (!$q = $this->con->query($s)) 
      return false;
      
    if ($rows !== false) 
      $rows = intval($rows);
    
    $rez = array(); 
    $count = 0;
    $type = $organize ? MYSQLI_NUM : MYSQLI_ASSOC;
    
    while (($rows===false || $count < $rows) && $line = $q->fetch_array($type))
    {
      if ($organize) 
      {
        foreach ($line as $field_id => $value) 
        {
          $field_direct = $q->fetch_field_direct($field_id);

          $table = $field_direct->table;
          if ($table==='') 
            $table=0;
            
          $field = $field_direct->name;
          
          if ($types == true)
          {
            $fieldType = $field_direct->type;
            $rez[$count][$table][$field]['data'] = $value;
            $rez[$count][$table][$field]['type'] = $fieldType;
          }
          else
          {
            $rez[$count][$table][$field] = $value;
          }
        }
      } 
      else 
      {
        $rez[$count] = $line;
      }
      
      ++$count;
    }
    
    $q->close();
      
    return $rez;
  }
  
  // -----------------------------------------------------------------------------------------------
  function execute($s='') 
  {
    if ($this->con->query($s)) 
      return true;
    
    FB::error($this->con->error);
    return false;
  }

  function chooseUnseenList($uid)
  {
    $sqls = <<<EOD
select list_id + 1 as gap_start, 
       next_nr - 1 as gap_end
from (
  select last_visits.list_id, 
         lead(last_visits.list_id) over (order by last_visits.list_id) as next_nr
  from last_visits where user_id = {$uid} and last_visits.list_id >= 0 and last_visits.list_id < 5000 and is_listid_valid(list_id)
) nr
where list_id + 1 <> next_nr and is_listid_valid(list_id + 1)
limit 10;
EOD;
    return $this->query($sqls);
  }
  
  // -----------------------------------------------------------------------------------------------
  function select($options) 
  {
    $default = array (
      'fields' => '*',
      'table' => '',
      'join' => '',
      'condition' => '',
      'group' => '',
      'order' => '',
      'limit' => 0,
      'getTypes' => false
    );
    
    $options = array_merge($default,$options);
    $sql = "SELECT {$options['fields']} FROM {$options['table']} ";

    if ($options['join'] != '')
      $sql .= "JOIN {$options['join']} ";
      
    if ($options['condition'] != '')
      $sql .= "WHERE {$options['condition']} ";
    
    if ($options['group'] != '')
      $sql .= "GROUP BY {$options['group']} ";

    if ($options['order'] != '')
      $sql .= "ORDER BY {$options['order']} ";
      
    if ($options['limit'] != 0)
      $sql .= "LIMIT {$options['limit']} ";

    return $this->query($sql, false, true, $options['getTypes']);
  }
  
  // -----------------------------------------------------------------------------------------------
  function row($options) 
  {
    $default = array (
      'table' => '',
      'fields' => '*',
      'condition' => '1',
      'order' => '1'
    );
    
    $options = array_merge($default,$options);
    $sql = "SELECT {$options['fields']} FROM {$options['table']} WHERE {$options['condition']} ORDER BY {$options['order']}";
    $result = $this->query($sql,1,false);
    
    if (empty($result[0])) 
      return false;
      
    return $result[0];
  }
  
  // -----------------------------------------------------------------------------------------------
  function update($table=null, $array_of_values=array(), $conditions='FALSE') 
  {
    if ($table===null || empty($array_of_values)) 
      return false;
      
    $what_to_set = array();
    foreach ($array_of_values as $field => $value) 
    {
      if (is_array($value) && !empty($value[0])) 
        $what_to_set[] = "$field={$value[0]}";
      else 
        $what_to_set []= "$field='" . $this->con->real_escape_string($value) . "'";
    }
    $what_to_set_string = implode(',', $what_to_set);
    
    return $this->execute("UPDATE $table SET $what_to_set_string WHERE $conditions");
  }
  
  // -----------------------------------------------------------------------------------------------
  function insert($table=null, $array_of_values=array()) 
  {
    if ($table===null || empty($array_of_values) || !is_array($array_of_values)) 
      return false;
      
    $fields=array(); $values=array();
    foreach ($array_of_values as $id => $value) 
    {
      $fields[] = $id;
      if (is_array($value) && !empty($value[0])) 
        $values[] = $value[0];
      else 
        $values[] = "'" . $this->con->real_escape_string($value) . "'";
    }

    $s = "INSERT INTO $table (".implode(',',$fields).') VALUES ('.implode(',',$values).')';
    if ($this->con->query($s)) 
      return $this->con->insert_id;
    
    FB::error($this->con->error);
    return false;
  }  

  
  // -----------------------------------------------------------------------------------------------
  function optimizeHotTables()
  {
$sqls = <<<EOD
DROP FUNCTION IF EXISTS is_listid_valid;

DELIMITER //

CREATE FUNCTION is_listid_valid(lid INT)
RETURNS INT
NOT DETERMINISTIC
BEGIN
DECLARE select_var INT;
SET select_var = (SELECT COUNT(*) FROM t_lists where list_id = lid and list_featured != 'deleted');
RETURN select_var;
END
//

DELIMITER ;
EOD;
    //$this->execute($sqls);
    //print($this->con->error);

    $this->execute("OPTIMIZE TABLE ai");
    $this->execute("OPTIMIZE TABLE last_visits");
    $this->execute("OPTIMIZE TABLE t_discussion");
    $this->execute("OPTIMIZE TABLE t_items");
    $this->execute("OPTIMIZE TABLE t_lists");
    $this->execute("OPTIMIZE TABLE users");
    $this->execute("OPTIMIZE TABLE itemlikes");
    $this->execute("OPTIMIZE TABLE avatars");
    $this->execute("OPTIMIZE TABLE messages");
  }
}
