package org.opengroupware.jope.eoaccess.postgresql;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Map;

import org.opengroupware.jope.eoaccess.EOAdaptor;
import org.opengroupware.jope.eoaccess.EOAdaptorChannel;
import org.opengroupware.jope.eoaccess.EOAttribute;
import org.opengroupware.jope.eoaccess.EOEntity;
import org.opengroupware.jope.eoaccess.EOSQLExpression;

public class EOPostgreSQLChannel extends EOAdaptorChannel {

  public EOPostgreSQLChannel(EOAdaptor _adaptor, Connection _c) {
    super(_adaptor, _c);
  }

  /* reflection */
  
  public String[] describeTableNames() {
    return this.fetchSingleStringRows(tableNameQuery, null /* first column */);
  }
  
  public String[] describeDatabaseNames() {
    return this.fetchSingleStringRows(dbNameQuery, null /* first column */);
  }

  public EOEntity describeEntityWithTableName(String _tableName) {
    if (_tableName == null) return null;

    List<Map<String,Object>> columnInfos =
      this._fetchPGColumnsOfTable(_tableName);
    String[] pkeyNames =
      this._fetchPGPrimaryKeyNamesOfTable(_tableName);
    
    if (columnInfos == null) /* error */
      return null;
    
    EOAttribute[] attributes = this.attributesFromColumnInfos(columnInfos);
    
    return new EOEntity
      (this.entityNameForTableName(_tableName),
       _tableName, false /* not a pattern */,
       null /* classname */, null /* datasource classname */,
       attributes,
       this.attributeNamesFromColumnNames(pkeyNames, attributes),
       null /* fetch specifications */,
       null /* adaptor operations */);
  }
  
  /* attributes */
  
  protected String[] attributeNamesFromColumnNames
    (String[] _colnames, EOAttribute[] _attrs)
  {
    if (_colnames == null || _attrs == null) return null;
    
    String[] attrNames = new String[_colnames.length];
    for (int i = 0; i < attrNames.length; i++) {
      for (int j = 0; j < _attrs.length; j++) {
        if (_colnames[i].equals(_attrs[j].columnName())) {
          attrNames[i] = _attrs[j].name();
          break;
        }
      }
    }
    return attrNames;
  }
  
  protected EOAttribute[] attributesFromColumnInfos
    (List<Map<String,Object>> _columnInfos)
  {
    // map: a.attnum, a.attname, t.typname, a.attlen, a.attnotnull "
    if (_columnInfos == null) return null;

    int count = _columnInfos.size();
    EOAttribute[] attributes = new EOAttribute[count];

    for (int i = 0; i < count; i++) {
      Map<String,Object> colinfo = _columnInfos.get(i);
      String colname = (String)colinfo.get("colname");
      String exttype = (String)colinfo.get("exttype");
      
      exttype = exttype.toUpperCase();
      
      // TODO: complete information
      attributes[i] = new EOAttribute
        (this.attributeNameForColumnName(colname),
         colname, false /* not a pattern */,
         exttype,
         null,  // TODO: auto-increment
         null,  // TODO: not-null
         null,  // TODO: width
         null /* default    */,
         null /* Comment    */,
         null /* Collation  */,
         null /* privileges */);
    }
    
    return attributes;
  }

  /* PostgreSQL reflection */
  
  protected List<Map<String,Object>> _fetchPGColumnsOfTable(String _table) {
    if (_table == null) return null;
    
    String sql = columnBaseQuery + " AND c.relname='" +  _table +
      "' ORDER BY attnum;";
    return this.performSQL(sql);
  }
  
  protected String[] _fetchPGPrimaryKeyNamesOfTable(String _table) {
    if (_table == null) return null;
    
    String sql = pkeyBaseQuery.replace("$PKEY_TABLE_NAME$", _table);
    
    List<Map<String,Object>> pkeyRecords = this.performSQL(sql);
    if (pkeyRecords == null) return null;
    
    /* extract column name */
    String[] pkeys = new String[pkeyRecords.size()];
    for (int i = 0; i < pkeyRecords.size(); i++)
      pkeys[i] = (String)(pkeyRecords.get(i).get("pkey"));
    return pkeys;
  }
  
  /* sequences */
  
  public Integer nextNumberInSequence(String _sequence) {
    // SQL: SELECT NEXTVAL('key_generator')
    EOSQLExpression e = this.adaptor.expressionFactory().createExpression(null);
    
    StringBuffer sql = new StringBuffer(32);
    sql.append("SELECT NEXTVAL(");
    sql.append(e.sqlStringForSchemaObjectName(_sequence));
    sql.append(")");
    
    /* acquire DB resources */
    
    Statement  stmt = this._createStatement();
    if (stmt == null) return -1;
    
    int nextNumber = -1;
    ResultSet rs = null;
    try {
      rs = stmt.executeQuery(sql.toString());
      if (rs.next())
        nextNumber = rs.getInt(1);
      else
        log.error("could not retrieve PostgreSQL sequence value: " + _sequence);
    }
    catch (SQLException ex) {
      log.error("could not increase PostgreSQL sequence", ex);
    }
    finally {
      this._releaseResources(stmt, rs);
    }
    
    return nextNumber;
  }
  
  /* queries */
  
  protected static final String tableNameQuery =
    "SELECT relname FROM pg_class WHERE " +
    "(relkind='r') AND (relname !~ '^pg_') AND (relname !~ '^xinv[0-9]+') " +
    "ORDER BY relname";
  
  protected static final String dbNameQuery =
    "SELECT datname FROM pg_database ORDER BY datname";
  
  protected static final String columnBaseQuery =
      "SELECT a.attnum, a.attname AS colname, t.typname AS exttype, " +
      "a.attlen, a.attnotnull " +
      "FROM pg_class c, pg_attribute a, pg_type t " +
      "WHERE (a.attnum > 0 AND a.attrelid = c.oid AND a.atttypid = t.oid)";
  
  protected static final String pkeyBaseQuery = 
     "SELECT attname AS pkey FROM pg_attribute WHERE " +
     "attrelid IN (" +
     "SELECT a.indexrelid FROM pg_index a, pg_class b WHERE " + 
     "a.indexrelid = b.oid AND a.indisprimary AND b.relname IN (" +
     "SELECT indexname FROM pg_indexes WHERE " + 
     "tablename = '$PKEY_TABLE_NAME$'" +
     ")" +
     ")";
  
}