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

#nullable disable

namespace Backend.Migrations;

[DbContext(typeof(AwardsDbContext))]
[Migration("20260629183000_SeedLiveDemoAwardData")]
public partial class SeedLiveDemoAwardData : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql(
            """
            DO $vtsa_live_demo$
            DECLARE
                demo_year integer := 2030;
                demo_season_id integer;
                should_make_current boolean;
                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 NOT EXISTS (SELECT 1 FROM "Seasons" WHERE "IsCurrent" = TRUE)
                INTO should_make_current;

                INSERT INTO "Seasons" (
                    "Year",
                    "Name",
                    "IsDemo",
                    "IsCurrent",
                    "IsCommunityOnly",
                    "CurrentPhase",
                    "NominationStartsAt",
                    "NominationEndsAt",
                    "VotingStartsAt",
                    "VotingEndsAt",
                    "ReviewStartsAt",
                    "ReviewEndsAt",
                    "ShowDate",
                    "ShowStartsAt",
                    "WinnersPublishedAt",
                    "WinnersPublishedByTwitchId",
                    "SubcategoryTemplatesJson",
                    "WorkflowRulesJson"
                )
                VALUES (
                    demo_year,
                    'VTuber Star Awards Demo Season',
                    TRUE,
                    should_make_current,
                    FALSE,
                    'Voting',
                    DATE '2030-05-01',
                    DATE '2030-05-31',
                    DATE '2030-06-01',
                    DATE '2030-06-30',
                    DATE '2030-07-01',
                    DATE '2030-07-10',
                    DATE '2030-07-20',
                    TIME '20:00',
                    NULL,
                    NULL,
                    subcategory_templates,
                    '[]'
                )
                ON CONFLICT ("Year") DO UPDATE
                SET
                    "Name" = EXCLUDED."Name",
                    "IsDemo" = TRUE,
                    "IsCurrent" = CASE
                        WHEN should_make_current THEN TRUE
                        ELSE "Seasons"."IsCurrent"
                    END,
                    "IsCommunityOnly" = FALSE,
                    "CurrentPhase" = 'Voting',
                    "NominationStartsAt" = DATE '2030-05-01',
                    "NominationEndsAt" = DATE '2030-05-31',
                    "VotingStartsAt" = DATE '2030-06-01',
                    "VotingEndsAt" = DATE '2030-06-30',
                    "ReviewStartsAt" = DATE '2030-07-01',
                    "ReviewEndsAt" = DATE '2030-07-10',
                    "ShowDate" = DATE '2030-07-20',
                    "ShowStartsAt" = TIME '20:00',
                    "SubcategoryTemplatesJson" = subcategory_templates
                RETURNING "Id" INTO demo_season_id;

                IF should_make_current THEN
                    UPDATE "Seasons"
                    SET "IsCurrent" = FALSE
                    WHERE "Id" <> demo_season_id;

                    UPDATE "Seasons"
                    SET "IsCurrent" = TRUE
                    WHERE "Id" = demo_season_id;
                END IF;

                WITH award_groups(ord, group_name, group_slug, description, name_prefix) AS (
                    VALUES
                        (1, 'Creator des Jahres', 'creator-des-jahres', 'Allround-Creator mit starker Season, klarer Community-Praesenz und wiedererkennbarer Identitaet.', 'Astra'),
                        (2, 'Newcomer', 'newcomer', 'Neue Stimmen, Debuets und Creator, die in dieser Season besonders sichtbar geworden sind.', 'Nova'),
                        (3, 'Model und Design', 'model-design', 'Live2D, 3D, Outfit, Rigging und visuelle Praesentation im Stream.', 'Velvet'),
                        (4, 'Gesang und Musik', 'gesang-musik', 'Covers, Originale, Live-Gesang, Musikproduktion und Buehnenmomente.', 'Melo'),
                        (5, 'Gaming', 'gaming', 'Gameplay, Skill, Chaos, Speedruns und starke Gaming-Unterhaltung.', 'Pixel'),
                        (6, 'Variety und Entertainment', 'variety-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 und Duo', 'collab-duo', '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
                    demo_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, 'Creator des Jahres', 'creator-des-jahres', 'Astra'),
                        (2, 'Newcomer', 'newcomer', 'Nova'),
                        (3, 'Model und Design', 'model-design', 'Velvet'),
                        (4, 'Gesang und Musik', 'gesang-musik', 'Melo'),
                        (5, 'Gaming', 'gaming', 'Pixel'),
                        (6, 'Variety und Entertainment', 'variety-entertainment', 'Taro'),
                        (7, 'Community', 'community', 'Lumi'),
                        (8, 'Collab und Duo', 'collab-duo', '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_' || 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" = demo_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
                    demo_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 Live-Testdaten.',
                    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" = demo_season_id
                      AND lower(existing."ChannelSlug") = lower(candidate_rows.channel_slug)
                      AND existing."CategoryId" = candidate_rows.category_id
                );

                PERFORM setval(pg_get_serial_sequence('"Seasons"', 'Id'), COALESCE((SELECT MAX("Id") FROM "Seasons"), 1));
                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_live_demo$;
            """
        );
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql(
            """
            DO $vtsa_live_demo_down$
            DECLARE
                demo_season_id integer;
            BEGIN
                SELECT "Id"
                INTO demo_season_id
                FROM "Seasons"
                WHERE "Year" = 2030
                  AND "IsDemo" = TRUE
                  AND "Name" = 'VTuber Star Awards Demo Season';

                IF demo_season_id IS NULL THEN
                    RETURN;
                END IF;

                DELETE FROM "Candidates"
                WHERE "SeasonId" = demo_season_id
                  AND "ChannelSlug" LIKE 'vtsa_demo_%';

                DELETE FROM "Categories"
                WHERE "SeasonId" = demo_season_id
                  AND "Slug" IN (
                    'creator-des-jahres-hidden-star',
                    'creator-des-jahres-rising-star',
                    'creator-des-jahres-shining-star',
                    'newcomer-hidden-star',
                    'newcomer-rising-star',
                    'newcomer-shining-star',
                    'model-design-hidden-star',
                    'model-design-rising-star',
                    'model-design-shining-star',
                    'gesang-musik-hidden-star',
                    'gesang-musik-rising-star',
                    'gesang-musik-shining-star',
                    'gaming-hidden-star',
                    'gaming-rising-star',
                    'gaming-shining-star',
                    'variety-entertainment-hidden-star',
                    'variety-entertainment-rising-star',
                    'variety-entertainment-shining-star',
                    'community-hidden-star',
                    'community-rising-star',
                    'community-shining-star',
                    'collab-duo-hidden-star',
                    'collab-duo-rising-star',
                    'collab-duo-shining-star'
                  );
            END;
            $vtsa_live_demo_down$;
            """
        );
    }
}