ANTOOZA-BACKEND/Infrastructure/Persistence/Migrations/20260417000739_AddRankingAndBroadcastEntities.cs

using System;
using Microsoft.EntityFrameworkCore.Migrations;

#nullable disable

namespace Infrastructure.Migrations.AppDb;

/// <inheritdoc />
public partial class AddRankingAndBroadcastEntities : Migration
{
    /// <inheritdoc />
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.CreateTable(
            name: "BroadcastSession",
            columns: table => new
            {
                ID = table.Column<int>(type: "int", nullable: false)
                    .Annotation("SqlServer:Identity", "1, 1"),
                ChannelID = table.Column<int>(type: "int", nullable: false, comment: "채널 ID"),
                Platform = table.Column<int>(type: "int", nullable: false, comment: "플랫폼 (YouTube=1)"),
                VideoID = table.Column<string>(type: "nvarchar(50)", maxLength: 50, nullable: false, comment: "플랫폼 비디오 ID"),
                Title = table.Column<string>(type: "nvarchar(500)", maxLength: 500, nullable: true, comment: "방송 제목"),
                StartAt = table.Column<DateTime>(type: "datetime2", nullable: true, comment: "방송 시작 시각"),
                EndAt = table.Column<DateTime>(type: "datetime2", nullable: true, comment: "방송 종료 시각"),
                DurationSec = table.Column<int>(type: "int", nullable: false, comment: "방송 시간 (초)"),
                FinalViews = table.Column<long>(type: "bigint", nullable: false, comment: "최종 조회수"),
                FinalLikes = table.Column<long>(type: "bigint", nullable: false, comment: "최종 좋아요"),
                IsFinalized = table.Column<bool>(type: "bit", nullable: false, comment: "최종 확정 여부 (End + 24h 경과)"),
                UpdatedAt = table.Column<DateTime>(type: "datetime2", nullable: true, comment: "수정 일시"),
                CreatedAt = table.Column<DateTime>(type: "datetime2", nullable: false, comment: "생성 일시")
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_BroadcastSession", x => x.ID);
                table.ForeignKey(
                    name: "FK_BroadcastSession_Channel_ChannelID",
                    column: x => x.ChannelID,
                    principalTable: "Channel",
                    principalColumn: "ID",
                    onDelete: ReferentialAction.Cascade);
            },
            comment: "방송 세션 (라이브 1회 단위)");

        migrationBuilder.CreateTable(
            name: "ChannelBroadcastStats",
            columns: table => new
            {
                ChannelID = table.Column<int>(type: "int", nullable: false, comment: "채널 ID"),
                TotalDurationSec = table.Column<long>(type: "bigint", nullable: false, comment: "누적 방송 시간 (초)"),
                CumulativeViews = table.Column<long>(type: "bigint", nullable: false, comment: "누적 조회수"),
                TotalLikes = table.Column<long>(type: "bigint", nullable: false, comment: "누적 좋아요"),
                SessionCount = table.Column<int>(type: "int", nullable: false, comment: "방송 횟수"),
                LastBroadcastAt = table.Column<DateTime>(type: "datetime2", nullable: true, comment: "마지막 방송 일시"),
                LastAggregatedAt = table.Column<DateTime>(type: "datetime2", nullable: true, comment: "마지막 집계 일시"),
                CreatedAt = table.Column<DateTime>(type: "datetime2", nullable: false, comment: "생성 일시")
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_ChannelBroadcastStats", x => x.ChannelID);
                table.ForeignKey(
                    name: "FK_ChannelBroadcastStats_Channel_ChannelID",
                    column: x => x.ChannelID,
                    principalTable: "Channel",
                    principalColumn: "ID",
                    onDelete: ReferentialAction.Cascade);
            },
            comment: "채널별 누적 방송 지표");

