package org.opengroupware.jope.eoaccess;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.text.CharacterIterator;
import java.text.StringCharacterIterator;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.opengroupware.jope.eoaccess.mysql.EOMySQLAdaptor;
import org.opengroupware.jope.eoaccess.postgresql.EOPostgreSQLAdaptor;
import org.opengroupware.jope.foundation.NSDisposable;
import org.opengroupware.jope.foundation.NSObject;
import org.opengroupware.jope.foundation.UString;

/*
 * EOAdaptor
 * 
 * Wraps a JDBC adaptor. In "real" EOF we would use a subclass, but its not
 * (yet) considered worth the effort ;-)
 * 
 * The adaptor also works as a SQL connection pool.
 * 
 * THREAD: this class, especially the connection pool, is thread safe. You
 *         should usually use one adaptor per login-configuration.
 */
public class EOAdaptor extends NSObject implements NSDisposable {
  protected static final Log log = LogFactory.getLog("EOAdaptor");
  
  protected String url;
  protected int    maxConnections;
  protected long   maxChannelWaitTime;
  
  protected List<EOAdaptorChannel> availableChannels;
  protected List<EOAdaptorChannel> checkedOutChannels;
  
  protected int openCountSinceLastMaintenance    = 0;
  protected int releaseCountSinceLastMaintenance = 0;
  
  protected EOModel model;
  protected EOModel modelPattern;
  protected long    modelFetchTime = 0;
  public    int     modelRefetchTimeout = 3; /* in seconds */
  
  public static EOAdaptor adaptorWithURL(String _url, EOModel _model) {
    // TODO: select adaptor based upon URL
    EOAdaptor adaptor = null;
    
    if (_url.startsWith("jdbc:mysql"))
      adaptor = new EOMySQLAdaptor(_url, _model, 64 /* max pool size */);
    else if (_url.startsWith("jdbc:postgres"))
      adaptor = new EOPostgreSQLAdaptor(_url, _model, 64 /* max pool size */);
    else {
      System.err.println("no specific adaptor for url: " + _url);
      adaptor = new EOAdaptor(_url, _model, 64 /* max pool size */);
    }
    
    if (adaptor == null)
      return null;
    if (!adaptor.loadDriver()) {
      System.err.println("ERROR: failed to load driver for JDBC URL: " + _url);
      return null;
    }
    
    return adaptor;
  }
  public static EOAdaptor adaptorWithURL(String _url) {
    return adaptorWithURL(_url, null /* model */);
  }

  protected EOAdaptor(String _url, EOModel _model, int maxSize) {
    this.url                = _url;
    this.maxConnections     = maxSize;
    this.maxChannelWaitTime = 3000; /* ms */
    
    this.availableChannels  = new ArrayList<EOAdaptorChannel>(16);
    this.checkedOutChannels = new ArrayList<EOAdaptorChannel>(16);
    
    if (_model != null) {
      if (_model.isPatternModel())
        this.modelPattern = _model;
      else
        this.model = _model;
    }    
  }
  
  /* loading a JDBC driver */
  
  protected boolean loadDriver() {
    /* this should be overridden by subclasses */
    return true;
  }

  /* testing connection */

  public boolean testConnect() {
    EOAdaptorChannel channel;
    
    channel = this.openChannelAndRegisterInPool();
    if (channel == null) {
      log.info("failed to acquire a connection to the DB: " + this.url);
      return false;
    }

    log.debug("managed to acquired a connection to the DB: " + this.url);
    this.releaseChannel(channel);
    return true;
  }
  
  /* methods */
  
  public boolean hasOpenChannels() {
    /* Note: this only tracks pooled connections */
    int coCount, availCount;
    synchronized (this) {
      coCount    = this.checkedOutChannels.size();
      availCount = this.availableChannels.size();
    }
    return coCount > 0 || availCount > 0;
  }
  
