using System;
using System.Data;
using MySql.Data.MySqlClient;
using Dapper;
using ContactManager.Models;

namespace ContactManager.Helpers
{
    public class DbHelper
    {
        private readonly string _connectionString;

        public DbHelper(IConfiguration configuration)
        {
            _connectionString = configuration.GetConnectionString("DefaultConnection");
        }

        private IDbConnection CreateConnection()
        {
            try
            {
                var connection = new MySqlConnection(_connectionString);
                connection.Open();
                return connection;
            }
            catch (MySqlException ex)
            {
                Console.WriteLine($"Error creating database connection: {ex.Message}");
                throw;
            }
        }

        public async Task<IEnumerable<Contact>> GetAllContacts()
        {
            try
            {
                using var dbConnection = CreateConnection();
                return await dbConnection.QueryAsync<Contact>("SELECT * FROM Contacts");
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Error in GetAllContacts: {ex.Message}");
                throw;
            }
        }

        public async Task<Contact> GetContact(int id)
        {
            try
            {
                using var dbConnection = CreateConnection();
                return await dbConnection.QueryFirstOrDefaultAsync<Contact>(
                    "SELECT * FROM Contacts WHERE Id = @Id",
                    new { Id = id }
                );
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Error in GetContact: {ex.Message}");
                throw;
            }
        }

        public async Task<int> AddContact(Contact contact)
        {
            try
            {
                using var dbConnection = CreateConnection();
                return await dbConnection.ExecuteAsync(
                    "INSERT INTO Contacts (FirstName, LastName) VALUES (@FirstName, @LastName)",
                    contact
                );
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Error in AddContact: {ex.Message}");
                throw;
            }
        }

        public async Task<int> UpdateContact(Contact contact)
        {
            try
            {
                using var dbConnection = CreateConnection();
                return await dbConnection.ExecuteAsync(
                    "UPDATE Contacts SET FirstName = @FirstName, LastName = @LastName WHERE Id = @Id",
                    contact
                );
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Error in UpdateContact: {ex.Message}");
                throw;
            }
        }

        public async Task<int> DeleteContact(int id)
        {
            try
            {
                using var dbConnection = CreateConnection();
                return await dbConnection.ExecuteAsync(
                    "DELETE FROM Contacts WHERE Id = @Id",
                    new { Id = id }
                );
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Error in DeleteContact: {ex.Message}");
                throw;
            }
        }
    }
}