using DbController;
using Tabletop.Core.Models;

namespace Tabletop.Core.Services
{
    public class SurfaceService : IModelService<Surface, int>
    {
        public async Task CreateAsync(Surface input, IDbController dbController, CancellationToken cancellationToken = default)
        {
            string sql = $@"INSERT INTO Surfaces
                (
                Image
                )
                VALUES
                (
                @IMAGE
                ); {dbController.GetLastIdSql()}";

            input.SurfaceId = await dbController.GetFirstAsync<int>(sql, input.GetParameters(), cancellationToken);

            foreach (var description in input.Description)
            {
                sql = @"INSERT INTO SurfaceDescription
                    (
                    SurfaceId,
                    Code,
                    Name,
                    Description
                    )
                    VALUES
                    (
                    @SURFACE_ID,
                    @CODE,
                    @NAME,
                    @DESCRIPTION
                    )";

                var parameters = new
                {
                    BATTLEGROUND_ID = input.SurfaceId,
                    CODE = description.Code,
                    NAME = description.Name,
                    description = description.Description,
                };

                await dbController.QueryAsync(sql, parameters, cancellationToken);
            }
        }

        public async Task DeleteAsync(Surface input, IDbController dbController, CancellationToken cancellationToken = default)
        {
            string sql = "DELETE FROM Surfaces WHERE SurfaceId = @SURFACE_ID";

            await dbController.QueryAsync(sql, new
            {
                SURFACE_ID = input.SurfaceId
            }, cancellationToken);
        }

        public static async Task<List<Surface>> GetAllAsync(IDbController dbController, CancellationToken cancellationToken = default)
        {
            string sql = "SELECT * FROM Surfaces";

            var list = await dbController.SelectDataAsync<Surface>(sql, cancellationToken: cancellationToken);

            foreach(var item in list)
            {
                if(item.Image != null)
                {
                    string base64String = Convert.ToBase64String(item.Image);
                    item.ConvertedImage = $"data:image/png;base64,{base64String}";
                }
            }

            await LoadSurfaceDescriptionsAsync(list, dbController, cancellationToken);
            return list;
        }

        public async Task<Surface?> GetAsync(int surfaceId, IDbController dbController, CancellationToken cancellationToken = default)
        {
            string sql = @"SELECT * FROM Surfaces WHERE SurfaceId = @SURFACE_ID";

            var surface = await dbController.GetFirstAsync<Surface>(sql, new
            {
                SURFACE_ID = surfaceId
            }, cancellationToken);

            return surface;
        }

        private static async Task LoadSurfaceDescriptionsAsync(List<Surface> list, IDbController dbController, CancellationToken cancellationToken = default)
        {
            cancellationToken.ThrowIfCancellationRequested();
            if (list.Count != 0)
            {
                IEnumerable<int> surfaceIds = list.Select(x => x.SurfaceId);
                string sql = $"SELECT * FROM SurfaceDescription WHERE SurfaceId IN ({string.Join(",", surfaceIds)})";
                List<SurfaceDescription> descriptions = await dbController.SelectDataAsync<SurfaceDescription>(sql, null, cancellationToken);

                foreach (var battleLocation in list)
                {
                    battleLocation.Description = [.. descriptions.Where(x => x.SurfaceId == battleLocation.SurfaceId)];
                }
            }
        }

        public Task UpdateAsync(Surface input, IDbController dbController, CancellationToken cancellationToken = default)
        {
            throw new NotImplementedException();
        }
    }
}