  protected EOAdaptorChannel checkoutFirstAvailableChannel() {
    // this needs to run inside the monitor
    for (EOAdaptorChannel availChannel: this.availableChannels) {
      if (this.shouldKeepChannel(availChannel)) {
        this.checkedOutChannels.add(availChannel);
        this.availableChannels.remove(availChannel);
        return availChannel;
      }
      // else
      //  needsMaintenance = true; /* found an invalid entry */
    }
    return null;
  }
  
  protected EOAdaptorChannel openChannelFromPool(int _attempt) {
    // TODO: this method is TOO big, split it up
    
    boolean isDebugOn = log.isDebugEnabled();
    boolean isInfoOn  = log.isInfoEnabled();
    if (isInfoOn)
      log.info("open channel from pool: (attempt=" + _attempt + ")");
    
    /* first look for an available connection */
    EOAdaptorChannel channel = null;
    boolean needsMaintenance = false;
    boolean mayCreate        = false;
    
    synchronized (this) {
      this.openCountSinceLastMaintenance++;
      
      channel = this.checkoutFirstAvailableChannel();
      if (channel == null)
        mayCreate = this.checkedOutChannels.size() < this.maxConnections;
    }
    
    /* create a new connection if required */
    
    if (channel == null) {
      if (mayCreate) {
        if (isDebugOn) log.info("  no avail connection, create ...");
        channel = this.openChannelAndRegisterInPool();
      }
      else {
        log.warn("out of pool connections, attempt: " + _attempt);

        int coCount, availCount;
        synchronized (this) {
          coCount    = this.checkedOutChannels.size();
          availCount = this.availableChannels.size();
        }
        log.warn("  checked-out=" + coCount + " / avail=" + availCount +
                      " / max=" + this.maxConnections);

        if (_attempt > 3) {
          log.error("too many attempts to open a SQL connection, stop.");
          return null;
        }

        /* wait a while for a connection to become available */
        
        boolean gotInterrupted = false;
        long startTime = new Date().getTime();
        synchronized (this) {
          /* hm, we just got a lock, maybe a channel became available? :-) */
          channel = this.checkoutFirstAvailableChannel();
          
          while (channel == null) { /* protect against spurious wakeups */
            try {
              /* Note: using maxChannelWaitTime is not entirely correct, this
               *       might make us up to twice as long.
               */
              this.wait(this.maxChannelWaitTime /* ms */);
            }
            catch (InterruptedException e) {
              /* OK, we got Interrupted, so we stop doing anything. */
              gotInterrupted = true;
              break; /* leave while loop */
            }
            
            /* ok, we did wait, so lets check whether there is a connection */
            //System.err.println("SCAN AVAIL: "+this.availableChannels.size());
            channel = this.checkoutFirstAvailableChannel();
            
            /* we still didn't find a channel, lets check our wait time */
            // TODO: not too got to detect time inside a lock?
            long timePassed = new Date().getTime() - startTime;
            if (timePassed > this.maxChannelWaitTime /* ms */)
              break;
          }
        }        
        
        if (channel != null) {
          if (isDebugOn)
            log.info("found a free channel after a bit of waiting.");
          return channel;
        }
        else if (gotInterrupted)
          log.error("  wait got interrupted ...");
        
        log.error("  failed to wait for a free channel ...");
      }
    }
    
    /* perform maintenance */
    
    if (needsMaintenance  || this.shouldMaintainPool()) {
      if (isInfoOn) log.info("  maintain ...");
      this.maintainPool();
    }
    
    /* return */
    
    if (channel == null)
      return null;
    
    if (isInfoOn) log.info("connection: " + channel.connection);
    return channel;
  }
  public EOAdaptorChannel openChannelFromPool() {
    return this.openChannelFromPool(1);
  }
  
