---

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Security.Cryptography;
using System.Text;
using System.Windows;

namespace FarmManagementWPF.Data
{
    public class FarmContext
    {
        private string _connectionString = "Server=(localdb)\\MSSQLLocalDB;Database=FarmManagementDB;Trusted_Connection=True;";

        private SqlConnection GetConnection()
        {
            return new SqlConnection(_connectionString);
        }

        public List<Animal> GetAllAnimals()
        {
            var list = new List<Animal>();
            using (var conn = GetConnection())
            {
                conn.Open();
                var cmd = new SqlCommand("SELECT * FROM Animals ORDER BY AnimalNumber", conn);
                using (var r = cmd.ExecuteReader())
                {
                    while (r.Read())
                    {
                        list.Add(new Animal
                        {
                            AnimalId = r.GetGuid(0),
                            AnimalNumber = r.GetString(1),
                            AnimalType = r.GetString(2),
                            Breed = r.IsDBNull(3) ? "" : r.GetString(3),
                            BirthDate = r.IsDBNull(4) ? null : (DateTime?)r.GetDateTime(4),
                            Weight = r.IsDBNull(5) ? null : (decimal?)r.GetDecimal(5)
                        });
                    }
                }
            }
            return list;
        }

        public void AddAnimal(Animal a)
        {
            using (var conn = GetConnection())
            {
                conn.Open();
                var cmd = new SqlCommand("INSERT INTO Animals (AnimalId, AnimalNumber, AnimalType, Breed, BirthDate, Weight) VALUES (@id, @num, @type, @breed, @date, @weight)", conn);
                cmd.Parameters.AddWithValue("@id", a.AnimalId);
                cmd.Parameters.AddWithValue("@num", a.AnimalNumber);
                cmd.Parameters.AddWithValue("@type", a.AnimalType);
                cmd.Parameters.AddWithValue("@breed", string.IsNullOrEmpty(a.Breed) ? DBNull.Value : (object)a.Breed);
                cmd.Parameters.AddWithValue("@date", a.BirthDate == null ? DBNull.Value : (object)a.BirthDate);
                cmd.Parameters.AddWithValue("@weight", a.Weight == null ? DBNull.Value : (object)a.Weight);
                cmd.ExecuteNonQuery();
            }
        }

        public void UpdateAnimal(Animal a)
        {
            using (var conn = GetConnection())
            {
                conn.Open();
                var cmd = new SqlCommand("UPDATE Animals SET AnimalNumber=@num, AnimalType=@type, Breed=@breed, BirthDate=@date, Weight=@weight WHERE AnimalId=@id", conn);
                cmd.Parameters.AddWithValue("@id", a.AnimalId);
                cmd.Parameters.AddWithValue("@num", a.AnimalNumber);
                cmd.Parameters.AddWithValue("@type", a.AnimalType);
                cmd.Parameters.AddWithValue("@breed", string.IsNullOrEmpty(a.Breed) ? DBNull.Value : (object)a.Breed);
                cmd.Parameters.AddWithValue("@date", a.BirthDate == null ? DBNull.Value : (object)a.BirthDate);
                cmd.Parameters.AddWithValue("@weight", a.Weight == null ? DBNull.Value : (object)a.Weight);
                cmd.ExecuteNonQuery();
            }
        }

        public void DeleteAnimal(Guid id)
        {
            using (var conn = GetConnection())
            {
                conn.Open();
                var cmd = new SqlCommand("DELETE FROM Animals WHERE AnimalId = @id", conn);
                cmd.Parameters.AddWithValue("@id", id);
                cmd.ExecuteNonQuery();
            }
        }

        public List<Animal> GetSimpleAnimals()
        {
            var list = new List<Animal>();
            using (var conn = GetConnection())
            {
                conn.Open();
                var cmd = new SqlCommand("SELECT AnimalId, AnimalNumber FROM Animals ORDER BY AnimalNumber", conn);
                using (var r = cmd.ExecuteReader())
                {
                    while (r.Read())
                    {
                        list.Add(new Animal { AnimalId = r.GetGuid(0), AnimalNumber = r.GetString(1) });
                    }
                }
            }
            return list;
        }

