using System;
using Microsoft.EntityFrameworkCore.Migrations;

#nullable disable

namespace CMSMicroservice.Infrastructure.Persistence.Migrations
{
    /// <inheritdoc />
    public partial class AddPackageBasedSystem : Migration
    {
        /// <inheritdoc />
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropIndex(
                name: "IX_WeeklyCommissionPool_WeekDefinitionId",
                schema: "CMS",
                table: "WeeklyCommissionPools");

            migrationBuilder.DropIndex(
                name: "IX_UserCommissionPayout_UserId_WeekDefinitionId",
                schema: "CMS",
                table: "UserCommissionPayouts");

            migrationBuilder.DropIndex(
                name: "IX_NetworkWeeklyBalance_UserId_WeekDefinitionId",
                schema: "CMS",
                table: "NetworkWeeklyBalances");

            migrationBuilder.AddColumn<long>(
                name: "PackageId",
                schema: "CMS",
                table: "WeeklyCommissionPools",
                type: "bigint",
                nullable: false,
                defaultValue: 0L);

            migrationBuilder.AddColumn<long>(
                name: "PackageId",
                schema: "CMS",
                table: "UserWalletChangeLogs",
                type: "bigint",
                nullable: true);

            migrationBuilder.AddColumn<long>(
                name: "PackageId",
                schema: "CMS",
                table: "UserCommissionPayouts",
                type: "bigint",
                nullable: false,
                defaultValue: 0L);

            migrationBuilder.AddColumn<long>(
                name: "ActivationFee",
                schema: "CMS",
                table: "Packages",
                type: "bigint",
                nullable: false,
                defaultValue: 0L);

            migrationBuilder.AddColumn<decimal>(
                name: "DiscountMultiplier",
                schema: "CMS",
                table: "Packages",
                type: "decimal(18,4)",
                precision: 18,
                scale: 4,
                nullable: false,
                defaultValue: 2.0m);

            migrationBuilder.AddColumn<bool>(
                name: "IsActive",
                schema: "CMS",
                table: "Packages",
                type: "bit",
                nullable: false,
                defaultValue: true);

            migrationBuilder.AddColumn<bool>(
                name: "IsBasePackage",
                schema: "CMS",
                table: "Packages",
                type: "bit",
                nullable: false,
                defaultValue: false);

            migrationBuilder.AddColumn<long>(
                name: "MagicWalletMaxCredit",
                schema: "CMS",
                table: "Packages",
                type: "bigint",
                nullable: false,
                defaultValue: 2500000000L);

            migrationBuilder.AddColumn<long>(
                name: "MagicWalletMaxDeposit",
                schema: "CMS",
                table: "Packages",
                type: "bigint",
                nullable: false,
                defaultValue: 1000000000L);

            migrationBuilder.AddColumn<decimal>(
                name: "MagicWalletMultiplier",
                schema: "CMS",
                table: "Packages",
                type: "decimal(18,4)",
                precision: 18,
                scale: 4,
                nullable: false,
                defaultValue: 2.5m);

            migrationBuilder.AddColumn<int>(
                name: "MaxBalancesPerLeg",
                schema: "CMS",
                table: "Packages",
                type: "int",
                nullable: false,
                defaultValue: 300);

            migrationBuilder.AddColumn<int>(
                name: "MaxNetworkLevel",
                schema: "CMS",
                table: "Packages",
                type: "int",
                nullable: false,
                defaultValue: 15);

            migrationBuilder.AddColumn<int>(
                name: "SortOrder",
                schema: "CMS",
                table: "Packages",
                type: "int",
                nullable: false,
                defaultValue: 0);

            migrationBuilder.AddColumn<bool>(
                name: "SupportsDayaPurchase",
                schema: "CMS",
                table: "Packages",
                type: "bit",
                nullable: false,
                defaultValue: false);

            migrationBuilder.AddColumn<bool>(
                name: "SupportsDirectPurchase",
                schema: "CMS",
                table: "Packages",
                type: "bit",
                nullable: false,
                defaultValue: true);

            migrationBuilder.AddColumn<long>(
                name: "PackageId",
                schema: "CMS",
                table: "NetworkWeeklyBalances",
                type: "bigint",
                nullable: false,
                defaultValue: 0L);

            migrationBuilder.AddColumn<DateTime>(
                name: "FirstActivationDate",
                schema: "CMS",
                table: "ClubMemberships",
                type: "datetime2",
                nullable: true);

