using Microsoft.Azure.Functions.Worker;
using Microsoft.Azure.Functions.Worker.Http;
using Microsoft.Azure.WebJobs;
using Microsoft.Extensions.Logging;
using System.Data.SqlClient;
using System.Net;

namespace templates
{
    public class fn_dbselect
    {
        private readonly ILogger _logger;

        public fn_dbselect(ILoggerFactory loggerFactory)
        {
            _logger = loggerFactory.CreateLogger<fn_dbselect>();
        }

        [FunctionName("SqlSelect")]
        public HttpResponseData Run([HttpTrigger(AuthorizationLevel.Anonymous,
            "get",
            Route = null)] HttpRequestData req,
            FunctionContext executionContext)
        {
            _logger.LogInformation("C# HTTP trigger function processed a request.");
            string connectionString = Environment.GetEnvironmentVariable("SqlConnectionString");

            using (SqlConnection connection = new SqlConnection(connectionString))
            {

                string sqlQuery = "select name from name_table where id = 12";
                using (SqlCommand command = new SqlCommand(sqlQuery, connection))
                {
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            // Access the columns of the current row
                            int id = reader.GetInt32(0);
                            string name = reader.GetString(1);

                            // Process the data or log it
                            _logger.LogInformation($"Common information - ID: {id}, Name: {name}");

                            var message = String.Format($"id: {0}, name: {1}", id, name);
                            var response = req.CreateResponse(HttpStatusCode.OK);
                            response.Headers.Add("Content-Type", "text/plain; charset=utf-8");
                            response.WriteString(String.Format("Welcome to Azure Functions!\nSite: {0}\n#Users: {1}\n", CommonInformation.siteName, CommonInformation.numberUsers));
                            response.WriteString(message);

                            return response;
                        }
                    }
                }
            }

            return null;
        }
    }
}