/*
 * Copyright (c) 2000-2007 MetaMatrix, Inc.
 * All rights reserved.
 * Modified by
 * @author  Data Virtuality GmbH
 * @date    $Date$
 */
package org.teiid.test.testcases;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.concurrent.TimeoutException;

import org.junit.jupiter.api.Assertions;
import org.junit.jupiter.api.Test;
import org.teiid.jdbc.AbstractQueryTest;
import org.teiid.jdbc.TeiidSQLException;
import org.teiid.test.framework.query.AbstractQueryTransactionTest;
import org.teiid.test.framework.query.QueryExecution;


/** 
 * A common test case among many different transaction stuff. 
 */
@SuppressWarnings("nls")
public abstract class CommonTransactionTests extends BaseAbstractTransactionTestCase {
    
     
//    void runConcurrentTestCases(int howMany, final String[] sqls) {
//        
//        SeparateClient[] clients = new SeparateClient[howMany]; 
//                       
//        for(int i = 0; i < howMany; i++) {
//            AbstractQueryTransactionTest testCase = new AbstractQueryTransactionTest() {
//                public void testCase() throws Exception {
//                    execute(sqls);
//                }
//            };            
//            clients[i] = new SeparateClient(getTransactionContainter(), testCase);
//        }
//        
//        for(int i = 0; i < howMany; i++) {
//            clients[i].start();
//        }
//
//        try {
//            for(int i = 0; i < howMany; i++) {
//                clients[i].join();
//            }
//        } catch (InterruptedException e) {
//            // boo
//        }        
//    }
    
//    static class SeparateClient extends Thread{
//        TransactionContainer container = null;
//        AbstractTransactionTestCase testCase = null;
//        
//        public SeparateClient(TransactionContainer container, AbstractTransactionTestCase testCase) {
//            this.container = container;
//            this.testCase = testCase;
//        }
//
//        public void run() {
//            this.container.runTransaction(this.testCase);
//        }
//    }
    
    ///////////////////////////////////////////////////////////////////////////////////////////////
    //  Single Source - Rows below 500 (for insert/update/delete)
    ///////////////////////////////////////////////////////////////////////////////////////////////
    
    /**
     * Sources = 1
     * Commands = 1, Select
     * Batching = Full Processing, Single Connector Batch
     * result = commit 
     */
    @Test
    public void testSingleSourceSelect() throws Exception {
        AbstractQueryTransactionTest userTxn = new AbstractQueryTransactionTest("testSingleSourceSelect") {
            public void testCase() throws Exception {
                execute("select * from pm1.g1 where pm1.g1.e1 < 100");
                assertRowCount(100);
            }
        };        
        
        // run test
        getTransactionContainter().runTransaction(userTxn);
        
        // there is nothing to verify here..
    }
    
    
    /**
     * Sources = 1
     * Commands = 1, Update
     * Batching = Full Processing, Single Connector Batch
     * result = commit 
     */
    @Test
    public void testSingleSourceUpdate() throws Exception {
        AbstractQueryTransactionTest userTxn = new AbstractQueryTransactionTest("testSingleSourceUpdate") {
            public void testCase() throws Exception {
                execute("insert into pm1.g1 (e1, e2) values(100, '100')");
            }
        };        
        
        // run test
        getTransactionContainter().runTransaction(userTxn);       
        
        // now verify the results
        AbstractQueryTest test = new QueryExecution(userTxn.getSource("pm1"));
        test.execute("select * from g1 where e1 = 100");
        test.assertRowCount(1);
    }
    

