package version import ( "fmt" "runtime" "go-admin/app/bell/contact" duty "go-admin/app/bell/duty_schedule" "go-admin/cmd/migrate/migration" common "go-admin/common/models" "gorm.io/gorm" "gorm.io/gorm/clause" ) func init() { _, fileName, _, _ := runtime.Caller(0) migration.Migrate.SetVersion(migration.GetFilename(fileName), migrateBellContactSchedule) } func migrateBellContactSchedule(db *gorm.DB, version string) error { return db.Transaction(func(tx *gorm.DB) error { if err := tx.AutoMigrate(&contact.Contact{}, &contact.Channel{}, &contact.ChannelValidation{}, &contact.AuditFact{}, &duty.Group{}, &duty.Member{}, &duty.ScheduleVersion{}, &duty.RotationSlot{}, &duty.Override{}, &duty.AuditFact{}); err != nil { return err } if tx.Dialector.Name() == "postgres" { for _, sql := range contactScheduleSQL { if err := tx.Exec(sql).Error; err != nil { return err } } if err := seedContactScheduleAccess(tx); err != nil { return err } } return tx.Clauses(clause.OnConflict{DoNothing: true}).Create(&common.Migration{Version: version}).Error }) } var contactScheduleSQL = []string{ `ALTER TABLE bell_contacts ADD CONSTRAINT bell_contacts_version_check CHECK (version > 0)`, `ALTER TABLE bell_contact_channels ADD CONSTRAINT bell_contact_channels_kind_check CHECK (kind IN ('sms','voice'))`, `ALTER TABLE bell_contact_channels ADD CONSTRAINT bell_contact_channels_contact_fk FOREIGN KEY (contact_id) REFERENCES bell_contacts(id) ON UPDATE RESTRICT ON DELETE RESTRICT`, `CREATE UNIQUE INDEX bell_contact_channel_identity_idx ON bell_contact_channels(contact_id,kind,address_fingerprint)`, `CREATE TRIGGER bell_contact_channels_immutable BEFORE UPDATE OR DELETE ON bell_contact_channels FOR EACH ROW EXECUTE FUNCTION bell_reject_immutable_fact()`, `ALTER TABLE bell_contact_channel_validations ADD CONSTRAINT bell_contact_validation_status_check CHECK (status IN ('verified','failed'))`, `ALTER TABLE bell_contact_channel_validations ADD CONSTRAINT bell_contact_validation_channel_fk FOREIGN KEY (channel_id) REFERENCES bell_contact_channels(id) ON UPDATE RESTRICT ON DELETE RESTRICT`, `ALTER TABLE bell_contact_audit_facts ADD CONSTRAINT bell_contact_audit_contact_fk FOREIGN KEY (contact_id) REFERENCES bell_contacts(id) ON UPDATE RESTRICT ON DELETE RESTRICT`, `ALTER TABLE bell_duty_groups ADD CONSTRAINT bell_duty_groups_version_check CHECK (version > 0)`, `ALTER TABLE bell_duty_members ADD CONSTRAINT bell_duty_member_role_check CHECK (role IN ('primary','backup'))`, `ALTER TABLE bell_duty_members ADD CONSTRAINT bell_duty_member_group_fk FOREIGN KEY (group_id) REFERENCES bell_duty_groups(id) ON UPDATE RESTRICT ON DELETE RESTRICT`, `ALTER TABLE bell_duty_members ADD CONSTRAINT bell_duty_member_contact_fk FOREIGN KEY (contact_id) REFERENCES bell_contacts(id) ON UPDATE RESTRICT ON DELETE RESTRICT`, `CREATE UNIQUE INDEX bell_duty_schedule_group_version_idx ON bell_duty_schedule_versions(group_id,version)`, `ALTER TABLE bell_duty_schedule_versions ADD CONSTRAINT bell_duty_schedule_status_check CHECK (status IN ('draft','published'))`, `ALTER TABLE bell_duty_schedule_versions ADD CONSTRAINT bell_duty_schedule_group_fk FOREIGN KEY (group_id) REFERENCES bell_duty_groups(id) ON UPDATE RESTRICT ON DELETE RESTRICT`, `CREATE OR REPLACE FUNCTION bell_guard_schedule_version() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN IF TG_OP = 'DELETE' OR OLD.status = 'published' THEN RAISE EXCEPTION 'Bell published schedule cannot be changed' USING ERRCODE = '55000'; END IF; IF NEW.status <> 'published' OR OLD.status <> 'draft' THEN RAISE EXCEPTION 'Bell schedule transition is invalid' USING ERRCODE = '55000'; END IF; RETURN NEW; END $$`, `CREATE TRIGGER bell_duty_schedule_version_guard BEFORE UPDATE OR DELETE ON bell_duty_schedule_versions FOR EACH ROW EXECUTE FUNCTION bell_guard_schedule_version()`, `ALTER TABLE bell_duty_rotation_slots ADD CONSTRAINT bell_duty_slot_range_check CHECK (weekday BETWEEN 0 AND 6 AND start_minute >= 0 AND end_minute <= 1440 AND start_minute < end_minute AND primary_contact_id <> backup_contact_id)`, `ALTER TABLE bell_duty_rotation_slots ADD CONSTRAINT bell_duty_slot_version_fk FOREIGN KEY (schedule_version_id) REFERENCES bell_duty_schedule_versions(id) ON UPDATE RESTRICT ON DELETE RESTRICT`, `ALTER TABLE bell_duty_rotation_slots ADD CONSTRAINT bell_duty_slot_primary_fk FOREIGN KEY (primary_contact_id) REFERENCES bell_contacts(id) ON UPDATE RESTRICT ON DELETE RESTRICT`, `ALTER TABLE bell_duty_rotation_slots ADD CONSTRAINT bell_duty_slot_backup_fk FOREIGN KEY (backup_contact_id) REFERENCES bell_contacts(id) ON UPDATE RESTRICT ON DELETE RESTRICT`, `ALTER TABLE bell_duty_overrides ADD CONSTRAINT bell_duty_override_range_check CHECK (starts_at < ends_at AND original_contact_id <> replacement_contact_id)`, `ALTER TABLE bell_duty_overrides ADD CONSTRAINT bell_duty_override_group_fk FOREIGN KEY (group_id) REFERENCES bell_duty_groups(id) ON UPDATE RESTRICT ON DELETE RESTRICT`, `ALTER TABLE bell_duty_overrides ADD CONSTRAINT bell_duty_override_original_fk FOREIGN KEY (original_contact_id) REFERENCES bell_contacts(id) ON UPDATE RESTRICT ON DELETE RESTRICT`, `ALTER TABLE bell_duty_overrides ADD CONSTRAINT bell_duty_override_replacement_fk FOREIGN KEY (replacement_contact_id) REFERENCES bell_contacts(id) ON UPDATE RESTRICT ON DELETE RESTRICT`, `ALTER TABLE bell_duty_audit_facts ADD CONSTRAINT bell_duty_audit_group_fk FOREIGN KEY (group_id) REFERENCES bell_duty_groups(id) ON UPDATE RESTRICT ON DELETE RESTRICT`, `CREATE TRIGGER bell_contact_validations_immutable BEFORE UPDATE OR DELETE ON bell_contact_channel_validations FOR EACH ROW EXECUTE FUNCTION bell_reject_immutable_fact()`, `CREATE TRIGGER bell_contact_audit_immutable BEFORE UPDATE OR DELETE ON bell_contact_audit_facts FOR EACH ROW EXECUTE FUNCTION bell_reject_immutable_fact()`, `CREATE TRIGGER bell_duty_slots_immutable BEFORE UPDATE OR DELETE ON bell_duty_rotation_slots FOR EACH ROW EXECUTE FUNCTION bell_reject_immutable_fact()`, `CREATE TRIGGER bell_duty_overrides_immutable BEFORE UPDATE OR DELETE ON bell_duty_overrides FOR EACH ROW EXECUTE FUNCTION bell_reject_immutable_fact()`, `CREATE TRIGGER bell_duty_audit_immutable BEFORE UPDATE OR DELETE ON bell_duty_audit_facts FOR EACH ROW EXECUTE FUNCTION bell_reject_immutable_fact()`, } type contactScheduleSeed struct { ID int Path string Action string } func seedContactScheduleAccess(tx *gorm.DB) error { if err := tx.Exec(`SELECT setval(pg_get_serial_sequence('sys_menu','menu_id'),GREATEST((SELECT max(menu_id) FROM sys_menu),1));SELECT setval(pg_get_serial_sequence('sys_api','id'),GREATEST((SELECT max(id) FROM sys_api),1))`).Error; err != nil { return err } var rootID int if err := tx.Raw("SELECT menu_id FROM sys_menu WHERE path='/bell' AND parent_id=0 ORDER BY menu_id LIMIT 1").Scan(&rootID).Error; err != nil || rootID == 0 { return fmt.Errorf("Bell menu root missing") } contacts, err := insertContactScheduleMenu(tx, rootID, "BellContacts", "联系人与通道", "user", "contacts", "C", "bell:contact:list", "", "/bell/contacts/index", 4) if err != nil { return err } dutyMenu, err := insertContactScheduleMenu(tx, rootID, "BellDutySchedules", "值班排班", "time", "duty-schedules", "C", "bell:duty:list", "", "/bell/duty-schedules/index", 5) if err != nil { return err } contactWrite, err := insertContactScheduleMenu(tx, contacts.ID, "", "维护联系人", "", "", "F", "bell:contact:write", "POST", "", 1) if err != nil { return err } dutyWrite, err := insertContactScheduleMenu(tx, dutyMenu.ID, "", "维护排班", "", "", "F", "bell:duty:write", "POST", "", 1) if err != nil { return err } specs := []struct{ title, path, action string }{ {"联系人列表", "/api/v1/bell/contacts", "GET"}, {"新增联系人", "/api/v1/bell/contacts", "POST"}, {"修改联系人", "/api/v1/bell/contacts/:id", "PUT"}, {"启停联系人", "/api/v1/bell/contacts/:id/enabled", "PUT"}, {"新增联系通道", "/api/v1/bell/contacts/:id/channels", "POST"}, {"记录通道验证", "/api/v1/bell/contact-channels/:id/validations", "POST"}, {"值班组列表", "/api/v1/bell/duty-groups", "GET"}, {"新增值班组", "/api/v1/bell/duty-groups", "POST"}, {"修改值班组", "/api/v1/bell/duty-groups/:id", "PUT"}, {"保存值班成员", "/api/v1/bell/duty-groups/:id/members", "POST"}, {"新增排班版本", "/api/v1/bell/duty-groups/:id/schedules", "POST"}, {"发布排班版本", "/api/v1/bell/duty-schedules/:id/publish", "POST"}, {"新增临时替班", "/api/v1/bell/duty-groups/:id/overrides", "POST"}, } apis := make([]contactScheduleSeed, 0, len(specs)) for _, s := range specs { v, e := insertContactScheduleAPI(tx, s.title, s.path, s.action) if e != nil { return e } apis = append(apis, v) } links := map[int][]contactScheduleSeed{contacts.ID: {apis[0]}, contactWrite.ID: apis[1:6], dutyMenu.ID: {apis[6]}, dutyWrite.ID: apis[7:]} for menu, items := range links { for _, item := range items { if err := tx.Exec("INSERT INTO sys_menu_api_rule(sys_menu_menu_id,sys_api_id) VALUES(?,?) ON CONFLICT DO NOTHING", menu, item.ID).Error; err != nil { return err } } } var operator int if err := tx.Raw("SELECT role_id FROM sys_role WHERE role_key='operator' AND deleted_at IS NULL ORDER BY role_id LIMIT 1").Scan(&operator).Error; err != nil { return err } if operator != 0 { for _, menu := range []contactScheduleSeed{contacts, dutyMenu} { if err := tx.Exec("INSERT INTO sys_role_menu(role_id,menu_id) VALUES(?,?) ON CONFLICT DO NOTHING", operator, menu.ID).Error; err != nil { return err } } for _, item := range []contactScheduleSeed{apis[0], apis[6]} { if err := tx.Exec("INSERT INTO casbin_rule(ptype,v0,v1,v2,v3,v4,v5) VALUES('p','operator',?,?, '', '', '') ON CONFLICT DO NOTHING", item.Path, item.Action).Error; err != nil { return err } } } return nil } func insertContactScheduleMenu(tx *gorm.DB, parent int, name, title, icon, path, menuType, permission, action, component string, sort int) (contactScheduleSeed, error) { var id int err := tx.Raw(`INSERT INTO sys_menu(menu_name,title,icon,path,paths,menu_type,action,permission,parent_id,no_cache,breadcrumb,component,sort,visible,is_frame,create_by,update_by,created_at,updated_at) VALUES(?,?,?,?, '',?,?,?,?,false,'',?,?, '0','1',1,1,now(),now()) RETURNING menu_id`, name, title, icon, path, menuType, action, permission, parent, component, sort).Scan(&id).Error if err != nil { return contactScheduleSeed{}, err } var parentPaths string if err = tx.Raw("SELECT paths FROM sys_menu WHERE menu_id=?", parent).Scan(&parentPaths).Error; err != nil { return contactScheduleSeed{}, err } if err = tx.Exec("UPDATE sys_menu SET paths=? WHERE menu_id=?", fmt.Sprintf("%s/%d", parentPaths, id), id).Error; err != nil { return contactScheduleSeed{}, err } return contactScheduleSeed{ID: id}, nil } func insertContactScheduleAPI(tx *gorm.DB, title, path, action string) (contactScheduleSeed, error) { var id int err := tx.Raw(`INSERT INTO sys_api(handle,title,path,type,action,created_at,updated_at,create_by,update_by) VALUES('',?,?, 'BUS',?,now(),now(),1,1) RETURNING id`, title, path, action).Scan(&id).Error return contactScheduleSeed{ID: id, Path: path, Action: action}, err }