using System;
using System.Data;
using System.IO;
using System.Text.Json;
using System.Text.Json.Serialization;
using Microsoft.AspNetCore.Http.Metadata;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Data.SqlClient;

namespace WebApplication1.Controllers
{
    [ApiController]
    [Route("[controller]")]
    public class WeatherForecastController : ControllerBase
    {

        SqlConnection SqlConnection = new SqlConnection("Data Source=ngknn.ru;Initial Catalog=AAAnobldemo;User ID=21p;Password=12357;Connect Timeout=30;Encrypt=False;Trust Server Certificate=True;Application Intent=ReadWrite;Multi Subnet Failover=False");



        [HttpGet(Name = "GetRegion")]
        [Tags("GetRegion")]
        public List<region> GetRegion()
        {

            SqlConnection.Open();

            SqlCommand sqlCommand = new SqlCommand("select NameRegion from Region", SqlConnection);

            List<region> list = new List<region>();
            SqlDataReader reader = sqlCommand.ExecuteReader();
            while (reader.Read())
            {
                region zalupa = new region();
                zalupa.Name = reader.GetString(0);
                list.Add(zalupa);

            }


            reader.Close();
            SqlConnection.Close();

            return list;
        }

        [HttpGet("GetReciplentMessage")]
        [Tags("GetWeatherForecast")]
        public List<ReciplentMessage> GetReciplentMessage()
        {

            SqlConnection.Open();
            SqlCommand sqlCommand = new SqlCommand("select RecipientMessage from RecipientMessage", SqlConnection);
            List<ReciplentMessage> list = new List<ReciplentMessage>();
            SqlDataReader reader = sqlCommand.ExecuteReader();
            while (reader.Read())
            {
                ReciplentMessage reciplentMessage = new ReciplentMessage();
                reciplentMessage.Name = reader.GetString(0);
                list.Add(reciplentMessage);
            }

            reader.Close();
            SqlConnection.Close();
            return list;
        }
        [HttpGet("GetUserRole")]
        [Tags("GetUserRole")]
        public IActionResult GetUserRole(string login, string password)
        {

            SqlConnection.Open();
            SqlCommand sqlCommand = new SqlCommand($"select id_role from role where login = '{login}' and password = '{password}'", SqlConnection);
            SqlDataReader reader = sqlCommand.ExecuteReader();
            if (reader.Read())
            {
                reader.Close();
                SqlConnection.Close();
                return Ok();
            }
            else
            {
                reader.Close();
                SqlConnection.Close();
                return Unauthorized();
            }





        }
        [HttpGet("UserUpdate")]
        [Tags("UserUpdate")]
        public bool UserUpdate(string? email, string? Area, string? city, string? street, string? housenymber, string? HouseBody, string? appartament, string? surname, string? name, string? patronomic, string? phone, string? socialstatus)
        {
            SqlConnection.Open();
            SqlCommand cmd = new SqlCommand($"insert into [User] values ('{email}','{Area}','{city}','{street}','{housenymber}','{HouseBody}','{appartament}','{surname}','{name}','{patronomic}','{phone}','{socialstatus}',NULL)", SqlConnection);
            if (cmd.ExecuteNonQuery() > 0)
            {
                SqlConnection.Close();
                return true;
            }
            else
            {
                SqlConnection.Close();
                return false;
            }
        }
        [HttpGet("FileUpdate")]
        [Tags("FileUpdate")]
        public void FileUpdate(byte[] formFile)
        {


            SqlConnection.Open();
            var cmd = new SqlCommand("INSERT INTO [File] ([File]) VALUES (@file)", SqlConnection);

            cmd.Parameters.Add("@file", SqlDbType.VarBinary).Value = formFile;


            if (cmd.ExecuteNonQuery() > 0)
            {
                SqlConnection.Close();

            }
            else
            {
                SqlConnection.Close();

            }


        }
        [HttpGet("AppealUpdate")]
        [Tags("AppealUpdate")]
        public bool AppealUpdate(string region, string? index, string RecipientMessage, string? text)
        {
            SqlConnection.Open();
            SqlCommand cmd = new SqlCommand($"insert into [Appeal] values ((select Region.idRegion from Region where NameRegion = '{region}'),'{index}',(select IdRecipientMessage from RecipientMessage where RecipientMessage = '{RecipientMessage}' ),'{text}',(SELECT MAX([File].IdFile) FROM [File]),(SELECT MAX(id_user) from [User]))", SqlConnection);
            cmd.ExecuteScalar();
            SqlConnection.Close();
            return true;




        }
        [HttpGet("RegionUpdate")]
        [Tags("RegionUpdate")]
        public void RegionUpdate(string? region)
        {
            SqlConnection.Open();
            SqlCommand cmd = new SqlCommand($" insert into Region values ('{region}') ", SqlConnection);
            cmd.ExecuteScalar();
            SqlConnection.Close();





        }
        [HttpGet("resipleUpdate")]
        [Tags("resipleUpdate")]
        public void resipleUpdate(string RecipientMessage)
        {
            SqlConnection.Open();
            SqlCommand cmd = new SqlCommand($" insert into RecipientMessage values ('{RecipientMessage}') ", SqlConnection);
            cmd.ExecuteScalar();
            SqlConnection.Close();





        }

    }

    public class region
    {
        public string Name { get; set; }
    }
    public class ReciplentMessage
    {
        public string Name { get; set; }
    }
}