using System;
using System.Data.SQLite;
using System.IO;

namespace Sqlite
{
    public class Program
    {
        private static string dir = Path.Combine(Environment
    .GetFolderPath(Environment.SpecialFolder.ApplicationData), "Peppemess");
        private static string databaseName = Path.Combine(dir, "PeppemessDB.sqlite");

        static void Main(string[] args)
        {
            CreateDatabase();
            CreateQuote("Quote", "Author");
            CreatePhonenumber("012345");
        }

        private static void CreateQuote(string quote, string author)
        {
            using (var dbConnection = new SQLiteConnection("Data Source=" + databaseName))
            {
                dbConnection.Open();

                var sqlCommand = "INSERT INTO Quotes(Quote, Author) VALUES('" + quote + "', '" + author + "')";

                using (SQLiteCommand command = new SQLiteCommand(sqlCommand, dbConnection))
                {
                    command.ExecuteNonQuery();
                    dbConnection.Close();
                }
            }
        }
        private static void CreatePhonenumber(string phoneNumber)
        {
            using (var dbConnection = new SQLiteConnection("Data Source=" + databaseName))
            {
                dbConnection.Open();

                var sqlCommand = "INSERT INTO Phonenumbers(Phonenumber, DateCreated, IsDeleted) VALUES('" + phoneNumber + "', '" + DateTime.Now + "', false)";

                using (SQLiteCommand command = new SQLiteCommand(sqlCommand, dbConnection))
                {
                    command.ExecuteNonQuery();
                    dbConnection.Close();
                }
            }
        }


        private static void CreateDatabase()
        {
            // Create system result database
            if (!Directory.Exists(dir))
                Directory.CreateDirectory(dir);
            SQLiteConnection.CreateFile(databaseName);

            // Connect to system result database
            using (var dbConnection = new SQLiteConnection("Data Source=" + databaseName))
            {
                dbConnection.Open();

                using (var transaction = dbConnection.BeginTransaction())
                {
                    // Create table
                    //Spara i en tabell: telefonnummer, utskickstid, namn, sign-up-tid, isdeleted
                    var sqlCommand = "CREATE TABLE Phonenumbers (Phonenumber NVARCHAR(450) PRIMARY KEY, " +
                        "DateCreated DATETIME, " +
                        "TimeToSend DATETIME, " +
                        "Name NVARCHAR(450)," +
                        "IsDeleted bit)";

                    using (SQLiteCommand command = new SQLiteCommand(sqlCommand, dbConnection))
                    {
                        command.ExecuteNonQuery();
                    }

                    sqlCommand = "CREATE TABLE Quotes (ID INTEGER PRIMARY KEY, Quote nvarchar(140), Author nvarchar(20), SendTime DATETIME)";

                    using (SQLiteCommand command = new SQLiteCommand(sqlCommand, dbConnection))
                    {
                        command.ExecuteNonQuery();
                    }

                    transaction.Commit();
                }

                dbConnection.Close();
            }
        }

    }
}