using System.Data;
using Microsoft.Data.SqlClient;
using NT.AltText.Models;
using NT.AltText.Repository.Database;
using NT.AltText.Repository.Interfaces;
using NT.AltText.Repository.Models;

namespace NT.AltText.Repository
{
    public class JobAssignedRepository : IUserJobRepository
    {
        private string connectionString = AltTextDbConnection.GetConnection();
        public async Task<int> AddAssignedJobAsync(UserJobDetails job)
        {
            using var conn = new SqlConnection(connectionString);
            using var cmd = new SqlCommand(SpNames.AddAssociationJobMaster, conn)
            {
                CommandType = CommandType.StoredProcedure
            };

            // Add input parameters, convert null-coalescing to DBNull.Value
            cmd.Parameters.AddWithValue("@JobId", (object?)job.JobId ?? DBNull.Value);
            cmd.Parameters.AddWithValue("@AssignedBy", (object?)job.AssignedBy ?? DBNull.Value);
            cmd.Parameters.AddWithValue("@AssignedTo", (object?)job.AssignedTo ?? DBNull.Value);
            cmd.Parameters.AddWithValue("@AssignedToName", (object?)job.AssignedToName ?? DBNull.Value);
            cmd.Parameters.AddWithValue("@Status", (object?)job.Status ?? DBNull.Value);
            await conn.OpenAsync().ConfigureAwait(false);
            return await cmd.ExecuteNonQueryAsync().ConfigureAwait(false);
        }

        public Task<bool> DeleteJobAsync(int id)
        {
            throw new NotImplementedException();
        }

        public Task<IEnumerable<UserJobDetails>> GetAllJobsAsync()
        {
            throw new NotImplementedException();
        }

        public Task<UserJobDetails?> GetJobByIdAsync(int id)
        {
            throw new NotImplementedException();
        }

        public Task<UserJobDetails?> UpdateJobAsync(int id, UserJobDetails job)
        {
            throw new NotImplementedException();
        }

        public async Task<int> UpdateStatusByJobIdAndAssignedTo(UpdateStatusForJob  updateStatusForJob)
        {
            int rowsAffected = 0;
            using (SqlConnection conn = new SqlConnection(connectionString))
            using (SqlCommand cmd = new SqlCommand(SpNames.UpdateStatusJobMasterByJobIdAndAssignedTo, conn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                // Add parameters
                cmd.Parameters.Add("@JobId", SqlDbType.NChar, 10).Value = updateStatusForJob.JobId;
                cmd.Parameters.Add("@AssignedTo", SqlDbType.Int).Value = updateStatusForJob.AssignedTo;
                cmd.Parameters.Add("@Status", SqlDbType.NVarChar, 50).Value = updateStatusForJob.Status ?? (object)DBNull.Value;

                try
                {
                    conn.Open();
                    rowsAffected =await cmd.ExecuteNonQueryAsync();

                   // Console.WriteLine($"Rows updated: {rowsAffected}");
                }
                catch (SqlException ex)
                {
                   // Console.WriteLine("SQL Error: " + ex.Message);
                }

                return rowsAffected;
            }
        }
    }
}
