829 lines
26 KiB
Go
829 lines
26 KiB
Go
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
|
|
}
|