using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Threading.Tasks;
using Dapper;
using DM.Entities;
using Security;

namespace DM.Context
{
    public class AccountRepository:BaseRepository<Account>
    {
        public AccountRepository(DbConnection connection) : base(connection)
        {
        }

        #region New_methods
        public Account GetBySessionKey(string sessionKey)
        {
            string sql = @"SELECT *
                            FROM Accounts INNER JOIN Sessions ON Accounts.Id=Sessions.AccountId
                            WHERE Sessions.SessionKey=@SessionKey";

            return DapperConnection.Query<Account>(sql, new { SessionKey = sessionKey }).FirstOrDefault();
        }

        public async Task<Account> GetClientByEmail(string username)
        {
            var account = (await DapperConnection.GetListAsync<Account>("WHERE Username = @Username", new { Username = username })).FirstOrDefault();

            return account;
        }
        #endregion

        public async Task<IEnumerable<Account>> GetWatchedVideoAccounts()
        {
            string sql = @"SELECT * FROM Accounts INNER JOIN AccountCourses ON AccountCourses.AccountId=Accounts.Id
                            WHERE AccountCourses.IsWatchedToday=1 AND AccountCourses.Day<60 AND Role=0";


            return await DapperConnection.QueryAsync<Account, AccountCourse, Account>(sql, ((account, course) =>
                {
                    account.CurrentAccountCourse = course;
                    return account;
                }));
        }

        public async Task<IEnumerable<Account>> GetUnsubscribedAccounts(long date)
        {
            string sql = @"SELECT * FROM Accounts WHERE Subscription<@Date AND Role=0";
            return await DapperConnection.QueryAsync<Account>(sql, new { Date = date });
        }

        public async Task<IEnumerable<Account>> GetActiveNonWatchedVideoAccounts(long date)
        {
            string sql = @"SELECT * FROM Accounts INNER JOIN AccountCourses ON AccountCourses.AccountId=Accounts.Id
                            WHERE Accounts.Subscription>@Date AND AccountCourses.IsWatchedToday<>1 AND AccountCourses.Day<60 AND Role=0";

            return await DapperConnection.QueryAsync<Account>(sql, new { Date = date });
        }

        public async Task<IEnumerable<Account>> GetClientsAsync(int? page)
        {
            if (page == null)
                return await DapperConnection.GetListAsync<Account>();
            return await DapperConnection.GetListPagedAsync<Account>(page.Value, 20, null, null);
        }

        public IEnumerable<Account> GetClients(int? page)
        {
            if (page == null)
                return DapperConnection.GetList<Account>();
            return DapperConnection.GetListPaged<Account>(page.Value, 20, null, null);
        }

        public async Task<Account> SelectAccount(string username, string password)
        {
            var  account = (await DapperConnection.GetListAsync<Account>("WHERE Username = @Username", new {Username=username})).FirstOrDefault();
            if (account != null)
            {
                string decryptedPassword;
                try
                {
                    decryptedPassword = StringCipher.Decrypt(account.Password);
                }
                catch (Exception exception)
                {
                    return null;
                }

                if (String.Equals(decryptedPassword, password))
                    return account;
            }
            return null;
        }

        public override async Task<IEnumerable<Account>> GetList(int? page)
        {
            if (page == null)
                return await DapperConnection.GetListAsync<Account>();
            return await DapperConnection.GetListPagedAsync<Account>(page.Value, 20, null, null);
        }

        public override async Task<Account> GetById(int id)
        {
            return await DapperConnection.GetAsync<Account>(id);
        }

        public override  Account Save(Account entity)
        {
            if (entity.Id == 0)
            {
                var id = DapperConnection.Insert(entity);
                if (id != null) entity.Id = id.Value;
            }
            else
            {
                DapperConnection.Update(entity);
            }
            return entity;
        }

        public override bool Delete(Account entity)
        {
            var res=DapperConnection.Delete(entity);
            return res > 0;
        }
    }
}