  public void releaseChannel(EOAdaptorChannel _channel, boolean _keepConnect) {
    if (_channel == null)
      return;
    
    boolean isDebugOn = log.isDebugEnabled();
    if (isDebugOn)    
      log.info("releasing channel: " + _channel);
    
    boolean didKeepEntry = _keepConnect;
    
    synchronized (this) {
      this.releaseCountSinceLastMaintenance++;
      
      this.checkedOutChannels.remove(_channel);
        
      if (_keepConnect && this.shouldKeepChannel(_channel))
        this.availableChannels.add(_channel);
      else
        didKeepEntry = false;
    }
    
    if (didKeepEntry && isDebugOn && _channel != null)
      log.debug("  kept entry: " + _channel);
    
    if (!didKeepEntry)
      _channel.close();
    
    if (!didKeepEntry || this.shouldMaintainPool())
      this.maintainPool();
    
    /* now notify other threads waiting for a free connection */

    synchronized (this) {
      this.notifyAll();
    }
    
    if (isDebugOn)
      log.debug("finished release.");
  }
  
  public void releaseChannel(EOAdaptorChannel _channel) {
    this.releaseChannel(_channel, true /* try to keep connection */);
  }
  
  public void releaseAfterError(EOAdaptorChannel _channel, Exception _e) {
    log.info("releasing connection after error.");
    this.releaseChannel(_channel, false /* do not try to keep */);
  }
  
  /*
   * This creates a new connection which is not registered in the pool.
   */
  public EOAdaptorChannel openChannel() {
    Connection connection = null;
    
    log.info("opening channel ...");
    try {
      connection = DriverManager.getConnection(this.url);
    }
    catch (SQLException e) {
      log.error("failed to create new SQL connection", e);
      
      int coCount, availCount;
      synchronized (this) {
        coCount    = this.checkedOutChannels.size();
        availCount = this.availableChannels.size();
      }
      log.error("  checked out=" + coCount + "/avail " + availCount);
      return null;
    }
    
    /* create entry */
    
    return this.primaryCreateChannelForConnection(connection);
  }
  protected EOAdaptorChannel primaryCreateChannelForConnection(Connection _c) {
    /* can be overridden by subclasses to provide specific channels */
    return new EOAdaptorChannel(this, _c);
  }
  
  public EOAdaptorChannel openChannelAndRegisterInPool() {
    EOAdaptorChannel channel = this.openChannel();
    if (channel == null)
      return null;
    
    /* register entry */
    
    synchronized (this) {
      this.checkedOutChannels.add(channel);
    }
    
    return channel;
  }
  
  /* pool maintenance */
  
  protected boolean shouldMaintainPool() {
    synchronized (this) {
      // TODO: find a good maintenance interval
      if (this.releaseCountSinceLastMaintenance > 64)
        return true;
      if (this.openCountSinceLastMaintenance > 64)
        return true;
    }
    return false;
  }
  
  public void maintainPool() {
    // TODO: we might want to run this in a separate thread (java.util.Timer)
    
    /* scan pool for connections which should be closed */
    log.debug("running maintenance ...");
    
    List<EOAdaptorChannel> toBeClosed = null;
    
    /* collect expired connections */
    
    synchronized (this) {
      /* scan for connections which should be closed */
      
      for (EOAdaptorChannel entry: this.availableChannels) {
        if (!this.shouldKeepChannel(entry)) {
          if (toBeClosed == null)
            toBeClosed = new ArrayList<EOAdaptorChannel>(4);
          
          toBeClosed.add(entry);
        }
      }
      
      /* remove expired entries */
      if (toBeClosed != null) {
        log.debug("  removing expired entries ...");
        for (EOAdaptorChannel entry: toBeClosed)
          this.availableChannels.remove(entry);
      }
      
      this.openCountSinceLastMaintenance    = 0;
      this.releaseCountSinceLastMaintenance = 0;
    }
    
    /* close expired entries */
    
    if (toBeClosed != null) {
      log.debug("  closing expired entries ...");
      for (EOAdaptorChannel entry: toBeClosed)
        this.closeChannel(entry);
    }
    
    log.debug("pool maintenance done.");
  }
  
