using UnityEngine;
using System;
using System.Collections;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;

public class SQLDatabase : MonoBehaviour {
    // Global variables
    private static IDbConnection dbConnection;
    public static float dollars;
    public static float Lodging;
    public static float Vehicles;

    // Initialisation
    public void OnEnable() {
        Debug.Log("SQLDatabase.Awake()");
        openSqlConnection();
        //displayBank();
    }

    // Connect to database
    private static void openSqlConnection() {
        string connectionString = "Data source = localhost\\DEVILSZONE\\SQLEXPRESS;" +
            "Database=safariville;" +
            "Integrated security=True;"+
            "Pooling=false;";
        try {
            dbConnection = new SqlConnection(connectionString);
            dbConnection.Open();
            Debug.Log("DATABASE connected");
        }  catch(Exception ex) {
            Debug.LogWarning("Could not connect to DB, got: " + ex.ToString());
            dbConnection = null;
        }
    }

    // MySQL Query
    public static void displayBank() {
        try {
            IDbCommand dbCommand = dbConnection.CreateCommand();
            dbCommand.CommandText = "SELECT * FROM safariville.dbo.bank WHERE SCash = 3000.00";
            Debug.Log("SELECT executed");
            IDataReader reader = dbCommand.ExecuteReader();
            // Executing or Getting the data from the databse
            while (reader.Read())
            {
                dollars = reader.GetFloat(0);
                Lodging = reader.GetFloat(3);
                Debug.Log("bank: " + Lodging);
                Vehicles = reader.GetFloat(4);
                Debug.Log("bank: " + Vehicles);
            }
            reader.Close();
            reader = null;
            dbCommand.Dispose();
            dbCommand = null;
        } catch (Exception ex) {
            Debug.Log(ex);
        }
    }

    // Disconnect from database
    private static void closeSqlConnection() {
        if(dbConnection != null) {
            dbConnection.Close();
            dbConnection = null;
        }
    }

    // On quit
    public void OnApplicationQuit() {
        closeSqlConnection();
    }
}