package org.openbravo.test.system;

import static org.hamcrest.Matchers.allOf;
import static org.hamcrest.Matchers.containsString;
import static org.hamcrest.Matchers.is;
import static org.hamcrest.Matchers.nullValue;
import static org.junit.Assert.assertThat;
import static org.junit.Assume.assumeThat;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

import org.junit.After;
import org.junit.Before;
import org.junit.Rule;
import org.junit.Test;
import org.junit.rules.ExpectedException;
import org.openbravo.data.UtilSql;
import org.openbravo.database.ConnectionProvider;
import org.openbravo.service.db.CallStoredProcedure;
import org.openbravo.service.db.DalConnectionProvider;
import org.openbravo.test.base.OBBaseTest;
import org.postgresql.util.PGobject;

public class StoredProcedure extends OBBaseTest {

  @Rule
  public ExpectedException thrownExeception = ExpectedException.none();

  @Before
  public void init() throws Exception {
    String pl;
    if (new DalConnectionProvider(false).getRDBMS().equals("POSTGRE")) {
      pl = " CREATE OR REPLACE FUNCTION test(  \n" //
          + "     IN  i character varying,  \n" //
          + "     OUT a character varying,  \n" //
          + "     OUT b character varying)  \n" //
          + "   RETURNS record AS  \n" //
          + " $BODY$ DECLARE   \n" //
          + " BEGIN  \n" //
          + "  a := i||'_a';  \n" //
          + "  b := i||'_b';  \n" //
          + " END ; $BODY$  \n" //
          + "   LANGUAGE plpgsql VOLATILE  \n";
    } else {
      pl = "create or replace PROCEDURE test(i in varchar2, a OUT VARCHAR2, b OUT VARCHAR2) \n"//
          + " AS  \n" //
          + " BEGIN \n" //
          + "   a:=i||'_a'; \n" //
          + "   b:=i||'_b'; \n" //
          + " END test;";
    }

    ConnectionProvider cp = new DalConnectionProvider();
    PreparedStatement ps = cp.getPreparedStatement(pl);
    ps.execute();
    ps.close();
  }

  @Test
  public void outputParamsAsProcedure() throws Exception {
    String sql = "{ call test(?, ? , ?) }";
    ConnectionProvider cp = new DalConnectionProvider();
    CallableStatement cs = cp.getCallableStatement(sql);
    cs.setString(1, "value");
    cs.registerOutParameter(2, Types.VARCHAR);
    cs.registerOutParameter(3, Types.VARCHAR);
    cs.execute();
    System.out.println(UtilSql.getStringCallableStatement(cs, 2));
    System.out.println(UtilSql.getStringCallableStatement(cs, 3));
    cs.close();
  }

  @Test
  public void outputParamsAsFunctionPG() {
    assumeThat(new DalConnectionProvider(false).getRDBMS(), is("POSTGRE"));

    List<Object> params = new ArrayList<Object>();
    params.add("value");

    boolean returnObject = true;
    Object output = CallStoredProcedure.getInstance()
        .call("test", params, null, true, returnObject);

    assertThat(output.getClass().getName(), is(org.postgresql.util.PGobject.class.getName()));

    PGobject pgObj = (PGobject) output; // specific PG type
    assertThat(pgObj.getType(), is("record"));
    // value can only be obtained as String, it needs to be parsed
    assertThat(pgObj.getValue(), is("(value_a,value_b)"));
  }

  @Test
  public void outputParamsAsProcedureOracle() {
    assumeThat(new DalConnectionProvider(false).getRDBMS(), is("ORACLE"));

    List<Object> params = new ArrayList<Object>();
    params.add("value");

    // all params are needed in Oracle
    params.add(null);
    params.add(null);

    boolean returnObject = false;

    Object output = CallStoredProcedure.getInstance()
        .call("test", params, null, true, returnObject);

    assertThat(output, is(nullValue()));
    // output is null: can't get output fields with this approach
  }

  @Test
  public void outputParamsAsFunctionOracle() {
    assumeThat(new DalConnectionProvider(false).getRDBMS(), is("ORACLE"));

    List<Object> params = new ArrayList<Object>();
    params.add("value");

    // all params are needed in Oracle
    params.add(null);
    params.add(null);

    thrownExeception.expect(IllegalStateException.class);
    thrownExeception.expectMessage(allOf(containsString("ORA-00904"),
        containsString("invalid identifier")));

    // Oracle can't invoke a procedure as a function: ORA-00904: "TEST": invalid identifier
    boolean returnObject = true;
    CallStoredProcedure.getInstance().call("test", params, null, true, returnObject);
  }

  @After
  public void dropTestFunction() throws Exception {
    String pl;
    if (new DalConnectionProvider(false).getRDBMS().equals("POSTGRE")) {
      pl = "DROP FUNCTION test(character varying)";
    } else {
      pl = "drop procedure TEST";
    }

    ConnectionProvider cp = new DalConnectionProvider();
    PreparedStatement ps = cp.getPreparedStatement(pl);
    ps.execute();
    ps.close();
  }
}