using Microsoft.EntityFrameworkCore.Migrations;
using System;

#nullable disable

namespace gehGassi.Persistence.Migrations
{
    /// <inheritdoc />
    public partial class AppUserReportMessageAdded : Migration
    {
        /// <inheritdoc />
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.AddColumn<string>(
                name: "Message",
                table: "AppUserReports",
                type: "nvarchar(500)",
                maxLength: 500,
                nullable: true);

            var viewName = "vw_AppUserReportsWithNames";
            var sql = @"SELECT AUR.*, 
						CONCAT(AU1.FirstName, ' ', AU1.LastName) AS ReportingAppUserName, AU1.Type AS ReportingAppUserType,AU1.Contact_Email AS ReportingAppUserEmail,
						CONCAT(AU2.FirstName, ' ', AU2.LastName) AS ReportedAppUserName, AU2.Type AS ReportedAppUserType, AU2.Contact_Email AS ReportedAppUserEmail
						FROM AppUserReports AUR
						LEFT OUTER JOIN AppUsers AU1 ON AU1.Id = AUR.ReportingAppUserId
						LEFT OUTER JOIN AppUsers AU2 ON AU2.Id = AUR.ReportedAppUserId";

            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)
        {
            migrationBuilder.DropColumn(
                name: "Message",
                table: "AppUserReports");

            var viewName = "vw_AppUserReportsWithNames";
            var sql = @"SELECT AUR.*, 
						CONCAT(AU1.FirstName, ' ', AU1.LastName) AS ReportingAppUserName, AU1.Type AS ReportingAppUserType,AU1.Contact_Email AS ReportingAppUserEmail,
						CONCAT(AU2.FirstName, ' ', AU2.LastName) AS ReportedAppUserName, AU2.Type AS ReportedAppUserType, AU2.Contact_Email AS ReportedAppUserEmail
						FROM AppUserReports AUR
						LEFT OUTER JOIN AppUsers AU1 ON AU1.Id = AUR.ReportingAppUserId
						LEFT OUTER JOIN AppUsers AU2 ON AU2.Id = AUR.ReportedAppUserId";

            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");
        }
    }
}