        public List<Supplier> GetAllSuppliers()
        {
            var list = new List<Supplier>();
            using (var conn = GetConnection())
            {
                conn.Open();
                var cmd = new SqlCommand("SELECT * FROM Suppliers ORDER BY Name", conn);
                using (var r = cmd.ExecuteReader())
                {
                    while (r.Read())
                    {
                        list.Add(new Supplier
                        {
                            SupplierId = r.GetGuid(0),
                            Name = r.GetString(1),
                            Phone = r.IsDBNull(2) ? "" : r.GetString(2)
                        });
                    }
                }
            }
            return list;
        }

        public void AddSupplier(Supplier s)
        {
            using (var conn = GetConnection())
            {
                conn.Open();
                var cmd = new SqlCommand("INSERT INTO Suppliers (SupplierId, Name, Phone) VALUES (@id, @name, @phone)", conn);
                cmd.Parameters.AddWithValue("@id", s.SupplierId);
                cmd.Parameters.AddWithValue("@name", s.Name);
                cmd.Parameters.AddWithValue("@phone", string.IsNullOrEmpty(s.Phone) ? DBNull.Value : (object)s.Phone);
                cmd.ExecuteNonQuery();
            }
        }

        public void UpdateSupplier(Supplier s)
        {
            using (var conn = GetConnection())
            {
                conn.Open();
                var cmd = new SqlCommand("UPDATE Suppliers SET Name=@name, Phone=@phone WHERE SupplierId=@id", conn);
                cmd.Parameters.AddWithValue("@id", s.SupplierId);
                cmd.Parameters.AddWithValue("@name", s.Name);
                cmd.Parameters.AddWithValue("@phone", string.IsNullOrEmpty(s.Phone) ? DBNull.Value : (object)s.Phone);
                cmd.ExecuteNonQuery();
            }
        }

        public void DeleteSupplier(Guid id)
        {
            using (var conn = GetConnection())
            {
                conn.Open();
                var cmd = new SqlCommand("DELETE FROM Suppliers WHERE SupplierId = @id", conn);
                cmd.Parameters.AddWithValue("@id", id);
                cmd.ExecuteNonQuery();
            }
        }

        public User GetUserByLogin(string login)
        {
            using (var conn = GetConnection())
            {
                conn.Open();
                var cmd = new SqlCommand("SELECT * FROM Users WHERE Login = @login", conn);
                cmd.Parameters.AddWithValue("@login", login);
                using (var r = cmd.ExecuteReader())
                {
                    if (r.Read())
                    {
                        return new User
                        {
                            UserId = r.GetGuid(0),
                            FullName = r.GetString(1),
                            Login = r.GetString(2),
                            PasswordHash = r.GetString(3)
                        };
                    }
                }
            }
            return null;
        }

        public void AddUser(User u)
        {
            using (var conn = GetConnection())
            {
                conn.Open();
                var cmd = new SqlCommand("INSERT INTO Users (UserId, FullName, Login, PasswordHash) VALUES (@id, @name, @login, @pass)", conn);
                cmd.Parameters.AddWithValue("@id", u.UserId);
                cmd.Parameters.AddWithValue("@name", u.FullName);
                cmd.Parameters.AddWithValue("@login", u.Login);
                cmd.Parameters.AddWithValue("@pass", u.PasswordHash);
                cmd.ExecuteNonQuery();
            }
        }

