using System;
using System.Collections.Generic;
using System.Data;
using System.Threading;
using System.Threading.Tasks;
using Dapper;
using FrameworkDAL.DTO.SessionStore;
using FrameworkDAL.Query.SessionStore;
using GB5Shared.Connection;
using GB5Shared.DTO.Framework.CommonConfig;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Options;
using Npgsql;
using static GB5Shared.GB5Constant.Constant;

namespace FrameworkDAL.CustomCode.SessionStore
{
    /// <summary>
    /// Performs all MSESSIONSTORE operations against the GB5 system database.
    /// Does NOT use IQueryExecutor (which routes to tenant DBs).
    /// Uses a direct Dapper connection to the system DB, matching the pattern
    /// used by EventLogSubBLL for system-level operations.
    /// </summary>
    public class SessionStoreDAL : ISessionStoreDAL
    {
        private readonly IApplicationConnection _appConnection;
        private readonly IOptionsMonitor<Gb5SystemDTO> _systemDto;
        private readonly ILogger<SessionStoreDAL> _logger;

        public SessionStoreDAL(
            IApplicationConnection appConnection,
            IOptionsMonitor<Gb5SystemDTO> systemDto,
            ILogger<SessionStoreDAL> logger)
        {
            _appConnection = appConnection;
            _systemDto = systemDto;
            _logger = logger;
        }

        // ── Helpers ─────────────────────────────────────────────────────────

        private async Task<IDbConnection> OpenSystemConnectionAsync(CancellationToken ct)
        {
            string connStr = await _appConnection.Gb5SystemConnectionString().ConfigureAwait(false);
            int dbType = _systemDto.CurrentValue.DataBaseType;

            IDbConnection conn = dbType switch
            {
                DBTYPE.SQL => new SqlConnection(connStr),
                DBTYPE.POSTGRESQL => new NpgsqlConnection(connStr),
                _ => throw new NotSupportedException($"Unsupported GB5 system DB type: {dbType}")
            };

            conn.Open();
            return conn;
        }

        private string UpsertSql
        {
            get
            {
                int dbType = _systemDto.CurrentValue.DataBaseType;
                return dbType == DBTYPE.POSTGRESQL
                    ? SessionStoreQB.UPSERT_SESSION_POSTGRESQL
                    : SessionStoreQB.UPSERT_SESSION_SQL;
            }
        }

        private string DeleteOldSql
        {
            get
            {
                int dbType = _systemDto.CurrentValue.DataBaseType;
                return dbType == DBTYPE.POSTGRESQL
                    ? SessionStoreQB.DELETE_OLD_SESSIONS_POSTGRESQL
                    : SessionStoreQB.DELETE_OLD_SESSIONS_SQL;
            }
        }

        private string GetUnusedSql
        {
            get
            {
                int dbType = _systemDto.CurrentValue.DataBaseType;
                return dbType == DBTYPE.POSTGRESQL
                    ? SessionStoreQB.GET_UNUSED_SESSIONS_POSTGRESQL
                    : SessionStoreQB.GET_UNUSED_SESSIONS_SQL;
            }
        }

        // ── Operations ───────────────────────────────────────────────────────

        public async Task UpsertSessionAsync(SessionStoreDTO dto, CancellationToken ct = default)
        {
            using IDbConnection conn = await OpenSystemConnectionAsync(ct).ConfigureAwait(false);
            await conn.ExecuteAsync(
                new CommandDefinition(
                    UpsertSql,
                    new
                    {
                        dto.ServerconfigId,
                        dto.LoginEventLogId,
                        dto.LoginTime,
                        dto.LastLoginUsedTime,
                        dto.MachineIp,
                        dto.UserId,
                        dto.UserCode,
                        dto.UserName
                    },
                    cancellationToken: ct)).ConfigureAwait(false);

            _logger.LogInformation(
                "SessionStore upserted | ServerconfigId={ServerconfigId} LoginEventLogId={LoginEventLogId} UserId={UserId}",
                dto.ServerconfigId, dto.LoginEventLogId, dto.UserId);
        }

        public async Task UpdateSessionAsync(int serverConfigId, int loginEventLogId, DateTime lastUsedTime, CancellationToken ct = default)
        {
            int count = await GetSessionCountAsync(serverConfigId, loginEventLogId, ct).ConfigureAwait(false);
            if (count == 0)
            {
                _logger.LogWarning(
                    "SessionStore update skipped — session not found | ServerconfigId={ServerconfigId} LoginEventLogId={LoginEventLogId}",
                    serverConfigId, loginEventLogId);
                return;
            }

            using IDbConnection conn = await OpenSystemConnectionAsync(ct).ConfigureAwait(false);
            await conn.ExecuteAsync(
                new CommandDefinition(
                    SessionStoreQB.UPDATE_SESSION,
                    new { LastLoginUsedTime = lastUsedTime, ServerconfigId = serverConfigId, LoginEventLogId = loginEventLogId },
                    cancellationToken: ct)).ConfigureAwait(false);
        }

        public async Task DeleteSessionAsync(int serverConfigId, int loginEventLogId, CancellationToken ct = default)
        {
            using IDbConnection conn = await OpenSystemConnectionAsync(ct).ConfigureAwait(false);
            await conn.ExecuteAsync(
                new CommandDefinition(
                    SessionStoreQB.DELETE_SESSION,
                    new { ServerconfigId = serverConfigId, LoginEventLogId = loginEventLogId },
                    cancellationToken: ct)).ConfigureAwait(false);

            _logger.LogInformation(
                "SessionStore deleted | ServerconfigId={ServerconfigId} LoginEventLogId={LoginEventLogId}",
                serverConfigId, loginEventLogId);
        }

        public async Task<int> GetSessionCountAsync(int serverConfigId, int loginEventLogId, CancellationToken ct = default)
        {
            using IDbConnection conn = await OpenSystemConnectionAsync(ct).ConfigureAwait(false);
            return await conn.ExecuteScalarAsync<int>(
                new CommandDefinition(
                    SessionStoreQB.SELECT_SESSION_COUNT,
                    new { ServerconfigId = serverConfigId, LoginEventLogId = loginEventLogId },
                    cancellationToken: ct)).ConfigureAwait(false);
        }


        public async Task<IEnumerable<UnusedSessionDTO>> GetUnusedSessionsAsync(int serverConfigId, int expiryMinutes, CancellationToken ct = default)
        {
            using IDbConnection conn = await OpenSystemConnectionAsync(ct).ConfigureAwait(false);
            return await conn.QueryAsync<UnusedSessionDTO>(
                new CommandDefinition(
                    GetUnusedSql,
                    new { ServerconfigId = serverConfigId, ExpiryTime = expiryMinutes },
                    cancellationToken: ct)).ConfigureAwait(false);
        }

        public async Task DeleteOldSessionsAsync(DateTime todayDate, CancellationToken ct = default)
        {
            using IDbConnection conn = await OpenSystemConnectionAsync(ct).ConfigureAwait(false);
            int rows = await conn.ExecuteAsync(
                new CommandDefinition(
                    DeleteOldSql,
                    new { TodayDate = todayDate },
                    cancellationToken: ct)).ConfigureAwait(false);

            _logger.LogInformation("SessionStore cleanup removed {RowCount} stale session(s)", rows);
        }
    }
}