        migrationBuilder.CreateTable(
            name: "RankingSnapshot",
            columns: table => new
            {
                ID = table.Column<int>(type: "int", nullable: false)
                    .Annotation("SqlServer:Identity", "1, 1"),
                Type = table.Column<int>(type: "int", nullable: false, comment: "랭킹 종류 (1=종합, 2=크리에이터, 3=후원자)"),
                Period = table.Column<int>(type: "int", nullable: false, comment: "기간 (1=오늘, 2=어제, 3=이번주, 4=이번달, 5=지난달, 6=전체)"),
                PeriodStart = table.Column<DateTime>(type: "datetime2", nullable: false, comment: "기간 시작"),
                PeriodEnd = table.Column<DateTime>(type: "datetime2", nullable: false, comment: "기간 종료"),
                Category = table.Column<string>(type: "nvarchar(30)", maxLength: 30, nullable: true, comment: "카테고리 (전체/게임/버츄얼/토크/음악/먹방)"),
                MemberID = table.Column<int>(type: "int", nullable: false, comment: "회원 ID"),
                Rank = table.Column<int>(type: "int", nullable: false, comment: "순위"),
                PreviousRank = table.Column<int>(type: "int", nullable: true, comment: "전일 순위"),
                Score = table.Column<long>(type: "bigint", nullable: false, comment: "종합 점수"),
                MoneyAmount = table.Column<long>(type: "bigint", nullable: false, comment: "수령/후원 금액"),
                DonorCount = table.Column<int>(type: "int", nullable: false, comment: "후원자 수"),
                BroadcastSec = table.Column<long>(type: "bigint", nullable: false, comment: "방송 시간 (초)"),
                ViewCount = table.Column<long>(type: "bigint", nullable: false, comment: "조회수"),
                LikeCount = table.Column<long>(type: "bigint", nullable: false, comment: "좋아요"),
                SnapshotAt = table.Column<DateTime>(type: "datetime2", nullable: false, comment: "스냅샷 일시")
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_RankingSnapshot", x => x.ID);
                table.ForeignKey(
                    name: "FK_RankingSnapshot_Member_MemberID",
                    column: x => x.MemberID,
                    principalTable: "Member",
                    principalColumn: "ID");
            },
            comment: "랭킹 스냅샷 (일 1회 집계)");

        migrationBuilder.CreateIndex(
            name: "IX_BroadcastSession_ChannelID_EndAt",
            table: "BroadcastSession",
            columns: new[] { "ChannelID", "EndAt" },
            descending: new[] { false, true });

        migrationBuilder.CreateIndex(
            name: "IX_BroadcastSession_IsFinalized",
            table: "BroadcastSession",
            column: "IsFinalized");

        migrationBuilder.CreateIndex(
            name: "IX_BroadcastSession_Platform_VideoID",
            table: "BroadcastSession",
            columns: new[] { "Platform", "VideoID" },
            unique: true);

        migrationBuilder.CreateIndex(
            name: "IX_RankingSnapshot_MemberID",
            table: "RankingSnapshot",
            column: "MemberID");

        migrationBuilder.CreateIndex(
            name: "IX_RankingSnapshot_SnapshotAt",
            table: "RankingSnapshot",
            column: "SnapshotAt",
            descending: new bool[0]);

        migrationBuilder.CreateIndex(
            name: "IX_RankingSnapshot_Type_Period_Category_MemberID",
            table: "RankingSnapshot",
            columns: new[] { "Type", "Period", "Category", "MemberID" },
            unique: true,
            filter: "[Category] IS NOT NULL");

        migrationBuilder.CreateIndex(
            name: "IX_RankingSnapshot_Type_Period_Category_Rank",
            table: "RankingSnapshot",
            columns: new[] { "Type", "Period", "Category", "Rank" });
    }

    /// <inheritdoc />
    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropTable(
            name: "BroadcastSession");

        migrationBuilder.DropTable(
            name: "ChannelBroadcastStats");

        migrationBuilder.DropTable(
            name: "RankingSnapshot");
    }
}