using System.Text.Json;
using Microsoft.Data.Sqlite;

namespace ROIEditor
{

    public class ListaPersistente<T>
    {
        private readonly string connectionString;
        private readonly string tableName;
        private List<T> data = new();
        public string Clave;

        public void Add(T item)
        {
            data.Add(item);
        }

        public void Remove(T item)
        {
            data.Remove(item);
        }

        public void Clear()
        {
            data.Clear();
        }

        public List<T> GetList()
        {
            return data;
        }

        public int IndexOf(T item)
        {
            return data.IndexOf(item);
        }


        public int Count => data.Count;

        public ListaPersistente(string dbPath, string tableName)
        {
            connectionString = $"Data Source={dbPath}";
            this.tableName = tableName;
            InitializeDatabase();
        }

        private void InitializeDatabase()
        {
            using (var connection = new SqliteConnection(connectionString))
            {
                connection.Open();

                var command = connection.CreateCommand();
                command.CommandText = $@"
                CREATE TABLE IF NOT EXISTS {tableName} (
                    Id INTEGER PRIMARY KEY AUTOINCREMENT,
                    Clave TEXT,
                    SerializeData TEXT
                )";
                command.ExecuteNonQuery();
            }
        }

        public void Save()
        {
            Save(Clave);
        }

        public void Save(string clave)
        {
            if (clave == null) return;
            Clave = clave;

            using (var connection = new SqliteConnection(connectionString))
            {
                connection.Open();

                var deleteCommand = connection.CreateCommand();
                deleteCommand.CommandText = $"DELETE FROM {tableName} WHERE Clave = $Clave";
                deleteCommand.Parameters.AddWithValue("$Clave", clave);
                deleteCommand.ExecuteNonQuery();

                foreach (var elemento in data)
                {
                    string serializedData = JsonSerializer.Serialize(elemento);

                    var command = connection.CreateCommand();
                    command.CommandText = $"INSERT INTO {tableName} (Clave, SerializeData) VALUES ($Clave, $SerializedData)";
                    command.Parameters.AddWithValue("$Clave", clave);
                    command.Parameters.AddWithValue("$SerializedData", serializedData);
                    command.ExecuteNonQuery();
                }
            }
        }

        public void Load() { Load(Clave); }

        public void Load(string clave)
        {
            data.Clear();
            if (clave == null) return;
            Clave = clave;

            using (var connection = new SqliteConnection(connectionString))
            {
                connection.Open();

                var command = connection.CreateCommand();
                command.CommandText = $"SELECT SerializeData FROM {tableName} WHERE Clave = $Clave";
                command.Parameters.AddWithValue("$Clave", clave);

                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        string serializedData = reader.GetString(0);
                        data.Add( JsonSerializer.Deserialize<T>(serializedData) );
                    }
                }
            }
        }
    }

  

}