            migrationBuilder.AddColumn<long>(
                name: "FirstPackageId",
                schema: "CMS",
                table: "ClubMemberships",
                type: "bigint",
                nullable: true);

            migrationBuilder.AddColumn<DateTime>(
                name: "LastActivationDate",
                schema: "CMS",
                table: "ClubMemberships",
                type: "datetime2",
                nullable: true);

            migrationBuilder.AddColumn<long>(
                name: "LastPackageId",
                schema: "CMS",
                table: "ClubMemberships",
                type: "bigint",
                nullable: true);

            migrationBuilder.AddColumn<long>(
                name: "PackageId",
                schema: "CMS",
                table: "ClubMembershipCycles",
                type: "bigint",
                nullable: false,
                defaultValue: 0L);

            migrationBuilder.CreateTable(
                name: "PackageFeatures",
                schema: "CMS",
                columns: table => new
                {
                    Id = table.Column<long>(type: "bigint", nullable: false)
                        .Annotation("SqlServer:Identity", "1, 1"),
                    PackageId = table.Column<long>(type: "bigint", nullable: false),
                    ClubFeatureId = table.Column<long>(type: "bigint", nullable: false),
                    IsIncluded = table.Column<bool>(type: "bit", nullable: false, defaultValue: true),
                    Created = table.Column<DateTime>(type: "datetime2", nullable: false),
                    CreatedBy = table.Column<string>(type: "nvarchar(max)", nullable: true),
                    LastModified = table.Column<DateTime>(type: "datetime2", nullable: true),
                    LastModifiedBy = table.Column<string>(type: "nvarchar(max)", nullable: true),
                    IsDeleted = table.Column<bool>(type: "bit", nullable: false)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_PackageFeatures", x => x.Id);
                    table.ForeignKey(
                        name: "FK_PackageFeatures_ClubFeatures_ClubFeatureId",
                        column: x => x.ClubFeatureId,
                        principalSchema: "CMS",
                        principalTable: "ClubFeatures",
                        principalColumn: "Id",
                        onDelete: ReferentialAction.Restrict);
                    table.ForeignKey(
                        name: "FK_PackageFeatures_Packages_PackageId",
                        column: x => x.PackageId,
                        principalSchema: "CMS",
                        principalTable: "Packages",
                        principalColumn: "Id",
                        onDelete: ReferentialAction.Cascade);
                });

            migrationBuilder.CreateIndex(
                name: "IX_WeeklyCommissionPool_WeekDef_Package",
                schema: "CMS",
                table: "WeeklyCommissionPools",
                columns: new[] { "WeekDefinitionId", "PackageId" },
                unique: true);

            migrationBuilder.CreateIndex(
                name: "IX_WeeklyCommissionPools_PackageId",
                schema: "CMS",
                table: "WeeklyCommissionPools",
                column: "PackageId");

            migrationBuilder.CreateIndex(
                name: "IX_UserWalletChangeLogs_PackageId",
                schema: "CMS",
                table: "UserWalletChangeLogs",
                column: "PackageId");

            migrationBuilder.CreateIndex(
                name: "IX_UserCommissionPayout_User_WeekDef_Package",
                schema: "CMS",
                table: "UserCommissionPayouts",
                columns: new[] { "UserId", "WeekDefinitionId", "PackageId" },
                unique: true);

            migrationBuilder.CreateIndex(
                name: "IX_UserCommissionPayouts_PackageId",
                schema: "CMS",
                table: "UserCommissionPayouts",
                column: "PackageId");

            migrationBuilder.CreateIndex(
                name: "IX_Package_IsActive",
                schema: "CMS",
                table: "Packages",
                column: "IsActive");

            migrationBuilder.CreateIndex(
                name: "IX_Package_SortOrder",
                schema: "CMS",
                table: "Packages",
                column: "SortOrder");

            migrationBuilder.CreateIndex(
                name: "IX_NetworkWeeklyBalance_User_WeekDef_Package",
                schema: "CMS",
                table: "NetworkWeeklyBalances",
                columns: new[] { "UserId", "WeekDefinitionId", "PackageId" },
                unique: true);

            migrationBuilder.CreateIndex(
                name: "IX_NetworkWeeklyBalances_PackageId",
                schema: "CMS",
                table: "NetworkWeeklyBalances",
                column: "PackageId");

            migrationBuilder.CreateIndex(
                name: "IX_ClubMembership_LastActivationDate",
                schema: "CMS",
                table: "ClubMemberships",
                column: "LastActivationDate");

