package com.swtstuff;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

public class PersonalInfoDatabaseHelper {

	private static final String DATABASE_NAME = "pi.db";
	private static final int DATABASE_VERSION = 1;

	private String name;
	private int version;

	ArrayList<PersonalInfoDatabase> ptypes = null;


	PersonalInfoDatabaseHelper() {
		this.name = DATABASE_NAME;
		this.version = DATABASE_VERSION;
	}

	public Connection getDB() {
		Connection db = null;

		// to test if a table exists:
		//  SELECT count(*) FROM sqlite_master WHERE type='table' AND name='table_name';
		Statement stmt = null;
		try {
			db = DriverManager.getConnection("jdbc:sqlite:" + DATABASE_NAME);
			stmt = db.createStatement();
			stmt.setQueryTimeout(30);  // set timeout to 30 sec.

			ResultSet rs = stmt.executeQuery( "SELECT count(*) FROM sqlite_master WHERE type='table' AND name='" +
					PersonalInfoDatabase.PI_TABLE_NAME + "';" );
			while(rs.next())
			{
				//System.out.println(rs.getString(1));
				if (rs.getString(1).compareTo("0") == 0) { // this table does NOT exist
					//System.out.println("calling onCreate()");
					onCreate(db);
				}
			}
			rs.close();
			stmt.close();
		} catch (Exception e) {
			System.err.println( e.getClass().getName() + ": " + e.getMessage() );
			throw new AssertionError();
			//System.exit(0);
		}
		System.out.println("Operation (getDB) done successfully");
		return db;
	}

	public void onCreate(Connection db) {
		Statement stmt = null;
		try {
			if (db.isClosed()) {
				db = DriverManager.getConnection("jdbc:sqlite:" + DATABASE_NAME);
			}

			db.setAutoCommit(false);
			stmt = db.createStatement();
			stmt.setQueryTimeout(30);  // set timeout to 30 sec.

			// 	Create the Personal Info Type table
			stmt.executeUpdate("CREATE TABLE " + PersonalInfoDatabase.PI_TABLE_NAME + " ("
					+ PersonalInfoDatabase._ID + " INTEGER PRIMARY KEY AUTOINCREMENT ,"
					+ PersonalInfoDatabase.PI_NAME + " TEXT"
					+ ");");

			stmt.close();
			db.commit();
		} catch (Exception e) {
			System.err.println( e.getClass().getName() + ": " + e.getMessage() );
			throw new AssertionError();
			//System.exit(0);
		}
		System.out.println("Operation (onCreate) done successfully");
	}

	public void addPetName(Connection db, String piName) {
		Statement stmt = null;
		try {
			if (db.isClosed()) {
				db = DriverManager.getConnection("jdbc:sqlite:" + DATABASE_NAME);
			}

			db.setAutoCommit(false);
			stmt = db.createStatement();
			String sql = "INSERT INTO " + PersonalInfoDatabase.PI_TABLE_NAME +
					" (" + PersonalInfoDatabase.PI_NAME + ") " +
					"VALUES ('" + piName + "');";
			stmt.executeUpdate(sql);

			stmt.close();
			db.commit(); // commit this insert/change
		} catch (Exception e) {
			System.err.println( e.getClass().getName() + ": " + e.getMessage() );
			throw new AssertionError();
			//System.exit(0);
		}
		System.out.println("Record created successfully");
	}
}