    /**
     * Sources = 1
     * Commands = 1, Update(prepared statement)
     * Batching = Full Processing, Single Connector Batch
     * result = commit 
     */
    @Test
    public void testSingleSourcePreparedUpdate() throws Exception {
        AbstractQueryTransactionTest userTxn = new AbstractQueryTransactionTest("testSingleSourcePreparedUpdate") {
            
            public void testCase() throws Exception {
                execute("insert into pm1.g1 (e1, e2) values(?, ?)", new Object[] {new Integer(102), "102"});                
            }
        };        
        
        // run test
        getTransactionContainter().runTransaction(userTxn);
        
        // now verify the results
        AbstractQueryTest test = new QueryExecution(userTxn.getSource("pm1"));
        test.execute("select * from g1 where e1 = 102");
        test.assertRowCount(1);
      
    }    
    
    /**
     * Sources = 1
     * Commands = multiple - Success
     * Batching = Full Processing, Single Connector Batch
     * result = commit
     */
    @Test
    public void testSingleSourceMultipleCommands() throws Exception {
        AbstractQueryTransactionTest userTxn = new AbstractQueryTransactionTest("testSingleSourceMultipleCommands") {
            public void testCase() throws Exception {
                
                execute("delete from pm1.g1 where pm1.g1.e1 >= ?", new Object[] {new Integer(100)});
                
                execute("select * from pm1.g1");
                assertRowCount(100);
                
                for (int i = 100; i < 110; i++) {
                    Integer val = new Integer(i);
                    execute("insert into pm1.g1 (e1, e2) values(?,?)", new Object[] {val, val.toString()});
                    execute("insert into pm1.g2 (e1, e2) values(?,?)", new Object[] {val, val.toString()});
                }
            }
        };        
        
        // run test
        getTransactionContainter().runTransaction(userTxn);      

        // now verify the results
        AbstractQueryTest test = new QueryExecution(userTxn.getSource("pm1"));
        test.execute("select * from g1 where e1 >= 100");
        test.assertRowCount(10);
        test.execute("select * from g2 where e1 >= 100");
        test.assertRowCount(10);        
      
    }
        
    /**
     * Sources = 1
     * Commands = 1, Select
     * Batching = Partial Processing, Single Connector Batch
     * result = commit 
     */
    @Test
    public void testSingleSourcePartialProcessing() throws Exception {
        AbstractQueryTransactionTest userTxn = new AbstractQueryTransactionTest("testSingleSourcePartialProcessing") {
            public void testCase() throws Exception {
                execute("select * from pm1.g1 where pm1.g1.e1 < 100 limit 10");
                assertRowCount(10);
            }
        };        
        
        // run test
        getTransactionContainter().runTransaction(userTxn);       
    }    
    
    ///////////////////////////////////////////////////////////////////////////////////////////////
    //  Multiple Sources     - Rows from 500
    ///////////////////////////////////////////////////////////////////////////////////////////////
    
    /**
     * Sources = 2
     * Commands = 1, Select
     * Batching = Full Processing, Single Connector Batch
     * result = commit 
     */
    @Test
    public void testMultipleSourceSelect() throws Exception {
        AbstractQueryTransactionTest userTxn = new AbstractQueryTransactionTest("testMultipleSourceSelect") {
            public void testCase() throws Exception {
                execute("select * from pm1.g1 join pm2.g1 on pm1.g1.e1 = pm2.g1.e1 where pm1.g1.e1 < 100");
                assertRowCount(100);
            }
        };        
        
        // run test
        getTransactionContainter().runTransaction(userTxn);
    }

    /**
     * Sources = 2
     * Commands = 1, Select
     * Batching = Full Processing, Single Connector Batch
     * result = commit 
     */    
    @Test
    public void testMultipleSourceVirtualSelect() throws Exception {
        AbstractQueryTransactionTest userTxn = new AbstractQueryTransactionTest("testMultipleSourceVirtualSelect") {
            public void testCase() throws Exception {
                execute("select * from vm.g1 where vm.g1.pm1e1 < 100");
                assertRowCount(100);
            }
        };        
        
        // run test
        getTransactionContainter().runTransaction(userTxn);
    }    
    
