
//import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;

import java.io.File;
import java.io.FileInputStream;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;

import java.util.StringTokenizer;

import pervasync.SyncDirection;
import pervasync.SyncException;
import pervasync.SyncSummary;

import pervasync.client.SyncAgent;
import pervasync.client.SyncClient;

import pervasync.client.SyncClientAdmin;


import pervasync.util.OrderedProperties;

/**
 * To invoke a synchronization, you need to set the sync client properties such as
 * the sync server URL, user name and password. Initial synchronization will
 * create the physical DB tables and populate them with initial data.
 * To generate DML operations on the client side tables. invoke "insert", "delete" 
 * and "update" methods. Synchronization will check in these changes to
 * the server DB on the server server side.
 *
 */
public class ClientAppMysql {

    String pervasyncHome = null;
    OrderedProperties clientAppMysqlIni = null;
    boolean bUsage = false;
    String pervasyncClientDbUrl = null;
    String pervasyncClientDbSystemUser = null;
    String pervasyncClientDbSystemPassword = null;
    String pervasyncClientDemoSchema = null;
    //MysqlDataSource pervasyncClientDbSystemDataSource = null;
    Connection pervasyncClientDbSystemConn = null;
    String pervasyncClientAdminUser = null;
    int iEmp = -10;
    long taskNextId = 0;
    int taskInsertsPerEmployee = 0;
    int taskUpdatesPerEmployee = 0;
    int taskDeletesPerEmployee = 0;
    int totInsertCount = 0;
    int totUpdateCount = 0;
    int totDeleteCount = 0;
    static SyncClientAdmin syncClientAdmin = null;

    public static void main(String[] args) throws Exception, Throwable {

        long beginTime = System.currentTimeMillis();
        String strArgs = "";
        ClientAppMysql clientAppMysql = new ClientAppMysql();

        // Command line processing
        if (args.length < 1) {
            clientAppMysql.bUsage = true;
        }
        for (int i = 0; i < args.length; i++) {
            try {
                syncClientAdmin = SyncClientAdmin.getInstance();
                if (!args[i].startsWith("-")) {
                    strArgs += args[i];
                    if (args[i].equalsIgnoreCase("sync")) {
                        clientAppMysql.sync();
                    } else if (args[i].equalsIgnoreCase("insert")) {
                        clientAppMysql.insert();
                    } else if (args[i].equalsIgnoreCase("delete")) {
                        clientAppMysql.delete();
                    } else if (args[i].equalsIgnoreCase("update")) {
                        clientAppMysql.update();
                    } else if (args[i].equalsIgnoreCase("add_sync_table")) {
                        clientAppMysql.addSyncTable();
                    } else {
                        clientAppMysql.bUsage = true;
                    }
                    continue;
                }
            } catch (Throwable t) {
                t.printStackTrace();
                System.exit(1);
            } finally {
                syncClientAdmin.destroy();
            }


        }
        
        if(clientAppMysql.pervasyncClientDbSystemConn != null){
            clientAppMysql.pervasyncClientDbSystemConn.close();
        }

        if (clientAppMysql.bUsage) {
            System.out.println("Usage: java ClientAppMysql " +
                    "{setClientProperties | sync | insert | delete | update}");
            System.exit(1);
        }

        long endTime = System.currentTimeMillis();
        System.out.println("\njava pervasync.client.ClientAppMysql " +
                strArgs + " total time: " +
                (endTime - beginTime) / 1000.0 + " seconds");
    }

