using System;
using System.Collections.Generic;
using System.Data.Linq;
using System.Data.SqlClient;
using System.Linq;

namespace Extensions
{
    /// <summary>Provides helper functions to deal with SQL 2008 Change Tracking and LINQ</summary>
    public static class ChangeTracking
    {
        public static long GetCurrentVersion(DataContext dc)
        {
            var ver = dc.ExecuteQuery<long?>("SELECT CHANGE_TRACKING_CURRENT_VERSION()").First();
            if (!ver.HasValue) throw new InvalidOperationException("Change tracking is not enabled on this database.");
            return ver.Value;
        }

        public static long GetValidVersion(DataContext dc, string table)
        {
            var ver = dc.ExecuteQuery<long?>("SELECT CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID('" + table + "'))").First();
            if (!ver.HasValue) throw new ArgumentException("Change tracking version not available for specified table.");
            return ver.Value;
        }

        public static long GetValidVersionForAll(DataContext dc) {
            var ver = dc.ExecuteQuery<long?>("SELECT MAX(min_valid_version) FROM sys.change_tracking_tables").First();
            if (!ver.HasValue) throw new ArgumentException("Change tracking version not found.");
            return ver.Value;
        }

        public static System.Transactions.TransactionScope GetSnapshotScope()
        {
            var txOptions = new System.Transactions.TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.Snapshot };
            return new System.Transactions.TransactionScope(System.Transactions.TransactionScopeOption.Required, txOptions);
        }

        #region Updated/Inserted

        public static IEnumerable<T> GetChangedItems<T>(DataContext dc, IQueryable<T> query, string tableName, string keyColumn, long lastVersion)
        {
            string join =
                " INNER JOIN " + getChangeTable(tableName, lastVersion) +
                " ON [t0]." + keyColumn + " = CT." + keyColumn + " AND " + sysOpNotDel;
            return getChangedItems<T>(dc, query, tableName, lastVersion, join);
        }

        public static IEnumerable<T> GetChangedItems<T>(DataContext dc, IQueryable<T> query, string tableName, string keyColumn1, string keyColumn2, long lastVersion)
        {
            string join =
                " INNER JOIN " + getChangeTable(tableName, lastVersion) +
                " ON [t0]." + keyColumn1 + " = CT." + keyColumn1 + " AND [t0]." + keyColumn2 + " = CT." + keyColumn2 + " AND " + sysOpNotDel;
            return getChangedItems<T>(dc, query, tableName, lastVersion, join);
        }

        static IEnumerable<T> getChangedItems<T>(DataContext dc, IQueryable query, string tableName, long lastVersion, string join)
        {
            var comm = dc.GetCommand(query);
            var sql = setJoin(comm.CommandText, join);
            return dc.ExecuteQuery<T>(sql, comm.Parameters.OfType<SqlParameter>().Select(p => p.Value).ToArray());
        }

        const string sysOpNotDel = " SYS_CHANGE_OPERATION != 'D' ";

        static string getChangeTable(string tableName, long lastVersion)
        {
            return " CHANGETABLE (CHANGES " + tableName + ", " + lastVersion + ") AS CT ";
        }

        static readonly char[] newChar = new[] { '\n' };

        static string setJoin(string command, string innerJoin)
        {
            var lines = command.Split(newChar);
            int fromIndex = -1;
            for (int i = 0; i < lines.Length; i++) {
                if (lines[i].StartsWith("FROM ")) {
                    if (fromIndex != -1) throw new ArgumentException("Multiple FROM lines found in SQL command.");
                    fromIndex = i;
                    lines[i] += innerJoin;
                }
            }
            if (fromIndex == -1) throw new ArgumentException("No FROM line found in SQL command.");
            return string.Join("\n", lines);
        }

