using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using RestSharp;

namespace HorseRacingPredictor.Football.Database
{
    /// <summary>
    /// Classe per la gestione della tabella API_Response che memorizza le risposte API prima dell'elaborazione
    /// </summary>
    internal class APIResponse : HorseRacingPredictor.Football.Manager.Database
    {
        /// <summary>
        /// Inserisce una nuova risposta API nel database
        /// </summary>
        /// <param name="apiType">Tipo di API (es. FootballFixtures, FootballOdds)</param>
        /// <param name="endpoint">Endpoint API chiamato</param>
        /// <param name="parameters">Parametri utilizzati nella chiamata</param>
        /// <param name="response">Risposta API (RestResponse)</param>
        /// <returns>ID della risposta inserita</returns>
        public int InsertApiResponse(string apiType, string endpoint, string parameters, RestResponse response)
        {
            int responseId = 0;

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

                    string query = @"
                        INSERT INTO API_Response (
                            timestamp, api_type, endpoint, parameters, 
                            response_content, status_code, processed, processed_timestamp, error_message
                        ) VALUES (
                            @timestamp, @apiType, @endpoint, @parameters, 
                            @responseContent, @statusCode, @processed, NULL, @errorMessage
                        );
                        SELECT SCOPE_IDENTITY();";

                    using (var command = new SqlCommand(query, connection))
                    {
                        command.Parameters.AddWithValue("@timestamp", DateTime.Now);
                        command.Parameters.AddWithValue("@apiType", apiType);
                        command.Parameters.AddWithValue("@endpoint", endpoint);
                        command.Parameters.AddWithValue("@parameters", parameters ?? (object)DBNull.Value);
                        command.Parameters.AddWithValue("@responseContent", response.Content);
                        command.Parameters.AddWithValue("@statusCode", (int)response.StatusCode);
                        command.Parameters.AddWithValue("@processed", false);
                        command.Parameters.AddWithValue("@errorMessage", 
                            response.IsSuccessful ? DBNull.Value : (object)(response.ErrorMessage ?? "Unknown error"));

                        var result = command.ExecuteScalar();
                        if (result != null && result != DBNull.Value)
                        {
                            responseId = Convert.ToInt32(result);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                LogError("inserimento risposta API", ex);
                throw;
            }

            return responseId;
        }

        /// <summary>
        /// Aggiorna lo stato di elaborazione di una risposta API
        /// </summary>
        /// <param name="responseId">ID della risposta API</param>
        /// <param name="processed">Stato di elaborazione</param>
        /// <param name="errorMessage">Messaggio di errore (se presente)</param>
        public void UpdateProcessingStatus(int responseId, bool processed, string errorMessage = null)
        {
            try
            {
                using (var connection = new SqlConnection(_connectionString))
                {
                    connection.Open();

                    string query = @"
                        UPDATE API_Response 
                        SET processed = @processed, 
                            processed_timestamp = @processedTimestamp,
                            error_message = @errorMessage
                        WHERE id = @id";

                    using (var command = new SqlCommand(query, connection))
                    {
                        command.Parameters.AddWithValue("@processed", processed);
                        command.Parameters.AddWithValue("@processedTimestamp", processed ? DateTime.Now : (object)DBNull.Value);
                        command.Parameters.AddWithValue("@errorMessage", errorMessage ?? (object)DBNull.Value);
                        command.Parameters.AddWithValue("@id", responseId);

                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                LogError($"aggiornamento stato elaborazione risposta API ID:{responseId}", ex);
                throw;
            }
        }

        /// <summary>
        /// Ottiene le risposte API non elaborate per un determinato tipo
        /// </summary>
        /// <param name="apiType">Tipo di API (opzionale)</param>
        /// <returns>DataTable con le risposte API non elaborate</returns>
        public DataTable GetUnprocessedResponses(string apiType = null)
        {
            DataTable dataTable = new DataTable();

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

                    StringBuilder queryBuilder = new StringBuilder(@"
                        SELECT id, timestamp, api_type, endpoint, parameters, 
                               response_content, status_code, processed
                        FROM API_Response
                        WHERE processed = 0 AND status_code = 200");

                    if (!string.IsNullOrEmpty(apiType))
                    {
                        queryBuilder.Append(" AND api_type = @apiType");
                    }

                    queryBuilder.Append(" ORDER BY timestamp ASC");

                    using (var command = new SqlCommand(queryBuilder.ToString(), connection))
                    {
                        if (!string.IsNullOrEmpty(apiType))
                        {
                            command.Parameters.AddWithValue("@apiType", apiType);
                        }

                        using (var adapter = new SqlDataAdapter(command))
                        {
                            adapter.Fill(dataTable);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                LogError("recupero risposte API non elaborate", ex);
                throw;
            }

            return dataTable;
        }

        /// <summary>
        /// Ottiene una specifica risposta API dal database
        /// </summary>
        /// <param name="responseId">ID della risposta API</param>
        /// <returns>DataRow con i dati della risposta API o null se non trovata</returns>
        public DataRow GetApiResponseById(int responseId)
        {
            DataTable dataTable = new DataTable();

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

                    string query = @"
                        SELECT id, timestamp, api_type, endpoint, parameters, 
                               response_content, status_code, processed
                        FROM API_Response
                        WHERE id = @id";

                    using (var command = new SqlCommand(query, connection))
                    {
                        command.Parameters.AddWithValue("@id", responseId);

                        using (var adapter = new SqlDataAdapter(command))
                        {
                            adapter.Fill(dataTable);
                        }
                    }
                }

                if (dataTable.Rows.Count > 0)
                {
                    return dataTable.Rows[0];
                }
            }
            catch (Exception ex)
            {
                LogError($"recupero risposta API ID:{responseId}", ex);
                throw;
            }

            return null;
        }
    }
}
