// SqlResult.cxx

#include "dataset_sql/SqlResult.h"
#include "dataset_sql/SelectQuery.h"
#include "dataset_sql/InsertQuery.h"
#include "dataset_sql/DeleteQuery.h"
#include <iostream>
#include <cassert>
#include <string>
#include <map>
#include <sstream>

using std::string;
using std::endl;
using std::map;
using std::ostream;
using std::istringstream;
using dset::SqlResult;
using dset::SelectQuery;
using dset::InsertQuery;
using dset::DeleteQuery;

//**********************************************************************
// Local typedefs.
//**********************************************************************

typedef SqlResult::NameList NameList;
typedef SqlResult::Row Row;
typedef SqlResult::Result Result;
typedef SqlResult::size_type size_type;

//**********************************************************************
// Local definitions.
//**********************************************************************

namespace {

// Remove unwanted columns from a row

void prune_row(Row& row, const NameList& cols) {
  // Flag to mark column to be deleted
  bool del;
  Row::iterator rit = row.begin();
  while(rit != row.end()) {
    del = true;
    unsigned int i=0;
    for(; i < cols.size(); ++i) {
      if(rit->first == cols[i]) {
        del = false;
        break;
      }
    }
    if( del) {
      row.erase(rit);
      rit = row.begin();
    } else ++rit;
  }
  return;
}

//**********************************************************************

// Simple implementation of SQL LIKE.
// We allow only one %.

bool like(string s1, string s2) {
  string::size_type ipos = s2.find("%");
  if ( ipos == string::npos ) {
    return s1 == s2;
  }
  bool match1 = s1.substr(0,ipos) == s2.substr(0,ipos);
  if ( ! match1 ) return false;
  string rem1 = s1.substr(ipos);
  string rem2 = s2.substr(ipos+1);
  string::size_type lrem = rem2.size();
  if ( lrem == 0 ) return true;
  string::size_type ipos1 = s1.size() - rem1.size();
  rem1 = rem1.substr(ipos1, lrem);
  return rem1 == rem2;
}
  
} // end of unnamed namespace
 
//**********************************************************************
// Member functions.
//**********************************************************************

// Constructor.

SqlResult::SqlResult() : m_valid(false) {
}

//**********************************************************************

// Constructor using vector of column names.

SqlResult::SqlResult(NameList &names) : m_valid(true) {
  m_names = names;
}

//**********************************************************************

// Copy Constructor.

SqlResult::SqlResult(const SqlResult& res) {
  m_names = res.m_names;
  m_result = res.m_result;
  m_valid = res.m_valid;
}

//**********************************************************************

// Destructor.

SqlResult::~SqlResult() {}

//**********************************************************************

// Validity.

bool SqlResult::is_valid() const {
  return m_valid;
}

//**********************************************************************

// Check validity of columns

bool SqlResult::check_valid_cols(NameList& cols) const {
  for ( NameList::const_iterator inam=cols.begin();
        inam!=cols.end(); ++inam ) {
    string name = *inam;
    if ( find(m_names.begin(), m_names.end(), name) ==
         m_names.end() ) {
      return false;
    }
  }
  return true;  
}

//**********************************************************************

// Insert a new row.
// Returns 0 on success.

int SqlResult::insert_row(const Row& row) { 	

  // Empty row
  if(row.empty()) return 1;

  // If invalid field is specified in query, return 1
  NameList cols;    
  for(Row::const_iterator it = row.begin(); it != row.end(); ++it)
    cols.push_back(it->first);
                    
  if( !check_valid_cols(cols) )
    return 1;

  m_result.push_back(row);
  return 0;
}  

//**********************************************************************

// Fetch a row.

const Row& SqlResult::fetch_row(const unsigned int index) const {
  
  if( index < m_result.size() )
    return m_result[index];

  static Row badrow;
  return badrow;
}

//**********************************************************************

// Return the row count.

