[Part 2 / Phase 9] PathogenClassification: DB migration script

[roldy]

Problem Description

Part of Epic: [Epic] PathogenClassification: Replace GenoTypeResult & SeroGroupSpecification enums
Blocked by: Phase 8

Write the SQL migration that creates the new table, seeds all classification values,
migrates existing pathogentest rows, and drops the old columns.

Proposed Solution

Tasks

• CREATE TABLE pathogenclassification with columns: id, uuid, value, caption, disease, classification_type, parent_id, depth, active, archived, changedate, creationdate
• Create indexes: idx_pathogenclassification_disease, idx_pathogenclassification_parent
• Seed all Measles genotypes (23 values from GenoTypeResult)
• Seed Cryptosporidiosis species (5 values: CRYPTOSPORIDIUM_HOMINIS, CRYPTOSPORIDIUM_PARVUM, CRYPTOSPORIDIUM_SPECIES, OTHER, UNKNOWN)
• Seed IMI serogroups (12 values from SeroGroupSpecification)
• ALTER TABLE pathogentest ADD COLUMN pathogenclassification_id BIGINT REFERENCES pathogenclassification(id)
• ALTER TABLE pathogentest ADD COLUMN pathogenclassificationdetails VARCHAR(512)
• UPDATE pathogentest — migrate genotyperesult → pathogenclassification_id lookup
• UPDATE pathogentest — migrate serogroupspecification → pathogenclassification_id lookup (don't overwrite if genotype already set)
• After verification: DROP COLUMN genotyperesult, genotyperesulttext, serogroupspecification, serogroupspecificationtext
• Verify serotype, serotypingmethod, serotypingmethodtext columns are untouched

Additional Information

Acceptance Criteria

• Row counts before/after migration are equal (zero data loss)
• Spot-check: records with genoTypeResult = OTHER have pathogenClassificationDetails populated
• Spot-check: records with seroGroupSpecification != null mapped to correct pathogenclassification_id
• Old columns dropped cleanly
• serotype / serotypingmethod columns still populated and intact