using IWS5.Core.DTO.IAdmin.Reports;
using IWS5.Core.Enums;
using IWS5.Core.Models;
using IWS5.Core.Services.IAdmin;
using IWS5.DatabaseAccess.Interfaces.IAdmin;
using IWS5.DatabaseAccess.IwsDataModels;
using Serilog;

namespace IWS5.Services.IAdminServices;

public class ReportsService(IReportRepository reportRepository, ILogger logger) : IReportsService
{
    public async Task<CodeResponse<List<ReportDetailsDTO>>> GetReportsAsync()
    {
        logger.Information("Fetching all reports");

        var reports = await reportRepository.ListAsync();

        var dtos = reports.Select(r => new ReportDetailsDTO(
            reportId: r.ReportId,
            name: r.Name,
            description: r.Description!,
            sqlCode: r.SqlCode?
            .ReplaceLineEndings(" ").Trim() ?? string.Empty,
            isActive: r.IsActive
        )).ToList();

        return new CodeResponse<List<ReportDetailsDTO>>(ResponseCode.OK, null, dtos);
    }

    public async Task<CodeResponse<ReportDetailsDTO>> GetReportDetailsAsync(int reportId)
    {
        var report = await reportRepository.GetByIdAsync(reportId);

        if (report == null)
        {
            logger.Warning("Report with ID {ReportId} not found", reportId);
            return new CodeResponse<ReportDetailsDTO>(ResponseCode.NotFound, "Report not found", null);
        }

        var sqlCode = report?.SqlCode?.ReplaceLineEndings(" ").Trim() ?? string.Empty;

        var dto = new ReportDetailsDTO(report.ReportId, report.Name, report.Description, sqlCode, report.IsActive);
        return new CodeResponse<ReportDetailsDTO>(ResponseCode.OK, null, dto);
    }

    public async Task<CodeResponse<RunReportResultDTO>> RunReportAsync(int reportId)
    {
        logger.Information("Running report with ID {ReportId}", reportId);

        var report = await reportRepository.GetByIdAsync(reportId);
        if (report == null)
            return new CodeResponse<RunReportResultDTO>(ResponseCode.NotFound, "Report not found");

        if (string.IsNullOrWhiteSpace(report.SqlCode))
            return new CodeResponse<RunReportResultDTO>(ResponseCode.NotFound, "SQL code is empty");

        try
        {
            var (columns, rows) = await reportRepository.ExecuteReportQueryAsync(report.SqlCode ?? string.Empty);

            var result = new RunReportResultDTO(
                report.ReportId,
                report.Name,
                DateTime.UtcNow,
                columns,
                rows
            );

            return new CodeResponse<RunReportResultDTO>(ResponseCode.OK, null, result);
        }
        catch (Exception ex)
        {
            logger.Error(ex, "Failed to run SQL for report {ReportId}", reportId);
            return new CodeResponse<RunReportResultDTO>(ResponseCode.InternalServerError, ex.Message);
        }
    }

    public async Task<CodeResponse<MakeActiveReportResponseDTO>> UpdateStatusAsync(int reportId)
    {
        logger.Information("Marking report {ReportId} as active", reportId);

        var report = await reportRepository.GetByIdAsync(reportId);
        if (report == null)
        {
            logger.Warning("Report {ReportId} not found", reportId);
            return new CodeResponse<MakeActiveReportResponseDTO>(ResponseCode.NotFound, "Report not found");
        }

        report.IsActive = true;
        await reportRepository.UpdateAsync(report);

        var dto = new MakeActiveReportResponseDTO(reportId, report.IsActive);
        return new CodeResponse<MakeActiveReportResponseDTO>(ResponseCode.OK, null, dto);
    }

    public async Task<CodeResponse<ReportDetailsDTO>> CreateReportAsync(CreateReportRequestDTO request)
    {
        var isSqlCodeValid = await reportRepository.ValidateSqlAsync(request.SqlCode);
        if (!isSqlCodeValid)
            return new CodeResponse<ReportDetailsDTO>(ResponseCode.BadRequest, "SQL code is invalid or cannot be parsed by the database.");

        var report = new Atreport
        {
            Name = request.Name,
            Description = request.Description,
            SqlCode = request.SqlCode,
            IsActive = request.IsActive
        };

        await reportRepository.AddAsync(report);

        var dto = new ReportDetailsDTO(
            report.ReportId,
            report.Name,
            report.Description ?? string.Empty,
            report.SqlCode ?? string.Empty,
            report.IsActive
        );

        return new CodeResponse<ReportDetailsDTO>(ResponseCode.OK, null, dto);
    }


    public async Task<CodeResponse<ReportDetailsDTO>> UpdateReportAsync(int reportId, UpdateReportRequestDTO request)
    {
        var report = await reportRepository.GetByIdAsync(reportId);
        if (report == null)
            return new CodeResponse<ReportDetailsDTO>(ResponseCode.NotFound, "Report not found");

        var isSqlCodeValid = await reportRepository.ValidateSqlAsync(request.SqlCode);
        if (!isSqlCodeValid)
            return new CodeResponse<ReportDetailsDTO>(ResponseCode.BadRequest, "SQL code is invalid or cannot be parsed by the database.");

        report.Name = request.Name;
        report.Description = request.Description;
        report.SqlCode = request.SqlCode;
        report.IsActive = request.IsActive;

        await reportRepository.UpdateAsync(report);

        var dto = new ReportDetailsDTO(report.ReportId, report.Name, report.Description, report.SqlCode ?? string.Empty, report.IsActive);
        return new CodeResponse<ReportDetailsDTO>(ResponseCode.OK, null, dto);
    }

    public async Task<bool> DeleteReportAsync(int reportId)
    {
        var report = await reportRepository.GetByIdAsync(reportId);
        if (report == null)
            return false;

        await reportRepository.DeleteAsync(report);

        return true;
    }
}

