from tortoise import BaseDBAsyncClient

RUN_IN_TRANSACTION = True


async def upgrade(db: BaseDBAsyncClient) -> str:
    return """
        DROP INDEX IF EXISTS "idx_player_rati_game_ty_3326d1";
        ALTER TABLE "player_rating" ADD "points" DOUBLE PRECISION NOT NULL DEFAULT 0;
        ALTER TABLE "player_rating" ADD "display_name" VARCHAR(200) NOT NULL DEFAULT '';
        ALTER TABLE "player_rating" ADD "wins" INT NOT NULL DEFAULT 0;
        -- Backfill the leaderboard aggregates from the match history:
        -- existing rows keep their rating and matches, while players from
        -- matches recorded before ratings existed are inserted at the
        -- initial rating (1500) so they stay on the board.
        INSERT INTO "player_rating" (
            "id", "user_sub", "game_type", "rating", "matches_played",
            "wins", "points", "display_name", "updated_at"
        )
        SELECT gen_random_uuid(), agg.user_sub, agg.game_type, 1500,
               agg.matches, agg.wins, agg.points, agg.display_name, NOW()
        FROM (
            SELECT mp.user_sub AS user_sub,
                   m.game_type AS game_type,
                   COUNT(*) AS matches,
                   SUM(CASE WHEN mp.won THEN 1 ELSE 0 END) AS wins,
                   SUM(mp.score) AS points,
                   (ARRAY_AGG(mp.display_name ORDER BY m.finished_at DESC, m.id DESC))[1]
                       AS display_name
            FROM "match_player" mp
            JOIN "match" m ON m.id = mp.match_id
            GROUP BY mp.user_sub, m.game_type
        ) agg
        ON CONFLICT ("user_sub", "game_type") DO UPDATE
            SET "wins" = EXCLUDED."wins",
                "points" = EXCLUDED."points",
                "display_name" = EXCLUDED."display_name";
        CREATE INDEX "idx_player_rati_game_ty_abcb75" ON "player_rating" ("game_type", "rating", "wins", "points", "matches_played", "user_sub");"""


async def downgrade(db: BaseDBAsyncClient) -> str:
    return """
        DROP INDEX IF EXISTS "idx_player_rati_game_ty_abcb75";
        ALTER TABLE "player_rating" DROP COLUMN "points";
        ALTER TABLE "player_rating" DROP COLUMN "display_name";
        ALTER TABLE "player_rating" DROP COLUMN "wins";
        CREATE INDEX "idx_player_rati_game_ty_3326d1" ON "player_rating" ("game_type", "rating");"""


MODELS_STATE = (
    "eJztmm1v4jgQgP9KlE89qVcBfdnV6XRSoFTLLZQK6N1pq1VkEkOtGjubOMeiXv/72c67k/"
    "DSEgoVH7ZLxh7HeSYez4zzrM+oDbF31gPMetR/0551AmaQ/8g2nGo6cJxELAQMjHHQM+4y"
    "9pgLLMaFE4A9yEU29CwXOQxRIroamkVnDoYM2ppU0+hEowSK/9gj1Fw4RR6DLm+e8nlobO"
    "FA70yMbVOLD47I9G3D+AT98KHJ6BTyji4f7OE7FyNiw5/Qiy6dJ3OCILYzQJAtBpByUwwo"
    "ZPf3nesb2VNMcWxaFPszkvR2FuyRkri77yP7TOiItikk0AX8EVK4iI9xiDUSBTPmAub6MJ"
    "6qnQhsOAE+FtD13yc+sQRrTd5J/Ln4I5xaqptp3vZH5rA9Mk09ZyMxBYV3KLIoEfZFhAlQ"
    "zy/BuAkQKdXFDVpfjMHJ+dUvEgH12NSVjRKX/iIVAQOBqoSeUBb2CnjlYLcegVsMO6OkMO"
    "dTfg3tSLAEd0SqArZ8Tf00MSRTJhbleWMJ67+MQYC7IXFTvgCDZXkbtjRkk6CeUPYYcDkL"
    "E7A85mtOiaEZLEad1VRY26HqWfRjO+QTZ7ID9EtQjzq99nBk9O7E8DPP+4ElL2PUFi0NKV"
    "0o0pMrxSzxINrfndEXTVxq3/q3bXWtxP1G33QxJ+AzahI6N4GdZhKJI1HGzBNEkPf4Kjsr"
    "qkdD77Wh+VCCTc7Gfw77t8X2TTRU0yKLaf9pmG+gVRk1tU+NfYQZIt6ZuO17bFWCUMbKkd"
    "s86Rn/qB611e03VfOJAZrcvYq4YfKU2tOEYAyspzlwbTPTktjNwWABXS9vuGaoePN1ADGQ"
    "z5s3VTpEu5MjHe4yTKThViuB0gYtI5pvmjVmqgQQMJWPJO4t7lSArCzoTYiuCH1NJ+m5Mg"
    "K+49snspAjbRqFrb4HXQ0R+VsOmQ95N9AriHEfkiBd6JieP9a/HwPffQp8Y7tsEPemdT5e"
    "2Nu4vFwj7uW9SgNf2ZaNfG3kieVqyusNUKt6VeHeqe9VeNdq6/Cu1cp5izYl04BFsedwBj"
    "DuEFaSY8DCoFPsDQfIeCom8et549PVZ94q5yguPi1hPewZ3W7ndqSwZBDMNnlno/6velfD"
    "db83GCvOiOdBhKVEYZRiCEgx3XlhTDbmKgf4li7B2ez3u5kIudkZKVzve8324KQucfNOiE"
    "lx/gX2LOoWeN0bTEGZK4g0FMwToVIV59ruIV/375vdtnY3aLc6w06YksQppmzMwh20ja5C"
    "F2LK54wZ2NTfZhRf5XT3zVlsz+fakAGECzK08tQ6pXLMrbeSW6cNEqRcm+UkaZ1tZib767"
    "RXJCK5YoXCt8BHcz+MpuQrXEjEHT4RQKwi16weHB0m1Fw9gotdMI/T48w7xZ+ePzMMPHPL"
    "GLaM67b+Ul4AqrK0EZQtBoAFD5mrbWTaT5cVN4KyhukmXVdWN1q+60LCtDamWqCnAWJrPI"
    "iyoTum/OE0RhnXjSoYwT00h/8Thzj5usdWRiysiKST5+QASa2KPGQPlxIYc+5Zg9Ul35zT"
    "8I2AXlAOsvO1lmNx5Vhc2Z8UqpLqyv6e3n6oZDXxQlnGpRF+orC7mkr9slZVIhXG9436xa"
    "eLz+dXF3GQH0uWRfr5ID/vudfkmlfcHd9DgRttlGsijffVI0gFZBJprF0+SVSO9ZOV9ZPD"
    "OxrQq4rCdnAw4Dvi25HXfJqS1fyAX6boLs9u+gQv9PAovBz+nn+pEh/kr/llRJWJsQFdVP"
    "yNa9hyuiwZBkmfVVlwuZ23/PVp6R5amCYW7KBhrve2/LD6Ou4bd9DytPBf6Hqo6OCn3Nmn"
    "VD5gqlJJUigW1QaEw+4fkG59rX20vmQfref3UX5HBslG3/6lVHZ/QPE+9fD3+Mxv+5vZy/"
    "+g2+dS"
)