using System;
using System.Collections.Generic;
using System.Data.Linq;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Linq;
using System.Text;

namespace App_Code
{
    /// <summary>
    /// Summary description for DataAccess
    /// </summary>
    public class DataAccess
    {
        private string _cStr = System.Configuration.ConfigurationManager.ConnectionStrings["SnippetDB"].ConnectionString;
        private SqlConnection _connection;
        private SqlCommand _sqlcmd;

        //temp
        StringBuilder output = new StringBuilder();

        public DataAccess()
        {
        }

        public void CreateSnippetTable()
        {
            //set variables
            _cStr = System.Configuration.ConfigurationManager.ConnectionStrings["SnippetDB"].ConnectionString;
            _connection = new SqlConnection(_cStr);
            //connect to the database
            _connection.Open();

            string q = @"CREATE TABLE tblSnippets
                    (
                    snippetID int IDENTITY(0,1) NOT NULL,
                    snippetContent varchar(MAX),
                    snippetName varchar(50),
                    snippetLang varchar(25),
                    snippetTags varchar(255),
                    snippetDescription varchar(MAX),
                    snippetAuthorID int,

                    )";

            _sqlcmd = new SqlCommand(q, _connection);
            _sqlcmd.ExecuteNonQuery();

            //close connection
            _connection.Close();

        }

        public void InsertRandomSnippets()
        {
            DataContext dc = new DataContext(_cStr);
            Table<Snippet> snippetstbl = dc.GetTable<Snippet>();
            snippetstbl.InsertAllOnSubmit(GetRandomSnippetList());
            //snippetstbl.InsertOnSubmit(new Snippet("00", "00", "00", "00"));
            dc.SubmitChanges();
            dc.Log = new DebuggerWriter();
        }

        public List<Snippet> GetRandomSnippetList()
        {
            List<Snippet> output = new List<Snippet>();
            for (int x = 0; x < 100; x++)
            {
                Snippet s = new Snippet();
                s.snippetContent = Tools.RandomSentence(500, 10);
                s.snippetTags = Tools.RandomWord(10) + ";" + Tools.RandomWord(10) + ";" + Tools.RandomWord(10) + ";" +
                                Tools.RandomWord(10);
                s.snippetName = Tools.RandomWord(10);
                s.snippetLang = "" + ThreadSafeRandom.Next(0, 100) + ";" + ThreadSafeRandom.Next(0, 100) + ";" + ThreadSafeRandom.Next(0, 100);
                s.snippetID = -55;
                s.snippetDescription = Tools.RandomSentence(50,10);
                output.Add(s);
            }
            return output;

        }

        public IEnumerable<Snippet> GetAllSnippets()
        {

            var dc = new DataContext(_cStr);
            var snippetstbl = dc.GetTable<Snippet>();
            dc.Log = new DebuggerWriter();
            return snippetstbl.AsEnumerable();

        }

        public void InsertSnippet(Snippet saveThis)
        {
            DataContext dc = new DataContext(_cStr);
            Table<Snippet> snippets = dc.GetTable<Snippet>();
            snippets.InsertOnSubmit(saveThis);
            dc.SubmitChanges();
        }

        public Snippet GetSnippet(int pID)
        {
            var dc = new DataContext(_cStr);
            var snippets = dc.GetTable<Snippet>();
            IEnumerable<Snippet> snippetResults = from s in snippets
                          where s.snippetID == pID
                          select s;
            dc.Log = new DebuggerWriter();
            foreach(var s in snippetResults)
            {
                return new Snippet(s.snippetName, s.snippetContent, s.snippetTags, s.snippetLang, s.snippetDescription)
                           {snippetID = s.snippetID};
            }
            return null;


        }
    }
}