USE [AzureDbInstance]

ALTER USER [nLlrTgVqEULogin_AzureDbInstanceUser] WITH DEFAULT_SCHEMA = dbo;

GRANT INSERT ON SCHEMA :: dbo TO [nLlrTgVqEULogin_AzureDbInstanceUser]
GRANT SELECT ON SCHEMA :: dbo TO [nLlrTgVqEULogin_AzureDbInstanceUser]
GRANT ALTER ON SCHEMA :: dbo TO [nLlrTgVqEULogin_AzureDbInstanceUser]
GRANT DELETE ON SCHEMA :: dbo TO [nLlrTgVqEULogin_AzureDbInstanceUser]
GRANT UPDATE ON SCHEMA :: dbo TO [nLlrTgVqEULogin_AzureDbInstanceUser]
GRANT CONTROL ON SCHEMA :: dbo TO [nLlrTgVqEULogin_AzureDbInstanceUser]
GRANT EXECUTE ON SCHEMA :: dbo TO [nLlrTgVqEULogin_AzureDbInstanceUser]

public class MobileServiceContext : DbContext
{
	// keep this as-is - it uses the connection string set on Azure
	private const string ConnectionStringName = "Name=MS_TableConnectionString";
	public string Schema { get; private set; }
	
	public MobileServiceContext()
		: base(ConnectionStringName)
	{
		// note that we're setting this, but we're not explicitly telling it to use the mobile service
		// name, which is what it tries to do by default. if we leave this as-is, it will actually create the
		// tables in the default dbo schema, which is great if you want to launch multiple instances of 
		// this service (think preprod/prod)
		this.Schema = ServiceSettingsDictionary.GetSchemaName();
	}

	// define your read-only tables here. we're not going to use any special data annotations in your classes,
	// but instead will use some Type nomenclature and Fluent API to define relationships. these are read-only
	// by CQRS pattern definition
	public DbSet<VehicleManufacturer> VehicleManufacturers { get; set; } // notice the pluralization
	public DbSet<VehicleModel> VehicleModels { get; set; }
	
	// let's say we have an application where we want to be able to update the number of accolades that a vehicle
	// model has received. under the CQRS model, we would create a set of segregated tables here that are meant for writing
	// data from the application. we will only write to these tables via a command interface
	public DbSet<VehicleModel> VehicleModelUpdates { get; set; }

	protected override void OnModelCreating(DbModelBuilder modelBuilder)
	{
		// we remove this convention since we're doing it ourselves
		modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
		
		modelBuilder.Conventions.Add(
			new AttributeToColumnAnnotationConvention<TableColumnAttribute, string>(
				"ServiceTableColumn", (property, attributes) => attributes.Single().ColumnType.ToString()));

		// Fluent API
		this.SetupVehicleManufacturer(modelBuilder);
		this.SetupVehicleModel(modelBuilder);
		this.SetupVehicleModelUpdate(modelBuilder);
		base.OnModelCreating(modelBuilder);
	}

	// Fluent API Mappings
	private void SetupVehicleManufacturer(DbModelBuilder modelBuilder)
	{
		// properties
		modelBuilder.Entity<VehicleManufacturer>()
			.ToTable("VehicleManufacturer");

		modelBuilder.Entity<VehicleManufacturer>()
			.Property(x => x.Name)
			.HasColumnType("nvarchar")
			.HasMaxLength(25);

		modelBuilder.Entity<VehicleManufacturer>()
			.Property(x => x.Country)
			.HasColumnType("nvarchar")
			.HasMaxLength(100);

		// here we need to create the relationship between VehicleManufacturer and VehicleModel
		// entities, where we explicitly tell EF how to map them
		modelBuilder.Entity<VehicleManufacturer>()
			.HasMany(x => x.VehicleModels)
			.WithRequired(x => x.VehicleManufacturer)
			.HasForeignKey(x => x.VehicleManufacturerId)
			.WillCascadeOnDelete(false);
	}

	private void SetupVehicleModel(DbModelBuilder modelBuilder)
	{
		// properties
		modelBuilder.Entity<VehicleModel>()
			.ToTable("VehicleModel");

		modelBuilder.Entity<VehicleModel>()
			.Property(x => x.Name)
			.HasColumnType("nvarchar")
			.HasMaxLength(50);

		modelBuilder.Entity<VehicleModel>()
			.Property(x => x.Year)
			.HasColumnType("int");
			
		modelBuilder.Entity<VehicleModel>()
			.Property(x => x.AccoladeCount)
			.HasColumnType("int");	

		// we only need to define the relationship on one side, so no
		// need for an additional mapping here
	}
	
	private void SetupVehicleModelUpdates(DbModelBuilder modelBuilder)
	{
		// properties
		modelBuilder.Entity<VehicleModelUpdate>()
			.ToTable("VehicleModelUpdate");
	}
}

public class VehicleManufacturer 
	: EntityData // this is a required mobile service class that adds additional properties for use with sync
{
	public string Name { get; set; }
	
	public string Country { get; set; }
	
	public virtual ICollection<VehicleModel> VehicleModels { get; set; }
}

