package com.frequentis.fms.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.junit.*;
import static org.junit.Assert.*;
import org.postgresql.PGStatement;


public class RenameSchemaSearchPathIssueTest
{
   private static final String URL = "jdbc:postgresql://localhost:5432/test";
   private static final String USER = "test";
   private static final String PASSWORD = "test";
   private static final String SCHEMA_A = "test_a";
   private static final String SCHEMA_B = "test_b";
   private static final String TABLE = "ttest";

   private Connection conn = null;
   private PreparedStatement deleteStatement = null;


   @Before
   public void setUp() throws SQLException
   {
      setupDatabase();
      this.conn = getConnection();
      this.deleteStatement = this.conn.prepareStatement("DELETE FROM " + TABLE + " WHERE test_id = ?");
   }


   @After
   public void tearDown() throws SQLException
   {
      if (this.conn != null)
      {
         try (final Statement stmt = conn.createStatement())
         {
            stmt.executeUpdate("DROP SCHEMA IF EXISTS " + SCHEMA_A + " CASCADE");
            stmt.executeUpdate("DROP SCHEMA IF EXISTS " + SCHEMA_B + " CASCADE");
         }
         catch (final SQLException ex)
         {
            ex.printStackTrace();
         }

         try
         {
            this.conn.close();
         }
         finally
         {
            this.deleteStatement = null;
            this.conn = null;
         }
      }
   }


   @Test
   public void testDoNotExceedThreshold() throws SQLException
   {
      final int threshold = 5;
      final int numRows = 10;
      final int numDeleteBeforeCopy = 1;
      final int numDeleteAfterCopy = 1;

      doTestRenameSchemaSearchPathIssue(threshold, numRows, numDeleteBeforeCopy, numDeleteAfterCopy);
   }


   @Test
   public void testDoNotExceedThresholdAndDeleteOnlyBeforeCopy() throws SQLException
   {
      final int threshold = 5;
      final int numRows = 10;
      final int numDeleteBeforeCopy = 1;
      final int numDeleteAfterCopy = 0;

      doTestRenameSchemaSearchPathIssue(threshold, numRows, numDeleteBeforeCopy, numDeleteAfterCopy);
   }


   @Test
   public void testDoNotExceedThresholdAndDeleteOnlyAfterCopy() throws SQLException
   {
      final int threshold = 5;
      final int numRows = 10;
      final int numDeleteBeforeCopy = 0;
      final int numDeleteAfterCopy = 1;

      doTestRenameSchemaSearchPathIssue(threshold, numRows, numDeleteBeforeCopy, numDeleteAfterCopy);
   }


   @Test
   public void testExceedThresholdBeforeCopy() throws SQLException
   {
      final int threshold = 5;
      final int numRows = 10;
      final int numDeleteBeforeCopy = 6;
      final int numDeleteAfterCopy = 2;

      doTestRenameSchemaSearchPathIssue(threshold, numRows, numDeleteBeforeCopy, numDeleteAfterCopy);
   }


   @Test
   public void testExceedThresholdAfterCopy() throws SQLException
   {
      final int threshold = 5;
      final int numRows = 10;
      final int numDeleteBeforeCopy = 4;
      final int numDeleteAfterCopy = 5;

      doTestRenameSchemaSearchPathIssue(threshold, numRows, numDeleteBeforeCopy, numDeleteAfterCopy);
   }


   @Test
   public void testServerReuseDisabled() throws SQLException
   {
      final int threshold = 0;
      final int numRows = 10;
      final int numDeleteBeforeCopy = 5;
      final int numDeleteAfterCopy = 5;

      doTestRenameSchemaSearchPathIssue(threshold, numRows, numDeleteBeforeCopy, numDeleteAfterCopy);
   }


