migration_verify.sql

CREATE EXTENSION IF NOT EXISTS postgis;
CREATE TABLE IF NOT EXISTS "__EFMigrationsHistory" (
    "MigrationId" character varying(150) NOT NULL,
    "ProductVersion" character varying(32) NOT NULL,
    CONSTRAINT "PK___EFMigrationsHistory" PRIMARY KEY ("MigrationId")
);

START TRANSACTION;

DO $EF$
BEGIN
    IF NOT EXISTS(SELECT 1 FROM pg_namespace WHERE nspname = 'AspNetIdentity') THEN
        CREATE SCHEMA "AspNetIdentity";
    END IF;
END $EF$;

DO $EF$
BEGIN
    IF NOT EXISTS(SELECT 1 FROM pg_namespace WHERE nspname = 'SnakeAid') THEN
        CREATE SCHEMA "SnakeAid";
    END IF;
END $EF$;

DO $EF$
BEGIN
    IF NOT EXISTS(SELECT 1 FROM pg_namespace WHERE nspname = 'SnakeAid') THEN
        CREATE SCHEMA "SnakeAid";
    END IF;
END $EF$;

CREATE EXTENSION IF NOT EXISTS postgis;

CREATE TABLE "AspNetIdentity"."Accounts" (
    "Id" uuid NOT NULL,
    "FullName" character varying(200) NOT NULL,
    "AvatarUrl" character varying(1000),
    "Role" integer NOT NULL,
    "CreatedAt" timestamp with time zone NOT NULL,
    "UpdatedAt" timestamp with time zone NOT NULL,
    "IsActive" boolean NOT NULL,
    "ReputationPoints" integer NOT NULL,
    "ReputationStatus" integer NOT NULL,
    "SuspendedUntil" timestamp with time zone,
    "SuspensionReason" text,
    "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_Accounts" PRIMARY KEY ("Id")
);

CREATE TABLE "SnakeAid"."AIModels" (
    "Id" integer GENERATED BY DEFAULT AS IDENTITY,
    "Version" character varying(100) NOT NULL,
    "Description" character varying(1000),
    "IsActive" boolean NOT NULL,
    "IsDefault" boolean NOT NULL,
    "DeployedAt" timestamp with time zone,
    "RetiredAt" timestamp with time zone,
    "CreatedAt" timestamp with time zone NOT NULL,
    "UpdatedAt" timestamp with time zone NOT NULL,
    CONSTRAINT "PK_AIModels" PRIMARY KEY ("Id")
);

CREATE TABLE "SnakeAid"."CatchingEnvironments" (
    "Id" integer GENERATED BY DEFAULT AS IDENTITY,
    "Name" text NOT NULL,
    "Description" text NOT NULL,
    "Price" numeric(18,2) NOT NULL,
    "Currency" character varying(5) NOT NULL,
    "CreatedAt" timestamp with time zone NOT NULL,
    "UpdatedAt" timestamp with time zone NOT NULL,
    CONSTRAINT "PK_CatchingEnvironments" PRIMARY KEY ("Id")
);

CREATE TABLE "SnakeAid"."ExpertCertificates" (
    "Id" uuid NOT NULL,
    "ExpertId" uuid NOT NULL,
    "CertificateName" character varying(250) NOT NULL,
    "IssuingOrganization" character varying(250) NOT NULL,
    "IssueDate" timestamp with time zone NOT NULL,
    "ExpiryDate" timestamp with time zone,
    "CertificateUrl" text NOT NULL,
    "VerificationStatus" integer NOT NULL,
    "RejectionReason" text NOT NULL,
    "CreatedAt" timestamp with time zone NOT NULL,
    "UpdatedAt" timestamp with time zone NOT NULL,
    CONSTRAINT "PK_ExpertCertificates" PRIMARY KEY ("Id")
);

CREATE TABLE "SnakeAid"."FilterQuestions" (
    "Id" integer GENERATED BY DEFAULT AS IDENTITY,
    "Question" text NOT NULL,
    "IsActive" boolean NOT NULL,
    "CreatedAt" timestamp with time zone NOT NULL,
    "UpdatedAt" timestamp with time zone NOT NULL,
    CONSTRAINT "PK_FilterQuestions" PRIMARY KEY ("Id")
);

CREATE TABLE "SnakeAid"."FirstAidGuidelines" (
    "Id" integer GENERATED BY DEFAULT AS IDENTITY,
    "Name" character varying(255) NOT NULL,
    "Content" jsonb NOT NULL,
    "Type" integer NOT NULL,
    "Summary" character varying(500),
    "CreatedAt" timestamp with time zone NOT NULL,
    "UpdatedAt" timestamp with time zone NOT NULL,
    CONSTRAINT "PK_FirstAidGuidelines" PRIMARY KEY ("Id")
);

CREATE TABLE "SnakeAid"."Lessons" (
    "Id" uuid NOT NULL,
    "Title" text NOT NULL,
    "Content" text NOT NULL,
    "CreatedAt" timestamp with time zone NOT NULL,
    "UpdatedAt" timestamp with time zone NOT NULL,
    CONSTRAINT "PK_Lessons" PRIMARY KEY ("Id")
);

CREATE TABLE "SnakeAid"."LocationEvents" (
    "Id" bigint GENERATED BY DEFAULT AS IDENTITY,
    "SessionId" uuid NOT NULL,
    "SessionType" integer NOT NULL,
    "AccountId" uuid NOT NULL,
    "Role" integer NOT NULL,
    "Location" geometry NOT NULL,
    "RecordedAt" timestamp with time zone NOT NULL,
    "Speed" real,
    "Heading" real,
    CONSTRAINT "PK_LocationEvents" PRIMARY KEY ("Id")
);

CREATE TABLE "SnakeAid"."PaymentCards" (
    "Id" uuid NOT NULL,
    "CardNumber" text NOT NULL,
    "CardHolderName" text NOT NULL,
    "ExpiryDate" timestamp with time zone NOT NULL,
    "Cvv" text NOT NULL,
    "IsDefault" boolean NOT NULL,
    "CreatedAt" timestamp with time zone NOT NULL,
    "UpdatedAt" timestamp with time zone NOT NULL,
    CONSTRAINT "PK_PaymentCards" PRIMARY KEY ("Id")
);

CREATE TABLE "SnakeAid"."ReputationRules" (
    "Id" integer GENERATED BY DEFAULT AS IDENTITY,
    "Name" text NOT NULL,
    "Description" text NOT NULL,
    "Points" integer NOT NULL,
    "CreatedAt" timestamp with time zone NOT NULL,
    "UpdatedAt" timestamp with time zone NOT NULL,
    CONSTRAINT "PK_ReputationRules" PRIMARY KEY ("Id")
);

CREATE TABLE "SnakeAid"."SnakeSpecies" (
    "Id" integer GENERATED BY DEFAULT AS IDENTITY,
    "ScientificName" character varying(500) NOT NULL,
    "Slug" character varying(200) NOT NULL,
    "CommonName" character varying(500) NOT NULL,
    "ImageUrl" character varying(2000) NOT NULL,
    "Description" character varying(2000) NOT NULL,
    "IdentificationSummary" character varying(2000) NOT NULL,
    "PrimaryVenomType" integer,
    "Identification" jsonb,
    "SymptomsByTime" jsonb,
    "FirstAidGuidelineOverride" jsonb,
    "RiskLevel" real NOT NULL,
    "IsVenomous" boolean NOT NULL,
    "IsActive" boolean NOT NULL,
    "CreatedAt" timestamp with time zone NOT NULL,
    "UpdatedAt" timestamp with time zone NOT NULL,
    CONSTRAINT "PK_SnakeSpecies" PRIMARY KEY ("Id")
);

CREATE TABLE "SnakeAid"."Specializations" (
    "Id" integer GENERATED BY DEFAULT AS IDENTITY,
    "Name" character varying(100) NOT NULL,
    "CreatedAt" timestamp with time zone NOT NULL,
    "UpdatedAt" timestamp with time zone NOT NULL,
    CONSTRAINT "PK_Specializations" PRIMARY KEY ("Id")
);

CREATE TABLE "SnakeAid"."SystemSettings" (
    "SettingKey" character varying(100) NOT NULL,
    "Value" character varying(2000) NOT NULL,
    "Description" character varying(500),
    "ValueType" integer NOT NULL,
    "CreatedAt" timestamp with time zone NOT NULL,
    "UpdatedAt" timestamp with time zone NOT NULL,
    CONSTRAINT "PK_SystemSettings" PRIMARY KEY ("SettingKey")
);

CREATE TABLE "SnakeAid"."TrackingSessions" (
    "Id" uuid NOT NULL,
    "SessionId" uuid NOT NULL,
    "SessionType" integer NOT NULL,
    "IsActive" boolean NOT NULL,
    "MemberLocation" geometry(Point, 4326),
    "RescuerLocation" geometry(Point, 4326),
    "MemberLastUpdate" timestamp with time zone,
    "RescuerLastUpdate" timestamp with time zone,
    "DistanceMeters" double precision,
    "EtaMinutes" integer,
    "CreatedAt" timestamp with time zone NOT NULL,
    "UpdatedAt" timestamp with time zone NOT NULL,
    CONSTRAINT "PK_TrackingSessions" PRIMARY KEY ("Id")
);

CREATE TABLE "SnakeAid"."TreatmentFacilities" (
    "Id" integer GENERATED BY DEFAULT AS IDENTITY,
    "Name" character varying(200) NOT NULL,
    "Address" character varying(500) NOT NULL,
    "ContactNumber" character varying(20) NOT NULL,
    "Location" geometry(Point, 4326) NOT NULL,
    "IsActive" boolean NOT NULL,
    "CreatedAt" timestamp with time zone NOT NULL,
    "UpdatedAt" timestamp with time zone NOT NULL,
    CONSTRAINT "PK_TreatmentFacilities" PRIMARY KEY ("Id")
);

