using Backend.Data;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Migrations;

#nullable disable

namespace Backend.Migrations;

[DbContext(typeof(AwardsDbContext))]
[Migration("20260629185000_SeedCurrentSeasonDemoVotingData")]
public partial class SeedCurrentSeasonDemoVotingData : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql(
            """
            DO $vtsa_current_demo$
            DECLARE
                target_season_id integer;
                subcategory_templates text := '[{"name":"Hidden Star","slug":"hidden-star","sortOrder":1,"viewerRangeMin":1,"viewerRangeMax":20},{"name":"Rising Star","slug":"rising-star","sortOrder":2,"viewerRangeMin":21,"viewerRangeMax":60},{"name":"Shining Star","slug":"shining-star","sortOrder":3,"viewerRangeMin":61,"viewerRangeMax":null}]';
            BEGIN
                SELECT "Id"
                INTO target_season_id
                FROM "Seasons"
                WHERE "IsCurrent" = TRUE
                ORDER BY "Year" DESC
                LIMIT 1;

                IF target_season_id IS NULL THEN
                    SELECT "Id"
                    INTO target_season_id
                    FROM "Seasons"
                    WHERE "Year" = 2030
                    ORDER BY "Id" DESC
                    LIMIT 1;
                END IF;

                IF target_season_id IS NULL THEN
                    RETURN;
                END IF;

                UPDATE "Seasons"
                SET "SubcategoryTemplatesJson" = subcategory_templates
                WHERE "Id" = target_season_id;

                WITH award_groups(ord, group_name, group_slug, description, name_prefix) AS (
                    VALUES
                        (1, 'Main Awards', 'main-awards', 'Die groessten Allround-Auszeichnungen der aktuellen Season.', 'Astra'),
                        (2, 'Discovery', 'discovery', 'Neue Stimmen, Debuets und Creator, die in dieser Season besonders sichtbar geworden sind.', 'Nova'),
                        (3, 'Creative', 'creative', 'Model, Design, Rigging, Art und visuelle Praesentation im Stream.', 'Velvet'),
                        (4, 'Performance', 'performance', 'Gesang, Musik, Buehnenpraesenz und besondere Live-Momente.', 'Melo'),
                        (5, 'Gaming', 'gaming', 'Gameplay, Skill, Chaos, Speedruns und starke Gaming-Unterhaltung.', 'Pixel'),
                        (6, 'Entertainment', 'entertainment', 'Talk, Comedy, Watchalongs, Challenges und kreative Streamformate.', 'Taro'),
                        (7, 'Community', 'community', 'Creator, die ihre Community sichtbar verbinden, einladen und aktiv einbeziehen.', 'Lumi'),
                        (8, 'Collab', 'collab', 'Gemeinsame Streams, Duo-Dynamik und Projekte, die zusammen staerker wirken.', 'Orbit')
                ),
                tiers(ord, tier_name, tier_slug, min_viewers, max_viewers) AS (
                    VALUES
                        (1, 'Hidden Star', 'hidden-star', 1, 20),
                        (2, 'Rising Star', 'rising-star', 21, 60),
                        (3, 'Shining Star', 'shining-star', 61, NULL)
                )
                INSERT INTO "Categories" (
                    "SeasonId",
                    "GroupName",
                    "Name",
                    "Slug",
                    "Description",
                    "SortOrder",
                    "MaxNomineesPerUser",
                    "ViewerRangeMin",
                    "ViewerRangeMax"
                )
                SELECT
                    target_season_id,
                    award_groups.group_name,
                    tiers.tier_name,
                    award_groups.group_slug || '-' || tiers.tier_slug,
                    award_groups.description,
                    (award_groups.ord * 100) + (tiers.ord * 10),
                    3,
                    tiers.min_viewers,
                    tiers.max_viewers
                FROM award_groups
                CROSS JOIN tiers
                ON CONFLICT ("SeasonId", "Slug") DO UPDATE
                SET
                    "GroupName" = EXCLUDED."GroupName",
                    "Name" = EXCLUDED."Name",
                    "Description" = EXCLUDED."Description",
                    "SortOrder" = EXCLUDED."SortOrder",
                    "MaxNomineesPerUser" = EXCLUDED."MaxNomineesPerUser",
                    "ViewerRangeMin" = EXCLUDED."ViewerRangeMin",
                    "ViewerRangeMax" = EXCLUDED."ViewerRangeMax";

                WITH award_groups(ord, group_name, group_slug, name_prefix) AS (
                    VALUES
                        (1, 'Main Awards', 'main-awards', 'Astra'),
                        (2, 'Discovery', 'discovery', 'Nova'),
                        (3, 'Creative', 'creative', 'Velvet'),
                        (4, 'Performance', 'performance', 'Melo'),
                        (5, 'Gaming', 'gaming', 'Pixel'),
                        (6, 'Entertainment', 'entertainment', 'Taro'),
                        (7, 'Community', 'community', 'Lumi'),
                        (8, 'Collab', 'collab', 'Orbit')
                ),
                tiers(ord, tier_name, tier_slug) AS (
                    VALUES
                        (1, 'Hidden Star', 'hidden-star'),
                        (2, 'Rising Star', 'rising-star'),
                        (3, 'Shining Star', 'shining-star')
                ),
                slots(ord, name_suffix, platform) AS (
                    VALUES
                        (1, 'Aster', 'Twitch'),
                        (2, 'Vega', 'YouTube')
                ),
                candidate_rows AS (
                    SELECT
                        categories."Id" AS category_id,
                        award_groups.ord AS group_ord,
                        tiers.ord AS tier_ord,
                        slots.ord AS slot_ord,
                        award_groups.name_prefix || ' ' || slots.name_suffix || ' (' || tiers.tier_name || ')' AS display_name,
                        regexp_replace(
                            lower('vtsa_demo_current_' || award_groups.name_prefix || '_' || tiers.tier_slug || '_' || slots.name_suffix),
                            '[^a-z0-9]+',
                            '_',
                            'g'
                        ) AS channel_slug,
                        slots.platform
                    FROM award_groups
                    CROSS JOIN tiers
                    CROSS JOIN slots
                    INNER JOIN "Categories" categories
                        ON categories."SeasonId" = target_season_id
                        AND categories."Slug" = award_groups.group_slug || '-' || tiers.tier_slug
                )
                INSERT INTO "Candidates" (
                    "SeasonId",
                    "CategoryId",
                    "StreamerIdentityId",
                    "DisplayName",
                    "ChannelSlug",
                    "Platform",
                    "NominationTally",
                    "AcceptanceStatus",
                    "AcceptanceNote",
                    "ClipCompilationUrl",
                    "ClipCompilationTitle",
                    "ClipCompilationPlatform",
                    "ClipEmbedStatus"
                )
                SELECT
                    target_season_id,
                    candidate_rows.category_id,
                    NULL,
                    candidate_rows.display_name,
                    candidate_rows.channel_slug,
                    candidate_rows.platform,
                    4 + (candidate_rows.group_ord * 2) + candidate_rows.tier_ord + candidate_rows.slot_ord,
                    'approved',
                    'Demo-Kandidat fuer den aktuellen Live-Voting-Test.',
                    CASE
                        WHEN candidate_rows.slot_ord = 1 THEN 'https://youtu.be/demo-' || candidate_rows.channel_slug
                        ELSE NULL
                    END,
                    CASE
                        WHEN candidate_rows.slot_ord = 1 THEN candidate_rows.display_name || ' Highlight Reel'
                        ELSE NULL
                    END,
                    CASE
                        WHEN candidate_rows.slot_ord = 1 THEN 'YouTube'
                        ELSE NULL
                    END,
                    CASE
                        WHEN candidate_rows.slot_ord = 1 THEN 'available'
                        ELSE 'unchecked'
                    END
                FROM candidate_rows
                WHERE NOT EXISTS (
                    SELECT 1
                    FROM "Candidates" existing
                    WHERE existing."SeasonId" = target_season_id
                      AND lower(existing."ChannelSlug") = lower(candidate_rows.channel_slug)
                      AND existing."CategoryId" = candidate_rows.category_id
                );

                PERFORM setval(pg_get_serial_sequence('"Categories"', 'Id'), COALESCE((SELECT MAX("Id") FROM "Categories"), 1));
                PERFORM setval(pg_get_serial_sequence('"Candidates"', 'Id'), COALESCE((SELECT MAX("Id") FROM "Candidates"), 1));
            END;
            $vtsa_current_demo$;
            """
        );
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql(
            """
            DELETE FROM "Candidates"
            WHERE "ChannelSlug" LIKE 'vtsa_demo_current_%';
            """
        );
    }
}