  /* non-synchronized methods */
  
  protected void closeChannel(EOAdaptorChannel _entry) {
    if (_entry == null)
      return;
    
    log.debug("closing entry: " + _entry);
    
    if (_entry.connection == null) /* nothing to tear down */
      return;
    
    try {
      if (!_entry.connection.isClosed())
        _entry.connection.close();
    }
    catch (SQLException e) {
      log.debug("SQL exception while tearing down the connection", e);
    }
  }
  
  protected EOAdaptorChannel findChannelForConnection(Connection _c) {
    for (EOAdaptorChannel entry: this.checkedOutChannels) {
      if (entry.connection == _c)
        return entry;
    }
    return null;
  }
  
  protected boolean shouldKeepChannel(EOAdaptorChannel _channel) {
    if (_channel == null)
      return false;
    
    if (this.maxConnections <= this.availableChannels.size()) {
      log.info("reached connection pool limit: " + this.maxConnections);
      return false;
    }
    
    if (_channel.connection == null)
      return false;
    
    try {
      if (_channel.connection.isClosed())
        return false;
    }
    catch (SQLException e) {
      log.debug("catched exception while checking close status", e);
      return false;
    }
    
    // TODO: check some connection age
    
    return true;
  }
  
  /* high level convenience methods which maintain their connection */

  // TODO: fix return type
  public List<Map<String, Object>> performSQL(String _sql) {
    if (_sql == null || _sql.length() == 0)
      return null;
    
    EOAdaptorChannel channel = this.openChannelFromPool();
    if (channel == null) return null;
    
    List<Map<String, Object>> result = channel.performSQL(_sql);
    if (result == null) { /* failed with some error */
      this.releaseChannel(channel, false /* do not keep channel */);
      return null;
    }
    
    this.releaseChannel(channel);
    return result;
  }
  public int performUpdateSQL(String _sql) {
    if (_sql == null || _sql.length() == 0)
      return -1;
    
    EOAdaptorChannel channel = this.openChannelFromPool();
    if (channel == null) return -1;
    
    int result = channel.performUpdateSQL(_sql);
    if (result < 0) { /* failed with some error */
      this.releaseChannel(channel, false /* do not keep channel */);
      return result;
    }
    
    this.releaseChannel(channel);
    return result;
  }
  
  // TODO: fix return type
  public Map<String, Object> fetchRecord
    (String _table, String _field, Object _value)
  {
    if (_table.length() < 1 || _field.length() < 1)
      return null;
    if (_value == null)
      return null;
    
    /* generate SQL */
    
    EOSQLExpression e = this.expressionFactory().createExpression(null);
    StringBuffer sql = new StringBuffer(255);
    
    sql.append("SELECT * FROM ");
    sql.append(e.sqlStringForSchemaObjectName(_table));
    sql.append(" WHERE ");
    sql.append(e.sqlStringForSchemaObjectName(_field));
    sql.append(" = ");
    sql.append(e.formatValueForAttribute(_value, null));
    sql.append(" LIMIT 2"); /* 2 so that we can detect multiple records */
    
    /* run query */
    
    List<Map<String, Object>> records = this.performSQL(sql.toString());
    if (records == null)
      return null;
    
    if (records.size() == 0) {
      log.debug("found no matching record in table " + _table + ": " +
                     _field + " = " + _value);
      return null;
    }
    if (records.size() > 1) {
      log.warn("found multiple matches for fetchRecord, table " +
                     _table + ": " + _field + " = " + _value);
    }
    
    return records.get(0);
  }

  public boolean insertRow(String _table, Map<String, Object> _record) {
    if (_table == null || _record == null)
      return false;
    
    EOAdaptorChannel channel = this.openChannelFromPool();
    if (channel == null) return false;
    
    if (!channel.insertRow(_table, _record)) {
      this.releaseChannel(channel, false /* do not keep channel */);
      return false;
    }
    
    this.releaseChannel(channel);
    return true;
  }

