DbHelper

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Threading.Tasks;

namespace App.Helpers
{
    /// <summary>
    /// DbHelper - สุดติ่งกระดิ่งแมวเวอร์ชันปรับปรุง
    /// ✅ รองรับ async, ✅ memory ดีขึ้น, ✅ type-safe, ✅ reuse สูง
    /// </summary>
    public class DbHelper : IDisposable
    {
        private readonly ConfigHelper _config;
        private bool _disposed = false;

        public DbHelper()
        {
            _config = new ConfigHelper();
        }

        // --- Connection String ---
        protected string GetConnectionString(string name)
        {
            if (string.IsNullOrWhiteSpace(name)) 
                return string.Empty;

            try
            {
                return name.Equals("DEFAULT", StringComparison.OrdinalIgnoreCase)
                    ? _config.GetConnectionString()
                    : _config.GetConfig(name);
            }
            catch
            {
                return string.Empty;
            }
        }

        // --- ExecuteNonQuery (Insert/Update/Delete) ---
        public async Task<int> ExecuteAsync(
            string connectionStringName,
            string sql,
            params SqlParameter[] parameters)
        {
            var cs = EncryptionHelper.Decrypt(GetConnectionString(connectionStringName));
            if (string.IsNullOrEmpty(cs))
                throw new InvalidOperationException("Connection string is empty or invalid.");

            using var conn = new SqlConnection(cs);
            using var cmd = CreateCommand(conn, sql, parameters);

            await conn.OpenAsync();
            return await cmd.ExecuteNonQueryAsync();
        }

        // --- ExecuteScalar ---
        public async Task<object> ExecuteScalarAsync(
            string connectionStringName,
            string sql,
            params SqlParameter[] parameters)
        {
            var cs = EncryptionHelper.Decrypt(GetConnectionString(connectionStringName));
            if (string.IsNullOrEmpty(cs))
                throw new InvalidOperationException("Connection string is empty or invalid.");

            using var conn = new SqlConnection(cs);
            using var cmd = CreateCommand(conn, sql, parameters);

            await conn.OpenAsync();
            return await cmd.ExecuteScalarAsync() ?? DBNull.Value;
        }

        // --- Query to List<Dictionary<string, object>> + Schema ---
        public async Task<(List<Dictionary<string, object>> Rows, List<ColumnInfo> Schema)> 
            QueryAsync(
                string connectionStringName,
                string sql,
                params SqlParameter[] parameters)
        {
            var cs = EncryptionHelper.Decrypt(GetConnectionString(connectionStringName));
            if (string.IsNullOrEmpty(cs))
                throw new InvalidOperationException("Connection string is empty or invalid.");

            var rows = new List<Dictionary<string, object>>();
            var schema = new List<ColumnInfo>();
            var schemaMap = new Dictionary<string, ColumnInfo>(StringComparer.OrdinalIgnoreCase);

            using var conn = new SqlConnection(cs);
            using var cmd = CreateCommand(conn, sql, parameters);

            await conn.OpenAsync();
            using var reader = await cmd.ExecuteReaderAsync();

            // อ่าน schema ก่อน
            var schemaTable = reader.GetSchemaTable();
            foreach (DataRow row in schemaTable.Rows)
            {
                var col = new ColumnInfo(
                    name: row["ColumnName"].ToString(),
                    dataType: (Type)row["DataType"],
                    ordinal: (int)row["ColumnOrdinal"],
                    isNullable: (bool)row["AllowDBNull"]
                );
                schema.Add(col);
                schemaMap[col.Name] = col;
            }

            // อ่านข้อมูล
            while (await reader.ReadAsync())
            {
                var rowDict = new Dictionary<string, object>(StringComparer.OrdinalIgnoreCase);
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    var value = reader.GetValue(i);
                    rowDict[reader.GetName(i)] = value ?? DBNull.Value;
                }
                rows.Add(rowDict);
            }

            return (rows, schema);
        }

        // --- Helper: สร้าง Command ---
        private static DbCommand CreateCommand(DbConnection conn, string sql, SqlParameter[] parameters)
        {
            var cmd = conn.CreateCommand();
            cmd.CommandText = sql;
            cmd.CommandTimeout = 600;
            cmd.CommandType = CommandType.Text;

            if (parameters?.Length > 0)
            {
                foreach (var p in parameters)
                {
                    cmd.Parameters.Add(p);
                }
            }

            return cmd;
        }

        // --- IDisposable ---
        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        protected virtual void Dispose(bool disposing)
        {
            if (_disposed) return;
            if (disposing)
            {
                // ถ้ามี resource เช่น connection pool ฯลฯ จัดการตรงนี้
            }
            _disposed = true;
        }
    }

    // --- Column Info Record ---
    public record ColumnInfo(
        string Name,
        Type DataType,
        int Ordinal,
        bool IsNullable
    );
}

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Threading.Tasks;

namespace App.Helpers
{
    /// <summary>
    /// DbHelper - สุดติ่งกระดิ่งแมวเวอร์ชันปรับปรุง
    /// ✅ รองรับ async, ✅ memory ดีขึ้น, ✅ type-safe, ✅ reuse สูง
    /// </summary>
    public class DbHelper : IDisposable
    {
        private readonly ConfigHelper _config;
        private bool _disposed = false;

        public DbHelper()
        {
            _config = new ConfigHelper();
        }

        // --- Connection String ---
        protected string GetConnectionString(string name)
        {
            if (string.IsNullOrWhiteSpace(name)) 
                return string.Empty;

            try
            {
                return name.Equals("DEFAULT", StringComparison.OrdinalIgnoreCase)
                    ? _config.GetConnectionString()
                    : _config.GetConfig(name);
            }
            catch
            {
                return string.Empty;
            }
        }

