using System;
using System.Collections.Generic;
using System.Text;
using System.Collections;
using System.Data;
using System.IO;
using MarkItUp.SingleUserBlog.Common;
using MarkItUp.SingleUserBlog.Services;
using System.Data.SQLite;

namespace kowitz.SingleUserBlog.Services
{
    class SQLiteDataLayer : IDisposable
    {
        private SQLiteCommand sqlCommand;
        private SQLiteConnection sqlConnection;
        private SQLiteTransaction sqlTransaction;
        private ArrayList sqlParameters;

        public SQLiteDataLayer()
        {
            if (sqlParameters == null)
                sqlParameters = new ArrayList();
        }

        ~SQLiteDataLayer()
        {
            Dispose();
        }

        #region IDisposable Members

        public void Dispose()
        {
            Disconnect(false);
            if (sqlConnection != null)
            {
                sqlConnection.Dispose();
            }
        }

        #endregion


        public void CheckForConnection()
        {
            string connectionstr = "Data Source=" + Path.Combine(Globals.BlogPostDataFolderPath, "sub.db") + ";Version=3;New=False;Compress=True;";
            if (sqlConnection == null)
            {
                sqlConnection = new SQLiteConnection(connectionstr);
            }
            if (sqlConnection.State == ConnectionState.Closed || sqlCommand == null || sqlCommand.Connection == null)
            {
                sqlCommand = new SQLiteCommand(sqlConnection);
                sqlConnection.Open();
            }
            if (sqlTransaction == null || sqlTransaction.Connection == null)
            {
                if (sqlTransaction != null && sqlTransaction.Connection == null)
                {
                    sqlTransaction.Dispose();
                    sqlTransaction = null;
                }

                sqlTransaction = sqlConnection.BeginTransaction();
            }
        }

        public void Disconnect()
        {
            Disconnect(true);
        }

        public void Commit()
        {
            if (sqlTransaction != null)
            {
                if (sqlConnection != null && sqlConnection.State != ConnectionState.Closed && sqlTransaction.Connection != null)
                {
                    sqlTransaction.Commit();
                }
                sqlTransaction.Dispose();
                sqlTransaction = null;
            }
        }

        public void Disconnect(bool commit)
        {
            if (commit)
            {
                Commit();
            }
            
            if (sqlParameters != null)
            {
                sqlParameters.Clear();
            }
            if (sqlCommand != null)
            {
                sqlCommand.Parameters.Clear();
                sqlCommand.Dispose();
            }
        }

        public void Rollback()
        {
            if (sqlTransaction != null)
            {
                try
                {
                    sqlTransaction.Rollback();
                }
                catch (Exception) { }
            }
        }

        public void AddParameter(string name, object value, DbType paramType)
        {
            if (sqlParameters == null)
                sqlParameters = new ArrayList();
            SQLiteParameter param = new SQLiteParameter(name,paramType);
            param.Direction = ParameterDirection.Input;
            param.Value = value;
            sqlParameters.Add(param);           
        }

        private void AddParameters()
        {
            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.AddRange(sqlParameters.ToArray());
        }

        public void ClearParameters()
        {
            sqlParameters.Clear();
        }

        public int ExecuteNonQuery(string commandtext)
        {
            try
            {
                CheckForConnection();
                sqlCommand.CommandText = commandtext;
                AddParameters();

                int retval = sqlCommand.ExecuteNonQuery();

                return retval;
            }
            catch (Exception err)
            {
                Rollback();
                Disconnect(false);
                throw err;
            }            
        }

        public object ExecuteScalar(string commandtext)
        {
            try
            {
                CheckForConnection();
                sqlCommand.CommandText = commandtext;
                AddParameters();

                object retval = sqlCommand.ExecuteScalar();

                return retval;
            }
            catch (Exception err)
            {
                Rollback();
                Disconnect(false);
                throw err;
            }
        }

