package com.sampleapp;

import android.app.Activity;
import android.os.Bundle;
import android.util.Log;
import com.ianywhere.ultralitejni16.*;

public class NewUltraLiteJAppActivity extends Activity {
    Connection _conn = null;
    ResultSet _departments = null;
    PreparedStatement _inserter = null;
    PreparedStatement _updater = null;
    PreparedStatement _deleter = null;
    PreparedStatement _preparer = null;

    public void PrintText(String strText) {
        Log.i("NewUltraLiteJAppActivity", strText);
    }

    public void HandleError(ULjException err) {
        Log.w("NewUltraLiteJAppActivity", "Exception: " + err.toString());
    }

    public Connection GetDatabase(String strFilename) {
        ConfigFileAndroid config = null;
        Connection dbConnection = null;

        try {
            config = DatabaseManager.createConfigurationFileAndroid(
                    strFilename, getApplicationContext()
            );
            dbConnection = DatabaseManager.connect(config);
            PrintText("Successfully connected to the database at: " 
                    + strFilename);
        } catch(ULjException ex) {
            if (config != null) {
                try {
                    dbConnection = DatabaseManager.createDatabase(config);
                    PrintText("Successfully created a new database at: "
                            + strFilename);
                } catch(ULjException exception) {
                    HandleError(exception);
                }
            }
            HandleError(ex);
        }
        return dbConnection;
    }

    public void Commit() {
        try {
            _conn.commit();
        } catch (ULjException e1) {
            HandleError(e1);
        }
    }

    public void CloseDatabase() {
        try {
            _conn.release();
        } catch (ULjException e) {
            HandleError(e);
        }
    }

    public void ExecuteSQLStatement(String strSQLstmt) {
        PreparedStatement ps;
        try {
            ps = _conn.prepareStatement(strSQLstmt);
            ps.execute();
            ps.close();
            PrintText("Successfully executed: " + strSQLstmt);
        } catch (ULjException e) {
            HandleError(e);
        }
    }

    public void InitStatements() {
        String stmt;
        try {
            stmt = "INSERT INTO Department(dept_no, name) VALUES (?,?)";
            _inserter = _conn.prepareStatement(stmt);
            stmt = "UPDATE Department SET dept_no = ? WHERE dept_no = ?";
            _updater = _conn.prepareStatement(stmt);
            stmt = "DELETE FROM Department WHERE dept_no = ?";
            _deleter = _conn.prepareStatement(stmt);
        } catch (ULjException e) {
            HandleError(e);
        }
    }

    public void FiniStatements() {
        try {
            _departments.close();
            _inserter.close();
            _updater.close();
            _deleter.close();
            _preparer.close();
        } catch (ULjException e) {
            HandleError(e);
        }
    }

    public void AddDepartment(int deptID, String deptName) {
        try {
            _inserter.set(1, deptID);
            _inserter.set(2, deptName);
            _inserter.execute();
            PrintText("Successfully executed:"
                    + " INSERT INTO Department(dept_no, name)" 
                    + " VALUES (" + deptID + "," + deptName + ")");
        } catch (ULjException e) {
            HandleError(e);
        }
    }

    public void UpdateDepartment(int deptIDold, int deptIDnew) {
        try {
            _updater.set(1, deptIDnew);
            _updater.set(2, deptIDold);
            _updater.execute();
            PrintText("Successfully executed:"
                    + " UPDATE Department SET dept_no = " + deptIDnew 
                    + " WHERE dept_no = " + deptIDold);
        } catch (ULjException e) {
            HandleError(e);
        }
    }

    public void DeleteDepartment(int deptID) {
        try {
            _deleter.set(1, deptID);
            _deleter.execute();
            PrintText("Successfully executed:"
                    + " DELETE FROM Department WHERE dept_no = " + deptID);
        } catch (ULjException e) {
            HandleError(e);
        }
    }

    public ResultSet SelectDepartmentRows() {
        String stmt = "SELECT * FROM Department ORDER BY dept_no";
        _preparer = null;
        _departments = null;
        try {
            _preparer = _conn.prepareStatement(stmt);
            _departments = _preparer.executeQuery();
            PrintText("Successfully executed: " + stmt);
        } catch (ULjException e) {
            HandleError(e);
        }
        return _departments;
    }

    /** Called when the activity is first created. */
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);

        PrintText("Starting application...");
        
        _conn = GetDatabase("test.udb");
        if (_conn == null) {
            return;
        }
        
        String[] stmt = new String[3];
        
        stmt[0] = "CREATE TABLE Department("
                + "dept_no INT PRIMARY KEY, "
                + "name CHAR(50) NOT NULL)";

        stmt[1] = "CREATE TABLE Employee("
            + "id INT PRIMARY KEY, "
            + "last_name CHAR(50) NOT NULL, "
            + "first_name CHAR(50) NOT NULL, "
            + "dept_id INT NOT NULL, "
            + "NOT NULL FOREIGN KEY(dept_id) "
            + "REFERENCES Department(dept_no))";

        stmt[2] = "CREATE INDEX ON Employee(last_name, first_name)";
        
        for(int i = 0; i< stmt.length; i++) {
            ExecuteSQLStatement(stmt[i]);
        }

        InitStatements();

        AddDepartment(101, "Electronics");
        AddDepartment(105, "Sales");
        AddDepartment(109, "Accounting");

        UpdateDepartment(101, 102);

        DeleteDepartment(102);

        Commit();

        _departments = SelectDepartmentRows();
        if (_departments != null) {
            try {
                while(_departments.next()) {
                    int dept_no = _departments.getInt(1);
                    String dept_name = _departments.getString(2);
                    PrintText("Department no.:" + dept_no
                            + " Department name: " + dept_name);
                }
            } catch (ULjException e) {
                HandleError(e);
            }
        }
    
        FiniStatements();

        CloseDatabase();

        PrintText("Closing application...");
        finish();
    }
}