  public boolean updateRow
    (String _table, String _pkey, Object _value, Map<String, Object> _record)
  {
    // Note: this does not support insertion of NULLs
    if (_table == null || _record == null)
      return false;
    
    EOAdaptorChannel channel = this.openChannelFromPool();
    if (channel == null) return false;
    
    if (!channel.updateRow(_table, _pkey, _value, _record)) {
      this.releaseChannel(channel, false /* do not keep channel */);
      return false;
    }
    
    this.releaseChannel(channel);
    return true;
  }
  
  /* quoting SQL expressions */
  // TODO: maybe we want to move that to an own per-adaptor object?
  
  public String stringByQuotingIdentifier(String _id) {
    if (_id == null) return null;
    
    // TODO: fix me
    return "\"" + escape(_id, '"') + "\"";
  }
  
  public boolean escapeIntoBuffer
    (StringBuffer _sb, String _value, char _quoteChar)
  {
    if (_value == null)
      return false;

    StringCharacterIterator localParser = new StringCharacterIterator(_value);
    
    for (char c = localParser.current();
         c != CharacterIterator.DONE;
         c = localParser.next())
    {
      if (c == _quoteChar) {
        _sb.append('\'');
        _sb.append('\'');
      }
      else if (c == '\\') {
        _sb.append('\\');
        _sb.append('\\');
      }
      else
        _sb.append(c);
    }
    
    return true;
  }

  public String escape(String _value, char _quoteChar) {
    if (_value == null)
      return null;
    if (_value.length() == 0)
      return "";
    
    // TODO: better use some FastStringBuffer (unsynchronized)
    StringBuffer buffer = new StringBuffer(_value.length());
    if (!this.escapeIntoBuffer(buffer, _value, _quoteChar))
      return null;
    
    return buffer.toString ();
  }
  
  public Class defaultExpressionClass() {
    return EOSQLExpression.class;
  }
  public Class expressionClass() {
    return this.defaultExpressionClass();
  }
  public EOSQLExpressionFactory expressionFactory() {
    return new EOSQLExpressionFactory(this);
  }
  public EOSchemaGeneration synchronizationFactory() {
    return new EOSynchronizationFactory(this);
  }
  
  /* model support */
  
  public void setModelPattern(EOModel _pattern) {
    synchronized(this) {
      if (_pattern == null) {
        this.modelPattern = null;
        this.model = null;
      }
      else {
        this.modelPattern = _pattern;
        this.model = _pattern.isPatternModel() ? null : this.modelPattern;
      }
    }
  }
  public EOModel modelPattern() {
    synchronized(this) {
      return this.modelPattern;
    }
  }
  
  public EOModel model() {
    synchronized(this) {
      if (this.model != null) {
        // TODO: check timeout
        return this.model;
      }
    }
    
    log.debug("determine model for adaptor ...");
    
    EOModel newModel = null;
    if (this.modelPattern != null) {
      log.debug("  create model by resolving pattern ...");
      newModel = this.resolveModelPattern(this.modelPattern);
    }
    else {
      log.debug("  fetch model from database ...");
      newModel = this.fetchModel();
    }
    
    if (newModel == null)
      log.info("could not fetch model from database.");
    
    synchronized(this) {
      this.model = newModel;
      this.modelFetchTime = new Date().getTime();
    }
    return newModel;
  }
  
  public EOModel fetchModel() {
    EOAdaptorChannel channel = this.openChannelFromPool();
    if (channel == null) {
      log.info("could not open channel to fetch model ...");
      return null;
    }
    
    EOModel newModel = null;
    try {
      String[] tableNames = channel.describeTableNames();
      newModel = channel.describeModelWithTableNames(tableNames);
    }
    finally {
      this.releaseChannel(channel);
    }
    return newModel;
  }
  