        public System.Data.IDataReader ExecuteReader(string commandtext)
        {
            try
            {
                CheckForConnection();
                sqlCommand.CommandText = commandtext;
                AddParameters();

                return (System.Data.IDataReader)sqlCommand.ExecuteReader();
            }
            catch (Exception err)
            {
                Rollback();
                Disconnect(false);
                throw err;
            }
        }
    }

    public class SQLiteDataHandler : IDisposable
    {
        private SQLiteDataLayer _DL;

        public SQLiteDataHandler()
        {
            _DL = new SQLiteDataLayer();
        }

        #region IDisposable Members

        public void Dispose()
        {
            if (_DL != null)
            {
                _DL.Disconnect();
                _DL.Dispose();
                _DL = null;
            }
        }

        #endregion

        public void Commit()
        {
            _DL.Commit();
        }

        public int DeleteFeedback(string feedbackId)
        {
            _DL.ClearParameters();
            _DL.AddParameter("FEEDBACKID", feedbackId, DbType.String);
            return _DL.ExecuteNonQuery("delete from Feedback where id=?");
        }

        public System.Data.IDataReader LoadFeedback(string feedbackItemId)
        {
            _DL.ClearParameters();
            _DL.AddParameter("FEEDBACKID", feedbackItemId, DbType.String);
            return _DL.ExecuteReader("select * from Feedback where id=?");
        }

        public System.Data.IDataReader ListFeedbackByPost(string postId)
        {
            _DL.ClearParameters();
            _DL.AddParameter("POSTID", postId, DbType.String);
            return _DL.ExecuteReader("select * from Feedback where postid=? order by DATETIME(datecreated) desc");
        }

        public int SaveFeedback(FeedbackItem item)
        {
            _DL.ClearParameters();
            _DL.AddParameter("@POSTID",      item.PostId, DbType.String);
            _DL.AddParameter("@URL",         item.UserUrl, DbType.String);
            _DL.AddParameter("@DISPNAME",    item.DisplayName, DbType.String);
            _DL.AddParameter("@DESC",        item.Description, DbType.String);
            _DL.AddParameter("@USER",        item.Username, DbType.String);
            _DL.AddParameter("@DATECREATE",  item.DateCreated.ToUniversalTime().ToString("s"), DbType.String);
            _DL.AddParameter("@DATEMOD",     item.DateModified.ToUniversalTime().ToString("s"), DbType.String);
            _DL.AddParameter("@APPROVED",    (item.IsApproved?1:0), DbType.Int32);

            return _DL.ExecuteNonQuery("insert into Feedback(postid,UserUrl,DisplayName,description,UserName,datecreated,datemodified,IsApproved) " +
                "values (@POSTID,@URL,@DISPNAME,@DESC,@USER,@DATECREATE,@DATEMOD,@APPROVED)");
        }

        public int FeedbackCount(string postid)
        {
            _DL.ClearParameters();
            _DL.AddParameter("POSTID", postid, DbType.String);
            int retval = 0;
            try
            {
                retval = Convert.ToInt32(_DL.ExecuteScalar("select count(*) from Feedback where postid=?"));
            }
            catch (Exception) { retval = 0; }
            return retval;
        }

        public System.Data.IDataReader ListCategories()
        {
            _DL.ClearParameters();
            return _DL.ExecuteReader("select * from Categories");
        }

        public System.Data.IDataReader LoadPost(string postId)
        {
            _DL.ClearParameters();
            _DL.AddParameter("POSTID", postId, DbType.String);
            return _DL.ExecuteReader("select * from PostDetails where id=?");
        }

        public int NewPostID()
        {
            _DL.ClearParameters();
            int retval;
            try
            {
                retval = Convert.ToInt32( _DL.ExecuteScalar("select max(id)+1 from Posts") );
            }
            catch (Exception)
            {
                return 1;
            }
            return retval;
        }

