434 lines
21 KiB
C#
434 lines
21 KiB
C#
using System;
|
|
using Microsoft.EntityFrameworkCore.Migrations;
|
|
|
|
#nullable disable
|
|
|
|
namespace DramaLing.Api.Migrations
|
|
{
|
|
/// <inheritdoc />
|
|
public partial class AddImageGenerationTables : Migration
|
|
{
|
|
/// <inheritdoc />
|
|
protected override void Up(MigrationBuilder migrationBuilder)
|
|
{
|
|
migrationBuilder.DropForeignKey(
|
|
name: "FK_flashcards_card_sets_card_set_id",
|
|
table: "flashcards");
|
|
|
|
migrationBuilder.RenameColumn(
|
|
name: "PhrasesDetected",
|
|
table: "SentenceAnalysisCache",
|
|
newName: "IdiomsDetected");
|
|
|
|
migrationBuilder.AddColumn<string>(
|
|
name: "english_level",
|
|
table: "user_profiles",
|
|
type: "TEXT",
|
|
maxLength: 10,
|
|
nullable: false,
|
|
defaultValue: "");
|
|
|
|
migrationBuilder.AddColumn<bool>(
|
|
name: "is_level_verified",
|
|
table: "user_profiles",
|
|
type: "INTEGER",
|
|
nullable: false,
|
|
defaultValue: false);
|
|
|
|
migrationBuilder.AddColumn<string>(
|
|
name: "level_notes",
|
|
table: "user_profiles",
|
|
type: "TEXT",
|
|
maxLength: 500,
|
|
nullable: true);
|
|
|
|
migrationBuilder.AddColumn<DateTime>(
|
|
name: "level_updated_at",
|
|
table: "user_profiles",
|
|
type: "TEXT",
|
|
nullable: false,
|
|
defaultValue: new DateTime(1, 1, 1, 0, 0, 0, 0, DateTimeKind.Unspecified));
|
|
|
|
migrationBuilder.AlterColumn<Guid>(
|
|
name: "card_set_id",
|
|
table: "flashcards",
|
|
type: "TEXT",
|
|
nullable: true,
|
|
oldClrType: typeof(Guid),
|
|
oldType: "TEXT");
|
|
|
|
migrationBuilder.CreateTable(
|
|
name: "audio_cache",
|
|
columns: table => new
|
|
{
|
|
Id = table.Column<Guid>(type: "TEXT", nullable: false),
|
|
text_hash = table.Column<string>(type: "TEXT", maxLength: 64, nullable: false),
|
|
text_content = table.Column<string>(type: "TEXT", nullable: false),
|
|
Accent = table.Column<string>(type: "TEXT", maxLength: 2, nullable: false),
|
|
voice_id = table.Column<string>(type: "TEXT", maxLength: 50, nullable: false),
|
|
audio_url = table.Column<string>(type: "TEXT", nullable: false),
|
|
file_size = table.Column<int>(type: "INTEGER", nullable: true),
|
|
duration_ms = table.Column<int>(type: "INTEGER", nullable: true),
|
|
created_at = table.Column<DateTime>(type: "TEXT", nullable: false),
|
|
last_accessed = table.Column<DateTime>(type: "TEXT", nullable: false),
|
|
access_count = table.Column<int>(type: "INTEGER", nullable: false)
|
|
},
|
|
constraints: table =>
|
|
{
|
|
table.PrimaryKey("PK_audio_cache", x => x.Id);
|
|
});
|
|
|
|
migrationBuilder.CreateTable(
|
|
name: "example_images",
|
|
columns: table => new
|
|
{
|
|
Id = table.Column<Guid>(type: "TEXT", nullable: false),
|
|
relative_path = table.Column<string>(type: "TEXT", maxLength: 500, nullable: false),
|
|
alt_text = table.Column<string>(type: "TEXT", maxLength: 200, nullable: true),
|
|
gemini_prompt = table.Column<string>(type: "TEXT", nullable: true),
|
|
gemini_description = table.Column<string>(type: "TEXT", nullable: true),
|
|
replicate_prompt = table.Column<string>(type: "TEXT", nullable: true),
|
|
replicate_model = table.Column<string>(type: "TEXT", maxLength: 100, nullable: true),
|
|
replicate_version = table.Column<string>(type: "TEXT", maxLength: 100, nullable: true),
|
|
gemini_cost = table.Column<decimal>(type: "TEXT", nullable: true),
|
|
replicate_cost = table.Column<decimal>(type: "TEXT", nullable: true),
|
|
total_generation_cost = table.Column<decimal>(type: "TEXT", nullable: true),
|
|
file_size = table.Column<int>(type: "INTEGER", nullable: true),
|
|
image_width = table.Column<int>(type: "INTEGER", nullable: true),
|
|
image_height = table.Column<int>(type: "INTEGER", nullable: true),
|
|
content_hash = table.Column<string>(type: "TEXT", maxLength: 64, nullable: true),
|
|
quality_score = table.Column<decimal>(type: "TEXT", nullable: true),
|
|
moderation_status = table.Column<string>(type: "TEXT", maxLength: 20, nullable: false),
|
|
moderation_notes = table.Column<string>(type: "TEXT", nullable: true),
|
|
access_count = table.Column<int>(type: "INTEGER", nullable: false),
|
|
created_at = table.Column<DateTime>(type: "TEXT", nullable: false),
|
|
updated_at = table.Column<DateTime>(type: "TEXT", nullable: false)
|
|
},
|
|
constraints: table =>
|
|
{
|
|
table.PrimaryKey("PK_example_images", x => x.Id);
|
|
});
|
|
|
|
migrationBuilder.CreateTable(
|
|
name: "pronunciation_assessments",
|
|
columns: table => new
|
|
{
|
|
Id = table.Column<Guid>(type: "TEXT", nullable: false),
|
|
user_id = table.Column<Guid>(type: "TEXT", nullable: false),
|
|
flashcard_id = table.Column<Guid>(type: "TEXT", nullable: true),
|
|
target_text = table.Column<string>(type: "TEXT", nullable: false),
|
|
audio_url = table.Column<string>(type: "TEXT", nullable: true),
|
|
overall_score = table.Column<int>(type: "INTEGER", nullable: false),
|
|
accuracy_score = table.Column<decimal>(type: "TEXT", nullable: false),
|
|
fluency_score = table.Column<decimal>(type: "TEXT", nullable: false),
|
|
completeness_score = table.Column<decimal>(type: "TEXT", nullable: false),
|
|
prosody_score = table.Column<decimal>(type: "TEXT", nullable: false),
|
|
phoneme_scores = table.Column<string>(type: "TEXT", nullable: true),
|
|
suggestions = table.Column<string>(type: "TEXT", nullable: true),
|
|
study_session_id = table.Column<Guid>(type: "TEXT", nullable: true),
|
|
practice_mode = table.Column<string>(type: "TEXT", maxLength: 20, nullable: false),
|
|
created_at = table.Column<DateTime>(type: "TEXT", nullable: false)
|
|
},
|
|
constraints: table =>
|
|
{
|
|
table.PrimaryKey("PK_pronunciation_assessments", x => x.Id);
|
|
table.ForeignKey(
|
|
name: "FK_pronunciation_assessments_flashcards_flashcard_id",
|
|
column: x => x.flashcard_id,
|
|
principalTable: "flashcards",
|
|
principalColumn: "Id",
|
|
onDelete: ReferentialAction.SetNull);
|
|
table.ForeignKey(
|
|
name: "FK_pronunciation_assessments_study_sessions_study_session_id",
|
|
column: x => x.study_session_id,
|
|
principalTable: "study_sessions",
|
|
principalColumn: "Id",
|
|
onDelete: ReferentialAction.SetNull);
|
|
table.ForeignKey(
|
|
name: "FK_pronunciation_assessments_user_profiles_user_id",
|
|
column: x => x.user_id,
|
|
principalTable: "user_profiles",
|
|
principalColumn: "Id",
|
|
onDelete: ReferentialAction.Cascade);
|
|
});
|
|
|
|
migrationBuilder.CreateTable(
|
|
name: "user_audio_preferences",
|
|
columns: table => new
|
|
{
|
|
UserId = table.Column<Guid>(type: "TEXT", nullable: false),
|
|
preferred_accent = table.Column<string>(type: "TEXT", maxLength: 2, nullable: false),
|
|
preferred_voice_male = table.Column<string>(type: "TEXT", maxLength: 50, nullable: true),
|
|
preferred_voice_female = table.Column<string>(type: "TEXT", maxLength: 50, nullable: true),
|
|
default_speed = table.Column<decimal>(type: "TEXT", nullable: false),
|
|
auto_play_enabled = table.Column<bool>(type: "INTEGER", nullable: false),
|
|
pronunciation_difficulty = table.Column<string>(type: "TEXT", maxLength: 20, nullable: false),
|
|
target_score_threshold = table.Column<int>(type: "INTEGER", nullable: false),
|
|
enable_detailed_feedback = table.Column<bool>(type: "INTEGER", nullable: false),
|
|
updated_at = table.Column<DateTime>(type: "TEXT", nullable: false)
|
|
},
|
|
constraints: table =>
|
|
{
|
|
table.PrimaryKey("PK_user_audio_preferences", x => x.UserId);
|
|
table.ForeignKey(
|
|
name: "FK_user_audio_preferences_user_profiles_UserId",
|
|
column: x => x.UserId,
|
|
principalTable: "user_profiles",
|
|
principalColumn: "Id",
|
|
onDelete: ReferentialAction.Cascade);
|
|
});
|
|
|
|
migrationBuilder.CreateTable(
|
|
name: "WordQueryUsageStats",
|
|
columns: table => new
|
|
{
|
|
Id = table.Column<Guid>(type: "TEXT", nullable: false),
|
|
UserId = table.Column<Guid>(type: "TEXT", nullable: false),
|
|
Date = table.Column<DateOnly>(type: "TEXT", nullable: false),
|
|
SentenceAnalysisCount = table.Column<int>(type: "INTEGER", nullable: false),
|
|
HighValueWordClicks = table.Column<int>(type: "INTEGER", nullable: false),
|
|
LowValueWordClicks = table.Column<int>(type: "INTEGER", nullable: false),
|
|
TotalApiCalls = table.Column<int>(type: "INTEGER", nullable: false),
|
|
UniqueWordsQueried = table.Column<int>(type: "INTEGER", nullable: false),
|
|
CreatedAt = table.Column<DateTime>(type: "TEXT", nullable: false),
|
|
UpdatedAt = table.Column<DateTime>(type: "TEXT", nullable: false)
|
|
},
|
|
constraints: table =>
|
|
{
|
|
table.PrimaryKey("PK_WordQueryUsageStats", x => x.Id);
|
|
table.ForeignKey(
|
|
name: "FK_WordQueryUsageStats_user_profiles_UserId",
|
|
column: x => x.UserId,
|
|
principalTable: "user_profiles",
|
|
principalColumn: "Id",
|
|
onDelete: ReferentialAction.Cascade);
|
|
});
|
|
|
|
migrationBuilder.CreateTable(
|
|
name: "flashcard_example_images",
|
|
columns: table => new
|
|
{
|
|
flashcard_id = table.Column<Guid>(type: "TEXT", nullable: false),
|
|
example_image_id = table.Column<Guid>(type: "TEXT", nullable: false),
|
|
display_order = table.Column<int>(type: "INTEGER", nullable: false),
|
|
is_primary = table.Column<bool>(type: "INTEGER", nullable: false),
|
|
context_relevance = table.Column<decimal>(type: "TEXT", nullable: true),
|
|
created_at = table.Column<DateTime>(type: "TEXT", nullable: false)
|
|
},
|
|
constraints: table =>
|
|
{
|
|
table.PrimaryKey("PK_flashcard_example_images", x => new { x.flashcard_id, x.example_image_id });
|
|
table.ForeignKey(
|
|
name: "FK_flashcard_example_images_example_images_example_image_id",
|
|
column: x => x.example_image_id,
|
|
principalTable: "example_images",
|
|
principalColumn: "Id",
|
|
onDelete: ReferentialAction.Cascade);
|
|
table.ForeignKey(
|
|
name: "FK_flashcard_example_images_flashcards_flashcard_id",
|
|
column: x => x.flashcard_id,
|
|
principalTable: "flashcards",
|
|
principalColumn: "Id",
|
|
onDelete: ReferentialAction.Cascade);
|
|
});
|
|
|
|
migrationBuilder.CreateTable(
|
|
name: "image_generation_requests",
|
|
columns: table => new
|
|
{
|
|
Id = table.Column<Guid>(type: "TEXT", nullable: false),
|
|
user_id = table.Column<Guid>(type: "TEXT", nullable: false),
|
|
flashcard_id = table.Column<Guid>(type: "TEXT", nullable: false),
|
|
overall_status = table.Column<string>(type: "TEXT", maxLength: 20, nullable: false),
|
|
gemini_status = table.Column<string>(type: "TEXT", maxLength: 20, nullable: false),
|
|
replicate_status = table.Column<string>(type: "TEXT", maxLength: 20, nullable: false),
|
|
original_request = table.Column<string>(type: "TEXT", nullable: false),
|
|
gemini_prompt = table.Column<string>(type: "TEXT", nullable: true),
|
|
generated_description = table.Column<string>(type: "TEXT", nullable: true),
|
|
final_replicate_prompt = table.Column<string>(type: "TEXT", nullable: true),
|
|
generated_image_id = table.Column<Guid>(type: "TEXT", nullable: true),
|
|
gemini_error_message = table.Column<string>(type: "TEXT", nullable: true),
|
|
replicate_error_message = table.Column<string>(type: "TEXT", nullable: true),
|
|
gemini_processing_time_ms = table.Column<int>(type: "INTEGER", nullable: true),
|
|
replicate_processing_time_ms = table.Column<int>(type: "INTEGER", nullable: true),
|
|
total_processing_time_ms = table.Column<int>(type: "INTEGER", nullable: true),
|
|
gemini_cost = table.Column<decimal>(type: "TEXT", nullable: true),
|
|
replicate_cost = table.Column<decimal>(type: "TEXT", nullable: true),
|
|
total_cost = table.Column<decimal>(type: "TEXT", nullable: true),
|
|
created_at = table.Column<DateTime>(type: "TEXT", nullable: false),
|
|
gemini_started_at = table.Column<DateTime>(type: "TEXT", nullable: true),
|
|
gemini_completed_at = table.Column<DateTime>(type: "TEXT", nullable: true),
|
|
replicate_started_at = table.Column<DateTime>(type: "TEXT", nullable: true),
|
|
replicate_completed_at = table.Column<DateTime>(type: "TEXT", nullable: true),
|
|
completed_at = table.Column<DateTime>(type: "TEXT", nullable: true)
|
|
},
|
|
constraints: table =>
|
|
{
|
|
table.PrimaryKey("PK_image_generation_requests", x => x.Id);
|
|
table.ForeignKey(
|
|
name: "FK_image_generation_requests_example_images_generated_image_id",
|
|
column: x => x.generated_image_id,
|
|
principalTable: "example_images",
|
|
principalColumn: "Id",
|
|
onDelete: ReferentialAction.SetNull);
|
|
table.ForeignKey(
|
|
name: "FK_image_generation_requests_flashcards_flashcard_id",
|
|
column: x => x.flashcard_id,
|
|
principalTable: "flashcards",
|
|
principalColumn: "Id",
|
|
onDelete: ReferentialAction.Cascade);
|
|
table.ForeignKey(
|
|
name: "FK_image_generation_requests_user_profiles_user_id",
|
|
column: x => x.user_id,
|
|
principalTable: "user_profiles",
|
|
principalColumn: "Id",
|
|
onDelete: ReferentialAction.Cascade);
|
|
});
|
|
|
|
migrationBuilder.CreateIndex(
|
|
name: "IX_AudioCache_LastAccessed",
|
|
table: "audio_cache",
|
|
column: "last_accessed");
|
|
|
|
migrationBuilder.CreateIndex(
|
|
name: "IX_AudioCache_TextHash",
|
|
table: "audio_cache",
|
|
column: "text_hash",
|
|
unique: true);
|
|
|
|
migrationBuilder.CreateIndex(
|
|
name: "IX_example_images_access_count",
|
|
table: "example_images",
|
|
column: "access_count");
|
|
|
|
migrationBuilder.CreateIndex(
|
|
name: "IX_example_images_content_hash",
|
|
table: "example_images",
|
|
column: "content_hash",
|
|
unique: true);
|
|
|
|
migrationBuilder.CreateIndex(
|
|
name: "IX_flashcard_example_images_example_image_id",
|
|
table: "flashcard_example_images",
|
|
column: "example_image_id");
|
|
|
|
migrationBuilder.CreateIndex(
|
|
name: "IX_image_generation_requests_flashcard_id",
|
|
table: "image_generation_requests",
|
|
column: "flashcard_id");
|
|
|
|
migrationBuilder.CreateIndex(
|
|
name: "IX_image_generation_requests_generated_image_id",
|
|
table: "image_generation_requests",
|
|
column: "generated_image_id");
|
|
|
|
migrationBuilder.CreateIndex(
|
|
name: "IX_image_generation_requests_user_id",
|
|
table: "image_generation_requests",
|
|
column: "user_id");
|
|
|
|
migrationBuilder.CreateIndex(
|
|
name: "IX_pronunciation_assessments_flashcard_id",
|
|
table: "pronunciation_assessments",
|
|
column: "flashcard_id");
|
|
|
|
migrationBuilder.CreateIndex(
|
|
name: "IX_PronunciationAssessment_Session",
|
|
table: "pronunciation_assessments",
|
|
column: "study_session_id");
|
|
|
|
migrationBuilder.CreateIndex(
|
|
name: "IX_PronunciationAssessment_UserFlashcard",
|
|
table: "pronunciation_assessments",
|
|
columns: new[] { "user_id", "flashcard_id" });
|
|
|
|
migrationBuilder.CreateIndex(
|
|
name: "IX_WordQueryUsageStats_CreatedAt",
|
|
table: "WordQueryUsageStats",
|
|
column: "CreatedAt");
|
|
|
|
migrationBuilder.CreateIndex(
|
|
name: "IX_WordQueryUsageStats_UserDate",
|
|
table: "WordQueryUsageStats",
|
|
columns: new[] { "UserId", "Date" },
|
|
unique: true);
|
|
|
|
migrationBuilder.AddForeignKey(
|
|
name: "FK_flashcards_card_sets_card_set_id",
|
|
table: "flashcards",
|
|
column: "card_set_id",
|
|
principalTable: "card_sets",
|
|
principalColumn: "Id",
|
|
onDelete: ReferentialAction.SetNull);
|
|
}
|
|
|
|
/// <inheritdoc />
|
|
protected override void Down(MigrationBuilder migrationBuilder)
|
|
{
|
|
migrationBuilder.DropForeignKey(
|
|
name: "FK_flashcards_card_sets_card_set_id",
|
|
table: "flashcards");
|
|
|
|
migrationBuilder.DropTable(
|
|
name: "audio_cache");
|
|
|
|
migrationBuilder.DropTable(
|
|
name: "flashcard_example_images");
|
|
|
|
migrationBuilder.DropTable(
|
|
name: "image_generation_requests");
|
|
|
|
migrationBuilder.DropTable(
|
|
name: "pronunciation_assessments");
|
|
|
|
migrationBuilder.DropTable(
|
|
name: "user_audio_preferences");
|
|
|
|
migrationBuilder.DropTable(
|
|
name: "WordQueryUsageStats");
|
|
|
|
migrationBuilder.DropTable(
|
|
name: "example_images");
|
|
|
|
migrationBuilder.DropColumn(
|
|
name: "english_level",
|
|
table: "user_profiles");
|
|
|
|
migrationBuilder.DropColumn(
|
|
name: "is_level_verified",
|
|
table: "user_profiles");
|
|
|
|
migrationBuilder.DropColumn(
|
|
name: "level_notes",
|
|
table: "user_profiles");
|
|
|
|
migrationBuilder.DropColumn(
|
|
name: "level_updated_at",
|
|
table: "user_profiles");
|
|
|
|
migrationBuilder.RenameColumn(
|
|
name: "IdiomsDetected",
|
|
table: "SentenceAnalysisCache",
|
|
newName: "PhrasesDetected");
|
|
|
|
migrationBuilder.AlterColumn<Guid>(
|
|
name: "card_set_id",
|
|
table: "flashcards",
|
|
type: "TEXT",
|
|
nullable: false,
|
|
defaultValue: new Guid("00000000-0000-0000-0000-000000000000"),
|
|
oldClrType: typeof(Guid),
|
|
oldType: "TEXT",
|
|
oldNullable: true);
|
|
|
|
migrationBuilder.AddForeignKey(
|
|
name: "FK_flashcards_card_sets_card_set_id",
|
|
table: "flashcards",
|
|
column: "card_set_id",
|
|
principalTable: "card_sets",
|
|
principalColumn: "Id",
|
|
onDelete: ReferentialAction.Cascade);
|
|
}
|
|
}
|
|
}
|