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

import java.sql.*;

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;
import pervasync.util.Util;

/**
 * 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 ClientAppSqlite {

    String pervasyncHome = null;
    OrderedProperties clientAppSqliteIni = null;
    String pervasyncClientDemoSchema = null;
    String pervasyncClientAdminUser = null;
    String pervasyncClientDemoDbPath = null;
    //String pervasyncClientAdminDbPath = 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 = "";
        ClientAppSqlite clientAppSqlite = null;
        boolean bUsage = false;

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


        }
        if (args.length < 1) {
            bUsage = true;
        }

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

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

    /**
     * Constructor, read the app parameters from client_app_sqlite.ini
     */
    public ClientAppSqlite() throws Exception {
        // load the sqlite-JDBC driver using the current class loader
        Class.forName("org.sqlite.JDBC");

        File clientAppSqliteIniFile = new File("client_app_sqlite.ini");
        if (!clientAppSqliteIniFile.exists()) {
            System.out.println("clientAppSqliteIniFile="
                    + clientAppSqliteIniFile.getCanonicalPath());
            throw new Exception("Can not find config file "
                    + clientAppSqliteIniFile);
        }
        clientAppSqliteIni = new OrderedProperties();
        FileInputStream clientAppSqliteIniFileIs =
                new FileInputStream(clientAppSqliteIniFile);
        clientAppSqliteIni.load(clientAppSqliteIniFileIs);
        clientAppSqliteIniFileIs.close();

        pervasyncClientDemoSchema =
                clientAppSqliteIni.getPropertyValue("pervasync.client.demo.schema");
        pervasyncClientAdminUser =
                clientAppSqliteIni.getPropertyValue("pervasync.client.admin.user");
        pervasyncClientDemoDbPath = SyncClient.getSqliteDbPath(pervasyncClientDemoSchema);
        //pervasyncClientAdminDbPath = Util.getSqliteDbPath(pervasyncClientAdminUser);


        taskInsertsPerEmployee =
                Integer.parseInt((clientAppSqliteIni.getPropertyValue("task.inserts.per.employee")).trim());
        taskUpdatesPerEmployee =
                Integer.parseInt((clientAppSqliteIni.getPropertyValue("task.updates.per.employee")).trim());
        taskDeletesPerEmployee =
                Integer.parseInt((clientAppSqliteIni.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, null, 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 pervasyncClientDemoDbConn =
                DriverManager.getConnection("jdbc:sqlite:" + this.pervasyncClientDemoDbPath);

        pervasyncClientDemoDbConn.setAutoCommit(false);

        totInsertCount = 0;

        long beginTime = System.currentTimeMillis();
        PreparedStatement pstmtTsk =
                pervasyncClientDemoDbConn.prepareStatement("INSERT INTO "
                //+ pervasyncClientDemoSchema
                + "main"
                + ".tasks(ID,NAME,DESCRIPTION,ASSIGN_DATE,START_DATE,"
                + "FINISH_DATE,PRIORITY,STATUS,COMMENTS)"
                + " VALUES (?,?,?,?,?,?,?,?,?)\n");
        PreparedStatement pstmtEmpTsk =
                pervasyncClientDemoDbConn.prepareStatement("INSERT INTO "
                //+ pervasyncClientDemoSchema
                + "main"
                + ".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();

        pervasyncClientDemoDbConn.commit();
        pervasyncClientDemoDbConn.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 pervasyncClientDemoDbConn =
                DriverManager.getConnection("jdbc:sqlite:" + this.pervasyncClientDemoDbPath);
        pervasyncClientDemoDbConn.setAutoCommit(false);
        totDeleteCount = 0;

        long beginTime = System.currentTimeMillis();

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

        PreparedStatement pstmtTskDelete =
                pervasyncClientDemoDbConn.prepareStatement("DELETE FROM "
                //+ pervasyncClientDemoSchema
                + "main"
                + ".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 =
                pervasyncClientDemoDbConn.prepareStatement("DELETE FROM "
                //+ pervasyncClientDemoSchema
                + "main"
                + ".emp_task WHERE TASK_ID NOT IN (select ID FROM "
                //+ pervasyncClientDemoSchema
                + "main"
                + ".tasks)");
        totDeleteCount += pstmtEmpTsk.executeUpdate();
        pstmtEmpTsk.close();


        pervasyncClientDemoDbConn.commit();
        pervasyncClientDemoDbConn.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 pervasyncClientDemoDbConn =
                DriverManager.getConnection("jdbc:sqlite:" + this.pervasyncClientDemoDbPath);
        pervasyncClientDemoDbConn.setAutoCommit(false);
        totUpdateCount = 0;

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

        PreparedStatement pstmtTskUpdate =
                pervasyncClientDemoDbConn.prepareStatement("UPDATE "
                //+ pervasyncClientDemoSchema
                + "main"
                + ".tasks SET NAME=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 =
                pervasyncClientDemoDbConn.prepareStatement("UPDATE "
                //+ pervasyncClientDemoSchema
                + "main"
                + ".executives SET BIOGRAPHY='From client: '+BIOGRAPHY");
        totUpdateCount += pstmtExecUpdate.executeUpdate();
        pstmtExecUpdate.close();

        pervasyncClientDemoDbConn.commit();
        pervasyncClientDemoDbConn.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");
        // 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);
        }
    }
}