size_type SqlResult::num_rows() const {
  return m_result.size();
}

//**********************************************************************

// Return the column count.

int SqlResult::num_cols() const {
  return m_names.size();
}

//**********************************************************************

// Return the column names.

NameList SqlResult::get_col_names() const {
  return m_names;
}

//**********************************************************************

// Return the rows.

Result SqlResult::get_rows() const {
  return m_result;
}

//**********************************************************************

// Return the Result of execution of a SELECT query.

SqlResult SqlResult::
execute_select_query(const SelectQuery query) const {

  string con = query.get_constraint();
  string columns = query.get_columns();

  NameList schema_cols = get_col_names();

  // If no constraints or columns are specified, return the entire set
  if ( con.size() == 0 && columns.size() == 0 ) return *this;

  // Initialize cols with the columns specified in the query
  // If none specified, initialize it with all columns from schema
  NameList cols;
  if ( columns.size() == 0 ) {
    cols = schema_cols;
  } else {
    string::size_type ipos, jpos;
    ipos = 0;
    while ( (jpos = columns.find(',',ipos)) != string::npos ) {
      string column = columns.substr(ipos, jpos-ipos);
      cols.push_back(column);
      ipos = jpos+1;
    }

    string lastcolumn = columns.substr(ipos, columns.length()-ipos);
    cols.push_back(lastcolumn);

    // If invalid field is specified in query, mark result invalid
    if ( ! check_valid_cols(cols) ) {
      m_valid = false;
      return SqlResult();
    }
  }

  // Create the empty subresult
  SqlResult subresult(schema_cols);

  // If the constraint is empty, return the entire result
  // after stripping out the unwanted columns.
  if ( con.size() == 0 ) {
    for ( Result::const_iterator ires = m_result.begin();
          ires != m_result.end(); ++ires ) {
      Row row = *ires;
      prune_row(row, cols);
      subresult.insert_row(row);
    }
    return subresult;
  }    

  size_type rlim = 0;
  string::size_type pos;

  // If constraint contains only "limit", return that many rows
  // agfter selecting the desired columns.
  if ( (pos = con.find("limit")) == 0 ) {
    string val = con.substr(pos+6, con.size()-(pos+6));
    rlim = atoi(val.c_str());
    for ( Result::const_iterator pres = m_result.begin();
         pres != m_result.end() && subresult.num_rows() < rlim; ++pres ) {
      Row row = *pres;
      // Prune row only if all columns aren't specified
      if (cols.size() < m_names.size() ) prune_row(row, cols);
      subresult.insert_row(row);
    }
    return subresult;
  }
    
  // If a constraint is specified, return rows satisfying it.
  // Extract first "field operator value" or "field operator 'value'".
  string::size_type ipos = 0;
  // Strip any leading whitespace.
  while ( ipos<con.size() && isspace(con[ipos]) ) ++ipos;
  if ( ipos == string::npos ) return SqlResult();
  // Strip "(" -- no grouping -- we just ignore them.
  if ( con[ipos] == '(' ) {
    ++ipos;
  }
  // Fetch field name.
  string field;
  for ( ; ipos<con.size() && !isspace(con[ipos]); ++ipos )
    field += con[ipos];
  if ( field.size() == 0 ) return SqlResult();
  // Strip any whitespace.
  while ( ipos<con.size() && isspace(con[ipos]) ) ++ipos;
  if ( ipos == string::npos ) return SqlResult();
  // Fetch operator.
  string op;
  for ( ; ipos<con.size() && !(isspace(con[ipos]) && op!="NOT"); ++ipos )
    op += con[ipos];
  if ( op.size() == 0 ) return SqlResult();
  // Strip any whitespace.
  while ( ipos<con.size() && isspace(con[ipos]) ) ++ipos;
  if ( ipos == string::npos ) return SqlResult();
  // Fetch value.
  string value;
  char ch0 = con[ipos++];
  // ... start with quote ==> continue until endquote
  if ( ch0 == '\'' ) {
    for ( ; ipos<con.size() && con[ipos]!='\''; ++ipos )
      value += con[ipos];
    if ( ipos == string::npos ) return SqlResult();
    ++ipos;
  // ... otherwise continue until blank
  } else {
    value += ch0;
    for ( ; ipos<con.size() && !isspace(con[ipos]) && con[ipos]!=')';
          ++ipos )
      value += con[ipos];
    if ( value.size() == 0 ) return SqlResult();
  } 
  // Strip ")"
  if ( ipos<con.size() && con[ipos] == ')' ) {
    ++ipos;
  }
  // Strip any whitespace.
  while ( ipos<con.size() && isspace(con[ipos]) ) ++ipos;

  // Define the next query and flag indictating if this routine should
  // be called again with that query.
  SelectQuery newquery(query.get_columns(), "");
  bool repeat = false;
  string cond;
  string::size_type jpos = ipos;
  for ( ; jpos<con.size() && !isspace(con[jpos]); ++jpos )
    cond += con[jpos];
  if ( cond.size() ) {
    // If condition contains only "limit", don't strip it out
    if ( cond != "limit") {
      ipos = jpos;
    }
    string newconstraint = con.substr(ipos);
    string newcolumns = query.get_columns();
    newquery.andconstraint(newconstraint);
    repeat = true; 
  }  

  // If invalid field is specified in constraint, mark result invalid
  NameList::const_iterator it =
    find(m_names.begin(), m_names.end(), field);
  if ( it == m_names.end() ) {
    m_valid = false;
    return SqlResult();
  }

  // Map to associate the operator strings with the numeric values
  static map<string, int> m_operate;
  m_operate["="] = 1;
  m_operate["<"] = 2;
  m_operate[">"] = 3;
  m_operate["<>"] = 4;  
  m_operate["!="] = 4;
  m_operate["<="] = 5;  
  m_operate[">="] = 6;
  m_operate["LIKE"] = 7;
  m_operate["NOT LIKE"] = 8;
  string strParsedWord;

  for ( Result::const_iterator pres = m_result.begin();
        pres != m_result.end(); ++pres ) {
    // Extract each row and test against constraint
    Row row = *pres;
    bool match;
    // Identify rows which satisfy the constraint 	    
    for ( Row::const_iterator icol=row.begin();
          icol!=row.end(); ++icol ) {
      // Set this flag if there is a match 	
      match = false;
      // First match the fieldname
      string fieldname = icol->first;
      if ( fieldname == field ) {
	string fieldval = icol->second;
        // Match the value
        switch (m_operate[op]) {
        case 1: match = fieldval == value; break;
        case 2: match = fieldval < value; break;
        case 3: match = fieldval > value; break;
        case 4: match = fieldval != value; break;
        case 5: match = fieldval <= value; break;
        case 6: match = fieldval >= value; break;
        case 7: match = like(fieldval, value); break;
        case 8: match = ! like(fieldval, value); break;
        default: break;
        }
        if ( match == true ) break;
      }
    }
    // If match flag is set, insert the row
    if ( match ) assert(subresult.insert_row(row) == 0);
  }

  // Check if more conditions should be satisfied
  if ( repeat ) {
    SqlResult newsubresult(cols);
    newsubresult = subresult.execute_select_query(newquery);
    return newsubresult;
  }

  // Prune rows to contain only the columns of interest
  for ( Result::const_iterator pres = subresult.m_result.begin();
        pres != subresult.m_result.end(); ++pres ) 
    prune_row((Row&)(*pres), cols);
    
  return subresult;
}