    /**
     * Constructor, read the app parameters from client_app_mysql.ini
     */
    public ClientAppMysql() throws Exception {


        File clientAppMysqlIniFile = new File("client_app_mysql.ini");
        if (!clientAppMysqlIniFile.exists()) {
            System.out.println("clientAppMysqlIniFile=" +
                    clientAppMysqlIniFile.getCanonicalPath());
            throw new Exception("Can not find config file " +
                    clientAppMysqlIniFile);
        }
        clientAppMysqlIni = new OrderedProperties();
        FileInputStream clientAppMysqlIniFileIs =
                new FileInputStream(clientAppMysqlIniFile);
        clientAppMysqlIni.load(clientAppMysqlIniFileIs);
        clientAppMysqlIniFileIs.close();

        pervasyncClientDbUrl =
                clientAppMysqlIni.getPropertyValue("pervasync.client.db.url");
        pervasyncClientDbSystemUser =
                clientAppMysqlIni.getPropertyValue("pervasync.client.db.system.user");
        pervasyncClientDbSystemPassword =
                clientAppMysqlIni.getPropertyValue("pervasync.client.db.system.password");
        pervasyncClientDemoSchema =
                clientAppMysqlIni.getPropertyValue("pervasync.client.demo.schema");
        pervasyncClientAdminUser =
                clientAppMysqlIni.getPropertyValue("pervasync.client.admin.user");


//        pervasyncClientDbSystemDataSource = new MysqlDataSource();
//        pervasyncClientDbSystemDataSource.setURL(pervasyncClientDbUrl);
//        pervasyncClientDbSystemDataSource.setUser(pervasyncClientDbSystemUser);
//        pervasyncClientDbSystemDataSource.setPassword(pervasyncClientDbSystemPassword);
        Class.forName("com.mysql.jdbc.Driver");
        pervasyncClientDbSystemConn = DriverManager.getConnection(pervasyncClientDbUrl, pervasyncClientDbSystemUser, pervasyncClientDbSystemPassword);
        pervasyncClientDbSystemConn.setAutoCommit(false);

        taskInsertsPerEmployee =
                Integer.parseInt((clientAppMysqlIni.getPropertyValue("task.inserts.per.employee")).trim());
        taskUpdatesPerEmployee =
                Integer.parseInt((clientAppMysqlIni.getPropertyValue("task.updates.per.employee")).trim());
        taskDeletesPerEmployee =
                Integer.parseInt((clientAppMysqlIni.getPropertyValue("task.deletes.per.employee")).trim());

        // Find department id from manager name
        SyncClientAdmin admin = SyncClientAdmin.getInstance();
        String syncUserName = admin.getSyncUserName();
        admin.destroy();
        StringTokenizer st = new StringTokenizer(syncUserName, "_");
        st.nextToken();
        iEmp = Integer.parseInt(st.nextToken());

    }

    /**
     * sync
     */
    public void sync() throws Exception {
        SyncAgent syncAgent = SyncAgent.getInstance(null);
        SyncSummary syncSummary =
                syncAgent.sync(SyncDirection.TWO_WAY, new String[]{"schema1", "schema2"}, null);
        System.out.println("dataSyncSummary:\n" +
                syncSummary.toString());

        // second if checkin skipped
        if (syncSummary.syncErrorMessages != null &&
                syncSummary.syncErrorMessages.contains(
                "PVS-" + SyncException.PVS_CHECK_IN_SKIPPED + ":")) {
            System.out.println("\nWill sync again since last sync only refreshed def changes.\n");
            syncSummary =
                    syncAgent.sync(SyncDirection.TWO_WAY, null, null);
            System.out.println("syncSummary:\n" +
                    syncSummary.toString());
        }

        // third if checkin skipped
        if (syncSummary.syncErrorMessages != null &&
                syncSummary.syncErrorMessages.contains(
                "PVS-" + SyncException.PVS_CHECK_IN_SKIPPED + ":")) {
            System.out.println("\nWill sync again since last sync only refreshed def changes.\n");
            syncSummary =
                    syncAgent.sync(SyncDirection.TWO_WAY, null, null);
            System.out.println("syncSummary:\n" +
                    syncSummary.toString());
        }

        syncAgent.destroy();
        if (syncSummary.syncException != null) {
            throw syncSummary.syncException;
        }
    }