CREATE TABLE "SnakeAid"."AppNotifications" (
    "Id" uuid NOT NULL,
    "UserId" uuid NOT NULL,
    "Title" text NOT NULL,
    "Message" text NOT NULL,
    "IsRead" boolean NOT NULL,
    "CreatedAt" timestamp with time zone NOT NULL,
    "UpdatedAt" timestamp with time zone NOT NULL,
    CONSTRAINT "PK_AppNotifications" PRIMARY KEY ("Id"),
    CONSTRAINT "FK_AppNotifications_Accounts_UserId" FOREIGN KEY ("UserId") REFERENCES "AspNetIdentity"."Accounts" ("Id") ON DELETE CASCADE
);

CREATE TABLE "SnakeAid"."Blogs" (
    "Id" uuid NOT NULL,
    "AuthorId" uuid NOT NULL,
    "Title" character varying(500) NOT NULL,
    "Content" text NOT NULL,
    "Status" integer NOT NULL,
    "RejectionReason" text,
    "CreatedAt" timestamp with time zone NOT NULL,
    "UpdatedAt" timestamp with time zone NOT NULL,
    CONSTRAINT "PK_Blogs" PRIMARY KEY ("Id"),
    CONSTRAINT "FK_Blogs_Accounts_AuthorId" FOREIGN KEY ("AuthorId") REFERENCES "AspNetIdentity"."Accounts" ("Id") ON DELETE RESTRICT
);

CREATE TABLE "SnakeAid"."CommunityReports" (
    "Id" uuid NOT NULL,
    "UserId" uuid NOT NULL,
    "LocationCoordinates" geometry NOT NULL,
    "AdditionalDetails" text NOT NULL,
    "CreatedAt" timestamp with time zone NOT NULL,
    "UpdatedAt" timestamp with time zone NOT NULL,
    CONSTRAINT "PK_CommunityReports" PRIMARY KEY ("Id"),
    CONSTRAINT "FK_CommunityReports_Accounts_UserId" FOREIGN KEY ("UserId") REFERENCES "AspNetIdentity"."Accounts" ("Id") ON DELETE RESTRICT
);

CREATE TABLE "SnakeAid"."ExpertProfiles" (
    "AccountId" uuid NOT NULL,
    "Biography" character varying(2000) NOT NULL,
    "IsOnline" boolean NOT NULL,
    "ConsultationFee" numeric(18,2) NOT NULL,
    "Rating" numeric(3,2) NOT NULL,
    "RatingCount" integer NOT NULL,
    "CreatedAt" timestamp with time zone NOT NULL,
    "UpdatedAt" timestamp with time zone NOT NULL,
    CONSTRAINT "PK_ExpertProfiles" PRIMARY KEY ("AccountId"),
    CONSTRAINT "FK_ExpertProfiles_Accounts_AccountId" FOREIGN KEY ("AccountId") REFERENCES "AspNetIdentity"."Accounts" ("Id") ON DELETE CASCADE
);

CREATE TABLE "SnakeAid"."ExpertTimeSlots" (
    "Id" uuid NOT NULL,
    "ExpertId" uuid NOT NULL,
    "StartTime" timestamp with time zone NOT NULL,
    "EndTime" timestamp with time zone NOT NULL,
    "Status" integer NOT NULL,
    CONSTRAINT "PK_ExpertTimeSlots" PRIMARY KEY ("Id"),
    CONSTRAINT "FK_ExpertTimeSlots_Accounts_ExpertId" FOREIGN KEY ("ExpertId") REFERENCES "AspNetIdentity"."Accounts" ("Id") ON DELETE CASCADE
);

CREATE TABLE "SnakeAid"."MemberProfiles" (
    "AccountId" uuid NOT NULL,
    "Rating" real NOT NULL,
    "RatingCount" integer NOT NULL,
    "EmergencyContacts" jsonb NOT NULL,
    "HasUnderlyingDisease" boolean NOT NULL DEFAULT FALSE,
    "CreatedAt" timestamp with time zone NOT NULL,
    "UpdatedAt" timestamp with time zone NOT NULL,
    CONSTRAINT "PK_MemberProfiles" PRIMARY KEY ("AccountId"),
    CONSTRAINT "FK_MemberProfiles_Accounts_AccountId" FOREIGN KEY ("AccountId") REFERENCES "AspNetIdentity"."Accounts" ("Id") ON DELETE CASCADE
);

CREATE TABLE "SnakeAid"."Otp" (
    "Id" uuid NOT NULL,
    "Email" character varying(150) NOT NULL,
    "UserId" uuid,
    "OtpCode" character varying(10) NOT NULL,
    "ExpirationTime" timestamp with time zone NOT NULL,
    "AttemptLeft" integer NOT NULL,
    CONSTRAINT "PK_Otp" PRIMARY KEY ("Id"),
    CONSTRAINT "FK_Otp_Accounts_UserId" FOREIGN KEY ("UserId") REFERENCES "AspNetIdentity"."Accounts" ("Id")
);

CREATE TABLE "SnakeAid"."ReputationRawEvents" (
    "Id" uuid NOT NULL,
    "UserId" uuid NOT NULL,
    "EventType" character varying(100) NOT NULL,
    "ReferenceId" uuid NOT NULL,
    "ReferenceType" text NOT NULL,
    "PointsChange" integer NOT NULL,
    "IsProcessed" boolean NOT NULL,
    "ProcessedAt" timestamp with time zone,
    "ProcessingError" text,
    "Notes" text,
    "CreatedAt" timestamp with time zone NOT NULL,
    "UpdatedAt" timestamp with time zone NOT NULL,
    CONSTRAINT "PK_ReputationRawEvents" PRIMARY KEY ("Id"),
    CONSTRAINT "FK_ReputationRawEvents_Accounts_UserId" FOREIGN KEY ("UserId") REFERENCES "AspNetIdentity"."Accounts" ("Id") ON DELETE RESTRICT
);

CREATE TABLE "SnakeAid"."RescuerProfiles" (
    "AccountId" uuid NOT NULL,
    "IsOnline" boolean NOT NULL,
    "Rating" numeric(3,2) NOT NULL,
    "RatingCount" integer NOT NULL,
    "Type" integer NOT NULL DEFAULT 0,
    "LastLocation" geometry(Point, 4326),
    "LastLocationUpdate" timestamp with time zone,
    "TotalMissions" integer NOT NULL,
    "CompletedMissions" integer NOT NULL,
    "CreatedAt" timestamp with time zone NOT NULL,
    "UpdatedAt" timestamp with time zone NOT NULL,
    CONSTRAINT "PK_RescuerProfiles" PRIMARY KEY ("AccountId"),
    CONSTRAINT "FK_RescuerProfiles_Accounts_AccountId" FOREIGN KEY ("AccountId") REFERENCES "AspNetIdentity"."Accounts" ("Id") ON DELETE CASCADE
);

CREATE TABLE "SnakeAid"."Transactions" (
    "Id" uuid NOT NULL,
    "UserId" uuid NOT NULL,
    "ReferenceId" uuid NOT NULL,
    "Amount" numeric(18,2) NOT NULL,
    "Currency" character varying(3) NOT NULL,
    "TransactionType" integer NOT NULL,
    "Description" character varying(500),
    "PaymentMethod" character varying(100),
    "ExternalTransactionId" character varying(200),
    "CreatedAt" timestamp with time zone,
    CONSTRAINT "PK_Transactions" PRIMARY KEY ("Id"),
    CONSTRAINT "FK_Transactions_Accounts_UserId" FOREIGN KEY ("UserId") REFERENCES "AspNetIdentity"."Accounts" ("Id") ON DELETE RESTRICT
);

CREATE TABLE "AspNetIdentity"."UserClaims" (
    "Id" integer GENERATED BY DEFAULT AS IDENTITY,
    "UserId" uuid NOT NULL,
    "ClaimType" text,
    "ClaimValue" text,
    CONSTRAINT "PK_UserClaims" PRIMARY KEY ("Id"),
    CONSTRAINT "FK_UserClaims_Accounts_UserId" FOREIGN KEY ("UserId") REFERENCES "AspNetIdentity"."Accounts" ("Id") ON DELETE CASCADE
);

CREATE TABLE "SnakeAid"."UserFeedbacks" (
    "Id" uuid NOT NULL,
    "RaterId" uuid NOT NULL,
    "TargetUserId" uuid NOT NULL,
    "ReferenceId" uuid NOT NULL,
    "Type" integer NOT NULL,
    "Rating" integer NOT NULL,
    "Comments" character varying(2000),
    "CreatedAt" timestamp with time zone NOT NULL,
    "UpdatedAt" timestamp with time zone NOT NULL,
    CONSTRAINT "PK_UserFeedbacks" PRIMARY KEY ("Id"),
    CONSTRAINT "FK_UserFeedbacks_Accounts_RaterId" FOREIGN KEY ("RaterId") REFERENCES "AspNetIdentity"."Accounts" ("Id") ON DELETE RESTRICT,
    CONSTRAINT "FK_UserFeedbacks_Accounts_TargetUserId" FOREIGN KEY ("TargetUserId") REFERENCES "AspNetIdentity"."Accounts" ("Id") ON DELETE RESTRICT
);

