// Questo file fa parte di MyTown.
// MyTown è software libero: puoi ridistribuirlo e/o modificarlo secondo
// i termini della GNU Affero General Public License come pubblicata dalla
// Free Software Foundation, versione 3.
// Distribuito senza ALCUNA GARANZIA; vedi la GNU AGPL v3 per i dettagli.
// Copyright (C) Kondor Consulting & Services S.r.l.
// SPDX-License-Identifier: AGPL-3.0-or-later

using Npgsql;
using System;
using System.Collections.Generic;

namespace KondorCS.ScheduleLibrary.AIFileProcessor
{
    /// <summary>
    /// Controller per le operazioni sul database PostgreSQL
    /// </summary>
    public class DBController
    {
        private string mConnDB = string.Empty;

        public DBController(string ConnDB)
        {
            mConnDB = ConnDB;
        }

        /// <summary>
        /// Recupera la lista dei file da elaborare dalla tabella schedule_process
        /// con sp_status_detail = 'in attesa di lettura dell'ai'
        /// Evita di prendere file già in elaborazione da altri processi
        /// </summary>
        public List<FileUpload> GetFilesToProcess()
        {
            List<FileUpload> lstFiles = new List<FileUpload>();

            try
            {
                using (var conn = new NpgsqlConnection(mConnDB))
                {
                    conn.Open();
                    using (var command = new NpgsqlCommand(
                        @"SELECT fu.*
                          FROM file_upload fu
                          WHERE EXISTS (
                              SELECT 1
                              FROM schedule_process sp
                              WHERE sp.sp_guid_detail::text = fu.fu_sp_guid_detail::text
                              AND sp.sp_status_detail = 'in attesa di lettura dell''ai'
                          )
                          -- Evita file già in elaborazione (race condition protection)
                          AND NOT EXISTS (
                              SELECT 1
                              FROM schedule_process sp2
                              WHERE sp2.sp_guid_detail::text = fu.fu_sp_guid_detail::text
                              AND sp2.sp_status_detail = 'in elaborazione'
                          )
                          ORDER BY fu.fu_id
                          LIMIT 20", conn))
                {
                    command.CommandType = System.Data.CommandType.Text;

                    using (NpgsqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            FileUpload file = new FileUpload
                            {
                                Fu_id = reader.GetInt32(reader.GetOrdinal("fu_id")),
                                Fu_fui_id = reader.IsDBNull(reader.GetOrdinal("fu_fui_id")) ? (int?)null : reader.GetInt32(reader.GetOrdinal("fu_fui_id")),
                                Fu_ft_id = reader.IsDBNull(reader.GetOrdinal("fu_ft_id")) ? (int?)null : reader.GetInt32(reader.GetOrdinal("fu_ft_id")),
                                Fu_path = reader.IsDBNull(reader.GetOrdinal("fu_path")) ? null : reader.GetString(reader.GetOrdinal("fu_path")),
                                Fu_ai_response = reader.IsDBNull(reader.GetOrdinal("fu_ai_response")) ? null : reader.GetString(reader.GetOrdinal("fu_ai_response")),
                                Fu_name = reader.IsDBNull(reader.GetOrdinal("fu_name")) ? null : reader.GetString(reader.GetOrdinal("fu_name")),
                                Fu_ai_status = reader.IsDBNull(reader.GetOrdinal("fu_ai_status")) ? 0 : reader.GetInt32(reader.GetOrdinal("fu_ai_status"))
                            };
                            lstFiles.Add(file);
                        }
                    }
                }
                }

                return lstFiles;
            }
            catch (Npgsql.PostgresException pgEx)
            {
                Console.WriteLine($"[AI ERROR] Errore PostgreSQL in GetFilesToProcess: {pgEx.SqlState} - {pgEx.Message}");
                throw;
            }
            catch (Exception ex)
            {
                Console.WriteLine($"[AI ERROR] Errore in GetFilesToProcess: {ex.Message}");
                throw;
            }
        }

        /// <summary>
        /// Recupera un FileUpload dal database tramite fu_id
        /// </summary>
        public FileUpload GetFileUploadById(int fu_id)
        {
            FileUpload file = null;

            using (var conn = new NpgsqlConnection(mConnDB))
            {
                conn.Open();
                using (var command = new NpgsqlCommand("SELECT * FROM file_upload WHERE fu_id = @fu_id", conn))
                {
                    command.Parameters.AddWithValue("@fu_id", fu_id);
                    command.CommandType = System.Data.CommandType.Text;

                    using (NpgsqlDataReader reader = command.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            file = new FileUpload
                            {
                                Fu_id = reader.GetInt32(reader.GetOrdinal("fu_id")),
                                Fu_fui_id = reader.IsDBNull(reader.GetOrdinal("fu_fui_id")) ? (int?)null : reader.GetInt32(reader.GetOrdinal("fu_fui_id")),
                                Fu_ft_id = reader.IsDBNull(reader.GetOrdinal("fu_ft_id")) ? (int?)null : reader.GetInt32(reader.GetOrdinal("fu_ft_id")),
                                Fu_path = reader.IsDBNull(reader.GetOrdinal("fu_path")) ? null : reader.GetString(reader.GetOrdinal("fu_path")),
                                Fu_ai_response = reader.IsDBNull(reader.GetOrdinal("fu_ai_response")) ? null : reader.GetString(reader.GetOrdinal("fu_ai_response")),
                                Fu_name = reader.IsDBNull(reader.GetOrdinal("fu_name")) ? null : reader.GetString(reader.GetOrdinal("fu_name")),
                                Fu_ai_status = reader.IsDBNull(reader.GetOrdinal("fu_ai_status")) ? 0 : reader.GetInt32(reader.GetOrdinal("fu_ai_status"))
                            };
                        }
                    }
                }
            }

            return file;
        }