        /* Example SQL generated from a simple command:
        SELECT [t0].[RatePlanId], [t0].[Prefix], [t0].[InitialIntervalExpr], [t0].[PeriodicIntervalExpr], [t0].[ConnectionChargeExpr], [t0].[RateExpr]
        FROM [dbo].[RateItems] AS [t0]
        WHERE [t0].[RatePlanId] = @p0
        ORDER BY [t0].[RatePlanId]
         * 
         * We may have to insert the where ourselves if one is not found (insert it on the line after FROM)
        SELECT [t0].[RatePlanId], [t0].[Prefix], [t0].[InitialIntervalExpr], [t0].[PeriodicIntervalExpr], [t0].[ConnectionChargeExpr], [t0].[RateExpr]
        FROM [dbo].[RateItems] AS [t0]
        ORDER BY [t0].[RatePlanId]
         * 
         * More advanced (LoadWith):
         * 
        SELECT [t0].[EndpointName], [t0].[EndpointSetName], [t0].[WeightOrPriority], [t0].[InboundCallLimit], [t0].[KeepAliveInterval], [t0].[KeepAliveTimeout], [t0].[
        escription], [t0].[Created], [t0].[ProtocolOptions], [t2].[test], [t2].[EndpointName] AS [EndpointName2], [t2].[IPs], [t2].[OriginalIPHeader], [t2].[UserName],
        [t2].[Password], [t2].[FromDialedNumberPrefix], [t2].[FromDialedNumberPrefixAutoRemove], [t2].[DialedNumberPrefix], [t2].[DialedNumberPrefixAutoRemove], [t2].[
        ustomHeaderName], [t2].[CustomHeaderValue], [t2].[Certificate], [t4].[test] AS [test2], [t4].[EndpointName] AS [EndpointName3], [t4].[Host], [t4].[OutboundProx
        ], [t4].[UserName] AS [UserName2], [t4].[Password] AS [Password2], [t4].[DialStringPrefix], [t4].[FromDialStringPrefix], [t4].[CustomHeaderName] AS [CustomHead
        rName2], [t4].[CustomHeaderValue] AS [CustomHeaderValue2], [t4].[Certificate] AS [Certificate2]
        FROM [dbo].[Endpoints] AS [t0]
        LEFT OUTER JOIN (
            SELECT 1 AS [test], [t1].[EndpointName], [t1].[IPs], [t1].[OriginalIPHeader], [t1].[UserName], [t1].[Password], [t1].[FromDialedNumberPrefix], [t1].[FromDi
        ledNumberPrefixAutoRemove], [t1].[DialedNumberPrefix], [t1].[DialedNumberPrefixAutoRemove], [t1].[CustomHeaderName], [t1].[CustomHeaderValue], [t1].[Certificat
        ]
            FROM [dbo].[EndpointAuthentication] AS [t1]
            ) AS [t2] ON [t2].[EndpointName] = [t0].[EndpointName]
        LEFT OUTER JOIN (
            SELECT 1 AS [test], [t3].[EndpointName], [t3].[Host], [t3].[OutboundProxy], [t3].[UserName], [t3].[Password], [t3].[DialStringPrefix], [t3].[FromDialString
        refix], [t3].[CustomHeaderName], [t3].[CustomHeaderValue], [t3].[Certificate]
            FROM [dbo].[EndpointConnection] AS [t3]
            ) AS [t4] ON [t4].[EndpointName] = [t0].[EndpointName]
        
        Notice that sub-clauses have spaces first.
        */

        #endregion

        #region Keys

        static string changeOpToSql(ChangeAction a)
        {
            switch (a)
            {
                case ChangeAction.Delete: return "'D'";
                case ChangeAction.Insert: return "'I'";
                case ChangeAction.Update: return "'U'";
                default: throw new ArgumentException("Invalid ChangeAction.");
            }
        }
            
        const string whereOp = "WHERE SYS_CHANGE_OPERATION = ";


        public static IEnumerable<TKey> GetChangedKeys<TKey>(DataContext dc, string tableName, string keyColumn, long lastVersion, ChangeAction changeAction) {
            return GetChangedKeys<TKey>(dc, tableName, keyColumn, lastVersion, changeAction, null);
        }

        public static IEnumerable<TKey> GetChangedKeys<TKey>(DataContext dc, string tableName, string keyColumn, long lastVersion, ChangeAction changeAction, string filterString)
        {
            if (!string.IsNullOrEmpty(filterString)) filterString = " AND " + filterString;
            string sql = "SELECT [" + keyColumn + "] FROM " + getChangeTable(tableName, lastVersion) + whereOp + changeOpToSql(changeAction) + " " + filterString;
            return dc.ExecuteQuery<TKey>(sql);
        }

        public static IEnumerable<Tuple<TKey1, TKey2>> GetChangedKeys<TKey1, TKey2>(DataContext dc, string tableName, string keyColumn1, string keyColumn2, long lastVersion, ChangeAction changeAction)
        {
            string sql = "SELECT [" + keyColumn1 + "] Key1, [" + keyColumn2 + "] Key2 FROM " + getChangeTable(tableName, lastVersion) + whereOp + changeOpToSql(changeAction);
            return
                dc.ExecuteQuery<TwoKeyHolder<TKey1, TKey2>>(sql)
                .Select(t => Tuple.Create(t.Key1, t.Key2));
        }

        public static IEnumerable<Tuple<TKey1, TKey2>> GetKeys<TKey1, TKey2>(DataContext dc, string sqlCommand, params object[] parameters)
        {
            return
                dc.ExecuteQuery<TwoKeyHolder<TKey1, TKey2>>(sqlCommand, parameters)
                .Select(t => Tuple.Create(t.Key1, t.Key2));
        }

        /// Interop class to get values out of two-column query. Apparently structs don't work.
        class TwoKeyHolder<Ta, Tb>
        {
            public Ta Key1 { get; set; }
            public Tb Key2 { get; set; }
        }

        #endregion
    }
}