CREATE TABLE "AspNetIdentity"."UserLogins" (
    "LoginProvider" text NOT NULL,
    "ProviderKey" text NOT NULL,
    "ProviderDisplayName" text,
    "UserId" uuid NOT NULL,
    CONSTRAINT "PK_UserLogins" PRIMARY KEY ("LoginProvider", "ProviderKey"),
    CONSTRAINT "FK_UserLogins_Accounts_UserId" FOREIGN KEY ("UserId") REFERENCES "AspNetIdentity"."Accounts" ("Id") ON DELETE CASCADE
);

CREATE TABLE "AspNetIdentity"."UserTokens" (
    "UserId" uuid NOT NULL,
    "LoginProvider" text NOT NULL,
    "Name" text NOT NULL,
    "Value" text,
    CONSTRAINT "PK_UserTokens" PRIMARY KEY ("UserId", "LoginProvider", "Name"),
    CONSTRAINT "FK_UserTokens_Accounts_UserId" FOREIGN KEY ("UserId") REFERENCES "AspNetIdentity"."Accounts" ("Id") ON DELETE CASCADE
);

CREATE TABLE "SnakeAid"."Wallets" (
    "Id" uuid NOT NULL,
    "UserId" uuid NOT NULL,
    "Balance" numeric(18,2) NOT NULL,
    "CreatedAt" timestamp with time zone NOT NULL,
    "UpdatedAt" timestamp with time zone NOT NULL,
    CONSTRAINT "PK_Wallets" PRIMARY KEY ("Id"),
    CONSTRAINT "FK_Wallets_Accounts_UserId" FOREIGN KEY ("UserId") REFERENCES "AspNetIdentity"."Accounts" ("Id") ON DELETE CASCADE
);

CREATE TABLE "SnakeAid"."FilterOptions" (
    "Id" integer GENERATED BY DEFAULT AS IDENTITY,
    "OptionText" character varying(500) NOT NULL,
    "OptionImageUrl" character varying(1000) NOT NULL,
    "QuestionId" integer NOT NULL,
    "IsActive" boolean NOT NULL,
    "CreatedAt" timestamp with time zone NOT NULL,
    "UpdatedAt" timestamp with time zone NOT NULL,
    CONSTRAINT "PK_FilterOptions" PRIMARY KEY ("Id"),
    CONSTRAINT "FK_FilterOptions_FilterQuestions_QuestionId" FOREIGN KEY ("QuestionId") REFERENCES "SnakeAid"."FilterQuestions" ("Id") ON DELETE CASCADE
);

CREATE TABLE "SnakeAid"."VenomTypes" (
    "Id" integer GENERATED BY DEFAULT AS IDENTITY,
    "Name" character varying(100) NOT NULL,
    "ScientificName" character varying(200),
    "Description" character varying(1000) NOT NULL,
    "IsActive" boolean NOT NULL,
    "SeverityIndex" integer,
    "FirstAidGuidelineId" integer NOT NULL,
    "CreatedAt" timestamp with time zone NOT NULL,
    "UpdatedAt" timestamp with time zone NOT NULL,
    CONSTRAINT "PK_VenomTypes" PRIMARY KEY ("Id"),
    CONSTRAINT "FK_VenomTypes_FirstAidGuidelines_FirstAidGuidelineId" FOREIGN KEY ("FirstAidGuidelineId") REFERENCES "SnakeAid"."FirstAidGuidelines" ("Id") ON DELETE RESTRICT
);

CREATE TABLE "SnakeAid"."AISnakeClassMappings" (
    "Id" uuid NOT NULL,
    "AIModelId" integer NOT NULL,
    "SnakeSpeciesId" integer NOT NULL,
    "YoloClassName" text NOT NULL,
    "YoloClassId" integer NOT NULL,
    "IsActive" boolean NOT NULL,
    CONSTRAINT "PK_AISnakeClassMappings" PRIMARY KEY ("Id"),
    CONSTRAINT "FK_AISnakeClassMappings_AIModels_AIModelId" FOREIGN KEY ("AIModelId") REFERENCES "SnakeAid"."AIModels" ("Id") ON DELETE CASCADE,
    CONSTRAINT "FK_AISnakeClassMappings_SnakeSpecies_SnakeSpeciesId" FOREIGN KEY ("SnakeSpeciesId") REFERENCES "SnakeAid"."SnakeSpecies" ("Id") ON DELETE CASCADE
);

CREATE TABLE "SnakeAid"."LibraryMedias" (
    "Id" uuid NOT NULL,
    "MediaUrl" character varying(2000) NOT NULL,
    "MediaType" integer NOT NULL,
    "FileName" character varying(200),
    "FileSizeBytes" bigint,
    "ContentType" character varying(50),
    "IsActive" boolean NOT NULL,
    "IsPublic" boolean NOT NULL,
    "UploadedById" uuid,
    "UploadedAt" timestamp with time zone,
    "SnakeSpeciesId" integer,
    "CreatedAt" timestamp with time zone NOT NULL,
    "UpdatedAt" timestamp with time zone NOT NULL,
    CONSTRAINT "PK_LibraryMedias" PRIMARY KEY ("Id"),
    CONSTRAINT "FK_LibraryMedias_Accounts_UploadedById" FOREIGN KEY ("UploadedById") REFERENCES "AspNetIdentity"."Accounts" ("Id") ON DELETE SET NULL,
    CONSTRAINT "FK_LibraryMedias_SnakeSpecies_SnakeSpeciesId" FOREIGN KEY ("SnakeSpeciesId") REFERENCES "SnakeAid"."SnakeSpecies" ("Id") ON DELETE CASCADE
);

CREATE TABLE "SnakeAid"."SnakeCatchingTariffs" (
    "Id" uuid NOT NULL,
    "SnakeSpeciesId" integer NOT NULL,
    "SizeCategory" integer NOT NULL,
    "BasePrice" numeric(18,2) NOT NULL,
    "Currency" text NOT NULL,
    "IsActive" boolean NOT NULL,
    "Notes" character varying(1000),
    "CreatedAt" timestamp with time zone NOT NULL,
    "UpdatedAt" timestamp with time zone NOT NULL,
    CONSTRAINT "PK_SnakeCatchingTariffs" PRIMARY KEY ("Id"),
    CONSTRAINT "FK_SnakeCatchingTariffs_SnakeSpecies_SnakeSpeciesId" FOREIGN KEY ("SnakeSpeciesId") REFERENCES "SnakeAid"."SnakeSpecies" ("Id") ON DELETE CASCADE
);

CREATE TABLE "SnakeAid"."SnakeSpeciesNames" (
    "Id" integer GENERATED BY DEFAULT AS IDENTITY,
    "Name" character varying(500) NOT NULL,
    "Slug" character varying(500) NOT NULL,
    "SnakeSpeciesId" integer NOT NULL,
    "CreatedAt" timestamp with time zone NOT NULL,
    "UpdatedAt" timestamp with time zone NOT NULL,
    CONSTRAINT "PK_SnakeSpeciesNames" PRIMARY KEY ("Id"),
    CONSTRAINT "FK_SnakeSpeciesNames_SnakeSpecies_SnakeSpeciesId" FOREIGN KEY ("SnakeSpeciesId") REFERENCES "SnakeAid"."SnakeSpecies" ("Id") ON DELETE CASCADE
);

CREATE TABLE "SnakeAid"."Antivenoms" (
    "Id" integer GENERATED BY DEFAULT AS IDENTITY,
    "Name" text NOT NULL,
    "Manufacturer" character varying(200) NOT NULL,
    "Description" character varying(1000) NOT NULL,
    "TreatmentFacilityId" integer,
    "CreatedAt" timestamp with time zone NOT NULL,
    "UpdatedAt" timestamp with time zone NOT NULL,
    CONSTRAINT "PK_Antivenoms" PRIMARY KEY ("Id"),
    CONSTRAINT "FK_Antivenoms_TreatmentFacilities_TreatmentFacilityId" FOREIGN KEY ("TreatmentFacilityId") REFERENCES "SnakeAid"."TreatmentFacilities" ("Id")
);

CREATE TABLE "SnakeAid"."ExpertSpecializations" (
    "Id" uuid NOT NULL,
    "ExpertId" uuid NOT NULL,
    "SpecializationId" integer NOT NULL,
    "CreatedAt" timestamp with time zone NOT NULL,
    "UpdatedAt" timestamp with time zone NOT NULL,
    CONSTRAINT "PK_ExpertSpecializations" PRIMARY KEY ("Id"),
    CONSTRAINT "FK_ExpertSpecializations_ExpertProfiles_ExpertId" FOREIGN KEY ("ExpertId") REFERENCES "SnakeAid"."ExpertProfiles" ("AccountId") ON DELETE CASCADE,
    CONSTRAINT "FK_ExpertSpecializations_Specializations_SpecializationId" FOREIGN KEY ("SpecializationId") REFERENCES "SnakeAid"."Specializations" ("Id") ON DELETE CASCADE
);

CREATE TABLE "SnakeAid"."Consultations" (
    "Id" uuid NOT NULL,
    "CallerId" uuid NOT NULL,
    "CalleeId" uuid NOT NULL,
    "RoomId" text NOT NULL,
    "StartTime" timestamp with time zone NOT NULL,
    "EndTime" timestamp with time zone,
    "Status" integer NOT NULL,
    "Type" integer NOT NULL,
    "ExpertTimeSlotId" uuid,
    "CreatedAt" timestamp with time zone NOT NULL,
    "UpdatedAt" timestamp with time zone NOT NULL,
    CONSTRAINT "PK_Consultations" PRIMARY KEY ("Id"),
    CONSTRAINT "FK_Consultations_Accounts_CalleeId" FOREIGN KEY ("CalleeId") REFERENCES "AspNetIdentity"."Accounts" ("Id") ON DELETE RESTRICT,
    CONSTRAINT "FK_Consultations_Accounts_CallerId" FOREIGN KEY ("CallerId") REFERENCES "AspNetIdentity"."Accounts" ("Id") ON DELETE RESTRICT,
    CONSTRAINT "FK_Consultations_ExpertTimeSlots_ExpertTimeSlotId" FOREIGN KEY ("ExpertTimeSlotId") REFERENCES "SnakeAid"."ExpertTimeSlots" ("Id")
);

