using System;
using System.Collections.Generic;
using System.Data;
using System.Dynamic;
using System.Threading.Tasks;
using Microsoft.Extensions.Configuration;
using Oracle.ManagedDataAccess.Client;

namespace gdbg.Services
{
    public class OracleDbService
    {
        private readonly string _connectionString;

        public OracleDbService(IConfiguration configuration)
        {
            _connectionString = configuration.GetConnectionString("OracleDB");
        }

        // 通用的获取数据方法（执行 SELECT 语句），不带参数
        public async Task<List<T>> GetAsync<T>(string query, Func<IDataReader, T> mapFunction)
        {
            return await GetAsync(query, mapFunction, null);  // 调用带参数的重载方法
        }

        // 通用的获取数据方法（执行 SELECT 语句），支持带参数
        public async Task<List<T>> GetAsync<T>(string query, Func<IDataReader, T> mapFunction, List<OracleParameter> parameters)
        {
            var results = new List<T>();

            using (var connection = new OracleConnection(_connectionString))
            {
                await connection.OpenAsync();

                using (var command = new OracleCommand(query, connection))
                {
                    // 添加参数到命令
                    if (parameters != null)
                    {
                        command.Parameters.AddRange(parameters.ToArray());
                    }

                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            results.Add(mapFunction(reader));
                        }
                    }
                }
            }

            return results;
        }
        // 执行存储过程并返回结果
        public async Task<(List<ExpandoObject>[] results, Dictionary<string, object> outputParams)> ExecuteStoredProcedureAsync(
    string procedureName,
    OracleParameter[] parameters,
    params string[] cursorParamNames)
        {
            var results = new List<ExpandoObject>[cursorParamNames.Length];
            var outputParams = new Dictionary<string, object>();

            using (var connection = new OracleConnection(_connectionString))
            {
                await connection.OpenAsync();

                using (var command = new OracleCommand(procedureName, connection))
                {
                    command.CommandType = CommandType.StoredProcedure;

                    // 添加所有输入参数
                    if (parameters != null)
                    {
                        command.Parameters.AddRange(parameters);
                    }

                    // 添加游标输出参数
                    for (int i = 0; i < cursorParamNames.Length; i++)
                    {
                        var cursorParam = new OracleParameter(cursorParamNames[i], OracleDbType.RefCursor)
                        {
                            Direction = ParameterDirection.Output
                        };
                        command.Parameters.Add(cursorParam);
                    }

                    // 执行存储过程
                    await command.ExecuteNonQueryAsync();

                    // 读取每个游标输出
                    for (int i = 0; i < cursorParamNames.Length; i++)
                    {
                        using (var reader = command.Parameters[cursorParamNames[i]].Value as OracleDataReader)
                        {
                            var cursorResults = new List<ExpandoObject>();
                            while (await reader.ReadAsync())
                            {
                                dynamic expando = new ExpandoObject();
                                var expandoDict = (IDictionary<string, object>)expando;

                                for (int j = 0; j < reader.FieldCount; j++)
                                {
                                    string columnName = reader.GetName(j);
                                    object columnValue = reader.IsDBNull(j) ? null : reader.GetValue(j);
                                    expandoDict[columnName] = columnValue;
                                }

                                cursorResults.Add(expando);
                            }
                            results[i] = cursorResults;
                        }
                    }

                    // 获取输出参数的值
                    foreach (var param in parameters)
                    {
                        if (param.Direction == ParameterDirection.Output)
                        {
                            outputParams[param.ParameterName] = param.Value;
                        }
                    }
                }
            }

            return (results, outputParams);
        }



        // 执行存储过程的方法
        public async Task ExecuteProcedureAsync(string procedureCall, List<OracleParameter> parameters)
        {
            using (var connection = new OracleConnection(_connectionString))
            {
                await connection.OpenAsync();

                using (var command = new OracleCommand(procedureCall, connection))
                {
                    command.CommandType = CommandType.Text; // 使用文本命令来调用存储过程
                    if (parameters != null)
                    {
                        command.Parameters.AddRange(parameters.ToArray());
                    }

                    await command.ExecuteNonQueryAsync(); // 执行存储过程
                }
            }
        }
        // 通用的插入数据方法（执行 INSERT 语句）
        public async Task<int> InsertAsync(string query, List<OracleParameter> parameters)
        {
            return await ExecuteNonQueryAsync(query, parameters);
        }

        // 通用的更新数据方法（执行 UPDATE 语句）
        public async Task<int> UpdateAsync(string query, List<OracleParameter> parameters)
        {
            return await ExecuteNonQueryAsync(query, parameters);
        }

        // 通用的删除数据方法（执行 DELETE 语句）
        public async Task<int> DeleteAsync(string query, List<OracleParameter> parameters)
        {
            return await ExecuteNonQueryAsync(query, parameters);
        }

        // 执行非查询语句的方法（INSERT, UPDATE, DELETE）
        private async Task<int> ExecuteNonQueryAsync(string query, List<OracleParameter> parameters)
        {
            using (var connection = new OracleConnection(_connectionString))
            {
                await connection.OpenAsync();

                using (var command = new OracleCommand(query, connection))
                {
                    if (parameters != null)
                    {
                        command.Parameters.AddRange(parameters.ToArray());
                    }

                    return await command.ExecuteNonQueryAsync();
                }
            }
        }
    }
}