using System;
using System.Collections.Generic;
using System.Threading.Tasks;
using Npgsql;

namespace selfHostedHttp
{
    public class Database
    {
        private static readonly string connectionSettings =
            "Host=localhost;Username=postgres;Password=******;Database=university_rating";

        public static async Task AddStudentToDatabase(Student student)
        {
            var task = GetIdLastStudent();
            task.Wait();
            var lastStudentId = task.Result;
            await using var connection = new NpgsqlConnection(connectionSettings);
            await connection.OpenAsync();
            var request = $"INSERT INTO students  " +
                          $"(student_id, " +
                          $"student_first_name, " +
                          $"student_second_name, " +
                          $"student_patronymic, " +
                          $"student_birthday, " +
                          $"student_group_id, " +
                          $"student_document_number, " +
                          $"student_email, " +
                          $"student_additional_information) " +
                          $"VALUES (@id, @firstName, @secondName, @patronymic, " +
                          $"@birthday, @groupNumber, @documentNumber, @email, @additionalInformation)";
            await using var cmd = new NpgsqlCommand(request, connection);
            cmd.Parameters.AddWithValue("id", ++lastStudentId);
            cmd.Parameters.AddWithValue("firstName", student.FirstName);
            cmd.Parameters.AddWithValue("secondName", student.SecondName);
            cmd.Parameters.AddWithValue("patronymic", student.Patronymic);
            cmd.Parameters.AddWithValue("birthday", GetValidatedDate(student.Birthday));
            cmd.Parameters.AddWithValue("groupNumber", GetValidatedGroupNumber(student.GroupNumber));
            cmd.Parameters.AddWithValue("documentNumber", student.DocumentNumber);
            cmd.Parameters.AddWithValue("email", student.Email);
            cmd.Parameters.AddWithValue("additionalInformation", student.AdditionalInformation);
            await cmd.ExecuteNonQueryAsync();
            await connection.CloseAsync();
        }

        public static async Task<List<Student>> RetrieveStudents(int countRecords, int pageNumber)
        {
            await using var connection = new NpgsqlConnection(connectionSettings);
            await connection.OpenAsync();
            var request = $"SELECT " +
                          $"student_id, " +
                          $"student_first_name, " +
                          $"student_second_name, " +
                          $"student_patronymic, " +
                          $"student_birthday, " +
                          $"student_group_id, " +
                          $"student_document_number, " +
                          $"student_email, " +
                          $"student_additional_information " +
                          $"FROM students " +
                          $"ORDER BY student_id " +
                          $"LIMIT {countRecords} OFFSET {countRecords*pageNumber}";
            await using var cmd = new NpgsqlCommand(request, connection);
            await using var reader = await cmd.ExecuteReaderAsync();
            var result = new List<Student>();
            while (await reader.ReadAsync())
            {
                var groupNumber = reader.GetValue(5).ToString();
                if (groupNumber.Length == 2)
                    groupNumber = "11-0" + groupNumber;
                if (groupNumber.Length == 1)
                    groupNumber = "11-00" + groupNumber;
                var student = new Student(reader.GetValue(2).ToString(), reader.GetValue(1).ToString(),
                    reader.GetValue(3).ToString(),
                    reader.GetDate(4).ToString(), groupNumber, reader.GetValue(6).ToString(),
                    reader.GetValue(7).ToString(), reader.GetValue(8).ToString());
                result.Add(student);
            }
            await reader.CloseAsync();
            await connection.CloseAsync();
            return result;
        }

        public static async Task<Student> RetrieveLastStudent()
        {
            await using var connection = new NpgsqlConnection(connectionSettings);
            await connection.OpenAsync();
            var request = $"SELECT " +
                          $"student_id, " +
                          $"student_first_name, " +
                          $"student_second_name, " +
                          $"student_patronymic, " +
                          $"student_birthday, " +
                          $"student_group_id, " +
                          $"student_document_number, " +
                          $"student_email, " +
                          $"student_additional_information " +
                          $"FROM students " +
                          $"ORDER BY student_id DESC " +
                          $"LIMIT 1";
            await using var cmd = new NpgsqlCommand(request, connection);
            await using var reader = await cmd.ExecuteReaderAsync();
            var result = default(Student);
            while (await reader.ReadAsync())
            {
                var groupNumber = reader.GetValue(5).ToString();
                if (groupNumber.Length == 2)
                    groupNumber = "11-0" + groupNumber;
                if (groupNumber.Length == 1)
                    groupNumber = "11-00" + groupNumber;
                var student = new Student(reader.GetValue(2).ToString(), reader.GetValue(1).ToString(),
                    reader.GetValue(3).ToString(),
                    reader.GetDate(4).ToString(), groupNumber, reader.GetValue(6).ToString(),
                    reader.GetValue(7).ToString(), reader.GetValue(8).ToString());
                result = student;
            }
            await reader.CloseAsync();
            await connection.CloseAsync();
            return result;
        }

        public static async Task<int> GetIdLastStudent()
        {
            await using var connection = new NpgsqlConnection(connectionSettings);
            await connection.OpenAsync();
            var request = $"SELECT " +
                          $"student_id " +
                          $"FROM students " +
                          $"ORDER BY student_id DESC " +
                          $"LIMIT 1";
            await using var cmd = new NpgsqlCommand(request, connection);
            await using var reader = await cmd.ExecuteReaderAsync();
            var id = -1;
            while (await reader.ReadAsync())
                id = reader.GetInt32(0);
            if (id < 0)
                throw new ArgumentException();
            await reader.CloseAsync();
            await connection.CloseAsync();
            return id;
        }

        public static DateTime GetValidatedDate(string date)
        {
            if (!DateTime.TryParse(date, out var value))
                throw new ArgumentException();
            return value;
        }

        public static int GetValidatedGroupNumber(string groupNumber)
        {
            if (String.IsNullOrEmpty(groupNumber))
                throw new ArgumentException();
            if (!int.TryParse(groupNumber.Substring(groupNumber.Length - 2, 2), out var value))
                throw new ArgumentException();
            return value;
        }
    }
}