20260124113855_InitialCreate.cs

using System;
using Microsoft.EntityFrameworkCore.Migrations;
using Npgsql.EntityFrameworkCore.PostgreSQL.Metadata;
 
#nullable disable
 
namespace TirsvadCLI.Portfolio.Infrastructure.Migrations
{
    public partial class InitialCreate : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            // Use idempotent SQL for PostgreSQL to avoid errors when tables already exist (e.g. during CI or reused DB)
            migrationBuilder.Sql(@"CREATE TABLE IF NOT EXISTS ""AspNetRoles"" (
    ""Id"" uuid NOT NULL,
    ""Name"" character varying(256),
    ""NormalizedName"" character varying(256),
    ""ConcurrencyStamp"" text,
    CONSTRAINT ""PK_AspNetRoles"" PRIMARY KEY (""Id"")
);");
 
            migrationBuilder.Sql(@"CREATE TABLE IF NOT EXISTS ""AspNetUsers"" (
    ""Id"" uuid NOT NULL,
    ""UserName"" character varying(256),
    ""NormalizedUserName"" character varying(256),
    ""Email"" character varying(256),
    ""NormalizedEmail"" character varying(256),
    ""EmailConfirmed"" boolean NOT NULL,
    ""PasswordHash"" text,
    ""SecurityStamp"" text,
    ""ConcurrencyStamp"" text,
    ""PhoneNumber"" text,
    ""PhoneNumberConfirmed"" boolean NOT NULL,
    ""TwoFactorEnabled"" boolean NOT NULL,
    ""LockoutEnd"" timestamp with time zone,
    ""LockoutEnabled"" boolean NOT NULL,
    ""AccessFailedCount"" integer NOT NULL,
    CONSTRAINT ""PK_AspNetUsers"" PRIMARY KEY (""Id"")
);");
 
            migrationBuilder.Sql(@"CREATE TABLE IF NOT EXISTS ""AspNetRoleClaims"" (
    ""Id"" integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    ""RoleId"" uuid NOT NULL,
    ""ClaimType"" text,
    ""ClaimValue"" text,
    CONSTRAINT ""FK_AspNetRoleClaims_AspNetRoles_RoleId"" FOREIGN KEY (""RoleId"") REFERENCES ""AspNetRoles"" (""Id"") ON DELETE CASCADE
);");
 
            migrationBuilder.Sql(@"CREATE TABLE IF NOT EXISTS ""AspNetUserClaims"" (
    ""Id"" integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    ""UserId"" uuid NOT NULL,
    ""ClaimType"" text,
    ""ClaimValue"" text,
    CONSTRAINT ""FK_AspNetUserClaims_AspNetUsers_UserId"" FOREIGN KEY (""UserId"") REFERENCES ""AspNetUsers"" (""Id"") ON DELETE CASCADE
);");
 
            migrationBuilder.Sql(@"CREATE TABLE IF NOT EXISTS ""AspNetUserLogins"" (
    ""LoginProvider"" text NOT NULL,
    ""ProviderKey"" text NOT NULL,
    ""ProviderDisplayName"" text,
    ""UserId"" uuid NOT NULL,
    CONSTRAINT ""PK_AspNetUserLogins"" PRIMARY KEY (""LoginProvider"", ""ProviderKey""),
    CONSTRAINT ""FK_AspNetUserLogins_AspNetUsers_UserId"" FOREIGN KEY (""UserId"") REFERENCES ""AspNetUsers"" (""Id"") ON DELETE CASCADE
);");
 
            migrationBuilder.Sql(@"CREATE TABLE IF NOT EXISTS ""AspNetUserRoles"" (
    ""UserId"" uuid NOT NULL,
    ""RoleId"" uuid NOT NULL,
    CONSTRAINT ""PK_AspNetUserRoles"" PRIMARY KEY (""UserId"", ""RoleId""),
    CONSTRAINT ""FK_AspNetUserRoles_AspNetRoles_RoleId"" FOREIGN KEY (""RoleId"") REFERENCES ""AspNetRoles"" (""Id"") ON DELETE CASCADE,
    CONSTRAINT ""FK_AspNetUserRoles_AspNetUsers_UserId"" FOREIGN KEY (""UserId"") REFERENCES ""AspNetUsers"" (""Id"") ON DELETE CASCADE
);");
 