        public int InsertPost(Post item)
        {
            _DL.ClearParameters();
            _DL.AddParameter("@ID", item.Id, DbType.String);
            _DL.AddParameter("@DISPNAME",    item.DisplayName, DbType.String);
            _DL.AddParameter("@DESC",        item.Description, DbType.String);
            _DL.AddParameter("@DATECREATE",  item.DateCreated.ToUniversalTime().ToString("s"), DbType.String);
            _DL.AddParameter("@DATEMOD",     item.DateModified.ToUniversalTime().ToString("s"), DbType.String);
            _DL.AddParameter("@APPROVED",    (item.IsApproved ? 1 : 0), DbType.Int32);

            return _DL.ExecuteNonQuery("insert into Posts(id,DisplayName,description,datecreated,datemodified,IsApproved) " +
                "values(@ID,@DISPNAME,@DESC,@DATECREATE,@DATEMOD,@APPROVED)");
        }

        public int UpdatePost(Post item)
        {
            _DL.ClearParameters();
            _DL.AddParameter("@DISPNAME", item.DisplayName, DbType.String);
            _DL.AddParameter("@DESC", item.Description, DbType.String);
            _DL.AddParameter("@DATEMOD", item.DateModified.ToUniversalTime().ToString("s"), DbType.String);
            _DL.AddParameter("@APPROVED", (item.IsApproved ? 1 : 0), DbType.Int32);
            _DL.AddParameter("@POSTID", item.Id, DbType.String);

            return _DL.ExecuteNonQuery("update Posts set DisplayName=@DISPNAME,description=@DESC," +
            "datemodified=@DATEMOD,IsApproved=@APPROVED where id=@POSTID");
        }

        public int DeletePost(string PostID)
        {
            _DL.ClearParameters();
            _DL.AddParameter("POSTID", PostID, DbType.String);
            return _DL.ExecuteNonQuery("delete from Posts where id=?");
        }

        public System.Data.IDataReader ListPostsByAll()
        {
            _DL.ClearParameters();
            return _DL.ExecuteReader("select * from PostDetails order by DATETIME(datecreated) desc");
        }

        public System.Data.IDataReader ListPostsByLatest(int number)
        {
            _DL.ClearParameters();
            _DL.AddParameter("LIMIT", number, DbType.Int32);
            return _DL.ExecuteReader("select * from PostDetails order by DATETIME(datecreated) desc limit ?");
        }

        public DateTime EarliestPostDate()
        {
            _DL.ClearParameters();
            DateTime dt;
            try{
            dt = Convert.ToDateTime(_DL.ExecuteScalar("select datetime(datecreated) as Earliest from Posts order by datetime(datecreated) asc LIMIT 1"));
            }catch(Exception)
            {
                dt = DateTime.MinValue; 
            }
            return dt;
        }

        public System.Data.IDataReader SearchPosts(string searchPhrase, int limit)
        {
            _DL.ClearParameters();
            _DL.AddParameter("@SEARCHTEXT", "%"+searchPhrase+"%", DbType.String);
            _DL.AddParameter("@LIMIT", limit, DbType.Int32);
            return _DL.ExecuteReader("select p.* from PostDetails p left outer join Feedback f on f.PostId=p.id where p.DisplayName like @SEARCHTEXT or p.Description like @SEARCHTEXT or f.DisplayName like @SEARCHTEXT or f.Description like @SEARCHTEXT LIMIT @LIMIT");
        }

        public System.Data.IDataReader PostsByMonthlyArchive()
        {
            _DL.ClearParameters();
            return _DL.ExecuteReader("select strftime('%m,%Y', p.datecreated) as DisplayName, count(p.id) as PostCount from PostDetails p group by strftime('%m,%Y', p.datecreated) order by datetime(p.datecreated) desc");
        }

        public System.Data.IDataReader PostsByMonthString(string monthStr)
        {
            _DL.ClearParameters();
            _DL.AddParameter("MONTHSTRING", monthStr, DbType.String);
            return _DL.ExecuteReader("select * from PostDetails where strftime('%m,%Y', datecreated) = ?");
        }

