using System;
using System.Collections.Generic;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using SyncEngine.Access;
using SyncEngine.SqlServer;

namespace SyncEngine.Tests
{
    [TestClass]
    public class SqlViewTranslatorTests
    {
        [TestMethod]
        public void Translate_DoesNotDoubleQuoteDatesAlreadyQuoted()
        {
            var sql = Translate("SELECT * FROM t WHERE d >= '2025/01/01'");
            Assert.AreEqual("SELECT * FROM t WHERE d >= '2025-01-01'", sql);
        }

        [TestMethod]
        public void Translate_ConvertsHashAndBareAccessDates()
        {
            var sql = Translate("SELECT * FROM t WHERE d >= #2025/01/01# AND d < 2025/02/01");
            StringAssert.Contains(sql, "'2025-01-01'");
            StringAssert.Contains(sql, "'2025-02-01'");
        }

        [TestMethod]
        public void Translate_ConvertsNestedIIf()
        {
            var sql = Translate("SELECT IIf([A], IIf([B], 1, 0), 2) FROM t");
            StringAssert.Contains(sql, "CASE WHEN");
            StringAssert.Contains(sql, "ELSE 2 END");
        }

        [TestMethod]
        public void Translate_ConvertsIIfWithNumericCondition()
        {
            var sql = Translate("SELECT IIf([Qty], [Qty], 0) FROM t");
            StringAssert.Contains(sql, "[Qty] IS NOT NULL");
            StringAssert.Contains(sql, "THEN [Qty] ELSE 0 END");
        }

        [TestMethod]
        public void Translate_ConvertsAccessAndOperator()
        {
            var sql = Translate("SELECT IIf([A] And [B], 1, 0) FROM t");
            StringAssert.Contains(sql, "[A] AND [B]");
        }

        [TestMethod]
        public void Translate_FixesIsNullInOrClause()
        {
            var sql = Translate("SELECT * FROM t WHERE (Len([d])=0) OR (Nz([d])) AND x=1");
            StringAssert.Contains(sql, "ISNULL([d], '') <> ''");
        }

        [TestMethod]
        public void Translate_FixesDoubleQuotedDateComparison()
        {
            var sql = Translate("SELECT * FROM t WHERE ([d])<\"2025/01/01\"");
            StringAssert.Contains(sql, "<'2025-01-01'");
        }

        [TestMethod]
        public void Translate_StripsTrailingOrderByForViews()
        {
            var sql = Translate("SELECT * FROM t WHERE x=1 ORDER BY t.id;");
            Assert.IsFalse(sql.IndexOf("ORDER BY", StringComparison.OrdinalIgnoreCase) >= 0);
        }

        [TestMethod]
        public void Translate_ConvertsAccessFirstAggregate()
        {
            var sql = Translate("SELECT First([DeliveryNoteID]), Count([DeliveryNoteID]) FROM t GROUP BY [DeliveryNoteID]");
            StringAssert.Contains(sql, "MIN([DeliveryNoteID])");
        }

        [TestMethod]
        public void Translate_ExpandsTableStarUsingCatalog()
        {
            var catalog = Catalog("adn", "DeliveryNoteID", "strDateOut");
            var sql = Translate("SELECT adn.* FROM adn WHERE adn.strDateOut > '2010-05-31'", catalog);
            StringAssert.Contains(sql, "adn.DeliveryNoteID");
            StringAssert.Contains(sql, "adn.strDateOut");
            Assert.IsFalse(sql.Contains("adn.*"));
        }

        [TestMethod]
        public void Translate_ExpandsTableStarExcludingAlreadyListedColumns()
        {
            var catalog = Catalog("t", "a", "b", "c");
            var sql = Translate("SELECT t.a, t.b, t.* FROM t", catalog);
            StringAssert.Contains(sql, "t.a");
            StringAssert.Contains(sql, "t.b");
            StringAssert.Contains(sql, "t.c");
            Assert.IsFalse(sql.Contains("t.*"));
        }

        [TestMethod]
        public void Translate_KeepsUnknownTableStarWhenNotInCatalog()
        {
            var catalog = Catalog("t", "a");
            var sql = Translate("SELECT missing.* FROM missing", catalog);
            StringAssert.Contains(sql, "missing.*");
        }

        [TestMethod]
        public void Translate_ExpandsTableStarWithCommaAfterExplicitColumns()
        {
            var catalog = Catalog("tblDeliveryNote", "DeliveryNoteID", "strDateIn", "strDateOut", "blnCanceled");
            var sql = Translate(
                "SELECT tblDeliveryNote.DeliveryNoteID, tblDeliveryNote.strDateIn, tblDeliveryNote.* FROM tblDeliveryNote",
                catalog);
            StringAssert.Contains(sql, "tblDeliveryNote.strDateIn, tblDeliveryNote.strDateOut");
            Assert.IsFalse(sql.Contains("strDateIntblDeliveryNote"));
            Assert.IsFalse(sql.Contains("tblDeliveryNote.*"));
        }

        [TestMethod]
        public void Translate_ExpandsBracketedTableStar()
        {
            var catalog = Catalog("My Table", "Col A", "ColB");
            var sql = Translate("SELECT [My Table].* FROM [My Table]", catalog);
            StringAssert.Contains(sql, "[My Table].[Col A]");
            StringAssert.Contains(sql, "[My Table].ColB");
            Assert.IsFalse(sql.Contains("[My Table].*"));
        }

        private static ViewColumnCatalog Catalog(string table, params string[] columns)
        {
            var map = new Dictionary<string, IReadOnlyList<string>>
            {
                { table, columns }
            };
            return new ViewColumnCatalog(map);
        }

        private static string Translate(string accessSql, ViewColumnCatalog catalog = null)
        {
            return SqlViewTranslator.Translate(accessSql, catalog);
        }
    }
}