//**********************************************************************

// Return the result of execution of a INSERT query.
// Returns 0 on success.

int SqlResult::execute_insert_query(const InsertQuery query) {
  return ( this->insert_row(query.get_row()) );
}

//**********************************************************************

// Return the result of execution of a UPDATE query.

int SqlResult::execute_update_query(const InsertQuery insquery, 
		                    const SelectQuery selquery) {

  Row insrow = insquery.get_row();
  NameList cols;

  for ( Row::const_iterator icol=insrow.begin();
        icol!=insrow.end(); ++icol) {
    string col = icol->first;
    cols.push_back(col);
  }

  // If invalid field is specified in insert query, query fails
  if ( ! check_valid_cols(cols) ) return 1;

  SqlResult res = this->execute_select_query(selquery);  
  if ( ! res.is_valid() ) return 1;
  if ( res.num_rows() == 0 ) return 2;

  Row row = res.fetch_row(0);  
  for( Row::const_iterator icol = insrow.begin();
       icol != insrow.end(); ++icol ) {
    for( Row::iterator icol1 = row.begin();
         icol1 != row.end(); ++icol1 ) {
      // Update all attributes specified in updaterow
      if ( icol->first == icol1->first ) {
        icol1->second = icol->second;
	break;
      }
    }
  }

  DeleteQuery delquery(selquery.get_constraint());
  assert(execute_delete_query(delquery) == 0);
  assert(insert_row(row) == 0);
  return 0;
}

