namespace _03MinionNames
{
    using System;
    using System.Data.SqlClient;

    public class StartUp
    {
        private const string DB_NAME = "MinionsDB";

        private static string connectionString = @$"Server=.\SQLEXPRESS;Database={DB_NAME};Integrated Security=true";

        public static void Main(string[] args)
        {
            int villainId = int.Parse(Console.ReadLine());

            SqlConnection connection = new SqlConnection(connectionString);

            connection.Open();

            using (connection)
            {
                string queryText = @"SELECT [Name] FROM Villains WHERE Id = @Id

                                     SELECT ROW_NUMBER() OVER(ORDER BY [Name]) AS RowNumber,
                                            m.[Name],
                                            m.Age
                                       FROM MinionsVillains AS mv
                                            JOIN Minions AS m ON m.Id = mv.MinionId
                                      WHERE mv.VillainId = @Id
                                   ORDER BY m.[Name]";

                using SqlCommand sqlCommand = new SqlCommand(queryText, connection);

                sqlCommand.Parameters.AddWithValue("@Id", villainId);

                string villainName = (string)sqlCommand.ExecuteScalar();

                if (villainName == null)
                {
                    Console.WriteLine($"No villain with ID {villainId} exists in the database.");
                    return;
                }

                SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();

                using (sqlDataReader)
                {
                    while (sqlDataReader.Read())
                    {
                        Console.WriteLine($"Villain: {villainName}");

                        int counter = 1;
                        string minionName = (string)sqlDataReader["m.Name"];

                        if (minionName != null)
                        {
                            int minionAge = (int)sqlDataReader["m.Age"];

                            Console.WriteLine($"{counter}. {minionName} {minionAge}");
                            counter++;
                        }
                        else
                        {
                            Console.WriteLine("(no minions)");
                        }
                    }
                }
            }
        }
    }
}