// <copyright file="SplitBaseItemExtras.cs" company="PlaceholderCompany">
// Copyright (c) PlaceholderCompany. All rights reserved.
// </copyright>

using System;
using Microsoft.EntityFrameworkCore.Migrations;

#nullable disable

namespace Jellyfin.Database.Providers.Postgres.Migrations
{
    /// <inheritdoc />
    public partial class SplitBaseItemExtras : Migration
    {
        /// <inheritdoc />
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            // ── 1. Create BaseItemTvExtras ─────────────────────────────────────────
            // Stores TV-specific columns (previously on BaseItems) for
            // TV.Episode, TV.Season, and TV.Series items.
            migrationBuilder.CreateTable(
                name: "BaseItemTvExtras",
                schema: "library",
                columns: table => new
                {
                    ItemId = table.Column<Guid>(type: "uuid", nullable: false),
                    SeriesId = table.Column<Guid>(type: "uuid", nullable: true),
                    SeasonId = table.Column<Guid>(type: "uuid", nullable: true),
                    SeriesName = table.Column<string>(type: "text", nullable: true),
                    SeasonName = table.Column<string>(type: "text", nullable: true),
                    SeriesPresentationUniqueKey = table.Column<string>(type: "text", nullable: true),
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_BaseItemTvExtras", x => x.ItemId);
                    table.ForeignKey(
                        name: "FK_BaseItemTvExtras_BaseItems_ItemId",
                        column: x => x.ItemId,
                        principalSchema: "library",
                        principalTable: "BaseItems",
                        principalColumn: "Id",
                        onDelete: ReferentialAction.Cascade);
                });

            // ── 2. Create BaseItemLiveTvExtras ────────────────────────────────────
            // Stores LiveTV-specific columns for LiveTvProgram and LiveTvChannel items.
            migrationBuilder.CreateTable(
                name: "BaseItemLiveTvExtras",
                schema: "library",
                columns: table => new
                {
                    ItemId = table.Column<Guid>(type: "uuid", nullable: false),
                    StartDate = table.Column<DateTime>(type: "timestamp with time zone", nullable: true),
                    EndDate = table.Column<DateTime>(type: "timestamp with time zone", nullable: true),
                    EpisodeTitle = table.Column<string>(type: "text", nullable: true),
                    ShowId = table.Column<string>(type: "text", nullable: true),
                    ExternalSeriesId = table.Column<string>(type: "text", nullable: true),
                    ExternalServiceId = table.Column<string>(type: "text", nullable: true),
                    Audio = table.Column<int>(type: "integer", nullable: true),
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_BaseItemLiveTvExtras", x => x.ItemId);
                    table.ForeignKey(
                        name: "FK_BaseItemLiveTvExtras_BaseItems_ItemId",
                        column: x => x.ItemId,
                        principalSchema: "library",
                        principalTable: "BaseItems",
                        principalColumn: "Id",
                        onDelete: ReferentialAction.Cascade);
                });

            // ── 3. Create BaseItemAudioExtras ─────────────────────────────────────
            // Stores music-specific columns for Audio.Audio items.
            migrationBuilder.CreateTable(
                name: "BaseItemAudioExtras",
                schema: "library",
                columns: table => new
                {
                    ItemId = table.Column<Guid>(type: "uuid", nullable: false),
                    Album = table.Column<string>(type: "text", nullable: true),
                    Artists = table.Column<string>(type: "text", nullable: true),
                    AlbumArtists = table.Column<string>(type: "text", nullable: true),
                    LUFS = table.Column<float>(type: "real", nullable: true),
                    NormalizationGain = table.Column<float>(type: "real", nullable: true),
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_BaseItemAudioExtras", x => x.ItemId);
                    table.ForeignKey(
                        name: "FK_BaseItemAudioExtras_BaseItems_ItemId",
                        column: x => x.ItemId,
                        principalSchema: "library",
                        principalTable: "BaseItems",
                        principalColumn: "Id",
                        onDelete: ReferentialAction.Cascade);
                });