        public List<VeterinaryCheck> GetAllVeterinaryChecks()
        {
            var list = new List<VeterinaryCheck>();
            using (var conn = GetConnection())
            {
                conn.Open();
                var cmd = new SqlCommand(@"SELECT v.*, a.AnimalNumber, u.FullName FROM VeterinaryChecks v 
                    JOIN Animals a ON v.AnimalId = a.AnimalId 
                    JOIN Users u ON v.UserId = u.UserId 
                    ORDER BY v.CheckDate DESC", conn);
                using (var r = cmd.ExecuteReader())
                {
                    while (r.Read())
                    {
                        list.Add(new VeterinaryCheck
                        {
                            CheckId = r.GetGuid(0),
                            AnimalId = r.GetGuid(1),
                            UserId = r.GetGuid(2),
                            CheckDate = r.GetDateTime(3),
                            Diagnosis = r.IsDBNull(4) ? "" : r.GetString(4),
                            AnimalNumber = r.GetString(5),
                            UserName = r.GetString(6)
                        });
                    }
                }
            }
            return list;
        }

        public void AddVeterinaryCheck(VeterinaryCheck c)
        {
            using (var conn = GetConnection())
            {
                conn.Open();
                var cmd = new SqlCommand("INSERT INTO VeterinaryChecks (CheckId, AnimalId, UserId, CheckDate, Diagnosis) VALUES (@id, @aid, @uid, @date, @diag)", conn);
                cmd.Parameters.AddWithValue("@id", c.CheckId);
                cmd.Parameters.AddWithValue("@aid", c.AnimalId);
                cmd.Parameters.AddWithValue("@uid", c.UserId);
                cmd.Parameters.AddWithValue("@date", c.CheckDate);
                cmd.Parameters.AddWithValue("@diag", string.IsNullOrEmpty(c.Diagnosis) ? DBNull.Value : (object)c.Diagnosis);
                cmd.ExecuteNonQuery();
            }
        }

        public void UpdateVeterinaryCheck(VeterinaryCheck c)
        {
            using (var conn = GetConnection())
            {
                conn.Open();
                var cmd = new SqlCommand("UPDATE VeterinaryChecks SET AnimalId=@aid, UserId=@uid, CheckDate=@date, Diagnosis=@diag WHERE CheckId=@id", conn);
                cmd.Parameters.AddWithValue("@id", c.CheckId);
                cmd.Parameters.AddWithValue("@aid", c.AnimalId);
                cmd.Parameters.AddWithValue("@uid", c.UserId);
                cmd.Parameters.AddWithValue("@date", c.CheckDate);
                cmd.Parameters.AddWithValue("@diag", string.IsNullOrEmpty(c.Diagnosis) ? DBNull.Value : (object)c.Diagnosis);
                cmd.ExecuteNonQuery();
            }
        }

        public void DeleteVeterinaryCheck(Guid id)
        {
            using (var conn = GetConnection())
            {
                conn.Open();
                var cmd = new SqlCommand("DELETE FROM VeterinaryChecks WHERE CheckId = @id", conn);
                cmd.Parameters.AddWithValue("@id", id);
                cmd.ExecuteNonQuery();
            }
        }
    }

    public class Animal
    {
        public Guid AnimalId { get; set; } = Guid.NewGuid();
        public string AnimalNumber { get; set; } = "";
        public string AnimalType { get; set; } = "";
        public string Breed { get; set; } = "";
        public DateTime? BirthDate { get; set; }
        public decimal? Weight { get; set; }
    }

    public class Supplier
    {
        public Guid SupplierId { get; set; } = Guid.NewGuid();
        public string Name { get; set; } = "";
        public string Phone { get; set; } = "";
    }

    public class User
    {
        public Guid UserId { get; set; } = Guid.NewGuid();
        public string FullName { get; set; } = "";
        public string Login { get; set; } = "";
        public string PasswordHash { get; set; } = "";
    }

    public class VeterinaryCheck
    {
        public Guid CheckId { get; set; } = Guid.NewGuid();
        public Guid AnimalId { get; set; }
        public Guid UserId { get; set; }
        public DateTime CheckDate { get; set; }
        public string Diagnosis { get; set; } = "";
        public string AnimalNumber { get; set; } = "";
        public string UserName { get; set; } = "";
    }
}