1703neshinavarfolomeev2/MessageHelper.cs

using System;
using System.Data;
using MySql.Data.MySqlClient;

namespace Neshina1703
{
    public static class MessageHelper
    {
        public static int CreateConversation(int employeeId1, int employeeId2)
        {
            try
            {
                using (var conn = DB.GetConnection())
                {
                    conn.Open();
                    using (var transaction = conn.BeginTransaction())
                    {
                        var cmd1 = new MySqlCommand("INSERT INTO conversations (created_at) VALUES (NOW())", conn, transaction);
                        cmd1.ExecuteNonQuery();
                        cmd1.CommandText = "SELECT LAST_INSERT_ID()";
                        int conversationId = Convert.ToInt32(cmd1.ExecuteScalar());

                        var cmd2 = new MySqlCommand(
                            "INSERT INTO conversation_participants (conversation_id, employee_id) VALUES (@conv, @emp1), (@conv, @emp2)",
                            conn, transaction);
                        cmd2.Parameters.AddWithValue("@conv", conversationId);
                        cmd2.Parameters.AddWithValue("@emp1", employeeId1);
                        cmd2.Parameters.AddWithValue("@emp2", employeeId2);
                        cmd2.ExecuteNonQuery();

                        transaction.Commit();
                        return conversationId;
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Ошибка создания чата: " + ex.Message);
            }
        }

        public static int? GetExistingConversation(int employeeId1, int employeeId2)
        {
            var query = @"SELECT c.id FROM conversations c
                         JOIN conversation_participants cp1 ON c.id = cp1.conversation_id
                         JOIN conversation_participants cp2 ON c.id = cp2.conversation_id
                         WHERE cp1.employee_id = @emp1 AND cp2.employee_id = @emp2";

            var parameters = new MySqlParameter[]
            {
                new MySqlParameter("@emp1", employeeId1),
                new MySqlParameter("@emp2", employeeId2)
            };

            var result = DB.ExecuteScalar(query, parameters);
            return result != null && result != DBNull.Value ? Convert.ToInt32(result) : (int?)null;
        }

        public static void SendMessage(int conversationId, int senderId, string messageText)
        {
            if (string.IsNullOrWhiteSpace(messageText)) return;

            var query = @"INSERT INTO messages (conversation_id, sender_id, message_text, sent_at) 
                         VALUES (@conv, @sender, @text, NOW())";

            var parameters = new MySqlParameter[]
            {
                new MySqlParameter("@conv", conversationId),
                new MySqlParameter("@sender", senderId),
                new MySqlParameter("@text", messageText)
            };

            DB.ExecuteCommand(query, parameters);

            DB.ExecuteCommand("UPDATE conversations SET updated_at = NOW() WHERE id = @id",
                new MySqlParameter[] { new MySqlParameter("@id", conversationId) });
        }

        public static DataTable GetMessages(int conversationId, int limit = 100)
        {
            var query = @"SELECT m.id, m.message_text, m.sent_at, m.is_read,
                         e.id as sender_id, e.full_name as sender_name
                         FROM messages m
                         JOIN employees e ON m.sender_id = e.id
                         WHERE m.conversation_id = @conv
                         ORDER BY m.sent_at DESC
                         LIMIT @limit";

            var parameters = new MySqlParameter[]
            {
                new MySqlParameter("@conv", conversationId),
                new MySqlParameter("@limit", limit)
            };

            var table = DB.ExecuteQuery(query, parameters);
            var view = table.DefaultView;
            view.Sort = "sent_at ASC";
            return view.ToTable();
        }

        public static DataTable GetConversationsForEmployee(int employeeId)
        {
            var query = @"SELECT c.id, c.updated_at,
                         e.id as other_employee_id,
                         e.full_name as other_employee_name,
                         (SELECT COUNT(*) FROM messages m 
                          WHERE m.conversation_id = c.id 
                          AND m.sender_id != @emp 
                          AND m.is_read = FALSE) as unread_count,
                         (SELECT message_text FROM messages m2 
                          WHERE m2.conversation_id = c.id 
                          ORDER BY m2.sent_at DESC LIMIT 1) as last_message
                         FROM conversations c
                         JOIN conversation_participants cp ON c.id = cp.conversation_id
                         JOIN employees e ON e.id = (
                             SELECT cp2.employee_id FROM conversation_participants cp2 
                             WHERE cp2.conversation_id = c.id AND cp2.employee_id != @emp
                             LIMIT 1
                         )
                         WHERE cp.employee_id = @emp
                         ORDER BY c.updated_at DESC";

            var parameters = new MySqlParameter[]
            {
                new MySqlParameter("@emp", employeeId)
            };

            return DB.ExecuteQuery(query, parameters);
        }

        public static DataTable GetAllEmployeesExcept(int currentEmployeeId)
        {
            var query = @"SELECT id, full_name, position_id 
                         FROM employees 
                         WHERE id != @emp 
                         ORDER BY full_name";

            var parameters = new MySqlParameter[]
            {
                new MySqlParameter("@emp", currentEmployeeId)
            };

            return DB.ExecuteQuery(query, parameters);
        }

        public static void MarkMessagesAsRead(int conversationId, int employeeId)
        {
            var query = @"UPDATE messages m
                         JOIN conversation_participants cp ON m.conversation_id = cp.conversation_id
                         SET m.is_read = TRUE
                         WHERE m.conversation_id = @conv 
                         AND m.sender_id != @emp";

            var parameters = new MySqlParameter[]
            {
                new MySqlParameter("@conv", conversationId),
                new MySqlParameter("@emp", employeeId)
            };

            DB.ExecuteCommand(query, parameters);
        }

        public static int GetUnreadMessagesCount(int employeeId)
        {
            var query = @"SELECT COUNT(*) FROM messages m
                         JOIN conversation_participants cp ON m.conversation_id = cp.conversation_id
                         WHERE cp.employee_id = @emp AND m.sender_id != @emp AND m.is_read = FALSE";

            var parameters = new MySqlParameter[]
            {
                new MySqlParameter("@emp", employeeId)
            };

            var result = DB.ExecuteScalar(query, parameters);
            return result != null && result != DBNull.Value ? Convert.ToInt32(result) : 0;
        }
    }
}