            // ── 4. Populate BaseItemTvExtras from BaseItems ───────────────────────
            migrationBuilder.Sql(@"
                INSERT INTO library.""BaseItemTvExtras"" (
                    ""ItemId"",
                    ""SeriesId"",
                    ""SeasonId"",
                    ""SeriesName"",
                    ""SeasonName"",
                    ""SeriesPresentationUniqueKey""
                )
                SELECT
                    ""Id"",
                    ""SeriesId"",
                    ""SeasonId"",
                    ""SeriesName"",
                    ""SeasonName"",
                    ""SeriesPresentationUniqueKey""
                FROM library.""BaseItems""
                WHERE ""SeriesId""                    IS NOT NULL
                   OR ""SeasonId""                    IS NOT NULL
                   OR ""SeriesName""                  IS NOT NULL
                   OR ""SeasonName""                  IS NOT NULL
                   OR ""SeriesPresentationUniqueKey"" IS NOT NULL;
            ");

            // ── 5. Populate BaseItemLiveTvExtras from BaseItems ───────────────────
            migrationBuilder.Sql(@"
                INSERT INTO library.""BaseItemLiveTvExtras"" (
                    ""ItemId"",
                    ""StartDate"",
                    ""EndDate"",
                    ""EpisodeTitle"",
                    ""ShowId"",
                    ""ExternalSeriesId"",
                    ""ExternalServiceId"",
                    ""Audio""
                )
                SELECT
                    ""Id"",
                    ""StartDate"",
                    ""EndDate"",
                    ""EpisodeTitle"",
                    ""ShowId"",
                    ""ExternalSeriesId"",
                    ""ExternalServiceId"",
                    ""Audio""
                FROM library.""BaseItems""
                WHERE ""StartDate""         IS NOT NULL
                   OR ""EndDate""           IS NOT NULL
                   OR ""EpisodeTitle""      IS NOT NULL
                   OR ""ShowId""            IS NOT NULL
                   OR ""ExternalSeriesId""  IS NOT NULL
                   OR ""ExternalServiceId"" IS NOT NULL
                   OR ""Audio""             IS NOT NULL;
            ");

            // ── 6. Populate BaseItemAudioExtras from BaseItems ────────────────────
            migrationBuilder.Sql(@"
                INSERT INTO library.""BaseItemAudioExtras"" (
                    ""ItemId"",
                    ""Album"",
                    ""Artists"",
                    ""AlbumArtists"",
                    ""LUFS"",
                    ""NormalizationGain""
                )
                SELECT
                    ""Id"",
                    ""Album"",
                    ""Artists"",
                    ""AlbumArtists"",
                    ""LUFS"",
                    ""NormalizationGain""
                FROM library.""BaseItems""
                WHERE ""Album""            IS NOT NULL
                   OR ""Artists""          IS NOT NULL
                   OR ""AlbumArtists""     IS NOT NULL
                   OR ""LUFS""             IS NOT NULL
                   OR ""NormalizationGain"" IS NOT NULL;
            ");

            // ── 7. Create indexes on extension tables ─────────────────────────────
            migrationBuilder.CreateIndex(
                name: "IX_BaseItemTvExtras_SeriesId",
                schema: "library",
                table: "BaseItemTvExtras",
                column: "SeriesId");

            migrationBuilder.CreateIndex(
                name: "IX_BaseItemTvExtras_SeriesPresentationUniqueKey",
                schema: "library",
                table: "BaseItemTvExtras",
                column: "SeriesPresentationUniqueKey");

            migrationBuilder.CreateIndex(
                name: "IX_BaseItemLiveTvExtras_StartDate",
                schema: "library",
                table: "BaseItemLiveTvExtras",
                column: "StartDate");

            migrationBuilder.CreateIndex(
                name: "IX_BaseItemLiveTvExtras_EndDate",
                schema: "library",
                table: "BaseItemLiveTvExtras",
                column: "EndDate");
        }

        /// <inheritdoc />
        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropTable(name: "BaseItemTvExtras", schema: "library");
            migrationBuilder.DropTable(name: "BaseItemLiveTvExtras", schema: "library");
            migrationBuilder.DropTable(name: "BaseItemAudioExtras", schema: "library");
        }
    }
}