CREATE TABLE "SnakeAid"."ReputationTransactions" (
    "Id" uuid NOT NULL,
    "UserId" uuid NOT NULL,
    "RawEventId" uuid NOT NULL,
    "ReputationRuleId" integer NOT NULL,
    "PointsChanged" integer NOT NULL,
    "PreviousPoints" integer NOT NULL,
    "NewPoints" integer NOT NULL,
    "Notes" text,
    "CreatedAt" timestamp with time zone NOT NULL,
    "UpdatedAt" timestamp with time zone NOT NULL,
    CONSTRAINT "PK_ReputationTransactions" PRIMARY KEY ("Id"),
    CONSTRAINT "FK_ReputationTransactions_Accounts_UserId" FOREIGN KEY ("UserId") REFERENCES "AspNetIdentity"."Accounts" ("Id") ON DELETE RESTRICT,
    CONSTRAINT "FK_ReputationTransactions_ReputationRawEvents_RawEventId" FOREIGN KEY ("RawEventId") REFERENCES "SnakeAid"."ReputationRawEvents" ("Id") ON DELETE RESTRICT,
    CONSTRAINT "FK_ReputationTransactions_ReputationRules_ReputationRuleId" FOREIGN KEY ("ReputationRuleId") REFERENCES "SnakeAid"."ReputationRules" ("Id") ON DELETE RESTRICT
);

CREATE TABLE "SnakeAid"."SnakebiteIncidents" (
    "Id" uuid NOT NULL,
    "UserId" uuid NOT NULL,
    "LocationCoordinates" geometry(Point, 4326) NOT NULL,
    "SymptomsReport" jsonb,
    "Status" integer NOT NULL,
    "CurrentSessionNumber" integer NOT NULL,
    "CurrentRadiusKm" integer NOT NULL,
    "LastSessionAt" timestamp with time zone,
    "AssignedAt" timestamp with time zone,
    "AssignedRescuerId" uuid,
    "CancellationReason" character varying(500),
    "SeverityLevel" integer,
    "IncidentOccurredAt" timestamp with time zone,
    "CreatedAt" timestamp with time zone NOT NULL,
    "UpdatedAt" timestamp with time zone NOT NULL,
    CONSTRAINT "PK_SnakebiteIncidents" PRIMARY KEY ("Id"),
    CONSTRAINT "FK_SnakebiteIncidents_MemberProfiles_UserId" FOREIGN KEY ("UserId") REFERENCES "SnakeAid"."MemberProfiles" ("AccountId") ON DELETE RESTRICT,
    CONSTRAINT "FK_SnakebiteIncidents_RescuerProfiles_AssignedRescuerId" FOREIGN KEY ("AssignedRescuerId") REFERENCES "SnakeAid"."RescuerProfiles" ("AccountId") ON DELETE SET NULL
);

CREATE TABLE "SnakeAid"."SnakeCatchingRequests" (
    "Id" uuid NOT NULL,
    "UserId" uuid NOT NULL,
    "Address" character varying(1000) NOT NULL,
    "LocationCoordinates" geometry(Point, 4326) NOT NULL,
    "AdditionalDetails" character varying(2000) NOT NULL,
    "Status" integer NOT NULL,
    "Priority" integer NOT NULL,
    "RequestDate" timestamp with time zone NOT NULL,
    "PreferredTime" timestamp with time zone,
    "AssignedAt" timestamp with time zone,
    "AssignedRescuerId" uuid,
    "EstimatedPrice" numeric(18,2),
    "CancellationReason" character varying(500),
    "Notes" character varying(1000),
    "CreatedAt" timestamp with time zone NOT NULL,
    "UpdatedAt" timestamp with time zone NOT NULL,
    CONSTRAINT "PK_SnakeCatchingRequests" PRIMARY KEY ("Id"),
    CONSTRAINT "FK_SnakeCatchingRequests_MemberProfiles_UserId" FOREIGN KEY ("UserId") REFERENCES "SnakeAid"."MemberProfiles" ("AccountId") ON DELETE RESTRICT,
    CONSTRAINT "FK_SnakeCatchingRequests_RescuerProfiles_AssignedRescuerId" FOREIGN KEY ("AssignedRescuerId") REFERENCES "SnakeAid"."RescuerProfiles" ("AccountId") ON DELETE SET NULL
);

CREATE TABLE "SnakeAid"."WalletWithdraws" (
    "Id" uuid NOT NULL,
    "UserId" uuid NOT NULL,
    "WalletId" uuid NOT NULL,
    "Amount" numeric(18,2) NOT NULL,
    "BankAccount" character varying(100) NOT NULL,
    "BankName" character varying(100) NOT NULL,
    "Status" integer NOT NULL,
    "ProcessedAt" timestamp with time zone,
    "RejectionReason" character varying(500),
    "CreatedAt" timestamp with time zone NOT NULL,
    "UpdatedAt" timestamp with time zone NOT NULL,
    CONSTRAINT "PK_WalletWithdraws" PRIMARY KEY ("Id"),
    CONSTRAINT "FK_WalletWithdraws_Accounts_UserId" FOREIGN KEY ("UserId") REFERENCES "AspNetIdentity"."Accounts" ("Id") ON DELETE RESTRICT,
    CONSTRAINT "FK_WalletWithdraws_Wallets_WalletId" FOREIGN KEY ("WalletId") REFERENCES "SnakeAid"."Wallets" ("Id") ON DELETE RESTRICT
);

CREATE TABLE "SnakeAid"."FilterSnakeMappings" (
    "Id" integer GENERATED BY DEFAULT AS IDENTITY,
    "FilterOptionId" integer NOT NULL,
    "SnakeSpeciesId" integer NOT NULL,
    "CreatedAt" timestamp with time zone NOT NULL,
    "IsActive" boolean NOT NULL,
    "Notes" character varying(500),
    CONSTRAINT "PK_FilterSnakeMappings" PRIMARY KEY ("Id"),
    CONSTRAINT "FK_FilterSnakeMappings_FilterOptions_FilterOptionId" FOREIGN KEY ("FilterOptionId") REFERENCES "SnakeAid"."FilterOptions" ("Id") ON DELETE CASCADE,
    CONSTRAINT "FK_FilterSnakeMappings_SnakeSpecies_SnakeSpeciesId" FOREIGN KEY ("SnakeSpeciesId") REFERENCES "SnakeAid"."SnakeSpecies" ("Id") ON DELETE CASCADE
);

CREATE TABLE "SnakeAid"."SpeciesVenoms" (
    "SnakeSpeciesId" integer NOT NULL,
    "VenomTypeId" integer NOT NULL,
    CONSTRAINT "PK_SpeciesVenoms" PRIMARY KEY ("SnakeSpeciesId", "VenomTypeId"),
    CONSTRAINT "FK_SpeciesVenoms_SnakeSpecies_SnakeSpeciesId" FOREIGN KEY ("SnakeSpeciesId") REFERENCES "SnakeAid"."SnakeSpecies" ("Id") ON DELETE CASCADE,
    CONSTRAINT "FK_SpeciesVenoms_VenomTypes_VenomTypeId" FOREIGN KEY ("VenomTypeId") REFERENCES "SnakeAid"."VenomTypes" ("Id") ON DELETE CASCADE
);

CREATE TABLE "SnakeAid"."SymptomConfigs" (
    "Id" integer GENERATED BY DEFAULT AS IDENTITY,
    "GroupName" character varying(100) NOT NULL,
    "AttributeKey" character varying(100) NOT NULL,
    "AttributeLabel" character varying(500) NOT NULL,
    "DisplayOrder" integer NOT NULL,
    "Name" character varying(300) NOT NULL,
    "Description" character varying(1000),
    "IsCritical" boolean NOT NULL,
    "AlertMessage" character varying(1000),
    "Category" integer NOT NULL,
    "TimeScoresJson" jsonb,
    "VenomTypeId" integer,
    "IsActive" boolean NOT NULL,
    "CreatedAt" timestamp with time zone NOT NULL,
    "UpdatedAt" timestamp with time zone NOT NULL,
    CONSTRAINT "PK_SymptomConfigs" PRIMARY KEY ("Id"),
    CONSTRAINT "FK_SymptomConfigs_VenomTypes_VenomTypeId" FOREIGN KEY ("VenomTypeId") REFERENCES "SnakeAid"."VenomTypes" ("Id") ON DELETE SET NULL
);

CREATE TABLE "SnakeAid"."SpeciesAntivenoms" (
    "Id" integer GENERATED BY DEFAULT AS IDENTITY,
    "SnakeSpeciesId" integer NOT NULL,
    "AntivenomId" integer NOT NULL,
    "IsActive" boolean NOT NULL,
    "CreatedAt" timestamp with time zone NOT NULL,
    "UpdatedAt" timestamp with time zone NOT NULL,
    CONSTRAINT "PK_SpeciesAntivenoms" PRIMARY KEY ("Id"),
    CONSTRAINT "FK_SpeciesAntivenoms_Antivenoms_AntivenomId" FOREIGN KEY ("AntivenomId") REFERENCES "SnakeAid"."Antivenoms" ("Id") ON DELETE CASCADE,
    CONSTRAINT "FK_SpeciesAntivenoms_SnakeSpecies_SnakeSpeciesId" FOREIGN KEY ("SnakeSpeciesId") REFERENCES "SnakeAid"."SnakeSpecies" ("Id") ON DELETE CASCADE
);