public class VehicleModel : EntityData
{
	public string Name { get; set; }
	
	public int Year { get; set; }
	
	public int AccoladeCount { get; set; }
	
	public string VehicleManufacturerId { get; set; }
	
	public virtual VehicleManufacturer VehicleManufacturer { get; set; }
}

public class VehicleModelUpdate : EntityData
{
	public string VehicleModelId { get; set; }
	
	public virtual VehicleModel VehicleModel { get; set; }
	
	public int AccoladeCount { get; set; }
}

public class BaseTableController<T> : TableController<T> where T : EntityData
{
	protected override void Initialize(HttpControllerContext controllerContext)
	{
		base.Initialize(controllerContext);
		MobileServiceContext context = new MobileServiceContext();
		this.DomainManager = new EntityDomainManager<T>(context, base.Request, base.Services);
	}

	// GET tables/T
	public virtual IQueryable<T> GetAllT()
	{
		return base.Query();
	}

	// GET tables/T/48D68C86-6EA6-4C25-AA33-223FC9A27959
	public virtual SingleResult<T> GetT(string id)
	{
		return base.Lookup(id);
	}

	// PATCH tables/T/48D68C86-6EA6-4C25-AA33-223FC9A27959
	public virtual Task<T> PatchT(string id, Delta<T> patch)
	{
		return base.UpdateAsync(id, patch);
	}

	// POST tables/T
	public virtual async Task<IHttpActionResult> PostT(T item)
	{
		T current = await base.InsertAsync(item);
		return base.CreatedAtRoute("Tables", new {id = current.Id}, current);
	}

	// DELETE tables/T/48D68C86-6EA6-4C25-AA33-223FC9A27959
	public virtual Task DeleteT(string id)
	{
		return base.DeleteAsync(id);
	}
}

public class EntityDomainManager<TDto, TModel> : MappedEntityDomainManager<TDto, TModel> where TDto : Entity where TModel : Entity
{
	private readonly MobileServiceContext _context;

	public EntityDomainManager(MobileServiceContext context, HttpRequestMessage request, ApiServices services) : base(context, request, services)
	{
		this._context = context;
	}

	public EntityDomainManager(MobileServiceContext context, HttpRequestMessage request, ApiServices services, bool enableSoftDelete)
		: base(context, request, services, enableSoftDelete)
	{
		this._context = context;
	}

	public override async Task<bool> DeleteAsync(string id)
	{
		return await this.DeleteItemAsync(id);
	}

	public override SingleResult<TDto> Lookup(string id)
	{
		return this.LookupEntity(s => s.Id == id);
	}

	public override async Task<TDto> UpdateAsync(string id, Delta<TDto> patch)
	{
		return await this.UpdateEntityAsync(patch, id);
	}

	protected override void SetOriginalVersion(TModel model, byte[] version)
	{
		this._context.Entry(model).OriginalValues["Version"] = version;
	}
}

public class VehicleManufacturerController : BaseTableController<VehicleManufacturer>
{
}

public static void Register()
{
	// Use this class to set WebAPI configuration options
	HttpConfiguration config = ServiceConfig.Initialize(new ConfigBuilder(options));

	// To display errors in the browser during development, uncomment the following
	// line. Comment it out again when you deploy your service for production use.
	config.IncludeErrorDetailPolicy = IncludeErrorDetailPolicy.Always; // TODO: remove this for prod

	// this sets up configuration of your database, including your namespace and automatic migration settings
	DbMigrator migrator = new DbMigrator(new Configuration());
	migrator.Update();
}

internal sealed class Configuration : DbMigrationsConfiguration<MobileServiceContext>
{
	public Configuration()
	{
		base.AutomaticMigrationsEnabled = true;
		base.AutomaticMigrationDataLossAllowed = true;
		base.ContextKey = "Rightpoint.Server.AzureMobileService.Models.MobileServiceContext"; // path to MobileServiceContext
		base.ContextType = typeof(MobileServiceContext);
	}
}

public class MobileServiceDatabaseContext : MobileServiceClient
{
	static readonly Uri Uri = new Uri("https://mobile-service-name.azure-mobile.net/", UriKind.Absolute);
	const string Passkey = "PASSKEYPASSKEYPASSKEYPASSKEY";
	
	public MobileServiceDatabaseContext() : base(Uri, Passkey)
	{
		base.SerializerSettings = new MobileServiceJsonSerializerSettings()
		{
			CamelCasePropertyNames = true
		};

		// setup SQLite storage
		MobileServiceSQLiteStore syncSqlLite = new MobileServiceSQLiteStore("sync.db");

		// here we define all tables that we want to use as read-only. these classes will be more or less exact
		// matches to what is currently defined in your Mobile Service project. the big difference is that they can't
		// inherit from Azure's EntityData base class, but you don't need most of those properties anyway
		syncSqlLite.DefineTable<VehicleManufacturer>();
		syncSqlLite.DefineTable<VehicleModel>();
		
		// read/write - same reason as in the Mobile Service, as this creates the write-based models that are meant
		// for the command channel of our CQRS implementation
		syncSqlLite.DefineTable<VehicleModelUpdate>();

		// Sync Handler
		SyncHandler syncHandler = new SyncHandler();
		base.SyncContext.InitializeAsync(syncSqlLite, syncHandler).Wait();
	}
}