        /// <summary>
        /// Recupera il TypeFile associato a un file upload
        /// </summary>
        public TypeFile GetTypeFileByFuId(int pt_id, int user_id, int fu_id)
        {
            TypeFile typeFile = null;

            try
            {
                using (var conn = new NpgsqlConnection(mConnDB))
                {
                    conn.Open();
                    using (var command = new NpgsqlCommand("SELECT * FROM drive_file_type_get_by_fu_id(@fu_id)", conn))
                    {
                        command.Parameters.AddWithValue("@fu_id", fu_id);
                        command.CommandType = System.Data.CommandType.Text;

                    using (NpgsqlDataReader reader = command.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            typeFile = new TypeFile
                            {
                                Ft_id = reader.GetInt32(reader.GetOrdinal("ft_id")),
                                Ft_name = reader.IsDBNull(reader.GetOrdinal("ft_name")) ? null : reader.GetString(reader.GetOrdinal("ft_name")),
                                Ft_form = reader.IsDBNull(reader.GetOrdinal("ft_form")) ? null : reader.GetString(reader.GetOrdinal("ft_form")),
                                Ft_fg_id = reader.IsDBNull(reader.GetOrdinal("ft_fg_id")) ? (int?)null : reader.GetInt32(reader.GetOrdinal("ft_fg_id")),
                                Ft_desc = reader.IsDBNull(reader.GetOrdinal("ft_desc")) ? null : reader.GetString(reader.GetOrdinal("ft_desc")),
                                Ft_multi = reader.IsDBNull(reader.GetOrdinal("ft_multi")) ? (bool?)null : reader.GetBoolean(reader.GetOrdinal("ft_multi")),
                                Ft_relative_path = reader.IsDBNull(reader.GetOrdinal("ft_relative_path")) ? null : reader.GetString(reader.GetOrdinal("ft_relative_path"))
                            };

                            // Leggi le colonne fui_* (la funzione le ritorna)
                            int fui_id = reader.IsDBNull(reader.GetOrdinal("fui_id")) ? 0 : reader.GetInt32(reader.GetOrdinal("fui_id"));
                            int fui_fg_id = reader.IsDBNull(reader.GetOrdinal("fui_fg_id")) ? 0 : reader.GetInt32(reader.GetOrdinal("fui_fg_id"));
                            string fui_name = reader.IsDBNull(reader.GetOrdinal("fui_name")) ? null : reader.GetString(reader.GetOrdinal("fui_name"));

                            // Carica anche i dati del file associato se presenti
                            if (!reader.IsDBNull(reader.GetOrdinal("fu_id")))
                            {
                                typeFile.Files = new FileUpload
                                {
                                    Fu_id = reader.GetInt32(reader.GetOrdinal("fu_id")),
                                    Fu_fui_id = reader.IsDBNull(reader.GetOrdinal("fu_fui_id")) ? (int?)null : reader.GetInt32(reader.GetOrdinal("fu_fui_id")),
                                    Fu_ft_id = reader.IsDBNull(reader.GetOrdinal("fu_ft_id")) ? (int?)null : reader.GetInt32(reader.GetOrdinal("fu_ft_id")),
                                    Fu_path = reader.IsDBNull(reader.GetOrdinal("fu_path")) ? null : reader.GetString(reader.GetOrdinal("fu_path")),
                                    Fu_ai_response = reader.IsDBNull(reader.GetOrdinal("fu_ai_response")) ? null : reader.GetString(reader.GetOrdinal("fu_ai_response")),
                                    Fu_name = reader.IsDBNull(reader.GetOrdinal("fu_name")) ? null : reader.GetString(reader.GetOrdinal("fu_name")),
                                    Fu_ai_status = reader.IsDBNull(reader.GetOrdinal("fu_ai_status")) ? 0 : reader.GetInt32(reader.GetOrdinal("fu_ai_status"))
                                };
                            }

                            // Leggi anche fu_sp_guid_detail anche se non lo usiamo (la funzione lo ritorna come UUID)
                            Guid? fu_sp_guid_detail = reader.IsDBNull(reader.GetOrdinal("fu_sp_guid_detail")) ? (Guid?)null : reader.GetGuid(reader.GetOrdinal("fu_sp_guid_detail"));
                        }
                    }
                }
                }

                return typeFile;
            }
            catch (Npgsql.PostgresException pgEx)
            {
                Console.WriteLine($"[AI ERROR] Errore PostgreSQL in GetTypeFileByFuId per fu_id={fu_id}: {pgEx.SqlState} - {pgEx.Message}");
                throw;
            }
            catch (Exception ex)
            {
                Console.WriteLine($"[AI ERROR] Errore in GetTypeFileByFuId per fu_id={fu_id}: {ex.Message}");
                throw;
            }
        }

