using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Threading;
using System.Threading.Tasks;
using GB5Shared.Draft.Query;
using GB5Shared.DTO.Draft;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using static GB5Shared.GB5Constant.Constant;
using GB5Shared.Validation;
using Newtonsoft.Json;

namespace GB5Shared.Draft
{
    public class DraftRowDAL : IDraftRowDAL
    {
        private readonly IQueryExecutor _qe;
        private readonly IValidation    _validation;

        public DraftRowDAL(IQueryExecutor queryExecutor, IValidation validation)
        {
            _qe         = queryExecutor;
            _validation = validation;
        }

        // ── Header ───────────────────────────────────────────────────────

        public async Task<int> GetDraftIdBySessionAsync(
            string sessionGuid, LoginDTO login, CancellationToken ct)
        {
            try
            {
                string sql = login.DatabaseType == DBType.PostGre
                    ? DraftRowQB.GET_DRAFT_ID_BY_SESSION_PG
                    : DraftRowQB.GET_DRAFT_ID_BY_SESSION_SQL;

                return await _qe.ExecuteScalarAsync<int>(
                    login, sql, new { SessionGuid = sessionGuid },
                    cancellationToken: ct).ConfigureAwait(false);
            }
            catch (Exception ex)
            {
                string error = await _validation.HandleException(ex, "Error checking draft session").ConfigureAwait(false);
                throw new Exception(error);
            }
        }

        public async Task<DraftHeaderDTO?> GetDraftHeaderAsync(
            string sessionGuid, LoginDTO login, CancellationToken ct)
        {
            try
            {
                string sql = login.DatabaseType == DBType.PostGre
                    ? DraftRowQB.HEADER_GET_PG
                    : DraftRowQB.HEADER_GET_SQL;

                var results = await _qe.QueryAsync<DraftHeaderDTO>(
                    login, sql,
                    new { SessionGuid = sessionGuid },
                    cancellationToken: ct).ConfigureAwait(false);

                return results.FirstOrDefault();
            }
            catch (Exception ex)
            {
                string error = await _validation.HandleException(ex, "Error loading draft header").ConfigureAwait(false);
                throw new Exception(error);
            }
        }

        public async Task<int> UpsertHeaderAsync(
            DraftSessionDTO dto, int draftId, LoginDTO login, CancellationToken ct)
        {
            try
            {
                if (login.DatabaseType == DBType.PostGre)
                {
                    // HEADER_UPSERT_PG already ends with "SELECT draftid FROM tdraft WHERE
                    // sessionguid = @SessionGuid" in the same batch — ExecuteScalarAsync reads
                    // that back directly. This used to be ExecuteAsync (which discards the
                    // result set) followed by a second GetDraftIdBySessionAsync round trip for
                    // a value the first query already returned — a needless extra query on a
                    // hot path (every debounced header edit, plus the 30s idle re-check).
                    return await _qe.ExecuteScalarAsync<int>(
                        login, DraftRowQB.HEADER_UPSERT_PG,
                        new
                        {
                            DraftId              = draftId,
                            dto.SessionGuid,
                            dto.EntityId,
                            dto.MenuId,
                            dto.BizTransactionTypeId,
                            dto.OuId,
                            dto.Code,
                            dto.Name,
                            dto.Particulars,
                            dto.DraftNumber,
                            dto.JsonObject,
                            UserId = login.UserId
                        },
                        cancellationToken: ct).ConfigureAwait(false);
                }

                // SQL Server: EXEC_HEADER_UPSERT_SQL's batch ends with "SELECT @OutDraftId AS
                // DraftId" after the SP call, so ExecuteScalarAsync reads the SP's OUTPUT
                // parameter back in the same round trip — same fix as the Postgres branch above.
                return await _qe.ExecuteScalarAsync<int>(
                    login, DraftRowQB.EXEC_HEADER_UPSERT_SQL,
                    new
                    {
                        DraftId              = draftId,
                        dto.SessionGuid,
                        dto.EntityId,
                        dto.MenuId,
                        dto.BizTransactionTypeId,
                        dto.OuId,
                        dto.Code,
                        dto.Name,
                        dto.Particulars,
                        dto.DraftNumber,
                        dto.JsonObject,
                        UserId = login.UserId
                    },
                    cancellationToken: ct).ConfigureAwait(false);
            }
            catch (Exception ex)
            {
                string error = await _validation.HandleException(ex, "Error upserting draft header").ConfigureAwait(false);
                throw new Exception(error);
            }
        }