CREATE TABLE "SnakeAid"."ChatMessages" (
    "Id" uuid NOT NULL,
    "ConsultationId" uuid NOT NULL,
    "SenderId" uuid NOT NULL,
    "Content" character varying(2000) NOT NULL,
    "SentAt" timestamp with time zone NOT NULL,
    "AttachmentUrl" text,
    CONSTRAINT "PK_ChatMessages" PRIMARY KEY ("Id"),
    CONSTRAINT "FK_ChatMessages_Accounts_SenderId" FOREIGN KEY ("SenderId") REFERENCES "AspNetIdentity"."Accounts" ("Id") ON DELETE RESTRICT,
    CONSTRAINT "FK_ChatMessages_Consultations_ConsultationId" FOREIGN KEY ("ConsultationId") REFERENCES "SnakeAid"."Consultations" ("Id") ON DELETE CASCADE
);

CREATE TABLE "SnakeAid"."ConsultationBookings" (
    "Id" uuid NOT NULL,
    "UserId" uuid NOT NULL,
    "ExpertId" uuid NOT NULL,
    "Price" numeric NOT NULL,
    "BookedAt" timestamp with time zone NOT NULL,
    "PaymentDeadline" timestamp with time zone NOT NULL,
    "Status" integer NOT NULL,
    "CancelledAt" timestamp with time zone,
    "CancellationReason" text,
    "ConsultationId" uuid,
    "TimeSlotId" uuid NOT NULL,
    "CreatedAt" timestamp with time zone NOT NULL,
    "UpdatedAt" timestamp with time zone NOT NULL,
    CONSTRAINT "PK_ConsultationBookings" PRIMARY KEY ("Id"),
    CONSTRAINT "FK_ConsultationBookings_Accounts_ExpertId" FOREIGN KEY ("ExpertId") REFERENCES "AspNetIdentity"."Accounts" ("Id") ON DELETE RESTRICT,
    CONSTRAINT "FK_ConsultationBookings_Accounts_UserId" FOREIGN KEY ("UserId") REFERENCES "AspNetIdentity"."Accounts" ("Id") ON DELETE RESTRICT,
    CONSTRAINT "FK_ConsultationBookings_Consultations_ConsultationId" FOREIGN KEY ("ConsultationId") REFERENCES "SnakeAid"."Consultations" ("Id") ON DELETE SET NULL,
    CONSTRAINT "FK_ConsultationBookings_ExpertTimeSlots_TimeSlotId" FOREIGN KEY ("TimeSlotId") REFERENCES "SnakeAid"."ExpertTimeSlots" ("Id") ON DELETE RESTRICT
);

CREATE TABLE "SnakeAid"."RescueMissions" (
    "Id" uuid NOT NULL,
    "IncidentId" uuid NOT NULL,
    "RescuerId" uuid NOT NULL,
    "Status" integer NOT NULL,
    "Price" numeric(18,2) NOT NULL,
    "StartedAt" timestamp with time zone,
    "ArrivedAt" timestamp with time zone,
    "CompletedAt" timestamp with time zone,
    "Notes" character varying(2000),
    "CancellationReason" character varying(500),
    "EstimatedCost" numeric(18,2),
    "ActualCost" numeric(18,2),
    "CreatedAt" timestamp with time zone NOT NULL,
    "UpdatedAt" timestamp with time zone NOT NULL,
    CONSTRAINT "PK_RescueMissions" PRIMARY KEY ("Id"),
    CONSTRAINT "FK_RescueMissions_RescuerProfiles_RescuerId" FOREIGN KEY ("RescuerId") REFERENCES "SnakeAid"."RescuerProfiles" ("AccountId") ON DELETE RESTRICT,
    CONSTRAINT "FK_RescueMissions_SnakebiteIncidents_IncidentId" FOREIGN KEY ("IncidentId") REFERENCES "SnakeAid"."SnakebiteIncidents" ("Id") ON DELETE CASCADE
);

CREATE TABLE "SnakeAid"."RescueRequestSessions" (
    "Id" uuid NOT NULL,
    "IncidentId" uuid NOT NULL,
    "SessionNumber" integer NOT NULL,
    "RadiusKm" integer NOT NULL,
    "Status" integer NOT NULL,
    "CompletedAt" timestamp with time zone,
    "TriggerType" integer NOT NULL,
    "RescuersPinged" integer NOT NULL,
    "CreatedAt" timestamp with time zone NOT NULL,
    "UpdatedAt" timestamp with time zone NOT NULL,
    CONSTRAINT "PK_RescueRequestSessions" PRIMARY KEY ("Id"),
    CONSTRAINT "FK_RescueRequestSessions_SnakebiteIncidents_IncidentId" FOREIGN KEY ("IncidentId") REFERENCES "SnakeAid"."SnakebiteIncidents" ("Id") ON DELETE CASCADE
);

CREATE TABLE "SnakeAid"."CatchingRequestDetails" (
    "Id" uuid NOT NULL,
    "SnakeCatchingRequestId" uuid NOT NULL,
    "SnakeSpeciesId" integer NOT NULL,
    "Quantity" integer NOT NULL,
    "CreatedAt" timestamp with time zone NOT NULL,
    "UpdatedAt" timestamp with time zone NOT NULL,
    CONSTRAINT "PK_CatchingRequestDetails" PRIMARY KEY ("Id"),
    CONSTRAINT "FK_CatchingRequestDetails_SnakeCatchingRequests_SnakeCatchingR~" FOREIGN KEY ("SnakeCatchingRequestId") REFERENCES "SnakeAid"."SnakeCatchingRequests" ("Id") ON DELETE CASCADE,
    CONSTRAINT "FK_CatchingRequestDetails_SnakeSpecies_SnakeSpeciesId" FOREIGN KEY ("SnakeSpeciesId") REFERENCES "SnakeAid"."SnakeSpecies" ("Id") ON DELETE RESTRICT
);

CREATE TABLE "SnakeAid"."ReportMedias" (
    "Id" uuid NOT NULL,
    "ReferenceId" uuid NOT NULL,
    "ReferenceType" integer NOT NULL,
    "FileName" text NOT NULL,
    "MediaUrl" text NOT NULL,
    "ContentType" text NOT NULL,
    "FileSize" bigint NOT NULL,
    "Purpose" integer NOT NULL,
    "UploadBatchId" uuid,
    "SequenceOrder" integer,
    "RequiresAIProcessing" boolean NOT NULL,
    "IsProcessed" boolean NOT NULL,
    "ProcessedAt" timestamp with time zone,
    "SnakeCatchingRequestId" uuid,
    "SnakebiteIncidentId" uuid,
    "CreatedAt" timestamp with time zone NOT NULL,
    "UpdatedAt" timestamp with time zone NOT NULL,
    CONSTRAINT "PK_ReportMedias" PRIMARY KEY ("Id"),
    CONSTRAINT "FK_ReportMedias_SnakeCatchingRequests_SnakeCatchingRequestId" FOREIGN KEY ("SnakeCatchingRequestId") REFERENCES "SnakeAid"."SnakeCatchingRequests" ("Id"),
    CONSTRAINT "FK_ReportMedias_SnakebiteIncidents_SnakebiteIncidentId" FOREIGN KEY ("SnakebiteIncidentId") REFERENCES "SnakeAid"."SnakebiteIncidents" ("Id")
);

CREATE TABLE "SnakeAid"."SnakeCatchingMissions" (
    "Id" uuid NOT NULL,
    "RescuerId" uuid NOT NULL,
    "SnakeCatchingRequestId" uuid NOT NULL,
    "Status" integer NOT NULL,
    "Price" numeric(18,2) NOT NULL,
    "StartedAt" timestamp with time zone,
    "ArrivedAt" timestamp with time zone,
    "CompletedAt" timestamp with time zone,
    "Notes" character varying(2000),
    "CancellationReason" character varying(500),
    "EstimatedCost" numeric(18,2),
    "ActualCost" numeric(18,2),
    "CreatedAt" timestamp with time zone NOT NULL,
    "UpdatedAt" timestamp with time zone NOT NULL,
    CONSTRAINT "PK_SnakeCatchingMissions" PRIMARY KEY ("Id"),
    CONSTRAINT "FK_SnakeCatchingMissions_RescuerProfiles_RescuerId" FOREIGN KEY ("RescuerId") REFERENCES "SnakeAid"."RescuerProfiles" ("AccountId") ON DELETE RESTRICT,
    CONSTRAINT "FK_SnakeCatchingMissions_SnakeCatchingRequests_SnakeCatchingRe~" FOREIGN KEY ("SnakeCatchingRequestId") REFERENCES "SnakeAid"."SnakeCatchingRequests" ("Id") ON DELETE CASCADE
);

CREATE TABLE "SnakeAid"."ConsultationPingRequests" (
    "Id" uuid NOT NULL,
    "RescuerId" uuid NOT NULL,
    "ExpertId" uuid NOT NULL,
    "Status" integer NOT NULL,
    "RequestedAt" timestamp with time zone NOT NULL,
    "RespondedAt" timestamp with time zone,
    "ExpiresAt" timestamp with time zone,
    "RescueMissionId" uuid,
    "ConsultationId" uuid,
    CONSTRAINT "PK_ConsultationPingRequests" PRIMARY KEY ("Id"),
    CONSTRAINT "FK_ConsultationPingRequests_Accounts_ExpertId" FOREIGN KEY ("ExpertId") REFERENCES "AspNetIdentity"."Accounts" ("Id") ON DELETE RESTRICT,
    CONSTRAINT "FK_ConsultationPingRequests_Accounts_RescuerId" FOREIGN KEY ("RescuerId") REFERENCES "AspNetIdentity"."Accounts" ("Id") ON DELETE RESTRICT,
    CONSTRAINT "FK_ConsultationPingRequests_Consultations_ConsultationId" FOREIGN KEY ("ConsultationId") REFERENCES "SnakeAid"."Consultations" ("Id") ON DELETE SET NULL,
    CONSTRAINT "FK_ConsultationPingRequests_RescueMissions_RescueMissionId" FOREIGN KEY ("RescueMissionId") REFERENCES "SnakeAid"."RescueMissions" ("Id") ON DELETE SET NULL
);

