using System;
using System.Collections.Generic;
using System.Threading;
using System.Threading.Tasks;
using GB5Shared.Query.Framework.SchemaIntrospection;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.DTO.Framework.SchemaIntrospection;
using GB5Shared.QueryExecutor;
using GB5Shared.Validation;
using static GB5Shared.GB5Constant.Constant;

namespace GB5Shared.SchemaIntrospection
{
    /// <summary>
    /// "Own DB" path of the Analytics Catalog Wizard (Phase 1) — introspects the SAME database
    /// the current request is already connected to via IQueryExecutor. No connection string
    /// involved; branches on LoginDTO.DatabaseType to pick SQL Server vs PostgreSQL SQL.
    /// </summary>
    public class SchemaIntrospectionDAL : ISchemaIntrospectionDAL
    {
        private readonly IQueryExecutor _queryExecutor;
        private readonly IValidation _validation;

        public SchemaIntrospectionDAL(IQueryExecutor queryExecutor, IValidation validation)
        {
            _queryExecutor = queryExecutor;
            _validation = validation;
        }

        public async Task<IEnumerable<SchemaTableColumnDTO>> GetOwnDbColumnsAsync(LoginDTO login, CancellationToken ct)
        {
            try
            {
                string sql = login.DatabaseType == DBType.PostGre
                    ? SchemaIntrospectionQB.GET_OWN_DB_COLUMNS_POSTGRES
                    : SchemaIntrospectionQB.GET_OWN_DB_COLUMNS_SQLSERVER;

                return await _queryExecutor.QueryAsync<SchemaTableColumnDTO>(
                    login, sql, null!, cancellationToken: ct).ConfigureAwait(false);
            }
            catch (Exception ex)
            {
                string error = await _validation.HandleException(ex, "Error retrieving own-DB schema columns").ConfigureAwait(false);
                throw new Exception(error);
            }
        }

        public async Task<IEnumerable<SchemaForeignKeyDTO>> GetOwnDbForeignKeysAsync(LoginDTO login, CancellationToken ct)
        {
            try
            {
                string sql = login.DatabaseType == DBType.PostGre
                    ? SchemaIntrospectionQB.GET_OWN_DB_FOREIGNKEYS_POSTGRES
                    : SchemaIntrospectionQB.GET_OWN_DB_FOREIGNKEYS_SQLSERVER;

                return await _queryExecutor.QueryAsync<SchemaForeignKeyDTO>(
                    login, sql, null!, cancellationToken: ct).ConfigureAwait(false);
            }
            catch (Exception ex)
            {
                string error = await _validation.HandleException(ex, "Error retrieving own-DB foreign keys").ConfigureAwait(false);
                throw new Exception(error);
            }
        }
    }
}