            migrationBuilder.Sql(@"CREATE TABLE IF NOT EXISTS ""AspNetUserTokens"" (
    ""UserId"" uuid NOT NULL,
    ""LoginProvider"" text NOT NULL,
    ""Name"" text NOT NULL,
    ""Value"" text,
    CONSTRAINT ""PK_AspNetUserTokens"" PRIMARY KEY (""UserId"", ""LoginProvider"", ""Name""),
    CONSTRAINT ""FK_AspNetUserTokens_AspNetUsers_UserId"" FOREIGN KEY (""UserId"") REFERENCES ""AspNetUsers"" (""Id"") ON DELETE CASCADE
);");
 
            // Seed test user if not exists
            migrationBuilder.Sql(@"INSERT INTO ""AspNetUsers"" (""Id"", ""AccessFailedCount"", ""ConcurrencyStamp"", ""Email"", ""EmailConfirmed"", ""LockoutEnabled"", ""LockoutEnd"", ""NormalizedEmail"", ""NormalizedUserName"", ""PasswordHash"", ""PhoneNumber"", ""PhoneNumberConfirmed"", ""SecurityStamp"", ""TwoFactorEnabled"", ""UserName"")
SELECT '11111111-1111-1111-1111-111111111111'::uuid, 0, '11111111-1111-1111-1111-111111111113', 'testuser@example.com', true, false, NULL, 'TESTUSER@EXAMPLE.COM', 'TESTUSER', 'AQAAAAEAACcQAAAAEJQwQwQwQwQwQwQwQwQwQwQwQwQwQwQwQwQwQwQwQwQwQwQwQwQwQwQwQwQwQwQwQw==', NULL, false, '11111111-1111-1111-1111-111111111112', false, 'testuser'
WHERE NOT EXISTS (SELECT 1 FROM ""AspNetUsers"" WHERE ""Id"" = '11111111-1111-1111-1111-111111111111'::uuid);");
 
            // Indexes (create only if not exists)
            migrationBuilder.Sql(@"CREATE INDEX IF NOT EXISTS ""IX_AspNetRoleClaims_RoleId"" ON ""AspNetRoleClaims"" (""RoleId"");");
            migrationBuilder.Sql(@"CREATE UNIQUE INDEX IF NOT EXISTS ""RoleNameIndex"" ON ""AspNetRoles"" (""NormalizedName"");");
            migrationBuilder.Sql(@"CREATE INDEX IF NOT EXISTS ""IX_AspNetUserClaims_UserId"" ON ""AspNetUserClaims"" (""UserId"");");
            migrationBuilder.Sql(@"CREATE INDEX IF NOT EXISTS ""IX_AspNetUserLogins_UserId"" ON ""AspNetUserLogins"" (""UserId"");");
            migrationBuilder.Sql(@"CREATE INDEX IF NOT EXISTS ""IX_AspNetUserRoles_RoleId"" ON ""AspNetUserRoles"" (""RoleId"");");
            migrationBuilder.Sql(@"CREATE INDEX IF NOT EXISTS ""EmailIndex"" ON ""AspNetUsers"" (""NormalizedEmail"");");
            migrationBuilder.Sql(@"CREATE UNIQUE INDEX IF NOT EXISTS ""UserNameIndex"" ON ""AspNetUsers"" (""NormalizedUserName"");");
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropTable(
                name: "AspNetRoleClaims");
 
            migrationBuilder.DropTable(
                name: "AspNetUserClaims");
 
            migrationBuilder.DropTable(
                name: "AspNetUserLogins");
 
            migrationBuilder.DropTable(
                name: "AspNetUserRoles");
 
            migrationBuilder.DropTable(
                name: "AspNetUserTokens");
 
            migrationBuilder.DropTable(
                name: "AspNetRoles");
 
            migrationBuilder.DropTable(
                name: "AspNetUsers");
        }
    }
}