documentation we might lose when merging

using System;
using System.Data.SqlClient;

using TeamCShared.Data;
using TeamCShared.Messages;
using TeamCShared.DatabaseAPI.Query;
using TeamCShared.DatabaseAPI.DBMessages;

namespace TeamCShared.DatabaseAPI
{

    public class DBWrapper
    {

        // Use to test Database independently
        static void Main()
        {
            DBWrapper database = new DBWrapper();
            database.Connect();
            // database.InitializeSchema();
            //database.Command("create table sharks (SharkName varchar(255), Depth int);");
            //database.Command("drop table sharks");
            database.Disconnect();
        }

        // Members
        SqlConnection connection;
        SQLGenerator sqlGenerator;

        /* TODO: DBWrapper should not have state, because instantiated in multiple classes
        elsewhere in the system. need to find a better way to keep track of transaction ids
        and get rid of these ID members */
        ulong jsonID;
        ulong transactionID;

        /// <summary>
        /// Constructor
        /// </summary>
        public DBWrapper() {
            jsonID = 1;
            transactionID = 1;
            sqlGenerator = new SQLGenerator(this);
        }

        // Methods

        // -- Helper Methods

        public string freshJID()
        {
            string rval = Convert.ToString(jsonID);
            jsonID++;
            return rval;
        }

        public ulong freshTID()
        {
            ulong rval = transactionID;
            transactionID++;
            return rval;
        }

        /// <summary>
        /// Set up a connection to the Database and create tables if they do not exist.
        /// </summary>
        ///
        /// <returns>
        /// true if connection was successful, false if SQL exception occurred
        /// </returns>
        public bool Connect()
        {
            bool returnVal = true;
            string connectionString, password, userName, SQLConnectionString;

            connectionString =
                @"Server=tcp:comp410-teamc.database.windows.net,1433;
                Database=Warmup-C;
                Connection Timeout=30;
                Encrypt = True;
                TrustServerCertificate=False;";

            // njm3: this hard-coded authentication stuff worries me
            userName = "teamc_admin";
            password = "Sharks4ever";

            SQLConnectionString = "Password=" + password + ';' +
                "User ID=" + userName + ";" + connectionString;

            try
            {
                connection = new SqlConnection(SQLConnectionString);
                connection.Open();
            }
            catch (SqlException e)
            {
                e.ToString();
                returnVal = false;
            }

            InitializeSchema();
            return returnVal;

        }

        /// <summary>
        /// Close connection to the database.
        /// </summary>
        ///
        /// <returns>
        /// True if connection successfully closed, false if a SQL exception occurred
        /// </returns>
        public bool Disconnect()
        {
            bool returnVal = true;
            try
            {
                connection.Close();
                connection.Dispose();
            }
            catch (SqlException e)
            {
                e.ToString();
                returnVal = false;
            }

            return returnVal;
        }



        /// <summary>
        /// Sends Non-Query SQL commands (that is, commands that do not require a return value)
        /// </summary>
        ///
        /// <param name="sqlQuery">
        /// the non-query command to be executed
        /// </param>
        public void Command(string sqlQuery)
        {
            SqlCommand command = new SqlCommand();
            command.Connection = connection;
            command.CommandType = System.Data.CommandType.Text;
            command.CommandText = sqlQuery;
            command.ExecuteNonQuery();
        }

        /// <summary>
        /// Sends Query SQL commands (that is, commands that require a return value)
        /// </summary>
        ///
        /// <param name="sqlQuery">
        /// the query command to be executed
        /// </param>
        ///
        /// <returns>
        /// a SQLDataReader
        /// </returns>
        public System.Data.SqlClient.SqlDataReader Query(string sqlQuery)
        {
            SqlCommand command = new SqlCommand();
            command.Connection = connection;
            command.CommandType = System.Data.CommandType.Text;
            command.CommandText = sqlQuery;
            return command.ExecuteReader();
        }

        public void InitializeSchema()
        {
            // Construct Float Table

            Command(@"if not exists(select 1 from sys.Tables where Name = N'FloatData' and Type = N'U')
                      begin
                          create table FloatData (Data float, ID int);
                      end");

            // Construct String Table
            Command(@"if not exists(select 1 from sys.Tables where Name = N'StringData' and Type = N'U')
                      begin
                          create table StringData (Data varchar(255), ID int);
                      end");

            // Construct JSON Table
            Command(@"
                      if not exists(select 1 from sys.Tables where Name = N'JSONData' and Type = N'U')
                      begin
                          create table JSONData (Name varchar(255), JSONKey varchar(255), FloatVal float, StringVal varchar(255), ID int);
                      end");

            // Construct ToDelete bookeeping table.
            Command(@"
                      if not exists(select 1 from sys.Tables where Name = N'ToDelete' and Type = N'U')
                      begin
                          create table ToDelete (ID int, TableType varchar(255));
                      end");

        }


        //API


        /// <summary>
        /// Get data from the already-connected database
        /// </summary>
        ///
        /// <param name="message">
        /// An <see cref="TeamCShared.DatabaseAPI.DBMessages.IDBMessage"/> which contains a query
        /// </param>
        public void getData(GetDBMessage message)
        {
            /*
            string query = "";
            string dataType = "";

            // Get query from message
            // Get table identifier

            query = @" UPDATE _____Data
                    SET ID = ___
                    OUTPUT Inserted.Data
                    WHERE _____ AND ID = 0";

           query =  @"UPDATE JSONData
                    SET ID = ___
                    OUTPUT Inserted.Name, Inserted.JSONKey, Inserted.FloatData, Inserted.StringData
                    WHERE NAME IN
                    (SELECT NAME FROM JSONData
                    WHERE _______) AND ID = 0;";

           */

            // TODO
            // Set data type in ToDelete
            // Remember to close the reader

        }

        /// <summary>
        /// Put data into the already-connected database
        /// </summary>
        ///
        /// <param name="message">
        /// An <see cref="TeamCShared.DatabaseAPI.DBMessages.IDBMessage"/> with the data to be sent
        /// </param>
        public void putData(PutDBMessage message)
        {
            IData data = message.data;
            data.process(sqlGenerator);
        }

        /// <summary>
        /// Delete data from the already-connected database
        /// </summary>
        ///
        /// <param name="message">
        /// An <see cref="TeamCShared.DatabaseAPI.DBMessages.IDBMessage"/> with the transaction id to delete
        /// </param>
        public void delData(DeleteDBMessage message)
        {
            // TODO DISALLOW 0 REMOVAL
            int id = message.id;
            string tableName = "";
            System.Data.SqlClient.SqlDataReader reader = Query(
                @"SELECT TableType
                FROM ToDelete
                WHERE ToDelete.ID = " + Convert.ToString(id));



            if (reader.Read())
            {
                switch (reader.GetString(0))
                {
                    case "f":
                        tableName = "FloatData";
                        break;
                    case "s":
                        tableName = "StringData";
                        break;
                    case "j":
                        tableName = "JSONData";
                        break;
                    default:
                        break;
                        // TODO HANDLE ERROR CASES
                }
            }
            reader.Close();

            //remove data from table only if transaction id is in ToDelete table
            Command(
                    @"DELETE " +  tableName +
                    "FROM " + tableName +
                    "INNER JOIN ToDelete" +
                    "ON ToDelete.ID = " + tableName + ".ID " +
                    "Where ToDelete.ID = " + Convert.ToString(id) + ";");

            //remove transaction id from ToDelete table
            Command(
                    @"DELETE FROM ToDelete
                    WHERE ID = "+ Convert.ToString(id) + ";");
        }

    }
}