public class Entity
{
	public string Id { get; set; }
}

public class SyncHandler : IMobileServiceSyncHandler
{
	// this is a simplified conflict resolution method, mostly due to the CQRS pattern and the sync
	// rules which we have chosen to follow. this method will always take the latest data if there is
	// a conflict, so "last in" wins. you could write it differently if you have more complicated logic
	public async Task<JObject> ExecuteTableOperationAsync(IMobileServiceTableOperation operation)
	{
		MobileServiceInvalidOperationException error = null;

		do
		{
			error = null;

			try
			{
				JObject result = await operation.ExecuteAsync();
				return result;
			}
			catch (MobileServiceConflictException ex)
			{
				error = ex;
			}
			catch (MobileServicePreconditionFailedException ex)
			{
				error = ex;
			}
			catch (Exception e)
			{
				Debug.WriteLine(e.ToString());
				throw e;
			}
		} while (error == null);

		return null;
	}

	public Task OnPushCompleteAsync(MobileServicePushCompletionResult result)
	{
		return Task.FromResult(0);
	}
}

public interface IReadOnlyMobileServiceDatabaseContext
{
	Task<IEnumerable<T>> GetSyncTableReadOnly<T>();
}

public interface ITypedCommand<T>
{
}

public interface ITypedCommandHandler<in TCommand, T> where TCommand : ITypedCommand<T>
{
	Task<object> Handle(TCommand command);
}

public class SyncDownTableCommand<T> : TypedCommand<T>
{
	public string QueryId { get; set; }

	public Expression<Func<T, bool>> Query { get; set; }

	public SyncDownTableCommand(string queryId, Expression<Func<T, bool>> query)
	{
		if (query == null) throw new ArgumentNullException("query");

		this.QueryId = queryId;
		this.Query = query;
	}
}

public class TypedCommand<T> : ITypedCommand<T>
{
	public string Id { get; private set; }

	public TypedCommand()
	{
		this.Id = Guid.NewGuid().ToString();
	}
}

public static class Commands
{
	public async static Task<object> Execute<TCommand>(this TCommand command) where TCommand : ICommand
	{
		try
		{
			ICommandHandler<TCommand> handler = ServiceLocator.Current.GetInstance<ICommandHandler<TCommand>>();

			return await handler.Handle(command);
		}
		catch (InvalidOperationException)
		{
			// When service locator is not set, ignore it.

			return null;
		}
	}

	public async static Task<object> Execute<TCommand, T>(this TCommand command) where TCommand : ITypedCommand<T>
	{
		try
		{
			ITypedCommandHandler<TCommand, T> handler =
				ServiceLocator.Current.GetInstance<ITypedCommandHandler<TCommand, T>>();

			return await handler.Handle(command);
		}
		catch (InvalidOperationException ex)
		{
			// When service locator is not set, ignore it.

			return null;
		}
	}
}

public class TypedCommandHandler<T> :
	ITypedCommandHandler<SyncDownTableCommand<T>, T>
{
	private readonly IMobileServiceClient _mobileServiceClient;

	public TypedCommandHandler(IMobileServiceClient mobileServiceClient)
	{
		if (mobileServiceClient == null) throw new ArgumentNullException("mobileServiceClient");

		this._mobileServiceClient = mobileServiceClient;
	}

	// this is the implementation of the command handler for a single command. the service locator finds it
	// and will execute it, returning the result to the call site. this and the CommandHandler classes are the
	// only two allowed to have access to your actual database context, so ALL write operations (anything not returning
	// IEnumerable<T> collections) occur in these classes.
	public async Task<object> Handle(SyncDownTableCommand<T> command)
	{
		IMobileServiceSyncTable<T> table = this._mobileServiceClient.GetSyncTable<T>();

		if (!NetworkInterface.GetIsNetworkAvailable()) return false;

		try
		{
			await table.PullAsync(command.QueryId, table.Where(command.Query));
		}
		catch (MobileServiceInvalidOperationException ex)
		{
			string uri = ex.Request.RequestUri.ToString();
		}

		return true;
	}
}

await new SyncDownTableCommand<VehicleManufacturer>("vehicleManufacturers", 
       vm => vm.Country == "USA")
       .Execute<SyncDownTableCommand<VehicleManufacturer>, VehicleManufacturer>();

private static void RegisterCommands(IUnityContainer container)
{
	container.RegisterType(typeof (ICommandHandler<>), typeof (CommandHandler),
		new HierarchicalLifetimeManager());
		
	container.RegisterType(typeof (ITypedCommandHandler<,>), new HierarchicalLifetimeManager(),
		new InjectionFactory(
			(cont, type, obj) =>
				cont.Resolve(typeof (TypedCommandHandler<>)
                            .MakeGenericType(type.GenericTypeArguments[1]), obj)));
}

private static void RegisterMisc(IUnityContainer container)
{
	UnityServiceLocator locator = new UnityServiceLocator(container);
	ServiceLocator.SetLocatorProvider(() => locator);
}