/*
 *  BaseLink - Generic object relational mapping
 *  Copyright (C) 2024  Ulrich Hilger, http://uhilger.de
 *
 *  This program is free software: you can redistribute it and/or modify
 *  it under the terms of the GNU General Public License as published by
 *  the Free Software Foundation, either version 3 of the License, or
 *  (at your option) any later version.
 *
 *  This program is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *  GNU General Public License for more details.
 *
 *  You should have received a copy of the GNU General Public License
 *  along with this program.  If not, see http://www.gnu.org/licenses/
 */
package de.uhilger.baselink;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 * Methoden zum Ausfuehren von SQL-Skripten
 * 
 * @author Copyright (c) Ulrich Hilger, <a href="http://uhilger.de">http://uhilger.de</a>
 * @author Published under the terms and conditions of
 * the <a href="http://www.gnu.org/licenses/" target="_blank">GNU General Public License</a>
 * @version 1, January 22, 2024
 */
public class Script extends DBActor {
  private static final Logger logger = Logger.getLogger(Script.class.getName());
  
  public Script(PersistenceManager pm) {
    this.pm = pm;
  }
  /**
   * Execute an SQL statement and return keys generated in the database
   * @param sql  the statement to execute
   * @return  a list of generated keys
   */
  public List<Map<String, Object>> executeWithKeys(String sql) {
    List<Map<String, Object>> keys = null;
    Connection c = null;
    try {
      c = pm.getConnection();
      keys = executeWithKeys(c, sql);
      c.close();
      c = null;
    } catch (SQLException ex) {
      logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
    } finally {
      pm.closeConnectionFinally(c);
    }
    return keys;
  }

  /**
   * Execute an SQL statement and return keys generated in the database
   * @param c  database connection to use
   * @param sql  the statement to execute
   * @return  a list of generated keys
   */
  public List<Map<String, Object>> executeWithKeys(Connection c, String sql) {
    List<Map<String, Object>> keys = null;
    Statement s = null;
    try {
      s = c.createStatement();
      s.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
      keys = pm.toList(s.getGeneratedKeys());
      s.close();
      s = null;
    } catch (SQLException ex) {
      logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
    } finally {
      pm.closeStatementFinally(s);
    }
    return keys;
  }

  /**
   * Execute an SQL statement
   * @param sql  the statement to execute
   * @return  the number of records affected by this statement or -1 if none
   */
  public int execute(String sql) {
    int numRows = -1;
    Connection c = null;
    try {
      c = pm.getConnection();
      numRows = execute(c, sql);
      c.close();
      c = null;
    } catch (SQLException ex) {
      logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
    } finally {
      pm.closeConnectionFinally(c);
    }
    return numRows;
  }

  /**
   * Execute an SQL statement
   * @param c  database connection to use
   * @param sql  the statement to execute
   * @return  the number of records affected by this statement or -1 if none
   */
  public int execute(Connection c, String sql) {
    int numRows = -1;
    Statement s = null;
    try {
      s = c.createStatement();
      numRows = s.executeUpdate(sql);
      s.close();
      s = null;
    } catch (SQLException ex) {
      logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
    } finally {
      pm.closeStatementFinally(s);
    }
    return numRows;
  }

  /**
   * Execute an SQL statement
   * @param sql the SQL string with ? at the position of params
   * @param params list of parameters in the order they appear in the SQL string
   * @return  the number of records affected by this statement or -1 if none
   */
  public int execute(String sql, Object... params) {
    int numRows = -1;
    Connection c = null;
    try {
      c = pm.getConnection();
      numRows = execute(c, sql, params);
      c.close();
      c = null;
    } catch (SQLException ex) {
      logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
    } finally {
      pm.closeConnectionFinally(c);
    }
    return numRows;
  }

  /**
   * Execute an SQL statement
   * @param c  database connection to use
   * @param sql the SQL string with ? at the position of params
   * @param params list of parameters in the order they appear in the SQL string
   * @return  the number of records affected by this statement or -1 if none
   */
  public int execute(Connection c, String sql, Object... params) {
    int numRows = -1;
    Statement s = null;
    try {
      //s = c.createStatement();
      s = pm.buildQuery(c, sql, params);
      if(s != null && s instanceof PreparedStatement) {
        try (PreparedStatement ps = (PreparedStatement) s) {
          numRows = ps.executeUpdate();
        }
      }
      s = null;
    } catch (Exception ex) {
      logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
    } finally {
      pm.closeStatementFinally(s);
    }
    return numRows;
  }
  
  /**
   * Execute an SQL script
   * @param sqlScript  the SQL script to execute
   * @return an array of row counts referring to the number of affected rows of 
   * each sql statement in the script in the order of SQL commands in the script 
   * Statement.EXECUTE_FAILED indicates failure of single steps in the script 
   * Statement.SUCCESS_NO_INFO indicates successful execution without information 
   * about the number of affected rows
   */
  public int[] executeScript(String sqlScript) {
    int[] ergebnisse = null;
    Connection c = null;
    Statement s = null;
    try {
      c = pm.getConnection();
      s = c.createStatement();
      String[] sqlKommandos = sqlScript.split(";");
      for (String sqlKommando : sqlKommandos) {
        s.addBatch(sqlKommando);
      }
      ergebnisse = s.executeBatch();
    } catch(SQLException ex) {
      logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
    } finally {
      pm.closeStatementFinally(s);
      pm.closeConnectionFinally(c);
    }
    return ergebnisse;
  }
  
  /**
   * Execute an SQL script
   * @param c  the Connection object to use
   * @param sqlScript  the SQL script to execute
   * @return an array of row counts referring to the number of affected rows of 
   * each sql statement in the script in the order of SQL commands in the script 
   * Statement.EXECUTE_FAILED indicates failure of single steps in the script 
   * Statement.SUCCESS_NO_INFO indicates successful execution without information 
   * about the number of affected rows
   */
  public int[] executeScript(Connection c, String sqlScript) {
    int[] ergebnisse = null;
    Statement s = null;
    try {
      s = c.createStatement();
      String[] sqlKommandos = sqlScript.split(";");
      for (String sqlKommando : sqlKommandos) {
        s.addBatch(sqlKommando);
      }
      ergebnisse = s.executeBatch();
    } catch(SQLException ex) {
      logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
    } finally {
      pm.closeStatementFinally(s);
    }
    return ergebnisse;
  }
    
}