CREATE TABLE "SnakeAid"."RescuerRequests" (
    "Id" uuid NOT NULL,
    "SessionId" uuid NOT NULL,
    "IncidentId" uuid NOT NULL,
    "RescuerId" uuid NOT NULL,
    "Status" integer NOT NULL,
    "RequestSentAt" timestamp with time zone NOT NULL,
    "ResponseAt" timestamp with time zone,
    "ExpiredAt" timestamp with time zone NOT NULL,
    "CreatedAt" timestamp with time zone NOT NULL,
    "UpdatedAt" timestamp with time zone NOT NULL,
    CONSTRAINT "PK_RescuerRequests" PRIMARY KEY ("Id"),
    CONSTRAINT "FK_RescuerRequests_RescueRequestSessions_SessionId" FOREIGN KEY ("SessionId") REFERENCES "SnakeAid"."RescueRequestSessions" ("Id") ON DELETE CASCADE,
    CONSTRAINT "FK_RescuerRequests_RescuerProfiles_RescuerId" FOREIGN KEY ("RescuerId") REFERENCES "SnakeAid"."RescuerProfiles" ("AccountId") ON DELETE RESTRICT,
    CONSTRAINT "FK_RescuerRequests_SnakebiteIncidents_IncidentId" FOREIGN KEY ("IncidentId") REFERENCES "SnakeAid"."SnakebiteIncidents" ("Id") ON DELETE CASCADE
);

CREATE TABLE "SnakeAid"."SnakeAIRecognitionResults" (
    "Id" uuid NOT NULL,
    "ReportMediaId" uuid NOT NULL,
    "AIModelId" integer NOT NULL,
    "YoloClassName" character varying(200) NOT NULL,
    "Confidence" numeric(5,4) NOT NULL,
    "DetectedSpeciesId" integer,
    "IsMapped" boolean NOT NULL,
    "AllDetections" jsonb,
    "Status" integer NOT NULL,
    "ExpertId" uuid,
    "ExpertVerifiedAt" timestamp with time zone,
    "ExpertCorrectedSpeciesId" integer,
    "ExpertNotes" character varying(1000),
    "ClassMappingId" uuid,
    "CreatedAt" timestamp with time zone NOT NULL,
    "UpdatedAt" timestamp with time zone NOT NULL,
    CONSTRAINT "PK_SnakeAIRecognitionResults" PRIMARY KEY ("Id"),
    CONSTRAINT "FK_SnakeAIRecognitionResults_AIModels_AIModelId" FOREIGN KEY ("AIModelId") REFERENCES "SnakeAid"."AIModels" ("Id") ON DELETE RESTRICT,
    CONSTRAINT "FK_SnakeAIRecognitionResults_AISnakeClassMappings_ClassMapping~" FOREIGN KEY ("ClassMappingId") REFERENCES "SnakeAid"."AISnakeClassMappings" ("Id"),
    CONSTRAINT "FK_SnakeAIRecognitionResults_Accounts_ExpertId" FOREIGN KEY ("ExpertId") REFERENCES "AspNetIdentity"."Accounts" ("Id") ON DELETE SET NULL,
    CONSTRAINT "FK_SnakeAIRecognitionResults_ReportMedias_ReportMediaId" FOREIGN KEY ("ReportMediaId") REFERENCES "SnakeAid"."ReportMedias" ("Id") ON DELETE CASCADE,
    CONSTRAINT "FK_SnakeAIRecognitionResults_SnakeSpecies_DetectedSpeciesId" FOREIGN KEY ("DetectedSpeciesId") REFERENCES "SnakeAid"."SnakeSpecies" ("Id") ON DELETE SET NULL,
    CONSTRAINT "FK_SnakeAIRecognitionResults_SnakeSpecies_ExpertCorrectedSpeci~" FOREIGN KEY ("ExpertCorrectedSpeciesId") REFERENCES "SnakeAid"."SnakeSpecies" ("Id") ON DELETE SET NULL
);

CREATE TABLE "SnakeAid"."CatchingMissionDetails" (
    "Id" uuid NOT NULL,
    "SnakeCatchingMissionId" uuid NOT NULL,
    "SnakeSpeciesId" integer NOT NULL,
    "Quantity" integer NOT NULL,
    "CreatedAt" timestamp with time zone NOT NULL,
    "UpdatedAt" timestamp with time zone NOT NULL,
    CONSTRAINT "PK_CatchingMissionDetails" PRIMARY KEY ("Id"),
    CONSTRAINT "FK_CatchingMissionDetails_SnakeCatchingMissions_SnakeCatchingM~" FOREIGN KEY ("SnakeCatchingMissionId") REFERENCES "SnakeAid"."SnakeCatchingMissions" ("Id") ON DELETE CASCADE,
    CONSTRAINT "FK_CatchingMissionDetails_SnakeSpecies_SnakeSpeciesId" FOREIGN KEY ("SnakeSpeciesId") REFERENCES "SnakeAid"."SnakeSpecies" ("Id") ON DELETE RESTRICT
);

CREATE INDEX "EmailIndex" ON "AspNetIdentity"."Accounts" ("NormalizedEmail");

CREATE INDEX "IX_Accounts_IsActive" ON "AspNetIdentity"."Accounts" ("IsActive");

CREATE INDEX "IX_Accounts_Role" ON "AspNetIdentity"."Accounts" ("Role");

CREATE UNIQUE INDEX "UserNameIndex" ON "AspNetIdentity"."Accounts" ("NormalizedUserName");

CREATE INDEX "IX_AIModels_IsActive" ON "SnakeAid"."AIModels" ("IsActive");

CREATE INDEX "IX_AIModels_IsDefault" ON "SnakeAid"."AIModels" ("IsDefault");

CREATE UNIQUE INDEX "IX_AIModels_Version" ON "SnakeAid"."AIModels" ("Version");

CREATE UNIQUE INDEX "IX_AISnakeClassMappings_AIModelId_YoloClassId" ON "SnakeAid"."AISnakeClassMappings" ("AIModelId", "YoloClassId");

CREATE INDEX "IX_AISnakeClassMappings_IsActive" ON "SnakeAid"."AISnakeClassMappings" ("IsActive");

CREATE INDEX "IX_AISnakeClassMappings_SnakeSpeciesId" ON "SnakeAid"."AISnakeClassMappings" ("SnakeSpeciesId");

CREATE INDEX "IX_Antivenoms_TreatmentFacilityId" ON "SnakeAid"."Antivenoms" ("TreatmentFacilityId");

CREATE INDEX "IX_AppNotifications_IsRead" ON "SnakeAid"."AppNotifications" ("IsRead");

CREATE INDEX "IX_AppNotifications_UserId" ON "SnakeAid"."AppNotifications" ("UserId");

CREATE INDEX "IX_AppNotifications_UserId_IsRead" ON "SnakeAid"."AppNotifications" ("UserId", "IsRead");

CREATE INDEX "IX_Blogs_AuthorId" ON "SnakeAid"."Blogs" ("AuthorId");

CREATE INDEX "IX_Blogs_Status" ON "SnakeAid"."Blogs" ("Status");

CREATE INDEX "IX_CatchingMissionDetails_SnakeCatchingMissionId" ON "SnakeAid"."CatchingMissionDetails" ("SnakeCatchingMissionId");

CREATE INDEX "IX_CatchingMissionDetails_SnakeSpeciesId" ON "SnakeAid"."CatchingMissionDetails" ("SnakeSpeciesId");

CREATE INDEX "IX_CatchingRequestDetails_SnakeCatchingRequestId" ON "SnakeAid"."CatchingRequestDetails" ("SnakeCatchingRequestId");

CREATE INDEX "IX_CatchingRequestDetails_SnakeSpeciesId" ON "SnakeAid"."CatchingRequestDetails" ("SnakeSpeciesId");

CREATE INDEX "IX_ChatMessages_ConsultationId" ON "SnakeAid"."ChatMessages" ("ConsultationId");

CREATE INDEX "IX_ChatMessages_SenderId" ON "SnakeAid"."ChatMessages" ("SenderId");

CREATE INDEX "IX_ChatMessages_SentAt" ON "SnakeAid"."ChatMessages" ("SentAt");

CREATE INDEX "IX_CommunityReports_UserId" ON "SnakeAid"."CommunityReports" ("UserId");

CREATE INDEX "IX_ConsultationBookings_ConsultationId" ON "SnakeAid"."ConsultationBookings" ("ConsultationId");

CREATE INDEX "IX_ConsultationBookings_ExpertId" ON "SnakeAid"."ConsultationBookings" ("ExpertId");

CREATE INDEX "IX_ConsultationBookings_Status" ON "SnakeAid"."ConsultationBookings" ("Status");

CREATE INDEX "IX_ConsultationBookings_TimeSlotId" ON "SnakeAid"."ConsultationBookings" ("TimeSlotId");

CREATE INDEX "IX_ConsultationBookings_UserId" ON "SnakeAid"."ConsultationBookings" ("UserId");

CREATE INDEX "IX_ConsultationPingRequests_ConsultationId" ON "SnakeAid"."ConsultationPingRequests" ("ConsultationId");