        // ── Rows ─────────────────────────────────────────────────────────

        public async Task UpsertRowAsync(
            DraftRowDTO dto, LoginDTO login, CancellationToken ct)
        {
            try
            {
                string sql = login.DatabaseType == DBType.PostGre
                    ? DraftRowQB.ROW_UPSERT_PG
                    : DraftRowQB.EXEC_ROW_UPSERT_SQL;

                await _qe.ExecuteAsync(
                    login, sql,
                    new
                    {
                        dto.DraftId,
                        dto.SessionGuid,
                        ClientId = login.ClientId,
                        dto.RowGuid,
                        dto.ParentRowGuid,
                        dto.ObjectTypeId,
                        RowState       = (byte)dto.RowState,
                        dto.ServerRowId,
                        dto.ParentServerId,
                        dto.SortOrder,
                        dto.RowData,
                        ModifiedOn = DateTime.UtcNow,
                        CreatedOn = DateTime.UtcNow
                    },
                    cancellationToken: ct).ConfigureAwait(false);
            }
            catch (Exception ex)
            {
                string error = await _validation.HandleException(ex, "Error upserting draft row").ConfigureAwait(false);
                throw new Exception(error);
            }
        }

        public async Task<IEnumerable<DraftRowDTO>> GetDraftRowsAsync(
            string sessionGuid, LoginDTO login, CancellationToken ct)
        {
            try
            {
                string sql = login.DatabaseType == DBType.PostGre
                    ? DraftRowQB.ROWS_GET_PG
                    : DraftRowQB.EXEC_ROWS_GET_SQL;

                return await _qe.QueryAsync<DraftRowDTO>(
                    login, sql,
                    new { SessionGuid = sessionGuid, ClientId = login.ClientId },
                    cancellationToken: ct).ConfigureAwait(false);
            }
            catch (Exception ex)
            {
                string error = await _validation.HandleException(ex, "Error loading draft rows").ConfigureAwait(false);
                throw new Exception(error);
            }
        }

        public async Task DeleteRowAsync(
            string sessionGuid, Guid rowGuid, LoginDTO login, CancellationToken ct)
        {
            try
            {
                string sql = login.DatabaseType == DBType.PostGre
                    ? DraftRowQB.ROW_DELETE_PG
                    : DraftRowQB.ROW_DELETE_SQL;

                await _qe.ExecuteAsync(
                    login, sql,
                    new { SessionGuid = sessionGuid, RowGuid = rowGuid },
                    cancellationToken: ct).ConfigureAwait(false);
            }
            catch (Exception ex)
            {
                string error = await _validation.HandleException(ex, "Error deleting draft row").ConfigureAwait(false);
                throw new Exception(error);
            }
        }

        public async Task<IEnumerable<DraftPoolItemDTO>> GetDraftPoolAsync(
            int menuId, LoginDTO login, CancellationToken ct)
        {
            try
            {
                string sql = login.DatabaseType == DBType.PostGre
                    ? DraftRowQB.POOL_GET_PG
                    : DraftRowQB.EXEC_POOL_GET_SQL;

                return await _qe.QueryAsync<DraftPoolItemDTO>(
                    login, sql,
                    new { MenuId = menuId, UserId = login.UserId },
                    cancellationToken: ct).ConfigureAwait(false);
            }
            catch (Exception ex)
            {
                string error = await _validation.HandleException(ex, "Error loading draft pool").ConfigureAwait(false);
                throw new Exception(error);
            }
        }

