using Microsoft.EntityFrameworkCore.Migrations;

#nullable disable

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

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

            // Backfill ReactionScore from existing reactions (subquery-based for SQLite compatibility)
            migrationBuilder.Sql(@"
                UPDATE AspNetUsers
                SET ReactionScore = COALESCE((
                    SELECT SUM(scored.score)
                    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 scored.owner_id = AspNetUsers.Id
                      AND scored.reactor_id != AspNetUsers.Id
                ), 0);
            ");

            // Also backfill from existing post votes
            migrationBuilder.Sql(@"
                UPDATE AspNetUsers
                SET ReactionScore = ReactionScore + COALESCE((
                    SELECT SUM(v.Value)
                    FROM BooruPostVotes v
                    INNER JOIN BooruPosts bp ON v.PostId = bp.Id
                    WHERE bp.UploaderId = AspNetUsers.Id
                      AND v.UserId != AspNetUsers.Id
                ), 0);
            ");
        }

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

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