package mysql import ( "errors" "fmt" "log" "os" "strings" "time" "gorm.io/driver/mysql" "gorm.io/gorm" "gorm.io/gorm/clause" "gorm.io/gorm/logger" "wucher/internal/config" actionsignoff "wucher/internal/domain/action_signoff" otherperson "wucher/internal/domain/other_person" afterflightinspection "wucher/internal/domain/after_flight_inspection" airrescuechecklist "wucher/internal/domain/air_rescue_checklist" "wucher/internal/domain/audit" "wucher/internal/domain/auth" "wucher/internal/domain/base" beforeflightinspection "wucher/internal/domain/before_flight_inspection" complaint "wucher/internal/domain/complaint" "wucher/internal/domain/dul" dutyroster "wucher/internal/domain/duty_roster" easarelease "wucher/internal/domain/easa_release" "wucher/internal/domain/facility" "wucher/internal/domain/federal_state" filemanager "wucher/internal/domain/file_manager" fleethistory "wucher/internal/domain/fleet_history" fleetstatus "wucher/internal/domain/fleet_status" "wucher/internal/domain/flight" flightdata "wucher/internal/domain/flight_data" flightinspection "wucher/internal/domain/flight_inspection" flightinspectionfilechecklist "wucher/internal/domain/flight_inspection_file_checklist" flightprepcheck "wucher/internal/domain/flight_prep_check" fmreport "wucher/internal/domain/fm_report" "wucher/internal/domain/forces_present" "wucher/internal/domain/health_insurance_companies" "wucher/internal/domain/helicopter" helicopterfile "wucher/internal/domain/helicopter_file" helicopterusage "wucher/internal/domain/helicopter_usage" "wucher/internal/domain/hospital" "wucher/internal/domain/icao" insurancepatientdata "wucher/internal/domain/insurance_patient_data" "wucher/internal/domain/land" mastersettings "wucher/internal/domain/master_settings" "wucher/internal/domain/mcf" "wucher/internal/domain/medicine" "wucher/internal/domain/mission" "wucher/internal/domain/opc" "wucher/internal/domain/operation" patientdata "wucher/internal/domain/patient_data" reserveac "wucher/internal/domain/reserve_ac" takeover "wucher/internal/domain/takeover" "wucher/internal/domain/transient" "wucher/internal/domain/vocation" "wucher/internal/shared/pkg/uuidv7" ) func Connect(cfg config.MySQLConfig) (*gorm.DB, error) { if cfg.DSN == "" { return nil, errors.New("MYSQL_DSN is required") } db, err := gorm.Open(mysql.Open(cfg.DSN), &gorm.Config{ Logger: logger.New( log.New(os.Stdout, "\r\n", log.LstdFlags), logger.Config{ SlowThreshold: 200 * time.Millisecond, LogLevel: logger.Warn, IgnoreRecordNotFoundError: true, Colorful: true, }, ), NowFunc: func() time.Time { return time.Now().UTC() }, }) if err != nil { return nil, err } sqlDB, err := db.DB() if err != nil { return nil, err } sqlDB.SetMaxOpenConns(cfg.MaxOpenConns) sqlDB.SetMaxIdleConns(cfg.MaxIdleConns) sqlDB.SetConnMaxLifetime(cfg.ConnMaxLifetime) sqlDB.SetConnMaxIdleTime(cfg.ConnMaxIdleTime) return db, nil } func AutoMigrate(db *gorm.DB) error { probe := newSchemaCache(db) if db.Dialector.Name() == "mysql" && db.Migrator().HasColumn("flight_data", "hook_realeases") && !db.Migrator().HasColumn("flight_data", "hook_releases") { if err := db.Exec("ALTER TABLE flight_data RENAME COLUMN hook_realeases TO hook_releases").Error; err != nil { return err } } if err := db.AutoMigrate( &auth.Role{}, &auth.Permission{}, &auth.User{}, &auth.UserRole{}, &auth.EmailOutboxMessage{}, &auth.PilotProfile{}, &auth.DoctorProfile{}, &auth.AirRescuerProfile{}, &auth.TechnicianProfile{}, &auth.FlightAssistantProfile{}, &auth.StaffProfile{}, &auth.RolePermission{}, &auth.UserIdentity{}, &auth.UserMicrosoftOAuthToken{}, &auth.UserTOTP{}, &auth.UserEmailLoginOTP{}, &auth.UserWebAuthnCredential{}, &auth.PasswordResetToken{}, &auth.SecurityPINResetToken{}, &transient.TokenEntry{}, &transient.QueueIdempotencyRecord{}, &helicopter.Helicopter{}, &helicopterusage.HelicopterUsage{}, &flightinspection.FlightInspection{}, &flight.Flight{}, &flightdata.FlightData{}, &flightdata.HESLOFlight{}, &flightdata.HESLOSling{}, &flightdata.LoggingSling{}, &flightdata.HECFlight{}, &flightdata.HECSling{}, &flightdata.HECLoads{}, &mission.Mission{}, &mission.MissionFile{}, &reserveac.ReserveAc{}, &helicopterfile.HelicopterFile{}, &beforeflightinspection.BeforeFlightInspection{}, &afterflightinspection.AfterFlightInspection{}, &fmreport.Report{}, &fmreport.FleetHistory{}, &flightprepcheck.FlightPrepCheck{}, &flightinspectionfilechecklist.FlightInspectionFileChecklist{}, &takeover.TakeoverAc{}, &takeover.TakeoverRosterCrew{}, &takeover.TakeoverOtherPerson{}, &otherperson.OtherPerson{}, &takeover.TakeoverFile{}, &complaint.Complaint{}, &easarelease.EASARelease{}, &actionsignoff.ActionSignoff{}, &mcf.MaintenanceCheckFlight{}, &fleethistory.FleetHistory{}, &fleetstatus.FleetStatus{}, &fleetstatus.MaintenanceSchedule{}, &fleetstatus.FleetStatusFile{}, &fleetstatus.FleetStatusServiceLog{}, &facility.Facility{}, &filemanager.Folder{}, &filemanager.File{}, &filemanager.Attachment{}, &filemanager.FileUploadIntent{}, &filemanager.FileProcessingOutboxMessage{}, &filemanager.FileStatusRealtimeEvent{}, &base.BaseCategory{}, &base.Base{}, &base.BaseOperationalShiftTime{}, &base.BaseContactRole{}, &dul.DUL{}, &medicine.MedicineGroup{}, &medicine.MotorReaction{}, &medicine.MedicineMotorReaction{}, &medicine.Medicine{}, &vocation.Vocation{}, &opc.Opc{}, &forces_present.ForcesPresent{}, &hospital.Hospital{}, &health_insurance_companies.HealthInsuranceCompany{}, &federal_state.FederalState{}, &icao.ICAO{}, &land.Land{}, &operation.HEMSOperationalData{}, &operation.OperationalDataForcePresent{}, &operation.OperationalFile{}, &operation.HEMSOperationCategory{}, &operation.HEMSOperation{}, &mastersettings.MasterSettings{}, &mastersettings.MasterSettingValue{}, &dutyroster.DutyRoster{}, &dutyroster.DutyRosterCrew{}, &airrescuechecklist.AirRescuerChecklist{}, &airrescuechecklist.AirRescuerChecklistItem{}, &audit.AuditLog{}, &insurancepatientdata.InsurancePatientData{}, &patientdata.PatientData{}, ); err != nil { return err } if db.Dialector.Name() == "mysql" { if err := ensureBaseOperationalShiftTimeColumns(db); err != nil { return err } if err := ensureBaseDefaultShiftColumns(db); err != nil { return err } if err := ensureHelicopterColumns(db); err != nil { return err } if err := ensureHelicopterFileAttachmentColumns(db); err != nil { return err } if err := ensureActionSignoffStandaloneFlightUnique(db); err != nil { return err } } if err := backfillFacilityCategories(db); err != nil { return err } // AutoMigrate won't remove columns; explicitly drop deprecated columns. deprecatedColumns := []struct { model any column string }{ {model: &helicopter.Helicopter{}, column: "maintenance"}, {model: &helicopter.Helicopter{}, column: "counter"}, {model: &flightinspection.FlightInspection{}, column: "helicopter_id"}, {model: &flightinspection.FlightInspection{}, column: "operation_id"}, {model: "flights", column: "mission_type"}, {model: &complaint.Complaint{}, column: "mcf_required"}, {model: &complaint.Complaint{}, column: "mcf_decided_at"}, {model: &complaint.Complaint{}, column: "mcf_decided_by"}, {model: &complaint.Complaint{}, column: "mcf_completed_at"}, {model: &complaint.Complaint{}, column: "mcf_result"}, {model: &complaint.Complaint{}, column: "mcf_notes"}, {model: &mcf.MaintenanceCheckFlight{}, column: "flight_inspection_id"}, {model: &complaint.Complaint{}, column: "flight_inspection_id"}, {model: &easarelease.EASARelease{}, column: "flight_inspection_id"}, {model: &complaint.Complaint{}, column: "action_signed_at"}, {model: &complaint.Complaint{}, column: "action_signed_by"}, {model: &actionsignoff.ActionSignoff{}, column: "action_taken"}, {model: &actionsignoff.ActionSignoff{}, column: "created_by"}, {model: &actionsignoff.ActionSignoff{}, column: "updated_by"}, {model: &actionsignoff.ActionSignoff{}, column: "deleted_at"}, {model: &actionsignoff.ActionSignoff{}, column: "deleted_by"}, // EASA maintenance program field removed from the release + its fm-report snapshot. {model: &easarelease.EASARelease{}, column: "easa_maintenance_program"}, {model: &fmreport.FleetHistory{}, column: "easa_maintenance_program"}, // medical_license_no, license_issued_at, and license_expired_at removed from doctor_profiles; // is_chief_physician_in_charge removed from air_rescuer_profiles. {model: &auth.DoctorProfile{}, column: "medical_license_no"}, {model: &auth.DoctorProfile{}, column: "license_issued_at"}, {model: &auth.DoctorProfile{}, column: "license_expired_at"}, {model: &auth.AirRescuerProfile{}, column: "is_chief_physician_in_charge"}, } if db.Dialector.Name() == "mysql" { deprecatedConstraints := []struct{ table, name string }{ {"complaints", "fk_complaints_flight_inspection"}, {"easa_releases", "fk_easa_releases_flight_inspection"}, {"maintenance_check_flights", "fk_maintenance_check_flights_flight_inspection"}, } for _, fc := range deprecatedConstraints { if db.Migrator().HasConstraint(fc.table, fc.name) { if err := db.Migrator().DropConstraint(fc.table, fc.name); err != nil { return err } } } for _, item := range deprecatedColumns { if !probe.HasColumn(item.model, item.column) { continue } if err := db.Migrator().DropColumn(item.model, item.column); err != nil { return err } } if db.Migrator().HasConstraint("patient_data", "fk_patient_data_place") { if err := db.Migrator().DropConstraint("patient_data", "fk_patient_data_place"); err != nil { return err } } if probe.HasColumn("patient_data", "place_id") { if err := db.Migrator().DropColumn("patient_data", "place_id"); err != nil { return err } } } if probe.HasTable("places") { if err := db.Migrator().DropTable("places"); err != nil { return err } } // Flight prep checks refactor: move from dynamic item rows to static boolean fields. if db.Migrator().HasConstraint(&flightprepcheck.FlightPrepCheck{}, "fk_flight_prep_checks_flight_prep_item") { if err := db.Migrator().DropConstraint(&flightprepcheck.FlightPrepCheck{}, "fk_flight_prep_checks_flight_prep_item"); err != nil { return err } } if db.Migrator().HasIndex(&flightprepcheck.FlightPrepCheck{}, "uk_flight_prep_checks_inspection_item") { if err := db.Migrator().DropIndex(&flightprepcheck.FlightPrepCheck{}, "uk_flight_prep_checks_inspection_item"); err != nil { return err } } if db.Migrator().HasIndex(&flightprepcheck.FlightPrepCheck{}, "idx_flight_prep_checks_flight_inspection_sorted") { if err := db.Migrator().DropIndex(&flightprepcheck.FlightPrepCheck{}, "idx_flight_prep_checks_flight_inspection_sorted"); err != nil { return err } } if probe.HasColumn(&flightprepcheck.FlightPrepCheck{}, "flight_prep_item_id") { if err := db.Migrator().DropColumn(&flightprepcheck.FlightPrepCheck{}, "flight_prep_item_id"); err != nil { return err } } if probe.HasColumn(&flightprepcheck.FlightPrepCheck{}, "is_done") { if err := db.Migrator().DropColumn(&flightprepcheck.FlightPrepCheck{}, "is_done"); err != nil { return err } } if probe.HasColumn(&flightprepcheck.FlightPrepCheck{}, "note") { if err := db.Migrator().DropColumn(&flightprepcheck.FlightPrepCheck{}, "note"); err != nil { return err } } if !probe.HasColumn(&flightprepcheck.FlightPrepCheck{}, "notam_briefing") { if err := db.Migrator().AddColumn(&flightprepcheck.FlightPrepCheck{}, "NOTAMBriefing"); err != nil { return err } } if !probe.HasColumn(&flightprepcheck.FlightPrepCheck{}, "weather_briefing") { if err := db.Migrator().AddColumn(&flightprepcheck.FlightPrepCheck{}, "WeatherBriefing"); err != nil { return err } } if !probe.HasColumn(&flightprepcheck.FlightPrepCheck{}, "operational_flight_plan") { if err := db.Migrator().AddColumn(&flightprepcheck.FlightPrepCheck{}, "OperationalFlightPlan"); err != nil { return err } } if !db.Migrator().HasIndex(&flightprepcheck.FlightPrepCheck{}, "uk_flight_prep_checks_inspection") { if err := db.Migrator().CreateIndex(&flightprepcheck.FlightPrepCheck{}, "uk_flight_prep_checks_inspection"); err != nil { return err } } // Rename legacy duty roster tables to neutral names. if probe.HasTable("hems_duty_rosters") && !probe.HasTable("duty_rosters") { if err := db.Exec("RENAME TABLE hems_duty_rosters TO duty_rosters").Error; err != nil { return err } } if probe.HasTable("hems_duty_roster_crews") && !probe.HasTable("duty_roster_crews") { if err := db.Exec("RENAME TABLE hems_duty_roster_crews TO duty_roster_crews").Error; err != nil { return err } } // Rename legacy duty roster base column to general name. if probe.HasColumn(&dutyroster.DutyRoster{}, "hems_base_id") && !probe.HasColumn(&dutyroster.DutyRoster{}, "base_id") { if err := db.Exec("ALTER TABLE duty_rosters RENAME COLUMN hems_base_id TO base_id").Error; err != nil { return err } } if err := db.Exec("UPDATE users SET username = NULL WHERE username IS NOT NULL AND TRIM(username) = ''").Error; err != nil { return err } if db.Dialector.Name() == "mysql" && probe.HasColumn(&auth.User{}, "username") { if err := db.Exec("ALTER TABLE users MODIFY COLUMN username varchar(100) NULL").Error; err != nil { return err } } if err := backfillUserRoles(db); err != nil { return err } if err := backfillBaseCategories(db); err != nil { return err } if err := backfillMissionTypes(db); err != nil { return err } if err := backfillPermissionRequiresPIN(db); err != nil { return err } if err := alignFlightDataHospitalConstraints(db); err != nil { return err } if err := ensureFlightDataPilotNullable(db); err != nil { return err } if err := ensureFlightDataStatusColumn(db); err != nil { return err } if err := ensureMissionTimeColumns(db); err != nil { return err } if db.Dialector.Name() == "mysql" { if err := db.Exec("ALTER TABLE flights MODIFY COLUMN mission_code varchar(64) NULL").Error; err != nil { return err } if err := db.Exec("UPDATE flights SET mission_code = NULL WHERE mission_code IS NOT NULL AND TRIM(mission_code) = ''").Error; err != nil { return err } } return nil } // ensureActionSignoffStandaloneFlightUnique enforces one standalone (complaint-independent) // sign-off per flight via a functional unique index: the key is flight_id for standalone // rows (complaint_id IS NULL) and NULL for complaint-linked rows (which are deduped by // uidx_action_signoffs_complaint_id instead). Requires MySQL 8.0.13+ (functional index). func ensureActionSignoffStandaloneFlightUnique(db *gorm.DB) error { if !db.Migrator().HasTable(&actionsignoff.ActionSignoff{}) { return nil } if db.Migrator().HasIndex(&actionsignoff.ActionSignoff{}, "uidx_action_signoffs_standalone_flight") { return nil } return db.Exec( "CREATE UNIQUE INDEX uidx_action_signoffs_standalone_flight " + "ON action_signoffs ((IF(complaint_id IS NULL, flight_id, NULL)))", ).Error } func ensureBaseOperationalShiftTimeColumns(db *gorm.DB) error { if !db.Migrator().HasTable(&base.BaseOperationalShiftTime{}) { return nil } if !db.Migrator().HasColumn(&base.BaseOperationalShiftTime{}, "DateStart") { if err := db.Exec(`ALTER TABLE base_operational_shift_times ADD COLUMN date_start DATE NULL`).Error; err != nil { return err } } if !db.Migrator().HasColumn(&base.BaseOperationalShiftTime{}, "DateEnd") { if err := db.Exec(`ALTER TABLE base_operational_shift_times ADD COLUMN date_end DATE NULL`).Error; err != nil { return err } } if db.Migrator().HasColumn("base_operational_shift_times", "operation_date") { if err := db.Exec(` UPDATE base_operational_shift_times SET date_start = COALESCE(date_start, operation_date), date_end = COALESCE(date_end, operation_date) WHERE deleted_at IS NULL `).Error; err != nil { return err } if db.Migrator().HasConstraint(&base.BaseOperationalShiftTime{}, "fk_base_operational_shift_times_base") { if err := db.Exec(`ALTER TABLE base_operational_shift_times DROP FOREIGN KEY fk_base_operational_shift_times_base`).Error; err != nil { return err } } if db.Migrator().HasIndex(&base.BaseOperationalShiftTime{}, "idx_base_operational_shift_times_base_date") { if err := db.Migrator().DropIndex(&base.BaseOperationalShiftTime{}, "idx_base_operational_shift_times_base_date"); err != nil { return err } } if err := db.Exec(`ALTER TABLE base_operational_shift_times DROP COLUMN operation_date`).Error; err != nil { return err } if err := db.Migrator().CreateIndex(&base.BaseOperationalShiftTime{}, "idx_base_operational_shift_times_base_date"); err != nil { return err } if err := db.Exec(` ALTER TABLE base_operational_shift_times ADD CONSTRAINT fk_base_operational_shift_times_base FOREIGN KEY (base_id) REFERENCES bases(id) ON UPDATE CASCADE ON DELETE CASCADE `).Error; err != nil { return err } } return db.Exec(` ALTER TABLE base_operational_shift_times MODIFY COLUMN date_start DATE NULL, MODIFY COLUMN date_end DATE NULL, MODIFY COLUMN shift_start TIME NULL, MODIFY COLUMN shift_end TIME NULL `).Error } func ensureBaseDefaultShiftColumns(db *gorm.DB) error { if !db.Migrator().HasTable(&base.Base{}) { return nil } if !db.Migrator().HasColumn(&base.Base{}, "DefaultStartTimeType") { if err := db.Exec(`ALTER TABLE bases ADD COLUMN default_start_time_type VARCHAR(16) NOT NULL DEFAULT 'FIXED'`).Error; err != nil { return err } } if !db.Migrator().HasColumn(&base.Base{}, "DefaultEndTimeType") { if err := db.Exec(`ALTER TABLE bases ADD COLUMN default_end_time_type VARCHAR(16) NOT NULL DEFAULT 'FIXED'`).Error; err != nil { return err } } return db.Exec(` ALTER TABLE bases MODIFY COLUMN default_shift_start TIME NULL, MODIFY COLUMN default_shift_end TIME NULL `).Error } func alignFlightDataHospitalConstraints(db *gorm.DB) error { if db == nil || db.Dialector.Name() != "mysql" { return nil } if err := alignFlightDataHospitalConstraint(db, "from_hospital_id", "FromHospital", "fk_flight_data_from_hospital"); err != nil { return err } if err := alignFlightDataHospitalConstraint(db, "to_hospital_id", "ToHospital", "fk_flight_data_to_hospital"); err != nil { return err } return nil } func alignFlightDataHospitalConstraint(db *gorm.DB, column, association, constraintName string) error { type foreignKeyInfo struct { ConstraintName string `gorm:"column:constraint_name"` ReferencedTableName string `gorm:"column:referenced_table_name"` } var info foreignKeyInfo err := db.Table("information_schema.key_column_usage"). Select("constraint_name, referenced_table_name"). Where("table_schema = DATABASE() AND table_name = ? AND column_name = ? AND referenced_table_name IS NOT NULL", "flight_data", column). Order("ordinal_position ASC"). Limit(1). Scan(&info).Error if err != nil { return err } if info.ReferencedTableName == "no_icao_codes" { return nil } if info.ConstraintName != "" { if err := db.Exec("ALTER TABLE `flight_data` DROP FOREIGN KEY `" + info.ConstraintName + "`").Error; err != nil { return err } } return db.Migrator().CreateConstraint(&flightdata.FlightData{}, association) } func ensureFlightDataPilotNullable(db *gorm.DB) error { if db == nil || db.Dialector.Name() != "mysql" { return nil } if !db.Migrator().HasTable(&flightdata.FlightData{}) { return nil } type columnInfo struct { IsNullable string `gorm:"column:is_nullable"` } var info columnInfo if err := db.Table("information_schema.columns"). Select("is_nullable"). Where("table_schema = DATABASE() AND table_name = ? AND column_name = ?", "flight_data", "co_pilot_id"). Limit(1). Scan(&info).Error; err != nil { return err } // Column missing (e.g. not yet created by migrations) or already nullable: nothing to do. if strings.TrimSpace(info.IsNullable) == "" || strings.EqualFold(strings.TrimSpace(info.IsNullable), "YES") { return nil } return db.Exec("ALTER TABLE flight_data MODIFY COLUMN co_pilot_id binary(16) NULL").Error } func ensureFlightDataStatusColumn(db *gorm.DB) error { if db == nil { return nil } if !db.Migrator().HasTable(&flightdata.FlightData{}) { return nil } if !db.Migrator().HasColumn(&flightdata.FlightData{}, "status") { addColumnSQL := "ALTER TABLE flight_data ADD COLUMN status varchar(20) NOT NULL DEFAULT 'in_progress'" if db.Dialector.Name() == "mysql" { addColumnSQL = "ALTER TABLE flight_data ADD COLUMN status varchar(20) NOT NULL DEFAULT 'in_progress' AFTER id" } if err := db.Exec(addColumnSQL).Error; err != nil { return err } } if err := db.Exec("UPDATE flight_data SET status = 'in_progress' WHERE status IS NULL OR TRIM(status) = '' OR status IN ('on_going', 'on_progress')").Error; err != nil { return err } return db.Exec("UPDATE flight_data SET status = 'completed' WHERE status = 'complete'").Error } func ensureMissionTimeColumns(db *gorm.DB) error { if db == nil { return nil } if !db.Migrator().HasTable(&mission.Mission{}) { return nil } if db.Dialector.Name() != "mysql" { return nil } return db.Exec(` ALTER TABLE missions MODIFY COLUMN start_time TIME NULL, MODIFY COLUMN end_time TIME NULL `).Error } func ensureHelicopterColumns(db *gorm.DB) error { if !db.Migrator().HasTable(&helicopter.Helicopter{}) { return nil } if !db.Migrator().HasColumn(&helicopter.Helicopter{}, "DeletedAt") { if err := db.Exec(`ALTER TABLE helicopters ADD COLUMN deleted_at DATETIME(6) NULL AFTER updated_at`).Error; err != nil { return err } } return nil } func ensureHelicopterFileAttachmentColumns(db *gorm.DB) error { if db == nil || db.Dialector.Name() != "mysql" { return nil } if !db.Migrator().HasTable(&helicopterfile.HelicopterFile{}) { return nil } if !db.Migrator().HasColumn(&helicopterfile.HelicopterFile{}, "SourceFileID") { if err := db.Exec("ALTER TABLE helicopter_files ADD COLUMN source_file_id binary(16) NULL AFTER file_attachment_id").Error; err != nil { return err } } return db.Exec("ALTER TABLE helicopter_files MODIFY COLUMN file_attachment_id binary(16) NULL").Error } func backfillBaseCategories(db *gorm.DB) error { now := time.Now().UTC() seeds := []struct { key string name string }{ {key: base.CategoryKeyRegular, name: "Regular"}, {key: base.CategoryKeyHEMS, name: "HEMS"}, } for _, seed := range seeds { if err := db.Exec( `INSERT INTO base_categories (id, `+"`key`"+`, name, created_at, updated_at) SELECT ?, ?, ?, ?, ? WHERE NOT EXISTS ( SELECT 1 FROM base_categories WHERE `+"`key`"+` = ? )`, uuidv7.MustBytes(), seed.key, seed.name, now, now, seed.key, ).Error; err != nil { return fmt.Errorf("seed base category %q: %w", seed.key, err) } } var regular struct { ID []byte `gorm:"column:id"` } if err := db.Table("base_categories"). Select("id"). Where("`key` = ?", base.CategoryKeyRegular). Take(®ular).Error; err != nil { return err } return db.Exec( "UPDATE bases SET base_category_id = ? WHERE base_category_id IS NULL OR LENGTH(base_category_id) = 0", regular.ID, ).Error } func backfillFacilityCategories(db *gorm.DB) error { if db == nil { return nil } mappings := map[string]string{ "fuel-truck": "heslo-rope-label", "heslo-sling": "heslo-rope-length", "logging-sling": "heslo-hook", "hec-sling": "hec-rope-label", "hems-hec-sling": "hec-rope-length", } for oldValue, newValue := range mappings { if err := db.Exec( "UPDATE facilities SET category = ? WHERE LOWER(TRIM(category)) = ?", newValue, oldValue, ).Error; err != nil { return fmt.Errorf("backfill facility category %q -> %q: %w", oldValue, newValue, err) } } return nil } func backfillUserRoles(db *gorm.DB) error { var users []struct { ID []byte `gorm:"column:id"` RoleID []byte `gorm:"column:role_id"` } if err := db.Table("users"). Select("id, role_id"). Where("role_id IS NOT NULL"). Find(&users).Error; err != nil { return err } if len(users) == 0 { return nil } items := make([]auth.UserRole, 0, len(users)) for i := range users { if len(users[i].ID) != 16 || len(users[i].RoleID) != 16 { continue } items = append(items, auth.UserRole{ UserID: append([]byte(nil), users[i].ID...), RoleID: append([]byte(nil), users[i].RoleID...), }) } if len(items) == 0 { return nil } return db.Clauses(clause.OnConflict{ Columns: []clause.Column{{Name: "user_id"}, {Name: "role_id"}}, DoNothing: true, }).Create(&items).Error } func backfillPermissionRequiresPIN(db *gorm.DB) error { // Intentionally no-op. // Do not override existing permissions.requires_pin values on startup. // Source of truth for requires_pin is the database row itself. _ = db return nil } func backfillMissionTypes(db *gorm.DB) error { categories := []struct { key string name string id []byte }{ {key: "HEMS", name: "HEMS", id: uuidv7.MustBytes()}, {key: "CAT", name: "CAT", id: uuidv7.MustBytes()}, {key: "SPO", name: "SPO", id: uuidv7.MustBytes()}, {key: "NCO", name: "NCO", id: uuidv7.MustBytes()}, } for i := range categories { if err := db.Exec( `INSERT INTO mission_categories (id, code_type, name) SELECT ?, ?, ? WHERE NOT EXISTS ( SELECT 1 FROM mission_categories WHERE code_type = ? )`, categories[i].id, categories[i].key, categories[i].name, categories[i].key, ).Error; err != nil { return fmt.Errorf("seed mission category %q: %w", categories[i].key, err) } } var hems struct { ID []byte `gorm:"column:id"` } if err := db.Table("mission_categories"). Select("id"). Where("code_type = ?", "HEMS"). Take(&hems).Error; err != nil { return err } subtypes := []struct { code string name string taskName string id []byte }{ {code: "PRIM", name: "Primary mission", taskName: "Primary mission task", id: uuidv7.MustBytes()}, {code: "SECONDARY", name: "Secondary mission", taskName: "Secondary mission task", id: uuidv7.MustBytes()}, } for i := range subtypes { if err := db.Exec( `INSERT INTO mission_subcategories (id, mission_category_id, sub_type, name, task_name) SELECT ?, ?, ?, ?, ? WHERE NOT EXISTS ( SELECT 1 FROM mission_subcategories WHERE mission_category_id = ? AND sub_type = ? )`, subtypes[i].id, hems.ID, subtypes[i].code, subtypes[i].name, subtypes[i].taskName, hems.ID, subtypes[i].code, ).Error; err != nil { return fmt.Errorf("seed mission subtype %q: %w", subtypes[i].code, err) } } return nil }