    /**
     * Create some new tasks in the pervasync client
     * @throws Exception
     */
    public void insert() throws Exception {

//        Connection pervasyncClientDbSystemConn =
//                 pervasyncClientDbSystemDataSource.getConnection();
//        pervasyncClientDbSystemConn.setAutoCommit(false);

        totInsertCount = 0;

        long beginTime = System.currentTimeMillis();
        PreparedStatement pstmtTsk =
                pervasyncClientDbSystemConn.prepareStatement("INSERT INTO " +
                pervasyncClientDemoSchema +
                ".tasks(ID,NAME,DESCRIPTION,ASSIGN_DATE,START_DATE," +
                "FINISH_DATE,PRIORITY,STATUS,COMMENTS)" +
                " VALUES (?,?,?,?,?,?,?,?,?)\n");
        PreparedStatement pstmtEmpTsk =
                pervasyncClientDbSystemConn.prepareStatement("INSERT INTO " +
                pervasyncClientDemoSchema +
                ".emp_task(EMP_ID, TASK_ID)" +
                " VALUES (?,?)\n");
        pstmtEmpTsk.setLong(1, iEmp);
        for (int iTsk = 0; iTsk < taskInsertsPerEmployee; iTsk++) {
            //System.out.println("Calling getNextTaskId, iTsk=" + iTsk);
            long nextTaskId = getNextTaskId();
            //System.out.println("After calling getNextTaskId, nextTaskId=" + nextTaskId);
            pstmtTsk.setLong(1, nextTaskId);
            pstmtEmpTsk.setLong(2, nextTaskId);
            pstmtTsk.setString(2, "Task " + nextTaskId + " Name ");
            pstmtTsk.setString(3, "Task " + nextTaskId + " Description ");
            pstmtTsk.setTimestamp(4,
                    new Timestamp(System.currentTimeMillis()));
            pstmtTsk.setTimestamp(5,
                    new Timestamp(System.currentTimeMillis()));
            pstmtTsk.setTimestamp(6, null);
            pstmtTsk.setInt(7, iTsk % 3);
            pstmtTsk.setString(8, iTsk % 2 == 0 ? "READY" : "PROCESSING");
            pstmtTsk.setString(9, null);
            totInsertCount += pstmtTsk.executeUpdate();
            totInsertCount += pstmtEmpTsk.executeUpdate();
        }

        pstmtTsk.close();
        pstmtEmpTsk.close();

        pervasyncClientDbSystemConn.commit();
        //pervasyncClientDbSystemConn.close();

        long endTime = System.currentTimeMillis();
        System.out.println(" Total inserts=" + totInsertCount);
        System.out.println("Time: " + (endTime - beginTime) / 1000.0 +
                " seconds");
    }

    /**
     * Delete some new tasks in the pervasync client
     * @throws Exception
     */
    public void delete() throws Exception {

//        Connection pervasyncClientDbSystemConn =
//                pervasyncClientDbSystemDataSource.getConnection();
//        pervasyncClientDbSystemConn.setAutoCommit(false);
        totDeleteCount = 0;

        long beginTime = System.currentTimeMillis();

        PreparedStatement pstmtTskSelect =
                pervasyncClientDbSystemConn.prepareStatement("SELECT ID FROM " +
                pervasyncClientDemoSchema +
                ".tasks LIMIT 0, ?");
        pstmtTskSelect.setInt(1, taskDeletesPerEmployee);
        ResultSet rs = pstmtTskSelect.executeQuery();

        PreparedStatement pstmtTskDelete =
                pervasyncClientDbSystemConn.prepareStatement("DELETE FROM " +
                pervasyncClientDemoSchema +
                ".tasks WHERE ID=? ");
        while (rs.next()) {
            long id = rs.getLong(1);
            pstmtTskDelete.setLong(1, id);
            totDeleteCount += pstmtTskDelete.executeUpdate();
        }
        rs.close();

        pstmtTskDelete.close();
        pstmtTskSelect.close();

        PreparedStatement pstmtEmpTsk =
                pervasyncClientDbSystemConn.prepareStatement("DELETE FROM "
                + pervasyncClientDemoSchema
                + ".emp_task WHERE TASK_ID NOT IN (select ID FROM "
                + pervasyncClientDemoSchema
                + ".tasks)");
        totDeleteCount += pstmtEmpTsk.executeUpdate();
        pstmtEmpTsk.close();


        pervasyncClientDbSystemConn.commit();
        //pervasyncClientDbSystemConn.close();

        long endTime = System.currentTimeMillis();
        System.out.println(" Total deletes=" + totDeleteCount);
        System.out.println("Time: " + (endTime - beginTime) / 1000.0 +
                " seconds");
    }

