using Npgsql;
using OpcUaManager.Models;

namespace OpcUaManager.Services;

/// <summary>
/// PostgreSQL opc_history 테이블에 대한 읽기/쓰기를 담당합니다.
/// 원본 SaveToDatabase 함수의 INSERT 패턴을 그대로 유지하며,
/// OPC 태그 읽기(OpcSessionService)와 DB 저장을 조율합니다.
/// </summary>
public class DatabaseService
{
    private readonly ILogger<DatabaseService> _logger;
    private readonly OpcSessionService        _sessionSvc;

    public DatabaseService(ILogger<DatabaseService> logger, OpcSessionService sessionSvc)
    {
        _logger     = logger;
        _sessionSvc = sessionSvc;
    }

    // ── 연결 문자열 빌더 ──────────────────────────────────────────────

    private static string BuildConnString(DbWriteRequest req)
        => $"Host={req.DbHost};Username={req.DbUser};Password={req.DbPassword};Database={req.DbName}";

    // ── DB/테이블 초기화 (최초 한 번) ────────────────────────────────

    /// <summary>
    /// opc_history 테이블이 없으면 자동 생성합니다.
    /// 원본 코드에는 없었지만, 프로젝트화 시 처음 실행 환경을 위해 추가합니다.
    /// </summary>
    public async Task<(bool Ok, string Msg)> EnsureTableAsync(DbWriteRequest req)
    {
        const string ddl = """
            CREATE TABLE IF NOT EXISTS opc_history (
                id          BIGSERIAL PRIMARY KEY,
                tag_name    TEXT        NOT NULL,
                tag_value   DOUBLE PRECISION NOT NULL,
                status_code TEXT        NOT NULL,
                created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
            );
            """;
        try
        {
            await using var conn = new NpgsqlConnection(BuildConnString(req));
            await conn.OpenAsync();
            await using var cmd = new NpgsqlCommand(ddl, conn);
            await cmd.ExecuteNonQueryAsync();
            return (true, "테이블 확인/생성 완료");
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "테이블 초기화 실패");
            return (false, ex.Message);
        }
    }

    // ── OPC 읽기 + DB 저장 루프 ──────────────────────────────────────

    /// <summary>
    /// OPC 노드를 req.Count 회 읽고 각 결과를 DB에 저장합니다.
    /// 원본 for 루프 + SaveToDatabase 패턴을 그대로 유지합니다.
    /// </summary>
    public async Task<DbWriteResult> WriteLoopAsync(DbWriteRequest req)
    {
        if (!_sessionSvc.IsConnected)
            return new DbWriteResult { Success = false, Message = "OPC 세션이 연결되어 있지 않습니다." };

        var (tableOk, tableMsg) = await EnsureTableAsync(req);
        if (!tableOk)
            return new DbWriteResult { Success = false, Message = $"테이블 오류: {tableMsg}" };

        var records = new List<DbWriteRecord>();
        int saved   = 0;

        for (int i = 1; i <= req.Count; i++)
        {
            // ── 1. OPC 태그 읽기 ────────────────────────────────────
            double val     = 0;
            string status  = "Unknown";
            bool   dbSaved = false;

            try
            {
                var (rawVal, sc) = await _sessionSvc.ReadValueAsync(req.TagNodeId);
                val    = Convert.ToDouble(rawVal);
                status = sc;
                _logger.LogInformation("[{I}/{N}] {Tag} = {Val} ({Status})",
                    i, req.Count, req.TagName, val, status);
            }
            catch (Exception ex)
            {
                status = $"ReadError: {ex.Message}";
                _logger.LogWarning("[{I}/{N}] OPC 읽기 실패: {Msg}", i, req.Count, ex.Message);
            }

            // ── 2. DB 저장 (원본 SaveToDatabase 로직) ───────────────
            try
            {
                await SaveToDatabaseAsync(req, val, status);
                dbSaved = true;
                saved++;
                _logger.LogInformation("[{I}/{N}] DB 저장 완료.", i, req.Count);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "[{I}/{N}] DB 저장 실패", i, req.Count);
            }

            records.Add(new DbWriteRecord
            {
                Seq       = i,
                Timestamp = DateTime.UtcNow,
                TagName   = req.TagName,
                Value     = val,
                StatusCode = status,
                DbSaved   = dbSaved
            });

            // ── 3. 인터벌 대기 (마지막 회차는 생략) ─────────────────
            if (i < req.Count && req.IntervalMs > 0)
                await Task.Delay(req.IntervalMs);
        }

        return new DbWriteResult
        {
            Success    = saved > 0,
            Message    = $"{saved}/{req.Count}회 저장 완료",
            SavedCount = saved,
            Records    = records
        };
    }

    // ── 원본 SaveToDatabase (INSERT 패턴 동일) ────────────────────────

    private async Task SaveToDatabaseAsync(DbWriteRequest req, double val, string status)
    {
        await using var conn = new NpgsqlConnection(BuildConnString(req));
        await conn.OpenAsync();

        // 원본 동일 INSERT
        const string sql =
            "INSERT INTO opc_history (tag_name, tag_value, status_code) " +
            "VALUES (@tag, @val, @status)";

        await using var cmd = new NpgsqlCommand(sql, conn);
        cmd.Parameters.AddWithValue("tag",    req.TagName);
        cmd.Parameters.AddWithValue("val",    val);
        cmd.Parameters.AddWithValue("status", status);

        await cmd.ExecuteNonQueryAsync();
    }

    // ── DB 조회 ───────────────────────────────────────────────────────

    public async Task<DbQueryResult> QueryRecentAsync(DbWriteRequest req, int limit = 100)
    {
        try
        {
            await using var conn = new NpgsqlConnection(BuildConnString(req));
            await conn.OpenAsync();

            string sql = $"""
                SELECT id, tag_name, tag_value, status_code, created_at
                FROM opc_history
                ORDER BY id DESC
                LIMIT {limit}
                """;

            await using var cmd  = new NpgsqlCommand(sql, conn);
            await using var reader = await cmd.ExecuteReaderAsync();

            var rows = new List<OpcHistoryRow>();
            while (await reader.ReadAsync())
            {
                rows.Add(new OpcHistoryRow
                {
                    Id         = reader.GetInt64(0),
                    TagName    = reader.GetString(1),
                    TagValue   = reader.GetDouble(2),
                    StatusCode = reader.GetString(3),
                    CreatedAt  = reader.GetDateTime(4)
                });
            }

            // 전체 카운트
            await reader.CloseAsync();
            await using var cntCmd = new NpgsqlCommand("SELECT COUNT(*) FROM opc_history", conn);
            long total = (long)(await cntCmd.ExecuteScalarAsync() ?? 0L);

            return new DbQueryResult
            {
                Success    = true,
                TotalCount = (int)total,
                Rows       = rows
            };
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "DB 조회 실패");
            return new DbQueryResult { Success = false, Message = ex.Message };
        }
    }

    // ── 연결 테스트 ───────────────────────────────────────────────────

    public async Task<(bool Ok, string Msg)> TestConnectionAsync(DbWriteRequest req)
    {
        try
        {
            await using var conn = new NpgsqlConnection(BuildConnString(req));
            await conn.OpenAsync();
            await using var cmd = new NpgsqlCommand("SELECT version()", conn);
            var ver = await cmd.ExecuteScalarAsync();
            return (true, $"연결 성공: {ver}");
        }
        catch (Exception ex)
        {
            return (false, ex.Message);
        }
    }
}