    /**
     * Sources = 2
     * Commands = 1, Update
     * Batching = Full Processing, Single Connector Batch
     * result = commit 
     */
    @Test
    public void testMultipleSourceUpdate() throws Exception {
        AbstractQueryTransactionTest userTxn = new AbstractQueryTransactionTest("testMultipleSourceUpdate") {
            public void testCase() throws Exception {
                execute("insert into vm.g1 (pm1e1, pm1e2, pm2e1, pm2e2) values(500, '500', 500, '500')");
            }
        };        
        
        // run test
        getTransactionContainter().runTransaction(userTxn);       
        
        // now verify the results
        AbstractQueryTest test = new QueryExecution(userTxn.getSource("pm1"));
        test.execute("select * from g1 where e2 = '500'");
        test.assertRowCount(1);
        test.closeConnection();
        
        test = new QueryExecution(userTxn.getSource("pm2"));
        test.execute("select * from g1 where e2 = '500'");
        test.assertRowCount(1);
    }
    
    /**
     * Sources = 2
     * Commands = 1, Update
     * Batching = Full Processing, Single Connector Batch
     * result = commit 
     */
    @Test
    public void testMultipleSourceSelectInto() throws Exception {
        AbstractQueryTransactionTest userTxn = new AbstractQueryTransactionTest("testMultipleSourceSelectInto") {
            public void testCase() throws Exception {
                execute("insert into vm.g1 (pm1e1, pm1e2, pm2e1, pm2e2) values(501, '501', 501, '501')");
                execute("select pm1.g1.e1, pm1.g1.e2 into pm2.g2 from pm1.g1 where pm1.g1.e1 = 501");
            }
        };        
        
        // run test
        getTransactionContainter().runTransaction(userTxn);       
        
        // now verify the results
        AbstractQueryTest test = new QueryExecution(userTxn.getSource("pm1"));
        test.execute("select * from g1 where e2 = '501'");
        test.assertRowCount(1);
        
        test = new QueryExecution(userTxn.getSource("pm2"));
        test.execute("select * from g1 where e2 = '501'");
        test.assertRowCount(1);
    }    
    
    /**
     * Sources = 2
     * Commands = 1, Update
     * Batching = Full Processing, Single Connector Batch
     * result = commit 
     */
    @Test
    public void testMultipleSourceBulkRowInsert() throws Exception {
        AbstractQueryTransactionTest userTxn = new AbstractQueryTransactionTest("testMultipleSourceBulkRowInsert") {
            public void testCase() throws Exception {
                for (int i = 100; i < 112; i++) {
                    Integer val = new Integer(i);
                    execute("insert into vm.g1 (pm1e1, pm1e2, pm2e1, pm2e2) values(?,?,?,?)", new Object[] {val, val.toString(), val, val.toString()});
                }
                execute("select pm1.g1.e1, pm1.g1.e2 into pm2.g2 from pm1.g1 where pm1.g1.e1 >= 100");
            }
        };        
        
        // run test
        getTransactionContainter().runTransaction(userTxn);       
        
        // now verify the results
        AbstractQueryTest test = new QueryExecution(userTxn.getSource("pm1"));
        test.execute("select * from g1 where e1 >= 100 and e1 < 112");
        test.assertRowCount(12);
        
        test = new QueryExecution(userTxn.getSource("pm2"));
        test.execute("select * from g1 where e1 >= 100 and e1 < 112");
        test.assertRowCount(12);
        test.execute("select * from g2 where e1 >= 100 and e1 < 112");
        test.assertRowCount(12);        
    }    

