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

namespace MatrixCalculator
{
    class WorksheetFile
    {
        private string filename = "";
        private Queue<SQLiteCommand> queue = new Queue<SQLiteCommand>();

        public string FileName
        {
            get { return filename; }
            set { filename = value; }
        }

        /// <summary>
        /// Creates a new worksheet that will be destroyed at exit
        /// </summary>
        public WorksheetFile() 
        {
            queue.Enqueue(new SQLiteCommand("CREATE TABLE matrices (name TEXT PRIMARY KEY, description TEXT, csv TEXT)"));
        }

        /// <summary>
        /// Opens an existing worksheet
        /// </summary>
        public WorksheetFile(string filename)
        {
            this.filename = filename;
            if (!System.IO.File.Exists(filename)) throw new Exception("Could not open file.");
        }

        public bool IsQueueEmpty {
            get {
                if (queue.Count == 1 && queue.Peek().CommandText.StartsWith("CREATE TABLE matrices")) return true; 
                return queue.Count == 0; 
            } 
        }

        public void Save()
        {
            System.Diagnostics.Debug.Assert(filename != "");
            
            // Open database
            SQLiteConnection connection = new SQLiteConnection("Data Source=" + filename + ";Version=3");
            connection.Open();

            // Write changes
            while (queue.Count > 0)
            {
                var cmd = queue.Dequeue();
                cmd.Connection = connection;
                cmd.ExecuteNonQuery();
                cmd.Dispose();
            }

            // Done
            connection.Close();
            connection.Dispose();
        }

        public void SaveAs(string new_file)
        {
            // Copy old file
            if (System.IO.File.Exists(filename))
                System.IO.File.Copy(filename, new_file, true);

            filename = new_file;
            
            // Save file
            Save();
        }

        public DataTable Read()
        {
            if (!System.IO.File.Exists(filename)) throw new Exception("File not found.");

            // Open connection
            SQLiteConnection connection = new SQLiteConnection("Data Source=" + filename + ";Version=3;");
            connection.Open();

            // Set up command
            SQLiteCommand command = connection.CreateCommand();
            command.CommandText = "SELECT * FROM matrices";
            
            // Read data
            SQLiteDataReader reader = command.ExecuteReader();

            // Load in table
            DataTable table = new DataTable();
            table.Load(reader);

            // Cleanup
            reader.Close();
            reader.Dispose();
            command.Dispose();
            connection.Close();
            connection.Dispose();

            return table;
        }
        
        public void AddMatrix(string name, string csv, string description = "")
        {
            SQLiteCommand cmd = new SQLiteCommand("INSERT INTO matrices VALUES (@name, @desc, @csv)");
            cmd.Parameters.AddWithValue("@name", name);
            cmd.Parameters.AddWithValue("@desc", description);
            cmd.Parameters.AddWithValue("@csv", csv);

            queue.Enqueue(cmd);
        }

        public void DeleteMatrix(string name)
        {
            SQLiteCommand cmd = new SQLiteCommand("DELETE FROM matrices WHERE name=@name");
            cmd.Parameters.AddWithValue("@name", name);
            queue.Enqueue(cmd);
        }

        public void ModifyMatrix(string old_name, string new_name, string csv, string description = "")
        {
            SQLiteCommand cmd = new SQLiteCommand("UPDATE matrices SET name=@newname, description=@desc, csv=@csv WHERE name=@oldname");
            cmd.Parameters.AddWithValue("@oldname", old_name);
            cmd.Parameters.AddWithValue("@newname", new_name);
            cmd.Parameters.AddWithValue("@desc", description);
            cmd.Parameters.AddWithValue("@csv", csv);
            
            queue.Enqueue(cmd);
        }
    }
}