using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;


namespace SampleDb
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var context = new SampleContext())
            {
                //A query to get all streams from an account and the subscribers during that stream
                var result =
                    context.Accounts.Where(account => account.Id == 6)
.Join(context.Streams, outer => outer.Id, inner => inner.AccountId,
    (account, stream) => new {Account = account, Stream = stream})
.GroupJoin(context.Subscriptions, outer => outer.Account.Id, inner => inner.TargetAccountId,
    (outer, subscriptions) => new
        {Account = outer.Account, Stream = outer.Stream, Subscriptions = subscriptions})
.SelectMany(x =>
        x.Subscriptions.Where(subscription =>
                subscription.Date > x.Stream.StreamStart &&
                subscription.Date < x.Stream.StreamEnd)
            .DefaultIfEmpty(),
    (x, subscription) => new {x.Account, x.Stream, Subscription = subscription}).ToList();
            }
        }
    }

    public class SampleContext : DbContext
    {
        public DbSet<Account> Accounts { get; set; }
        public DbSet<Stream> Streams { get; set; }
        public DbSet<Subscription> Subscriptions { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Subscription>()
                .HasKey(bc => new { bc.SourceAccountId, bc.TargetAccountId });

            modelBuilder.Entity<Subscription>()
                .HasOne(bc => bc.SourceAccount)
                .WithMany(b => b.Subscribers)
                .HasForeignKey(s => s.SourceAccountId);


            modelBuilder.Entity<Subscription>()
                .HasOne(bc => bc.TargetAccount)
                .WithMany(c => c.Subscriptions)
                .HasForeignKey(s => s.TargetAccountId);

            modelBuilder.Entity<Account>().HasData(
                new { Id = 1, Name = "SomeViewer1" },
                new { Id = 2, Name = "SomeViewer2" },
                new { Id = 3, Name = "SomeViewer3" },
                new { Id = 4, Name = "SomeViewer4" },
                new { Id = 5, Name = "SomeViewer5" },
                new { Id = 6, Name = "SomeStreamer" }
            );

            modelBuilder.Entity<Stream>().HasData(
                new { Id = 1, Title = "my first stream", AccountId = 6,
                    StreamStart = new DateTime(2019,8, 22, 19, 0,0),
                    StreamEnd = new DateTime(2019, 8, 22, 20, 0, 0) },

                new { Id = 2, Title = "my second stream", AccountId = 6,
                    StreamStart = new DateTime(2019, 8, 23, 19, 0, 0),
                    StreamEnd = new DateTime(2019, 8, 23, 20, 0, 0)
                });

            modelBuilder.Entity<Subscription>().HasData(
                //Not Subscribed during a stream
                new {SourceAccountId = 1, TargetAccountId = 6, Date = new DateTime(2019, 8, 21, 19, 10, 0) },

                //Subscribed during first stream
                new {SourceAccountId = 2, TargetAccountId = 6, Date = new DateTime(2019, 8, 22, 19, 15, 0) },
                new {SourceAccountId = 3, TargetAccountId = 6, Date = new DateTime(2019, 8, 22, 19, 15, 0) },

                //Subscribed during second stream
                new { SourceAccountId = 4, TargetAccountId = 6, Date = new DateTime(2019, 8, 23, 19, 30, 0) },
                new {SourceAccountId = 5, TargetAccountId = 6, Date = new DateTime(2019, 8, 23, 19, 13, 0) }
            );
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
            => optionsBuilder.UseNpgsql("Host=localhost;Database=my_db;Port=2345;Username=postgres;Password=postgres")
                .UseLoggerFactory(new LoggerFactory().AddConsole())
                .EnableSensitiveDataLogging();
    }

    public class Stream
    {
        [Key]
        public int Id { get; set; }
        public string Title { get; set; }
        [ForeignKey(nameof(Account))]
        public int AccountId { get; set; }
        public DateTime StreamStart { get; set; }
        public DateTime StreamEnd { get; set; }
        public Account Account { get; set; }
    }

    public class Subscription
    {
        public int SourceAccountId { get; set; }
        public int TargetAccountId { get; set; }

        public Account SourceAccount { get; set; }
        public Account TargetAccount { get; set; }
        public DateTime Date { get; set; }
    }

    public class Account
    {
        [Key]
        public int Id { get; set; }
        public string Name { get; set; }
        public ICollection<Subscription> Subscribers { get; set; }
        public ICollection<Subscription> Subscriptions { get; set; }
    }
}