    /**
     * Sources = 2
     * Commands = 1, Update(prepared statement)
     * Batching = Full Processing, Single Connector Batch
     * result = commit 
     */
    @Test
    public void testMultipleSourcePreparedUpdate() throws Exception {
        AbstractQueryTransactionTest userTxn = new AbstractQueryTransactionTest("testMultipleSourcePreparedUpdate") {
            public void testCase() throws Exception {
                Integer value = new Integer(500);
                execute("insert into vm.g1 (pm1e1, pm1e2, pm2e1, pm2e2) values(?,?,?,?)", new Object[] {value, value.toString(), value, value.toString()});
            }
        };        
        
        // run test
        getTransactionContainter().runTransaction(userTxn);       
        
        // now verify the results
        AbstractQueryTest test = new QueryExecution(userTxn.getSource("pm1"));
        test.execute("select * from g1 where e1 = 500");
        test.assertRowCount(1);
        
        test = new QueryExecution(userTxn.getSource("pm2"));
        test.execute("select * from g1 where e1 = 500");
        test.assertRowCount(1);
      
    }    
    
    
    /**
     * Sources = 2
     * Commands = multiple - Success
     * Batching = Full Processing, Single Connector Batch
     * result = commit
     */
    @Test
    public void testMultipleSourceMultipleCommands() throws Exception {
        AbstractQueryTransactionTest userTxn = new AbstractQueryTransactionTest("testMultipleSourceMultipleCommands") {
            public void testCase() throws Exception {
                execute("delete from pm1.g2 where e1 >= ?", new Object[] {new Integer(100)});
                execute("delete from pm1.g1 where e1 >= ?", new Object[] {new Integer(100)});
                execute("delete from pm2.g2 where e1 >= ?", new Object[] {new Integer(100)});
                execute("delete from pm2.g1 where e1 >= ?", new Object[] {new Integer(100)});
                
                execute("select * from pm1.g1");
                assertRowCount(100);
                
                for (int i = 100; i < 115; i++) {
                    Integer val = new Integer(i);
                    execute("insert into pm1.g1 (e1, e2) values(?,?)", new Object[] {val, val.toString()});
                    execute("insert into pm1.g2 (e1, e2) values(?,?)", new Object[] {val, val.toString()});
                    
                    execute("insert into pm2.g1 (e1, e2) values(?,?)", new Object[] {val, val.toString()});
                    execute("insert into pm2.g2 (e1, e2) values(?,?)", new Object[] {val, val.toString()});                    
                }
                
                execute("update pm1.g1 set e2='blah' where e1 > 100");
            }
        };        
        
        // run test
        getTransactionContainter().runTransaction(userTxn);      

        // now verify the results
        AbstractQueryTest test = new QueryExecution(userTxn.getSource("pm1")) {
	    protected boolean compareCaseSensitive() {
		return false;
	    }
        };
        test.execute("select * from g1 where e1 >= 100 and e1 < 115");
        test.assertRowCount(15);
        test.execute("select * from g2 where e1 >= 100 and e1 < 115");
        test.assertRowCount(15);
        test.execute("select distinct e2 from g1 where e1 > 100");
        
        // NOTE:  if this is an oracle source, it failes because it return varchar2
        if (userTxn.getSource("pm1").getMetaData().getDatabaseProductName().toLowerCase().indexOf("oracle") > -1) {
            test.assertResultsSetEquals(new String[] {"e2[varchar2]", "blah"});
        } else {
            test.assertResultsSetEquals(new String[] {"e2[varchar]", "blah"});
        }
     
    }
    
