using System;
using System.Collections.Generic;
using System.IO;
using Newtonsoft.Json;

namespace SyncEngine.Configuration
{
    public sealed class SyncOptions
    {
        public string AccessDbPath { get; set; }
        public string AccessDbPassword { get; set; }
        /// <summary>Empty or "current" = Application.CurrentUser for GRANT; Shell Runtime open uses Admin when empty.</summary>
        public string AccessDbUser { get; set; }
        /// <summary>Workgroup (/user) account password for Access Runtime Shell. Not the database encrypt password.</summary>
        public string AccessWorkgroupPassword { get; set; }
        public string SqlExpressConnectionString { get; set; }
        public string SqlExpressDatabase { get; set; }
        public string SqlExpressSchema { get; set; }
        public string MySqlHost { get; set; }
        public int MySqlPort { get; set; }
        public string MySqlUser { get; set; }
        public string MySqlPassword { get; set; }
        public string MySqlDatabase { get; set; }
        public string ShopSyncApiUrl { get; set; }
        public string ShopSyncApiKey { get; set; }
        public bool SyncAccessToSqlExpress { get; set; }
        public bool SyncSqlExpressToMySql { get; set; }
        public IReadOnlyList<string> AccessSqlExcludeTables { get; set; }
        public int BatchSize { get; set; }
        public int MaxRetries { get; set; }
        public int CommandTimeoutSeconds { get; set; }
        public bool SoftDeleteEnabled { get; set; }
        public bool DryRun { get; set; }
        public string PlatformTarget { get; set; }
        public string ConfigDirectory { get; set; }
        public ProductSyncMap ProductSyncMap { get; set; }

        public static SyncOptions LoadFromEnvironment()
        {
            var excludeRaw = EnvSettings.Get("ACCESS_SQL_EXCLUDE_TABLES", "");
            var configDir = Path.Combine(EnvSettings.ExeDirectory(), "config");
            var mapPath = Path.Combine(configDir, "product-sync-map.json");
            ProductSyncMap map = null;
            if (File.Exists(mapPath))
            {
                map = JsonConvert.DeserializeObject<ProductSyncMap>(File.ReadAllText(mapPath));
            }

            if (map == null)
            {
                map = new ProductSyncMap();
            }

            var sqlCs = EnvSettings.Get("SQL_EXPRESS_CONNECTION_STRING", "");
            var sqlDb = EnvSettings.Get("SQL_EXPRESS_DATABASE", "AnniesAngels");

            return new SyncOptions
            {
                AccessDbPath = EnvSettings.ResolvePath(EnvSettings.Get("ACCESS_DB_PATH", "")),
                AccessDbPassword = EnvSettings.Get("ACCESS_DB_PASSWORD", ""),
                AccessDbUser = EnvSettings.Get("ACCESS_DB_USER", ""),
                AccessWorkgroupPassword = EnvSettings.Get("ACCESS_WORKGROUP_PASSWORD", ""),
                SqlExpressConnectionString = BuildSqlConnectionString(sqlCs, sqlDb),
                SqlExpressDatabase = sqlDb,
                SqlExpressSchema = EnvSettings.Get("SQL_EXPRESS_SCHEMA", "dbo"),
                MySqlHost = EnvSettings.Get("MYSQL_HOST", "127.0.0.1"),
                MySqlPort = EnvSettings.GetInt("MYSQL_PORT", 3306),
                MySqlUser = EnvSettings.Get("MYSQL_USER", ""),
                MySqlPassword = EnvSettings.Get("MYSQL_PASSWORD", ""),
                MySqlDatabase = EnvSettings.Get("MYSQL_DATABASE", "annies_angels_shop"),
                ShopSyncApiUrl = EnvSettings.Get("SHOP_SYNC_API_URL", ""),
                ShopSyncApiKey = EnvSettings.Get("SHOP_SYNC_API_KEY", ""),
                SyncAccessToSqlExpress = EnvSettings.GetBool("SYNC_ACCESS_TO_SQL_EXPRESS", true),
                SyncSqlExpressToMySql = EnvSettings.GetBool("SYNC_SQL_EXPRESS_TO_MYSQL", true),
                AccessSqlExcludeTables = TableExcludeFilter.Parse(excludeRaw),
                BatchSize = EnvSettings.GetInt("SYNC_BATCH_SIZE", 5000),
                MaxRetries = EnvSettings.GetInt("SYNC_MAX_RETRIES", 3),
                CommandTimeoutSeconds = EnvSettings.GetInt("SYNC_COMMAND_TIMEOUT_SECONDS", 120),
                SoftDeleteEnabled = EnvSettings.GetBool("SYNC_SOFT_DELETE_ENABLED", true),
                DryRun = EnvSettings.GetBool("SYNC_DRY_RUN", false),
                PlatformTarget = EnvSettings.Get("SYNC_PLATFORM_TARGET", "x64"),
                ConfigDirectory = configDir,
                ProductSyncMap = map
            };
        }

        private static string BuildSqlConnectionString(string baseCs, string database)
        {
            if (string.IsNullOrWhiteSpace(baseCs))
            {
                return string.Format("Server=.\\SQLEXPRESS;Database={0};Trusted_Connection=True;TrustServerCertificate=True", database);
            }

            if (baseCs.IndexOf("Database=", StringComparison.OrdinalIgnoreCase) >= 0 ||
                baseCs.IndexOf("Initial Catalog=", StringComparison.OrdinalIgnoreCase) >= 0)
            {
                return baseCs;
            }

            var sep = baseCs.EndsWith(";", StringComparison.Ordinal) ? "" : ";";
            return baseCs + sep + "Database=" + database;
        }

        public string MySqlConnectionString
        {
            get
            {
                // TlsVersion=Tls12: on SBS 2011 / Server 2008 R2, Schannel OS defaults omit TLS 1.2
                // (DisabledByDefault). Explicit Tls12 keeps SSL working without a reboot.
                // Connection Timeout bounds TCP/TLS handshake so a hang fails within 30s.
                // UseAffectedRows=true: IODKU reports 1=insert, 2=update, 0=unchanged (classic MySQL counts).
                return string.Format(
                    "Server={0};Port={1};User ID={2};Password={3};Database={4};SslMode=Preferred;TlsVersion=Tls12;Connection Timeout=30;Allow User Variables=True;Use Affected Rows=True",
                    MySqlHost, MySqlPort, MySqlUser, MySqlPassword, MySqlDatabase);
            }
        }

        public bool HasShopSyncApiFallback =>
            !string.IsNullOrWhiteSpace(ShopSyncApiUrl) && !string.IsNullOrWhiteSpace(ShopSyncApiKey);
    }

    public sealed class ProductSyncMap
    {
        [JsonProperty("barcodeColumn")]
        public string BarcodeColumn { get; set; } = "strCode";

        [JsonProperty("imageUrl1Column")]
        public string ImageUrl1Column { get; set; } = "strImagePath";

        [JsonProperty("productTable")]
        public string ProductTable { get; set; } = "tblProduct";

        [JsonProperty("productIdColumn")]
        public string ProductIdColumn { get; set; } = "ProductID";
    }
}