        // ── Cleanup ───────────────────────────────────────────────────────

        public async Task CleanupAsync(
            string sessionGuid, byte actionType, LoginDTO login,
            DbTransaction? tx, CancellationToken ct)
        {
            try
            {
                string sql = login.DatabaseType == DBType.PostGre
                    ? DraftRowQB.CLEANUP_PG
                    : DraftRowQB.EXEC_CLEANUP_SQL;

                await _qe.ExecuteAsync(
                    login, sql,
                    new
                    {
                        SessionGuid = sessionGuid,
                        ClientId    = login.ClientId,
                        ActionType  = actionType,
                        UserId      = login.UserId
                    },
                    tx, ct).ConfigureAwait(false);
            }
            catch (Exception ex)
            {
                string error = await _validation.HandleException(ex, "Error cleaning up draft").ConfigureAwait(false);
                throw new Exception(error);
            }
        }

        // ── Share ─────────────────────────────────────────────────────────

        public async Task ShareAsync(DraftShareDTO dto, LoginDTO login, CancellationToken ct)
        {
            try
            {
                string sql = login.DatabaseType == DBType.PostGre
                    ? DraftRowQB.SHARE_PG
                    : DraftRowQB.EXEC_SHARE_SQL;

                await _qe.ExecuteAsync(
                    login, sql,
                    new
                    {
                        dto.SessionGuid,
                        dto.TargetUserId,
                        RequestingUserId = login.UserId
                    },
                    cancellationToken: ct).ConfigureAwait(false);
            }
            catch (Exception ex)
            {
                string error = await _validation.HandleException(ex, "Error sharing draft").ConfigureAwait(false);
                throw new Exception(error);
            }
        }

        // ── Ownership check ───────────────────────────────────────────────

        public async Task<int> CheckOwnershipAsync(
            string sessionGuid, LoginDTO login, CancellationToken ct)
        {
            try
            {
                string sql = login.DatabaseType == DBType.PostGre
                    ? DraftRowQB.CHECK_OWNER_PG
                    : DraftRowQB.CHECK_OWNER_SQL;

                return await _qe.ExecuteScalarAsync<int>(
                    login, sql,
                    new { SessionGuid = sessionGuid, UserId = login.UserId },
                    cancellationToken: ct).ConfigureAwait(false);
            }
            catch (Exception ex)
            {
                string error = await _validation.HandleException(ex, "Error checking draft ownership").ConfigureAwait(false);
                throw new Exception(error);
            }
        }

        // ── Orphan cleanup ────────────────────────────────────────────────

        public async Task<int> CleanupOrphansAsync(
            int retentionHours, int batchSize, LoginDTO login, CancellationToken ct)
        {
            try
            {
                string sql = login.DatabaseType == DBType.PostGre
                    ? DraftRowQB.CLEANUP_ORPHANS_PG
                    : DraftRowQB.EXEC_CLEANUP_ORPHANS_SQL;

                // SQL Server SP returns a result set with ExpiredCount.
                // PostgreSQL: DELETE does not return a count here — use 0 as fallback.
                if (login.DatabaseType == DBType.PostGre)
                {
                    await _qe.ExecuteAsync(
                        login, sql,
                        new { RetentionHours = retentionHours, BatchSize = batchSize },
                        cancellationToken: ct).ConfigureAwait(false);
                    return 0;
                }

                return await _qe.ExecuteScalarAsync<int>(
                    login, sql,
                    new { RetentionHours = retentionHours, BatchSize = batchSize },
                    cancellationToken: ct).ConfigureAwait(false);
            }
            catch (Exception ex)
            {
                string error = await _validation.HandleException(ex, "Error running orphan draft cleanup").ConfigureAwait(false);
                throw new Exception(error);
            }
        }
    }
}