    /**
     * Sources = 2
     * Commands = multiple - Success
     * Batching = Full Processing, Single Connector Batch
     * result = commit
     */
    @Test
    public void testMultipleSourceMultipleVirtualCommands() throws Exception {
        AbstractQueryTransactionTest userTxn = new AbstractQueryTransactionTest("testMultipleSourceMultipleVirtualCommands") {
            public void testCase() throws Exception {

                for (int i = 200; i < 207; i++) {
                    Integer val = new Integer(i);
                    execute("insert into vm.g1 (pm1e1, pm1e2, pm2e1, pm2e2) values(?,?,?,?)", new Object[] {val, val.toString(), val, val.toString()});
                    execute("insert into vm.g2 (pm1e1, pm1e2, pm2e1, pm2e2) values(?,?,?,?)", new Object[] {val, val.toString(), val, val.toString()});                    
                }
                
                execute("update vm.g1 set pm1e2='blah' where pm1e1 >= 200");
                
                execute("delete from vm.g2 where vm.g2.pm1e1 >= 205");
                execute("delete from vm.g1 where vm.g1.pm1e1 >= 205");
                
                execute("select * from vm.g1 where pm1e1 >= 200 and pm1e1 < 207");
                assertRowCount(5);
            }
        };        
        
        // run test
        getTransactionContainter().runTransaction(userTxn);      

        // now verify the results
        AbstractQueryTest test = new QueryExecution(userTxn.getSource("pm1")){
	    protected boolean compareCaseSensitive() {
		return false;
	    }
        };
        test.execute("select * from g1 where e1 >= 200 and e1 < 207");
        test.assertRowCount(5);
        test.execute("select * from g2 where e1 >= 200 and e1 < 207");
        test.assertRowCount(5);
        test.execute("select distinct e2 from g1 where e1 >= 200 and e1 < 207");
        test.assertResultsSetEquals(new String[] {"e2[varchar2]", "blah"});
      
    }    
        
    /**
     * Sources = 2
     * Commands = multiple - Success
     * Batching = Full Processing, Single Connector Batch
     * result = rollback
     */    
    @Test
    public void testMultipleSourceMultipleCommandsCancel() throws Exception {
        AbstractQueryTransactionTest userTxn = new AbstractQueryTransactionTest("testMultipleSourceMultipleCommandsCancel") {
            
            public void testCase() throws Exception {
                Thread t = new Thread("Cancel Thread") {
                    public void run() {
                        try {
                            try {
                                Thread.sleep(500);
                                cancelQuery();
                            } catch (SQLException e) {
                         //       debug(e.getMessage());
                            }
                        } catch (InterruptedException e) {}
                    }
                };
                t.start();
                executeBatch(getMultipleSourceBatch());
            }
           

            public boolean exceptionExpected() {
                return true;
            }
        };
        getTransactionContainter().runTransaction(userTxn);
                
        // now verify the results (this may finish under one second, then this test is not valid)
        AbstractQueryTest test = new QueryExecution(userTxn.getSource("pm1"));
        test.execute("select * from g1 where e1 >= 600 and e1 < 650");
        test.assertRowCount(0);
        test.execute("select * from g2 where e1 >= 600 and e1 < 650");
        test.assertRowCount(0);
        test.execute("select distinct e2 from g1 where e1 >= 600 and e1 < 650");
        test.assertRowCount(0);
    }