        public System.Data.IDataReader ListPostsByCategory(string catid)
        {
            _DL.ClearParameters();
            _DL.AddParameter("CATEGORYID", catid, DbType.String);
            return _DL.ExecuteReader("select distinct p.* from PostDetails p join PostCategory pc on pc.postid=p.id where pc.categoryid=? order by DATETIME(p.datecreated) desc");
        }

        public System.Data.IDataReader ListCategoriesByPost(string postId)
        {
            _DL.ClearParameters();
            _DL.AddParameter("POSTID", postId, DbType.String);   
            return _DL.ExecuteReader("select distinct c.* from Categories c join PostCategory pc on c.id=pc.categoryid where pc.postid=?");
        }

        public int SavePostCategory(string postId, string categoryIds)
        {
            _DL.ClearParameters();
            _DL.AddParameter("POSTID", postId, DbType.String);
            _DL.AddParameter("CATEGORYID", categoryIds, DbType.String);

            return _DL.ExecuteNonQuery("insert into PostCategory(postid,categoryid) values(?,?)");
        }

        public int DeletePostCategories(string postId)
        {
            _DL.ClearParameters();
            _DL.AddParameter("POSTID", postId, DbType.String);
            return _DL.ExecuteNonQuery("delete from PostCategory where postid=?");
        }

        public System.Data.IDataReader LoadCategory(string categoryId)
        {
            _DL.ClearParameters();
            _DL.AddParameter("CATEGORYID", categoryId, DbType.String);
            return _DL.ExecuteReader("select * from Categories where id=?");
        }

        public int InsertCategory(Category item)
        {
            _DL.ClearParameters();
            _DL.AddParameter("@DISPNAME", item.DisplayName, DbType.String);
            _DL.AddParameter("@DESC", item.Description, DbType.String);
            _DL.AddParameter("@DATECREATE", item.DateCreated.ToUniversalTime().ToString("s"), DbType.String);
            _DL.AddParameter("@DATEMOD", item.DateModified.ToUniversalTime().ToString("s"), DbType.String);
            _DL.AddParameter("@APPROVED", (item.IsApproved ? 1 : 0), DbType.Int32);
            _DL.AddParameter("@PARENT", item.ParentId, DbType.Int32);
            _DL.AddParameter("@TYPE", (int)item.CategoryType, DbType.Int32);

            return _DL.ExecuteNonQuery("insert into Categories(DisplayName, description, datecreated, datemodified, IsApproved, parentid, type) "+
                "values(@DISPNAME,@DESC,@DATECREATE,@DATEMOD,@APPROVED,@PARENT,@TYPE)");
        }

        public int UpdateCategory(Category item)
        {
            _DL.ClearParameters();
            _DL.AddParameter("@DISPNAME", item.DisplayName, DbType.String);
            _DL.AddParameter("@DESC", item.Description, DbType.String);
            _DL.AddParameter("@DATEMOD", item.DateModified.ToUniversalTime().ToString("s"), DbType.String);
            _DL.AddParameter("@APPROVED", (item.IsApproved ? 1 : 0), DbType.Int32);
            _DL.AddParameter("@PARENT", item.ParentId, DbType.Int32);
            _DL.AddParameter("@TYPE", (int)item.CategoryType, DbType.Int32);
            _DL.AddParameter("@CATEGORYID", item.Id, DbType.String);

            return _DL.ExecuteNonQuery("update Categories set DisplayName=@DISPNAME, description=@DESC, datemodified=@DATEMOD, IaApproved=@APPROVED, parentid=@PARENT, type=@TYPE where id=@CATEGORYID");
        }

        public int DeleteCategory(string categoryId)
        {
            _DL.ClearParameters();
            _DL.AddParameter("CATEGORYID", categoryId, DbType.String);

            return _DL.ExecuteNonQuery("delete from Categories where id=?");
        }
    }  
}