   private void doTestRenameSchemaSearchPathIssue
         ( final int threshold
         , final int numRows
         , final int numDeleteBeforeCopy
         , final int numDeleteAfterCopy
         )
         throws SQLException
   {
      this.conn.setSchema(SCHEMA_A);
      insertRows(this.conn, numRows);
      assertEquals
            ( "Initial number of rows in test table of schema A"
            , numRows
            , getRowCount(this.conn, SCHEMA_A + '.' + TABLE)
            );

      setPrepareThreshold(this.deleteStatement, threshold);
      assertFalse
            ( "Initial isUseServerPrepare()"
            , isUseServerPrepare(this.deleteStatement)
            );

      this.conn.setSchema(SCHEMA_A);
      deleteRows(this.deleteStatement, 1, numDeleteBeforeCopy);
      assertEquals
            ( "isUseServerPrepare() before copy"
            , threshold > 0 && numDeleteBeforeCopy >= threshold - 1
            , isUseServerPrepare(deleteStatement)
            );
      assertEquals
            ( "Number of rows in test table of schema B before copy"
            , numRows - numDeleteBeforeCopy
            , getRowCount(this.conn, SCHEMA_A + '.' + TABLE)
            );

      simulateExternalCopySchema();
      assertEquals
            ( "Number of rows in test table of schema A after copy"
            , numRows - numDeleteBeforeCopy
            , getRowCount(this.conn, SCHEMA_A + '.' + TABLE)
            );
      assertEquals
            ( "Number of rows in test table of schema B after copy"
            , numRows - numDeleteBeforeCopy
            , getRowCount(this.conn, SCHEMA_B + '.' + TABLE)
            );

      this.conn.setSchema(SCHEMA_A);
      deleteRows(this.deleteStatement, 1 + numDeleteBeforeCopy, numDeleteAfterCopy);
      assertEquals
            ( "isUseServerPrepare() after copy"
            , threshold > 0 && numDeleteBeforeCopy + numDeleteAfterCopy >= threshold - 1
            , isUseServerPrepare(deleteStatement)
            );
      assertEquals
            ( "Final number of rows in test table of schema A"
            , numRows - numDeleteBeforeCopy - numDeleteAfterCopy
            , getRowCount(this.conn, SCHEMA_A + '.' + TABLE)
            );
      assertEquals
            ( "Final number of rows in test table of schema B"
            , numRows - numDeleteBeforeCopy
            , getRowCount(this.conn, SCHEMA_B + '.' + TABLE)
            );
   }


   private static Connection getConnection() throws SQLException
   {
      return DriverManager.getConnection(URL, USER, PASSWORD);
   }


   private static void setPrepareThreshold(final Statement stmt, final int threshold) throws SQLException
   {
      stmt.unwrap(PGStatement.class).setPrepareThreshold(threshold);
   }


   private boolean isUseServerPrepare(final Statement stmt) throws SQLException
   {
      return stmt.unwrap(PGStatement.class).isUseServerPrepare();
   }


   private static void setupDatabase() throws SQLException
   {
      try (final Connection conn = getConnection();
            final Statement stmt = conn.createStatement())
      {
         stmt.executeUpdate("DROP SCHEMA IF EXISTS " + SCHEMA_A + " CASCADE");
         stmt.executeUpdate("CREATE SCHEMA " + SCHEMA_A);
         stmt.executeUpdate("CREATE TABLE " + SCHEMA_A + '.' + TABLE + " (test_id integer)");
      }
   }


   private static void insertRows(final Connection conn, final int count) throws SQLException
   {
      try (final PreparedStatement pstmt = conn.prepareStatement("INSERT INTO " + TABLE + " VALUES (?)"))
      {
         for (int i = 1; i <= count; ++i)
         {
            pstmt.setInt(1, i);
            pstmt.executeUpdate();
         }
      }
   }


   /**
    * Inline simulation of "pg_dump --schema" -> "ALTER SCHEMA RENAME" -> "psql --file"
    */
   private static void simulateExternalCopySchema() throws SQLException
   {
      try (final Connection conn = getConnection();
            final Statement stmt = conn.createStatement())
      {
         stmt.executeUpdate("DROP SCHEMA IF EXISTS " + SCHEMA_B + " CASCADE");
         stmt.executeUpdate("ALTER SCHEMA " + SCHEMA_A + " RENAME TO " + SCHEMA_B);
         stmt.executeUpdate("CREATE SCHEMA " + SCHEMA_A);
         stmt.executeUpdate("SELECT * INTO " + SCHEMA_A + '.' + TABLE + " FROM " + SCHEMA_B + '.' + TABLE);
      }
   }


   private static void deleteRows(final PreparedStatement pstmt, final int start, final int count) throws SQLException
   {
      for (int i = 0; i < count; ++i)
      {
         pstmt.setInt(1, start + i);
         pstmt.executeUpdate();
      }
   }


   private static int getRowCount(final Connection conn, final String table) throws SQLException
   {
      try (final Statement stmt = conn.createStatement();
            final ResultSet rs = stmt.executeQuery("SELECT count(*) FROM " + table))
      {
         if (rs.next())
         {
            return rs.getInt(1);
         }
         else
         {
            return -1;
         }
      }
   }

}