using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Traffic.Data.Models;
using Traffic.Repository.Interfaces;

namespace Traffic.Repository.Implements
{
    public class AnnounceRepository : IAnnounceRepository
    {
        public IDbTransaction Transaction { get; }
        public IDbConnection Connection => Transaction.Connection;

        public AnnounceRepository(IDbTransaction transaction)
        {
            Transaction = transaction;
        }

        public IEnumerable<Announce> GetAnnounces()
        {
            var sql = $"SELECT * FROM {nameof(Announce)}";
            return Connection.Query<Announce>(sql, null, Transaction);
        }

        public Announce GetAnnounceById(int id)
        {
            var sql = $"SELECT * FROM {nameof(Announce)} Where {nameof(Announce.Id)} = @{nameof(Announce.Id)}";
            return Connection.QueryFirstOrDefault<Announce>(sql, new { id }, Transaction);
        }

        public bool InsertAnnounce(Announce announce)
        {
            var sql = $@"INSERT INTO {nameof(Announce)} (
                                     {nameof(Announce.Title)}, 
                                     {nameof(Announce.Content)}, 
                                     {nameof(Announce.StartOn)}, 
                                     {nameof(Announce.Status)}, 
                                     {nameof(Announce.CreatorId)}, 
                                     {nameof(Announce.CreatedOn)})
	                          VALUES(@{nameof(Announce.Title)}, 
                                     @{nameof(Announce.Content)},
                                     @{nameof(Announce.StartOn)},
                                     @{nameof(Announce.Status)},
                                     @{nameof(Announce.CreatorId)},
                                     @{nameof(Announce.CreatedOn)})";

            var result = Connection.Execute(sql, announce, Transaction);
            return result == 1;
        }

        public bool UpdateAnnounce(Announce announce)
        {
            var sql = $@"UPDATE {nameof(Announce)}
	                        SET {nameof(Announce.Title)} = @{nameof(Announce.Title)}, 
                                {nameof(Announce.Content)} = @{nameof(Announce.Content)}, 
                                {nameof(Announce.StartOn)} = @{nameof(Announce.StartOn)}, 
                                {nameof(Announce.Status)} = @{nameof(Announce.Status)}, 
                                {nameof(Announce.UpdatorId)} = @{nameof(Announce.UpdatorId)}, 
                                {nameof(Announce.UpdatedOn)} = @{nameof(Announce.UpdatedOn)} 
	                      WHERE {nameof(Announce.Id)} = @{nameof(Announce.Id)}";
            var result = Connection.Execute(sql, announce, Transaction);
            return result == 1;
        }

        public bool DeleteAnnounce(int id)
        {
            var sql = $@"DELETE FROM {nameof(Announce)} WHERE  {nameof(Announce.Id)} = @{nameof(Announce.Id)}";
            var result = Connection.Execute(sql, new { id }, Transaction);
            return result == 1;
        }
    }
}