/* 
 * Copyright (c) 2009 Craig Sutherland
 * 
 * Permission is hereby granted, free of charge, to any person
 * obtaining a copy of this software and associated documentation
 * files (the "Software"), to deal in the Software without
 * restriction, including without limitation the rights to use,
 * copy, modify, merge, publish, distribute, sublicense, and/or sell
 * copies of the Software, and to permit persons to whom the
 * Software is furnished to do so, subject to the following
 * conditions:
 * 
 * The above copyright notice and this permission notice shall be
 * included in all copies or substantial portions of the Software.
 * 
 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
 * EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES
 * OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
 * NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT
 * HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY,
 * WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
 * FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR
 * OTHER DEALINGS IN THE SOFTWARE.
 */

using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Data;

namespace FastForward.Cache
{
    public class SqlLiteDatabaseConnection
        : DatabaseConnection
    {
        #region Private fields
        private bool isInitialised;
        #endregion

        #region Public methods
        #region RetrieveData()
        /// <summary>
        /// Retrieve some data from the database.
        /// </summary>
        /// <returns>a <see cref="IDataReader"/> containing the data.</returns>
        public override DbDataReader RetrieveData(string dataSource, DatabaseCondition condition)
        {
            var parameters = new List<SQLiteParameter>();
            var command = string.Format("select * from {0}", dataSource);
            if (condition != null)
            {
                var conditionNumber = 1;
                command += " where " + BuildCondition(condition, conditionNumber, parameters);
            }

            var dbCommand = new SQLiteCommand(command, GenerateConnection());
            dbCommand.Parameters.AddRange(parameters.ToArray());

            // Execute the command
            dbCommand.Connection.Open();
            var reader = dbCommand.ExecuteReader(CommandBehavior.CloseConnection);
            return reader;
        }
        #endregion

        #region Insert()
        /// <summary>
        /// Inserts a row into the database.
        /// </summary>
        /// <param name="dataSource">The data store (e.g table).</param>
        /// <param name="values">The values to insert.</param>
        public override void Insert(string dataSource, params DatabaseValue[] values)
        {
            var parameters = new List<SQLiteParameter>();
            var columns = new List<string>();
            var placeHolders = new List<string>();
            for (var loop = 0; loop < values.Length; loop++)
            {
                columns.Add(values[loop].Column);
                placeHolders.Add(string.Format("@p{0}", loop + 1));
                parameters.Add(new SQLiteParameter(string.Format("@p{0}", loop + 1), values[loop].Value));
            }

            var command = string.Format(
                "insert into {0} ({1}) values ({2})", 
                dataSource, 
                string.Join(",", columns.ToArray()),
                string.Join(",", placeHolders.ToArray()));
            var dbCommand = new SQLiteCommand(command, GenerateConnection());
            dbCommand.Parameters.AddRange(parameters.ToArray());

            try
            {
                dbCommand.Connection.Open();
                using (var transaction = dbCommand.Connection.BeginTransaction())
                {
                    dbCommand.ExecuteNonQuery();
                    transaction.Commit();
                }
            }
            finally
            {
                dbCommand.Connection.Close();
            }
        }
        #endregion
        #endregion

        #region Protected methods
        #region OnInitialise()
        /// <summary>
        /// Initialise the database schema if necessary.
        /// </summary>
        protected override void OnInitialiseSchema()
        {
            if (!isInitialised)
            {
                var connection = GenerateConnection();
                connection.Open();
                try
                {
                    var command = new SQLiteCommand(connection);

                    command.CommandText = "create table if not exists BuildLog ( Server text, Project text, Build text, Log text )";
                    command.ExecuteNonQuery();
                }
                finally
                {
                    connection.Clone();
                }

                isInitialised = true;
            }
        }
        #endregion
        #endregion

        #region Private methods
        #region GenerateConnection()
        /// <summary>
        /// Generates the database connection.
        /// </summary>
        /// <returns></returns>
        private SQLiteConnection GenerateConnection()
        {
            var connection = new SQLiteConnection(ConnectionString);
            return connection;
        }
        #endregion

        #region BuildCondition()
        /// <summary>
        /// Builds a condition.
        /// </summary>
        /// <returns></returns>
        private string BuildCondition(DatabaseCondition condition, int conditionNumber, List<SQLiteParameter> parameters)
        {
            var actualCondition = string.Empty;
            if (condition is DatabaseConditionValue)
            {
                var valueCondition = condition as DatabaseConditionValue;
                actualCondition = string.Format("{0}=@p{1}", valueCondition.Column, conditionNumber);
                parameters.Add(new SQLiteParameter(string.Format("@p{0}", conditionNumber), valueCondition.Value));
            }
            else if (condition is DatabaseConditionJoin)
            {
                var joinCondition = condition as DatabaseConditionJoin;
                var conditions = new List<string>();
                foreach (var subCondition in joinCondition.Conditions)
                {
                    conditions.Add(BuildCondition(subCondition, conditionNumber++, parameters));
                }
                actualCondition = string.Join(string.Format(" {0} ", joinCondition.JoinType), conditions.ToArray());
            }

            return actualCondition;
        }
        #endregion
        #endregion
    }
}