using Microsoft.EntityFrameworkCore.Migrations;

#nullable disable

namespace Nuuru.Server.Migrations.PostgreSQL
{
    /// <inheritdoc />
    public partial class AddReactionScoreAndNotifyOnReaction : Migration
    {
        /// <inheritdoc />
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.AddColumn<bool>(
                name: "NotifyOnReaction",
                table: "UserSettings",
                type: "boolean",
                nullable: false,
                defaultValue: true);

            migrationBuilder.AddColumn<int>(
                name: "ReactionScore",
                table: "AspNetUsers",
                type: "integer",
                nullable: false,
                defaultValue: 0);

            // Backfill ReactionScore from existing reactions (join-based, not correlated subquery)
            migrationBuilder.Sql(@"
                UPDATE ""AspNetUsers"" u
                SET ""ReactionScore"" = sub.""total""
                FROM (
                    SELECT owner_id, SUM(score) AS ""total""
                    FROM (
                        SELECT
                            CASE r.""EntityType""
                                WHEN 1 THEN bp.""UploaderId""
                                WHEN 0 THEN bc.""UserId""
                                WHEN 2 THEN fp.""AuthorId""
                            END AS owner_id,
                            r.""UserId"" AS reactor_id,
                            CASE r.""EmoteName""
                                WHEN 'thumbsup' THEN 1 WHEN 'gem' THEN 1 WHEN 'wholesome' THEN 1
                                WHEN 'fact' THEN 1 WHEN 'tsmt' THEN 1 WHEN 'keyed' THEN 1
                                WHEN 'hot' THEN 1 WHEN 'true' THEN 1 WHEN 'feelsgoodman' THEN 1
                                WHEN 'kek' THEN 1 WHEN 'dreamy' THEN 1 WHEN 'giga' THEN 1
                                WHEN 'hunk' THEN 1 WHEN 'jamming' THEN 1 WHEN 'calm' THEN 1
                                WHEN 'cat' THEN 1 WHEN 'dog' THEN 1
                                WHEN 'thumbsdown' THEN -1 WHEN 'coal' THEN -1 WHEN 'brainlet' THEN -1
                                WHEN 'disgusted' THEN -1 WHEN 'itsover' THEN -1 WHEN 'sick' THEN -1
                                WHEN 'false' THEN -1 WHEN 'ack' THEN -1 WHEN 'rage' THEN -1
                                WHEN 'fuming' THEN -1 WHEN 'skull' THEN -1
                                ELSE 0
                            END AS score
                        FROM ""Reactions"" r
                        LEFT JOIN ""BooruPosts"" bp ON r.""EntityType"" = 1 AND r.""EntityId"" = bp.""Id""
                        LEFT JOIN ""BooruComments"" bc ON r.""EntityType"" = 0 AND r.""EntityId"" = bc.""Id""
                        LEFT JOIN ""ForumPosts"" fp ON r.""EntityType"" = 2 AND r.""EntityId"" = fp.""Id""
                    ) scored
                    WHERE owner_id IS NOT NULL AND reactor_id != owner_id
                    GROUP BY owner_id
                ) sub
                WHERE u.""Id"" = sub.owner_id;
            ");

            // Also backfill from existing post votes (join-based, not correlated subquery)
            migrationBuilder.Sql(@"
                UPDATE ""AspNetUsers"" u
                SET ""ReactionScore"" = ""ReactionScore"" + sub.""total""
                FROM (
                    SELECT bp.""UploaderId"" AS owner_id, SUM(v.""Value"") AS ""total""
                    FROM ""BooruPostVotes"" v
                    INNER JOIN ""BooruPosts"" bp ON v.""PostId"" = bp.""Id""
                    WHERE v.""UserId"" != bp.""UploaderId""
                    GROUP BY bp.""UploaderId""
                ) sub
                WHERE u.""Id"" = sub.owner_id;
            ");
        }

        /// <inheritdoc />
        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropColumn(
                name: "NotifyOnReaction",
                table: "UserSettings");

            migrationBuilder.DropColumn(
                name: "ReactionScore",
                table: "AspNetUsers");
        }
    }
}