using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SQLite;
using System.IO;
using System.Data;

namespace SQLite
{
     static class Info
    {
        public static string dbFileName = "SQLiteEx.db";
        public static string path = @"C:\Users\augus\Desktop\кроссплатформенные базы данных\SQLite\SQLite\bin\";
        public static string connectionString = @"Data Source="+path+
            Info.dbFileName + "; " +
            "Version=3;" +
            "Foreign Keys = True;";
        public static SQLiteConnection mDbConn = new SQLiteConnection(connectionString);
        public static SQLiteCommand mSqlCmd=new SQLiteCommand();
    }
    class Program
    {
        static void Create()
        {
            if (!File.Exists(Info.dbFileName))
                SQLiteConnection.CreateFile(Info.dbFileName);
            try
            {
                //Info.mDbConn = new SQLiteConnection("Data Source=" + Info.dbFileName + ";Version=3;Foreign Keys=True;");
                //Info.mDbConn = new SQLiteConnection(Info.connectionString);
                Info.mDbConn.Open();
                Info.mSqlCmd.Connection = Info.mDbConn;

                Info.mSqlCmd.CommandText = "CREATE TABLE IF NOT EXISTS Language " +
                    "(Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE," +
                    " LangTitle TEXT NOT NULL UNIQUE CHECK(LangTitle<> '')";
                Info.mSqlCmd.ExecuteNonQuery();

                Info.mSqlCmd.CommandText = "CREATE TABLE Author (" +
                    "Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE," +
                    "Name TEXT NOT NULL UNIQUE," +
                    "Nationality TEXT CHECK(Nationality<> '')," +
                    "BirthDay INTEGER CHECK(BirthDay > 0 AND BirthDay < 32)," +
                    "BirthMonth INTEGER CHECK(BirthMonth > 0 AND BirthMonth < 13)," +
                    "BirthYear INTEGER CHECK(BirthYear > 0 AND BirthYear < 5000))";
                Info.mSqlCmd.ExecuteNonQuery();

                Info.mSqlCmd.CommandText = "CREATE TABLE Quote (" +
                    "Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE," +
                    "Content TEXT NOT NULL UNIQUE CHECK(Content <> '')," +
                    "AuthorId INTEGER NOT NULL DEFAULT '1' REFERENCES Author(Id) _" +
                    "ON DELETE RESTRICT ON UPDATE RESTRICT," +
                    "LanguageId INTEGER NOT NULL DEFAULT '1' _" +
                    "REFERENCES Language(Id) ON DELETE RESTRICT ON UPDATE RESTRICT)";
                Info.mSqlCmd.ExecuteNonQuery();

                Console.WriteLine("Connected");
            }
            catch (SQLiteException ex)
            {
                Console.WriteLine("Disconnected Error:" + ex.Message);
            }
            Info.mDbConn.Close();
            Console.ReadKey();
        }

        static void Connect()
        {
            if (!File.Exists(Info.dbFileName))
                Console.WriteLine("Please, create DB and blank table (Push \"Create\" button)");

            try
            {
                //Info.mDbConn = new SQLiteConnection("Data Source=" + Info.dbFileName + ";Version=3;Foreign Keys=True;");
                Info.mDbConn.Open();
                Info.mSqlCmd.Connection = Info.mDbConn;

                Console.WriteLine("Connected");
            }
            catch (SQLiteException ex)
            {
                Console.WriteLine("Disconnected Error:" + ex.Message);
            }
            Console.ReadKey();
        }

        static void Exit()
        {
            Console.WriteLine("приложение заканчивает работу");
            Environment.Exit(0);
        }

        static void ReadTable(string value)
        {
            DataTable dTable = new DataTable();
            String sqlQuery;
            if (Info.mDbConn.State != ConnectionState.Open)
            {
                Console.WriteLine("Open connection with database");
                return;
            }
            try
            {
                sqlQuery = "SELECT * FROM "+value;
                SQLiteDataAdapter adapter = new SQLiteDataAdapter(sqlQuery, Info.mDbConn);
                adapter.Fill(dTable);

                if (dTable.Rows.Count > 0)
                {
                    Console.Clear();

                    for (int i = 0; i < dTable.Rows.Count; i++)
                        for (int j = 0; j < dTable.Rows[i].ItemArray.Length; j++)
                            Console.WriteLine(dTable.Rows[i].ItemArray[j]);
                    Console.WriteLine('\n');
                }
                else
                    Console.WriteLine("Language is empty");
            }
            catch (SQLiteException ex)
            {
                Console.WriteLine("Error: " + ex.Message);
            }
            //Info.mDbConn.Close();
        }

        static void Read()

        {
            ReadTable("Language");
            //ReadTable("Author");
            //ReadTable("Quote");
            Console.ReadKey();

        }

