using System;
using Microsoft.Data.SqlClient;
using SyncEngine.Configuration;

namespace SyncEngine.SqlServer
{
    public sealed class SqlServerConnectionFactory
    {
        private readonly SyncOptions _options;

        public SqlServerConnectionFactory(SyncOptions options)
        {
            _options = options;
        }

        public SqlConnection Create()
        {
            return new SqlConnection(_options.SqlExpressConnectionString);
        }

        /// <summary>Opens via master so the target database can be created when it does not exist yet.</summary>
        public SqlConnection Open()
        {
            var targetDb = GetTargetDatabaseName();
            var masterCs = BuildMasterConnectionString();
            var conn = new SqlConnection(masterCs);
            conn.Open();
            EnsureDatabaseExists(conn, targetDb);
            conn.ChangeDatabase(targetDb);
            return conn;
        }

        public string Qualify(string tableName)
        {
            return string.Format("[{0}].[{1}]", _options.SqlExpressSchema, tableName);
        }

        public string MetadataTable(string tableName)
        {
            return string.Format("[sync_metadata].[{0}]", tableName);
        }

        private string GetTargetDatabaseName()
        {
            var builder = new SqlConnectionStringBuilder(_options.SqlExpressConnectionString);
            return !string.IsNullOrWhiteSpace(builder.InitialCatalog)
                ? builder.InitialCatalog
                : _options.SqlExpressDatabase;
        }

        private string BuildMasterConnectionString()
        {
            var builder = new SqlConnectionStringBuilder(_options.SqlExpressConnectionString);
            builder.InitialCatalog = "master";
            return builder.ConnectionString;
        }

        private static void EnsureDatabaseExists(SqlConnection connection, string databaseName)
        {
            var escaped = databaseName.Replace("]", "]]");
            var sql = string.Format(@"
IF DB_ID(@db) IS NULL
BEGIN
    EXEC('CREATE DATABASE [{0}]');
END", escaped);

            using (var cmd = new SqlCommand(sql, connection))
            {
                cmd.Parameters.AddWithValue("@db", databaseName);
                cmd.ExecuteNonQuery();
            }
        }
    }
}