//**********************************************************************

// Return the result of execution of a DELETE query.
// Returns 0 on success.

int SqlResult::execute_delete_query(const DeleteQuery query) {
  string constraint = query.get_constraint();
  istringstream sscon(constraint); 
  string op;
  Row conditions;
  string field;	

  // 'words' are selected base on whitespace and EOF characters
  while ( sscon >> field ) {
    if ( field == "and" || field == "or" ||
         field == "AND" || field == "OR" ) sscon >> field;
    sscon >> op; //ignore operator : assume '=='
    if ( op != "=" ) return 2;
    // Read value handling single quotes.
    string value;
    bool first = true;
    bool quoted = false;
    while ( sscon ) {
      char ch;
      sscon.get(ch);
      if ( first ) {
        if ( isspace(ch) ) continue;
        first = false;
        if ( ch == '\'' ) {
          quoted = true;
          continue;
        }
      }
      if ( quoted ) {
        if ( ch == '\'' ) {
          break;
        }
      } else {
        assert( ch != '\'' );
      }
      value += ch;
    }
    // If invalid field is present in constraint, query fails
    NameList::const_iterator inam =
      find( m_names.begin(), m_names.end(), const_cast<string&>(field) );  
    if ( inam == m_names.end() ) return 1;
    conditions[field] = value;
  }

  bool del;	
  // For each Row in the result.	
  for( Result::iterator it = m_result.begin(); 
       it != m_result.end(); ++it ) {
    Row row = *it;
    
    // Flag to keep track of row to be deleted
    del = true;

    // For each condition to be tested.
    for( Row::const_iterator cit = conditions.begin(); 
         cit != conditions.end(); ++cit ) { 
      
      // If even one condition fails, don't delete the row
      if(row[cit->first] != cit->second) {
        del = false;
        break;
      }
    }

    if(del) {
      m_result.erase(it);
      --it;
    }

  } //End For.

  return 0;   
}

//**********************************************************************

// Output stream.
ostream& SqlResult::ostr(ostream &str) const {
  if (!m_valid) str << "Invalid Result";
  else if (num_rows() == 0) str << "Empty Result";
  else {
    bool first = true;
    for ( Result::const_iterator it = m_result.begin(); 
         it != m_result.end(); ++it ) {
      for ( Row::const_iterator miter = (*it).begin();
            miter != (*it).end(); ++miter ) {
	if ( ! first ) str << endl;
        first = false;
        str << miter->first << " - " << miter->second;
      }
    }
  }
  return str;
}

//**********************************************************************
// Free functions.
//**********************************************************************

// Output stream.
ostream& operator<<(ostream& lhs, const SqlResult& rhs) {
  return rhs.ostr(lhs);
}

//**********************************************************************