        static int UpdateLang(int id, string newLangTitle)
        {
            int result = -1;
            if (Info.mDbConn.State != ConnectionState.Open)
            {
                Console.WriteLine("Open connection with database");
                return result;
            }
            Info.mSqlCmd.CommandText = "UPDATE Language "
                        + "SET LangTitle = @Lang "
                        + "WHERE Id = @Id";
            Info.mSqlCmd.Prepare();
            Info.mSqlCmd.Parameters.AddWithValue("@Lang", newLangTitle);
            Info.mSqlCmd.Parameters.AddWithValue("@Id", id);
            try
            {
                result = Info.mSqlCmd.ExecuteNonQuery();
            }
            catch (SQLiteException ex)
            {
                Console.WriteLine("Error: " + ex.Message);
            }
            //Info.mDbConn.Close();
            return result;
        }
        static void Update()
        {
            Console.WriteLine("пример обновления данных таблицы Language");
            Console.Write("ввод id：");
            int id = Convert.ToInt32(Console.ReadLine());
            Console.Write("ввод LangTitle：");
            string LangTitle = Console.ReadLine();
            UpdateLang(id, LangTitle);
            Console.ReadKey();
        }
        static int DeleteLang(int id)
        {
            int result = -1;
            if (Info.mDbConn.State != ConnectionState.Open)
            {
                Console.WriteLine("Open connection with database");
                return result;
            }
            Info.mSqlCmd.CommandText = "Delete from Language where Id=@I";
            Info.mSqlCmd.Prepare();
            Info.mSqlCmd.Parameters.AddWithValue("@I", id);
            try
            {
                result = Info.mSqlCmd.ExecuteNonQuery();
            }
            catch (SQLiteException ex)
            {
                Console.WriteLine("Error: " + ex.Message);
            }
            //Info.mDbConn.Close();
            return result;
        }
        static void Delete()
        {
            Console.WriteLine("пример удаления данных таблицы Language");
            Console.Write("ввод id：");
            int id = Convert.ToInt32(Console.ReadLine());
            DeleteLang(id);
            Console.ReadKey();
        }
        static int AddLang(string LangTitle)
        {
            int result = -1;
            if (Info.mDbConn.State != ConnectionState.Open)
            {
                Console.WriteLine("Open connection with database");
                return result;
            }
            Info.mSqlCmd.CommandText = "Insert into Language(LangTitle) VALUES (@lang)";
            Info.mSqlCmd.Prepare();
            Info.mSqlCmd.Parameters.AddWithValue("@Lang", LangTitle);
            try
            {
                result = Info.mSqlCmd.ExecuteNonQuery();
            }
            catch (SQLiteException ex)
            {
                Console.WriteLine("Error: " + ex.Message);
            }
            //Info.mDbConn.Close();
            return result;
        }
        static void Add()
        {
            Console.WriteLine("пример добавления данных таблицы Language");
            Console.Write("ввод LangTitle：");
            string LangTitle = Console.ReadLine();
            AddLang(LangTitle);
            Console.ReadKey();
        }

        static void Main(string[] args)
        {
            while (true)
            {
                int counter;
                string[] items = { "создание базы данных", "подключение", "чтение", "обновление", "удаление", "добавление", "Выход" };

                PrintMenu(items, out counter);
                Console.Clear();
                select(counter);
            }
        }
        static int PrintMenu(string[] menuitems, out int counter)
        {


            counter = 0;
            ConsoleKeyInfo key;
            do
            {
                Console.Clear();
                for (int i = 0; i < menuitems.Length; i++)
                {
                    if (counter == i)
                    {
                        Console.BackgroundColor = ConsoleColor.White;
                        Console.ForegroundColor = ConsoleColor.Black;
                        Console.WriteLine(menuitems[i]);
                        Console.BackgroundColor = ConsoleColor.Black;
                        Console.ForegroundColor = ConsoleColor.White;
                    }
                    else
                        Console.WriteLine(menuitems[i]);

                }
                key = Console.ReadKey();
                if (key.Key == ConsoleKey.UpArrow)
                {
                    counter--;
                    if (counter == -1) counter = menuitems.Length - 1;
                }
                if (key.Key == ConsoleKey.DownArrow)
                {
                    counter++;
                    if (counter == menuitems.Length) counter = 0;
                }
            }
            while (key.Key != ConsoleKey.Enter);
            return counter;
        }

        static void select(int sel)
        {
            switch (sel)
            {
                case 0:Create();
                    break;
                case 1:Connect();
                    break;
                case 2:Read();
                    break;
                case 3:Update();
                    break;
                case 4:Delete();
                    break;
                case 5:Add();
                    break;
                default:
                    Exit();
                    break;
            }
        }
    }
}