    /**
     * Sources = 2
     * Commands = multiple - Success
     * Batching = Full Processing, Single Connector Batch
     * result = rollback
     */    
    @Test
    public void testMultipleSourceTimeout() throws Exception{
        AbstractQueryTransactionTest userTxn = new AbstractQueryTransactionTest("testMultipleSourceTimeout") {
            public void testCase() throws Exception {
                executeBatch(getMultipleSourceBatch(), 1); // time out after 1 sec
            }
            
            public boolean exceptionExpected() {
                return true;
            }            
            
            public void after() {
                if (!exceptionOccurred()) {
                   	Assertions.assertTrue(false, "should have failed with time out exception");
                }
                else {
                    if (getLastException() != null) {
                                                        
                	String msg = "NA";
                 	SQLException s = getLastException();
                 	
                 	Throwable t = s.getCause();
                 	if (t instanceof TimeoutException) {
                 	   msg = t.getMessage();
                 	} else if (s instanceof TeiidSQLException) {
                 		 TeiidSQLException mm = (TeiidSQLException) t;
                 	     if (mm.getNextException() != null) {
                 		 SQLException next = mm.getNextException();
                 		 msg = next.getMessage();
                 	     } else {
                 		 msg = mm.getMessage();
                 	     }
                 	} else {
                 	
                 	    msg = s.getMessage();
                 	}
                 	boolean isfound = (msg.indexOf("Operation timed out before completion") != -1 ? true : false);

                 	Assertions.assertTrue(isfound, "Exception Message didnt match 'Operation timed out before completion' found: " + msg);
                    } else {
                	Assertions.assertTrue(false, "Program Error: it indicates exception occured, but no exception is found");
                    }
                }
            }             
        };
        getTransactionContainter().runTransaction(userTxn);
        
        // now verify the results (this may finish under one second, then this test is not valid)
        AbstractQueryTest test = new QueryExecution(userTxn.getSource("pm1"));
        test.execute("select * from g1 where e1 >= 600 and e1 < 750");
        test.assertRowCount(0);
        test.execute("select * from g2 where e1 >= 600 and e1 < 750");
        test.assertRowCount(0);
        test.execute("select distinct e2 from g1 where e1 >= 600 and e1 < 750");
        test.assertRowCount(0);
      
    }    
    
        
    static String[] getMultipleSourceBatch() {
        ArrayList list = new ArrayList();
        
        for (int i = 600; i < 750; i++) {
            list.add("insert into pm1.g1 (e1, e2) values("+i+",'"+i+"')");
            list.add("insert into pm1.g2 (e1, e2) values ("+i+",'"+i+"')");
            list.add("insert into pm2.g1 (e1, e2) values("+i+",'"+i+"')");
            list.add("insert into pm2.g2 (e1, e2) values ("+i+",'"+i+"')");                                
        }
        
        list.add("update pm1.g1 set e2='blah' where pm1.g1.e1 >= 600");
        list.add("update pm2.g1 set e2='blah' where pm2.g1.e1 >= 600");
        
        list.add("delete from pm1.g2 where pm1.g2.e1 >= 610");
        list.add("delete from pm1.g1 where pm1.g1.e1 >= 610");
        list.add("delete from pm2.g2 where pm2.g2.e1 >= 610");
        list.add("delete from pm2.g1 where pm2.g1.e1 >= 610");
        
        return(String[])list.toArray(new String[list.size()]);
    }
    
    
    /**
     * Sources = 2
     * Commands = 1, Select
     * Batching = Partial Processing, Single Connector Batch
     * result = commit 
     * Note: This is producing the below error some times; however this is SQL Server issue.
     * http://support.microsoft.com/?kbid=834849
     */
    @Test
    public void testMultipleSourcePartialProcessingUsingLimit() throws Exception {
        AbstractQueryTransactionTest userTxn = new AbstractQueryTransactionTest("testMultipleSourcePartialProcessingUsingLimit") {
            public void testCase() throws Exception {
                execute("select * from vm.g1 where pm1e1 < 100 limit 10");
                assertRowCount(10);
            }
        };        
        
        // run test
        getTransactionContainter().runTransaction(userTxn);       
    }  

    /**
     * Sources = 2
     * Commands = 1, Select
     * Batching = Partial Processing, Single Connector Batch
     * result = commit
     * Note: This is producing the below error some times; however this is SQL Server issue.
     * http://support.microsoft.com/?kbid=834849
     */
    @Test
    public void testMultipleSourcePartialProcessingUsingMakedep() throws Exception {
        AbstractQueryTransactionTest userTxn = new AbstractQueryTransactionTest("testMultipleSourcePartialProcessingUsingMakedep") {
            public void testCase() throws Exception {
                execute("select pm1.g1.e1, pm1.g1.e2 from pm1.g1 LEFT OUTER JOIN pm2.g1 MAKENOTDEP ON pm1.g1.e2 = pm2.g1.e2 where pm2.g1.e1 >= 50 and pm2.g1.e1 < 100");
                assertRowCount(50);
            }
        };        
        
        // run test
        getTransactionContainter().runTransaction(userTxn);       
    }        
}