CREATE INDEX "IX_ConsultationPingRequests_ExpertId" ON "SnakeAid"."ConsultationPingRequests" ("ExpertId");

CREATE INDEX "IX_ConsultationPingRequests_ExpiresAt" ON "SnakeAid"."ConsultationPingRequests" ("ExpiresAt");

CREATE INDEX "IX_ConsultationPingRequests_RescueMissionId" ON "SnakeAid"."ConsultationPingRequests" ("RescueMissionId");

CREATE INDEX "IX_ConsultationPingRequests_RescuerId" ON "SnakeAid"."ConsultationPingRequests" ("RescuerId");

CREATE INDEX "IX_ConsultationPingRequests_Status" ON "SnakeAid"."ConsultationPingRequests" ("Status");

CREATE INDEX "IX_Consultations_CalleeId" ON "SnakeAid"."Consultations" ("CalleeId");

CREATE INDEX "IX_Consultations_CallerId" ON "SnakeAid"."Consultations" ("CallerId");

CREATE INDEX "IX_Consultations_ExpertTimeSlotId" ON "SnakeAid"."Consultations" ("ExpertTimeSlotId");

CREATE INDEX "IX_Consultations_RoomId" ON "SnakeAid"."Consultations" ("RoomId");

CREATE INDEX "IX_Consultations_Status" ON "SnakeAid"."Consultations" ("Status");

CREATE UNIQUE INDEX "IX_ExpertSpecializations_ExpertId_SpecializationId" ON "SnakeAid"."ExpertSpecializations" ("ExpertId", "SpecializationId");

CREATE INDEX "IX_ExpertSpecializations_SpecializationId" ON "SnakeAid"."ExpertSpecializations" ("SpecializationId");

CREATE INDEX "IX_ExpertTimeSlots_ExpertId" ON "SnakeAid"."ExpertTimeSlots" ("ExpertId");

CREATE INDEX "IX_ExpertTimeSlots_ExpertId_StartTime" ON "SnakeAid"."ExpertTimeSlots" ("ExpertId", "StartTime");

CREATE INDEX "IX_ExpertTimeSlots_Status" ON "SnakeAid"."ExpertTimeSlots" ("Status");

CREATE INDEX "IX_FilterOptions_IsActive" ON "SnakeAid"."FilterOptions" ("IsActive");

CREATE INDEX "IX_FilterOptions_QuestionId" ON "SnakeAid"."FilterOptions" ("QuestionId");

CREATE INDEX "IX_FilterQuestions_IsActive" ON "SnakeAid"."FilterQuestions" ("IsActive");

CREATE UNIQUE INDEX "IX_FilterSnakeMappings_FilterOptionId_SnakeSpeciesId" ON "SnakeAid"."FilterSnakeMappings" ("FilterOptionId", "SnakeSpeciesId");

CREATE INDEX "IX_FilterSnakeMappings_IsActive" ON "SnakeAid"."FilterSnakeMappings" ("IsActive");

CREATE INDEX "IX_FilterSnakeMappings_SnakeSpeciesId" ON "SnakeAid"."FilterSnakeMappings" ("SnakeSpeciesId");

CREATE INDEX "IX_FirstAidGuidelines_Type" ON "SnakeAid"."FirstAidGuidelines" ("Type");

CREATE INDEX "IX_LibraryMedias_IsActive" ON "SnakeAid"."LibraryMedias" ("IsActive");

CREATE INDEX "IX_LibraryMedias_IsPublic" ON "SnakeAid"."LibraryMedias" ("IsPublic");

CREATE INDEX "IX_LibraryMedias_MediaType" ON "SnakeAid"."LibraryMedias" ("MediaType");

CREATE INDEX "IX_LibraryMedias_SnakeSpeciesId" ON "SnakeAid"."LibraryMedias" ("SnakeSpeciesId");

CREATE INDEX "IX_LibraryMedias_UploadedById" ON "SnakeAid"."LibraryMedias" ("UploadedById");

CREATE INDEX "IX_LocationEvents_AccountId" ON "SnakeAid"."LocationEvents" ("AccountId");

CREATE INDEX "IX_LocationEvents_RecordedAt" ON "SnakeAid"."LocationEvents" ("RecordedAt");

CREATE INDEX "IX_LocationEvents_SessionId" ON "SnakeAid"."LocationEvents" ("SessionId");

CREATE INDEX "IX_LocationEvents_SessionId_RecordedAt" ON "SnakeAid"."LocationEvents" ("SessionId", "RecordedAt");

CREATE INDEX "IX_Otp_UserId" ON "SnakeAid"."Otp" ("UserId");

CREATE INDEX "IX_ReportMedias_ReferenceId" ON "SnakeAid"."ReportMedias" ("ReferenceId");

CREATE INDEX "IX_ReportMedias_ReferenceId_ReferenceType" ON "SnakeAid"."ReportMedias" ("ReferenceId", "ReferenceType");

CREATE INDEX "IX_ReportMedias_ReferenceType" ON "SnakeAid"."ReportMedias" ("ReferenceType");

CREATE INDEX "IX_ReportMedias_RequiresAIProcessing" ON "SnakeAid"."ReportMedias" ("RequiresAIProcessing");

CREATE INDEX "IX_ReportMedias_SnakebiteIncidentId" ON "SnakeAid"."ReportMedias" ("SnakebiteIncidentId");

CREATE INDEX "IX_ReportMedias_SnakeCatchingRequestId" ON "SnakeAid"."ReportMedias" ("SnakeCatchingRequestId");

CREATE INDEX "IX_ReputationRawEvents_EventType" ON "SnakeAid"."ReputationRawEvents" ("EventType");

CREATE INDEX "IX_ReputationRawEvents_IsProcessed" ON "SnakeAid"."ReputationRawEvents" ("IsProcessed");

CREATE INDEX "IX_ReputationRawEvents_ReferenceId_ReferenceType" ON "SnakeAid"."ReputationRawEvents" ("ReferenceId", "ReferenceType");

CREATE INDEX "IX_ReputationRawEvents_UserId" ON "SnakeAid"."ReputationRawEvents" ("UserId");

CREATE UNIQUE INDEX "IX_ReputationRules_Name" ON "SnakeAid"."ReputationRules" ("Name");

CREATE INDEX "IX_ReputationTransactions_RawEventId" ON "SnakeAid"."ReputationTransactions" ("RawEventId");

CREATE INDEX "IX_ReputationTransactions_ReputationRuleId" ON "SnakeAid"."ReputationTransactions" ("ReputationRuleId");

CREATE INDEX "IX_ReputationTransactions_UserId" ON "SnakeAid"."ReputationTransactions" ("UserId");

CREATE UNIQUE INDEX "IX_RescueMissions_IncidentId" ON "SnakeAid"."RescueMissions" ("IncidentId");

CREATE INDEX "IX_RescueMissions_RescuerId" ON "SnakeAid"."RescueMissions" ("RescuerId");

CREATE INDEX "IX_RescueMissions_Status" ON "SnakeAid"."RescueMissions" ("Status");

CREATE INDEX "IX_RescueRequestSessions_IncidentId" ON "SnakeAid"."RescueRequestSessions" ("IncidentId");

CREATE UNIQUE INDEX "IX_RescueRequestSessions_IncidentId_SessionNumber" ON "SnakeAid"."RescueRequestSessions" ("IncidentId", "SessionNumber");

CREATE INDEX "IX_RescueRequestSessions_Status" ON "SnakeAid"."RescueRequestSessions" ("Status");

CREATE INDEX "IX_RescuerProfiles_IsOnline" ON "SnakeAid"."RescuerProfiles" ("IsOnline");

CREATE INDEX "IX_RescuerProfiles_Type" ON "SnakeAid"."RescuerProfiles" ("Type");

CREATE INDEX "IX_RescuerRequests_ExpiredAt" ON "SnakeAid"."RescuerRequests" ("ExpiredAt");

CREATE INDEX "IX_RescuerRequests_IncidentId" ON "SnakeAid"."RescuerRequests" ("IncidentId");

CREATE INDEX "IX_RescuerRequests_RescuerId" ON "SnakeAid"."RescuerRequests" ("RescuerId");

CREATE INDEX "IX_RescuerRequests_SessionId" ON "SnakeAid"."RescuerRequests" ("SessionId");

CREATE INDEX "IX_RescuerRequests_Status" ON "SnakeAid"."RescuerRequests" ("Status");

CREATE INDEX "IX_SnakeAIRecognitionResults_AIModelId" ON "SnakeAid"."SnakeAIRecognitionResults" ("AIModelId");

CREATE INDEX "IX_SnakeAIRecognitionResults_ClassMappingId" ON "SnakeAid"."SnakeAIRecognitionResults" ("ClassMappingId");

CREATE INDEX "IX_SnakeAIRecognitionResults_DetectedSpeciesId" ON "SnakeAid"."SnakeAIRecognitionResults" ("DetectedSpeciesId");

CREATE INDEX "IX_SnakeAIRecognitionResults_ExpertCorrectedSpeciesId" ON "SnakeAid"."SnakeAIRecognitionResults" ("ExpertCorrectedSpeciesId");

CREATE INDEX "IX_SnakeAIRecognitionResults_ExpertId" ON "SnakeAid"."SnakeAIRecognitionResults" ("ExpertId");

CREATE INDEX "IX_SnakeAIRecognitionResults_IsMapped" ON "SnakeAid"."SnakeAIRecognitionResults" ("IsMapped");

CREATE INDEX "IX_SnakeAIRecognitionResults_ReportMediaId" ON "SnakeAid"."SnakeAIRecognitionResults" ("ReportMediaId");

