using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Data.Sqlite;
using System.Collections.ObjectModel;
namespace NotesDataAnalyst.NoteContent
{


    public class DatabaseContext
    {
        private readonly string _connectionString;

        public DatabaseContext(string dbPath)
        {
            _connectionString = $"Data Source={dbPath}";
            InitializeDatabase();
        }

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

                var command = connection.CreateCommand();
                command.CommandText =
                @"
            CREATE TABLE IF NOT EXISTS Notes (
                Id TEXT PRIMARY KEY,
                Category TEXT,
                Title TEXT,
                Image TEXT,
                Text TEXT,
                DateCreated TEXT
            );
            ";
                command.ExecuteNonQuery();
            }
        }

        public ObservableCollection<Note> GetAllNotes()
        {
            var notes = new ObservableCollection<Note>();

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

                var command = connection.CreateCommand();
                command.CommandText = "SELECT * FROM Notes";
                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var note = new Note
                        {
                            Id = reader.GetString(0),
                            Category = reader.GetString(1),
                            Title = reader.GetString(2),
                            Image = reader.GetString(3),
                            Text = reader.GetString(4), // Загружаем текст из БД
                            DateCreated = DateTime.TryParse(reader.GetString(5), out var date) ? date : DateTime.UtcNow
                        };

                        notes.Add(note);
                    }
                }
            }

            return notes;
        }

        public void SaveNote(Note note)
        {
            using (var connection = new SqliteConnection(_connectionString))
            {
                connection.Open();

                var command = connection.CreateCommand();
                command.CommandText =
                @"
            INSERT OR REPLACE INTO Notes (Id, Category, Title, Image, Text, DateCreated)
            VALUES ($id, $category, $title, $image, $text, $dateCreated)
            ";
                command.Parameters.AddWithValue("$id", note.Id);
                command.Parameters.AddWithValue("$category", note.Category);
                command.Parameters.AddWithValue("$title", note.Title);
                command.Parameters.AddWithValue("$image", note.Image);
                command.Parameters.AddWithValue("$text", note.Text);
                command.Parameters.AddWithValue("$dateCreated", note.DateCreated.ToString("o")); // Сохраняем в формате ISO
                command.ExecuteNonQuery();

            }
        }

        public void DeleteNote(Note note)
        {
            if (string.IsNullOrEmpty(note.Id))
            {
                Console.WriteLine("Ошибка: У заметки отсутствует Id.");
                return;
            }

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

                var command = connection.CreateCommand();
                command.CommandText =
                @"
            DELETE FROM Notes
            WHERE Id = $id
            ";
                command.Parameters.AddWithValue("$id", note.Id);
                command.ExecuteNonQuery();
                int result = command.ExecuteNonQuery();

                Console.WriteLine(result > 0 ? "Заметка удалена из базы данных." : "Ошибка: Заметка не найдена в базе данных.");
            }
        }
    }

}