using Data.DAO;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace data.Repository
{
    public class SQLGroupRepository : IGroupRepository

    {
        private readonly RemoteDatabaseContext _dbContext;
        public SQLGroupRepository(RemoteDatabaseContext remoteDatabaseContext) { _dbContext = remoteDatabaseContext; }

        public bool AddGroup(Group group)
        {
            var maxGroupId = _dbContext.Groups.Max(g => (int?)g.GroupId) ?? 0;

            group.GroupId = maxGroupId + 1;

            _dbContext.Groups.Add(group);
            return _dbContext.SaveChanges() > 0;
        }


        public bool addGroupWithStudent(Group groups, IEnumerable<Student> students)
        {
            using var transaction = _dbContext.Database.BeginTransaction();
            try
            {
                _dbContext.Groups.Add(groups);
                _dbContext.SaveChanges();

                _dbContext.Groups.Add(groups);
                foreach (var item in students)
                {
                    item.Group = groups;
                    _dbContext.Students.Add(item);
                }


                _dbContext.SaveChanges();
                transaction.Commit();
                return true;
            }
            catch (Exception ex)
            {
                transaction.Rollback();
                Console.WriteLine(ex.Message);
                return false;
            }
        }

        public void AddStudentsToGroup(List<Student> students)
        {
            var maxStudentId = _dbContext.Students.Max(s => (int?)s.StudentId) ?? 0;

            foreach (var student in students)
            {
                student.StudentId = ++maxStudentId;
            }

            _dbContext.Students.AddRange(students);
            _dbContext.SaveChanges();
        }


        public bool DeleteGroup(int groupId)
        {
            using var transaction = _dbContext.Database.BeginTransaction();
            try
            {
                var group = _dbContext.Groups
                    .Include(g => g.Students)
                    .FirstOrDefault(g => g.GroupId == groupId);

                if (group != null)
                {
                    _dbContext.Students.RemoveRange(group.Students);
                    _dbContext.Groups.Remove(group);
                    _dbContext.SaveChanges();
                    transaction.Commit();
                    return true;
                }
                return false;
            }
            catch
            {
                transaction.Rollback();
                throw;
            }
        }

        public IEnumerable<Group> GetAllGroup()
        {
            return _dbContext.Groups
                .Include(group => group.Students)
                .Include(group => group.GroupSubjects)
                    .ThenInclude(gs => gs.Subject)
                .ToList();
        }

        public Group GetGroupById(int groupId)
        {
            return _dbContext.Groups
                .Include(group => group.Students)
                .Include(group => group.Subjects)
                .FirstOrDefault(group => group.GroupId == groupId);
        }

        public Group GetGroupWithSubjects(int groupId)
        {
            return _dbContext.Groups
                .Include(group => group.Subjects)
                .FirstOrDefault(group => group.GroupId == groupId);
        }

        public void AddSubjectsToGroup(List<GroupSubject> groupSubjects)
        {
            using var transaction = _dbContext.Database.BeginTransaction();
            try
            {
                foreach (var groupSubject in groupSubjects)
                {
                    var group = _dbContext.Groups.Include(g => g.Subjects).FirstOrDefault(g => g.GroupId == groupSubject.GroupId);
                    if (group == null)
                    {
                        throw new KeyNotFoundException($"Group with ID {groupSubject.GroupId} not found.");
                    }

                    var subject = _dbContext.Subjects.FirstOrDefault(s => s.SubjectId == groupSubject.SubjectId);
                    if (subject == null)
                    {
                        throw new KeyNotFoundException($"Group with ID {groupSubject.SubjectId} not found.");
                    }

                    if (!group.Subjects.Any(s => s.SubjectId == subject.SubjectId))
                    {
                        group.Subjects.Add(subject);
                    }
                }

                _dbContext.SaveChanges();
                transaction.Commit();
            }
            catch
            {
                transaction.Rollback();
                throw;
            }
        }

        public Subject GetSubjectById(int subjectId)
        {
            return _dbContext.Subjects.FirstOrDefault(s => s.SubjectId == subjectId);
        }

        public Subject GetSubjectByName(string subjectName)
        {
            return _dbContext.Subjects.FirstOrDefault(s => s.SubjectName == subjectName);
        }

        public void AddSubject(Subject subject)
        {
            _dbContext.Subjects.Add(subject);
            _dbContext.SaveChanges();
        }


        public List<Subject> GetGroupSubjects(int groupId)
        {
            return _dbContext.Groups
                .Where(group => group.GroupId == groupId)
                .SelectMany(group => group.Subjects)
                .ToList();
        }

        public Subject GetSubjectByGroup(int subjectId)
        {
            var groupSubject = _dbContext.GroupSubjects
                .Include(gs => gs.Subject)
                .FirstOrDefault(gs => gs.SubjectId == subjectId);

            if (groupSubject == null || groupSubject.Subject == null)
            {
                throw new KeyNotFoundException($"Предмет с ID {subjectId} не найден.");
            }

            return groupSubject.Subject;
        }

        public List<Subject> GetSubjectsByGroupId(int groupId)
        {
            var group = _dbContext.Groups
                .Include(g => g.GroupSubjects)
                .ThenInclude(gs => gs.Subject)
                .FirstOrDefault(g => g.GroupId == groupId);

            if (group == null)
            {
                throw new KeyNotFoundException($"Группа с ID {groupId} не найдена.");
            }

            return group.GroupSubjects
                .Select(gs => gs.Subject)
                .ToList();
        }

        public bool AddSubjectToGroup(int groupId, Subject subject)
        {
            using (var transaction = _dbContext.Database.BeginTransaction())
            {
                try
                {
                    var group = _dbContext.Groups.Include(g => g.Subjects).FirstOrDefault(g => g.GroupId == groupId);

                    if (group == null)
                    {
                        transaction.Rollback();
                        return false;
                    }

                    int maxSubjectId = _dbContext.Subjects.Any()
                        ? _dbContext.Subjects.Max(s => s.SubjectId)
                        : 0;

                    subject.SubjectId = maxSubjectId + 1;
                    subject.Group = group;

                    _dbContext.Subjects.Add(subject);

                    _dbContext.SaveChanges();

                    transaction.Commit();
                    return true;
                }
                catch (Exception)
                {
                    transaction.Rollback();
                    throw;
                }
            }
        }

        public void DeleteAllAttendances()
        {
            using var context = new RemoteDatabaseContext();
            var allAttendances = context.Attendances.ToList();
            context.Attendances.RemoveRange(allAttendances);
            context.SaveChanges();
        }

        public void DeleteAttendancesByGroup(int groupId)
        {
            using var context = new RemoteDatabaseContext();
            var attendancesByGroup = context.Attendances.Where(a => a.GroupId == groupId).ToList();
            context.Attendances.RemoveRange(attendancesByGroup);
            context.SaveChanges();
        }

        public void AddAttendance(Attendance attendance)
        {
            _dbContext.Attendances.Add(attendance);
            _dbContext.SaveChanges();
        }

        public Visit GetVisitById(int visitId)
        {
            return _dbContext.Visits.FirstOrDefault(v => v.VisitId == visitId);
        }

        public int GetGroupIdBySubjectName(string subjectName)
        {
            var subject = _dbContext.Subjects.Include(s => s.Group)
                                             .FirstOrDefault(s => s.SubjectName == subjectName);

            if (subject == null || subject.Group == null)
            {
                throw new InvalidOperationException($"Предмет с названием '{subjectName}' или его группа не найдены.");
            }

            return subject.Group.GroupId;
        }

        public IEnumerable<Attendance> GetAttendances(int groupId, string subject = null, DateTime? date = null, int? studentId = null)
        {
            var query = _dbContext.Attendances.AsQueryable();

            query = query.Where(a => a.GroupId == groupId);

            if (!string.IsNullOrEmpty(subject))
            {
                query = query.Where(a => a.GroupSubject.Subject.SubjectName == subject);
            }

            if (date.HasValue)
            {
                var dateOnly = DateOnly.FromDateTime(date.Value);
                query = query.Where(a => a.Date == dateOnly);
            }

            if (studentId.HasValue)
            {
                query = query.Where(a => a.StudentId == studentId.Value);
            }

            return query.ToList();
        }

        public Attendance GetAttendanceByDateStudentAndLesson(DateTime date, int studentId, int lessonNumber)
        {
            var dateOnly = DateOnly.FromDateTime(date);
            return _dbContext.Attendances
                .FirstOrDefault(a => a.Date == dateOnly && a.StudentId == studentId && a.LessonNumber == lessonNumber);
        }

        public void UpdateAttendance(Attendance attendance)
        {
            _dbContext.Attendances.Update(attendance);
            _dbContext.SaveChanges();
        }


        public void RemoveStudentsFromGroup(int groupId)
        {
            var students = _dbContext.Students.Where(s => s.GroupId == groupId).ToList();
            _dbContext.Students.RemoveRange(students);
            _dbContext.SaveChanges();
        }


        public void RemoveStudentsFromGroupByIds(int groupId, List<int> studentIds)
        {
            var studentsToRemove = _dbContext.Students
                .Where(s => s.GroupId == groupId && studentIds.Contains(s.StudentId))
                .ToList();

            if (studentsToRemove.Any())
            {
                _dbContext.Students.RemoveRange(studentsToRemove);
                _dbContext.SaveChanges();
            }
        }
    }
}