using System;
using System.Collections;
using Microsoft.AspNetCore.Mvc;
using BarbarAPI.Models;
using System.Configuration;
using System.Diagnostics;
//using MySql.Data.MySqlClient;
using System.Data.SqlClient;

namespace BarbarAPI.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class BarberController : ControllerBase
    {
        // GET api/barber
        [HttpGet]
        public ArrayList Get()
        {
            ArrayList barbers = new ArrayList();

            SqlConnection conn = null;
            SqlCommand comm;
            SqlDataReader reader = null;

            String connectionString = ConfigurationManager.ConnectionStrings["azure"].ConnectionString;

            String query = "SELECT * FROM barber;";

            try
            {
                conn = new SqlConnection(connectionString);
                conn.Open();
                Debug.Write("CONNECTION SUCCESSFUL");
            }
            catch (Exception e)
            {
                Debug.Write("FAILED TO CONNECT: " + e);
            }

            try
            {
                comm = new SqlCommand(query, conn);
                comm.ExecuteNonQuery();
                reader = comm.ExecuteReader();
                while (reader.Read())
                {
                    Barber barber = new Barber();
                    barber.id = reader.GetInt32(0);
                    barber.username = reader.GetString(1);
                    barber.firstname = reader.GetString(2);
                    barber.surname = reader.GetString(3);
                    barber.email = reader.GetString(4);
                    barber.password = reader.GetString(5);
                    barber.avatar = reader.GetString(6);
                    barber.login_code = reader.GetString(7);
                    barbers.Add(barber);
                }
                Debug.Write("INSERT SUCCESSFUL");
                conn.Close();
                Debug.Write("CONNECTION CLOSED");
            }
            catch (Exception e)
            {
                Debug.Write("FAILED TO INSERT: " + e);
            }

            return barbers;
        }

        // GET api/barber/5
        [HttpGet("{id}")]
        public Barber Get(int id)
        {
            Barber barber = new Barber();

            SqlConnection conn = null;
            SqlCommand comm;
            SqlDataReader reader = null;

            String connectionString = ConfigurationManager.ConnectionStrings["azure"].ConnectionString;

            String query = "SELECT * FROM barber WHERE barber_id = '" + id + "';";

            try
            {
                conn = new SqlConnection(connectionString);
                conn.Open();
                Debug.Write("CONNECTION SUCCESSFUL");
            }
            catch (Exception e)
            {
                Debug.Write("FAILED TO CONNECT: " + e);
            }

            try
            {
                comm = new SqlCommand(query, conn);
                comm.ExecuteNonQuery();
                reader = comm.ExecuteReader();
                while (reader.Read())
                {
                    barber.id = reader.GetInt32(0);
                    barber.username = reader.GetString(1);
                    barber.firstname = reader.GetString(2);
                    barber.surname = reader.GetString(3);
                    barber.email = reader.GetString(4);
                    barber.password = reader.GetString(5);
                    barber.avatar = reader.GetString(6);
                    barber.login_code = reader.GetString(7);
                }
                Debug.Write("INSERT SUCCESSFUL");
                conn.Close();
                Debug.Write("CONNECTION CLOSED");
            }
            catch (Exception e)
            {
                Debug.Write("FAILED TO INSERT: " + e);
            }

            return barber;
        }

        // POST api/barber
        [HttpPost]
        public String Post([FromBody] Barber barber)
        {
            SqlConnection conn = null;
            SqlCommand comm;

            String connectionString = ConfigurationManager.ConnectionStrings["azure"].ConnectionString;

            String query = "INSERT INTO barber(barber_username,barber_name,barber_surname,barber_email," +
                "barber_password,barber_avatar,login_code) VALUES ('" + barber.username + "','" +
                barber.firstname + "','" + barber.surname + "','" + barber.email + "','" + barber.password + "','" +
                barber.avatar + "','" + barber.login_code + "'); ";

            try
            {
                conn = new SqlConnection(connectionString);
                conn.Open();
                Debug.Write("CONNECTION SUCCESSFUL");
            }
            catch (Exception e)
            {
                Debug.Write("FAILED TO CONNECT: " + e);
            }

            try
            {
                comm = new SqlCommand(query, conn);
                comm.ExecuteNonQuery();
                Debug.Write("INSERT SUCCESSFUL");
                conn.Close();
                Debug.Write("CONNECTION CLOSED");
                Exception exception = new Exception();
                return "";
            }
            catch (Exception e)
            {
                Debug.Write("FAILED TO INSERT: " + e);
                return query;
            }


        }

        // PUT api/barber/5
        [HttpPut("{id}")]
        public void Put(int id, [FromBody] string value)
        {
        }

        // DELETE api/barber/5
        [HttpDelete("{id}")]
        public void Delete(int id)
        {
        }

        //GET api/barber/code
        [HttpPost("code")]
        public String ValidateCode([FromBody] String code)
        {
            SqlConnection conn = null;
            SqlCommand comm;
            SqlDataReader reader = null;
            String codeObj = "";

            String connectionString = ConfigurationManager.ConnectionStrings["azure"].ConnectionString;

            String query = "SELECT * FROM codes WHERE code = '"+code+"'; ";

            try
            {
                conn = new SqlConnection(connectionString);
                conn.Open();
                Debug.Write("CONNECTION SUCCESSFUL");
            }
            catch (Exception e)
            {
                Debug.Write("FAILED TO CONNECT: " + e);
            }

            try
            {
                comm = new SqlCommand(query, conn);
                comm.ExecuteNonQuery();
                reader = comm.ExecuteReader();

                while (reader.Read())
                {
                    codeObj = "{\"id\":\"" + reader.GetInt32(0)+"\",";
                    codeObj = codeObj + "\"code\":\""+ reader.GetString(1)+"\"}";

                }
                if (codeObj.Equals(""))
                {
                    codeObj = "invalid code";
                }
                conn.Close();
                Debug.Write("CONNECTION CLOSED");
                return codeObj;
            }
            catch (Exception e)
            {
                Debug.Write(e);
                return e.ToString();
            }
        }


    }
}