    /**
     * Update some new tasks in the pervasync client
     * @throws Exception
     */
    public void update() throws Exception {

//        Connection pervasyncClientDbSystemConn =
//                pervasyncClientDbSystemDataSource.getConnection();
//        pervasyncClientDbSystemConn.setAutoCommit(false);
        totUpdateCount = 0;

        long beginTime = System.currentTimeMillis();
        PreparedStatement pstmtTskSelect =
                pervasyncClientDbSystemConn.prepareStatement("SELECT ID FROM " +
                pervasyncClientDemoSchema +
                ".tasks LIMIT 0, ?");
        pstmtTskSelect.setInt(1, taskUpdatesPerEmployee);
        ResultSet rs = pstmtTskSelect.executeQuery();

        PreparedStatement pstmtTskUpdate =
                pervasyncClientDbSystemConn.prepareStatement("UPDATE " +
                pervasyncClientDemoSchema +
                ".tasks SET NAME=CONCAT(NAME, '_C') WHERE ID=? ");
        while (rs.next()) {
            long id = rs.getLong(1);
            pstmtTskUpdate.setLong(1, id);
            totUpdateCount += pstmtTskUpdate.executeUpdate();
        }
        rs.close();

        pstmtTskUpdate.close();
        pstmtTskSelect.close();

        PreparedStatement pstmtExecUpdate =
                pervasyncClientDbSystemConn.prepareStatement("UPDATE " +
                pervasyncClientDemoSchema +
                ".executives SET BIOGRAPHY=CONCAT('From client: ', BIOGRAPHY)");
        totUpdateCount += pstmtExecUpdate.executeUpdate();
        pstmtExecUpdate.close();

        pervasyncClientDbSystemConn.commit();
        //pervasyncClientDbSystemConn.close();

        long endTime = System.currentTimeMillis();
        System.out.println(" Total Updates=" + totUpdateCount);
        System.out.println("Time: " + (endTime - beginTime) / 1000.0 +
                " seconds");
    }

    long getNextTaskId() throws Exception {
        long nextTaskId = 0;

        // the simplest way to get next value
        //System.out.println("the simplest way to get next value");
        nextTaskId =
                syncClientAdmin.sequenceNextval("schema1", "task_id_seq");

        // just to test the API
        syncClientAdmin.sequenceCurrval("schema1",
                "task_id_seq");

        // again just to test the API
        //System.out.println("again just to test the API");
        Connection clientAdminDbConn =
                syncClientAdmin.getClientAdminDbConnection();

        PreparedStatement pstmt =
                clientAdminDbConn.prepareStatement("SELECT " +
                pervasyncClientAdminUser +
                ".sequence_nextval('schema1', 'task_id_seq')");
        //System.out.println("after prepareStatement");
        ResultSet rs = pstmt.executeQuery();
        //System.out.println("after prepareStatement");
        if (rs.next()) {
            //nextTaskId = rs.getLong(1);
            rs.getLong(1);
        }
        rs.close();
        pstmt.close();
        clientAdminDbConn.rollback(); // just to test the API, so we rollback
        // clientAdminDbConn.commit(); // should have done this to simulate the fact that sequence numbers can not be rolled back

        return nextTaskId;
    }
    
    public void addSyncTable(){
        String syncSchemaName = "schema1";
        String tableName = "employees_from_client";
        String createTableSql = 
              "DROP TABLE IF EXISTS "  + tableName + ";\n"
            + "CREATE TABLE " + tableName + "(\n"
            + "    ID NUMERIC(20) PRIMARY KEY,\n"
            + "    NAME VARCHAR(72),\n"
            + "    TITLE VARCHAR(72) DEFAULT 'default title',\n"
            + "    JOIN_DATE DATETIME DEFAULT '2009-06-07 00:00:00',\n"
            + "    SALARY NUMERIC(20,2) DEFAULT 100000,\n"
            + "    WEIGHT DOUBLE,\n"
            + "    HEIGHT FLOAT DEFAULT 6.0,\n"
            + "    GENDER ENUM('M','F','UNKNOWN') DEFAULT 'UNKNOWN',\n"
            + "    BINARY_DATA VARBINARY(100) DEFAULT x'C9CBBBCCCEB9C8CABCCCCEB9C9CBBB',\n"
            + "    ACTIVE TINYINT(1) DEFAULT 1\n"
            + ") ";
       
        try{
             SyncClient.addSyncTable(
             syncSchemaName,  tableName,
             createTableSql);
             System.out.println("SyncClient.addSyncTable succeeded.");
        }catch(Throwable t){
            t.printStackTrace();
            System.out.println("SyncClient.addSyncTable failed with exception: " + t);
        }
    }
}
