/**
 * Copyright (C) 2009 - present by OpenGamma Inc. and the OpenGamma group of companies
 *
 * Please see distribution for license.
 */
package com.opengamma.util.test;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collections;
import java.util.List;

import org.hibernate.dialect.Dialect;
import org.hibernate.dialect.PostgreSQLDialect;
import org.hibernate.mapping.Table;
import org.testng.collections.Lists;

import com.opengamma.OpenGammaRuntimeException;

/**
 * Database management for Postgres databases.
 */
public final class PostgresDbManagement extends AbstractDbManagement {

  /**
   * SQL to retrieve all the columns.
   */
  private static final String GET_ALL_COLUMNS_SQL =
    "SELECT column_name AS name,data_type AS datatype,is_nullable AS allowsnull,column_default AS defaultvalue FROM information_schema.columns WHERE table_catalog='";
  /**
   * The Postgres default schema.
   */
  private static final String POSTGRES_DEFAULT_SCHEMA = "public";
  /**
   * Singleton instance.
   */
  private static final PostgresDbManagement INSTANCE = new PostgresDbManagement();

  /**
   * The underlying Hibernate dialect.
   */
  private PostgreSQLDialect _hibernateDialect;

  /**
   * Restricted constructor.
   */
  private PostgresDbManagement() {
  }

  /**
   * Gets the singleton instance.
   * 
   * @return the instance, not null
   */
  public static PostgresDbManagement getInstance() {
    return INSTANCE;
  }

  //-------------------------------------------------------------------------
  @Override
  public synchronized Dialect getHibernateDialect() {
    if (_hibernateDialect == null) {
      // constructed lazily so we don't get log message about 'using dialect' if we're not actually using it
      _hibernateDialect = new PostgreSQLDialect();
    }
    return _hibernateDialect;
  }

  @Override
  public Class<?> getJDBCDriverClass() {
    return org.postgresql.Driver.class;
  }

  @Override
  public String getDatabaseName() {
    return "postgres";
  }

  //-------------------------------------------------------------------------
  @Override
  public String getAllSchemasSQL(String catalog) {
    return "SELECT nspname AS name from pg_namespace";
  }

  @Override
  public String getAllForeignKeyConstraintsSQL(String catalog, String schema) {
    if (schema == null) {
      schema = POSTGRES_DEFAULT_SCHEMA;
    }
    String sql = "SELECT constraint_name AS name, table_name FROM information_schema.table_constraints WHERE " +
      "constraint_catalog = '" + catalog + "' AND constraint_schema = '" + schema + "'" + " AND constraint_type = 'FOREIGN KEY'";
    return sql;
  }

  @Override
  public String getAllSequencesSQL(String catalog, String schema) {
    if (schema == null) {
      schema = POSTGRES_DEFAULT_SCHEMA;
    }
    String sql = "SELECT sequence_name AS name FROM information_schema.sequences WHERE " +
      "sequence_catalog = '" + catalog + "'" + " AND sequence_schema = '" + schema + "'";
    return sql;
  }

  @Override
  public String getAllTablesSQL(String catalog, String schema) {
    if (schema == null) {
      schema = POSTGRES_DEFAULT_SCHEMA;
    }
    String sql = "SELECT table_name AS name FROM information_schema.tables WHERE " +
      "table_catalog = '" + catalog + "'" + " AND table_schema = '" + schema + "' AND table_type = 'BASE TABLE'";
    return sql;
  }

  @Override
  public String getAllViewsSQL(String catalog, String schema) {
    if (schema == null) {
      schema = POSTGRES_DEFAULT_SCHEMA;
    }
    String sql = "SELECT table_name AS name FROM information_schema.tables WHERE " +
      "table_catalog = '" + catalog + "'" + " AND table_schema = '" + schema + "' AND table_type = 'VIEW'";
    return sql;
  }

  @Override
  public String getAllColumnsSQL(String catalog, String schema, String table) {
    if (schema == null) {
      schema = POSTGRES_DEFAULT_SCHEMA;
    }
    StringBuilder sql = new StringBuilder(GET_ALL_COLUMNS_SQL);
    sql.append(catalog).append("' AND table_schema='").append(schema).append("' AND table_name='");
    sql.append(table).append("'");
    return sql.toString();
  }

  @Override
  public String getCreateSchemaSQL(String catalog, String schema) {
    return "CREATE SCHEMA " + schema;
  }

  @Override
  public CatalogCreationStrategy getCatalogCreationStrategy() {
    return new SQLCatalogCreationStrategy(
        getDbHost(), 
        getUser(), 
        getPassword(), 
        "SELECT datname AS name FROM pg_database",
        "template1");
  }

  @Override
  public void dropSchema(String catalog, String schema) {
    if (schema != null) {
      super.dropSchema(catalog, schema);
    } else {
      try {
        Connection conn = connect(catalog);
        //setActiveSchema(conn, schema);
        Statement statement = conn.createStatement();
        //TODO default schema
        statement.executeUpdate("DROP SCHEMA IF EXISTS public CASCADE;CREATE SCHEMA public;");
        statement.close();
        conn.close();
      } catch (SQLException se) {
        throw new OpenGammaRuntimeException("Failed to drop the default schema", se);
      }
    }
  }
  
  @Override
  protected List<String> getClearTablesCommand(String schema, List<String> tablesToClear) {
    {
      if (tablesToClear.isEmpty()) {
        return Lists.newArrayList();
      }
      StringBuffer buffer = new StringBuffer("TRUNCATE TABLE ");

      for (String name : tablesToClear) {
        Table table = new Table(name);
        String qualifiedName = table.getQualifiedName(getHibernateDialect(), null, schema);
        buffer.append(qualifiedName);
        buffer.append(",");
      }
      buffer.setCharAt(buffer.length() - 1, ';');
      return Lists.newArrayList(Collections.singleton(buffer.toString()));
    }
  }
}

@@ -253,61 +253,8 @@
 ent;
-%0Aimport java.util.Collections;%0Aimport java.util.List;
 %0A%0Aim
@@ -340,81 +340,8 @@
 ect;
-%0Aimport org.hibernate.mapping.Table;%0Aimport org.testng.collections.Lists;
 %0A%0Aim
@@ -4624,49 +4624,8 @@
 g);%0A
-        //setActiveSchema(conn, schema);%0A
     
@@ -4990,649 +4990,6 @@
   %7D%0A
-  %0A  @Override%0A  protected List%3CString%3E getClearTablesCommand(String schema, List%3CString%3E tablesToClear) %7B%0A    %7B%0A      if (tablesToClear.isEmpty()) %7B%0A        return Lists.newArrayList();%0A      %7D%0A      StringBuffer buffer = new StringBuffer(%22TRUNCATE TABLE %22);%0A%0A      for (String name : tablesToClear) %7B%0A        Table table = new Table(name);%0A        String qualifiedName = table.getQualifiedName(getHibernateDialect(), null, schema);%0A        buffer.append(qualifiedName);%0A        buffer.append(%22,%22);%0A      %7D%0A      buffer.setCharAt(buffer.length() - 1, ';');%0A      return Lists.newArrayList(Collections.singleton(buffer.toString()));%0A    %7D%0A  %7D%0A
 %7D%0A

TRUNCATE is slower for nearly empty tables