            migrationBuilder.CreateIndex(
                name: "IX_ClubMemberships_FirstPackageId",
                schema: "CMS",
                table: "ClubMemberships",
                column: "FirstPackageId");

            migrationBuilder.CreateIndex(
                name: "IX_ClubMemberships_LastPackageId",
                schema: "CMS",
                table: "ClubMemberships",
                column: "LastPackageId");

            migrationBuilder.CreateIndex(
                name: "IX_ClubMembershipCycles_PackageId",
                schema: "CMS",
                table: "ClubMembershipCycles",
                column: "PackageId");

            migrationBuilder.CreateIndex(
                name: "IX_PackageFeature_PackageId_ClubFeatureId",
                schema: "CMS",
                table: "PackageFeatures",
                columns: new[] { "PackageId", "ClubFeatureId" },
                unique: true);

            migrationBuilder.CreateIndex(
                name: "IX_PackageFeatures_ClubFeatureId",
                schema: "CMS",
                table: "PackageFeatures",
                column: "ClubFeatureId");

            // === DATA MIGRATION: Seed golden package and backfill existing records ===
            
            // 1. Ensure golden package exists with known Id
            // Use IDENTITY_INSERT to guarantee Id=1 (if Package table uses identity)
            migrationBuilder.Sql(@"
                SET IDENTITY_INSERT [CMS].[Packages] ON;
                
                IF NOT EXISTS (SELECT 1 FROM [CMS].[Packages] WHERE [Id] = 1)
                BEGIN
                    INSERT INTO [CMS].[Packages] 
                        ([Id], [Title], [Description], [ImagePath], [Price],
                         [SortOrder], [IsActive], [IsBasePackage], 
                         [SupportsDayaPurchase], [SupportsDirectPurchase],
                         [ActivationFee], [DiscountMultiplier], [MagicWalletMultiplier],
                         [MaxBalancesPerLeg], [MaxNetworkLevel],
                         [MagicWalletMaxDeposit], [MagicWalletMaxCredit],
                         [Created], [IsDeleted])
                    VALUES 
                        (1, N'پکیج طلایی', N'پکیج اصلی باشگاه مشتریان کارا بازار سلامت', 
                         N'/images/packages/golden.png', 56000000,
                         1, 1, 1, 
                         1, 1,
                         25200000, 2.0, 2.5,
                         300, 15,
                         1000000000, 2500000000,
                         GETUTCDATE(), 0);
                END
                
                SET IDENTITY_INSERT [CMS].[Packages] OFF;
            ");
            
            // 2. Backfill PackageId on existing records → point to golden package (Id=1)
            migrationBuilder.Sql(@"
                UPDATE [CMS].[WeeklyCommissionPools] SET [PackageId] = 1 WHERE [PackageId] = 0;
                UPDATE [CMS].[UserCommissionPayouts] SET [PackageId] = 1 WHERE [PackageId] = 0;
                UPDATE [CMS].[NetworkWeeklyBalances] SET [PackageId] = 1 WHERE [PackageId] = 0;
                UPDATE [CMS].[ClubMembershipCycles] SET [PackageId] = 1 WHERE [PackageId] = 0;
            ");
            
            // 3. Backfill ClubMembership First/Last fields from existing ActivatedAt
            migrationBuilder.Sql(@"
                UPDATE cm SET 
                    cm.[FirstActivationDate] = cm.[ActivatedAt],
                    cm.[LastActivationDate] = cm.[ActivatedAt],
                    cm.[FirstPackageId] = 1,
                    cm.[LastPackageId] = 1
                FROM [CMS].[ClubMemberships] cm
                WHERE cm.[ActivatedAt] IS NOT NULL 
                  AND cm.[FirstActivationDate] IS NULL;
            ");

            // === END DATA MIGRATION ===

            migrationBuilder.AddForeignKey(
                name: "FK_ClubMembershipCycles_Packages_PackageId",
                schema: "CMS",
                table: "ClubMembershipCycles",
                column: "PackageId",
                principalSchema: "CMS",
                principalTable: "Packages",
                principalColumn: "Id",
                onDelete: ReferentialAction.Restrict);

            migrationBuilder.AddForeignKey(
                name: "FK_ClubMemberships_Packages_FirstPackageId",
                schema: "CMS",
                table: "ClubMemberships",
                column: "FirstPackageId",
                principalSchema: "CMS",
                principalTable: "Packages",
                principalColumn: "Id",
                onDelete: ReferentialAction.Restrict);

            migrationBuilder.AddForeignKey(
                name: "FK_ClubMemberships_Packages_LastPackageId",
                schema: "CMS",
                table: "ClubMemberships",
                column: "LastPackageId",
                principalSchema: "CMS",
                principalTable: "Packages",
                principalColumn: "Id",
                onDelete: ReferentialAction.Restrict);

            migrationBuilder.AddForeignKey(
                name: "FK_NetworkWeeklyBalances_Packages_PackageId",
                schema: "CMS",
                table: "NetworkWeeklyBalances",
                column: "PackageId",
                principalSchema: "CMS",
                principalTable: "Packages",
                principalColumn: "Id",
                onDelete: ReferentialAction.Restrict);

            migrationBuilder.AddForeignKey(
                name: "FK_UserCommissionPayouts_Packages_PackageId",
                schema: "CMS",
                table: "UserCommissionPayouts",
                column: "PackageId",
                principalSchema: "CMS",
                principalTable: "Packages",
                principalColumn: "Id",
                onDelete: ReferentialAction.Restrict);

            migrationBuilder.AddForeignKey(
                name: "FK_UserWalletChangeLogs_Packages_PackageId",
                schema: "CMS",
                table: "UserWalletChangeLogs",
                column: "PackageId",
                principalSchema: "CMS",
                principalTable: "Packages",
                principalColumn: "Id",
                onDelete: ReferentialAction.Restrict);

            migrationBuilder.AddForeignKey(
                name: "FK_WeeklyCommissionPools_Packages_PackageId",
                schema: "CMS",
                table: "WeeklyCommissionPools",
                column: "PackageId",
                principalSchema: "CMS",
                principalTable: "Packages",
                principalColumn: "Id",
                onDelete: ReferentialAction.Restrict);
        }

        /// <inheritdoc />
        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropForeignKey(
                name: "FK_ClubMembershipCycles_Packages_PackageId",
                schema: "CMS",
                table: "ClubMembershipCycles");

            migrationBuilder.DropForeignKey(
                name: "FK_ClubMemberships_Packages_FirstPackageId",
                schema: "CMS",
                table: "ClubMemberships");

            migrationBuilder.DropForeignKey(
                name: "FK_ClubMemberships_Packages_LastPackageId",
                schema: "CMS",
                table: "ClubMemberships");

            migrationBuilder.DropForeignKey(
                name: "FK_NetworkWeeklyBalances_Packages_PackageId",
                schema: "CMS",
                table: "NetworkWeeklyBalances");

            migrationBuilder.DropForeignKey(
                name: "FK_UserCommissionPayouts_Packages_PackageId",
                schema: "CMS",
                table: "UserCommissionPayouts");

            migrationBuilder.DropForeignKey(
                name: "FK_UserWalletChangeLogs_Packages_PackageId",
                schema: "CMS",
                table: "UserWalletChangeLogs");

            migrationBuilder.DropForeignKey(
                name: "FK_WeeklyCommissionPools_Packages_PackageId",
                schema: "CMS",
                table: "WeeklyCommissionPools");

            migrationBuilder.DropTable(
                name: "PackageFeatures",
                schema: "CMS");

            migrationBuilder.DropIndex(
                name: "IX_WeeklyCommissionPool_WeekDef_Package",
                schema: "CMS",
                table: "WeeklyCommissionPools");

            migrationBuilder.DropIndex(
                name: "IX_WeeklyCommissionPools_PackageId",
                schema: "CMS",
                table: "WeeklyCommissionPools");

            migrationBuilder.DropIndex(
                name: "IX_UserWalletChangeLogs_PackageId",
                schema: "CMS",
                table: "UserWalletChangeLogs");

            migrationBuilder.DropIndex(
                name: "IX_UserCommissionPayout_User_WeekDef_Package",
                schema: "CMS",
                table: "UserCommissionPayouts");

            migrationBuilder.DropIndex(
                name: "IX_UserCommissionPayouts_PackageId",
                schema: "CMS",
                table: "UserCommissionPayouts");

            migrationBuilder.DropIndex(
                name: "IX_Package_IsActive",
                schema: "CMS",
                table: "Packages");

            migrationBuilder.DropIndex(
                name: "IX_Package_SortOrder",
                schema: "CMS",
                table: "Packages");

            migrationBuilder.DropIndex(
                name: "IX_NetworkWeeklyBalance_User_WeekDef_Package",
                schema: "CMS",
                table: "NetworkWeeklyBalances");

            migrationBuilder.DropIndex(
                name: "IX_NetworkWeeklyBalances_PackageId",
                schema: "CMS",
                table: "NetworkWeeklyBalances");

            migrationBuilder.DropIndex(
                name: "IX_ClubMembership_LastActivationDate",
                schema: "CMS",
                table: "ClubMemberships");

            migrationBuilder.DropIndex(
                name: "IX_ClubMemberships_FirstPackageId",
                schema: "CMS",
                table: "ClubMemberships");

            migrationBuilder.DropIndex(
                name: "IX_ClubMemberships_LastPackageId",
                schema: "CMS",
                table: "ClubMemberships");

            migrationBuilder.DropIndex(
                name: "IX_ClubMembershipCycles_PackageId",
                schema: "CMS",
                table: "ClubMembershipCycles");

            migrationBuilder.DropColumn(
                name: "PackageId",
                schema: "CMS",
                table: "WeeklyCommissionPools");

            migrationBuilder.DropColumn(
                name: "PackageId",
                schema: "CMS",
                table: "UserWalletChangeLogs");

            migrationBuilder.DropColumn(
                name: "PackageId",
                schema: "CMS",
                table: "UserCommissionPayouts");

            migrationBuilder.DropColumn(
                name: "ActivationFee",
                schema: "CMS",
                table: "Packages");

            migrationBuilder.DropColumn(
                name: "DiscountMultiplier",
                schema: "CMS",
                table: "Packages");

            migrationBuilder.DropColumn(
                name: "IsActive",
                schema: "CMS",
                table: "Packages");

            migrationBuilder.DropColumn(
                name: "IsBasePackage",
                schema: "CMS",
                table: "Packages");

            migrationBuilder.DropColumn(
                name: "MagicWalletMaxCredit",
                schema: "CMS",
                table: "Packages");

            migrationBuilder.DropColumn(
                name: "MagicWalletMaxDeposit",
                schema: "CMS",
                table: "Packages");

            migrationBuilder.DropColumn(
                name: "MagicWalletMultiplier",
                schema: "CMS",
                table: "Packages");

            migrationBuilder.DropColumn(
                name: "MaxBalancesPerLeg",
                schema: "CMS",
                table: "Packages");

            migrationBuilder.DropColumn(
                name: "MaxNetworkLevel",
                schema: "CMS",
                table: "Packages");

            migrationBuilder.DropColumn(
                name: "SortOrder",
                schema: "CMS",
                table: "Packages");

            migrationBuilder.DropColumn(
                name: "SupportsDayaPurchase",
                schema: "CMS",
                table: "Packages");

            migrationBuilder.DropColumn(
                name: "SupportsDirectPurchase",
                schema: "CMS",
                table: "Packages");

            migrationBuilder.DropColumn(
                name: "PackageId",
                schema: "CMS",
                table: "NetworkWeeklyBalances");

            migrationBuilder.DropColumn(
                name: "FirstActivationDate",
                schema: "CMS",
                table: "ClubMemberships");

            migrationBuilder.DropColumn(
                name: "FirstPackageId",
                schema: "CMS",
                table: "ClubMemberships");

            migrationBuilder.DropColumn(
                name: "LastActivationDate",
                schema: "CMS",
                table: "ClubMemberships");

            migrationBuilder.DropColumn(
                name: "LastPackageId",
                schema: "CMS",
                table: "ClubMemberships");

            migrationBuilder.DropColumn(
                name: "PackageId",
                schema: "CMS",
                table: "ClubMembershipCycles");

            migrationBuilder.CreateIndex(
                name: "IX_WeeklyCommissionPool_WeekDefinitionId",
                schema: "CMS",
                table: "WeeklyCommissionPools",
                column: "WeekDefinitionId",
                unique: true);

            migrationBuilder.CreateIndex(
                name: "IX_UserCommissionPayout_UserId_WeekDefinitionId",
                schema: "CMS",
                table: "UserCommissionPayouts",
                columns: new[] { "UserId", "WeekDefinitionId" },
                unique: true);

            migrationBuilder.CreateIndex(
                name: "IX_NetworkWeeklyBalance_UserId_WeekDefinitionId",
                schema: "CMS",
                table: "NetworkWeeklyBalances",
                columns: new[] { "UserId", "WeekDefinitionId" },
                unique: true);
        }
    }
}
