package basics.utl;

import java.io.File;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import basics.filesystem.file.LineReader;
import basics.unexpected.Problem;

public class JdbUtl
{
private JdbUtl()
{
}

public static void script(Connection c, File script) throws Problem
{
   LineReader rl = new LineReader(script);
   if(!rl.open())
   {
      throw new Problem("Script not found: " + script.getAbsolutePath());
   }
   String sql = "";
   try
   {
      Statement statement = c.createStatement();
      while(rl.hasLine())
      {
         sql = rl.nextLineTrimNotNull();
         // try
         // {
         if(!Empty.is(sql) && !sql.startsWith("#"))
            statement.execute(sql);
         // }
         // catch (Exception e)
         // {
         // OsUtl.trace("Failure processing " + script.getName() + ", statement:
         // '" +
         // sql + "'. " + e.getMessage());
         // }
      }
      rl.close();
      c.commit();
   }
   catch(Exception e)
   {
      throw new Problem("Failure processing " + script.getName() + ", statement: '" + sql + "'. "
         + e.getMessage());
   }
   finally
   {
      try
      {
         c.rollback();
      }
      catch(Exception e)
      {
         throw new Problem(e);
      }
      try
      {
         rl.close();
      }
      catch(Exception e)
      {
         throw new Problem(e);
      }
   }
}

public static int executeUpdate(Connection c, String sql) throws SQLException
{
   int result = 0;
   if(c != null)
   {
      Statement statement = c.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
         ResultSet.CONCUR_UPDATABLE);
      // OsUtl.trace(sql);
      result = statement.executeUpdate(sql);
      statement.close();
      // OsUtl.trace(" -----> "+ result);
   }
   return result;
}

public static ResultSet createReadOnlyResultSet(Connection c, String sql)
{
   if(c != null)
   {
      try
      {
         Statement statement = c.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
            ResultSet.CONCUR_READ_ONLY);
         if(statement == null)
         {
            return null;
         }
         ResultSet rs = statement.executeQuery(sql);
         if(rs != null)
            return rs;
      }
      catch(Exception e)
      {
      }
   }
   return null;
}

public static ResultSet createResultSet(Connection c, String sql)
{
   if(c != null)
   {
      try
      {
         Statement statement = c.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
            ResultSet.CONCUR_UPDATABLE);
         if(statement == null)
         {
            return null;
         }
         ResultSet rs = statement.executeQuery(sql);
         return rs;
      }
      catch(Exception e)
      {
      }
   }
   return null;
}

public static boolean existsTable(Connection c, String table)
{
   String sql = "show tables like '" + table + "'";
   try
   {
      return createReadOnlyResultSet(c, sql).next();
   }
   catch(SQLException e)
   {
      return false;
   }
}

public static boolean emptyTable(Connection c, String table)
{
   if(JdbUtl.existsTable(c, table))
   {
      String sql = "delete from " + table;
      try
      {
         JdbUtl.executeUpdate(c, sql);
      }
      catch(Throwable e)
      {
         return false;
      }
   }
   return true;
}

public static boolean dropTable(Connection c, String table)
{
   String sql = "drop table " + table;
   if(JdbUtl.existsTable(c, table))
   {
      try
      {
         return JdbUtl.executeUpdate(c, sql) > -1;
      }
      catch(Throwable e)
      {
         return false;
      }
   }
   return true;
}

public static double domFunction(Connection c, String table, String function, String field,
   String whereClause, double defaultValue) throws Problem
{
   double result = defaultValue;
   StringBuilder sql = new StringBuilder();
   sql.append("select ");
   sql.append(function);
   sql.append("(");
   sql.append(field);
   sql.append(") as dfvalue ");
   sql.append(" from ");
   sql.append(table);
   sql.append(" ");
   if(!Empty.is(whereClause))
   {
      sql.append(" where ");
      sql.append(whereClause);
   }
   // OsUtl.trace(sql);
   if(c != null)
   {
      try
      {
         ResultSet r = createReadOnlyResultSet(c, sql.toString());
         if(r.next())
            result = r.getDouble("dfvalue");
         r.close();
      }
      catch(SQLException w)
      {
         throw new Problem(w.getMessage());
      }
   }
   return result;
}
}