CREATE INDEX "IX_SnakeAIRecognitionResults_Status" ON "SnakeAid"."SnakeAIRecognitionResults" ("Status");

CREATE INDEX "IX_SnakebiteIncidents_AssignedRescuerId" ON "SnakeAid"."SnakebiteIncidents" ("AssignedRescuerId");

CREATE INDEX "IX_SnakebiteIncidents_Status" ON "SnakeAid"."SnakebiteIncidents" ("Status");

CREATE INDEX "IX_SnakebiteIncidents_UserId" ON "SnakeAid"."SnakebiteIncidents" ("UserId");

CREATE UNIQUE INDEX "IX_SnakeCatchingMissions_RequestId" ON "SnakeAid"."SnakeCatchingMissions" ("SnakeCatchingRequestId");

CREATE INDEX "IX_SnakeCatchingMissions_RescuerId" ON "SnakeAid"."SnakeCatchingMissions" ("RescuerId");

CREATE INDEX "IX_SnakeCatchingMissions_Status" ON "SnakeAid"."SnakeCatchingMissions" ("Status");

CREATE INDEX "IX_SnakeCatchingRequests_AssignedRescuerId" ON "SnakeAid"."SnakeCatchingRequests" ("AssignedRescuerId");

CREATE INDEX "IX_SnakeCatchingRequests_RequestDate" ON "SnakeAid"."SnakeCatchingRequests" ("RequestDate");

CREATE INDEX "IX_SnakeCatchingRequests_Status" ON "SnakeAid"."SnakeCatchingRequests" ("Status");

CREATE INDEX "IX_SnakeCatchingRequests_UserId" ON "SnakeAid"."SnakeCatchingRequests" ("UserId");

CREATE INDEX "IX_SnakeCatchingTariffs_IsActive" ON "SnakeAid"."SnakeCatchingTariffs" ("IsActive");

CREATE UNIQUE INDEX "IX_SnakeCatchingTariffs_SnakeSpeciesId_SizeCategory" ON "SnakeAid"."SnakeCatchingTariffs" ("SnakeSpeciesId", "SizeCategory");

CREATE INDEX "IX_SnakeSpecies_IsActive" ON "SnakeAid"."SnakeSpecies" ("IsActive");

CREATE INDEX "IX_SnakeSpecies_IsVenomous" ON "SnakeAid"."SnakeSpecies" ("IsVenomous");

CREATE UNIQUE INDEX "IX_SnakeSpecies_ScientificName" ON "SnakeAid"."SnakeSpecies" ("ScientificName");

CREATE UNIQUE INDEX "IX_SnakeSpecies_Slug" ON "SnakeAid"."SnakeSpecies" ("Slug");

CREATE INDEX "IX_SnakeSpeciesNames_Name" ON "SnakeAid"."SnakeSpeciesNames" ("Name");

CREATE UNIQUE INDEX "IX_SnakeSpeciesNames_Slug" ON "SnakeAid"."SnakeSpeciesNames" ("Slug");

CREATE INDEX "IX_SnakeSpeciesNames_SnakeSpeciesId" ON "SnakeAid"."SnakeSpeciesNames" ("SnakeSpeciesId");

CREATE UNIQUE INDEX "IX_Specializations_Name" ON "SnakeAid"."Specializations" ("Name");

CREATE INDEX "IX_SpeciesAntivenoms_AntivenomId" ON "SnakeAid"."SpeciesAntivenoms" ("AntivenomId");

CREATE UNIQUE INDEX "IX_SpeciesAntivenoms_SnakeSpeciesId_AntivenomId" ON "SnakeAid"."SpeciesAntivenoms" ("SnakeSpeciesId", "AntivenomId");

CREATE INDEX "IX_SpeciesVenoms_VenomTypeId" ON "SnakeAid"."SpeciesVenoms" ("VenomTypeId");

CREATE INDEX "IX_SymptomConfigs_AttributeKey" ON "SnakeAid"."SymptomConfigs" ("AttributeKey");

CREATE INDEX "IX_SymptomConfigs_DisplayOrder" ON "SnakeAid"."SymptomConfigs" ("DisplayOrder");

CREATE INDEX "IX_SymptomConfigs_IsActive" ON "SnakeAid"."SymptomConfigs" ("IsActive");

CREATE INDEX "IX_SymptomConfigs_VenomTypeId" ON "SnakeAid"."SymptomConfigs" ("VenomTypeId");

CREATE INDEX "IX_TrackingSessions_IsActive" ON "SnakeAid"."TrackingSessions" ("IsActive");

CREATE INDEX "IX_TrackingSessions_SessionId" ON "SnakeAid"."TrackingSessions" ("SessionId");

CREATE UNIQUE INDEX "IX_TrackingSessions_SessionId_SessionType" ON "SnakeAid"."TrackingSessions" ("SessionId", "SessionType");

CREATE INDEX "IX_TrackingSessions_SessionType" ON "SnakeAid"."TrackingSessions" ("SessionType");

CREATE INDEX "IX_Transactions_CreatedAt" ON "SnakeAid"."Transactions" ("CreatedAt");

CREATE INDEX "IX_Transactions_ReferenceId" ON "SnakeAid"."Transactions" ("ReferenceId");

CREATE INDEX "IX_Transactions_TransactionType" ON "SnakeAid"."Transactions" ("TransactionType");

CREATE INDEX "IX_Transactions_UserId" ON "SnakeAid"."Transactions" ("UserId");

CREATE INDEX "IX_TreatmentFacilities_IsActive" ON "SnakeAid"."TreatmentFacilities" ("IsActive");

CREATE INDEX "IX_TreatmentFacilities_Name" ON "SnakeAid"."TreatmentFacilities" ("Name");

CREATE INDEX "IX_UserClaims_UserId" ON "AspNetIdentity"."UserClaims" ("UserId");

CREATE INDEX "IX_UserFeedbacks_RaterId" ON "SnakeAid"."UserFeedbacks" ("RaterId");

CREATE INDEX "IX_UserFeedbacks_ReferenceId_Type" ON "SnakeAid"."UserFeedbacks" ("ReferenceId", "Type");

CREATE INDEX "IX_UserFeedbacks_TargetUserId" ON "SnakeAid"."UserFeedbacks" ("TargetUserId");

CREATE INDEX "IX_UserFeedbacks_Type" ON "SnakeAid"."UserFeedbacks" ("Type");

CREATE INDEX "IX_UserLogins_UserId" ON "AspNetIdentity"."UserLogins" ("UserId");

CREATE UNIQUE INDEX "IX_VenomTypes_FirstAidGuidelineId" ON "SnakeAid"."VenomTypes" ("FirstAidGuidelineId");

CREATE INDEX "IX_VenomTypes_IsActive" ON "SnakeAid"."VenomTypes" ("IsActive");

CREATE UNIQUE INDEX "IX_VenomTypes_Name" ON "SnakeAid"."VenomTypes" ("Name");

CREATE UNIQUE INDEX "IX_Wallets_UserId" ON "SnakeAid"."Wallets" ("UserId");

CREATE INDEX "IX_WalletWithdraws_Status" ON "SnakeAid"."WalletWithdraws" ("Status");

CREATE INDEX "IX_WalletWithdraws_UserId" ON "SnakeAid"."WalletWithdraws" ("UserId");

CREATE INDEX "IX_WalletWithdraws_WalletId" ON "SnakeAid"."WalletWithdraws" ("WalletId");

INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
VALUES ('20260201195624_SnakeAidMigratione', '8.0.23');

COMMIT;

START TRANSACTION;

DROP INDEX "SnakeAid"."IX_RescueMissions_IncidentId";

CREATE INDEX "IX_RescueMissions_IncidentId" ON "SnakeAid"."RescueMissions" ("IncidentId");

INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
VALUES ('20260210164830_UpdateSchemaV1', '8.0.23');

COMMIT;

START TRANSACTION;

ALTER TABLE "SnakeAid"."ReportMedias" DROP CONSTRAINT "FK_ReportMedias_SnakeCatchingRequests_SnakeCatchingRequestId";

ALTER TABLE "SnakeAid"."ReportMedias" DROP CONSTRAINT "FK_ReportMedias_SnakebiteIncidents_SnakebiteIncidentId";

DROP INDEX "SnakeAid"."IX_ReportMedias_SnakebiteIncidentId";

DROP INDEX "SnakeAid"."IX_ReportMedias_SnakeCatchingRequestId";

ALTER TABLE "SnakeAid"."ReportMedias" DROP COLUMN "SnakeCatchingRequestId";

ALTER TABLE "SnakeAid"."ReportMedias" DROP COLUMN "SnakebiteIncidentId";

INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
VALUES ('20260223180015_IgnoreMediaCollections', '8.0.23');

COMMIT;

START TRANSACTION;

ALTER TABLE "SnakeAid"."SnakeCatchingMissions" ADD "CatchingEnvironmentId" integer;

ALTER TABLE "SnakeAid"."ReportMedias" ALTER COLUMN "ReferenceId" DROP NOT NULL;

CREATE INDEX "IX_SnakeCatchingMissions_CatchingEnvironmentId" ON "SnakeAid"."SnakeCatchingMissions" ("CatchingEnvironmentId");

ALTER TABLE "SnakeAid"."SnakeCatchingMissions" ADD CONSTRAINT "FK_SnakeCatchingMissions_CatchingEnvironments_CatchingEnvironm~" FOREIGN KEY ("CatchingEnvironmentId") REFERENCES "SnakeAid"."CatchingEnvironments" ("Id") ON DELETE SET NULL;

INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
VALUES ('20260227194805_AddCatchingEnvironmentForMission', '8.0.23');

COMMIT;