        // --- ExecuteNonQuery (Insert/Update/Delete) ---
        public async Task<int> ExecuteAsync(
            string connectionStringName,
            string sql,
            params SqlParameter[] parameters)
        {
            var cs = EncryptionHelper.Decrypt(GetConnectionString(connectionStringName));
            if (string.IsNullOrEmpty(cs))
                throw new InvalidOperationException("Connection string is empty or invalid.");

            using var conn = new SqlConnection(cs);
            using var cmd = CreateCommand(conn, sql, parameters);

            await conn.OpenAsync();
            return await cmd.ExecuteNonQueryAsync();
        }

        // --- ExecuteScalar ---
        public async Task<object> ExecuteScalarAsync(
            string connectionStringName,
            string sql,
            params SqlParameter[] parameters)
        {
            var cs = EncryptionHelper.Decrypt(GetConnectionString(connectionStringName));
            if (string.IsNullOrEmpty(cs))
                throw new InvalidOperationException("Connection string is empty or invalid.");

            using var conn = new SqlConnection(cs);
            using var cmd = CreateCommand(conn, sql, parameters);

            await conn.OpenAsync();
            return await cmd.ExecuteScalarAsync() ?? DBNull.Value;
        }

        // --- Query to List<Dictionary<string, object>> + Schema ---
        public async Task<(List<Dictionary<string, object>> Rows, List<ColumnInfo> Schema)> 
            QueryAsync(
                string connectionStringName,
                string sql,
                params SqlParameter[] parameters)
        {
            var cs = EncryptionHelper.Decrypt(GetConnectionString(connectionStringName));
            if (string.IsNullOrEmpty(cs))
                throw new InvalidOperationException("Connection string is empty or invalid.");

            var rows = new List<Dictionary<string, object>>();
            var schema = new List<ColumnInfo>();
            var schemaMap = new Dictionary<string, ColumnInfo>(StringComparer.OrdinalIgnoreCase);

            using var conn = new SqlConnection(cs);
            using var cmd = CreateCommand(conn, sql, parameters);

            await conn.OpenAsync();
            using var reader = await cmd.ExecuteReaderAsync();

            // อ่าน schema ก่อน
            var schemaTable = reader.GetSchemaTable();
            foreach (DataRow row in schemaTable.Rows)
            {
                var col = new ColumnInfo(
                    name: row["ColumnName"].ToString(),
                    dataType: (Type)row["DataType"],
                    ordinal: (int)row["ColumnOrdinal"],
                    isNullable: (bool)row["AllowDBNull"]
                );
                schema.Add(col);
                schemaMap[col.Name] = col;
            }

            // อ่านข้อมูล
            while (await reader.ReadAsync())
            {
                var rowDict = new Dictionary<string, object>(StringComparer.OrdinalIgnoreCase);
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    var value = reader.GetValue(i);
                    rowDict[reader.GetName(i)] = value ?? DBNull.Value;
                }
                rows.Add(rowDict);
            }

            return (rows, schema);
        }

        // --- Helper: สร้าง Command ---
        private static DbCommand CreateCommand(DbConnection conn, string sql, SqlParameter[] parameters)
        {
            var cmd = conn.CreateCommand();
            cmd.CommandText = sql;
            cmd.CommandTimeout = 600;
            cmd.CommandType = CommandType.Text;

            if (parameters?.Length > 0)
            {
                foreach (var p in parameters)
                {
                    cmd.Parameters.Add(p);
                }
            }

            return cmd;
        }

        // --- IDisposable ---
        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        protected virtual void Dispose(bool disposing)
        {
            if (_disposed) return;
            if (disposing)
            {
                // ถ้ามี resource เช่น connection pool ฯลฯ จัดการตรงนี้
            }
            _disposed = true;
        }
    }

    // --- Column Info Record ---
    public record ColumnInfo(
        string Name,
        Type DataType,
        int Ordinal,
        bool IsNullable
    );
}

var dbHelper = new DbHelper();

// 1. Execute (INSERT/UPDATE)
await dbHelper.ExecuteAsync("DEFAULT", 
    "INSERT INTO Users (Name) VALUES (@name)", 
    new SqlParameter("@name", "John"));

// 2. Scalar
var count = await dbHelper.ExecuteScalarAsync("DEFAULT", 
    "SELECT COUNT(*) FROM Users");

// 3. Query with schema & data
var (rows, schema) = await dbHelper.QueryAsync("DEFAULT", 
    "SELECT Id, Name, BirthDate FROM Users WHERE Age > @age",
    new SqlParameter("@age", 18));

for (int i = 0; i < rows.Count; i++)
{
    var name = rows[i]["Name"].ToString();
    var birthDateType = schema.First(c => c.Name == "BirthDate").DataType; // → typeof(DateTime)
}

var dbHelper = new DbHelper();

// 1. Execute (INSERT/UPDATE)
await dbHelper.ExecuteAsync("DEFAULT", 
    "INSERT INTO Users (Name) VALUES (@name)", 
    new SqlParameter("@name", "John"));

// 2. Scalar
var count = await dbHelper.ExecuteScalarAsync("DEFAULT", 
    "SELECT COUNT(*) FROM Users");

// 3. Query with schema & data
var (rows, schema) = await dbHelper.QueryAsync("DEFAULT", 
    "SELECT Id, Name, BirthDate FROM Users WHERE Age > @age",
    new SqlParameter("@age", 18));

for (int i = 0; i < rows.Count; i++)
{
    var name = rows[i]["Name"].ToString();
    var birthDateType = schema.First(c => c.Name == "BirthDate").DataType; // → typeof(DateTime)
}