        /// <summary>
        /// Imposta lo stato di elaborazione AI di un file
        /// Status: 0 = Errore, 1 = In elaborazione, 2 = Completato
        /// </summary>
        public void SetFileUploadStatus(int fu_id, int status)
        {
            using (var conn = new NpgsqlConnection(mConnDB))
            {
                conn.Open();
                using (var command = new NpgsqlCommand("UPDATE file_upload SET fu_ai_status = @status WHERE fu_id = @fu_id", conn))
                {
                    command.Parameters.AddWithValue("@status", status);
                    command.Parameters.AddWithValue("@fu_id", fu_id);
                    command.CommandType = System.Data.CommandType.Text;
                    command.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// Aggiorna il form del file con la risposta dell'AI
        /// </summary>
        public void UpdateFileForm(FileUpload file)
        {
            using (var conn = new NpgsqlConnection(mConnDB))
            {
                conn.Open();
                using (var command = new NpgsqlCommand(
                    "UPDATE file_upload SET fu_ai_response = @fu_ai_response::json, fu_ai_status = 2 WHERE fu_id = @fu_id", conn))
                {
                    command.Parameters.AddWithValue("@fu_ai_response", file.Fu_ai_response ?? (object)DBNull.Value);
                    command.Parameters.AddWithValue("@fu_id", file.Fu_id);
                    command.CommandType = System.Data.CommandType.Text;
                    command.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// Elimina un file temporaneo dalla cartella AI
        /// </summary>
        public void DeletePdfFileFolderTempForAI(int fu_id)
        {
            using (var conn = new NpgsqlConnection(mConnDB))
            {
                conn.Open();
                using (var command = new NpgsqlCommand("DELETE FROM file_upload WHERE fu_id = @fu_id", conn))
                {
                    command.Parameters.AddWithValue("@fu_id", fu_id);
                    command.CommandType = System.Data.CommandType.Text;
                    command.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// Aggiorna lo stato del dettaglio nella tabella schedule_process tramite fu_id
        /// Se tutte le righe con lo stesso sp_guid_operation sono "elaborato",
        /// aggiorna sp_status_operation a "end operation" e sp_date_end
        /// </summary>
        /// <param name="fu_id">ID del file upload</param>
        /// <param name="status_detail">Stato del dettaglio (es. "elaborato", "errore", "in elaborazione")</param>
        /// <param name="error_message">Messaggio di errore opzionale da salvare quando status_detail = "errore"</param>
        public void UpdateScheduleProcessDetailStatus(int fu_id, string status_detail, string error_message = null)
        {
            int maxRetries = 3;
            int retryCount = 0;

            while (retryCount < maxRetries)
            {
                try
                {
                    UpdateScheduleProcessDetailStatusInternal(fu_id, status_detail, error_message);
                    return; // Success, exit
                }
                catch (Npgsql.PostgresException pgEx)
                {
                    // Errore di serializzazione (40001) - retry con backoff
                    if (pgEx.SqlState == "40001")
                    {
                        retryCount++;
                        if (retryCount >= maxRetries)
                        {
                            Console.WriteLine($"[AI ERROR] Errore di concorrenza dopo {maxRetries} tentativi per fu_id={fu_id}: {pgEx.Message}");
                            throw;
                        }

                        int delayMs = retryCount * 100; // Backoff progressivo: 100ms, 200ms, 300ms
                        Console.WriteLine($"[AI] Retry {retryCount}/{maxRetries} per fu_id={fu_id} dopo {delayMs}ms...");
                        System.Threading.Thread.Sleep(delayMs);
                    }
                    else
                    {
                        // Altri errori PostgreSQL (incluso 42883 per UUID) - log e rilancia
                        Console.WriteLine($"[AI ERROR] Errore PostgreSQL in UpdateScheduleProcessDetailStatus per fu_id={fu_id}: {pgEx.SqlState} - {pgEx.Message}");
                        throw;
                    }
                }
            }
        }

        /// <summary>
        /// Implementazione interna di UpdateScheduleProcessDetailStatus
        /// </summary>
        private void UpdateScheduleProcessDetailStatusInternal(int fu_id, string status_detail, string error_message)
        {
            using (var conn = new NpgsqlConnection(mConnDB))
            {
                conn.Open();

                // Prima recuperiamo il fu_sp_guid_detail come GUID
                Guid? fu_sp_guid_detail = null;
                using (var commandGetGuidDetail = new NpgsqlCommand(
                    "SELECT fu_sp_guid_detail FROM file_upload WHERE fu_id = @fu_id", conn))
                {
                    commandGetGuidDetail.Parameters.AddWithValue("@fu_id", fu_id);
                    object guidResult = commandGetGuidDetail.ExecuteScalar();
                    if (guidResult != null && guidResult != DBNull.Value)
                        fu_sp_guid_detail = (Guid)guidResult;
                }

                if (!fu_sp_guid_detail.HasValue)
                {
                    Console.WriteLine($"[AI ERROR] fu_sp_guid_detail non trovato per fu_id={fu_id}");
                    return;
                }

                // Recupera sp_guid_operation usando il GUID correttamente tipizzato
                Guid? sp_guid_operation = null;
                using (var commandGetGuid = new NpgsqlCommand(
                    @"SELECT sp_guid_operation
                      FROM schedule_process
                      WHERE sp_guid_detail = @fu_sp_guid_detail
                      LIMIT 1", conn))
                {
                    var param = commandGetGuid.Parameters.Add("@fu_sp_guid_detail", NpgsqlTypes.NpgsqlDbType.Uuid);
                    param.Value = fu_sp_guid_detail.Value;
                    commandGetGuid.CommandType = System.Data.CommandType.Text;

                    object result = commandGetGuid.ExecuteScalar();
                    if (result != null && result != DBNull.Value)
                        sp_guid_operation = (Guid)result;
                }

                // Aggiorna lo stato del dettaglio e la data di fine se stato = "elaborato" o "errore"
                // Se c'è un messaggio di errore, lo concatena allo status_detail
                string status_to_save = status_detail;
                if (status_detail == "errore" && !string.IsNullOrEmpty(error_message))
                {
                    status_to_save = $"errore: {error_message}";
                }

                // IMPORTANTE: Aggiorna TUTTE le righe in schedule_process che hanno lo stesso fu_id
                // per evitare che alcune rimangano bloccate in "in elaborazione"
                string updateQuery = (status_detail == "elaborato" || status_detail == "errore")
                    ? @"UPDATE schedule_process
                        SET sp_status_detail = @status_detail,
                            sp_date_end = @date_end
                        WHERE sp_guid_detail = @fu_sp_guid_detail"
                    : @"UPDATE schedule_process
                        SET sp_status_detail = @status_detail
                        WHERE sp_guid_detail = @fu_sp_guid_detail";

                using (var command = new NpgsqlCommand(updateQuery, conn))
                {
                    command.Parameters.AddWithValue("@status_detail", status_to_save);
                    var paramGuid = command.Parameters.Add("@fu_sp_guid_detail", NpgsqlTypes.NpgsqlDbType.Uuid);
                    paramGuid.Value = fu_sp_guid_detail.Value;
                    if (status_detail == "elaborato" || status_detail == "errore")
                        command.Parameters.AddWithValue("@date_end", DateTime.Now);
                    command.CommandType = System.Data.CommandType.Text;
                    int rowsAffected = command.ExecuteNonQuery();

                    if (rowsAffected > 1)
                        Console.WriteLine($"[AI] Aggiornate {rowsAffected} righe schedule_process per fu_id={fu_id} a stato '{status_to_save}'");
                }

                // Se lo stato è "elaborato" o "errore" e abbiamo un sp_guid_operation, verifica se tutti sono completati
                if ((status_detail == "elaborato" || status_detail == "errore") && sp_guid_operation.HasValue)
                {
                    // Controlla se ci sono ancora righe non completate (né elaborate né in errore) con lo stesso sp_guid_operation
                    // Nota: gli errori possono contenere messaggi come "errore: messaggio", quindi usiamo LIKE
                    using (var commandCheck = new NpgsqlCommand(
                        @"SELECT COUNT(*)
                          FROM schedule_process
                          WHERE sp_guid_operation = @sp_guid_operation
                          AND sp_status_detail != 'elaborato'
                          AND sp_status_detail NOT LIKE 'errore%'", conn))
                    {
                        var paramGuidOp = commandCheck.Parameters.Add("@sp_guid_operation", NpgsqlTypes.NpgsqlDbType.Uuid);
                        paramGuidOp.Value = sp_guid_operation.Value;
                        commandCheck.CommandType = System.Data.CommandType.Text;

                        long count = (long)commandCheck.ExecuteScalar();

                        // Se tutte le righe sono completate (elaborate o in errore), aggiorna sp_status_operation
                        if (count == 0)
                        {
                            using (var commandUpdate = new NpgsqlCommand(
                                @"UPDATE schedule_process
                                  SET sp_status_operation = 'end operation'
                                  WHERE sp_guid_operation = @sp_guid_operation", conn))
                            {
                                var paramGuidOpUpdate = commandUpdate.Parameters.Add("@sp_guid_operation", NpgsqlTypes.NpgsqlDbType.Uuid);
                                paramGuidOpUpdate.Value = sp_guid_operation.Value;
                                commandUpdate.CommandType = System.Data.CommandType.Text;
                                commandUpdate.ExecuteNonQuery();
                            }

                            Console.WriteLine($"[AI] Operazione completata per sp_guid_operation={sp_guid_operation}");
                        }
                    }
                }
            }
        }

        /// <summary>
        /// Recupera la configurazione del database tramite chiave
        /// </summary>
        public DbConfig GetDbConfigByKey(string db_name)
        {
            DbConfig config = null;

            using (var conn = new NpgsqlConnection(mConnDB))
            {
                conn.Open();
                using (var command = new NpgsqlCommand("SELECT * FROM drive_upload_get_db_config(@db_name)", conn))
                {
                    command.Parameters.AddWithValue("@db_name", db_name);
                    command.CommandType = System.Data.CommandType.Text;

                    using (NpgsqlDataReader reader = command.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            config = new DbConfig
                            {
                                Db_id = reader.GetInt32(reader.GetOrdinal("db_id")),
                                Db_name = reader.IsDBNull(reader.GetOrdinal("db_name")) ? null : reader.GetString(reader.GetOrdinal("db_name")),
                                Db_url = reader.IsDBNull(reader.GetOrdinal("db_url")) ? null : reader.GetString(reader.GetOrdinal("db_url")),
                                Db_username = reader.IsDBNull(reader.GetOrdinal("db_username")) ? null : reader.GetString(reader.GetOrdinal("db_username")),
                                Db_password = reader.IsDBNull(reader.GetOrdinal("db_password")) ? null : reader.GetString(reader.GetOrdinal("db_password"))
                            };
                        }
                    }
                }
            }

            return config;
        }
    }
}