  public EOModel resolveModelPattern(EOModel _pattern) {
    if (_pattern == null) return null;
    if (!_pattern.isPatternModel()) return _pattern;
    
    EOEntity[] entities = _pattern.entities();
    if (entities == null)
      return this.fetchModel();
    if (entities.length == 0) /* not sure whether this is a good idea */
      return this.fetchModel();
    
    log.info("starting to resolve pattern model ...");
    
    /* start fetches */
    
    EOAdaptorChannel channel = this.openChannelFromPool();
    if (channel == null) {
      log.info("could not open channel to fetch model ...");
      return null;
    }
    
    EOModel newModel = null;
    try {
      /* determine set of entities to work upon (tableNameLike) */
      
      if (_pattern.hasEntitiesWithExternalNamePattern()) {
        // TODO: maybe we should improve this for database which have a
        //       large number of dynamic tables (some kind of on-demand
        //       loading?)
        //       We could also declare an entity as having a "static"
        //       structure?
        log.info("  resolving dynamic table names ...");
        
        String[] tableNames = channel.describeTableNames();
        if (log.isInfoEnabled()) {
          log.info("  fetched table names: " + 
                   UString.componentsJoinedByString(tableNames, ", "));
        }
        
        List<EOEntity> resolvedList =
          new ArrayList<EOEntity>(tableNames.length);
        
        /* now lets each entity produce a clone for the given table */
        for (int i = 0; i < entities.length; i++) {
          entities[i].addEntitiesMatchingTableNamesToList
            (resolvedList, tableNames);
        }
        
        entities = resolvedList.toArray(new EOEntity[resolvedList.size()]);
      }
      
      if (entities != null && entities.length > 0) {
        /* now collect all table names */
      
        String[] tableNames = new String[entities.length];
        for (int i = 0; i < entities.length; i++)
          tableNames[i] = entities[i].externalName();

        /* fetch model for the tables we operate on */
        
        EOModel storedModel = channel.describeModelWithTableNames(tableNames);
        if (storedModel == null) {
          log.error("the database doesn't provide information for all tables, "+
                    "cannot resolve model: " +
                    UString.componentsJoinedByString(tableNames, ", "));
          return null;
        }
        
        /* now give all entities a chance to update their information */
        
        for (int i = 0; i < entities.length; i++)
          entities[i] = entities[i].resolveEntityPatternWithModel(storedModel);
      }
      
      /* create model object */
      
      if (entities != null)
        newModel = new EOModel(entities);
    }
    finally {
      this.releaseChannel(channel);
    }
    log.info("finished resolving pattern model: " + newModel);
    return newModel;
  }
  
  /* dispose */
  
  public void dispose() {
    /* Note: we also dispose channels which are checked out! */
    List<EOAdaptorChannel> channels1 = null, channels2 = null;
    
    synchronized (this) {
      channels1 = this.availableChannels;
      channels2 = this.checkedOutChannels;
      this.availableChannels  = null;
      this.checkedOutChannels = null;
    }
    
    if (channels1 != null) {
      for (EOAdaptorChannel channel: channels1)
        channel.dispose();
    }
    if (channels2 != null) {
      for (EOAdaptorChannel channel: channels2)
        channel.dispose();
    }
  }
  
  /* description */
  
  public void appendAttributesToDescription(StringBuffer _d) {
    super.appendAttributesToDescription(_d);

    int coCount, availCount, mOpCount, mRelCount;
    synchronized (this) {
      coCount    = this.checkedOutChannels.size();
      availCount = this.availableChannels.size();
      mOpCount   = this.openCountSinceLastMaintenance;
      mRelCount  = this.releaseCountSinceLastMaintenance;
    }
    
    _d.append(" #checked-out=" + coCount);
    _d.append(" #available="   + availCount);
    
    _d.append(" maintenance={#opened=");
    _d.append(mOpCount);
    _d.append(", #released=");
    _d.append(mRelCount);
    _d.append("}");
  }
}