using Microsoft.EntityFrameworkCore.Migrations;
using System;

#nullable disable

namespace gehGassi.Persistence.Migrations
{
    /// <inheritdoc />
    public partial class vw_ConversationsWithNamesAdded : Migration
    {
        /// <inheritdoc />
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            var viewName = "vw_ConversationsWithNames";
            var sql = @"SELECT C.*, 
                        CONCAT(AU1.FirstName, ' ' , AU1.LastName) AS SenderName,
                        AU1.FirstName AS SenderFirstName, AU1.LastName AS SenderLastName,
                        AU1.Locked AS SenderLocked,
                        CAST((SELECT CASE WHEN AUB1.Id IS NOT NULL THEN 1 ELSE 0 END) AS BIT)  AS SenderBlocked,
                        AU1.Type AS SenderType, AU1.Photo AS SenderPhoto,
                        CONCAT(AU2.FirstName, ' ' , AU2.LastName) AS ReceiverName,
                        AU2.FirstName AS ReceiverFirstName, AU2.LastName AS ReceiverLastName,
                        AU2.Locked AS ReceiverLocked,
                        CAST((SELECT CASE WHEN AUB2.Id IS NOT NULL THEN 1 ELSE 0 END) AS BIT)  AS ReceiverBlocked,
                        AU2.Type AS ReceiverType, AU2.Photo AS ReceiverPhoto
                        FROM Conversations C
                        JOIN AppUsers AU1 ON AU1.Id = C.SenderId 
                        JOIN AppUsers AU2 ON AU2.Id = C.ReceiverId 
                        LEFT OUTER JOIN AppUserBlocks AUB1 ON AUB1.BlockedAppUserId = C.SenderId AND AUB1.BlockingAppUserId = C.ReceiverId
                        LEFT OUTER JOIN AppUserBlocks AUB2 ON AUB2.BlockedAppUserId = C.ReceiverId AND AUB2.BlockingAppUserId = C.SenderId";

            migrationBuilder.Sql($"IF(exists (SELECT 1  FROM sys.views WHERE Name = '{viewName}')) BEGIN DROP VIEW [dbo].[{viewName}] END {Environment.NewLine} GO");
            migrationBuilder.Sql($"CREATE VIEW [dbo].[{viewName}] AS {sql} {Environment.NewLine} GO");
        }

        /// <inheritdoc />
        protected override void Down(MigrationBuilder migrationBuilder)
        {
            var viewName = "vw_ConversationsWithNames";
            migrationBuilder.Sql($"IF(exists (SELECT 1  FROM sys.views WHERE Name = '{viewName}')) BEGIN DROP VIEW [dbo].[{viewName}] END {Environment.NewLine} GO");
        }
    }
}